[DERBY-887] Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0
• Fix Version/s: 10.5.3.1, 10.6.1.0
• Component/s: SQL

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

• Urgency: Normal
• Bug behavior facts: Wrong query result
• Assignee: Richard N. Hillegas
• Reporter: Manjula Kutty
• Created: 28/Jan/06 07:45
• Updated: 17/Jun/13 09:19
• Resolved: 13/Jul/10 15:20

Description

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

Issue Links

• blocks: DERBY-499 Expose BOOLEAN datatype to end users
• is required by: DERBY-4728 10.5 backport effort tracking issue
• relates to: DERBY-1029 Backout boolean work from the 10.2 branch immediately after the branch is created