Comments (9)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 DomusOnline commented Permalink

One thing I think you did not cover and that I keep facing in Informix customer environments is the result of a concatenation with NULL:

 
SELECT 'PREFIX' || some_column
 
Returns NULL if some_column IS NULL. Most people expect it to return 'PREFIX'
 
Regards.

2 SergeRielau commented Permalink

DomusOnline,

 
Done. I have added a bullet.
 
Cheers
Serge

3 LennartJonsson commented Permalink

I think you covered it all very well. It is interesting to see how things we take for granted no longer hold when introducing nulls. One favourite of mine is that:

 
sum(a) + sum(b) <> sum(a+b)

4 SergeRielau commented Permalink

Lennart,

 
Thank you - and good point! I never considered looking at NULLs through that lens before.
 
Cheers
Serge
 

5 StefanMihokovic commented Permalink

Hi Serge,

 
In this context a question?
Why does the "DISTINCT predicate" not exist on DB2 for LUW as is does on DB2 for z/OS.
 
Reference: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_distinctpredicate.htm
 
 
Regards / Grüße Stefan

6 SergeRielau commented Permalink

Stefan,

 
DISTINCT was introduced I believe in DB2 9 for z.
DB2 for LUW simply hasn't gotten around to adding it yet.
A question of priorities.
 
Cheers
Serge

7 oschoett commented Permalink

Is there a construct to express eqality including the case "NULL equals NULL"? This is usually written as

 
a = b OR (a IS NULL AND b IS NULL)
 
which tends to make the query slow. For example, HASH JOIN is not applicable to this construction, and we found that the expression
 
COALESCE(a, '') = COALESCE(b, '')
 
does not cause HASH JOIN to be used either, although it should be possible to hash the result of the COALESCE function.
 
Thus, I see a need for a predicate that allows hash join to be used and that includes the case "NULL equals NULL".

8 Doug.Partch commented Permalink

On DB2 z/OS for "Nulls = Nulls" we do have the following statement.
WHERE COL1 IS DISTINCT FROM COL2

 
It performance the same as
WHERE COL1 = COL2
OR (COL1 IS NULL and COL2 IS NULL)
 
However DB2 LUW does not support the above phrase yet.

9 RickSwagerman commented Permalink

Doug,
If you want "Nulls = Nulls" to be TRUE, then you would need to use NOT DISTINCT.
The DISTINCT predicate will return FALSE when the values of COL1 and COL2 are equal with the special condition that nulls are considered equal.

 
When you said "perform the same as" I don't think you meant to be writing about the performance pf the DISTINCT predicate but rather describe the logical equivalent using other predicates. Did I read you correctly?
 
The functional equivalent for WHERE COL1 IS NOT DISTINCT FROM COL2 would be:
WHERE (COL1 IS NOT NULL
AND COL2 IS NOT NULL
AND COL1 = COL2)
OR
(COL1 IS NULL
AND COL2 IS NULL)
 
Note that this predicate is an exception to Serge's statement that "comparisons also return NULL (aka UNKNOWN) if any of the arguments are NULL". The DISTINCT predicate makes a special case of handling arguments that are NULL and therefore would never return NULL.
It is still true that DB2 for LUW does not support the DISTINCT predicate as yet.
...Rick