Re: 8.0.3 regexp_replace()...

On Wed, 1 Feb 2006, rlee0001 wrote:

> 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".

It's IN and NOT IN (subselect) that people often get confused by, exactly
because it does return NULL which means that a row not selected by IN may
also not be selected by NOT IN.

> 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.

The problem is that NULL isn't a known string, numeric, time, etc value.
Until you define semantics for it, you don't really have a value. Those
semantics could be alot simpler than the SQL ones however.

> 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.

I don't see how using COALESCE is particularly worse than using CAST,
honestly. CAST(NULL AS VARCHAR(n)) versus COALESCE(NULL, <what you want
out>) seems pretty much a wash, unless you want it to happen implicitly.

> 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)

Well, yes, keeping the separate uses of NULL separate would have been
nice.