Monday, November 28, 2005

The max value of Null

Nulls are always hot to discuss, especially as it often ends up being something DBA's and developers can argue about for entire lifetimes. Surprisingly few lifetimes are shortened because a DBA and a developer get into a fight over the Very Important Topic of ... Nothing/Null.

Well, two recent examples on how to handle nulls in important systems are so funny that I have to tell them, then I shall say no more on this topic. After all, Chris Date has written entire books on the topic, while another good friend, Lex de Haan, somehow restricts himself to entire days of the (three-valued) logic of Nulls.

First Case: A huge, public system is based on a system developed for another database than Oracle, and so the whole application is made "database independant".

I quoted Jonathan Lewis' chapter 10 of Tales of The OakTable where he says that only many users or much data will create problems for database-independant designs, and since this system will be big and busy, it should be fun to watch in the decades to come.

For one thing, no RI stuff was implemented using the standard SQL way of doing so. After all, who needs PK, FK, and all the rest when you can - hold on to your tables and chairs - can implement the whole thing using PL/SQL in triggers and procedures. Hey, never mind the famous /*+ no_trigger */ hint available in some versions. We're going on that picnic.

Second, nulls were implemented as 192 blanks. No, I'm not drunk and I haven't eaten funny mushrooms. I haven't even had more than a lot of beers in the last hour. 192 blanks it is. Of course I think 192 is a beautiful number, near-perfect, to tell you the truth. But why exactly that number to represent a null value? We'll possibly never know.

Second Case: A DBA told me this story: It had been a long and hard Summer, trying to convince the developers to use, say, only not null columns in primary keys - and not succeding. Well, there were glimpses of hope, they were beginning to understand things about ER diagramming.

That's what the DBA was thinking, hoping, perhaps even believing... until the other day.

The other day one of the developers had come to the DBA with a Very Good Question:

"How do I find the max value of nulls?"

That's when the DBA started crying, left work early, and consumed a bottle of alcohol that night.

/developer/null is the only thing I can add.


Blogger Noons said...

One major government project in Australia:

a)No, we do not release SQL scripts to update the schema, SQL is "proprietary"! We write a program to translate the SQL to XML and write another program to read the XML and act on the database.

b)We do not use inefficient joins to get data from multiple tables: we denormalize the query into a single result format table and we use "beans" to populate the result table from the original prime tables every time there is a change to the originals.

It's *only* been going for 3 years, four major *redesigns*, a small army of 40 developers to write about 35 different screens. And so on.

Spot the next disaster project: this one will beat the Australian Customs one, guaranteed! And yes, it is the SAME mob, under a different name!

Apparently, it's hard to get the message into the heads of IT governance in this country, they need multiple nailings...

6:32 PM  
Blogger Moans Nogood said...

Excellent example of design decisions from Hell! We have another project here (huge, man, huge) where it's only neccessary with five tables, since they are very, uhm, general in their layout.

Nothing like the big, hairy, lonely table whose first column really is the table name :).

10:08 PM  
Blogger Niall said...

Nulls, we do not use nulls. Oh no.

Obviously it would be a pain to have to supply values for a column so *all* columns have default values

number = 0
varchar2 = ' '
date = '01-Jan-1900 00:00'
clob = ' '

we do not use any other data type.

As far as design goes. We have a reporting type (maybe even DW) database

DATAVALUE - for storing the values (in a varchar)
DATASET - for grouping sets of data
DATAGROUP - for grouping sets of datasets

Guranteed I think to annoy the relational, the DW *and* the Object types all at once. A work of true genius.

10:21 PM  
Blogger Roderick said...

But think of all the row migration that will be avoided when those 192 blanks are updated to an actual value!! :-)

Perhaps the developer got confused about the ability to optionally sort NULLs first or last. So in an ORDER BY, NULLs act like an ACE in blackjack; it can be the lowest card (one) or the highest (eleven). :-)

I was always confused by NULLs until I was enlightened by the genius Donald Rumsfeld in his poem The Unknown.

8:29 PM  
Blogger Roger Snowden said...

Roderick and I had a conversion about the Rumsfeld verses a year or so ago. Roderick is not being sarcastic-- I think we agree Rumsfeld is a genius and those journalists who made fun of him are simply incapable of understand "null" concepts.

