Re: [PR] Support parsing SQL Server Top sql [shardingsphere]

[via GitHub]

TherChenYang commented on code in PR #29491:
URL: https://github.com/apache/shardingsphere/pull/29491#discussion_r1434727891


##########
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_ &gt; ?" 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_ &gt; ?" 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_ &gt; ?" 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_ &gt; ?" 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_ &gt; ?" 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_ &gt; 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_ &gt; 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_ &gt; 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_ &gt; 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_ &gt; 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_ &gt; ?" 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_ &gt; ?" 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_ &gt; ?" 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_ &gt; ?" 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_ &gt; ?" 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_ &gt; 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_ &gt; 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_ &gt; 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_ &gt; 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_ &gt; 100" />

Review Comment:
   Thanks, I will refer to the official documentation and modify the engine



-- 
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: notifications-unsubscr...@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org