crepererum commented on code in PR #6201:
URL: https://github.com/apache/arrow-datafusion/pull/6201#discussion_r1183584830
##########
datafusion/core/tests/sqllogictests/test_files/interval.slt:
##########
@@ -182,3 +182,193 @@ 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 error DataFusion error: Internal error: If RHS of the operation is an
array, then LHS also must be\. This was likely caused by a bug in DataFusion's
code and we would welcome that you file an bug report in our issue tracker
+select '1980-01-01'::date + i from t;
+
+query error DataFusion error: Internal error: If RHS of the operation is an
array, then LHS also must be\. This was likely caused by a bug in DataFusion's
code and we would welcome that you file an bug report in our issue tracker
+select '1980-01-01T12:00:00'::timestamp + i from t;
+
+
+query error DataFusion error: Internal error: If RHS of the operation is an
array, then LHS also must be\. This was likely caused by a bug in DataFusion's
code and we would welcome that you file an bug report in our issue tracker
+select '1980-01-01'::date - i from t;
+
+query error DataFusion error: Internal error: If RHS of the operation is an
array, then LHS also must be\. This was likely caused by a bug in DataFusion's
code and we would welcome that you file an bug report in our issue tracker
+select '1980-01-01T12:00:00'::timestamp - i from t;
+
+### 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
Review Comment:
This is more a wish list item: I think the error message is only half
helpful:
```suggestion
query error DataFusion error: Error during planning: interval can't subtract
date from interval
```
##########
datafusion/core/tests/sqllogictests/test_files/interval.slt:
##########
@@ -182,3 +182,193 @@ 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 error DataFusion error: Internal error: If RHS of the operation is an
array, then LHS also must be\. This was likely caused by a bug in DataFusion's
code and we would welcome that you file an bug report in our issue tracker
+select '1980-01-01'::date + i from t;
+
+query error DataFusion error: Internal error: If RHS of the operation is an
array, then LHS also must be\. This was likely caused by a bug in DataFusion's
code and we would welcome that you file an bug report in our issue tracker
+select '1980-01-01T12:00:00'::timestamp + i from t;
+
+
+query error DataFusion error: Internal error: If RHS of the operation is an
array, then LHS also must be\. This was likely caused by a bug in DataFusion's
code and we would welcome that you file an bug report in our issue tracker
+select '1980-01-01'::date - i from t;
+
+query error DataFusion error: Internal error: If RHS of the operation is an
array, then LHS also must be\. This was likely caused by a bug in DataFusion's
code and we would welcome that you file an bug report in our issue tracker
+select '1980-01-01T12:00:00'::timestamp - i from t;
+
+### 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;
Review Comment:
These tests are kinda important since they proof that intervals are NOT
durations and that their length depends on the operand (row wise!).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
