db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-887) Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
Date Tue, 31 Jan 2006 17:26:34 GMT
    [ 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


Mime
View raw message