[TRAFODION-3296] Subquery with [first n] + ORDER BY gives wrong answer

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 2.4
• Assignee: Dave Birdsall
• Reporter: Dave Birdsall
• Created: 09/Apr/19 21:05
• Updated: 16/Apr/19 17:34
• Resolved: 16/Apr/19 17:34

Description

The following test script illustrates the problem:

?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;

When run, the script shows:

>>?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.
>>

Issue Links

• links to: GitHub Pull Request #1828