yaooqinn commented on issue #26337: [SPARK-29679][SQL] Make interval type comparable and orderable URL: https://github.com/apache/spark/pull/26337#issuecomment-551144591 `Oracle`, `mssql`, `presto` etc. have two interval types which are the `interval year to month` and `interval day to second`, the binary comparison operators can not be applied to these two different types. I have it tested with `presto` with the following result as you can see. even with expilict casting. ```sql presto> select interval '30' day = interval '1' month; Query 20191107_145330_00007_f239d failed: line 1:26: '=' cannot be applied to interval day to second, interval year to month select interval '30' day = interval '1' month presto> select interval '30' day < interval '1' month; Query 20191107_150903_00008_f239d failed: line 1:26: '<' cannot be applied to interval day to second, interval year to month select interval '30' day < interval '1' month presto> presto> select interval '30' day < cast(interval '1' month as interval day to second); Query 20191107_153514_00009_f239d failed: line 1:28: Cannot cast interval year to month to interval day to second select interval '30' day < cast(interval '1' month as interval day to second) ``` As we are more likely to define interval types and parse them as consistent as PostgreSQL’s approach not other dbs. So option 1: when we do a binary comparison between two intervals where we need to adjust year-month to day-second part, we follow Postgres as https://github.com/apache/spark/pull/26337#discussion_r342396155 and https://github.com/apache/spark/pull/26337#discussion_r342467923 to keep feature parity with postgres. This is also current pr's approach. It's kind of "accurate" when only intervals themselves participate in calculating. We may need to a doc to notice users for such behavior Option 1: Use a year with a average value of 365.25 days and a month of 30 days to adjust. @cloud-fan @MaxGekk @maropu I hope we can reach an agreement here soon, thanks for your time.
---------------------------------------------------------------- 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]
