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: [email protected] With regards, Apache Git Services
