drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Volodymyr Vysotskyi (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DRILL-4722) date_add() function returns incorrect result with interval hour, minute and second
Date Tue, 27 Jun 2017 08:35:00 GMT

     [ https://issues.apache.org/jira/browse/DRILL-4722?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Volodymyr Vysotskyi updated DRILL-4722:
---------------------------------------
    Description: 
<b>Issue<b>
The following query returns the same data for the second column as the first:
{code:sql}
select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '3' HOUR), date_add(cast('2015-01-24
07:27:05.0' as timestamp), interval '5' HOUR) from (values(1));
+------------------------+------------------------+
|         EXPR$0         |         EXPR$1         |
+------------------------+------------------------+
| 2015-01-24 10:27:05.0  | 2015-01-24 10:27:05.0  |
+------------------------+------------------------+
{code}
If each column is run separately, then it produces correct result:
{code:sql}
select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '3' HOUR) from (values(1));
+------------------------+
|         EXPR$0         |
+------------------------+
| 2015-01-24 10:27:05.0  |
+------------------------+
{code}
{code:sql}
select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '5' HOUR) from (values(1));
+------------------------+
|         EXPR$0         |
+------------------------+
| 2015-01-24 12:27:05.0  |
+------------------------+
{code}
Same problem is seen for interval of minute and second:
{code:sql}
select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '50' MINUTE), date_add(cast('2015-01-24
07:27:05.0' as timestamp), interval '40' MINUTE) from (values(1));
+------------------------+------------------------+
|         EXPR$0         |         EXPR$1         |
+------------------------+------------------------+
| 2015-01-24 08:17:05.0  | 2015-01-24 08:17:05.0  |
+------------------------+------------------------+
{code}
{code:sql}
select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '50' second), date_add(cast('2015-01-24
07:27:05.0' as timestamp), interval '40' second) from (values(1));
+------------------------+------------------------+
|         EXPR$0         |         EXPR$1         |
+------------------------+------------------------+
| 2015-01-24 07:27:55.0  | 2015-01-24 07:27:55.0  |
+------------------------+------------------------+
{code}
{code:sql}
select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '3' HOUR), date_add(cast('2015-01-24
07:27:05.0' as timestamp), interval '50' MINUTE), date_add(cast('2015-01-24 07:27:05.0' as
timestamp), interval '50' second) from (values(1));
+------------------------+------------------------+------------------------+
|         EXPR$0         |         EXPR$1         |         EXPR$2         |
+------------------------+------------------------+------------------------+
| 2015-01-24 10:27:05.0  | 2015-01-24 10:27:05.0  | 2015-01-24 10:27:05.0  |
+------------------------+------------------------+------------------------+
{code}


  was:
The following query returns the same data for the second column as the first:
select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '3' HOUR), date_add(cast('2015-01-24
07:27:05.0' as timestamp), interval '5' HOUR) from (values(1));
+------------------------+------------------------+
|         EXPR$0         |         EXPR$1         |
+------------------------+------------------------+
| 2015-01-24 10:27:05.0  | 2015-01-24 10:27:05.0  |
+------------------------+------------------------+

If each column is run separately, then it produces correct result:
select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '3' HOUR) from (values(1));
+------------------------+
|         EXPR$0         |
+------------------------+
| 2015-01-24 10:27:05.0  |
+------------------------+

select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '5' HOUR) from (values(1));
+------------------------+
|         EXPR$0         |
+------------------------+
| 2015-01-24 12:27:05.0  |
+------------------------+

Same problem is seen for interval of minute and second:
select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '50' MINUTE), date_add(cast('2015-01-24
07:27:05.0' as timestamp), interval '40' MINUTE) from (values(1));
+------------------------+------------------------+
|         EXPR$0         |         EXPR$1         |
+------------------------+------------------------+
| 2015-01-24 08:17:05.0  | 2015-01-24 08:17:05.0  |
+------------------------+------------------------+

select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '50' second), date_add(cast('2015-01-24
07:27:05.0' as timestamp), interval '40' second) from (values(1));
+------------------------+------------------------+
|         EXPR$0         |         EXPR$1         |
+------------------------+------------------------+
| 2015-01-24 07:27:55.0  | 2015-01-24 07:27:55.0  |
+------------------------+------------------------+

select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '3' HOUR), date_add(cast('2015-01-24
07:27:05.0' as timestamp), interval '50' MINUTE), date_add(cast('2015-01-24 07:27:05.0' as
timestamp), interval '50' second) from (values(1));
+------------------------+------------------------+------------------------+
|         EXPR$0         |         EXPR$1         |         EXPR$2         |
+------------------------+------------------------+------------------------+
| 2015-01-24 10:27:05.0  | 2015-01-24 10:27:05.0  | 2015-01-24 10:27:05.0  |
+------------------------+------------------------+------------------------+



> date_add() function returns incorrect result with interval hour, minute and second  
> ------------------------------------------------------------------------------------
>
>                 Key: DRILL-4722
>                 URL: https://issues.apache.org/jira/browse/DRILL-4722
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Functions - Drill
>            Reporter: Krystal
>            Assignee: Volodymyr Vysotskyi
>             Fix For: Future
>
>
> <b>Issue<b>
> The following query returns the same data for the second column as the first:
> {code:sql}
> select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '3' HOUR), date_add(cast('2015-01-24
07:27:05.0' as timestamp), interval '5' HOUR) from (values(1));
> +------------------------+------------------------+
> |         EXPR$0         |         EXPR$1         |
> +------------------------+------------------------+
> | 2015-01-24 10:27:05.0  | 2015-01-24 10:27:05.0  |
> +------------------------+------------------------+
> {code}
> If each column is run separately, then it produces correct result:
> {code:sql}
> select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '3' HOUR) from (values(1));
> +------------------------+
> |         EXPR$0         |
> +------------------------+
> | 2015-01-24 10:27:05.0  |
> +------------------------+
> {code}
> {code:sql}
> select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '5' HOUR) from (values(1));
> +------------------------+
> |         EXPR$0         |
> +------------------------+
> | 2015-01-24 12:27:05.0  |
> +------------------------+
> {code}
> Same problem is seen for interval of minute and second:
> {code:sql}
> select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '50' MINUTE), date_add(cast('2015-01-24
07:27:05.0' as timestamp), interval '40' MINUTE) from (values(1));
> +------------------------+------------------------+
> |         EXPR$0         |         EXPR$1         |
> +------------------------+------------------------+
> | 2015-01-24 08:17:05.0  | 2015-01-24 08:17:05.0  |
> +------------------------+------------------------+
> {code}
> {code:sql}
> select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '50' second), date_add(cast('2015-01-24
07:27:05.0' as timestamp), interval '40' second) from (values(1));
> +------------------------+------------------------+
> |         EXPR$0         |         EXPR$1         |
> +------------------------+------------------------+
> | 2015-01-24 07:27:55.0  | 2015-01-24 07:27:55.0  |
> +------------------------+------------------------+
> {code}
> {code:sql}
> select date_add(cast('2015-01-24 07:27:05.0' as timestamp), interval '3' HOUR), date_add(cast('2015-01-24
07:27:05.0' as timestamp), interval '50' MINUTE), date_add(cast('2015-01-24 07:27:05.0' as
timestamp), interval '50' second) from (values(1));
> +------------------------+------------------------+------------------------+
> |         EXPR$0         |         EXPR$1         |         EXPR$2         |
> +------------------------+------------------------+------------------------+
> | 2015-01-24 10:27:05.0  | 2015-01-24 10:27:05.0  | 2015-01-24 10:27:05.0  |
> +------------------------+------------------------+------------------------+
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message