This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new ce51f0e527 [CALCITE-7886] Subquery expansion fails for IN subquery
with ROW types
ce51f0e527 is described below
commit ce51f0e52753af382b66c9089ec79cdccd920c06
Author: Mihai Budiu <[email protected]>
AuthorDate: Sat Oct 24 18:26:31 2026 -0700
[CALCITE-7886] Subquery expansion fails for IN subquery with ROW types
Signed-off-by: Mihai Budiu <[email protected]>
---
.../sql/validate/implicit/TypeCoercionImpl.java | 14 +++++
.../apache/calcite/sql2rel/SqlToRelConverter.java | 57 +++++++++++---------
core/src/test/resources/sql/row-equality.iq | 61 ++++++++++++++++++++++
3 files changed, 108 insertions(+), 24 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
index 7f4d0d7356..463216b006 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
@@ -553,6 +553,20 @@ private boolean coalesceCoercion(SqlCallBinding
callBinding) {
// A "scalar" (non-ROW) type is in the LHS of the IN
coerced = coerceOperandType(scope, binding.getCall(), 0,
widenTypes.get(0))
|| coerced;
+ } else if (node1.getKind() != SqlKind.ROW) {
+ // An expression of a ROW type is in the LHS of the IN, e.g.,
+ // SELECT r IN (ROW(1.5, 2), ...)
+ // ^
+ // In this case cast it to a ROW of the desired field types
+ final List<RelDataTypeField> fields = type1.getFieldList();
+ final List<RelDataType> fieldTypes = new ArrayList<>();
+ for (int i = 0; i < colCount; i++) {
+ fieldTypes.add(syncAttributes(fields.get(i).getType(),
widenTypes.get(i)));
+ }
+ coerced =
+ coerceOperandType(scope, binding.getCall(), 0,
+ factory.createStructType(fieldTypes, type1.getFieldNames()))
+ || coerced;
}
for (int i = 0; i < widenTypes.size(); i++) {
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index f380de8d82..7ff2a824a9 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -1443,7 +1443,7 @@ private void substituteSubQuery(Blackboard bb, SubQuery
subQuery) {
if (!config.isExpand()) {
if (query instanceof SqlNodeList) {
- convertNodeListToSubQuery(bb, subQuery, query, leftSqlKeys,
targetRowType, call);
+ convertNodeListToSubQuery(bb, subQuery, query, leftKeyNode,
targetRowType, call);
return;
}
return;
@@ -1486,7 +1486,7 @@ private void substituteSubQuery(Blackboard bb, SubQuery
subQuery) {
// Instead, we convert to a RexSubQuery which can be optimized later.
if (bb.root == null) {
if (query instanceof SqlNodeList) {
- convertNodeListToSubQuery(bb, subQuery, query, leftSqlKeys,
targetRowType, call);
+ convertNodeListToSubQuery(bb, subQuery, query, leftKeyNode,
targetRowType, call);
}
return;
}
@@ -2069,7 +2069,7 @@ private RexNode ensureComparisonTypes(RexNode node) {
* @param bb Blackboard containing the context
* @param subQuery The SubQuery to populate with the converted
expression
* @param query The query node (expected to be a SqlNodeList)
- * @param leftSqlKeys Left-hand key expressions for IN/SOME/ALL
+ * @param leftKeyNode Left-hand operand of IN/SOME/ALL
* @param targetRowType The target row type for the conversion
* @param call The original SQL call
*/
@@ -2077,19 +2077,42 @@ private void convertNodeListToSubQuery(
Blackboard bb,
SubQuery subQuery,
SqlNode query,
- List<SqlNode> leftSqlKeys,
+ SqlNode leftKeyNode,
RelDataType targetRowType,
SqlCall call) {
RelNode relNode = convertRowValues(bb, query, (SqlNodeList) query, false,
targetRowType);
- final ImmutableList.Builder<RexNode> builder = ImmutableList.builder();
- for (SqlNode node : leftSqlKeys) {
- builder.add(bb.convertExpression(node));
- }
- final ImmutableList<RexNode> list = builder.build();
+ final ImmutableList<RexNode> list = convertInKeys(bb, leftKeyNode);
assert relNode != null;
subQuery.expr = createSubquery(subQuery.node.getKind(), relNode, list,
call);
}
+ /**
+ * Converts the left-hand operand of IN/SOME/ALL to a list of expressions,
+ * one for each column of the right-hand operand.
+ *
+ * <p>For a ROW constructor, the list contains its operands:
+ * for {@code (a, b) IN ((1, 2))} the list is [a, b].
+ * For any other operand of a ROW type, the list contains its fields, as in
+ * the validator (see {@link SqlInOperator#deriveType}): if {@code r} has
+ * type {@code ROW(x INTEGER, y INTEGER)}, for {@code r IN (ROW(1, 2))}
+ * the list is [r.x, r.y]. For any other operand, the list contains only
+ * the operand.
+ */
+ private ImmutableList<RexNode> convertInKeys(Blackboard bb, SqlNode
leftKeyNode) {
+ if (leftKeyNode.getKind() == SqlKind.ROW) {
+ return ((SqlCall) leftKeyNode).getOperandList().stream()
+ .map(bb::convertExpression)
+ .collect(toImmutableList());
+ }
+ final RexNode key = bb.convertExpression(leftKeyNode);
+ if (!key.getType().isStruct()) {
+ return ImmutableList.of(key);
+ }
+ return key.getType().getFieldList().stream()
+ .map(field -> rexBuilder.makeFieldAccess(key, field.getIndex()))
+ .collect(toImmutableList());
+ }
+
/**
* Gets the list size threshold under which {@link #convertInToOr} is used.
* Lists of this size or greater will instead be converted to use a join
@@ -6170,21 +6193,7 @@ private RexNode convertExpression0(SqlNode expr) {
query = call.operand(1);
if (!(query instanceof SqlNodeList)) {
root = convertQueryRecursive(query, false, null);
- final SqlNode operand = call.operand(0);
- List<SqlNode> nodes;
- switch (operand.getKind()) {
- case ROW:
- nodes = ((SqlCall) operand).getOperandList();
- break;
- default:
- nodes = ImmutableList.of(operand);
- }
- final ImmutableList.Builder<RexNode> builder =
- ImmutableList.builder();
- for (SqlNode node : nodes) {
- builder.add(convertExpression(node));
- }
- final ImmutableList<RexNode> list = builder.build();
+ final ImmutableList<RexNode> list = convertInKeys(this,
call.operand(0));
RelNode rel = root.rel;
// Fix the correlation namespaces and de-duplicate the correlation
variables.
CorrelationUse correlationUse = getCorrelationUse(this, root.rel);
diff --git a/core/src/test/resources/sql/row-equality.iq
b/core/src/test/resources/sql/row-equality.iq
index aa9de0d6fc..a3cd23c293 100644
--- a/core/src/test/resources/sql/row-equality.iq
+++ b/core/src/test/resources/sql/row-equality.iq
@@ -507,6 +507,67 @@ SELECT (1, CAST(NULL AS VARCHAR))
!ok
+# Test cases for https://issues.apache.org/jira/browse/CALCITE-7886
+# [CALCITE-7886] Subquery expansion fails for IN subquery with ROW types
+!set insubquerythreshold 0
+
+# Validated on H2 2.4.240 and PostgreSQL 14: same result.
+SELECT * FROM (SELECT ROW(1, 2) AS r) AS t WHERE r IN (ROW(1, 2), ROW(3, 4));
++--------+
+| R |
++--------+
+| {1, 2} |
++--------+
+(1 row)
+
+!ok
+
+# IN_DECIMAL casts "a" to ROW(DECIMAL, CHAR(1)) before it compares.
+WITH t(id, a) AS (
+ SELECT id, ROW(x, y)
+ FROM (VALUES (1, 1, 'a'), (2, 1, NULL), (3, 2, 'c')) AS v(id, x, y))
+SELECT id,
+ a IN (ROW(1, 'a'), ROW(2, 'b')) AS in_list,
+ a NOT IN (ROW(1, 'a'), ROW(2, 'b')) AS notin_list,
+ a IN (ROW(1.0, 'a'), ROW(2.5, 'b')) AS in_decimal,
+ a IN (VALUES (1, 'a'), (2, 'b')) AS in_values,
+ a NOT IN (SELECT 1, 'a') AS notin_subq
+FROM t
+ORDER BY id;
++----+---------+------------+------------+-----------+------------+
+| ID | IN_LIST | NOTIN_LIST | IN_DECIMAL | IN_VALUES | NOTIN_SUBQ |
++----+---------+------------+------------+-----------+------------+
+| 1 | true | false | true | true | false |
+| 2 | | | | | |
+| 3 | false | true | false | false | true |
++----+---------+------------+------------+-----------+------------+
+(3 rows)
+
+!ok
+
+# A window function of a ROW type on the left of IN.
+# Validated on H2 2.4.240: same result.
+WITH t(id, a) AS (
+ SELECT id, ROW(x, y)
+ FROM (VALUES (1, 1, 'a'), (2, 1, NULL), (3, 2, 'c')) AS v(id, x, y))
+SELECT id,
+ LAG(a) OVER (ORDER BY id) IN (ROW(1, 'a'), ROW(2, 'b')) AS lag_in_list,
+ LEAD(a) OVER (ORDER BY id) IN (ROW(1, 'a'), ROW(2, 'b')) AS lead_in_list,
+ LAG(a) OVER (ORDER BY id) IN (SELECT 1, 'a') AS lag_in_subq
+FROM t
+ORDER BY id;
++----+-------------+--------------+-------------+
+| ID | LAG_IN_LIST | LEAD_IN_LIST | LAG_IN_SUBQ |
++----+-------------+--------------+-------------+
+| 1 | | | |
+| 2 | true | false | true |
+| 3 | | | |
++----+-------------+--------------+-------------+
+(3 rows)
+
+!ok
+!set insubquerythreshold 20
+
#####################################################################
# ROW values inside a collection
#
