{"record":{"id":"715c498e66b1ac72","repo":"baomidou/mybatis-plus","slug":"sql-where-715c49","errorCode":null,"errorMessage":"非法SQL，where条件中不能使用数据库函数，错误函数信息：{}","messagePattern":"非法SQL，where条件中不能使用数据库函数，错误函数信息：(.+?)","errorType":"exception","errorClass":"MybatisPlusException","httpStatus":null,"severity":"error","filePath":"mybatis-plus-jsqlparser-support/mybatis-plus-jsqlparser-4.9/src/main/java/com/baomidou/mybatisplus/extension/plugins/inner/IllegalSQLInnerInterceptor.java","lineNumber":188,"sourceCode":"            Parenthesis parenthesis = (Parenthesis) expression;\n            expression = parenthesis.getExpression();\n        }\n        //where条件使用了 or 关键字\n        if (expression instanceof OrExpression) {\n            OrExpression orExpression = (OrExpression) expression;\n            throw new MybatisPlusException(\"非法SQL，where条件中不能使用【or】关键字，错误or信息：\" + orExpression.toString());\n        } else if (expression instanceof NotEqualsTo) {\n            NotEqualsTo notEqualsTo = (NotEqualsTo) expression;\n            throw new MybatisPlusException(\"非法SQL，where条件中不能使用【!=】关键字，错误!=信息：\" + notEqualsTo.toString());\n        } else if (expression instanceof BinaryExpression) {\n            BinaryExpression binaryExpression = (BinaryExpression) expression;\n            // TODO 升级 jsqlparser 后待实现\n//            if (binaryExpression.isNot()) {\n//                throw new MybatisPlusException(\"非法SQL，where条件中不能使用【not】关键字，错误not信息：\" + binaryExpression.toString());\n//            }\n            if (binaryExpression.getLeftExpression() instanceof Function) {\n                Function function = (Function) binaryExpression.getLeftExpression();\n                throw new MybatisPlusException(\"非法SQL，where条件中不能使用数据库函数，错误函数信息：\" + function.toString());\n            }\n            if (binaryExpression.getRightExpression() instanceof Subtraction) {\n                Subtraction subSelect = (Subtraction) binaryExpression.getRightExpression();\n                throw new MybatisPlusException(\"非法SQL，where条件中不能使用子查询，错误子查询SQL信息：\" + subSelect.toString());\n            }\n        } else if (expression instanceof InExpression) {\n            InExpression inExpression = (InExpression) expression;\n            if (inExpression.getRightExpression() instanceof Subtraction) {\n                Subtraction subSelect = (Subtraction) inExpression.getRightExpression();\n                throw new MybatisPlusException(\"非法SQL，where条件中不能使用子查询，错误子查询SQL信息：\" + subSelect.toString());\n            }\n        }\n\n    }\n\n    /**\n     * 如果SQL用了 left Join，验证是否有or、not等等，并且验证是否使用了索引\n     *","sourceCodeStart":170,"sourceCodeEnd":206,"githubUrl":"https://github.com/baomidou/mybatis-plus/blob/bf67d907478c724120bf76292da54abf9e73c2b3/mybatis-plus-jsqlparser-support/mybatis-plus-jsqlparser-4.9/src/main/java/com/baomidou/mybatisplus/extension/plugins/inner/IllegalSQLInnerInterceptor.java#L170-L206","documentation":"IllegalSQLInnerInterceptor (jsqlparser 4.9) throws when the LEFT side of a binary WHERE condition is a database Function (e.g. DATE(x) = ?, UPPER(name) LIKE ?). Applying a function to a column defeats plain B-tree indexes, so the interceptor blocks it; the function text is appended to the message.","triggerScenarios":"Executing SQL like WHERE DATE(create_time) = '2024-01-01' or WHERE IFNULL(col,0)=? with IllegalSQLInnerInterceptor registered; only the LEFT expression of the binary condition is checked here.","commonSituations":"Date-truncation filters in reports (DATE()/TO_DATE() on the column), case-insensitive compares with UPPER()/LOWER() on the column side — all classic index-bypass patterns the policy exists to stop.","solutions":["Move the function to the value side or convert to a sargable range predicate: DATE(create_time)=? becomes create_time >= ? AND create_time < ?+1d.","Add a generated/column-store index or functional index on the DB side and then get the predicate allow-listed via @InterceptorIgnore(illegalSql = \"true\") on that statement.","Remove the interceptor if functional predicates are an accepted pattern in your project."],"exampleFix":"-- before\nSELECT * FROM t WHERE DATE(create_time) = '2024-01-01';\n\n-- after\nSELECT * FROM t WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';","handlingStrategy":"validation","validationCode":"// CI guard: reject function-on-column patterns in mapped SQL\n// git grep -nE \"(DATE|UPPER|LOWER|IFNULL|SUBSTR)\\\\s*\\\\(\" -- src/main/resources/mapper\n// then manually confirm functions are not applied to the column side of a WHERE","typeGuard":null,"tryCatchPattern":"try {\n    mapper.selectList(wrapper);\n} catch (MybatisPlusException e) {\n    if (String.valueOf(e.getMessage()).contains(\"不能使用数据库函数\")) {\n        log.error(\"rewrite with sargable range predicate: {}\", e.getMessage());\n    }\n}","preventionTips":["Teach sargable predicate patterns (col >= ? AND col < ?) as the default for date filtering.","Use functional indexes only with a deliberate @InterceptorIgnore on that statement.","Review .apply(...) fragments carefully — functions on columns usually hide there."],"tags":["mybatis-plus","sql-parsing","interceptor","policy","index","jsqlparser"],"backgroundTag":null,"analyzedSha":"bf67d907478c724120bf76292da54abf9e73c2b3","analyzedAt":"2026-08-14T15:17:09.543Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}