Обсуждение: WHERE =NULL malfunction in release 7.2

Hi,

first of all let me tell all of you that PostgreSQL is among the best
in OpenSource!

Unfortunaly there's a (hopefully easy to fix) bug in release 7.2 and
7.2.1 that was not there in 7.1.3 and caused me to switch back today.


Here's the table I'm using (unfortunately I already switched back to
7.1.3 and therefore can't verify whether or not this occurs with an
easier setup):

i++=# \d uri
                      Table "uri"
     Column    |           Type           | Modifiers
--------------+--------------------------+-----------
  template_id  | integer                  |
  content_id   | integer                  | not null
  freigegeben  | timestamp with time zone |
  gueltig_von  | timestamp with time zone |
  gueltig_bis  | timestamp with time zone |
  queue_status | integer                  |
  site_id      | integer                  |
  uri          | text                     |
Primary key: uri_pkey
Triggers: RI_ConstraintTrigger_17236,
           RI_ConstraintTrigger_17242,
           RI_ConstraintTrigger_17255,
           RI_ConstraintTrigger_17257


And here we add sam sample data:


i++=# INSERT INTO uri VALUES (3,3,NULL);
INSERT 17604 1


Which is in the database:


i++=# select * from uri;
  template_id | content_id |          freigegeben          |
gueltig_von          | gueltig_bis | queue_status | site_id |     uri

-------------+------------+-------------------------------+-------------------------------+-------------+--------------+---------+-------------
            3 |          3 |                               |
|             |              |         |
(1 rows)



However - if you want to access a <timestamp>=NULL value, it is not found :-((



i++=# select * from uri where freigegeben=NULL;
  template_id | content_id | freigegeben | gueltig_von | gueltig_bis |
queue_status | site_id | uri
-------------+------------+-------------+-------------+-------------+--------------+---------+-----
(0 rows)



Hope to hear from you in a while - please reply directly to
mailto:ciechowski@cis-computer.com, since I'm not on this list.


Ingo

--

-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/B/O d+(-) s+:++ a C++ UL++++$ P++$ L+++@ E- W+++@ N+ o? K? w-- O
M++@ V PS+ PE++ Y+ PGP++ t++ 5 X R tv+ b+@ DI+@ D--- G+ e? h-- r+++@ y?
------END GEEK CODE BLOCK------

On Thu, Jun 20, 2002 at 17:28:31 +0200,
  Ingo Ciechowski <ciechowski@cis-computer.com> wrote:
> Hi,
>
> first of all let me tell all of you that PostgreSQL is among the best
> in OpenSource!
>
> Unfortunaly there's a (hopefully easy to fix) bug in release 7.2 and
> 7.2.1 that was not there in 7.1.3 and caused me to switch back today.
>
>
> Here's the table I'm using (unfortunately I already switched back to
> 7.1.3 and therefore can't verify whether or not this occurs with an
> easier setup):

7.1 had a hack because = NULL worked that way in Microsoft's DB.
You want to use 'IS NULL' to match null values. '= NULL' will always
be 'unknown' (which isn't 'true' so the where clause won't be satisified).

On Thu, 20 Jun 2002, Ingo Ciechowski wrote:

> Unfortunaly there's a (hopefully easy to fix) bug in release 7.2 and
> 7.2.1 that was not there in 7.1.3 and caused me to switch back today.

Nope. It's a bug fix.

> However - if you want to access a <timestamp>=NULL value, it is not found :-((
>
>
>
> i++=# select * from uri where freigegeben=NULL;
>   template_id | content_id | freigegeben | gueltig_von | gueltig_bis |
> queue_status | site_id | uri
> -------------+------------+-------------+-------------+-------------+--------------+---------+-----
> (0 rows)

Which is correct because NULL does not equal NULL.  Think of NULL as
an unknown value, you don't know if two NULLs are equal.

In 7.1, there was a parser hack to allow this due to some older clients
that expected it to work.  In 7.2, you can turn this on with a variable
(TRANSFORM_NULL_EQUALS), however this behavior is contrary to the
SQL spec.

Ingo Ciechowski <ciechowski@cis-computer.com> writes:
> i++=# select * from uri where freigegeben=NULL;

This is not a bug; the 7.1.* behavior is the bug.

But see transform_null_equals if your application logic depends on
the old non-spec-compliant behavior.

            regards, tom lane