[
https://issues.apache.org/jira/browse/DRILL-6967?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Arina Ielchiieva updated DRILL-6967:
------------------------------------
Reviewer: Bohdan Kazydub
> 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
> Assignee: Volodymyr Vysotskyi
> Priority: Major
> 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)
