pagehelper-org/Mybatis-PageHelper · error · PageException

The pagination statement already contains the top, and can…

Error message

The pagination statement already contains the top, and can no longer be used to query the pagination plugin!

What it means

Thrown when the SQL Server pagination converter finds the query already has a TOP clause. Applying the ROW_NUMBER pagination rewrite on top of an existing TOP would produce nested/incorrect pagination, so PageHelper refuses to paginate such SQL.

Solutions

1. Remove the TOP clause from the original SQL and let PageHelper control the limit
2. Use PageHelper.startPage(offset/limit) instead of TOP for row limiting
3. Switch to a dialect/rewriting mode compatible with TOP if TOP must remain
4. Refactor the mapper to a plain SELECT and express limits via page parameters

Example fix

// before
String sql = "SELECT TOP 10 * FROM orders";
// after
String sql = "SELECT * FROM orders";
PageHelper.startPage(1, 10);

java.util.regex.Pattern TOP = java.util.regex.Pattern.compile("(?i)SELECT\\s+TOP\\s+\\d");
if (TOP.matcher(sql).find()) {
    throw new IllegalArgumentException("Remove TOP clause before PageHelper pagination");
}

try { PageHelper.startPage(1, 10); mapper.list(); } catch (PageException e) {
    if (e.getMessage().contains("top")) { /* strip TOP from SQL and retry */ }
}

When it happens

Trigger: Paginating SQL that already contains SELECT TOP n ... (or TOP n PERCENT) with the sqlserver dialect, e.g. helperDialect=sqlserver while the mapper SQL itself uses TOP.

Common situations: Legacy SQL fragments that hardcode TOP for limited fetches combined with PageHelper.startPage; developers copying TOP-based SQL from old code then adding the plugin; count/pagination applied on an already-limited inner query.

Related errors

• The SQL statement cannot be converted to a pagination query!
• the pagination statement must be a select query!
• Make sure the Dialect implementation class configured by…
• The order by in the original SQL
• order by [ ] has a risk of SQL injection, if you want to…

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

Appendix: source

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

        if (limit != null) {
            pageSql = pageSql.replace(PAGE_SIZE, String.valueOf(limit));
        }
        return pageSql;
    }

    /**
     * 获取一个外层包装的TOP查询
     *
     * @param select
     * @return
     */
    protected Select getPageSelect(Select select) {
        if (select instanceof SetOperationList) {
            select = wrapSetOperationList((SetOperationList) select);
        }
        //这里的selectBody一定是PlainSelect
        if (((PlainSelect) select).getTop() != null) {
            throw new PageException("The pagination statement already contains the top, and can no longer be used to query the pagination plugin!");
        }
        //获取查询列
        List<SelectItem<?>> selectItems = getSelectItems((PlainSelect) select);
        //对一层的SQL增加ROW_NUMBER()
        List<SelectItem<?>> autoItems = new ArrayList<>();
        SelectItem<?> orderByColumn = addRowNumber((PlainSelect) select, autoItems);
        //加入自动生成列
        ((PlainSelect) select).addSelectItems(autoItems.toArray(new SelectItem[0]));
        //处理子语句中的order by
        processSelectBody(select, 0);

        //中层子查询
        PlainSelect innerSelectBody = new PlainSelect();
        //PAGE_ROW_NUMBER
        innerSelectBody.addSelectItems(orderByColumn);
        innerSelectBody.addSelectItems(selectItems.toArray(new SelectItem[0]));
        //将原始查询作为内层子查询
        ParenthesedSelect fromInnerItem = new ParenthesedSelect();

View on GitHub (pinned to c692616c5b)