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 Fri, 15 Jul 2005 01:12:58 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
I tried the following repro... Seems like a <b>distinct </b>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 right.<br>
<br>
I think this is a bug.<br>
<br>
Satheesh<br>
<br>
Andrew Czapran wrote:<br>
<blockquote
 cite="midOF043F2772.2729F322-ON8525703E.00497A06-8525703E.0049F78F@ca.ibm.com"
 type="cite"><br>
  <font face="sans-serif" size="2">Hello derby developers:</font>
  <br>
  <br>
  <font face="sans-serif" size="2">I posted a query on the "IBM
Cloudscape"
forum and was refered to this id by others.</font>
  <br>
  <br>
  <font face="sans-serif" size="2">Basically, I have a small testcase
that
shows different results when run under cloudscape and IBM DB2.</font>
  <br>
  <font face="sans-serif" size="2">We think this is a Cloudscape issue,
but we could be wrong.</font>
  <br>
  <br>
  <font face="sans-serif" size="2">The testcase is a simplified version
of our real problem.</font>
  <br>
  <font face="sans-serif" size="2">Basically we have a complicated
subselect
that returns a 1 row, 1 column value that we want included in every</font>
  <br>
  <font face="sans-serif" size="2">row of our outer select. &nbsp; The
complicated scenario failed, so we tried to narrow it down as shown by
the attached test case.</font>
  <br>
  <br>
  <font face="sans-serif" size="2">I have attached my original append
to
the forum and testcase.</font>
  <br>
  <font face="sans-serif" size="2">Your review of this issue would be
greatly
appreciated.</font>
  <br>
  <font face="sans-serif" size="2">Thanks.</font>
  <br>
  <br>
  <font face="sans-serif" size="2">--- note start here.....</font>
  <br>
  <table width="100%">
    <tbody>
      <tr valign="top">
        <td bgcolor="white" width="100%"><font size="2"><br>
        </font>
        <table width="100%">
          <tbody>
            <tr valign="top">
              <td bgcolor="white" width="100%"><font size="2"><br>
              </font>
              <table width="100%">
                <tbody>
                  <tr>
                    <td bgcolor="white" width="100%"><font size="2"><br>
                    </font>
                    <table width="100%">
                      <tbody>
                        <tr valign="top">
                          <td bgcolor="white" width="28%"><font
 face="Verdana" size="3"><b>Why does keyword
distinct in subselect cause wrong results (when compared to db2 results)</b><br>
Originally posted: 2005 July 13 12:22 PM<br>
                          </font><img
 src="cid:part1.05040102.02060504@Sourcery.Org">
                          </td>
                          <td bgcolor="white" width="11%"><br>
                          </td>
                          <td bgcolor="white" width="13%"><font
 face="Verdana" size="3">andrewc </font>
                          </td>
                          <td bgcolor="white" width="11%"><br>
                          </td>
                          <td bgcolor="white" width="34%"><font size="2"><br>
                          </font>
                          <table width="100%">
                            <tbody>
                              <tr valign="top">
                                <td bgcolor="white" width="33%">
                                <div align="right"><img
 src="cid:part2.06090301.06080906@Sourcery.Org"></div>
                                </td>
                                <td bgcolor="white" width="33%"><font
 face="Verdana" size="3">&nbsp;</font>
                                </td>
                                <td bgcolor="white" width="33%"><a
 href="http://www-128.ibm.com/developerworks/forums/dw_post.jsp?forum=370&amp;thread=86591&amp;reply=true&amp;cat=19"><font
 color="#5c81a7" face="Verdana" size="3">Post
new reply</font></a><font face="Verdana" size="3"> </font></td>
                              </tr>
                            </tbody>
                          </table>
                          <br>
                          </td>
                        </tr>
                        <tr valign="top">
                          <td colspan="5" bgcolor="white"><font
 face="Verdana" size="3">There is a case
where we need a constant table lookup value returned in a specific
column
of another select.<br>
Thus we are writing a subselect. Yes, the column should be the same
value
for all the rows. Yes, we know its not pretty.<br>
                          <br>
I have narrowed the problem down to the following testcase.<br>
On db2 we get 3 rows returned. On cloudscape we get 1 row<br>
returned. What do you think the correct result should be?<br>
Someone is wrong. Is this a bug, and then with which product?<br>
                          <br>
run from the "ij" environment of cloudscape 10.0 downloaded from
the web on Friday July 8, 2005.<br>
ij&gt; run 'test1.txt';<br>
                          <br>
cloudscape output for last select:<br>
ij&gt; select a.ival1, a.cval1, c.ival<br>
from t1 a,<br>
(select distinct 1 from t3) as c(ival)<br>
where a.ival1 &gt;=100<br>
;<br>
IVAL1 |CVAL1 |IVAL<br>
------------------------------------------------------<br>
100 |row 100 from t1 |1<br>
                          <br>
1 row selected<br>
ij&gt;<br>
                          <br>
--- DB2 output:<br>
select a.ival1, a.cval1, c.ival from t1 a, (select distinct 1 from t3)
as c(ival<br>
) where a.ival1 &gt;=100<br>
                          <br>
IVAL1 CVAL1 IVAL<br>
----------- ------------------------------ -----------<br>
100 row 100 from t1 1<br>
200 row 200 from t1 1<br>
300 row 300 from t1 1<br>
                          <br>
3 record(s) selected.<br>
                          <br>
                          <br>
----------------------<br>
-- test script follows:<br>
-----------------------<br>
-- build first table<br>
drop table t1;<br>
create table t1 (<br>
ival1 integer,<br>
cval1 char(30)<br>
);<br>
                          <br>
-- build another table<br>
drop table t2;<br>
create table t2 (<br>
ival1 integer,<br>
cval1 char(30)<br>
);<br>
                          <br>
-- build another table<br>
drop table t3;<br>
create table t3 (<br>
ival1 integer,<br>
cval1 char(30)<br>
);<br>
                          <br>
-- insert into table t1<br>
insert into t1 values(<br>
100, 'row 100 from t1' );<br>
                          <br>
insert into t1 values(<br>
200, 'row 200 from t1' );<br>
                          <br>
insert into t1 values(<br>
300, 'row 300 from t1' );<br>
                          <br>
-- insert into table t2<br>
insert into t2 values(<br>
100, 'row 100 from t2' );<br>
insert into t2 values(<br>
200, 'row 200 from t2' );<br>
insert into t2 values(<br>
300, 'row 300 from t2' );<br>
                          <br>
-- insert into table t3<br>
insert into t3 values(<br>
300, 'row 300 from t3' );<br>
                          <br>
select * from t1;<br>
select * from t2;<br>
                          <br>
select a.ival1, a.cval1, c.ival<br>
from t1 a,<br>
(select distinct 1 from t3) as c(ival)<br>
where a.ival1 &gt;=100<br>
;<br>
                          <br>
                          <br>
                          <br>
                          <br>
Any takers.</font></td>
                        </tr>
                      </tbody>
                    </table>
                    <br>
                    </td>
                  </tr>
                </tbody>
              </table>
              <br>
              </td>
            </tr>
          </tbody>
        </table>
        <br>
        </td>
      </tr>
    </tbody>
  </table>
  <br>
  <br>
  <font face="sans-serif" size="2">--- note ends here...</font>
  <br>
  <font face="sans-serif" size="2"><br>
Andrew Czapran<br>
WebSphere Business Integration<br>
905-413-2843</font>
</blockquote>
</body>
</html>

Mime
View raw message