db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendle...@amberpoint.com>
Subject Possible wrong results bug involving column order in UNION view
Date Wed, 04 Apr 2007 22:15:16 GMT
I'm confused by the behavior of the small SQL script included
below. I think both select statements should return 1 row, but
in fact the first statement returns 1 row and the second returns
zero rows.

The only difference between the two statements is that the
columns in the UNION view are listed in a different order (bvw vs. bvw2).

This seems like a bug to me; the order of the columns in the view
definition shouldn't matter, should it?

Can somebody try this and confirm my results?

Does anyone have a theory about what might be happening?

thanks,

bryan

drop view bvw;
drop view bvw2;

drop table b;
drop table b2;
drop table b3;
drop table b4;

create table b2 (c1 int, c2 int, c3 char(1), c4 int, c5 int, c6 int);
create table b4 (c7 int, c4 int, c6 int);
create table b3 (c8 int, c9 int, c5 int, c6 int);
create table b (c1 int, c2 int, c3 char(1), c4 int, c5 int, c6 int);

create view bvw (c5, c1 ,c2 ,c3 ,c4) as
           select c5, c1 ,c2 ,c3 ,c4 from b2 union
           select c5, c1 ,c2 ,c3 ,c4 from b;

create view bvw2 (c1 ,c2 ,c3 ,c4 ,c5) as
            select c1 ,c2 ,c3 ,c4 ,c5 from b2 union
            select c1 ,c2 ,c3 ,c4 ,c5 from b;

insert into b4 (c7,c4,c6) values (4, 42, 31);
insert into b2 (c5,c1,c3,c4,c6) values (3,4, 'F',43,23);
insert into b3 (c5,c8,c9,c6) values (2,3,19,28);

select b3.* from b3 join bvw on (b3.c8 = bvw.c5) join b4 on (bvw.c1 = b4.c7) where b4.c4 =
42;
select b3.* from b3 join bvw2 on (b3.c8 = bvw2.c5) join b4 on (bvw2.c1 = b4.c7) where b4.c4
= 42;


Mime
View raw message