What causes "非法SQL，where条件中不能使用数据库函数，错误函数信息：{}" in baomidou/mybatis-plus?

Move the function to the value side or convert to a sargable range predicate: DATE(create_time)=? becomes create_time >= ? AND create_time < ?+1d.

baomidou/mybatis-plus · error · MybatisPlusException

非法SQL，where条件中不能使用数据库函数，错误函数信息：{}

Error message

非法SQL，where条件中不能使用数据库函数，错误函数信息：{}

What it means

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.

Source

Thrown at mybatis-plus-jsqlparser-support/mybatis-plus-jsqlparser-4.9/src/main/java/com/baomidou/mybatisplus/extension/plugins/inner/IllegalSQLInnerInterceptor.java:188

            Parenthesis parenthesis = (Parenthesis) expression;
            expression = parenthesis.getExpression();
        }
        //where条件使用了 or 关键字
        if (expression instanceof OrExpression) {
            OrExpression orExpression = (OrExpression) expression;
            throw new MybatisPlusException("非法SQL，where条件中不能使用【or】关键字，错误or信息：" + orExpression.toString());
        } else if (expression instanceof NotEqualsTo) {
            NotEqualsTo notEqualsTo = (NotEqualsTo) expression;
            throw new MybatisPlusException("非法SQL，where条件中不能使用【!=】关键字，错误!=信息：" + notEqualsTo.toString());
        } else if (expression instanceof BinaryExpression) {
            BinaryExpression binaryExpression = (BinaryExpression) expression;
            // TODO 升级 jsqlparser 后待实现
//            if (binaryExpression.isNot()) {
//                throw new MybatisPlusException("非法SQL，where条件中不能使用【not】关键字，错误not信息：" + binaryExpression.toString());
//            }
            if (binaryExpression.getLeftExpression() instanceof Function) {
                Function function = (Function) binaryExpression.getLeftExpression();
                throw new MybatisPlusException("非法SQL，where条件中不能使用数据库函数，错误函数信息：" + function.toString());
            }
            if (binaryExpression.getRightExpression() instanceof Subtraction) {
                Subtraction subSelect = (Subtraction) binaryExpression.getRightExpression();
                throw new MybatisPlusException("非法SQL，where条件中不能使用子查询，错误子查询SQL信息：" + subSelect.toString());
            }
        } else if (expression instanceof InExpression) {
            InExpression inExpression = (InExpression) expression;
            if (inExpression.getRightExpression() instanceof Subtraction) {
                Subtraction subSelect = (Subtraction) inExpression.getRightExpression();
                throw new MybatisPlusException("非法SQL，where条件中不能使用子查询，错误子查询SQL信息：" + subSelect.toString());
            }
        }

    }

    /**
     * 如果SQL用了 left Join，验证是否有or、not等等，并且验证是否使用了索引
     *

View on GitHub (pinned to bf67d90747)

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.

Example fix

-- before
SELECT * FROM t WHERE DATE(create_time) = '2024-01-01';

-- after
SELECT * FROM t WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

Defensive patterns

Strategy: validation

Validate before calling

// CI guard: reject function-on-column patterns in mapped SQL
// git grep -nE "(DATE|UPPER|LOWER|IFNULL|SUBSTR)\\s*\\(" -- src/main/resources/mapper
// then manually confirm functions are not applied to the column side of a WHERE

Try / catch

try {
    mapper.selectList(wrapper);
} catch (MybatisPlusException e) {
    if (String.valueOf(e.getMessage()).contains("不能使用数据库函数")) {
        log.error("rewrite with sargable range predicate: {}", e.getMessage());
    }
}

Prevention

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.

When it happens

Trigger: 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.

Common situations: 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.

Related errors

AI-assisted analysis of baomidou/mybatis-plus@bf67d90747 (2026-08-14). Data as JSON: /api/errors/715c498e66b1ac72. Report an issue: GitHub.