The following bug has been logged online:
Bug reference: 5655
Logged by: Nate Carson
Email address: nate1...@gmail.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;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
