[
https://issues.apache.org/jira/browse/DRILL-6849?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Abhishek Ravi resolved DRILL-6849.
----------------------------------
Resolution: Duplicate
> Runtime filter queries with nested broadcast returns wrong results
> ------------------------------------------------------------------
>
> Key: DRILL-6849
> URL: https://issues.apache.org/jira/browse/DRILL-6849
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Flow
> Affects Versions: 1.15.0
> Reporter: Abhishek Ravi
> Assignee: weijie.tong
> Priority: Major
> Fix For: 1.16.0
>
>
> 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.
> h3. Query 1
> {code:sql}
> 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;
> {code}
>
> h4. Expected Result (without Runtime filter)
> {noformat}
> +-----------------+---------------------------+------------+
> | 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
> {noformat}
>
> h4. Actual Result
> {noformat}
> +-----------------+---------------------------+------------+
> | 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
> {noformat}
>
> h3. Query 2 (TPC-H 7 query)
> {code:sql}
> 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;
> {code}
> h4. Expected Result
> {noformat}
> EGYPT UNITED STATES 1996 5.2735809932832E9
> UNITED STATES EGYPT 1996 5.320488357330402E9
> EGYPT UNITED STATES 1995 5.282512709079098E9
> UNITED STATES EGYPT 1995 5.3213732978949E9
> {noformat}
> h4. Actual Result
> {noformat}
> EGYPT UNITED STATES 1996 1.6282961704599997E7
> UNITED STATES EGYPT 1996 1.39809230059E7
> EGYPT UNITED STATES 1995 1.3606895522500003E7
> UNITED STATES EGYPT 1995 1.5241044473299999E7
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
