Hi, This is a bug in the IN(...) optimization. I will fix it in the next release.
To avoid such problems in the future, I will write additional 'randomized comparative tests' that run randomly generated statements against different databases and compare the results. Regards, Thomas 2010/5/7 Lars Helge Øverland <[email protected]>: > Hi there, > > I am using H2 in my project (dhis2.org) and its serving our needs very > well. > > However from version 1.2.130 there is a change in behavior for select > queries with IN-conditions with indexes. The following query used to > return 4 distinct rows but is now returning 8 rows, where each row is > duplicated. The same query is returning 4 rows in PostgreSQL and is > breaking our cross-database tests. I have created a test case: > > > --------------------------------------------------------------------------------------------------- > create table test ( a integer not null, b integer not null, c > varchar(30) ); > create index index_test on test (a,b); > > insert into test values (1,1,'a'); > insert into test values (1,2,'b'); > insert into test values (2,1,'c'); > insert into test values (2,2,'d'); > > select * from test where a in (1,2) and b in (1,2); > --------------------------------------------------------------------------------------------------- > > > I am interested to hear whether this is a bug or expected behavior. > Thanks a lot for your help. > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
