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 <[EMAIL PROTECTED]> 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
