When the query contains multiple IN sub-queries with only literal operands, and connected with OR predicate in WHERE clause, the result is wrong. The minimal reproducer is below:
The Plan generated by calcite master branch: (Notice the bold part of IS NULL($2) in the downstream LogicalFilter)
The wrong part is that when build the downstream LogicalFilter for the two sub-queries, the filter for the second sub-query is CASE(IS NULL($2), false, =($1, false), null:BOOLEAN, IS NOT NULL($1), true, false), notice that $2 should be the second sub-query's intermediate table field dt.c(which field index is $4), and $1 should be the second sub-query's intermediate table field dt.cs(which field index is $3), but now the actual reference is the first sub-query's, this leads to wrong plan, and wrong result.
The root cause is that intermediate table's field name is the same as the previous sub-query's, and when lookup intermediate table field, it always returns the previous one which is not belong to the current subquery.
To fix this issue, we should use both different intermediate table name and field name to identify the reference correctly. (Note that we make intermediate table name different in Calcite-5655)