[CALCITE-6506] Type inference for IN list is incorrect

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.38.0
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: Brandon Chong
• Created: 30/Jul/24 18:10
• Updated: 17/Oct/24 22:41
• Resolved: 17/Oct/24 22:41

Description

If a user writes a query like:
 
 select * from cp."iceberg/orders/orders.parquet" where o_orderkey IN (1, 2, 3)
It get's rewritten to use a LogicalValues with RelDataType nullable INTEGER row type. The problem is that it's actually NOT NULL.

You can see the issue in SqlToRelConverter:
 
 {{final RelDataType targetRowType = SqlTypeUtil.promoteToRowType(typeFactory,
validator.getValidatedNodeType(leftKeyNode), null);
final boolean notIn = call.getOperator().kind == SqlKind.NOT_IN;
converted =
convertExists(query, RelOptUtil.SubQueryType.IN, subQuery.logic,
notIn, targetRowType);}}
Note that it's using the type of the leftKeyNode and not right key node.

For example: A IN (B, C, D) ... it's using typeof(A) instead of typeof(B, C, D).

Issue Links

• is related to: CALCITE-4889 Double join is created for NOT IN when IN-list is converted to Values
• links to: GitHub Pull Request #3899