Here is the patch for the "distinct in subquery" bug; ran derbylang
with 0 failures.
I have also added a test case to an existing test (lang/distinct.sql).
There seem to be multiple copies of distinct.out in the source tree--
do I need to change them all?
Thanks,
Manish
On 7/15/05, Satheesh Bandaram <[EMAIL PROTECTED]> wrote:
> Great... I haven't tried the fix, but if you have a minute, would you be
> willing to submit a patch for this problem? You seem to have already done
> most of the work here... It seems running derbyAll suite and making a patch
> to be submitted through Jira or by mail to DerbyDev might be the next steps.
> See this link for how to submit a patch:
> (http://incubator.apache.org/derby/derby_comm.html#Contribute+Code+or+Documentation)
>
> Satheesh
>
> Manish Khettry wrote:
> 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
>
>
>
>
>
Index: java/engine/org/apache/derby/impl/sql/execute/SortResultSet.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/execute/SortResultSet.java
(revision 219729)
+++ java/engine/org/apache/derby/impl/sql/execute/SortResultSet.java
(working copy)
@@ -230,6 +230,7 @@
*/
public void openCore() throws StandardException
{
+ nextCalled = false;
beginTime = getCurrentTimeMillis();
// REVISIT: through the direct DB API, this needs to be an
// error, not an ASSERT; users can open twice. Only through JDBC
Index:
java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinct.sql
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinct.sql
(revision 219729)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/distinct.sql
(working copy)
@@ -329,5 +329,16 @@
execute c1 using 'values(''lusername1'', ''lusername2'', ''lname1'')';
rollback;
+create table td (x int);
+insert into td values (1);
+insert into td values (1);
+insert into td values (2);
+
+-- distinct in subquery where the store does not perform the sort.
+select * from td, (select distinct 1 from td) as sub(x);
+
+-- get the storage system to do the sort.
+select * from td, (select distinct x from td) as sub(x);
+
-- reset autocomiit
autocommit on;
Index: java/testing/org/apache/derbyTesting/functionTests/master/distinct.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/distinct.out
(revision 219729)
+++ java/testing/org/apache/derbyTesting/functionTests/master/distinct.out
(working copy)
@@ -2477,6 +2477,31 @@
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
name1
|sum2
ij> rollback;
+ij> create table td (x int);
+0 rows inserted/updated/deleted
+ij> insert into td values (1);
+1 row inserted/updated/deleted
+ij> insert into td values (1);
+1 row inserted/updated/deleted
+ij> insert into td values (2);
+1 row inserted/updated/deleted
+ij> -- distinct in subquery where the store does not perform the sort.
+select * from td, (select distinct 1 from td) as sub(x);
+X |X
+-----------------------
+1 |1
+1 |1
+2 |1
+ij> -- get the storage system to do the sort.
+select * from td, (select distinct x from td) as sub(x);
+X |X
+-----------------------
+1 |2
+1 |1
+1 |2
+1 |1
+2 |2
+2 |1
ij> -- reset autocomiit
autocommit on;
ij>
