You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@arrow.apache.org by al...@apache.org on 2023/02/02 18:23:10 UTC
[arrow-datafusion] branch master updated: Support coercing `utf8` to `interval` and `timestamp` (including arguments to `date_bin`) (#5117)
This is an automated email from the ASF dual-hosted git repository.
alamb pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/arrow-datafusion.git
The following commit(s) were added to refs/heads/master by this push:
new 224c68210 Support coercing `utf8` to `interval` and `timestamp` (including arguments to `date_bin`) (#5117)
224c68210 is described below
commit 224c682101949da57aebc36e92e5a881ef3040d4
Author: Andrew Lamb <an...@nerdnetworks.org>
AuthorDate: Thu Feb 2 19:23:03 2023 +0100
Support coercing `utf8` to `interval` and `timestamp` (including arguments to `date_bin`) (#5117)
* Support coercing strings to intervals (for date_bin and others)
* Review feedback
---
.../tests/sqllogictests/test_files/timestamps.slt | 70 ++++++++++++++++++++--
datafusion/expr/src/type_coercion/functions.rs | 14 ++++-
datafusion/optimizer/src/type_coercion.rs | 14 ++++-
3 files changed, 88 insertions(+), 10 deletions(-)
diff --git a/datafusion/core/tests/sqllogictests/test_files/timestamps.slt b/datafusion/core/tests/sqllogictests/test_files/timestamps.slt
index 2861028c4..6b13c42f4 100644
--- a/datafusion/core/tests/sqllogictests/test_files/timestamps.slt
+++ b/datafusion/core/tests/sqllogictests/test_files/timestamps.slt
@@ -72,39 +72,51 @@ drop table foo;
## test date_bin function
###
query T
-SELECT DATE_BIN(INTERVAL '15 minutes', TIMESTAMP '2022-08-03 14:38:50Z', TIMESTAMP '1970-01-01T00:00:00Z') AS res
+SELECT DATE_BIN(INTERVAL '15 minutes', TIMESTAMP '2022-08-03 14:38:50Z', TIMESTAMP '1970-01-01T00:00:00Z')
+----
+2022-08-03T14:30:00
+
+# Can coerce string interval arguments
+query T
+SELECT DATE_BIN('15 minutes', TIMESTAMP '2022-08-03 14:38:50Z', TIMESTAMP '1970-01-01T00:00:00Z')
+----
+2022-08-03T14:30:00
+
+# Can coerce all string arguments
+query T
+SELECT DATE_BIN('15 minutes', '2022-08-03 14:38:50Z', '1970-01-01T00:00:00Z')
----
2022-08-03T14:30:00
# Shift forward by 5 minutes
query T
-SELECT DATE_BIN(INTERVAL '15 minutes', TIMESTAMP '2022-08-03 14:38:50Z', TIMESTAMP '1970-01-01T00:05:00Z') AS res
+SELECT DATE_BIN(INTERVAL '15 minutes', TIMESTAMP '2022-08-03 14:38:50Z', TIMESTAMP '1970-01-01T00:05:00Z')
----
2022-08-03T14:35:00
# Shift backward by 5 minutes
query T
-SELECT DATE_BIN(INTERVAL '15 minutes', TIMESTAMP '2022-08-03 14:38:50Z', TIMESTAMP '1970-01-01T23:55:00Z') AS res
+SELECT DATE_BIN(INTERVAL '15 minutes', TIMESTAMP '2022-08-03 14:38:50Z', TIMESTAMP '1970-01-01T23:55:00Z')
----
2022-08-03T14:25:00
# origin after source, timestamp in previous bucket
query T
-SELECT DATE_BIN(INTERVAL '15 minutes', TIMESTAMP '2022-08-03 14:38:50Z', TIMESTAMP '2022-08-03 14:40:00Z') AS res
+SELECT DATE_BIN(INTERVAL '15 minutes', TIMESTAMP '2022-08-03 14:38:50Z', TIMESTAMP '2022-08-03 14:40:00Z')
----
2022-08-03T14:25:00
# stride by 7 days
query T
-SELECT DATE_BIN(INTERVAL '7 days', TIMESTAMP '2022-08-03 14:38:50Z', TIMESTAMP '1970-01-01 00:00:00Z') AS res
+SELECT DATE_BIN(INTERVAL '7 days', TIMESTAMP '2022-08-03 14:38:50Z', TIMESTAMP '1970-01-01 00:00:00Z')
----
2022-07-28T00:00:00
# origin shifts bins forward 1 day
query T
-SELECT DATE_BIN(INTERVAL '7 days', TIMESTAMP '2022-08-03 14:38:50Z', TIMESTAMP '1970-01-02 00:00:00Z') AS res
+SELECT DATE_BIN(INTERVAL '7 days', TIMESTAMP '2022-08-03 14:38:50Z', TIMESTAMP '1970-01-02 00:00:00Z')
----
2022-07-29T00:00:00
@@ -133,3 +145,49 @@ FROM (
(TIMESTAMP '2021-06-10 17:05:00Z', TIMESTAMP '2001-01-01T00:00:00Z', 0.5),
(TIMESTAMP '2021-06-10 17:19:10Z', TIMESTAMP '2001-01-01T00:00:00Z', 0.3)
) as t (time, origin, val)
+
+###
+## test date_trunc function
+###
+query T
+SELECT DATE_TRUNC('year', TIMESTAMP '2022-08-03 14:38:50Z');
+----
+2022-01-01T00:00:00
+
+query T
+SELECT DATE_TRUNC('quarter', TIMESTAMP '2022-08-03 14:38:50Z');
+----
+2022-07-01T00:00:00
+
+query T
+SELECT DATE_TRUNC('month', TIMESTAMP '2022-08-03 14:38:50Z');
+----
+2022-08-01T00:00:00
+
+query T
+SELECT DATE_TRUNC('day', TIMESTAMP '2022-08-03 14:38:50Z');
+----
+2022-08-03T00:00:00
+
+query T
+SELECT DATE_TRUNC('hour', TIMESTAMP '2022-08-03 14:38:50Z');
+----
+2022-08-03T14:00:00
+
+query T
+SELECT DATE_TRUNC('minute', TIMESTAMP '2022-08-03 14:38:50Z');
+----
+2022-08-03T14:38:00
+
+query T
+SELECT DATE_TRUNC('second', TIMESTAMP '2022-08-03 14:38:50Z');
+----
+2022-08-03T14:38:50
+
+
+# Demonstrate that strings are automatically coerced to timestamps (don't use TIMESTAMP)
+
+query T
+SELECT DATE_TRUNC('second', '2022-08-03 14:38:50Z');
+----
+2022-08-03T14:38:50
diff --git a/datafusion/expr/src/type_coercion/functions.rs b/datafusion/expr/src/type_coercion/functions.rs
index a732c552b..a038fdcc9 100644
--- a/datafusion/expr/src/type_coercion/functions.rs
+++ b/datafusion/expr/src/type_coercion/functions.rs
@@ -22,8 +22,10 @@ use arrow::{
};
use datafusion_common::{DataFusionError, Result};
-/// Performs type coercion for functions Returns the data types that
-/// each argument must be coerced to match `signature`.
+/// Performs type coercion for function arguments.
+///
+/// Returns the data types to which each argument must be coerced to
+/// match `signature`.
///
/// For more details on coercion in general, please see the
/// [`type_coercion`](crate::type_coercion) module.
@@ -175,7 +177,13 @@ pub fn can_coerce_from(type_into: &DataType, type_from: &DataType) -> bool {
| Decimal128(_, _)
),
Timestamp(TimeUnit::Nanosecond, _) => {
- matches!(type_from, Null | Timestamp(_, _) | Date32)
+ matches!(
+ type_from,
+ Null | Timestamp(_, _) | Date32 | Utf8 | LargeUtf8
+ )
+ }
+ Interval(_) => {
+ matches!(type_from, Utf8 | LargeUtf8)
}
Utf8 | LargeUtf8 => true,
Null => can_cast_types(type_from, type_into),
diff --git a/datafusion/optimizer/src/type_coercion.rs b/datafusion/optimizer/src/type_coercion.rs
index 96dacab51..f42b7520f 100644
--- a/datafusion/optimizer/src/type_coercion.rs
+++ b/datafusion/optimizer/src/type_coercion.rs
@@ -553,10 +553,22 @@ fn coerce_arguments_for_signature(
expressions
.iter()
.enumerate()
- .map(|(i, expr)| expr.clone().cast_to(&new_types[i], schema))
+ .map(|(i, expr)| cast_expr(expr, &new_types[i], schema))
.collect::<Result<Vec<_>>>()
}
+/// Cast `expr` to the specified type, if possible
+fn cast_expr(expr: &Expr, to_type: &DataType, schema: &DFSchema) -> Result<Expr> {
+ // Special case until Interval coercion is handled in arrow-rs
+ // https://github.com/apache/arrow-rs/issues/3643
+ match (expr, to_type) {
+ (Expr::Literal(ScalarValue::Utf8(Some(s))), DataType::Interval(_)) => {
+ parse_interval("millisecond", s.as_str()).map(Expr::Literal)
+ }
+ _ => expr.clone().cast_to(to_type, schema),
+ }
+}
+
/// Returns the coerced exprs for each `input_exprs`.
/// Get the coerced data type from `aggregate_rule::coerce_types` and add `try_cast` if the
/// data type of `input_exprs` need to be coerced.