Apps developers are ostensibly engineers, but in reality are people who cannot comprehend the value of baselines. DBAs, on the other hand, are ostensibly administrators and not engineers. Of course, they cannot comprehend baselines either. Nevertheless, the two groups are culturally opposed.

The "null" issue reminds me of the situation in statistics where you consider the probability of an event under a continuous function having a particular discrete value. That probability is said by statisticians to be "zero", but they are wrong, wrong, wrong.

Statisticians are mathematicians, but are pragmatists, you see. Personally, I think that probability is really "infinitesimal", which simply rounds to zero. Something is going on there, just not much and we can't quantify it precisely.

Of course, being neither an apps developer nor a DBA; not a statistician, mathematician nor a pragmatist, I can only assure you the probability is "null".

My friend Ricky Sanchez asplained this all to me the last time I saw him at IOUG-A. "Just do the math", he said, "just do the math."

Smart guy.

5:57 PM  
Anonymous Anonymous said...

but why does

SELECT SUM(a_number_column)
FROM my_table

return NULL rather than 0 (at least in Oracle and MySQL)? Surely the SUM of zero numerical elements is zero rather than an unknown quantity.

11:13 AM  
Blogger Erik Ykema said...

Please show me how you determine that the outcome of that SQL is null.

12:46 PM  
Blogger Jason said...

"DBA's" is possessive. You meant "DBAs".

11:26 AM  
Blogger pandorasell said...

Clip flees and this Chrismas costs buy pandora jewelery coming near. Surprise! Most belated cheap pandora jewelery at Discount Price I plan to explore the net because an allow Christmas gift discount pandora jewelery .In the online search appendage, I unexpectedly read a rattling occupying pandora bracelets and unbelievable story, and I hence besides chose pandora silver bracelets blimey Christmas Day empowers.A-list gross revenue from colligates from london sweetheart cheap pandora bracelets Decades ago in London, a small restaurant enjoyed good business concern and attacted a lot returned discount pandora bracelets buyers.called for to express thanks to these pandora bracelets sale ,

8:36 AM  
Blogger weddingdresses said...

Welcome to our Coach Outlet Store, 70% Off, Coach Purses, 2011 Coach Outlet Store Online and shopping, coach outlet store are proud to offer you the Best Designer Handbags. Our coach purses lasting quality, exquisite craft. Discount Handbags are perfect for young fashionable girls as well as mature traditional women. We believe this discount Coach Purses is what you want. It must be a wise choice for you to choose these Coach Handbags !

so many 2011 new porducts here,70% just for you!
winter boots
power balance bracelet
power balance
Coach Bags
Coach Shoulder bags
Coach Crossbody Bags
Coach Wallets
Coach Business Bags
Coach Backpacks
Coach Sling Bags

1:46 AM  
Anonymous Anonymous said...

It's Friday morning and I'm on my way away from San Francisco after a splendid week of OOW, good guys, a few beers, and a lot of tech talk.Cheap Soccer Jersey | Cheap Football Shirts | france jersey euro 2012 | germany national team jersey | italy jersey soccer shirt | japan soccer jersey 2012 | mexico soccer jersey wholesale | netherlands jersey euro 2012 | portugal euro 2012 jersey | russia jersey shirts wholesale | spain soccer jersey 2012 | cheap Spain soccer jersey | uruguay soccer jersey shirt wholesale | croatia euro 2012 jersey | denmark euro 2012 jersey

6:57 PM  
Anonymous Anonymous said...

The past is gone and static. Nothing we can do will change it.scarlet blade gold, the future is before us and dynamic. Everything we do will affect it rs gold, You laugh at mescarlet blade gold for being different , but I laugh at you for being the same.
Life is like a hot bath. It feels good while you're in it, but the longer you stay in, the more wrinkled you get rs gold, Life is too short to wake up in the morning with regrets. So, love the people who treat you right and forget about the ones who do not Runescape Gold, In the Orient young bulls are tested for the fight arena in a certain manner rs gold. Each is brought to the ring and allowed to attack a picador who pricks them with a lance..

1:16 AM  
Blogger oakleyses said...

