apache/shardingsphere · error · UnsupportedEncryptSQLException

The SQL clause 'BETWEEN...AND...' is unsupported in encrypt

Error message

The SQL clause 'BETWEEN...AND...' is unsupported in encrypt feature.

What it means

EncryptConditionEngine.createEncryptCondition inspects WHERE condition expressions to build encryption conditions. BetweenExpression is explicitly rejected with UnsupportedEncryptSQLException('BETWEEN...AND...') because the encrypt feature cannot rewrite range predicates over encrypted columns (standard encryptors only support equality matching).

Source

Thrown at features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/condition/EncryptConditionEngine.java:122

    }
    
    private boolean isContainsNullLiterals(final ExpressionSegment expression) {
        if (!(expression instanceof LiteralExpressionSegment)) {
            return false;
        }
        String literals = String.valueOf(((LiteralExpressionSegment) expression).getLiterals());
        return "NULL".equalsIgnoreCase(literals) || "NOT NULL".equalsIgnoreCase(literals);
    }
    
    private Collection<EncryptCondition> createEncryptCondition(final ExpressionSegment expression, final String tableName) {
        if (expression instanceof BinaryOperationExpression) {
            return createBinaryEncryptCondition((BinaryOperationExpression) expression, tableName);
        }
        if (expression instanceof InExpression) {
            return createInEncryptCondition(tableName, (InExpression) expression, ((InExpression) expression).getRight());
        }
        if (expression instanceof BetweenExpression) {
            throw new UnsupportedEncryptSQLException("BETWEEN...AND...");
        }
        return Collections.emptyList();
    }
    
    private Collection<EncryptCondition> createBinaryEncryptCondition(final BinaryOperationExpression expression, final String tableName) {
        String operator = expression.getOperator();
        if (LogicalOperator.valueFrom(operator).isPresent()) {
            return Collections.emptyList();
        }
        ShardingSpherePreconditions.checkContains(EncryptConstants.SUPPORTED_BINARY_OPERATORS, operator, () -> new UnsupportedEncryptSQLException(operator));
        return createCompareEncryptCondition(tableName, expression);
    }
    
    private Collection<EncryptCondition> createCompareEncryptCondition(final String tableName, final BinaryOperationExpression expression) {
        if (isLeftRightContainsSubquerySegment(expression)) {
            return Collections.emptyList();
        }
        Optional<ColumnSegment> columnSegment = Optional.ofNullable(isCompareValueSegment(expression.getLeft()) ? expression.getRight() : expression.getLeft()).filter(ColumnSegment.class::isInstance)

View on GitHub (pinned to e952770a21)

Solutions

1. Rewrite BETWEEN as two comparisons only if the algorithm supports range queries (most do not) — otherwise remove the range predicate from the encrypted column
2. Use an encrypt algorithm that supports range queries (e.g. a preserving-order algorithm) if the feature version provides one
3. Move the range filtering to application code by fetching candidate rows another way, or keep the column unencrypted if range queries are essential
4. Add the predicate on a non-encrypted column (e.g. an assisted-query column designed for it)

Example fix

-- before
SELECT * FROM t WHERE enc_col BETWEEN 10 AND 20;
-- after (drop range on cipher column or redesign)
SELECT * FROM t WHERE range_helper_col >= 10 AND range_helper_col <= 20;

if (sql.toUpperCase().matches("(?i).*\\bBETWEEN\\b.*\\bAND\\b.*") && touchesEncryptedColumns(sql)) { throw new IllegalArgumentException("Rewrite BETWEEN on encrypted columns before sending"); }

catch (UnsupportedEncryptSQLException e) { if (e.getMessage().contains("BETWEEN")) { /* remove/reroute the range predicate, then retry */ } }

When it happens

Trigger: Executing a query with a BETWEEN ... AND ... predicate on an encrypt-ruled column through a ShardingSphere proxy/JDBC with the encrypt feature active, so the condition engine hits the BetweenExpression branch.

Common situations: Porting existing range queries to encrypted tables; date-range queries on encrypted date columns; using queryWithCipherColumn=true with range predicates.

Related errors

• Projections not in simple select, table subquery, join subqu
• Algorithm `%s` is unsupported to decrypt
• Row expression can only compare with subquery
• Can not use different encryptor for %s and %s in %s
• Failed to decrypt the ciphertext '%s' in '%s'.

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