{"record":{"id":"b4e8dc86c889192e","repo":"apache/shardingsphere","slug":"the-sql-clause-between-and-is-unsupported-i","errorCode":null,"errorMessage":"The SQL clause 'BETWEEN...AND...' is unsupported in encrypt feature.","messagePattern":"The SQL clause 'BETWEEN\\.\\.\\.AND\\.\\.\\.' is unsupported in encrypt feature\\.","errorType":"exception","errorClass":"UnsupportedEncryptSQLException","httpStatus":null,"severity":"error","filePath":"features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/condition/EncryptConditionEngine.java","lineNumber":122,"sourceCode":"    }\n    \n    private boolean isContainsNullLiterals(final ExpressionSegment expression) {\n        if (!(expression instanceof LiteralExpressionSegment)) {\n            return false;\n        }\n        String literals = String.valueOf(((LiteralExpressionSegment) expression).getLiterals());\n        return \"NULL\".equalsIgnoreCase(literals) || \"NOT NULL\".equalsIgnoreCase(literals);\n    }\n    \n    private Collection<EncryptCondition> createEncryptCondition(final ExpressionSegment expression, final String tableName) {\n        if (expression instanceof BinaryOperationExpression) {\n            return createBinaryEncryptCondition((BinaryOperationExpression) expression, tableName);\n        }\n        if (expression instanceof InExpression) {\n            return createInEncryptCondition(tableName, (InExpression) expression, ((InExpression) expression).getRight());\n        }\n        if (expression instanceof BetweenExpression) {\n            throw new UnsupportedEncryptSQLException(\"BETWEEN...AND...\");\n        }\n        return Collections.emptyList();\n    }\n    \n    private Collection<EncryptCondition> createBinaryEncryptCondition(final BinaryOperationExpression expression, final String tableName) {\n        String operator = expression.getOperator();\n        if (LogicalOperator.valueFrom(operator).isPresent()) {\n            return Collections.emptyList();\n        }\n        ShardingSpherePreconditions.checkContains(EncryptConstants.SUPPORTED_BINARY_OPERATORS, operator, () -> new UnsupportedEncryptSQLException(operator));\n        return createCompareEncryptCondition(tableName, expression);\n    }\n    \n    private Collection<EncryptCondition> createCompareEncryptCondition(final String tableName, final BinaryOperationExpression expression) {\n        if (isLeftRightContainsSubquerySegment(expression)) {\n            return Collections.emptyList();\n        }\n        Optional<ColumnSegment> columnSegment = Optional.ofNullable(isCompareValueSegment(expression.getLeft()) ? expression.getRight() : expression.getLeft()).filter(ColumnSegment.class::isInstance)","sourceCodeStart":104,"sourceCodeEnd":140,"githubUrl":"https://github.com/apache/shardingsphere/blob/e952770a215630a3659c75d64369168cd3e26b82/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/condition/EncryptConditionEngine.java#L104-L140","documentation":"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).","triggerScenarios":"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.","commonSituations":"Porting existing range queries to encrypted tables; date-range queries on encrypted date columns; using queryWithCipherColumn=true with range predicates.","solutions":["Rewrite BETWEEN as two comparisons only if the algorithm supports range queries (most do not) — otherwise remove the range predicate from the encrypted column","Use an encrypt algorithm that supports range queries (e.g. a preserving-order algorithm) if the feature version provides one","Move the range filtering to application code by fetching candidate rows another way, or keep the column unencrypted if range queries are essential","Add the predicate on a non-encrypted column (e.g. an assisted-query column designed for it)"],"exampleFix":"-- before\nSELECT * FROM t WHERE enc_col BETWEEN 10 AND 20;\n-- after (drop range on cipher column or redesign)\nSELECT * FROM t WHERE range_helper_col >= 10 AND range_helper_col <= 20;","handlingStrategy":"validation","validationCode":"if (sql.toUpperCase().matches(\"(?i).*\\\\bBETWEEN\\\\b.*\\\\bAND\\\\b.*\") && touchesEncryptedColumns(sql)) { throw new IllegalArgumentException(\"Rewrite BETWEEN on encrypted columns before sending\"); }","typeGuard":null,"tryCatchPattern":"catch (UnsupportedEncryptSQLException e) { if (e.getMessage().contains(\"BETWEEN\")) { /* remove/reroute the range predicate, then retry */ } }","preventionTips":["Do not use range predicates on encrypted columns with equality-only algorithms","Design assisted/helper columns for range queries up front","Lint SQL for BETWEEN on encrypt-ruled tables before release"],"tags":["encrypt","between","range-query","unsupported-sql"],"backgroundTag":null,"analyzedSha":"e952770a215630a3659c75d64369168cd3e26b82","analyzedAt":"2026-08-14T13:54:53.392Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}