Re: BUG #6644: Query give wrong results when 'is not null' is applied in where clause

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: kwalbrecht(at)cghtech(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6644: Query give wrong results when 'is not null' is applied in where clause
Date: 2012-05-17 00:04:18
Message-ID: 7501.1337213058@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

kwalbrecht(at)cghtech(dot)com writes:
> geom.ArcToStreight(integer) is a function which returns the following type:
> The following queries show 1) the total number of rows. 2) the number of
> null rows. 3) the number of not null rows.
> The problem is that results of query 2 + the results of query 3 should equal
> the result of query 1 which they do not.

Well, you didn't provide enough detail to let somebody else reproduce
the problem, but I suspect your issue is that IS NULL and IS NOT NULL
are not all the possible states of a composite value. Per
http://www.postgresql.org/docs/9.0/static/functions-comparison.html

Note: If the expression is row-valued, then IS NULL is true when
the row expression itself is null or when all the row's fields
are null, while IS NOT NULL is true when the row expression
itself is non-null and all the row's fields are
non-null. Because of this behavior, IS NULL and IS NOT NULL do
not always return inverse results for row-valued expressions,
i.e., a row-valued expression that contains both NULL and
non-null values will return false for both tests. This
definition conforms to the SQL standard, and is a change from
the inconsistent behavior exhibited by PostgreSQL versions prior
to 8.2.

regards, tom lane

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tulio J Carrasco 2012-05-17 00:55:49 Re: BUG #6642: Spanish collation fault
Previous Message Alvaro Herrera 2012-05-16 19:20:44 Re: BUG #6642: Spanish collation fault