[DRILL-6967] TIMESTAMPDIFF returns incorrect value for SQL_TSI_QUARTER - ASF JIRA

Attach files

Attach Screenshot

Voters

Watch issue

Watchers

Create sub-task

Link

Clone

Update Comment Author

Replace String in Comment

Update Comment Visibility

Delete Comments

XML

Word

Printable

JSON

Details

Type: Bug
Status: Resolved
Priority: Major
Resolution: Fixed
Affects Version/s: 1.15.0
Fix Version/s: 1.16.0
Component/s: Functions - Drill
Labels:
- ready-to-commit

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!