pagehelper-org/Mybatis-PageHelper · error · PageException

Unable to process the SQL, you can submit issues in GitHub…

Error message

Unable to process the SQL, you can submit issues in GitHub for help.!

What it means

PageHelper's SqlServer parser rewrites UNION/INTERSECT/EXCEPT (set operation) queries to add pagination (OFFSET/TOP). It takes the last SELECT in the set operation list and expects it to be a simple PlainSelect; if it is not (e.g. a nested set operation or another non-plain body), the parser cannot safely wrap it and throws this PageException.

Solutions

1. Restructure the SQL so the set operation is a flat list of plain SELECTs without nested/parenthesized set operations, e.g. 'SELECT ... UNION ALL SELECT ... UNION ALL SELECT ...'
2. Wrap the whole set operation in a subquery and page over that: SELECT * FROM (original union sql) t
3. Compute the count query separately (countColumn or a hand-written count MS) so PageHelper does not have to rewrite the set-operation SQL
4. If the SQL is valid but fails, check the JSqlParser version bundled with pagehelper and upgrade pagehelper (parser bugs are often fixed in newer releases)
5. Report the SQL to PageHelper's GitHub issues as the message suggests if it should be supported

Example fix

// before (nested set op fails to parse into PlainSelect)
SELECT * FROM (SELECT a FROM t1 UNION SELECT a FROM t2) x UNION SELECT a FROM t3

// after (flat set operation, each branch a plain select)
SELECT a FROM t1 UNION SELECT a FROM t2 UNION SELECT a FROM t3

// Before calling the query with PageHelper.startPage, reject nested set operations:
String normalized = sql.toUpperCase();
if (normalized.matches("(?s).*UNION\s*\(.*") || normalized.matches("(?s).*\)\s*UNION.*")) {
    throw new IllegalArgumentException("Wrap the set operation in a subquery before pagination: SELECT * FROM ( " + sql + " ) t");
}

try {
    return mapper.selectPaged(params);
} catch (PageException e) {
    if (e.getMessage().startsWith("Unable to process the SQL")) {
        return mapper.selectFlatRewritten(params); // fallback to subquery-wrapped SQL
    }
    throw e;
}

When it happens

Trigger: Executing a page query (PageHelper.startPage + query) against SQL Server whose statement is a set-operation list (UNION/UNION ALL/INTERSECT/EXCEPT/MINUS) where the final select body is itself not a PlainSelect — e.g. nested parentheses producing a nested SetOperationList, or JSqlParser parsing the SQL into an unexpected body type.

Common situations: Paging over UNION queries where the last UNION branch is itself parenthesized or contains another UNION (e.g. '(a UNION b) UNION c'); SQL Server dialect with complex generated SQL from ORM tools; older JSqlParser versions producing different AST shapes than expected.

Understand the failure class

Background: UnsupportedOperationException and "is not supported" errors: when a library deliberately refuses a call — this error's family across 30 libraries.

Related errors

• The column " " needs to define an alias
• The SQL statement cannot be converted to a pagination query!
• the pagination statement must be a select query!
• The pagination statement already contains the top, and can…
• unable to get paginated query parameters!

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

Appendix: source

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

        if (isNotEmptyList(select.getWithItemsList())) {
            newSelect.setWithItemsList(select.getWithItemsList());
            select.setWithItemsList(null);
        }
        return newSelect;
    }

    /**
     * 包装SetOperationList
     *
     * @param setOperationList
     * @return
     */
    protected Select wrapSetOperationList(SetOperationList setOperationList) {
        //获取最后一个plainSelect
        Select setSelectBody = setOperationList.getSelects().get(setOperationList.getSelects().size() - 1);
        if (!(setSelectBody instanceof PlainSelect)) {
            throw new PageException("Unable to process the SQL, you can submit issues in GitHub for help.!");
        }
        PlainSelect plainSelect = (PlainSelect) setSelectBody;
        PlainSelect selectBody = new PlainSelect();
        List<SelectItem<?>> selectItems = getSelectItems(plainSelect);
        selectBody.setSelectItems(selectItems);

        //设置fromIterm
        ParenthesedSelect fromItem = new ParenthesedSelect();
        fromItem.setSelect(setOperationList);
        fromItem.setAlias(new Alias(WRAP_TABLE));
        selectBody.setFromItem(fromItem);
        //order by
        if (isNotEmptyList(setOperationList.getOrderByElements())) {
            selectBody.setOrderByElements(setOperationList.getOrderByElements());
            setOperationList.setOrderByElements(null);
        }
        return selectBody;
    }

View on GitHub (pinned to c692616c5b)