From: | "Joe Conway" <joseph(dot)conway(at)home(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards |
Date: | 2001-06-18 18:04:49 |
Message-ID: | 00fd01c0f821$2b076cd0$48d210ac@jecw2k1 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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
Attachment | Content-Type | Size |
---|---|---|
nulltest01.diff | application/octet-stream | 11.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-06-18 18:07:38 | Re: LEFT JOIN ... |
Previous Message | Bruce Momjian | 2001-06-18 18:04:32 | Re: timestamp with/without time zone |