db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrew Czapran <cv3...@ca.ibm.com>
Subject Potential bug when using distinct in subselect
Date Thu, 14 Jul 2005 13:28:06 GMT
Hello derby developers:

I posted a query on the "IBM Cloudscape" forum and was refered to this id 
by others.

Basically, I have a small testcase that shows different results when run 
under cloudscape and IBM DB2.
We think this is a Cloudscape issue, but we could be wrong.

The testcase is a simplified version of our real problem.
Basically we have a complicated subselect that returns a 1 row, 1 column 
value that we want included in every
row of our outer select.   The complicated scenario failed, so we tried to 
narrow it down as shown by the attached test case.

I have attached my original append to the forum and testcase.
Your review of this issue would be greatly appreciated.
Thanks.

--- note start here.....







Why does keyword distinct in subselect cause wrong results (when compared 
to db2 results)
Originally posted: 2005 July 13 12:22 PM


andrewc 




 
Post new reply 

There is a case where we need a constant table lookup value returned in a 
specific column of another select.
Thus we are writing a subselect. Yes, the column should be the same value 
for all the rows. Yes, we know its not pretty.

I have narrowed the problem down to the following testcase.
On db2 we get 3 rows returned. On cloudscape we get 1 row
returned. What do you think the correct result should be?
Someone is wrong. Is this a bug, and then with which product?

run from the "ij" environment of cloudscape 10.0 downloaded from the web 
on Friday July 8, 2005.
ij> run 'test1.txt';

cloudscape output for last select:
ij> select a.ival1, a.cval1, c.ival
from t1 a,
(select distinct 1 from t3) as c(ival)
where a.ival1 >=100
;
IVAL1 |CVAL1 |IVAL
------------------------------------------------------
100 |row 100 from t1 |1

1 row selected
ij>

--- DB2 output:
select a.ival1, a.cval1, c.ival from t1 a, (select distinct 1 from t3) as 
c(ival
) where a.ival1 >=100

IVAL1 CVAL1 IVAL
----------- ------------------------------ -----------
100 row 100 from t1 1
200 row 200 from t1 1
300 row 300 from t1 1

3 record(s) selected.


----------------------
-- test script follows:
-----------------------
-- build first table
drop table t1;
create table t1 (
ival1 integer,
cval1 char(30)
);

-- build another table
drop table t2;
create table t2 (
ival1 integer,
cval1 char(30)
);

-- build another table
drop table t3;
create table t3 (
ival1 integer,
cval1 char(30)
);

-- insert into table t1
insert into t1 values(
100, 'row 100 from t1' );

insert into t1 values(
200, 'row 200 from t1' );

insert into t1 values(
300, 'row 300 from t1' );

-- insert into table t2
insert into t2 values(
100, 'row 100 from t2' );
insert into t2 values(
200, 'row 200 from t2' );
insert into t2 values(
300, 'row 300 from t2' );

-- insert into table t3
insert into t3 values(
300, 'row 300 from t3' );

select * from t1;
select * from t2;

select a.ival1, a.cval1, c.ival
from t1 a,
(select distinct 1 from t3) as c(ival)
where a.ival1 >=100
;




Any takers.




--- note ends here...

Andrew Czapran
WebSphere Business Integration
905-413-2843
Mime
View raw message