[jira] [Created] (DRILL-4922) Difference in results returned by AGE function

["Khurram Faraaz (JIRA)" <ji...@apache.org>]

Khurram Faraaz created DRILL-4922:
-------------------------------------

             Summary: Difference in results returned by AGE function
                 Key: DRILL-4922
                 URL: https://issues.apache.org/jira/browse/DRILL-4922
             Project: Apache Drill
          Issue Type: Bug
          Components: Functions - Drill
    Affects Versions: 1.9.0
            Reporter: Khurram Faraaz


We are seeing a difference in results returned by Drill 1.9.0 vs Postgres 9.3

TIMEOFDAY function returns a varchar value.

AGE(TIMESTAMP) accepts a timestamp value and returns INTERVALDAY or INTERVALYEAR value.
Returns the interval between two timestamps or subtracts a timestamp from midnight of the current date.

Results from Drill 1.9.0

{noformat}
0: jdbc:drill:schema=dfs.tmp> select AGE(cast(TIMEOFDAY() as timestamp)) FROM (VALUES(1)) as subquery;
+----------------+
|     EXPR$0     |
+----------------+
| PT-26120.983S  |
+----------------+
1 row selected (0.119 seconds)
{noformat}

Results from Postgres 9.3

{noformat}
postgres=# select AGE(cast(TIMEOFDAY() as timestamp)) FROM (VALUES(1)) as subquery;
       age
------------------
 -07:14:47.995843
(1 row)
{noformat}

Results from Drill 1.9.0 (Drill is not checking for datatype of input to AGE fn. in below query)

{noformat}
0: jdbc:drill:schema=dfs.tmp> select AGE('1992-02-02 15:12:15') FROM (VALUES(1));
+-----------+
|  EXPR$0   |
+-----------+
| P300M10D  |
+-----------+
1 row selected (0.127 seconds)
{noformat}

Results from Postgres, returns error (due to datatype of input)

{noformat}
postgres=# 0: jdbc:drill:schema=dfs.tmp> select AGE('1992-02-02 15:12:15') FROM (VALUES(1)) as subquery;
ERROR:  syntax error at or near "0"
LINE 1: 0: jdbc:drill:schema=dfs.tmp> select AGE('1992-02-02 15:12:1...
{noformat}

Results from Drill 1.9.0 are different from the result returned by Postgres 9.3

{noformat}
0: jdbc:drill:schema=dfs.tmp> select AGE(CAST ('1992-02-02 15:12:15' AS TIMESTAMP)) FROM (VALUES(1));
+-----------------+
|     EXPR$0      |
+-----------------+
| P300M9DT31665S  |
+-----------------+
1 row selected (0.154 seconds)
{noformat}

Results from postgres 9.3

{noformat}
postgres=# select AGE(CAST ('1992-02-02 15:12:15' AS TIMESTAMP)) FROM (VALUES(1)) as subquery;
           age
--------------------------
 24 years 8 mons 08:47:45
(1 row)
{noformat}

Default time zone format in /conf/drill-env.sh was not set to -Duser.timezone=UTC in DRILL_JAVA_OPTS. 




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)