[
http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12364649 ]
Rick Hillegas commented on DERBY-887:
-------------------------------------
Although DERBY-499 introduced some additional casting cases, the root causes of
this bug are in an older version of Derby. Older releases allow illegal
comparisons with BOOLEANs. In addition, the problem query can be expressed in a
way which gets past the 10.1.1.0 parser and produces incorrect results. The
following incorrect results may be observed in 10.1.1.0:
ij> create table t (id bigint generated always as identity (start with
1,increment by 1), col1 char(2), primary key(id))
;
0 rows inserted/updated/deleted
ij> insert into t(col1) values('ca');
1 row inserted/updated/deleted
ij> insert into t(col1) values('ba');
1 row inserted/updated/deleted
ij> insert into t(col1) values('ea');
1 row inserted/updated/deleted
ij> insert into t(col1) values('dd');
1 row inserted/updated/deleted
ij> select * from t ;
ID |COL1
-------------------------
1 |ca
2 |ba
3 |ea
4 |dd
4 rows selected
ij> select * from t where id=0<3;
ID |COL1
-------------------------
1 |ca
2 |ba
3 |ea
4 |dd
4 rows selected
ij> select * from t where (id=0)<3;
ID |COL1
-------------------------
1 |ca
2 |ba
3 |ea
4 |dd
4 rows selected
ij> select * from t where id=(0<3);
ID |COL1
-------------------------
2 |ba
1 row selected
I think there are at least two bugs in here:
o A pre-existing 10.1.1.0 bug.
o Some illegal casts introduced by DERBY-499.
> Select statement returns wrong number of rows if you compare an integer
> column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Priority: Critical
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql
>
> I have a table with 2 columns one is BIGINT which is Generated always
> starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox
> where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is
> getting closed
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
