Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
1.15.0
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
- links to
Merged with commit id 0f05f53e0e34ccc0606a561f44d82b794be32b83