[DRILL-3094] TPCH query 15 returns non-deterministic result

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 1.0.0
• Fix Version/s: 1.1.0
• Component/s: Query Planning & Optimization
• Assignee: Aman Sinha
• Reporter: Abhishek Girish
• Created: 15/May/15 00:40
• Updated: 25/Apr/16 22:20
• Resolved: 23/Jun/15 22:09

Description

Query 15:

create or replace view revenue0 (supplier_no, total_revenue) as
  select
    l_suppkey,
    sum(l_extendedprice * (1 - l_discount))
  from
    lineitem
  where
    l_shipdate >= date '1993-05-01'
    and l_shipdate < date '1993-05-01' + interval '3' month
  group by
    l_suppkey;

select
  s.s_suppkey,
  s.s_name,
  s.s_address,
  s.s_phone,
  r.total_revenue
from
  supplier s,
  revenue0 r
where
  s.s_suppkey = r.supplier_no
  and r.total_revenue = (
    select
      max(total_revenue)
    from
      revenue0
  )
order by
  s.s_suppkey;

Drill sometimes returns 0 rows and other times 1 row. Postgres always returns 1 row.

This is possibly due to the non-deterministic comparison of floating point values.

total_revenue (calculated as sum(l_extendedprice * (1 - l_discount)))

is compared with

max(total_revenue)