oakley sunglasses, michael kors outlet, louboutin outlet, prada handbags, air max, burberry outlet, louis vuitton outlet, ray ban sunglasses, tory burch outlet, cheap uggs, michael kors outlet, michael kors outlet, chanel handbags, burberry outlet, oakley sunglasses, kate spade outlet, uggs outlet, oakley sunglasses cheap, prada outlet, longchamp handbags, gucci outlet, air max, louis vuitton, ray ban sunglasses, nike shoes, jordan shoes, nike free, polo ralph lauren, michael kors outlet, polo ralph lauren outlet, ray ban sunglasses, oakley sunglasses, michael kors outlet, christian louboutin, tiffany and co, louboutin, tiffany and co, rolex watches, longchamp outlet, louis vuitton outlet, longchamp handbags, ugg boots clearance, michael kors outlet online sale, uggs, ugg outlet, louis vuitton outlet stores, louboutin shoes, replica watches, louis vuitton handbags

4:35 PM  
Blogger oakleyses said...

vanessa bruno, air max, michael kors uk, coach outlet, michael kors, sac guess, true religion outlet, nike free pas cher, nike free, kate spade handbags, timberland, lululemon outlet online, michael kors outlet, north face, abercrombie and fitch, coach purses, ralph lauren, polo lacoste, oakley pas cher, north face, new balance pas cher, sac hermes, coach outlet store online, converse pas cher, longchamp, nike tn, air max pas cher, hollister, sac burberry, air max, michael kors pas cher, nike air force, hogan outlet, ray ban pas cher, louboutin, polo ralph lauren, true religion jeans, mulberry, sac longchamp pas cher, replica handbags, true religion outlet, nike blazer, vans pas cher, ray ban sunglasses, nike roshe, true religion jeans, nike roshe run pas cher, hollister, nike air max, air jordan

4:38 PM  
Blogger oakleyses said...

louboutin, nike roshe run, north face jackets, vans shoes, asics running shoes, new balance shoes, mcm handbags, iphone 6 cases, reebok outlet, air max, giuseppe zanotti, longchamp, oakley, nike trainers, soccer shoes, hollister, celine handbags, s6 case, insanity workout, baseball bats, wedding dresses, babyliss pro, north face outlet, hollister clothing store, soccer jerseys, p90x, beats by dre, ralph lauren, mac cosmetics, chi flat iron, iphone cases, ferragamo shoes, nike huaraches, valentino shoes, birkin bag, lululemon outlet, ghd, timberland boots, iphone 6s plus cases, abercrombie and fitch, instyler, nfl jerseys, bottega veneta, ipad cases, jimmy choo outlet, iphone 5s cases, mont blanc, herve leger, iphone 6s cases, iphone 6 plus cases

4:40 PM  
Blogger oakleyses said...

converse, pandora charms, toms shoes, barbour, moncler outlet, sac louis vuitton, swarovski, links of london, louis vuitton, barbour, moncler, canada goose, moncler, canada goose jackets, pandora charms, converse shoes, rolex watches, vans, supra shoes, moncler, ugg boots, abercrombie, ugg, marc jacobs, ugg pas cher, ugg, wedding dresses, pandora jewelry, sac lancel, air max, louis vuitton, sac louis vuitton, coach outlet store online, juicy couture, hollister, ugg, canada goose, canada goose, moncler, juicy couture outlet, thomas sabo, moncler, ray ban, louis vuitton uk, montre homme, moncler, gucci, canada goose outlet, pandora jewelry, canada goose, canada goose uk

4:44 PM  
Blogger dong dong23 said...

washington wizards jerseys
louis vuitton handbags
chicago bulls jerseys
rolex watches
polo ralph lauren
nike roshe run
ugg boots
ralph lauren outlet
toms shoes
coach outlet online

7:56 PM  
Blogger aaa kitty20101122 said...

true religion jeans
longchamp le pliage
hermes belt
pandora bracelet
prada glasses
lacoste outlet
chrome hearts online store
pandora charms
falcons jersey

12:13 AM  
Blogger Clipping path Help Zone said...


1:21 AM  
Blogger Clipping path Help Zone said...


1:23 AM  
Blogger Clipping path Help Zone said...


1:24 AM  

Post a Comment

<< Home