From: | "Nate Carson" <nate1001(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5655: Composite Type Handles Null Incorrectly |
Date: | 2010-09-14 00:17:18 |
Message-ID: | 201009140017.o8E0HIUA034470@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Howard | 2010-09-14 04:42:26 | Re: BUG #5654: Deferred Constraints don't work |
Previous Message | Tom Lane | 2010-09-13 17:51:35 | Re: 9.0 Bug: cannot build against python3.1, with two versions of python in the environment |