You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "Abhishek Ravi (JIRA)" <ji...@apache.org> on 2019/01/11 01:26:00 UTC
[jira] [Created] (DRILL-6967) TIMESTAMPDIFF returns incorrect value
for SQL_TSI_QUARTER
Abhishek Ravi created DRILL-6967:
------------------------------------
Summary: TIMESTAMPDIFF returns incorrect value for SQL_TSI_QUARTER
Key: DRILL-6967
URL: https://issues.apache.org/jira/browse/DRILL-6967
Project: Apache Drill
Issue Type: Bug
Components: Functions - Drill
Affects Versions: 1.15.0
Reporter: Abhishek Ravi
Fix For: 1.16.0
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
{noformat}
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 |
+-------------+------------+----------------+---------------+--------------+------------------+------------------+-----------------+--------------------------------------------+
{noformat}
Now let's use {{TIMESTAMPADD}} to get the date 8 quarters / 2 years ago
{noformat}
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 |
+--------------+
{noformat}
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)
{noformat}
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 |
+--------------+
{noformat}
*6 is incorrect!*
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)