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

The following bug has been logged on the website:

Bug reference:      6644
Logged by:          Karl Walbrecht
Email address:      kwalbrecht@cghtech.com
PostgreSQL version: 9.0.3
Operating system:   SunOS 5.10
Description:=20=20=20=20=20=20=20=20

geom.ArcToStreight(integer) is a function which returns the following type:

Composite type "geom.t_line_ref"
  Column   |       Type=20=20=20=20=20=20=20=20
-----------+-------------------
 line_id   | integer
 start_id  | integer
 mid_id    | integer
 end_id    | integer
 line      | geometry
 line_type | character varying
 chart_id  | integer

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.


SELECT count(*)
FROM (
  select geom.ArcToStreight(line_id) as a
  from sdat_legacy.lines
  where mid_node is not null
) foo=20
;

 count
-------
 14177
(1 row)

SELECT count(*)
FROM (
  select geom.ArcToStreight(line_id) as a
  from sdat_legacy.lines
  where mid_node is not null
) foo
WHERE foo.a is null=20
;

 count
-------
 13796
(1 row)


SELECT count(*)
FROM (
  select geom.ArcToStreight(line_id) as a
  from sdat_legacy.lines
  where mid_node is not null
) foo
WHERE foo.a is not null=20
;

 count
-------
     0
(1 row)

Clearly something is amiss.

  14197  Total number of rows
- 13796  Number of null row
--------
    381 Expected number of not null rows

      0 Number of not null rows returned

kwalbrecht@cghtech.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