Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-1184

TPCH Query 19 on top of text data (0.01 GB) does not match the baselines generated through postgres

    Details

      Description

      git.commit.id.abbrev=e5c2da0

      Query :

      select
      sum(l.l_extendedprice* (1 - l.l_discount)) as revenue
      from
      lineitem l,
      part p
      where
      p.p_partkey = l.l_partkey
      and (
      (
      p.p_brand = 'Brand#41'
      and p.p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
      and l.l_quantity >= 2 and l.l_quantity <= 2 + 10
      and p.p_size between 1 and 5
      and l.l_shipmode in ('AIR', 'AIR REG')
      and l.l_shipinstruct = 'DELIVER IN PERSON'
      )
      or
      (
      p.p_brand = 'Brand#13'
      and p.p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
      and l.l_quantity >= 14 and l.l_quantity <= 14 + 10
      and p.p_size between 1 and 10
      and l.l_shipmode in ('AIR', 'AIR REG')
      and l.l_shipinstruct = 'DELIVER IN PERSON'
      )
      or
      (
      p.p_brand = 'Brand#55'
      and p.p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
      and l.l_quantity >= 23 and l.l_quantity <= 23 + 10
      and p.p_size between 1 and 15
      and l.l_shipmode in ('AIR', 'AIR REG')
      and l.l_shipinstruct = 'DELIVER IN PERSON'
      ))

      Results :

      • Drill : 108600
      • Postgres : 106598.6290

      Attached the DDL for the views created before running this query.

      1. views.txt
        2 kB
        Rahul Challapalli

        Activity

        Hide
        sphillips Steven Phillips added a comment -

        The problem is that that the view is casting the decimal types as decimal, without specifying a precision or scale. It looks like drill is defaulting to 38 precision, 0 scale. So the decimal points are getting truncated from the extendedprice and discount fields.

        If you modify the view to cast as, e.g. decimal(16,2), the answer matches postrgres.

        Show
        sphillips Steven Phillips added a comment - The problem is that that the view is casting the decimal types as decimal, without specifying a precision or scale. It looks like drill is defaulting to 38 precision, 0 scale. So the decimal points are getting truncated from the extendedprice and discount fields. If you modify the view to cast as, e.g. decimal(16,2), the answer matches postrgres.
        Hide
        rkins Rahul Challapalli added a comment -

        Verified! Below is the testcase

        Functional/Passing/tpch0.01/text_double/19.q
        Functional/Passing/tpch0.01/text_decimal/19.q

        Show
        rkins Rahul Challapalli added a comment - Verified! Below is the testcase Functional/Passing/tpch0.01/text_double/19.q Functional/Passing/tpch0.01/text_decimal/19.q

          People

          • Assignee:
            sphillips Steven Phillips
            Reporter:
            rkins Rahul Challapalli
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Due:
              Created:
              Updated:
              Resolved:

              Development