I tried the following repro... Seems like a distinct implementation
bug in Derby to me. Even if I can the predicate to 'a.ival1 >0' or
to even 'a.ival1 = a.ival1' (both no-op predicates) having them changes
the results of the query. Without the predicate or distinct clause the
query seems to work right.
I think this is a bug.
Satheesh
Andrew Czapran wrote:
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
|
|
|
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
|
