Re: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards

"Joe Conway" <joseph(dot)conway(at)home(dot)com> writes:
> -- Should I increment catversion.h as part of the patch (I didn't in this
> patch), or is that usually centrally controlled by Bruce (or whomever
> commits the change)?

It's good to put the catversion bump into the patch, else the committer
might forget to do it.

> -- IMHO, if we are going to keep the (a = null) to (a is null) conversion,
> then there should also be a similar conversion from (a != null) to (a is not
> null). Otherwise the two operations which may be expected to be
> complimentary (as evidenced by at least one recent post) are not.

I'd resist this. The only reason the =NULL hack is in there at all is
to support Access97. We shouldn't extend the deviation from standards
further than the minimum needed to do that. The hack is fundamentally
inconsistent anyway, and breaking our standards compliance further in
pursuit of bogus consistency seems misguided.

Personally I'd rather take out the =NULL conversion anyway...

> -- If I have interpreted SQL92 correctly UNKNOWN IS TRUE should return
> FALSE, and UNKNOWN IS NOT TRUE is equivalent to NOT (UNKNOWN IS TRUE) ==>
> TRUE. Is this correct?

Yes. Table 15 is pretty illegible in the ASCII draft copies of SQL92
and SQL99, but the PDF version of SQL99 is okay, and it makes clear
what you'd expect:

input IS TRUE IS FALSE IS UNKNOWN

true true false false
false false true false
unknown false false true

and then the NOT variants are defined as

x IS NOT foo == NOT (x IS foo)

I'll try to look over and commit the patch later today.

For extra credit ;-) ... if you'd like to learn a little bit about the
optimizer, think about teaching clause_selectivity() in
optimizer/path/clausesel.c how to estimate the selectivity of these new
expression nodes. In the case where the argument is a boolean column
that we have statistics for, it should be possible to derive the correct
answer (including accounting for NULLs). If the argument is more
complex than that, you probably can't do anything really intelligent,
but you could handwave away NULLs and then compute the appropriate
function of the clause_selectivity() of the argument.

regards, tom lane