Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: domain of manish.khettry@gmail.com
 designates 64.233.184.202 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:references;
        b=lFZ/2V1/VNPlfJdFF2o+mUFaNtLmmJV7SFZATKH2zaZNjrthtr+9qkwHfhg/j5zwnCmhRTYqlldp4/yy4os7+6zn5BTYsGR6MqEFmxT2zfQZxXIOvhoXdlMGKdDa+P8+kwX0BoxZBEbIAl8l2km+RDO84QZIiGOCsoVz21R6KSk=
Message-ID: <ee5641605071914137a49d0c6@mail.gmail.com>
Date: Tue, 19 Jul 2005 14:13:49 -0700
From: Manish Khettry <manish.khettry@gmail.com>
Reply-To: Manish Khettry <manish.khettry@gmail.com>
To: Derby Development <derby-dev@db.apache.org>
Subject: Re: Potential bug when using distinct in subselect
In-Reply-To: <42D87457.3080401@Sourcery.Org>
Mime-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_Part_4720_16551885.1121807629344"
References: 
 <OF043F2772.2729F322-ON8525703E.00497A06-8525703E.0049F78F@ca.ibm.com>
	 <42D70D9A.4020408@Sourcery.Org>
	 <ee56416050715150619bc0203@mail.gmail.com>
	 <42D87457.3080401@Sourcery.Org>

------=_Part_4720_16551885.1121807629344
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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=20

On 7/15/05, Satheesh Bandaram <satheesh@sourcery.org> 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 pat=
ch
> to be submitted through Jira or by mail to DerbyDev might be the next ste=
ps.
> See this link for how to submit a patch:
> (http://incubator.apache.org/derby/derby_comm.html#Contribute+Code+or+Doc=
umentation)
> =20
>  Satheesh
>=20
>  Manish Khettry wrote:=20
>  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.
>=20
>=20
> Thanks,
> Manish
>=20
> On 7/14/05, Satheesh Bandaram <satheesh@sourcery.org> wrote:
> =20
> =20
>  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.iv=
al1
> =3D a.ival1' (both no-op predicates) having them changes the results of t=
he
> query. Without the predicate or distinct clause the query seems to work
> right.
> =20
>  I think this is a bug.
> =20
>  Satheesh
>=20
> =20
>  Andrew Czapran wrote:
> =20
>=20
>  Hello derby developers:=20
> =20
>  I posted a query on the "IBM Cloudscape" forum and was refered to this i=
d
> by others.=20
> =20
>  Basically, I have a small testcase that shows different results when run
> under cloudscape and IBM DB2.=20
>  We think this is a Cloudscape issue, but we could be wrong.=20
> =20
>  The testcase is a simplified version of our real problem.=20
>  Basically we have a complicated subselect that returns a 1 row, 1 column
> value that we want included in every=20
>  row of our outer select. The complicated scenario failed, so we tried to
> narrow it down as shown by the attached test case.=20
> =20
>  I have attached my original append to the forum and testcase.=20
>  Your review of this issue would be greatly appreciated.=20
>  Thanks.=20
> =20
>  --- note start here.....=20
> =20
> =20
> =20
> =20
> =20
> =20
> =20
>  Why does keyword distinct in subselect cause wrong results (when compare=
d
> to db2 results)
>  Originally posted: 2005 July 13 12:22 PM
> =20
>  andrewc=20
> =20
> =20
> =20
>  Post new reply=20
> =20
>  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 valu=
e
> for all the rows. Yes, we know its not pretty.
> =20
>  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?
> =20
>  run from the "ij" environment of cloudscape 10.0 downloaded from the web=
 on
