BUG #5655: Composite Type Handles Null Incorrectly

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;

Responses

Browse pgsql-bugs by date

  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