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.