MaxGekk opened a new pull request #26034: [SPARK-29364][SQL] Return intervals from date subtract according to SQL standard URL: https://github.com/apache/spark/pull/26034 ### What changes were proposed in this pull request? Modified the `DateDiff` expression to return `CalendarIntervalType` according to the SQL standard , see [4.5.3 Operations involving datetimes and intervals](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt). This affects the `datediff` function and dates subtraction. By default, the expression return an integer which is the number of days between dates but when `spark.sql.ansi.enabled` is set to `true` and `spark.sql.dialect` is `Spark`, result type is the `INTERVAL` type. For example: ```sql > select date'tomorrow' - date'yesterday'; interval 2 days ``` ### Why are the changes needed? To conform the SQL standard which states the result type of `date operand 1` - `date operand 2` must be the interval type. ### Does this PR introduce any user-facing change? Yes, when SQL ANSI is enabled in the Spark dialect. Before the query below returns number of days: ```sql spark-sql> SET spark.sql.ansi.enabled=true; spark.sql.ansi.enabled true spark-sql> SET spark.sql.dialect=Spark; spark.sql.dialect Spark spark-sql> select date'2019-10-05' - date'2018-09-01'; 399 ``` After it returns an interval: ```sql spark-sql> SET spark.sql.ansi.enabled=true; spark.sql.ansi.enabled true spark-sql> SET spark.sql.dialect=Spark; spark.sql.dialect Spark spark-sql> select date'2019-10-05' - date'2018-09-01'; interval 1 years 1 months 4 days ``` ### How was this patch tested? - by new tests in `DateExpressionsSuite`. - by examples for `DateDiff` that are checked due to https://github.com/apache/spark/pull/25942
---------------------------------------------------------------- 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]
