{"record":{"id":"2cfe9727909b96ca","repo":"pagehelper-org/Mybatis-PageHelper","slug":"the-column-expression-needs-to-define-an-alia","errorCode":null,"errorMessage":"The column \"${expression}\" needs to define an alias","messagePattern":"The column \"(.+?)\" needs to define an alias","errorType":"exception","errorClass":"PageException","httpStatus":null,"severity":"error","filePath":"src/main/java/com/github/pagehelper/parser/defaults/DefaultSqlServerSqlParser.java","lineNumber":461,"sourceCode":"            if (selectExpressionItem != null) { // OrderByElement 在查询列表中\n                Alias alias = selectExpressionItem.getAlias();\n                if (alias != null) { // 查询列含有别名时用查询列别名\n                    iterator.set(cloneOrderByElement(orderByElement, alias.getName()));\n\n                } else { // 查询列不包含别名\n                    if (expression instanceof Column) {\n                        // 查询列为普通列，这时因为列在嵌套查询外时名称中不包含表名，故去除排序列的表名引用\n                        // 例（仅为解释此处逻辑，不代表最终分页结果）：\n                        // SELECT TEST.A FROM TEST ORDER BY TEST.A\n                        // -->\n                        // SELECT A FROM (SELECT TEST.A FROM TEST) ORDER BY A\n                        ((Column) expression).setTable(null);\n\n                    } else {\n                        // 查询列不为普通列时（例如函数列）不支持分页\n                        // 此种情况比较难预测，简单的增加新列容易产生不可预料的结果\n                        // 而为列增加别名是非常简单的，故此要求排序复杂列必须使用别名\n                        throw new PageException(\"The column \\\"\" + expression + \"\\\" needs to define an alias\");\n                    }\n                }\n\n            } else { // OrderByElement 不在查询列表中，需要自动生成一个查询列\n                if (expression instanceof Column) { // OrderByElement 为普通列\n                    Table table = ((Column) expression).getTable();\n                    if (table == null) { // 表名为空\n                        if (allColumns ||\n                                (allColumnsTables.size() == 1 && plainSelect.getJoins() == null) ||\n                                aliases.contains(((Column) expression).getColumnName())) {\n                            // 包含`*`查询列 或者 只有一个 `t.*`列且为单表查询 或者 其实排序列是一个别名\n                            // 此时排序列其实已经包含在查询列表中了，不需做任何操作\n                            continue;\n                        }\n\n                    } else { //表名不为空\n                        String tableName = table.getName();\n                        if (allColumns || allColumnsTables.contains(tableName)) {","sourceCodeStart":443,"sourceCodeEnd":479,"githubUrl":"https://github.com/pagehelper-org/Mybatis-PageHelper/blob/c692616c5bc95b41aa779b502f991825c8e5acbc/src/main/java/com/github/pagehelper/parser/defaults/DefaultSqlServerSqlParser.java#L443-L479","documentation":"When PageHelper rewrites SQL Server queries it must duplicate ORDER BY expressions into the wrapping select's column list (via ROW_NUMBER). If an ORDER BY expression is not a simple column (e.g. a function like LOWER(name)) and does not appear in the original SELECT list, or appears as a complex expression without an alias, the parser cannot reference it safely and demands the query column define an alias.","triggerScenarios":"SQL Server pagination where an ORDER BY item is a non-Column expression (function, arithmetic) that is not a plain column in the select list, or the ORDER BY expression matches a select item that is a function/expression without an alias (e.g. SELECT UPPER(name) FROM t ORDER BY UPPER(name)).","commonSituations":"Ordering by a function or computed expression in SQL Server paged queries; ORM-generated SQL with expression-based ORDER BY; legacy SQL migrated to SQL Server pagination.","solutions":["Add an alias to the complex select expression and order by the alias: SELECT UPPER(name) AS upper_name FROM t ORDER BY upper_name","Order by a plain column instead of an expression","Include the ORDER BY expression as a plain column in the SELECT list with an alias","Remove the expression from ORDER BY or move ordering into an outer wrapper query that you page over"],"exampleFix":"// before\nSELECT UPPER(name) FROM users ORDER BY UPPER(name)\n\n// after\nSELECT UPPER(name) AS upper_name FROM users ORDER BY upper_name","handlingStrategy":"validation","validationCode":"// Ensure every ORDER BY expression used in a paged SQL Server query is a plain column or aliased select item\nfor (String orderByExpr : extractOrderByExpressions(sql)) {\n    if (orderByExpr.contains(\"(\") && !selectListContainsAlias(sql, orderByExpr)) {\n        throw new IllegalArgumentException(\"ORDER BY expression '\" + orderByExpr + \"' needs a select alias for pagination\");\n    }\n}","typeGuard":null,"tryCatchPattern":"try {\n    return mapper.selectPaged(params);\n} catch (PageException e) {\n    if (e.getMessage().contains(\"needs to define an alias\")) {\n        log.warn(\"Aliasing required for paged ORDER BY, falling back to manual paging\");\n        return manualPagedSelect(params);\n    }\n    throw e;\n}","preventionTips":["Always alias computed/function columns: SELECT UPPER(name) AS upper_name","ORDER BY aliases or plain columns, never raw expressions, in paged queries","Add a lint/test that paginates all production SQL against SQL Server"],"tags":["sql","sqlserver","pagination","order-by","alias"],"backgroundTag":"missing-required-argument","analyzedSha":"c692616c5bc95b41aa779b502f991825c8e5acbc","analyzedAt":"2026-09-08T03:50:49.192Z","contentChangedAt":"2026-09-08T03:50:49.192Z","schemaVersion":2},"datasetVersion":"2026-09-16T04:17:20.429Z"}