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

        Rahul Challapalli created issue -
        Rahul Challapalli made changes -
        Field Original Value New Value
        Attachment views.txt [ 12657633 ]
        Jacques Nadeau made changes -
        Fix Version/s 0.4.0 [ 12324963 ]
        Jacques Nadeau made changes -
        Fix Version/s 0.5.0 [ 12324880 ]
        Fix Version/s 0.4.0 [ 12324963 ]
        Jacques Nadeau made changes -
        Assignee Steven Phillips [ sphillips ]
        Hide
        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
        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.
        Sudheesh Katkam made changes -
        Due Date 11/Aug/14
        Jacques Nadeau made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Tony Stevenson made changes -
        Workflow no-reopen-closed, patch-avail, testing [ 12877608 ] Drill workflow [ 12934574 ]
        Hide
        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
        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
        Rahul Challapalli made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Transition Time In Source Status Execution Times Last Executer Last Execution Date
        Open Open Resolved Resolved
        21d 2h 52m 1 Jacques Nadeau 14/Aug/14 20:29
        Resolved Resolved Closed Closed
        260d 2h 31m 1 Rahul Challapalli Friday 23:01

          People

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

            Dates

            • Due:
              Created:
              Updated:
              Resolved:

              Development