db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: Potential bug when using distinct in subselect
Date Sat, 16 Jul 2005 02:43:35 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<body bgcolor="#ffffff" text="#000000">
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:
(<a class="moz-txt-link-freetext" href="http://incubator.apache.org/derby/derby_comm.html#Contribute+Code+or+Documentation">http://incubator.apache.org/derby/derby_comm.html#Contribute+Code+or+Documentation</a>)<br>
Manish Khettry wrote:
<blockquote cite="midee56416050715150619bc0203@mail.gmail.com"
  <pre wrap="">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.


On 7/14/05, Satheesh Bandaram <a class="moz-txt-link-rfc2396E" href="mailto:satheesh@sourcery.org">&lt;satheesh@sourcery.org&gt;</a>
  <blockquote type="cite">
    <pre wrap=""> I tried the following repro... Seems like a distinct implementation
bug in
Derby to me. Even if I can the predicate to 'a.ival1 &gt;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
 I think this is a bug.

 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. 
 --- 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
   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&gt; run 'test1.txt';
 cloudscape output for last select:
 ij&gt; select a.ival1, a.cval1, c.ival
 from t1 a,
 (select distinct 1 from t3) as c(ival)
 where a.ival1 &gt;=100
 100 |row 100 from t1 |1
 1 row selected
 --- DB2 output:
 select a.ival1, a.cval1, c.ival from t1 a, (select distinct 1 from t3) as
 ) where a.ival1 &gt;=100
 ----------- ------------------------------ -----------
 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 &gt;=100
 Any takers. 
 --- note ends here... 
 Andrew Czapran
 WebSphere Business Integration
  <pre wrap=""><!---->


View raw message