[CALCITE-6435] SqlToRel conversion of IN expressions may lead to incorrect simplifications

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.38.0
• Labels: pull-request-available
• Assignee: Zoltan Haindrich
• Reporter: Zoltan Haindrich
• Created: 13/Jun/24 14:05
• Updated: 17/Oct/24 22:41
• Resolved: 17/Oct/24 22:41

Description

the query must have the following features:

• not all columns are selected
  • to enable RelFieldTrimmer to start a cycle
• two equivalent eq filters
  • one in IN form (ename in ( 'Sebastian' ))
  • a regular = (ename = 'Sebastian')
• an unrelated filter like deptno < 100

the optimizer should more-or-less start with the `RelFieldTrimmer`

the issue happens like:

• at parse time both literals are parsed as CHAR( n )
• the number of values in the `IN` is below `inSubqueryThreshold` - so it gets converted to a set of `=` filters
  • expression is converted to OR form
  • during conversion SqlToRelConverter#ensureSqlType is called
    • which skips the conversion for CHAR / VARCHAR
• the = filter goes thru the "regular" rex conversion - which involves calling rexBuilder#ensureType
• the filter condition contains ename = 'Sebastian' twice; however the types differ
• RelFieldTrimmer starts a change cycle ; which induces the simplification of the filter condition
• RexSimplify is executed with predicate elimination disabled (this will be important)
• simplification compares the two literals with equals and returns `false`

workarounds:

• disable the conversion by setting inSubqueryThreshold=1
• run a rule which executes `RexSimplify` with predicate elimination enabled earlier than the trimmer (ex: ReduceExpressionsRule)
  • I think this bug remained hidden because this might happen easily

testcase for `RelOptRulesTest`

  @Test void testIncorrectInType() {
    final String sql = "select ename from emp "
        + "  where ename in ( 'Sebastian' ) and ename = 'Sebastian' and deptno < 100";
    sql(sql)
        .withTrim(true)
        .withRule()
        .checkUnchanged();
  }

results in plan

LogicalProject(ENAME=[$0])
  LogicalValues(tuples=[[]])

Issue Links

• is caused by: CALCITE-1343 Broken Druid query
• is related to: CALCITE-5156 Support implicit integer type cast for IN Sub-query
• links to: GitHub Pull Request #3835