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 017f28e10d Add sqllogic test coverage for interval arithmetic (#6201)
017f28e10d is described below
commit 017f28e10d0d053fda8a76d30a31ddc52e54f6c2
Author: Andrew Lamb <and...@nerdnetworks.org>
AuthorDate: Thu May 4 14:45:57 2023 -0400
Add sqllogic test coverage for interval arithmetic (#6201)
* Add sqllogic test coverage for interval arithmetic
* Update tests for intervals
---
.../tests/sqllogictests/test_files/interval.slt | 198 +++++++++++++++++++++
1 file changed, 198 insertions(+)
diff --git a/datafusion/core/tests/sqllogictests/test_files/interval.slt
b/datafusion/core/tests/sqllogictests/test_files/interval.slt
index 4a1cd4b220..1ba58a3d27 100644
--- a/datafusion/core/tests/sqllogictests/test_files/interval.slt
+++ b/datafusion/core/tests/sqllogictests/test_files/interval.slt
@@ -182,3 +182,201 @@ from t;
statement ok
drop table t;
+
+
+##### Tests for interval arithmetic
+
+statement ok
+create table t(i interval, d date, ts timestamp)
+as
+values
+ ('1 month', '1980-01-01', '2000-01-01T00:00:00'),
+ ('1 day', '1990-10-01', '2000-01-01T12:11:10'),
+ ('1 minute', '1980-01-02', '2000-02-01T00:00:00')
+;
+
+### date / timestamp (scalar) + interval (scalar)
+query D
+select '1980-01-01'::date + interval '1 day'
+----
+1980-01-02
+
+
+query P
+select '1980-01-01'::timestamp + interval '1 day'
+----
+1980-01-02T00:00:00
+
+
+### date / timestamp (scalar) - interval (scalar)
+query D
+select '1980-01-01'::date - interval '1 day'
+----
+1979-12-31
+
+
+query P
+select '1980-01-01'::timestamp - interval '1 day'
+----
+1979-12-31T00:00:00
+
+
+### date / timestamp (array) + interval (scalar)
+query D
+select d + interval '1 day' from t;
+----
+1980-01-02
+1990-10-02
+1980-01-03
+
+query P
+select ts + interval '1 day' from t;
+----
+2000-01-02T00:00:00
+2000-01-02T12:11:10
+2000-02-02T00:00:00
+
+### date / timestamp (array) - interval (scalar)
+query D
+select d - interval '1 day' from t;
+----
+1979-12-31
+1990-09-30
+1980-01-01
+
+query P
+select ts - interval '1 day' from t;
+----
+1999-12-31T00:00:00
+1999-12-31T12:11:10
+2000-01-31T00:00:00
+
+### date / timestamp (scalar) + interval (array)
+query D
+select '1980-01-01'::date + i from t;
+----
+1980-02-01
+1980-01-02
+1980-01-01
+
+query P
+select '1980-01-01T12:00:00'::timestamp + i from t;
+----
+1980-02-01T12:00:00
+1980-01-02T12:00:00
+1980-01-01T12:01:00
+
+
+query D
+select '1980-01-01'::date - i from t;
+----
+1979-12-01
+1979-12-31
+1980-01-01
+
+query P
+select '1980-01-01T12:00:00'::timestamp - i from t;
+----
+1979-12-01T12:00:00
+1979-12-31T12:00:00
+1980-01-01T11:59:00
+
+### date / timestamp (array) + interval (array)
+query D
+select d + i from t;
+----
+1980-02-01
+1990-10-02
+1980-01-02
+
+query P
+select ts + i from t;
+----
+2000-02-01T00:00:00
+2000-01-02T12:11:10
+2000-02-01T00:01:00
+
+
+### date / timestamp (array) - interval (array)
+query D
+select d - i from t;
+----
+1979-12-01
+1990-09-30
+1980-01-02
+
+query P
+select ts - i from t;
+----
+1999-12-01T00:00:00
+1999-12-31T12:11:10
+2000-01-31T23:59:00
+
+
+# Now reverse the argument order
+# interval (scalar) + date / timestamp (scalar)
+query D
+select '1 month'::interval + '1980-01-01'::date;
+----
+1980-02-01
+
+query P
+select '1 month'::interval + '1980-01-01T12:00:00'::timestamp;
+----
+1980-02-01T12:00:00
+
+# Exected error: interval (scalar) - date / timestamp (scalar)
+
+query error DataFusion error: Error during planning: interval can't subtract
timestamp/date
+select '1 month'::interval - '1980-01-01'::date;
+
+query error DataFusion error: Error during planning: interval can't subtract
timestamp/date
+select '1 month'::interval - '1980-01-01T12:00:00'::timestamp;
+
+# interval (array) + date / timestamp (array)
+query D
+select i + d from t;
+----
+1980-02-01
+1990-10-02
+1980-01-02
+
+query P
+select i + ts from t;
+----
+2000-02-01T00:00:00
+2000-01-02T12:11:10
+2000-02-01T00:01:00
+
+# expected error interval (array) - date / timestamp (array)
+query error DataFusion error: Error during planning: interval can't subtract
timestamp/date
+select i - d from t;
+
+query error DataFusion error: Error during planning: interval can't subtract
timestamp/date
+select i - ts from t;
+
+
+# interval (scalar) + date / timestamp (array)
+query D
+select '1 month'::interval + d from t;
+----
+1980-02-01
+1990-11-01
+1980-02-02
+
+query P
+select '1 month'::interval + ts from t;
+----
+2000-02-01T00:00:00
+2000-02-01T12:11:10
+2000-03-01T00:00:00
+
+# expected error interval (scalar) - date / timestamp (array)
+query error DataFusion error: Error during planning: interval can't subtract
timestamp/date
+select '1 month'::interval - d from t;
+
+query error DataFusion error: Error during planning: interval can't subtract
timestamp/date
+select '1 month'::interval - ts from t;
+
+statement ok
+drop table t
