Peter Franzen created DRILL-8421: ------------------------------------ Summary: Parquet TIMESTAMP_MICROS columns in WHERE clauses are not converted to milliseconds before filtering Key: DRILL-8421 URL: https://issues.apache.org/jira/browse/DRILL-8421 Project: Apache Drill Issue Type: Bug Components: Storage - Parquet Affects Versions: 1.21.0 Reporter: Peter Franzen
When using Drill with parquet files where the timestamp columns are in microseconds, Drill converts the microsecond values to milliseconds when displayed. However, when using a timestamp column in WHERE clauses it looks like the original microsecond value is used instead of the adjusted millisecond value when filtering records. *To Reproduce* Assume a parquet file in a directory "Test" with a column _timestampCol_ having the type {{{}org.apache.parquet.schema.OriginalType.TIMESTAMP_MICROS{}}}. Assume there are two records with the values 1673981999806149 and 1674759597743552, respectively, in that column (i.e. the UTC dates 2023-01-17T18:59:59.806149 and 2023-01-26T18:59:57.743552) # Execute the query {{SELECT timestampCol FROM dfs.Test;}} The result includes both records, as expected. # Execute the query {{SELECT timestampCol FROM dfs.Test WHERE timestampCol < TO_TIMESTAMP('2023-02-01 00:00:00', 'yyyy-MM-dd HH:mm:ss')}} This produces an empty result although both records have a value less than the argument. # Execute {{SELECT timestampCol FROM dfs.Test WHERE timestampCol > TO_TIMESTAMP('2023-02-01 00:00:00', 'yyyy-MM-dd HH:mm:ss')}} The result includes both records although neither have a value greater than the argument. *Expected behavior* The query in 2) above should produce a result with both records, and the query in 3) should produce an empty result. *Additional context* Even timestamps long into the future produce results with both records, e.g.: {{SELECT timestampCol FROM dfs.Test WHERE timestampCol > TO_TIMESTAMP('2502-04-04 00:00:00', 'yyyy-MM-dd HH:mm:ss')}} Manually converting the timestamp column to milliseconds produces the expected result: {{SELECT timestampCol FROM dfs.Test WHERE TO_TIMESTAMP(CONVERT_FROM(CONVERT_TO(timestampCol, 'TIMESTAMP_EPOCH'), 'BIGINT')/1000) < TO_TIMESTAMP('2023-02-01 00:00:00', 'yyyy-MM-dd HH:mm:ss')}} produces a result with both records. -- This message was sent by Atlassian Jira (v8.20.10#820010)