pagehelper-org/Mybatis-PageHelper · warning

Failed to handle sorting

Error message

Failed to handle sorting: ${e}, downgraded to a direct splice of the order by parameter

What it means

DefaultOrderBySqlParser attempted to rewrite the SQL to inject the orderBy clause using JSqlParser, but parsing/modification failed with a Throwable. This is not fatal: the parser logs a warning and falls back to naively appending ' order by <orderBy>' to the original SQL string. The message includes the underlying exception.

Solutions

1. Simplify or rewrite the SQL so JSqlParser can parse it (remove vendor-specific constructs or split complex UNION/CTE queries)
2. Upgrade pagehelper (and its bundled JSqlParser) to a newer version supporting your SQL grammar
3. Ensure the original SQL has no ORDER BY clause when using OrderByHelper, and definitely no '?' parameters inside ORDER BY
4. Verify the fallback result manually: since 'order by' is string-appended, check the generated SQL and add the ordering in the SQL itself if the fallback is wrong

Example fix

// before (unparseable by JSqlParser)
orderBy("id desc") over "SELECT ... WHERE name = {fn CONCAT(a,b)}"
// after — order in SQL directly
String sql = "SELECT ... ORDER BY id DESC"; // skip OrderByHelper
PageHelper.orderBy("id desc"); // only for JSqlParser-compatible SQL

// pre-check: ORDER BY with a placeholder cannot be rewritten
if (sql.matches("(?is).*order\\s+by[^)]*\\?.*")) {
    log.warn("skip OrderByHelper: SQL has parameterized ORDER BY");
}
// ideally: verify the SQL parses with the same JSqlParser version
CCJSqlParserUtil.parse(sql); // throws if unsupported -> handle before paging

try {
    PageHelper.orderBy("id desc");
    list = mapper.select();
} catch (Exception e) {
    log.warn("orderBy plugin failed, ordering manually", e);
    list.sort(Comparator.comparing(Entity::getId).reversed());
}

When it happens

Trigger: Original SQL that JSqlParser cannot parse (dialect-specific syntax, comments, CTEs, UNION, stored-procedure calls like CALL/EXEC); the original SQL already contains an ORDER BY with a '?' placeholder parameter; complex SELECT with DB-specific functions unsupported by the bundled JSqlParser version.

Common situations: Using PageHelper's orderBy support on legacy hand-written SQL with vendor syntax; passing OrderBy sql to queries with bind parameters in ORDER BY; JSqlParser version too old for the SQL grammar in use (upgrade fixes many cases).

Understand the failure class

Background: "query failed", "%w: SQL error" — wrapped database query errors in Go libraries explained — this error's family across 3 libraries.

Related errors

• The order by in the original SQL
• The SQL statement cannot be converted to a pagination query!
• When you use the PageHelper pagination plugin, you must set…
• Make sure the Dialect implementation class configured by…
• the pagination statement must be a select query!

AI-assisted analysis of pagehelper-org/Mybatis-PageHelper@c692616c5b (2026-09-08). Data as JSON: /api/errors/fc46bafeeb8d4561. Report an issue: GitHub.

Appendix: source

Thrown at src/main/java/com/github/pagehelper/parser/defaults/DefaultOrderBySqlParser.java:69

     * @return
     */
    @Override
    public String converToOrderBySql(String sql, String orderBy) {
        //解析SQL
        Statement stmt = null;
        try {
            stmt = SqlParserUtil.parse(sql);
            Select select = (Select) stmt;
            //处理body-去最外层order by
            List<OrderByElement> orderByElements = extraOrderBy(select);
            String defaultOrderBy = PlainSelect.orderByToString(orderByElements);
            if (defaultOrderBy.indexOf('?') != -1) {
                throw new PageException("The order by in the original SQL[" + sql + "] contains parameters, so it cannot be modified using the OrderBy plugin!");
            }
            //新的sql
            sql = select.toString();
        } catch (Throwable e) {
            log.warn("Failed to handle sorting: " + e + ", downgraded to a direct splice of the order by parameter");
        }
        return sql + " order by " + orderBy;
    }

    /**
     * extra order by and set default orderby to null
     *
     * @param select
     */
    public static List<OrderByElement> extraOrderBy(Select select) {
        if (select != null) {
            if (select instanceof PlainSelect || select instanceof SetOperationList) {
                List<OrderByElement> orderByElements = select.getOrderByElements();
                select.setOrderByElements(null);
                return orderByElements;
            } else if (select instanceof ParenthesedSelect) {
                extraOrderBy(((ParenthesedSelect) select).getSelect());
            }

View on GitHub (pinned to c692616c5b)