trafodion-codereview mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From GitBox <...@apache.org>
Subject [GitHub] [trafodion] DaveBirdsall commented on a change in pull request #1828: [TRAFODION-3296] Fix handling of [first n] + ORDER BY subqueries
Date Thu, 11 Apr 2019 01:44:53 GMT
DaveBirdsall commented on a change in pull request #1828: [TRAFODION-3296] Fix handling of
[first n] + ORDER BY subqueries
URL: https://github.com/apache/trafodion/pull/1828#discussion_r274231843
 
 

 ##########
 File path: core/sql/regress/core/TEST002
 ##########
 @@ -613,8 +623,44 @@ select
 (select count(*) from t002sub limit 20) as result_value
 from t002main;
 
--- Should return 7
+-- Should return 2
 select x from (select x from t002sol order by x desc limit 1);
+
+-- Tests of [first/any/last n] and limit n with ORDER BY in subqueries
+-- (Note that limit n is the same as [any n])
+
+prepare xx from select val from t002x1 where val in (select [first 1] val from t002x2 order
by val);
+
+-- query plan should be a sequence plan, not a firstn plan; the ORDER BY causes a rewrite
+explain options 'f' xx;
+
+-- should return one row, -1865644273
+execute xx;
+
+prepare xx from select val from t1 where val in (select [any 1] val from t2 order by val);
+
+-- query plan should be a firstn plan
+explain options 'f' xx;
+
+-- should fail with error 4484
+prepare xx from select val from t002x1 where val in (select [last 1] val from t002x2 order
by val);
+
+-- limit n has same semantics as [any n]
+prepare xx from select val from t1 where val in (select val from t2 order by val limit 1);
 
 Review comment:
   Another example where t1 should be t002x1, and t2 should be t002x2

----------------------------------------------------------------
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.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services

Mime
View raw message