This is an automated email from the ASF dual-hosted git repository.
alamb pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/arrow-datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new 17e6526354 Support `interval '1 month' + date/timestamp`: Handle
binary op interval in logical AST builder (#6270)
17e6526354 is described below
commit 17e65263547ce9527dc5a758b97d0b84192ea982
Author: Armin Primadi <[email protected]>
AuthorDate: Tue May 9 17:02:40 2023 +0700
Support `interval '1 month' + date/timestamp`: Handle binary op interval in
logical AST builder (#6270)
* Handle binary op interval in logical AST builder
* Fix test
* Support value + value interval binary operation
* Fix cargo doc
* Fix parser to handle complex test cases
* Add more test cases
* Update datafusion/sql/src/expr/value.rs
Co-authored-by: Andrew Lamb <[email protected]>
* Update type_coercion.slt
* Fix cargo fmt
---------
Co-authored-by: Andrew Lamb <[email protected]>
---
.../tests/sqllogictests/test_files/interval.slt | 87 +++++++++++++++++++
.../sqllogictests/test_files/type_coercion.slt | 6 +-
datafusion/sql/src/expr/mod.rs | 2 +
datafusion/sql/src/expr/value.rs | 99 +++++++++++++++++++++-
4 files changed, 188 insertions(+), 6 deletions(-)
diff --git a/datafusion/core/tests/sqllogictests/test_files/interval.slt
b/datafusion/core/tests/sqllogictests/test_files/interval.slt
index 82131f5d2a..e59bdbe312 100644
--- a/datafusion/core/tests/sqllogictests/test_files/interval.slt
+++ b/datafusion/core/tests/sqllogictests/test_files/interval.slt
@@ -126,8 +126,89 @@ select interval '5' nanoseconds
----
0 years 0 mons 0 days 0 hours 0 mins 0.000000005 secs
+# Interval with string literal addition
+query ?
+select interval '1 month' + '1 month'
+----
+0 years 2 mons 0 days 0 hours 0 mins 0.000000000 secs
+
+# Interval with string literal addition and leading field
+query ?
+select interval '1' + '1' month
+----
+0 years 2 mons 0 days 0 hours 0 mins 0.000000000 secs
+
+# Interval with nested string literal addition
+query ?
+select interval '1 month' + '1 month' + '1 month'
+----
+0 years 3 mons 0 days 0 hours 0 mins 0.000000000 secs
+
+# Interval with nested string literal addition and leading field
+query ?
+select interval '1' + '1' + '1' month
+----
+0 years 3 mons 0 days 0 hours 0 mins 0.000000000 secs
+
+# Interval mega nested string literal addition
+query ?
+select interval '1 year' + '1 month' + '1 day' + '1 hour' + '1 minute' + '1
second' + '1 millisecond' + '1 microsecond' + '1 nanosecond'
+----
+0 years 13 mons 1 days 1 hours 1 mins 1.001001001 secs
+
+# Interval with string literal subtraction
+query ?
+select interval '1 month' - '1 day';
+----
+0 years 1 mons -1 days 0 hours 0 mins 0.000000000 secs
+
+# Interval with string literal subtraction and leading field
+query ?
+select interval '5' - '1' - '2' year;
+----
+0 years 24 mons 0 days 0 hours 0 mins 0.000000000 secs
+
+# Interval with nested string literal subtraction
+query ?
+select interval '1 month' - '1 day' - '1 hour';
+----
+0 years 1 mons -1 days -1 hours 0 mins 0.000000000 secs
+# Interval with nested string literal subtraction and leading field
+query ?
+select interval '10' - '1' - '1' month;
+----
+0 years 8 mons 0 days 0 hours 0 mins 0.000000000 secs
+# Interval mega nested string literal subtraction
+query ?
+select interval '1 year' - '1 month' - '1 day' - '1 hour' - '1 minute' - '1
second' - '1 millisecond' - '1 microsecond' - '1 nanosecond'
+----
+0 years 11 mons -1 days -1 hours -1 mins -1.001001001 secs
+
+# Interval string literal + date
+query D
+select interval '1 month' + '1 day' + '2012-01-01'::date;
+----
+2012-02-02
+
+# Interval string literal parenthesized + date
+query D
+select ( interval '1 month' + '1 day' ) + '2012-01-01'::date;
+----
+2012-02-02
+
+# Interval nested string literal + date
+query D
+select interval '1 year' + '1 month' + '1 day' + '2012-01-01'::date
+----
+2013-02-02
+
+# Interval nested string literal subtraction + date
+query D
+select interval '1 year' - '1 month' + '1 day' + '2012-01-01'::date
+----
+2012-12-02
@@ -378,5 +459,11 @@ select '1 month'::interval - d from t;
query error DataFusion error: type_coercion\ncaused by\nError during planning:
Interval\(MonthDayNano\) \- Timestamp\(Nanosecond, None\) can't be evaluated
because there isn't a common type to coerce the types to
select '1 month'::interval - ts from t;
+# interval + date
+query D
+select interval '1 month' + '2012-01-01'::date;
+----
+2012-02-01
+
statement ok
drop table t
diff --git a/datafusion/core/tests/sqllogictests/test_files/type_coercion.slt
b/datafusion/core/tests/sqllogictests/test_files/type_coercion.slt
index 41bb541eac..9aced0a3fd 100644
--- a/datafusion/core/tests/sqllogictests/test_files/type_coercion.slt
+++ b/datafusion/core/tests/sqllogictests/test_files/type_coercion.slt
@@ -42,12 +42,10 @@ SELECT '2023-05-01 12:30:00'::timestamp - interval '1
month';
----
2023-04-01T12:30:00
-# TODO: https://github.com/apache/arrow-datafusion/issues/6180
# interval - date
-query error DataFusion error: This feature is not implemented: Unsupported
interval argument\. Expected string literal, got: BinaryOp \{ left:
Value\(SingleQuotedString\("1 month"\)\), op: Minus, right: Cast \{ expr:
Value\(SingleQuotedString\("2023\-05\-01"\)\), data_type: Date \} \}
+query error DataFusion error: type_coercion
select interval '1 month' - '2023-05-01'::date;
-# TODO: https://github.com/apache/arrow-datafusion/issues/6180
# interval - timestamp
-query error DataFusion error: This feature is not implemented: Unsupported
interval argument\. Expected string literal, got: BinaryOp \{ left:
Value\(SingleQuotedString\("1 month"\)\), op: Minus, right: Cast \{ expr:
Value\(SingleQuotedString\("2023\-05\-01 12:30:00"\)\), data_type:
Timestamp\(None, None\) \} \}
+query error DataFusion error: type_coercion
SELECT interval '1 month' - '2023-05-01 12:30:00'::timestamp;
diff --git a/datafusion/sql/src/expr/mod.rs b/datafusion/sql/src/expr/mod.rs
index 89f90b89e6..a281e8a985 100644
--- a/datafusion/sql/src/expr/mod.rs
+++ b/datafusion/sql/src/expr/mod.rs
@@ -140,6 +140,8 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
fractional_seconds_precision,
} => self.sql_interval_to_expr(
*value,
+ schema,
+ planner_context,
leading_field,
leading_precision,
last_field,
diff --git a/datafusion/sql/src/expr/value.rs b/datafusion/sql/src/expr/value.rs
index f1c93d8bdc..e747bf1b2a 100644
--- a/datafusion/sql/src/expr/value.rs
+++ b/datafusion/sql/src/expr/value.rs
@@ -19,9 +19,10 @@ use crate::planner::{ContextProvider, PlannerContext,
SqlToRel};
use arrow::compute::kernels::cast_utils::parse_interval_month_day_nano;
use arrow_schema::DataType;
use datafusion_common::{DFSchema, DataFusionError, Result, ScalarValue};
-use datafusion_expr::{lit, Expr};
+use datafusion_expr::expr::BinaryExpr;
+use datafusion_expr::{lit, Expr, Operator};
use log::debug;
-use sqlparser::ast::{DateTimeField, Expr as SQLExpr, Value};
+use sqlparser::ast::{BinaryOperator, DateTimeField, Expr as SQLExpr, Value};
use sqlparser::parser::ParserError::ParserError;
use std::collections::HashSet;
@@ -160,9 +161,17 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
}
}
+ /// Convert a SQL interval expression to a DataFusion logical plan
+ /// expression
+ ///
+ /// Waiting for this issue to be resolved:
+ /// `<https://github.com/sqlparser-rs/sqlparser-rs/issues/869>`
+ #[allow(clippy::too_many_arguments)]
pub(super) fn sql_interval_to_expr(
&self,
value: SQLExpr,
+ schema: &DFSchema,
+ planner_context: &mut PlannerContext,
leading_field: Option<DateTimeField>,
leading_precision: Option<u64>,
last_field: Option<DateTimeField>,
@@ -191,6 +200,92 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
SQLExpr::Value(
Value::SingleQuotedString(s) | Value::DoubleQuotedString(s),
) => s,
+ // Support expressions like `interval '1 month' + date/timestamp`.
+ // Such expressions are parsed like this by sqlparser-rs
+ //
+ // Interval
+ // BinaryOp
+ // Value(StringLiteral)
+ // Cast
+ // Value(StringLiteral)
+ //
+ // This code rewrites them to the following:
+ //
+ // BinaryOp
+ // Interval
+ // Value(StringLiteral)
+ // Cast
+ // Value(StringLiteral)
+ SQLExpr::BinaryOp { left, op, right } => {
+ let df_op = match op {
+ BinaryOperator::Plus => Operator::Plus,
+ BinaryOperator::Minus => Operator::Minus,
+ _ => {
+ return Err(DataFusionError::NotImplemented(format!(
+ "Unsupported interval operator: {op:?}"
+ )));
+ }
+ };
+ match (leading_field, left.as_ref(), right.as_ref()) {
+ (_, _, SQLExpr::Value(_)) => {
+ let left_expr = self.sql_interval_to_expr(
+ *left,
+ schema,
+ planner_context,
+ leading_field,
+ None,
+ None,
+ None,
+ )?;
+ let right_expr = self.sql_interval_to_expr(
+ *right,
+ schema,
+ planner_context,
+ leading_field,
+ None,
+ None,
+ None,
+ )?;
+ return Ok(Expr::BinaryExpr(BinaryExpr::new(
+ Box::new(left_expr),
+ df_op,
+ Box::new(right_expr),
+ )));
+ }
+ // In this case, the left node is part of the interval
+ // expr and the right node is an independent expr.
+ //
+ // Leading field is not supported when the right operand
+ // is not a value.
+ (None, _, _) => {
+ let left_expr = self.sql_interval_to_expr(
+ *left,
+ schema,
+ planner_context,
+ None,
+ None,
+ None,
+ None,
+ )?;
+ let right_expr = self.sql_expr_to_logical_expr(
+ *right,
+ schema,
+ planner_context,
+ )?;
+ return Ok(Expr::BinaryExpr(BinaryExpr::new(
+ Box::new(left_expr),
+ df_op,
+ Box::new(right_expr),
+ )));
+ }
+ _ => {
+ let value = SQLExpr::BinaryOp { left, op, right };
+ return Err(DataFusionError::NotImplemented(format!(
+ "Unsupported interval argument. Expected string
literal, got: {value:?}"
+ )));
+ }
+ }
+ }
_ => {
return Err(DataFusionError::NotImplemented(format!(
"Unsupported interval argument. Expected string literal,
got: {value:?}"