> Friday July 8, 2005.
>  ij> run 'test1.txt';
> =20
>  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 >=3D100
>  ;
>  IVAL1 |CVAL1 |IVAL
>  ------------------------------------------------------
>  100 |row 100 from t1 |1
> =20
>  1 row selected
>  ij>
> =20
>  --- DB2 output:
>  select a.ival1, a.cval1, c.ival from t1 a, (select distinct 1 from t3) a=
s
> c(ival
>  ) where a.ival1 >=3D100
> =20
>  IVAL1 CVAL1 IVAL
>  ----------- ------------------------------ -----------
>  100 row 100 from t1 1
>  200 row 200 from t1 1
>  300 row 300 from t1 1
> =20
>  3 record(s) selected.
> =20
> =20
>  ----------------------
>  -- test script follows:
>  -----------------------
>  -- build first table
>  drop table t1;
>  create table t1 (
>  ival1 integer,
>  cval1 char(30)
>  );
> =20
>  -- build another table
>  drop table t2;
>  create table t2 (
>  ival1 integer,
>  cval1 char(30)
>  );
> =20
>  -- build another table
>  drop table t3;
>  create table t3 (
>  ival1 integer,
>  cval1 char(30)
>  );
> =20
>  -- insert into table t1
>  insert into t1 values(
>  100, 'row 100 from t1' );
> =20
>  insert into t1 values(
>  200, 'row 200 from t1' );
> =20
>  insert into t1 values(
>  300, 'row 300 from t1' );
> =20
>  -- 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' );
> =20
>  -- insert into table t3
>  insert into t3 values(
>  300, 'row 300 from t3' );
> =20
>  select * from t1;
>  select * from t2;
> =20
>  select a.ival1, a.cval1, c.ival
>  from t1 a,
>  (select distinct 1 from t3) as c(ival)
>  where a.ival1 >=3D100
>  ;
> =20
> =20
> =20
> =20
>  Any takers.=20
> =20
> =20
> =20
> =20
>  --- note ends here...=20
> =20
>  Andrew Czapran
>  WebSphere Business Integration
>  905-413-2843
> =20
> =20
>=20
> =20
>

------=_Part_4720_16551885.1121807629344
Content-Type: text/plain; name="distinct.diff.txt"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="distinct.diff.txt"

