apache/shardingsphere · error · UnsupportedSQLOperationException

Projections not in simple select, table subquery, join subqu

Error message

Projections not in simple select, table subquery, join subquery, predicate subquery and insert select subquery are not supported in encrypt feature.

What it means

EncryptProjectionTokenGenerator routes subquery projection generation by SubqueryType (VIEW_DEFINITION, TABLE, JOIN, WITH, PREDICATE, INSERT_SELECT). A subquery whose type matches none of these falls through to UnsupportedSQLOperationException listing the supported kinds — i.e. the encrypt rewriter encountered a subquery shape it cannot rewrite projections for.

Source

Thrown at features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/projection/EncryptProjectionTokenGenerator.java:272

    }
    
    private Collection<Projection> generateProjections(final EncryptColumn encryptColumn, final ColumnProjection columnProjection, final SubqueryType subqueryType) {
        if (null == subqueryType || SubqueryType.PROJECTION == subqueryType) {
            return Collections.singleton(generateProjection(encryptColumn, columnProjection));
        }
        if (SubqueryType.VIEW_DEFINITION == subqueryType) {
            return Collections.singleton(generateViewDefinitionProjection(encryptColumn, columnProjection));
        }
        if (SubqueryType.TABLE == subqueryType || SubqueryType.JOIN == subqueryType || SubqueryType.WITH == subqueryType) {
            return generateProjectionsInTableSegmentSubquery(encryptColumn, columnProjection);
        }
        if (SubqueryType.PREDICATE == subqueryType) {
            return Collections.singleton(generateProjectionInPredicateSubquery(encryptColumn, columnProjection));
        }
        if (SubqueryType.INSERT_SELECT == subqueryType) {
            return generateProjectionsInInsertSelectSubquery(encryptColumn, columnProjection);
        }
        throw new UnsupportedSQLOperationException(
                "Projections not in simple select, table subquery, join subquery, predicate subquery and insert select subquery are not supported in encrypt feature.");
    }
    
    private ColumnProjection generateProjection(final EncryptColumn encryptColumn, final ColumnProjection columnProjection) {
        String encryptColumnName = getEncryptColumnName(columnProjection, encryptColumn);
        QuoteCharacter quoteCharacter = getQuoteCharacter(columnProjection);
        IdentifierValue cipherColumnName = new IdentifierValue(encryptColumnName, quoteCharacter);
        IdentifierValue cipherColumnAlias = columnProjection.getAlias().orElse(columnProjection.getName());
        return new ColumnProjection(columnProjection.getOwner().orElse(null), cipherColumnName, cipherColumnAlias,
                databaseType, columnProjection.getLeftParentheses().orElse(null), columnProjection.getRightParentheses().orElse(null));
    }
    
    private ColumnProjection generateViewDefinitionProjection(final EncryptColumn encryptColumn, final ColumnProjection columnProjection) {
        String encryptColumnName = getEncryptColumnName(columnProjection, encryptColumn);
        QuoteCharacter quoteCharacter = getQuoteCharacter(columnProjection);
        IdentifierValue cipherColumnName = new IdentifierValue(encryptColumnName, quoteCharacter);
        return new ColumnProjection(columnProjection.getOwner().orElse(null), cipherColumnName, null,
                databaseType, columnProjection.getLeftParentheses().orElse(null), columnProjection.getRightParentheses().orElse(null));

View on GitHub (pinned to e952770a21)

Solutions

Move the encrypted column out of the unsupported subquery position (hoist the projection into the main query)
Rewrite scalar subqueries as joins or simple projections that the supported types cover
Check release notes / upgrade ShardingSphere — subquery coverage in encrypt rewrite improves over versions
If the column need not be encrypted, exclude it from encrypt rules in this statement's table

Example fix

-- before
SELECT name, (SELECT enc_col FROM t2 WHERE ...) FROM t1;
-- after (hoist to supported simple select / join)
SELECT t1.name, t2.enc_col FROM t1 JOIN t2 ON ...;

Defensive patterns

Strategy: try-catch

Validate before calling

if (sqlHasScalarSubquerySelectingEncryptedColumn(sql, encryptRules)) { /* rewrite to join or hoist projection before sending */ }

Try / catch

catch (UnsupportedSQLOperationException e) { if (e.getMessage().contains("Projections not in")) { /* rewrite subquery shape and retry once */ } }

Prevention

Avoid scalar subqueries selecting encrypted columns
Prefer joins over nested subqueries on encrypted tables
Track encrypt subquery coverage in release notes when upgrading

When it happens

Trigger: An encrypt-ruled SELECT containing an encrypted column inside a subquery position not covered by the enumerated types — e.g. a scalar subquery in an expression, a subquery in UPDATE/DELETE ... SET, or an EXISTS/CTE shape classified as a different SubqueryType by the current parser version.

Common situations: Complex analytical SQL with nested scalar subqueries selecting encrypted columns; new SQL syntax after a parser upgrade that classifies subqueries into types the token generator has not caught up with; migrating dense reporting queries onto encrypted tables.

Related errors

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