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

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

David Wayne Birdsall created TRAFODION-3296:
-----------------------------------------------

             Summary: 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


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)