db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Manish Khettry <manish.khet...@gmail.com>
Subject Re: Potential bug when using distinct in subselect
Date Fri, 15 Jul 2005 22:06:28 GMT
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

Mime
View raw message