drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Khurram Faraaz (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-4922) Difference in results returned by AGE function
Date Mon, 03 Oct 2016 08:46:20 GMT
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)

Mime
View raw message