SW5kZXg6IGphdmEvZW5naW5lL29yZy9hcGFjaGUvZGVyYnkvaW1wbC9zcWwvZXhlY3V0ZS9Tb3J0
UmVzdWx0U2V0LmphdmEKPT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09
PT09PT09PT09PT09PT09PT09PT09PT09PQotLS0gamF2YS9lbmdpbmUvb3JnL2FwYWNoZS9kZXJi
eS9pbXBsL3NxbC9leGVjdXRlL1NvcnRSZXN1bHRTZXQuamF2YQkocmV2aXNpb24gMjE5NzI5KQor
KysgamF2YS9lbmdpbmUvb3JnL2FwYWNoZS9kZXJieS9pbXBsL3NxbC9leGVjdXRlL1NvcnRSZXN1
bHRTZXQuamF2YQkod29ya2luZyBjb3B5KQpAQCAtMjMwLDYgKzIzMCw3IEBACiAgICAgICovCiAJ
cHVibGljIHZvaWQJb3BlbkNvcmUoKSB0aHJvd3MgU3RhbmRhcmRFeGNlcHRpb24gCiAJeworCSAg
ICAgICAgbmV4dENhbGxlZCA9IGZhbHNlOwogCQliZWdpblRpbWUgPSBnZXRDdXJyZW50VGltZU1p
bGxpcygpOwogCQkvLyBSRVZJU0lUOiB0aHJvdWdoIHRoZSBkaXJlY3QgREIgQVBJLCB0aGlzIG5l
ZWRzIHRvIGJlIGFuCiAJCS8vIGVycm9yLCBub3QgYW4gQVNTRVJUOyB1c2VycyBjYW4gb3BlbiB0
d2ljZS4gT25seSB0aHJvdWdoIEpEQkMKSW5kZXg6IGphdmEvdGVzdGluZy9vcmcvYXBhY2hlL2Rl
cmJ5VGVzdGluZy9mdW5jdGlvblRlc3RzL3Rlc3RzL2xhbmcvZGlzdGluY3Quc3FsCj09PT09PT09
PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09
PT0KLS0tIGphdmEvdGVzdGluZy9vcmcvYXBhY2hlL2RlcmJ5VGVzdGluZy9mdW5jdGlvblRlc3Rz
L3Rlc3RzL2xhbmcvZGlzdGluY3Quc3FsCShyZXZpc2lvbiAyMTk3MjkpCisrKyBqYXZhL3Rlc3Rp
bmcvb3JnL2FwYWNoZS9kZXJieVRlc3RpbmcvZnVuY3Rpb25UZXN0cy90ZXN0cy9sYW5nL2Rpc3Rp
bmN0LnNxbAkod29ya2luZyBjb3B5KQpAQCAtMzI5LDUgKzMyOSwxNiBAQAogZXhlY3V0ZSBjMSB1
c2luZyAndmFsdWVzKCcnbHVzZXJuYW1lMScnLCAnJ2x1c2VybmFtZTInJywgJydsbmFtZTEnJykn
Owogcm9sbGJhY2s7CiAKK2NyZWF0ZSB0YWJsZSB0ZCAoeCBpbnQpOworaW5zZXJ0IGludG8gdGQg
dmFsdWVzICgxKTsKK2luc2VydCBpbnRvIHRkIHZhbHVlcyAoMSk7CitpbnNlcnQgaW50byB0ZCB2
YWx1ZXMgKDIpOworCistLSBkaXN0aW5jdCBpbiBzdWJxdWVyeSB3aGVyZSB0aGUgc3RvcmUgZG9l
cyBub3QgcGVyZm9ybSB0aGUgc29ydC4KK3NlbGVjdCAqIGZyb20gdGQsIChzZWxlY3QgZGlzdGlu
Y3QgMSBmcm9tIHRkKSBhcyBzdWIoeCk7CisKKy0tIGdldCB0aGUgc3RvcmFnZSBzeXN0ZW0gdG8g
ZG8gdGhlIHNvcnQuCitzZWxlY3QgKiBmcm9tIHRkLCAoc2VsZWN0IGRpc3RpbmN0IHggZnJvbSB0
ZCkgYXMgc3ViKHgpOworCiAtLSByZXNldCBhdXRvY29taWl0CiBhdXRvY29tbWl0IG9uOwpJbmRl
eDogamF2YS90ZXN0aW5nL29yZy9hcGFjaGUvZGVyYnlUZXN0aW5nL2Z1bmN0aW9uVGVzdHMvbWFz
dGVyL2Rpc3RpbmN0Lm91dAo9PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09
PT09PT09PT09PT09PT09PT09PT09PT09PT09Ci0tLSBqYXZhL3Rlc3Rpbmcvb3JnL2FwYWNoZS9k
ZXJieVRlc3RpbmcvZnVuY3Rpb25UZXN0cy9tYXN0ZXIvZGlzdGluY3Qub3V0CShyZXZpc2lvbiAy
MTk3MjkpCisrKyBqYXZhL3Rlc3Rpbmcvb3JnL2FwYWNoZS9kZXJieVRlc3RpbmcvZnVuY3Rpb25U
ZXN0cy9tYXN0ZXIvZGlzdGluY3Qub3V0CSh3b3JraW5nIGNvcHkpCkBAIC0yNDc3LDYgKzI0Nzcs
MzEgQEAKIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCiBuYW1lMSAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHxzdW0y
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgIAogaWo+IHJvbGxiYWNrOworaWo+IGNyZWF0ZSB0YWJsZSB0ZCAoeCBpbnQpOwor
MCByb3dzIGluc2VydGVkL3VwZGF0ZWQvZGVsZXRlZAoraWo+IGluc2VydCBpbnRvIHRkIHZhbHVl
cyAoMSk7CisxIHJvdyBpbnNlcnRlZC91cGRhdGVkL2RlbGV0ZWQKK2lqPiBpbnNlcnQgaW50byB0
ZCB2YWx1ZXMgKDEpOworMSByb3cgaW5zZXJ0ZWQvdXBkYXRlZC9kZWxldGVkCitpaj4gaW5zZXJ0
IGludG8gdGQgdmFsdWVzICgyKTsKKzEgcm93IGluc2VydGVkL3VwZGF0ZWQvZGVsZXRlZAoraWo+
IC0tIGRpc3RpbmN0IGluIHN1YnF1ZXJ5IHdoZXJlIHRoZSBzdG9yZSBkb2VzIG5vdCBwZXJmb3Jt
IHRoZSBzb3J0Lgorc2VsZWN0ICogZnJvbSB0ZCwgKHNlbGVjdCBkaXN0aW5jdCAxIGZyb20gdGQp
IGFzIHN1Yih4KTsKK1ggICAgICAgICAgfFggICAgICAgICAgCistLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLQorMSAgICAgICAgICB8MSAgICAgICAgICAKKzEgICAgICAgICAgfDEgICAgICAgICAgCisy
ICAgICAgICAgIHwxICAgICAgICAgIAoraWo+IC0tIGdldCB0aGUgc3RvcmFnZSBzeXN0ZW0gdG8g
ZG8gdGhlIHNvcnQuCitzZWxlY3QgKiBmcm9tIHRkLCAoc2VsZWN0IGRpc3RpbmN0IHggZnJvbSB0
ZCkgYXMgc3ViKHgpOworWCAgICAgICAgICB8WCAgICAgICAgICAKKy0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tCisxICAgICAgICAgIHwyICAgICAgICAgIAorMSAgICAgICAgICB8MSAgICAgICAgICAK
KzEgICAgICAgICAgfDIgICAgICAgICAgCisxICAgICAgICAgIHwxICAgICAgICAgIAorMiAgICAg
ICAgICB8MiAgICAgICAgICAKKzIgICAgICAgICAgfDEgICAgICAgICAgCiBpaj4gLS0gcmVzZXQg
YXV0b2NvbWlpdAogYXV0b2NvbW1pdCBvbjsKIGlqPiAK
------=_Part_4720_16551885.1121807629344--