This is an automated email from the ASF dual-hosted git repository. stigahuang pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/impala.git
commit 96964be7a31a78058dea08b43c3b4115fb93dea7 Author: gaurav1086 <[email protected]> AuthorDate: Tue Feb 6 14:34:51 2024 -0800 IMPALA-12815: Support timestamp for scan predicates for external data source table. Binary SCAN predicates involving timestamp literals are pushed down to remote Database. The current logic assumes ISO 8601 (SQL standard) format for timestamp literals - 'yyyy-mm-dd hh:mm:ss.ms' Testing: - Added custom cluster tests for timestamp predicates with operators: '=', '>', '<', '>=', '<=', '!=', 'BETWEEN' for postgres, mysql and remote impala. - Added coverage for timestamp with/without time in the timestamp - Added coverage for timestamp with/without milliseconds in timestamp. - Added Planner tests to check predicate pushdown for date/timestamp literals, date/timestamp functions and CASTs Change-Id: If6ffe672b4027e2cee094cec4f99b9df9308e441 Reviewed-on: http://gerrit.cloudera.org:8080/21015 Tested-by: Impala Public Jenkins <[email protected]> Reviewed-by: Wenzhe Zhou <[email protected]> --- .../apache/impala/planner/DataSourceScanNode.java | 11 +- .../queries/PlannerTest/data-source-tables.test | 22 ++++ .../impala-ext-jdbc-tables-predicates.test | 141 +++++++++++++++++++++ .../queries/QueryTest/jdbc-data-source.test | 141 +++++++++++++++++++++ .../queries/QueryTest/mysql-ext-jdbc-tables.test | 141 +++++++++++++++++++++ 5 files changed, 454 insertions(+), 2 deletions(-) diff --git a/fe/src/main/java/org/apache/impala/planner/DataSourceScanNode.java b/fe/src/main/java/org/apache/impala/planner/DataSourceScanNode.java index c1fc8ab5e..ec5097647 100644 --- a/fe/src/main/java/org/apache/impala/planner/DataSourceScanNode.java +++ b/fe/src/main/java/org/apache/impala/planner/DataSourceScanNode.java @@ -35,6 +35,7 @@ import org.apache.impala.analysis.LiteralExpr; import org.apache.impala.analysis.NumericLiteral; import org.apache.impala.analysis.SlotRef; import org.apache.impala.analysis.StringLiteral; +import org.apache.impala.analysis.TimestampLiteral; import org.apache.impala.analysis.TupleDescriptor; import org.apache.impala.catalog.DataSource; import org.apache.impala.catalog.FeDataSourceTable; @@ -140,10 +141,16 @@ public class DataSourceScanNode extends ScanNode { case DATE: return new TColumnValue().setDate_val( (int) ((DateLiteral) expr).getValue()); + case TIMESTAMP: + /** + * timestamp is of the ISO 8601 format(SQL standard): 'yyyy-mm-dd hh:mm:ss.ms' + * We are using string representation and not binary representation here as all + * Literals will finally be converted to string for pushdown. + */ + return new TColumnValue().setString_val( + ((TimestampLiteral) expr).getStringValue()); case DECIMAL: case DATETIME: - case TIMESTAMP: - // TODO: we support DECIMAL, TIMESTAMP and DATE but no way to specify it in SQL. return null; default: Preconditions.checkState(false); diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/data-source-tables.test b/testdata/workloads/functional-planner/queries/PlannerTest/data-source-tables.test index cbbebcd94..2754a83b8 100644 --- a/testdata/workloads/functional-planner/queries/PlannerTest/data-source-tables.test +++ b/testdata/workloads/functional-planner/queries/PlannerTest/data-source-tables.test @@ -103,3 +103,25 @@ PLAN-ROOT SINK | 00:EMPTYSET ==== +# IMPALA-12815: Support timestamp as external datasource scan predicates +select * from functional.alltypes_jdbc_datasource where date_col > DATE '2009-01-02' +and timestamp_col > '2009-01-02 00:12:00' limit 15 +---- PLAN +PLAN-ROOT SINK +| +00:SCAN DATA SOURCE [functional.alltypes_jdbc_datasource] +data source predicates: date_col > DATE '2009-01-02', timestamp_col > TIMESTAMP '2009-01-02 00:12:00' + limit: 15 + row-size=64B cardinality=1 +==== +# IMPALA-12815: use timestamp with cast() and to_timestamp() +select * from functional.alltypes_jdbc_datasource where timestamp_col between +date_add(cast('2009-01-01' as timestamp), interval 1 day) and to_timestamp('Jan 04, 2009', 'MMM dd, yyyy') limit 15; +---- PLAN +PLAN-ROOT SINK +| +00:SCAN DATA SOURCE [functional.alltypes_jdbc_datasource] +data source predicates: timestamp_col >= TIMESTAMP '2009-01-02 00:00:00', timestamp_col <= TIMESTAMP '2009-01-04 00:00:00' + limit: 15 + row-size=64B cardinality=1 +==== diff --git a/testdata/workloads/functional-query/queries/QueryTest/impala-ext-jdbc-tables-predicates.test b/testdata/workloads/functional-query/queries/QueryTest/impala-ext-jdbc-tables-predicates.test index 4a8f49e8d..69267209b 100644 --- a/testdata/workloads/functional-query/queries/QueryTest/impala-ext-jdbc-tables-predicates.test +++ b/testdata/workloads/functional-query/queries/QueryTest/impala-ext-jdbc-tables-predicates.test @@ -150,6 +150,147 @@ where date_col between DATE '2009-03-01' and DATE '2009-04-01' order by id limit INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE ==== ---- QUERY +# Gets specified columns based on timestamp predicate with operator '='. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col = '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +12,true,2,2.200000047683716,20.2,2009-01-02,2009-01-02 00:12:00.460000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '=' +# with empty result. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col = '1990-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '>'. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col > '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +13,false,3,3.299999952316284,30.3,2009-01-02,2009-01-02 00:13:00.480000000 +14,true,4,4.400000095367432,40.4,2009-01-02,2009-01-02 00:14:00.510000000 +15,false,5,5.5,50.5,2009-01-02,2009-01-02 00:15:00.550000000 +16,true,6,6.599999904632568,60.59999999999999,2009-01-02,2009-01-02 00:16:00.600000000 +17,false,7,7.699999809265137,70.7,2009-01-02,2009-01-02 00:17:00.660000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '>' +# with empty result. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col > '2990-01-01 00:12:00.460000000' order by id limit 5; +---- RESULTS +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '<'. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col < '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +0,true,0,0,0,2009-01-01,2009-01-01 00:00:00 +1,false,1,1.100000023841858,10.1,2009-01-01,2009-01-01 00:01:00 +2,true,2,2.200000047683716,20.2,2009-01-01,2009-01-01 00:02:00.100000000 +3,false,3,3.299999952316284,30.3,2009-01-01,2009-01-01 00:03:00.300000000 +4,true,4,4.400000095367432,40.4,2009-01-01,2009-01-01 00:04:00.600000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '<' +# with empty result. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col < '1990-01-01 00:12:00.460000000' order by id limit 5; +---- RESULTS +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '>='. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col >= '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +12,true,2,2.200000047683716,20.2,2009-01-02,2009-01-02 00:12:00.460000000 +13,false,3,3.299999952316284,30.3,2009-01-02,2009-01-02 00:13:00.480000000 +14,true,4,4.400000095367432,40.4,2009-01-02,2009-01-02 00:14:00.510000000 +15,false,5,5.5,50.5,2009-01-02,2009-01-02 00:15:00.550000000 +16,true,6,6.599999904632568,60.59999999999999,2009-01-02,2009-01-02 00:16:00.600000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '<='. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col <= '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +0,true,0,0,0,2009-01-01,2009-01-01 00:00:00 +1,false,1,1.100000023841858,10.1,2009-01-01,2009-01-01 00:01:00 +2,true,2,2.200000047683716,20.2,2009-01-01,2009-01-01 00:02:00.100000000 +3,false,3,3.299999952316284,30.3,2009-01-01,2009-01-01 00:03:00.300000000 +4,true,4,4.400000095367432,40.4,2009-01-01,2009-01-01 00:04:00.600000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '!='. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col != '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +0,true,0,0,0,2009-01-01,2009-01-01 00:00:00 +1,false,1,1.100000023841858,10.1,2009-01-01,2009-01-01 00:01:00 +2,true,2,2.200000047683716,20.2,2009-01-01,2009-01-01 00:02:00.100000000 +3,false,3,3.299999952316284,30.3,2009-01-01,2009-01-01 00:03:00.300000000 +4,true,4,4.400000095367432,40.4,2009-01-01,2009-01-01 00:04:00.600000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator 'between'. +select count(*) from alltypes_jdbc_datasource where timestamp_col between +'2009-01-03 00:12:00' and '2009-01-05 00:12:00' +---- RESULTS +20 +---- TYPES +BIGINT +==== +---- QUERY +# Gets specified columns based on timestamp predicate without time in timestamp. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col > '2009-01-03' order by id limit 5; +---- RESULTS +20,true,0,0,0,2009-01-03,2009-01-03 00:20:00.900000000 +21,false,1,1.100000023841858,10.1,2009-01-03,2009-01-03 00:21:00.900000000 +22,true,2,2.200000047683716,20.2,2009-01-03,2009-01-03 00:22:00.910000000 +23,false,3,3.299999952316284,30.3,2009-01-03,2009-01-03 00:23:00.930000000 +24,true,4,4.400000095367432,40.4,2009-01-03,2009-01-03 00:24:00.960000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate without milisecond in timestamp. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col > '2009-01-03 00:22:00' order by id limit 5; +---- RESULTS +22,true,2,2.200000047683716,20.2,2009-01-03,2009-01-03 00:22:00.910000000 +23,false,3,3.299999952316284,30.3,2009-01-03,2009-01-03 00:23:00.930000000 +24,true,4,4.400000095367432,40.4,2009-01-03,2009-01-03 00:24:00.960000000 +25,false,5,5.5,50.5,2009-01-03,2009-01-03 00:25:01 +26,true,6,6.599999904632568,60.59999999999999,2009-01-03,2009-01-03 00:26:01.500000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY # Drop table DROP TABLE alltypes_jdbc_datasource; ---- RESULTS diff --git a/testdata/workloads/functional-query/queries/QueryTest/jdbc-data-source.test b/testdata/workloads/functional-query/queries/QueryTest/jdbc-data-source.test index 7117b8380..84148d614 100644 --- a/testdata/workloads/functional-query/queries/QueryTest/jdbc-data-source.test +++ b/testdata/workloads/functional-query/queries/QueryTest/jdbc-data-source.test @@ -281,6 +281,147 @@ where date_col between DATE '2009-03-01' and DATE '2009-04-01' order by id limit INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE ==== ---- QUERY +# Gets specified columns based on timestamp predicate with operator '='. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col = '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +12,true,2,2.200000047683716,20.2,2009-01-02,2009-01-02 00:12:00.460000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '=' +# with empty result. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col = '1990-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '>'. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col > '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +13,false,3,3.299999952316284,30.3,2009-01-02,2009-01-02 00:13:00.480000000 +14,true,4,4.400000095367432,40.4,2009-01-02,2009-01-02 00:14:00.510000000 +15,false,5,5.5,50.5,2009-01-02,2009-01-02 00:15:00.550000000 +16,true,6,6.599999904632568,60.59999999999999,2009-01-02,2009-01-02 00:16:00.600000000 +17,false,7,7.699999809265137,70.7,2009-01-02,2009-01-02 00:17:00.660000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '>' +# with empty result. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col > '2990-01-01 00:12:00.460000000' order by id limit 5; +---- RESULTS +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '<'. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col < '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +0,true,0,0,0,2009-01-01,2009-01-01 00:00:00 +1,false,1,1.100000023841858,10.1,2009-01-01,2009-01-01 00:01:00 +2,true,2,2.200000047683716,20.2,2009-01-01,2009-01-01 00:02:00.100000000 +3,false,3,3.299999952316284,30.3,2009-01-01,2009-01-01 00:03:00.300000000 +4,true,4,4.400000095367432,40.4,2009-01-01,2009-01-01 00:04:00.600000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '<' +# with empty result. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col < '1990-01-01 00:12:00.460000000' order by id limit 5; +---- RESULTS +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '>='. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col >= '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +12,true,2,2.200000047683716,20.2,2009-01-02,2009-01-02 00:12:00.460000000 +13,false,3,3.299999952316284,30.3,2009-01-02,2009-01-02 00:13:00.480000000 +14,true,4,4.400000095367432,40.4,2009-01-02,2009-01-02 00:14:00.510000000 +15,false,5,5.5,50.5,2009-01-02,2009-01-02 00:15:00.550000000 +16,true,6,6.599999904632568,60.59999999999999,2009-01-02,2009-01-02 00:16:00.600000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '<='. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col <= '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +0,true,0,0,0,2009-01-01,2009-01-01 00:00:00 +1,false,1,1.100000023841858,10.1,2009-01-01,2009-01-01 00:01:00 +2,true,2,2.200000047683716,20.2,2009-01-01,2009-01-01 00:02:00.100000000 +3,false,3,3.299999952316284,30.3,2009-01-01,2009-01-01 00:03:00.300000000 +4,true,4,4.400000095367432,40.4,2009-01-01,2009-01-01 00:04:00.600000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '!='. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col != '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +0,true,0,0,0,2009-01-01,2009-01-01 00:00:00 +1,false,1,1.100000023841858,10.1,2009-01-01,2009-01-01 00:01:00 +2,true,2,2.200000047683716,20.2,2009-01-01,2009-01-01 00:02:00.100000000 +3,false,3,3.299999952316284,30.3,2009-01-01,2009-01-01 00:03:00.300000000 +4,true,4,4.400000095367432,40.4,2009-01-01,2009-01-01 00:04:00.600000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator 'between'. +select count(*) from alltypes_jdbc_datasource where timestamp_col between +'2009-01-03 00:12:00' and '2009-01-05 00:12:00' +---- RESULTS +20 +---- TYPES +BIGINT +==== +---- QUERY +# Gets specified columns based on timestamp predicate without time in timestamp. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col > '2009-01-03' order by id limit 5; +---- RESULTS +20,true,0,0,0,2009-01-03,2009-01-03 00:20:00.900000000 +21,false,1,1.100000023841858,10.1,2009-01-03,2009-01-03 00:21:00.900000000 +22,true,2,2.200000047683716,20.2,2009-01-03,2009-01-03 00:22:00.910000000 +23,false,3,3.299999952316284,30.3,2009-01-03,2009-01-03 00:23:00.930000000 +24,true,4,4.400000095367432,40.4,2009-01-03,2009-01-03 00:24:00.960000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate without milisecond in timestamp. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col > '2009-01-03 00:22:00' order by id limit 5; +---- RESULTS +22,true,2,2.200000047683716,20.2,2009-01-03,2009-01-03 00:22:00.910000000 +23,false,3,3.299999952316284,30.3,2009-01-03,2009-01-03 00:23:00.930000000 +24,true,4,4.400000095367432,40.4,2009-01-03,2009-01-03 00:24:00.960000000 +25,false,5,5.5,50.5,2009-01-03,2009-01-03 00:25:01 +26,true,6,6.599999904632568,60.59999999999999,2009-01-03,2009-01-03 00:26:01.500000000 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY # Drop table DROP TABLE alltypes_jdbc_datasource; ---- RESULTS diff --git a/testdata/workloads/functional-query/queries/QueryTest/mysql-ext-jdbc-tables.test b/testdata/workloads/functional-query/queries/QueryTest/mysql-ext-jdbc-tables.test index 4f29a712b..273f83b68 100644 --- a/testdata/workloads/functional-query/queries/QueryTest/mysql-ext-jdbc-tables.test +++ b/testdata/workloads/functional-query/queries/QueryTest/mysql-ext-jdbc-tables.test @@ -281,6 +281,147 @@ where date_col between DATE '2009-03-01' and DATE '2009-04-01' order by id limit INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE ==== ---- QUERY +# Gets specified columns based on timestamp predicate with operator '='. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col = '2009-01-02 00:12:00.00' order by id limit 5; +---- RESULTS +12,false,2,2.200000047683716,20.2,2009-01-02,2009-01-02 00:12:00 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '=' +# with empty result. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col = '1990-01-02 00:12:00.00' order by id limit 5; +---- RESULTS +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '>'. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col > '2009-01-02 00:12:00.00' order by id limit 5; +---- RESULTS +13,false,3,3.299999952316284,30.3,2009-01-02,2009-01-02 00:13:00 +14,false,4,4.400000095367432,40.4,2009-01-02,2009-01-02 00:14:01 +15,false,5,5.5,50.5,2009-01-02,2009-01-02 00:15:01 +16,false,6,6.599999904632568,60.59999999999999,2009-01-02,2009-01-02 00:16:01 +17,false,7,7.699999809265137,70.7,2009-01-02,2009-01-02 00:17:01 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '>' +# with empty result. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col > '2990-01-01 00:12:00.460000000' order by id limit 5; +---- RESULTS +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '<'. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col < '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +0,false,0,0,0,2009-01-01,2009-01-01 00:00:00 +1,false,1,1.100000023841858,10.1,2009-01-01,2009-01-01 00:01:00 +2,false,2,2.200000047683716,20.2,2009-01-01,2009-01-01 00:02:00 +3,false,3,3.299999952316284,30.3,2009-01-01,2009-01-01 00:03:00 +4,false,4,4.400000095367432,40.4,2009-01-01,2009-01-01 00:04:01 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '<' +# with empty result. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col < '1990-01-01 00:12:00.460000000' order by id limit 5; +---- RESULTS +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '>='. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col >= '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +13,false,3,3.299999952316284,30.3,2009-01-02,2009-01-02 00:13:00 +14,false,4,4.400000095367432,40.4,2009-01-02,2009-01-02 00:14:01 +15,false,5,5.5,50.5,2009-01-02,2009-01-02 00:15:01 +16,false,6,6.599999904632568,60.59999999999999,2009-01-02,2009-01-02 00:16:01 +17,false,7,7.699999809265137,70.7,2009-01-02,2009-01-02 00:17:01 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '<='. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col <= '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +0,false,0,0,0,2009-01-01,2009-01-01 00:00:00 +1,false,1,1.100000023841858,10.1,2009-01-01,2009-01-01 00:01:00 +2,false,2,2.200000047683716,20.2,2009-01-01,2009-01-01 00:02:00 +3,false,3,3.299999952316284,30.3,2009-01-01,2009-01-01 00:03:00 +4,false,4,4.400000095367432,40.4,2009-01-01,2009-01-01 00:04:01 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator '!='. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col != '2009-01-02 00:12:00.460000000' order by id limit 5; +---- RESULTS +0,false,0,0,0,2009-01-01,2009-01-01 00:00:00 +1,false,1,1.100000023841858,10.1,2009-01-01,2009-01-01 00:01:00 +2,false,2,2.200000047683716,20.2,2009-01-01,2009-01-01 00:02:00 +3,false,3,3.299999952316284,30.3,2009-01-01,2009-01-01 00:03:00 +4,false,4,4.400000095367432,40.4,2009-01-01,2009-01-01 00:04:01 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate with operator 'between'. +select count(*) from alltypes_jdbc_datasource where timestamp_col between +'2009-01-03 00:12:00' and '2009-01-05 00:12:00' +---- RESULTS +20 +---- TYPES +BIGINT +==== +---- QUERY +# Gets specified columns based on timestamp predicate without time in timestamp. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col > '2009-01-03' order by id limit 5; +---- RESULTS +20,false,0,0,0,2009-01-03,2009-01-03 00:20:01 +21,false,1,1.100000023841858,10.1,2009-01-03,2009-01-03 00:21:01 +22,false,2,2.200000047683716,20.2,2009-01-03,2009-01-03 00:22:01 +23,false,3,3.299999952316284,30.3,2009-01-03,2009-01-03 00:23:01 +24,false,4,4.400000095367432,40.4,2009-01-03,2009-01-03 00:24:01 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY +# Gets specified columns based on timestamp predicate without milisecond in timestamp. +select id, bool_col, smallint_col, float_col, double_col, date_col, timestamp_col +from alltypes_jdbc_datasource +where timestamp_col > '2009-01-03 00:22:00' order by id limit 5; +---- RESULTS +22,false,2,2.200000047683716,20.2,2009-01-03,2009-01-03 00:22:01 +23,false,3,3.299999952316284,30.3,2009-01-03,2009-01-03 00:23:01 +24,false,4,4.400000095367432,40.4,2009-01-03,2009-01-03 00:24:01 +25,false,5,5.5,50.5,2009-01-03,2009-01-03 00:25:01 +26,false,6,6.599999904632568,60.59999999999999,2009-01-03,2009-01-03 00:26:02 +---- TYPES +INT, BOOLEAN, SMALLINT, FLOAT, DOUBLE, DATE, TIMESTAMP +==== +---- QUERY # Drop table DROP TABLE alltypes_jdbc_datasource; ---- RESULTS
