apache/shardingsphere · error · UnsupportedSQLOperationException

Row expression can only compare with subquery

Error message

Row expression can only compare with subquery

What it means

SubqueryNestedInBinaryOperationEncryptorChecker validates row expressions compared against subqueries (e.g. (a,b) = (SELECT x,y ...)). getExpressionColumnBoundInfos only accepts SubqueryExpressionSegment or SubquerySegment; any other right-hand expression type throws UnsupportedSQLOperationException 'Row expression can only compare with subquery'.

Source

Thrown at features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/checker/cryptographic/SubqueryNestedInBinaryOperationEncryptorChecker.java:94

            checkEncryptorIsSame(rowColumnInfos.get(i), otherColumnInfos.get(i), encryptRule, scenario);
        }
    }
    
    private static List<ColumnSegmentBoundInfo> getRowExpressionColumnBoundInfos(final RowExpression rowExpression) {
        List<ColumnSegmentBoundInfo> result = new ArrayList<>();
        for (ExpressionSegment each : rowExpression.getItems()) {
            if (each instanceof ColumnSegment) {
                result.add(((ColumnSegment) each).getColumnBoundInfo());
            }
        }
        return result;
    }
    
    private static List<ColumnSegmentBoundInfo> getExpressionColumnBoundInfos(final ExpressionSegment expression, final int expectedSize) {
        if (expression instanceof SubqueryExpressionSegment || expression instanceof SubquerySegment) {
            return getSubqueryColumnBoundInfos(expression, expectedSize);
        }
        throw new UnsupportedSQLOperationException("Row expression can only compare with subquery");
    }
    
    private static List<ColumnSegmentBoundInfo> getSubqueryColumnBoundInfos(final ExpressionSegment expression, final int expectedSize) {
        ShardingSpherePreconditions.checkState(isSubquerySegment(expression),
                () -> new UnsupportedSQLOperationException(String.format("only support subquery segment, but got %s", expression.getClass().getName())));
        SubquerySegment subquerySegment = getSubquerySegment(expression);
        Collection<ProjectionSegment> projections = subquerySegment.getSelect().getProjections().getProjections();
        ShardingSpherePreconditions.checkState(projections.size() == expectedSize,
                () -> new UnsupportedSQLOperationException(String.format("Subquery column count %d does not match row expression column count %d", projections.size(), expectedSize)));
        List<ColumnSegmentBoundInfo> result = new ArrayList<>();
        for (ProjectionSegment each : projections) {
            result.add(each instanceof ColumnProjectionSegment
                    ? ((ColumnProjectionSegment) each).getColumn().getColumnBoundInfo()
                    : new ColumnSegmentBoundInfo(new IdentifierValue(each.getColumnLabel())));
        }
        return result;
    }
    

View on GitHub (pinned to e952770a21)

Solutions

1. Rewrite the row comparison as individual column comparisons (a = c AND b = d) for encrypted columns
2. If comparing with a subquery is intended, verify the subquery parses as a SubqueryExpressionSegment (check for parentheses/aliases that change AST shape)
3. Keep encrypted columns out of row-value constructor expressions
4. Upgrade ShardingSphere — checker coverage for row expression shapes grows between versions

Example fix

-- before
SELECT * FROM t WHERE (c1, c2) = (SELECT x, y FROM s)
-- after (if still unsupported for your AST shape)
SELECT * FROM t WHERE c1 = (SELECT x FROM s) AND c2 = (SELECT y FROM s)

String upper = sql.toUpperCase();
if (upper.matches("(?s).*\\(\\s*\\w+\\s*,\\s*\\w+\\s*\\)\\s*=\\s*\\(.*\\).*")) { /* row-vs-row or unsupported shape: rewrite before sending */ }

catch (UnsupportedSQLOperationException e) { if (e.getMessage().contains("Row expression can only compare with subquery")) { /* rewrite as separate column comparisons and retry */ } }

When it happens

Trigger: Executing an encrypt-ruled SQL where a RowExpression is compared with something that is neither a subquery expression nor a subquery segment — e.g. a row constructor on the right side ((a,b) = (c,d)), a function result, or a parser AST shape this checker does not recognize, while the table/columns involved are encrypt columns.

Common situations: Row-value constructor comparisons (SQL standard (a,b) = (c,d)) on encrypted columns; parser upgrades changing the AST node type emitted for parenthesized expressions; complex expressions mixing literals and columns against row expressions.

Related errors

• Can not use different encryptor for %s and %s in %s
• Projections not in simple select, table subquery, join subqu
• Algorithm `%s` is unsupported to decrypt
• Failed to decrypt the ciphertext '%s' in '%s'.
• The SQL clause 'BETWEEN...AND...' is unsupported in encrypt

AI-assisted analysis of apache/shardingsphere@e952770a21 (2026-08-14). Data as JSON: /api/errors/48770f55ade1ce2b. Report an issue: GitHub.