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

TIMESTAMPDIFF returns incorrect value for SQL_TSI_QUARTER

Details

    Description

      When checking the fix for DRILL-3610, I noticed that the value returned by TIMESTAMPDIFF for SQL_TSI_QUARTER is incorrect.

      For example, consider the following queries on orders table with TPC-H SF100 data

      Let's get a row from orders table

      0: jdbc:drill:drillbits=10.10.100.188> select * from orders limit 1;
      +-------------+------------+----------------+---------------+--------------+------------------+------------------+-----------------+--------------------------------------------+
      | o_orderkey  | o_custkey  | o_orderstatus  | o_totalprice  | o_orderdate  | o_orderpriority  |     o_clerk      | o_shippriority  |                 o_comment                  |
      +-------------+------------+----------------+---------------+--------------+------------------+------------------+-----------------+--------------------------------------------+
      | 456460071   | 9573185    | O              | 234213.28     | 1998-03-09   | 4-NOT SPECIFIED  | Clerk#000065824  | 0               | r deposits. quickly even ideas haggle flu  |
      +-------------+------------+----------------+---------------+--------------+------------------+------------------+-----------------+--------------------------------------------+
      

      Now let's use TIMESTAMPADD to get the date 8 quarters / 2 years ago

      0: jdbc:drill:drillbits=10.10.100.188> select cast(TIMESTAMPADD(SQL_TSI_QUARTER,-8,o_orderdate) as DATE) AS quarterdate from orders where o_orderkey = 456460071;
      +--------------+
      | quarterdate  |
      +--------------+
      | 1996-03-09   |
      +--------------+
      

      So far, so good.

      Now let's query the difference between the date in the row and the date returned by TIMESTAMPADD (a date from 8 quarters ago)

      0: jdbc:drill:drillbits=10.10.100.188> select TIMESTAMPDIFF(SQL_TSI_QUARTER,TO_DATE('1996-03-09','yyyy-MM-dd'),o_orderdate) AS quarterdiff from orders where o_orderkey = 456460071;
      +--------------+
      | quarterdiff  |
      +--------------+
      | 6            |
      +--------------+
      

      6 is incorrect!

      Attachments

        Issue Links

          Activity

            Merged with commit id 0f05f53e0e34ccc0606a561f44d82b794be32b83

            vitalii Vitalii Diravka added a comment - Merged with commit id 0f05f53e0e34ccc0606a561f44d82b794be32b83

            People

              volodymyr Vova Vysotskyi
              aravi5 Abhishek Ravi
              Bohdan Kazydub Bohdan Kazydub
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: