[jira] [Work logged] (TRAFODION-3296) Subquery with [first n] + ORDER BY gives wrong answer

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TRAFODION-3296?focusedWorklogId=225880&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-225880 ]

ASF GitHub Bot logged work on TRAFODION-3296:
---------------------------------------------

                Author: ASF GitHub Bot
            Created on: 10/Apr/19 23:13
            Start Date: 10/Apr/19 23:13
    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
 
 
   Queries containing IN-subqueries involving both [first n] and ORDER BY were giving wrong results. The ORDER BY essentially was being ignored. This has now been fixed. The [first n] is now evaluated with respect to the ORDER BY clause.
   
   For [any n] + ORDER BY (or equivalently, LIMIT n + ORDER BY), the ORDER BY continues to be ignored, since [any n] does not specify which rows should be returned and therefore ordering has no importance.
   
   The combination [last n] + ORDER BY in an IN-subquery is now forbidden, and results in a 4484 error. The reason is that [last n] is typically used as a performance tool when used at the top level of a query, but that purpose makes no sense in the context of a subquery.
 
----------------------------------------------------------------
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


Issue Time Tracking
-------------------

            Worklog Id:     (was: 225880)
            Time Spent: 10m
    Remaining Estimate: 0h

> 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: 10m
>  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)