In List Optimization produces wrong results

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. use attached database
2. Run this query to get right answer - select id,LossDate,retired   from 
cc_claim qroot where   qRoot.LossDate  between  TIMESTAMP  '2008-12-16
02:42:00.0' AND TIMESTAMP '2009-12-16 23:59:59.332' and 
id in ( 101 ) 
AND qRoot.Retired = 0

3.Run this query to get wrong answer - select id,LossDate,retired   from 
cc_claim qroot where   qRoot.LossDate  between  TIMESTAMP  '2008-12-16
02:42:00.0' AND TIMESTAMP '2009-12-16 23:59:59.332' and 
id in (  select  101 ) 
AND qRoot.Retired = 0

What is the expected output?
101 2009-12-16 14:42:02.0   0
What do you see instead?
no results

What version of the product are you using? On what operating system, file
system, and virtual machine?
1.2.125
Windows xp 64
ntfs
jdk 1.6

Do you know a workaround?
turning off In optimizations. But we are having a general problem with
1.2.125 where we non-deterministically get wrong results depending on
machine and whether we run file or in-memory. I cannot narrow down what the
relevent factor are. I can run 2 tests on my local machine using file or
in-memory. A colleague runs the same two tests, once with file and it
passes, once in-memory and one fails. 

How important/urgent is the problem for you?
We need the database to be stable and pass all of our tests, so it's very
important

In your view, is this a defect or a feature request?
definitely some sort of defect

Please provide any additional information below.
I will try to provide more test cases as well as one that fails
non-deterministically

Original issue reported on code.google.com by youn...@gmail.com on 17 Dec 2009 at 6:46

[GoogleCodeExporter]

Thanks a lot! This is a bug, it will be fixed in the next release.
I found a simpler test case:

drop table test;
create table test(id int, name varchar);
create index idx_name on test(id, name);
insert into test values(1, 'Hello'), (2, 'World');
select * from test where id between 1 and 3 and name in ('World');
select * from test where id between 1 and 3 and name in (select 'World');
explain select * from test where id between 1 and 3 and name in ('World');
explain select * from test where id between 1 and 3 and name in (select 
'World');

Original comment by thomas.t...@gmail.com on 17 Dec 2009 at 10:56

• Changed state: Accepted

[GoogleCodeExporter]

This problem should be fixed in version 1.2.126.

Original comment by thomas.t...@gmail.com on 18 Dec 2009 at 9:19

• Changed state: Fixed