[jira] [Created] (DRILL-8421) Parquet TIMESTAMP_MICROS columns in WHERE clauses are not converted to milliseconds before filtering

["Peter Franzen (Jira)" <ji...@apache.org>]

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)