Re: BUG #5655: Composite Type Handles Null Incorrectly

The following bug has been logged online:

Bug reference: 5655
Logged by: Nate Carson
Email address: nate1001(at)gmail(dot)com
PostgreSQL version: 8.4.4
Operating system: linux 2.6.33-sabayon (gentoo)
Description: Composite Type Handles Null Incorrectly
Details:

I have been using a composite type to handle the different fields of name
i.e. last name, first name, etc. This has been a good solution for handling
names that come from different formats while checking for duplicates.
However, I have found behavior that I do not believe is correct. Selecting
with a not null condition always returns 0 rows with null values for the
type, but querying 'is not null' in a column expression produces expected
results. I can coerce expected behavior by sub-querying 'is not null' on the
type in the inner query and select from the boolean condition in the outer
query.

Below is a script to reproduce behavior.

-- Composite Type Handles Null Incorrectly

drop type if exists t_person_test cascade;
create type t_
person_test as (
fname text,
finit char(1),
mname text,
minit char(1),
lname text,
suffix text
);

drop table if exists test;
create table test ( p t_person_test);
insert into test values
(('Charles','C',null,null,'Dickens',null)::t_person_test),
(null)
;

select p, p is null as pnull from test;

select * from test where p is null;

select * from (select p, p is null as pnull from test) as t where t.pnull =
false;
select * from (select p, p is null as pnull from test) as t where t.pnull =
true;

\echo 'This puts out 0 rows? Should output 1.'
select * from test where p is not null;

"Nate Carson" <nate1001(at)gmail(dot)com> writes:
> Description: Composite Type Handles Null Incorrectly

So far as I can see, this script just shows that is null/is not null
on a composite value behave as specified in the manual:

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.

http://www.postgresql.org/docs/8.4/static/functions-comparison.html

regards, tom lane