pagehelper-org/Mybatis-PageHelper · error · PageException

The SQL statement cannot be converted to a pagination query!

Error message

The SQL statement cannot be converted to a pagination query!

What it means

Thrown by the SQL Server pagination parser when the SQL cannot be parsed by the underlying SQL parser (JSqlParser). The parse failure is wrapped in a PageException indicating the statement cannot be turned into a SQL Server pagination query.

Solutions

1. Fix or simplify the SQL so it parses as standard SELECT syntax
2. Upgrade pagehelper/jsqlparser to a version supporting the SQL syntax used
3. Switch to a pagination mode that does not require SQL rewriting (e.g. sqlserver2012 dialect or count-based Wrapping via other dialects)
4. Validate the SQL parses with JSqlParser locally before passing it through

Example fix

// before
PageHelper.startPage(1, 10).setDialect("sqlserver");
String sql = "SELECT * FROM t WITH (NOLOCK)"; // unsupported by old parser
// after
helperDialect=sqlserver2012  // or upgrade jsqlparser
String sql = "SELECT * FROM t";

try { JSqlParserUtil.parse(sql); } catch (JSQLParserException e) {
    throw new IllegalArgumentException("SQL not parseable for sqlserver pagination: " + e.getMessage());
}

try { PageHelper.startPage(1, 10); mapper.select(); } catch (PageException e) {
    log.error("SQL Server pagination conversion failed", e);
}

When it happens

Trigger: Calling PageHelper pagination with helperDialect=sqlserver (2005 style ROW_NUMBER) on SQL that JSqlParser cannot parse: non-standard syntax, multi-statement strings, DBMS-specific extensions, or syntax errors in the SQL.

Common situations: SQL Server dialect used against SQL with vendor-specific syntax JSqlParser does not support; malformed SQL built by string concatenation; XML mapper SQL that contains parser-unfriendly constructs (e.g.某些 hints or WITH clauses in old parser versions).

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 pagination statement must be a select query!
• The pagination statement already contains the top, and can…
• Make sure the Dialect implementation class configured by…
• Failed to handle sorting

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

Appendix: source

Thrown at src/main/java/com/github/pagehelper/parser/defaults/DefaultSqlServerSqlParser.java:107

    public String convertToPageSql(String sql) {
        return convertToPageSql(sql, null, null);
    }

    /**
     * 转换为分页语句
     *
     * @param sql
     * @param offset
     * @param limit
     * @return
     */
    public String convertToPageSql(String sql, Integer offset, Integer limit) {
        //解析SQL
        Statement stmt;
        try {
            stmt = SqlParserUtil.parse(sql);
        } catch (Throwable e) {
            throw new PageException("The SQL statement cannot be converted to a pagination query!", e);
        }
        if (!(stmt instanceof Select)) {
            throw new PageException("the pagination statement must be a select query!");
        }
        //获取分页查询的select
        Select pageSelect = getPageSelect((Select) stmt);
        String pageSql = pageSelect.toString();
        //缓存移到外面了，所以不替换参数
        if (offset != null) {
            pageSql = pageSql.replace(START_ROW, String.valueOf(offset));
        }
        if (limit != null) {
            pageSql = pageSql.replace(PAGE_SIZE, String.valueOf(limit));
        }
        return pageSql;
    }

    /**

View on GitHub (pinned to c692616c5b)