I was playing around with this bug. I think the problem is that the
instance variable nextCalled in "impl/sql/execute/SortResultSet"
should be reset to false everytime openCore is called.
Thanks,
Manish
On 7/14/05, Satheesh Bandaram <satheesh@sourcery.org> wrote:
> 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
>
>
>
> 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
|