You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
SELECT column1, column2, ... FROM table1
UNIONSELECT column1, column2, ... FROM table2
UNIONSELECT column1, column2, ... FROM table3
ORDER BY column1, column2
期望的结果:
SELECT TOP 100
column1,
column2, ...
FROM(
SELECT
ROW_NUMBER ( ) OVER ( ORDER BY column1, column2 ) PAGE_ROW_NUMBER,
column1,
column2, ...
FROM(
SELECT column1, column2, ... FROM table1
UNIONSELECT column1, column2, ... FROM table2
UNIONSELECT column1, column2, ... FROM table3
)AS WRAP_OUTER_TABLE
)AS PAGE_TABLE_ALIAS
WHERE
PAGE_ROW_NUMBER >0ORDER BY
PAGE_ROW_NUMBER
实际经过pagehelper-5.3.1处理后的分页sql
SELECT TOP 100
column1,
column2, ...
FROM(
SELECT
ROW_NUMBER ( ) OVER ( ORDER BY RAND( ) ) PAGE_ROW_NUMBER,
column1,
column2, ...
FROM(
SELECT column1, column2, ... FROM table1
UNIONSELECT column1, column2, ... FROM table2
UNIONSELECT column1, column2, ... FROM table3
ORDER BY column1, column2
)AS WRAP_OUTER_TABLE
)AS PAGE_TABLE_ALIAS
WHERE
PAGE_ROW_NUMBER >0ORDER BY
PAGE_ROW_NUMBER
然后发现分页查询过程中产生了这个问题。两个版本产生的分页sql对比发现由于分页处理过程中5.3.1版本在分页处理时是使用ROW_NUMBER ( ) OVER ()函数获取行号且是对随机数RAND()进行的排序,但是同时又保留了原始sql中的order by子句,导致最终sql执行异常。【5.1.9版本中则会在使用ROW_NUMBER ( ) OVER ()获取行号后会提取原sql中的order by子句到OVER()部分(会删除原sql中的order by子句),所以该版本sql执行没有任何问题】
完整异常信息
Msg 1033, Level 15, State 1, Server YZT-TEST-ZAPP-4, Procedure , Line 0 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The ORDER BY clause is invalid in views,inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. (1033)
异常模板
windows10
SQL 解析错误
分页参数
原 SQL
期望的结果:
实际经过pagehelper-5.3.1处理后的分页sql
问题初步分析
然后发现分页查询过程中产生了这个问题。两个版本产生的分页sql对比发现由于分页处理过程中5.3.1版本在分页处理时是使用ROW_NUMBER ( ) OVER ()函数获取行号且是对随机数RAND()进行的排序,但是同时又保留了原始sql中的order by子句,导致最终sql执行异常。【5.1.9版本中则会在使用ROW_NUMBER ( ) OVER ()获取行号后会提取原sql中的order by子句到OVER()部分(会删除原sql中的order by子句),所以该版本sql执行没有任何问题】
完整异常信息
Msg 1033, Level 15, State 1, Server YZT-TEST-ZAPP-4, Procedure , Line 0 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
其他类型的错误
功能建议
目前测试发现pagehelper-5.3.1对sqlserver的order by解析上存在一些问题,针对一个sql中存在多个order by子句的情况暂未测试,多个order by存在的情况可能也会存在类似异常问题。
The text was updated successfully, but these errors were encountered: