[DRILL-661] join query produces inconsistent result set

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.4.0
• Assignee: Unassigned
• Reporter: Zhiyong Liu
• Created: 07/May/14 22:33
• Updated: 31/Jul/14 06:31
• Resolved: 20/May/14 16:02

Description

In sqlline, submit the following query multiple times:

select count
from (select l.l_orderkey as x, c.c_custkey as y
from lineitem l
left outer join customer c
on l.l_orderkey = c.c_custkey) as foo
where y < 10000;

Observation:
Exactly the same query executed in Postgres produces the following:

count
-------
1467
(1 row)

In drill via, results are different each time it is submitted:

0: jdbc:drill:schema=dfs.TpcH001> select count
. . . . . . . . . . . . . . . . > from (select l.l_orderkey as x, c.c_custkey as y
. . . . . . . . . . . . . . . . > from lineitem l
. . . . . . . . . . . . . . . . > left outer join customer c
. . . . . . . . . . . . . . . . > on l.l_orderkey = c.c_custkey) as foo
. . . . . . . . . . . . . . . . > where y < 10000;
------------

EXPR$0

------------

45748

------------
1 row selected (3.592 seconds)
0: jdbc:drill:schema=dfs.TpcH001> select count
. . . . . . . . . . . . . . . . > from (select l.l_orderkey as x, c.c_custkey as y
. . . . . . . . . . . . . . . . > from lineitem l
. . . . . . . . . . . . . . . . > left outer join customer c
. . . . . . . . . . . . . . . . > on l.l_orderkey = c.c_custkey) as foo
. . . . . . . . . . . . . . . . > where y < 10000;
------------

EXPR$0

------------

32292

------------
1 row selected (3.532 seconds)
0: jdbc:drill:schema=dfs.TpcH001> select count
. . . . . . . . . . . . . . . . > from (select l.l_orderkey as x, c.c_custkey as y
. . . . . . . . . . . . . . . . > from lineitem l
. . . . . . . . . . . . . . . . > left outer join customer c
. . . . . . . . . . . . . . . . > on l.l_orderkey = c.c_custkey) as foo
. . . . . . . . . . . . . . . . > where y < 10000;
------------

EXPR$0

------------

35970

------------
1 row selected (3.57 seconds)