Re: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards - Mailing list pgsql-hackers

From Tom Lane
Subject Re: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards
Date
Msg-id 23000.992965257@sss.pgh.pa.us
Whole thread Raw
In response to Re: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards  ("Joe Conway" <joseph.conway@home.com>)
List pgsql-hackers
"Joe Conway" <joseph.conway@home.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        falsefalse        false        true        falseunknown        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


pgsql-hackers by date:

Previous
From: Alex Pilosov
Date:
Subject: Re: PlPerl compile failure
Next
From: Pedro Abelleira Seco
Date:
Subject: Universal admin frontend