strongduanmu commented on code in PR #29491:
URL: https://github.com/apache/shardingsphere/pull/29491#discussion_r1434722363
##########
test/it/rewriter/src/test/resources/scenario/sharding/case/dml/select.xml:
##########
@@ -419,26 +419,26 @@
<rewrite-assertion
id="select_top_with_multiple_route_with_memory_group_by_for_parameters_for_sqlserver"
db-types="SQLServer">
<input sql="SELECT * FROM (SELECT TOP(?) row_number() OVER (ORDER BY
o.account_id) AS rownum_, o.account_id FROM t_account o WHERE o.account_id IN
(100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > ?" parameters="110, 100" />
- <output sql="SELECT * FROM (SELECT TOP(?) row_number() OVER (ORDER BY
o.account_id) AS rownum_, o.account_id FROM t_account_0 o WHERE o.account_id IN
(100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > ?" parameters="110, 0" />
- <output sql="SELECT * FROM (SELECT TOP(?) row_number() OVER (ORDER BY
o.account_id) AS rownum_, o.account_id FROM t_account_1 o WHERE o.account_id IN
(100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > ?" parameters="110, 0" />
+ <output sql="SELECT * FROM (SELECT TOP(?) row_number() OVER (ORDER BY
o.account_id) AS rownum_, o.account_id FROM t_account_0 o WHERE o.account_id IN
(100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > ?" parameters="110, 100" />
+ <output sql="SELECT * FROM (SELECT TOP(?) row_number() OVER (ORDER BY
o.account_id) AS rownum_, o.account_id FROM t_account_1 o WHERE o.account_id IN
(100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > ?" parameters="110, 100" />
</rewrite-assertion>
<rewrite-assertion
id="select_top_with_multiple_route_with_memory_group_by_for_literals_for_sqlserver"
db-types="SQLServer">
<input sql="SELECT * FROM (SELECT TOP(110) row_number() OVER (ORDER BY
o.account_id) AS rownum_, o.account_id FROM t_account o WHERE o.account_id IN
(100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > 100" />
- <output sql="SELECT * FROM (SELECT TOP(110) row_number() OVER (ORDER
BY o.account_id) AS rownum_, o.account_id FROM t_account_0 o WHERE o.account_id
IN (100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > 0" />
- <output sql="SELECT * FROM (SELECT TOP(110) row_number() OVER (ORDER
BY o.account_id) AS rownum_, o.account_id FROM t_account_1 o WHERE o.account_id
IN (100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > 0" />
+ <output sql="SELECT * FROM (SELECT TOP(110) row_number() OVER (ORDER
BY o.account_id) AS rownum_, o.account_id FROM t_account_0 o WHERE o.account_id
IN (100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > 100" />
+ <output sql="SELECT * FROM (SELECT TOP(110) row_number() OVER (ORDER
BY o.account_id) AS rownum_, o.account_id FROM t_account_1 o WHERE o.account_id
IN (100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > 100" />
</rewrite-assertion>
<rewrite-assertion
id="select_top_percent_with_ties_with_multiple_route_with_memory_group_by_for_parameters_for_sqlserver"
db-types="SQLServer">
<input sql="SELECT * FROM (SELECT TOP(?) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > ?" parameters="110, 100" />
- <output sql="SELECT * FROM (SELECT TOP(?) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_0 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > ?" parameters="110, 0" />
- <output sql="SELECT * FROM (SELECT TOP(?) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_1 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > ?" parameters="110, 0" />
+ <output sql="SELECT * FROM (SELECT TOP(?) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_0 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > ?" parameters="110, 100" />
+ <output sql="SELECT * FROM (SELECT TOP(?) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_1 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > ?" parameters="110, 100" />
</rewrite-assertion>
<rewrite-assertion
id="select_top_percent_with_ties_with_multiple_route_with_memory_group_by_for_literals_for_sqlserver"
db-types="SQLServer">
<input sql="SELECT * FROM (SELECT TOP(110) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > 100" />
- <output sql="SELECT * FROM (SELECT TOP(110) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_0 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > 0" />
- <output sql="SELECT * FROM (SELECT TOP(110) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_1 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > 0" />
+ <output sql="SELECT * FROM (SELECT TOP(110) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_0 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > 100" />
+ <output sql="SELECT * FROM (SELECT TOP(110) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_1 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > 100" />
Review Comment:
You can debug this method -
https://github.com/apache/shardingsphere/blob/ea3118d628c7c7f3d2d93bc9188aab7efe74fd9b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/pagination/engine/PaginationContextEngine.java#L69
##########
test/it/rewriter/src/test/resources/scenario/sharding/case/dml/select.xml:
##########
@@ -419,26 +419,26 @@
<rewrite-assertion
id="select_top_with_multiple_route_with_memory_group_by_for_parameters_for_sqlserver"
db-types="SQLServer">
<input sql="SELECT * FROM (SELECT TOP(?) row_number() OVER (ORDER BY
o.account_id) AS rownum_, o.account_id FROM t_account o WHERE o.account_id IN
(100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > ?" parameters="110, 100" />
- <output sql="SELECT * FROM (SELECT TOP(?) row_number() OVER (ORDER BY
o.account_id) AS rownum_, o.account_id FROM t_account_0 o WHERE o.account_id IN
(100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > ?" parameters="110, 0" />
- <output sql="SELECT * FROM (SELECT TOP(?) row_number() OVER (ORDER BY
o.account_id) AS rownum_, o.account_id FROM t_account_1 o WHERE o.account_id IN
(100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > ?" parameters="110, 0" />
+ <output sql="SELECT * FROM (SELECT TOP(?) row_number() OVER (ORDER BY
o.account_id) AS rownum_, o.account_id FROM t_account_0 o WHERE o.account_id IN
(100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > ?" parameters="110, 100" />
+ <output sql="SELECT * FROM (SELECT TOP(?) row_number() OVER (ORDER BY
o.account_id) AS rownum_, o.account_id FROM t_account_1 o WHERE o.account_id IN
(100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > ?" parameters="110, 100" />
</rewrite-assertion>
<rewrite-assertion
id="select_top_with_multiple_route_with_memory_group_by_for_literals_for_sqlserver"
db-types="SQLServer">
<input sql="SELECT * FROM (SELECT TOP(110) row_number() OVER (ORDER BY
o.account_id) AS rownum_, o.account_id FROM t_account o WHERE o.account_id IN
(100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > 100" />
- <output sql="SELECT * FROM (SELECT TOP(110) row_number() OVER (ORDER
BY o.account_id) AS rownum_, o.account_id FROM t_account_0 o WHERE o.account_id
IN (100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > 0" />
- <output sql="SELECT * FROM (SELECT TOP(110) row_number() OVER (ORDER
BY o.account_id) AS rownum_, o.account_id FROM t_account_1 o WHERE o.account_id
IN (100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > 0" />
+ <output sql="SELECT * FROM (SELECT TOP(110) row_number() OVER (ORDER
BY o.account_id) AS rownum_, o.account_id FROM t_account_0 o WHERE o.account_id
IN (100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > 100" />
+ <output sql="SELECT * FROM (SELECT TOP(110) row_number() OVER (ORDER
BY o.account_id) AS rownum_, o.account_id FROM t_account_1 o WHERE o.account_id
IN (100, 101) GROUP BY account_id ORDER BY account_id DESC) AS row_ WHERE
row_.rownum_ > 100" />
</rewrite-assertion>
<rewrite-assertion
id="select_top_percent_with_ties_with_multiple_route_with_memory_group_by_for_parameters_for_sqlserver"
db-types="SQLServer">
<input sql="SELECT * FROM (SELECT TOP(?) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > ?" parameters="110, 100" />
- <output sql="SELECT * FROM (SELECT TOP(?) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_0 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > ?" parameters="110, 0" />
- <output sql="SELECT * FROM (SELECT TOP(?) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_1 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > ?" parameters="110, 0" />
+ <output sql="SELECT * FROM (SELECT TOP(?) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_0 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > ?" parameters="110, 100" />
+ <output sql="SELECT * FROM (SELECT TOP(?) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_1 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > ?" parameters="110, 100" />
</rewrite-assertion>
<rewrite-assertion
id="select_top_percent_with_ties_with_multiple_route_with_memory_group_by_for_literals_for_sqlserver"
db-types="SQLServer">
<input sql="SELECT * FROM (SELECT TOP(110) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > 100" />
- <output sql="SELECT * FROM (SELECT TOP(110) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_0 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > 0" />
- <output sql="SELECT * FROM (SELECT TOP(110) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_1 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > 0" />
+ <output sql="SELECT * FROM (SELECT TOP(110) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_0 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > 100" />
+ <output sql="SELECT * FROM (SELECT TOP(110) PERCENT WITH TIES
row_number() OVER (ORDER BY o.account_id) AS rownum_, o.account_id FROM
t_account_1 o WHERE o.account_id IN (100, 101) GROUP BY account_id ORDER BY
account_id DESC) AS row_ WHERE row_.rownum_ > 100" />
Review Comment:
Can you take a look for sql server limit rewrite? May new parse result cause
wrong limit rewrite.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
