wangyum commented on issue #24940: [SPARK-28135] Better double support in SQL ceil/floor functions URL: https://github.com/apache/spark/pull/24940#issuecomment-504803625 It seems only Hive and Teradata returns `Long.MaxValue`: **Vertica**: ``` dbadmin=> select version(), ceil(1.2345678901234e+200), ceiling(1.2345678901234e+200), floor(1.2345678901234e+200); version | ceil | ceiling | floor ------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Vertica Analytic Database v9.1.1-0 | 123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 (1 row) ``` **PostgreSQL**: ``` postgres=# select version(), ceil(1.2345678901234e+200), ceiling(1.2345678901234e+200), floor(1.2345678901234e+200); version | ceil | cei ling | floor ----------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- PostgreSQL 11.3 (Debian 11.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit | 12345678901234000000000000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 1234567890123400000000000000000000000000000000000000000000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 (1 row) ``` **MySQL**: ``` mysql> select version(), ceil(1.2345678901234e+200), ceiling(1.2345678901234e+200), floor(1.2345678901234e+200); +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | version() | ceil(1.2345678901234e+200) | ceiling(1.2345678901234e+200) | floor(1.2345678901234e+200) | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 5.7.26 | 123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 123456789012340000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` **Presto**: ``` presto> select ceil(1.2345678901234e+200), ceiling(1.2345678901234e+200), floor(1.2345678901234e+200); _col0 | _col1 | _col2 ---------------------+---------------------+--------------------- 1.2345678901234E200 | 1.2345678901234E200 | 1.2345678901234E200 (1 row) ``` **Hive**: ``` hive> select version(), ceil(1.2345678901234e+200), ceiling(1.2345678901234e+200), floor(1.2345678901234e+200); OK 3.1.1 rf4e0529634b6231a0072295da48af466cf2f10b7 9223372036854775807 9223372036854775807 9223372036854775807 Time taken: 2.85 seconds, Fetched: 1 row(s) ``` **Teradata**:  **Oracle**:  **DB2**:  **SQL Server**: 
---------------------------------------------------------------- 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]
