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

> IS [NOT] NULL is handled a little differently: gram.y generates a
> specialized Expr node, which parse_expr.c translates to a function call
> on the specialized functions nullvalue() and nonnullvalue()
> respectively. I don't much care for this implementation either, again
> partly because ruleutils.c has to be uglified to deal with it, but
> partly because the optimizer can't cheaply recognize IS NULL tests
> either.
>
> I'd like to see all eight of these guys translated into a specialized
> kind of expression node, called perhaps BooleanTest. Actually, it'd
> probably be wise to keep IS NULL separate from the six boolean tests,
> with an eye to the future when it will need to support nonscalar
> arguments. So maybe BooleanTest and NullTest node types, each with a
> field showing exactly which test is wanted.
>

Attached is a patch for a new NullTest node type for review and comment.
Since it didn't seem like there was consensus regarding removal of the "a =
null" conversion to "a is null" behavior, I left it in. It is worth
mentioning, however, that neither Oracle 8.1.6 or MSSQL 7 seem to support
this -- see below:

Oracle:
****************************************
SQL> select f1,f2 from foo where f2 = null;

no rows selected

MSSQL 7
****************************************
select f1,f2 from foo where f2 = null
f1 f2
----------- --------------------------------------------------

(0 row(s) affected)

PostgreSQL
****************************************
test=# select f1,f2 from foo where f2 = null;
f1 | f2
----+----
1 |
4 |
(2 rows)

In all 3 cases table foo has 4 rows, 2 of which have null values for f2.
Based on this, should support for the converting "a = null" to "a is null"
be dropped?

I also noticed that in PostgreSQL I can do the following (both before and
after this patch):
select f2 is null from foo;
whereas in both Oracle and MSSQL it causes a syntax error. Any thoughts on
this?

Thanks,

-- Joe