[DRILL-6849] Runtime filter queries with nested broadcast returns wrong results

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 1.15.0
• Fix Version/s: 1.16.0
• Component/s: Execution - Flow
• Assignee: Weijie Tong
• Reporter: Abhishek Ravi
• Reviewer: Sorabh Hamirwasia
• Created: 13/Nov/18 23:55
• Updated: 27/Feb/19 18:48
• Resolved: 27/Feb/19 18:48

Description

Running few queries on TPC-H SF100 data with latest changes in [PR #1504|https://github.com/apache/drill/pull/1504.]

I noticed that for a couple of queries, there are vast discrepancies in the results returned by queries with Runtime Filter enabled and without.

Below are a couple of failing queries.

Query 1

select
p.p_mfgr,
p.p_type,
count(*) as num_parts
from
supplier s,
part p,
partsupp ps,
nation n
where
n.n_nationkey = 15
and n.n_nationkey = s.s_nationkey
and s.s_suppkey = ps.ps_suppkey
and ps.ps_partkey = p.p_partkey
and p.p_type like '%STEEL%'
group by
p.p_mfgr,
p.p_type
order by
p.p_mfgr,
p.p_type;

 

Expected Result (without Runtime filter)

+-----------------+---------------------------+------------+
| p_mfgr | p_type | num_parts |
+-----------------+---------------------------+------------+
| Manufacturer#1 | ECONOMY ANODIZED STEEL | 4448 |
| Manufacturer#1 | ECONOMY BRUSHED STEEL | 4308 |
| Manufacturer#1 | ECONOMY BURNISHED STEEL | 4268 |
| Manufacturer#1 | ECONOMY PLATED STEEL | 4266 |
| Manufacturer#1 | ECONOMY POLISHED STEEL | 4302 |
| Manufacturer#1 | LARGE ANODIZED STEEL | 4389 |
| Manufacturer#1 | LARGE BRUSHED STEEL | 4254 |
| Manufacturer#1 | LARGE BURNISHED STEEL | 4276 |
| Manufacturer#1 | LARGE PLATED STEEL | 4351 |
| Manufacturer#1 | LARGE POLISHED STEEL | 4281 |
| Manufacturer#1 | MEDIUM ANODIZED STEEL | 4230 |
| Manufacturer#1 | MEDIUM BRUSHED STEEL | 4247 |
| Manufacturer#1 | MEDIUM BURNISHED STEEL | 4286 |
| Manufacturer#1 | MEDIUM PLATED STEEL | 4269 |
| Manufacturer#1 | MEDIUM POLISHED STEEL | 4274 |
| Manufacturer#1 | PROMO ANODIZED STEEL | 4283 |
| Manufacturer#1 | PROMO BRUSHED STEEL | 4221 |
| Manufacturer#1 | PROMO BURNISHED STEEL | 4315 |
| Manufacturer#1 | PROMO PLATED STEEL | 4361 |
| Manufacturer#1 | PROMO POLISHED STEEL | 4213 |
| Manufacturer#1 | SMALL ANODIZED STEEL | 4354 |
| Manufacturer#1 | SMALL BRUSHED STEEL | 4159 |
| Manufacturer#1 | SMALL BURNISHED STEEL | 4222 |

...

...

150 rows

 

Actual Result

+-----------------+---------------------------+------------+
| p_mfgr | p_type | num_parts |
+-----------------+---------------------------+------------+
| Manufacturer#1 | ECONOMY ANODIZED STEEL | 63 |
| Manufacturer#1 | ECONOMY BRUSHED STEEL | 64 |
| Manufacturer#1 | ECONOMY BURNISHED STEEL | 58 |
| Manufacturer#1 | ECONOMY PLATED STEEL | 73 |
| Manufacturer#1 | ECONOMY POLISHED STEEL | 59 |
| Manufacturer#1 | LARGE ANODIZED STEEL | 60 |
| Manufacturer#1 | LARGE BRUSHED STEEL | 62 |
| Manufacturer#1 | LARGE BURNISHED STEEL | 47 |
| Manufacturer#1 | LARGE PLATED STEEL | 51 |
| Manufacturer#1 | LARGE POLISHED STEEL | 60 |
| Manufacturer#1 | MEDIUM ANODIZED STEEL | 54 |
| Manufacturer#1 | MEDIUM BRUSHED STEEL | 60 |
| Manufacturer#1 | MEDIUM BURNISHED STEEL | 53 |
| Manufacturer#1 | MEDIUM PLATED STEEL | 73 |
| Manufacturer#1 | MEDIUM POLISHED STEEL | 65 |
| Manufacturer#1 | PROMO ANODIZED STEEL | 71 |
| Manufacturer#1 | PROMO BRUSHED STEEL | 67 |
| Manufacturer#1 | PROMO BURNISHED STEEL | 64 |
| Manufacturer#1 | PROMO PLATED STEEL | 47 |
| Manufacturer#1 | PROMO POLISHED STEEL | 51 |
| Manufacturer#1 | SMALL ANODIZED STEEL | 48 |
| Manufacturer#1 | SMALL BRUSHED STEEL | 71 |
| Manufacturer#1 | SMALL BURNISHED STEEL | 33 |

...

...

150 rows

 

Query 2  (TPC-H 7 query)

select
 supp_nation,
 cust_nation,
 l_year,
 sum(volume) as revenue
from
 (
 select
 n1.n_name as supp_nation,
 n2.n_name as cust_nation,
 extract(year from l.l_shipdate) as l_year,
 l.l_extendedprice * (1 - l.l_discount) as volume
 from
 supplier s,
 lineitem l,
 orders o,
 customer c,
 nation n1,
 nation n2
 where
 s.s_suppkey = l.l_suppkey
 and o.o_orderkey = l.l_orderkey
 and c.c_custkey = o.o_custkey
 and s.s_nationkey = n1.n_nationkey
 and c.c_nationkey = n2.n_nationkey
 and (
 (n1.n_name = 'EGYPT' and n2.n_name = 'UNITED STATES')
 or (n1.n_name = 'UNITED STATES' and n2.n_name = 'EGYPT')
 )
 and l.l_shipdate between date '1995-01-01' and date '1996-12-31'
 ) as shipping
group by
 supp_nation,
 cust_nation,
 l_year
order by
 supp_nation,
 cust_nation,
 l_year;

Expected Result

EGYPT UNITED STATES 1996 5.2735809932832E9
UNITED STATES EGYPT 1996 5.320488357330402E9
EGYPT UNITED STATES 1995 5.282512709079098E9
UNITED STATES EGYPT 1995 5.3213732978949E9

Actual Result

EGYPT UNITED STATES 1996 1.6282961704599997E7
UNITED STATES EGYPT 1996 1.39809230059E7
EGYPT UNITED STATES 1995 1.3606895522500003E7
UNITED STATES EGYPT 1995 1.5241044473299999E7

Issue Links

• duplicates: DRILL-7016 Wrong query result with RuntimeFilter enabled when order of join and filter condition is swapped
• is part of: DRILL-6792 Find the right probe side fragment to any storage plugin