[GitHub] [spark] MaxGekk opened a new pull request #26132: [SPARK-29387][SQL] Support `*` and `\` operators for intervals

[GitBox <gi...@apache.org>]

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:
users@infra.apache.org


With regards,
Apache Git Services

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org