I thought what was supposed to happen in the current IN optimization is
that logically a between is added to the query but that the individual
predicates are still left for checking. The hope then is that between
allows a single index scan to narrow down the list of candidate rows
but each term in the IN list is then supposed to somehow be qualified
for each returned row. I don't know if the terms are pushed to store
as qualifiers, or are qualified in execution layer. My guess would be
that the problem is not "between" but some problem is applying the
real qualifiers to integer comparisons, where for some reason that
compare is different for transformed query; but that is only a guess.
Army wrote:
Bryan Pendleton wrote:
-- But this one returns 1 row...
ij> select * from t1 where i in (2.8, 4.23);
I
-----------
4
Is the 2-element IN list being converted to a BETWEEN expression,
perhaps?
The code in InListOperatorNode that does the conversion to BETWEEN is:
if (rightOperandList.size() == 1)
{
// just use an equality predicate
}
else if ((leftOperand instanceof ColumnReference) &&
rightOperandList.containsAllConstantNodes())
{
// convert to BETWEEN.
}
So Yes, it looks like that is what is happening here. That also
explains why
select * from t1 where i in (4.23);
correctly returns zero rows (because it is simply treated as an equality
predicate). I'll add a comment saying as much to the Jira.
Thanks for the suggestion!
Army
