MaxGekk opened a new pull request #26132: [SPARK-29387][SQL] Support `*` and `\` operators for intervals URL: https://github.com/apache/spark/pull/26132 ### What changes were proposed in this pull request? Added new expressions `MultiplyInterval` and `DivideInterval` to multiply/divide an interval by a numeric. Updated `TypeCoercion.DateTimeOperations` to turn the `Multiply`/`Divide` expressions of `CalendarIntervalType` and `NumericType` to `MultiplyInterval`/`DivideInterval`. To support new operations, added new methods `multiply()` and `divide()` to `CalendarInterval`. ### Why are the changes needed? - To maintain feature parity with PostgreSQL which supports multiplication and division of intervals by doubles: ```sql # select interval '1 hour' / double precision '1.5'; ?column? ---------- 00:40:00 ``` - To conform the SQL standard which defines those operations: `numeric * interval`, `interval * numeric` and `interval / numeric`. See [4.5.3 Operations involving datetimes and intervals](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt). - Improve Spark SQL UX and allow users to adjust interval columns. For example: ```sql spark-sql> select (timestamp'now' - timestamp'yesterday') * 1.3; interval 2 days 10 hours 39 minutes 38 seconds 568 milliseconds 900 microseconds ``` ### Does this PR introduce any user-facing change? Yes, previously the following query fails with the error: ```sql spark-sql> select interval 1 hour 30 minutes * 1.5; Error in query: cannot resolve '(interval 1 hours 30 minutes * 1.5BD)' due to data type mismatch: differing types in '(interval 1 hours 30 minutes * 1.5BD)' (interval and decimal(2,1)).; line 1 pos 7; ``` After: ```sql spark-sql> select interval 1 hour 30 minutes * 1.5; interval 2 hours 15 minutes ``` ### How was this patch tested? - Added tests for the `multiply()` and `divide()` methods to `CalendarIntervalSuite.java` - New test suite `IntervalExpressionsSuite` - by tests for `Multiply` -> `MultiplyInterval` and `Divide` -> `DivideInterval` in `TypeCoercionSuite` - updated `datetime.sql`
---------------------------------------------------------------- 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. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
