Re: [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

[Rick Hillegas]

Hi Kathey,

Sure. I'm also adjusting the casting tests in BooleanTest.java.

Regards,
-Rick

Kathey Marsden (JIRA) wrote:

[ http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12364653 ]

Kathey Marsden commented on DERBY-887:
--------------------------------------

Thanks Rick for looking at this issue.
As part of the fix,  can you add BOOLEAN to the the cast and comparison tables 
in the lang/casting.java test?

Thanks

Kathey

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