H2 doesn't use index when I use IN and EQUAL in one query

[lukdiman-developer]

I have a table Person and two columns firstName, lastName. I have a composite index on two columns (firstName, lastName) as well. My query is SELECT * FROM Person WHERE firstName IN ("FirstName1", "FirstName2") AND lastName="LastName1". H2 doesn't use the index. In my case Y IN(..) will produce far less rows than X=?.

I decided to have a look on source code. I have found a method org.h2.index.IndexCursor#prepare. There is such a piece of code in the method:
if (isStart || isEnd) { // an X=? condition will produce less rows than // an X IN(..) condition inColumn = null; inList = null; inResult = null; }

I agree that X=? condition will produce less rows than X IN(..). But I doesn't agree that X=? will produce less rows than Y IN (..). This condition excludes IN values from Row.

I changed in debug mode isStart and isEnd values to false and H2 will start to use my index.

1. How can I manage this situation?
2. Does H2 have statistics like Oracle?

[grandinj]

please ask this kind of question on the mailing list

[grandinj]

Fixed

[lukdiman-developer]

Great! Thank you! I've checked a solution from commit 7ec1dbe and it solves the problem.