Re: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards
От | Joe Conway |
---|---|
Тема | Re: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards |
Дата | |
Msg-id | 00fd01c0f821$2b076cd0$48d210ac@jecw2k1 обсуждение исходный текст |
Ответ на | AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards (Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>) |
Ответы |
Re: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards
Re: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards |
Список | 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
В списке pgsql-hackers по дате отправления: