db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Manjula Kutty (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-887) Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
Date Sat, 28 Jan 2006 01:05:00 GMT
     [ http://issues.apache.org/jira/browse/DERBY-887?page=all ]

Manjula Kutty updated DERBY-887:
--------------------------------

    Attachment: DERBY-887.sql

Even I feel the same. Still, if it is valid then it should show consistent behaviour.
I just happend to run this query and was expecting an error message , but ij returned me some
rows. In my schema I had primary key on the column 'id'. If I remove the primary key then
every thing works fine

I'm attaching the script to reproduce the problem.

The derby jars I have used are : 

[C:\pantry\derby.jar] 10.2.0.0 alpha - (372715)
[C:\pantry\derbytools.jar] 10.2.0.0 alpha - (372715)
......................................................................................................

Here is the results with primary key :
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=true;
ID                  |COL1
-------------------------
2                   |ba


--------------------------------------------------------
With out primary key

ij>create table t (id bigint generated always as identity (start with 1,increment by 1),
col1 char(2));
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=true;
ID                  |COL1
-------------------------
1                   |ca
2                   |ba
3                   |ea
4                   |dd

4 rows selected



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