Обсуждение: BUG #4019: Comparison of user defined domain doesn't work

The following bug has been logged online:

Bug reference:      4019
Logged by:          Jan Strube
Email address:      js@deriva.de
PostgreSQL version: 8.3
Operating system:   openSUSE Linux 10.2
Description:        Comparison of user defined domain doesn't work
Details:

Hi,

I think when cascading a DELETE to a dependent table Postgres doesn't
compare the whole related fields when they are of an user defined DOMAIN
type.

I have two tables "z_base" and "z_ul". "z_base"'s primary key is of type
"isin" which is referenced by "z_ul". "isin" is a DOMAIN of type
CHARACTER(12). When I delete a record from "z_base", all records of "z_ul"
whose "isin" has the same first character are also deleted. On Postgres
8.2.6 it's working as intended but not on 8.3.

Here is a test case:

create schema test;
create domain test.isin as character(12);
create table test.z_base (id test.isin primary key);
create table test.z_ul (id test.isin references test.z_base(id) on update
cascade on delete cascade);

insert into test.z_base values ('a111');
insert into test.z_base values ('a222');
insert into test.z_base values ('x333');
insert into test.z_ul values ('a111');
insert into test.z_ul values ('a222');
insert into test.z_ul values ('x333');

select * from test.z_base join test.z_ul using (id) where z_base.id =
'a222';
-- gives a222, ok

delete from test.z_base where id = 'a222';

select * from test.z_ul;
-- on PG 8.2.6. gives ('a111', 'x333'), ok
-- on PG 8.3 gives just 'x333', so 'a111' has also been deleted!!!

Regard,
Jan Strube

"Jan Strube" <js@deriva.de> writes:
> Description:        Comparison of user defined domain doesn't work

I can't reproduce this here --- the test case gives the expected output
on both 8.2 and 8.3.  (I'm checking CVS tip not the 8.3.0 release, but
there surely haven't been any bug fixes that seem likely to affect
this.)

Is it possible that you need a particular locale/encoding, or some other
non-default setting, to reproduce the problem?

            regards, tom lane

On Fri, 7 Mar 2008, Tom Lane wrote:

> "Jan Strube" <js@deriva.de> writes:
> > Description:        Comparison of user defined domain doesn't work
>
> I can't reproduce this here --- the test case gives the expected output
> on both 8.2 and 8.3.  (I'm checking CVS tip not the 8.3.0 release, but
> there surely haven't been any bug fixes that seem likely to affect
> this.)

Actually, I think it was fixed by your change in revision 1.103 which
appears to be newer than 8.3.0.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Fri, 7 Mar 2008, Tom Lane wrote:
>> "Jan Strube" <js@deriva.de> writes:
>>>> Description:        Comparison of user defined domain doesn't work
>>
>> I can't reproduce this here --- the test case gives the expected output
>> on both 8.2 and 8.3.  (I'm checking CVS tip not the 8.3.0 release, but
>> there surely haven't been any bug fixes that seem likely to affect
>> this.)

> Actually, I think it was fixed by your change in revision 1.103 which
> appears to be newer than 8.3.0.

Ohhh ... yeah, you're right, because the type it'd be using would be the
declared input type of the comparison operator, so bpchar not the domain.

So: closed, already fixed for 8.3.1.

            regards, tom lane