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

TIMESTAMPDIFF returns incorrect value for SQL_TSI_QUARTER

    XMLWordPrintableJSON

    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

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: