Extracting negated IN filters

[MMcM]

FDBInQueryTest.testNotInQueryParameterBad has a filter

Query.not(Query.field("num_value_3_indexed").in("valueThrees"))

giving a plan

Scan(<,>) | [MySimpleRecord] | Not(num_value_3_indexed IN $valueThrees)

If we make this slightly more complicated, so that there is an applicable index

Query.and(
    Query.field("str_value_indexed").equalsParameter("valueStr"),
    Query.not(Query.field("num_value_3_indexed").in("valueThrees")))

it becomes

Index(MySimpleRecord$str_value_indexed [EQUALS $valueStr]) | Not(num_value_3_indexed IN $valueThrees)

along the same lines.

However, if we now enable allowNonSargedInBindings mode with

setMaxNumReplansForInToJoin(-1)

it becomes

Index(MySimpleRecord$str_value_indexed [EQUALS $valueStr]) | Not(num_value_3_indexed EQUALS $__in_num_value_3_indexed__0) WHERE __in_num_value_3_indexed__0 IN $valueThrees

which is wrong.

The fundamental problem is that at some point the InExtractor started believing that NotComponent was okay to descend through for the purpose of hoisting the IN list. This mistake was masked by its considering the broken plan but never returning it, on the grounds that it was more work than the residual predicate. But after #1999, with the proper configuration, it can now escape.

There are probably other expressions for which In extraction is not appropriate.

[normen662]

Hi, I believe this is a duplicate of #1112.