[
https://issues.apache.org/jira/browse/TRAFODION-3296?focusedWorklogId=225909&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-225909
]
ASF GitHub Bot logged work on TRAFODION-3296:
---------------------------------------------
Author: ASF GitHub Bot
Created on: 11/Apr/19 01:44
Start Date: 11/Apr/19 01:44
Worklog Time Spent: 10m
Work Description: DaveBirdsall commented on 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:
us...@infra.apache.org
Issue Time Tracking
-------------------
Worklog Id: (was: 225909)
Time Spent: 40m (was: 0.5h)
> Subquery with [first n] + ORDER BY gives wrong answer
> -----------------------------------------------------
>
> Key: TRAFODION-3296
> URL: https://issues.apache.org/jira/browse/TRAFODION-3296
> Project: Apache Trafodion
> Issue Type: Bug
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
> Priority: Major
> Time Spent: 40m
> Remaining Estimate: 0h
>
> The following test script illustrates the problem:
> {quote}?section setup
> drop table if exists t1;
> drop table if exists t2;
> create table t1 (val integer);
> create table t2 (val integer);
> insert into t1 values(994707150),(1923979352),(-1865644273);
> insert into t2 select * from t1;
> ?section testit
> select [first 1] val from t2 order by val;
> prepare xx from select val from t1 where val in (select [first 1] val from t2
> order by val);
> explain options 'f' xx;
> -- should return -1865644273, but returns something different
> execute xx;
> {quote}
> When run, the script shows:
> {quote}>>?section testit
> >>
> >>select [first 1] val from t2 order by val;
> VAL
> -----------
> -1865644273
> --- 1 row(s) selected.
> >>
> >>prepare xx from select val from t1 where val in (select [first 1] val from
> >>t2 order by val);
> --- SQL command prepared.
> >>
> >>explain options 'f' xx;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> ---- ---- ---- -------------------- -------- -------------------- ---------
> 5 . 6 root 1.00E+002
> 3 4 5 nested_join 1.00E+002
> . . 4 trafodion_scan T1 1.00E+002
> 2 . 3 hash_groupby 1.00E+000
> 1 . 2 firstn 1.00E+000
> . . 1 trafodion_scan T2 1.00E+002
> --- SQL operation complete.
> >>
> >>-- should return -1865644273, but returns something different
> >>execute xx;
> VAL
> -----------
> 994707150
> --- 1 row(s) selected.
> >>
> {quote}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
