[DERBY-3061] Wrong results from query with two conjuncts

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 10.3.1.4
• Fix Version/s: 10.3.2.1, 10.4.1.3
• Component/s: SQL
• Bug behavior facts: Regression
• Assignee: A B
• Reporter: Richard N. Hillegas
• Created: 07/Sep/07 13:28
• Updated: 13/Jul/10 19:21
• Resolved: 11/Sep/07 23:43

Description

Tim Dudgeon, on the user list, reports that the following query returns no results in 10.3.1.4 but works correctly in 10.2. I have verified that the query returns no results in the mainline as well. If you eliminate either of the the conjuncts, then the query returns the correct results:

SELECT MYTABLE.MY_ID
FROM MYTABLE
WHERE MYTABLE.MY_ID < 100 AND MYTABLE.MY_ID IN (
2,15,19,20,21,48,49
)

Here is a more complete script which demonstrates the problem:

drop table mytable;

create table mytable ( id int primary key );

insert into mytable ( id )
values
( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 );

insert into mytable select id + 10 from mytable;
insert into mytable select id + 20 from mytable;
insert into mytable select id + 40 from mytable;
insert into mytable select id + 100 from mytable;

select mytable.id
from mytable
where mytable.id < 100;

select mytable.id
from mytable
where mytable.id in ( 2, 15, 19, 20, 21, 48, 49 );

select mytable.id
from mytable
where mytable.id < 100
and mytable.id in ( 2, 15, 19, 20, 21, 48, 49 );

Issue Links

• is duplicated by: DERBY-4456 The result is empty when the query contains in clause and other condition at the same time
• is duplicated by: DERBY-4464 "select * from table where intColumn < int3 and intColumn in (int1, int2)" always returns empty
• is part of: DERBY-2034 Tracking of bugs that lead to incorrect results being stored or returned to the client
• is related to: DERBY-2740 LIKE parameter marker combined with index multi-probing leads to ASSERT failure with sane jars, wrong results with insane jars.
• is related to: DERBY-47 Some possible improvements to IN optimization