[DRILL-609] TPCH: query 01 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:39
• 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_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval '120' day (3)
group by
l_returnflag,
l_linestatus

order by
l_returnflag,
l_linestatus

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:27:24 INFO TestVerifier:192 - These rows are not expected:
2014-04-30 14:27:24 INFO TestVerifier:195 - N F 499596.0 7.237821561700001E8 6.87811474102097E8 7.152735291765119E8 25.562627916496112 37032.99309250921 0.0 19544 : 1 time(s).
2014-04-30 14:27:24 INFO TestVerifier:195 - N O 3.6357561E7 5.271068187394886E10 5.007347520096913E10 5.2078457678915245E10 25.50090690001515 36970.3802709618 0.0 1425736 : 1 time(s).
2014-04-30 14:27:24 INFO TestVerifier:195 - A F 1.8865717E7 2.7356549949989704E10 2.598835690045046E10 2.7027321931296932E10 25.519179575692974 37004.03879606534 0.0 739276 : 1 time(s).
2014-04-30 14:27:24 INFO TestVerifier:195 - R F 1.8872497E7 2.734543103391961E10 2.5979800081985153E10 2.7018232810784813E10 25.51844400003786 36974.643676062755 0.0 739563 : 1 time(s).
2014-04-30 14:27:24 INFO TestVerifier:202 - Total number of unexpected rows: 4
2014-04-30 14:27:24 INFO TestVerifier:206 - These rows are expected but are not in result set:
2014-04-30 14:27:24 INFO TestVerifier:209 - R F 18872497 2.7345E+10 2.5980E+10 2.7018E+10 25.518444 36975.1205 .049998932 739563 : 1 time(s).
2014-04-30 14:27:24 INFO TestVerifier:209 - N F 499596 723782156 687811474 715273529 25.5626279 37033.4709 .049806079 19544 : 1 time(s).
2014-04-30 14:27:24 INFO TestVerifier:209 - N O 36723890 5.3242E+10 5.0579E+10 5.2604E+10 25.5009482 36971.3264 .050030831 1440099 : 1 time(s).
2014-04-30 14:27:24 INFO TestVerifier:209 - A F 18865717 2.7357E+10 2.5988E+10 2.7027E+10 25.5191796 37004.5152 .050007873 739276 : 1 time(s).
2014-04-30 14:27:24 INFO TestVerifier:216 - Total number of expected but missing: 4