Stephan,
How do IN and NOT IN treat NULLs? Don't these functions search an array
for a specified value returning true or false? I guess the intuitive
thing for IN and NOT IN to do would be to return NULL if NULL appears
anywhere in the array since those elements values are "unknown".
Personally I think treating NULL as "unknown" is rediculous. NULL is a
value and its value is known to be NULL. I know what NULL is: NULL. How
many NULLs do I have here {NULL, 'hi', NULL, NULL}? NULL NULLs? No,
three NULLs. How many NULLs are in this string: 'hi'? NULL NULLs? No,
zero NULLs. How many occurances of 'yo' are in 'hey'? NULL occurances?
No, zero occurances. How many NULLs are in this paragraph? You better
count them yourself because if you ask SQL you know what it will say.
NULL. Or will it tell you? Who the hell knows!?
Oh yeah, my favorite is this: NULL::VARCHAR? Nope, you can't do it. Not
without creating your own CAST. Seems to me that an obvious value would
be 'NULL'. Or maybe '' (empty string). I hate having to use COALESCE.
It just reaks of a bad programming practice.
Thats the way I see it. But if NULL has to mean "unknown" then all the
functions should treat it as such. Also several other values including
UNSPECIFIED and EMPTY should be provided. EMPTY should return an empty
array {} and UNSPECIFIED should do what NULL is often used to mean
(nothing). Then NULL should be renamed to UNKNOWN to clear up any
confusion. :o)
But I'm not really the ruler of the world. At least not yet. But maybe
someday...
-Robert