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 ef9000514 Support `INTERVAL` SQL Type (#5792)
ef9000514 is described below
commit ef9000514aec61e0411b3279c6dfcfaebce42b5b
Author: Andrew Lamb <[email protected]>
AuthorDate: Thu Mar 30 21:42:06 2023 +0200
Support `INTERVAL` SQL Type (#5792)
* Support INTERVAL SQL Type
* Add links
* Add more tets
---
datafusion/common/src/scalar.rs | 18 +++++-
.../sqllogictests/test_files/arrow_typeof.slt | 14 ++++-
.../tests/sqllogictests/test_files/interval.slt | 69 ++++++++++++++++++++++
datafusion/sql/src/planner.rs | 2 +-
docs/source/user-guide/sql/data_types.md | 12 ++--
5 files changed, 106 insertions(+), 9 deletions(-)
diff --git a/datafusion/common/src/scalar.rs b/datafusion/common/src/scalar.rs
index 8b55b0a79..de11d9d10 100644
--- a/datafusion/common/src/scalar.rs
+++ b/datafusion/common/src/scalar.rs
@@ -2155,6 +2155,9 @@ impl ScalarValue {
DataType::Interval(IntervalUnit::YearMonth) => {
build_array_primitive!(IntervalYearMonthArray,
IntervalYearMonth)
}
+ DataType::Interval(IntervalUnit::MonthDayNano) => {
+ build_array_primitive!(IntervalMonthDayNanoArray,
IntervalMonthDayNano)
+ }
DataType::List(fields) if fields.data_type() == &DataType::Int8 =>
{
build_array_list_primitive!(Int8Type, Int8, i8)
}
@@ -2304,7 +2307,6 @@ impl ScalarValue {
| DataType::Time64(TimeUnit::Millisecond)
| DataType::Duration(_)
| DataType::FixedSizeList(_, _)
- | DataType::Interval(_)
| DataType::LargeList(_)
| DataType::Union(_, _, _)
| DataType::Map(_, _)
@@ -2846,6 +2848,20 @@ impl ScalarValue {
},
)
}
+ DataType::Interval(IntervalUnit::DayTime) => {
+ typed_cast!(array, index, IntervalDayTimeArray,
IntervalDayTime)
+ }
+ DataType::Interval(IntervalUnit::YearMonth) => {
+ typed_cast!(array, index, IntervalYearMonthArray,
IntervalYearMonth)
+ }
+ DataType::Interval(IntervalUnit::MonthDayNano) => {
+ typed_cast!(
+ array,
+ index,
+ IntervalMonthDayNanoArray,
+ IntervalMonthDayNano
+ )
+ }
other => {
return Err(DataFusionError::NotImplemented(format!(
"Can't create a scalar from array of type \"{other:?}\""
diff --git a/datafusion/core/tests/sqllogictests/test_files/arrow_typeof.slt
b/datafusion/core/tests/sqllogictests/test_files/arrow_typeof.slt
index 94b954bfd..5166d291a 100644
--- a/datafusion/core/tests/sqllogictests/test_files/arrow_typeof.slt
+++ b/datafusion/core/tests/sqllogictests/test_files/arrow_typeof.slt
@@ -279,8 +279,20 @@ query error Cannot automatically convert
Interval\(DayTime\) to Interval\(MonthD
---
select arrow_cast(interval '30 minutes', 'Interval(MonthDayNano)');
-query error DataFusion error: This feature is not implemented: Can't create a
scalar from array of type "Interval\(MonthDayNano\)"
+query ?
+select arrow_cast('30 minutes', 'Interval(DayTime)');
+----
+0 years 0 mons 0 days 0 hours 30 mins 0.000 secs
+
+query ?
+select arrow_cast('1 year 5 months', 'Interval(YearMonth)');
+----
+1 years 5 mons 0 days 0 hours 0 mins 0.00 secs
+
+query ?
select arrow_cast('30 minutes', 'Interval(MonthDayNano)');
+----
+0 years 0 mons 0 days 0 hours 30 mins 0.000000000 secs
## Duration
diff --git a/datafusion/core/tests/sqllogictests/test_files/interval.slt
b/datafusion/core/tests/sqllogictests/test_files/interval.slt
new file mode 100644
index 000000000..0eeb87622
--- /dev/null
+++ b/datafusion/core/tests/sqllogictests/test_files/interval.slt
@@ -0,0 +1,69 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+
+# Use `interval` SQL literal syntax
+# the types should be the same:
https://github.com/apache/arrow-datafusion/issues/5801
+query TT
+select
+ arrow_typeof(interval '5 months'),
+ arrow_typeof(interval '5 days 3 nanoseconds')
+----
+Interval(YearMonth) Interval(MonthDayNano)
+
+
+# Use interval SQL type
+query TT
+select
+ arrow_typeof('5 months'::interval),
+ arrow_typeof('5 days 3 nanoseconds'::interval)
+----
+Interval(MonthDayNano) Interval(MonthDayNano)
+
+# cast with explicit cast sytax
+query TT
+select
+ arrow_typeof(cast ('5 months' as interval)),
+ arrow_typeof(cast ('5 days 3 nanoseconds' as interval))
+----
+Interval(MonthDayNano) Interval(MonthDayNano)
+
+
+# Should work tables with interval values
+# https://github.com/apache/arrow-datafusion/issues/5802
+statement error DataFusion error: This feature is not implemented: Unsupported
value Interval \{ value: Value\(SingleQuotedString\("5 days 3 nanoseconds"\)\),
leading_field: None, leading_precision: None, last_field: None,
fractional_seconds_precision: None \} in a values list expression
+create table t (i interval) as values (interval '5 days 3 nanoseconds');
+
+
+# Create tables with interval values
+statement ok
+create table t (i interval) as values ('5 days 3 nanoseconds'::interval);
+
+statement ok
+insert into t values ('6 days 7 nanoseconds'::interval)
+
+query ?T
+select
+ i,
+ arrow_typeof(i)
+from t;
+----
+0 years 0 mons 5 days 0 hours 0 mins 0.000000003 secs Interval(MonthDayNano)
+0 years 0 mons 6 days 0 hours 0 mins 0.000000007 secs Interval(MonthDayNano)
+
+statement ok
+drop table t;
diff --git a/datafusion/sql/src/planner.rs b/datafusion/sql/src/planner.rs
index 9156638f4..415e0721e 100644
--- a/datafusion/sql/src/planner.rs
+++ b/datafusion/sql/src/planner.rs
@@ -295,6 +295,7 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
make_decimal_type(precision, scale)
}
SQLDataType::Bytea => Ok(DataType::Binary),
+ SQLDataType::Interval =>
Ok(DataType::Interval(IntervalUnit::MonthDayNano)),
// Explicitly list all other types so that if sqlparser
// adds/changes the `SQLDataType` the compiler will tell us on
upgrade
// and avoid bugs like
https://github.com/apache/arrow-datafusion/issues/3059
@@ -305,7 +306,6 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
| SQLDataType::Varbinary(_)
| SQLDataType::Blob(_)
| SQLDataType::Datetime(_)
- | SQLDataType::Interval
| SQLDataType::Regclass
| SQLDataType::Custom(_, _)
| SQLDataType::Array(_)
diff --git a/docs/source/user-guide/sql/data_types.md
b/docs/source/user-guide/sql/data_types.md
index 9f0ca8f89..2753b014d 100644
--- a/docs/source/user-guide/sql/data_types.md
+++ b/docs/source/user-guide/sql/data_types.md
@@ -77,12 +77,12 @@ For example, to cast the output of `now()` to a `Timestamp`
with second precisio
## Date/Time Types
-| SQL DataType | Arrow DataType |
-| ------------ | :---------------------------------------------- |
-| `DATE` | `Date32` |
-| `TIME` | `Time64(Nanosecond)` |
-| `TIMESTAMP` | `Timestamp(Nanosecond, None)` |
-| `INTERVAL` | `Interval(IntervalUnit)` or `Interval(DayTime)` |
+| SQL DataType | Arrow DataType |
+| ------------ | :------------------------------- |
+| `DATE` | `Date32` |
+| `TIME` | `Time64(Nanosecond)` |
+| `TIMESTAMP` | `Timestamp(Nanosecond, None)` |
+| `INTERVAL` | `Interval(IntervalMonthDayNano)` |
## Boolean Types
