[DRILL-610] TPCH: query 03 verification fails

• Type: Bug
• Status: Resolved
• Priority: Blocker
• Resolution: Fixed
• Fix Version/s: 0.4.0
• Component/s: Query Planning & Optimization
• Assignee: Unassigned
• Reporter: Zhiyong Liu
• Created: 30/Apr/14 21:44
• Updated: 20/Apr/15 18:05
• Resolved: 29/May/14 16:58

Description

1. Set up drill test framework (see https://github.com/zhiyongliu/incubator-drill/tree/master/testing) and generate TCP H dataset with a scale factor of about 500MB.
2. Query file (modified from original by drill dev, and further modified as follows):

select
l.l_orderkey,
sum(l.l_extendedprice * (1 - l.l_discount)) as revenue,
o.o_orderdate,
o.o_shippriority

from
customer c,
orders o,
lineitem l

where
c.c_mktsegment = 'HOUSEHOLD'
and c.c_custkey = o.o_custkey
and l.l_orderkey = o.o_orderkey
and o.o_orderdate < date '1995-03-25'
and l.l_shipdate > date '1995-03-25'

group by
l.l_orderkey,
o.o_orderdate,
o.o_shippriority
order by
revenue desc,
o.o_orderdate

3. Drill build:
git.commit.id=1e0e4dfc96c8e7eb83659784dfbb85cd83d7d243

4. The baseline/expected results are generated via Oracle DB.
5. Run the above query in the test framework, with verification turned on.
6. Verification fails:

2014-04-30 14:42:21 INFO TestVerifier:192 - These rows are not expected:
2014-04-30 14:42:21 INFO TestVerifier:195 - 0 32217.824999999997 1995-03-17 2474884 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:195 - 0 21021.273 1995-02-10 2299361 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:195 - 0 49270.920999999995 1995-02-18 2627745 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:195 - 0 160321.524 1995-02-10 1828645 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:195 - 0 195687.96269999997 1995-03-16 799905 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:195 - 0 125110.5975 1995-01-02 1510471 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:195 - 0 179264.41250000003 1995-02-10 194212 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:195 - 0 48017.97 1994-12-15 521921 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:195 - 0 102168.95479999999 1995-02-11 2576902 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:195 - 0 154239.8077 1995-02-20 263687 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:202 - Total number of unexpected rows: 5853
2014-04-30 14:42:21 INFO TestVerifier:206 - These rows are expected but are not in result set:
2014-04-30 14:42:21 INFO TestVerifier:209 - 241477 11991.627 21-MAR-95 0 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:209 - 191553 119125.349 07-FEB-95 0 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:209 - 253728 283348.33 26-MAR-95 0 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:209 - 110823 112230.552 28-JAN-95 0 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:209 - 380135 122101.606 09-MAR-95 0 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:209 - 385542 70513.9196 04-MAR-95 0 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:209 - 21956 281118.193 02-FEB-95 0 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:209 - 43491 138577.744 09-MAR-95 0 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:209 - 234887 126183.41 01-MAR-95 0 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:209 - 198406 42139.1435 23-FEB-95 0 : 1 time(s).
2014-04-30 14:42:21 INFO TestVerifier:216 - Total number of expected but missing: 788