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)