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.

12 Comments:

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
WHERE 1=2

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  
Blogger GuildWars2Items 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  

Post a Comment

Links to this post:

Create a Link

<< Home