Tonix517 commented on issue #24940: [SPARK-28135] Better double support in SQL ceil/floor functions URL: https://github.com/apache/spark/pull/24940#issuecomment-504812198 The ceil and floor of both Java and Scala work on doubles: https://docs.oracle.com/javase/8/docs/api/java/lang/Math.html#ceil-double- "public static double ceil(double a) Returns the smallest (closest to negative infinity) double value that is greater than or equal to the argument and is equal to a mathematical integer. Special cases:" https://www.scala-lang.org/api/2.9.1/scala/Math$.html So I'm trying to keep the same semantics as Java/Scala here. If we have to return Integral type for some reason, I'd suggest to have them return BigInt, so that there'll be no data overflow. Best Regards, Tony Zhang On Sun, Jun 23, 2019 at 5:46 PM Yuming Wang <[email protected]> wrote: > 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*: > [image: image] > <https://user-images.githubusercontent.com/5399861/59983928-3eb6cf80-9657-11e9-9d72-07738c045cae.png> > *Oracle*: > [image: image] > <https://user-images.githubusercontent.com/5399861/59984111-3b244800-9659-11e9-930c-8c6a9c88b69a.png> > *DB2*: > [image: image] > <https://user-images.githubusercontent.com/5399861/59984176-e46b3e00-9659-11e9-9e82-199076446e39.png> > *SQL Server*: > [image: image] > <https://user-images.githubusercontent.com/5399861/59984290-02856e00-965b-11e9-82cc-4f3f102765f7.png> > > — > You are receiving this because you authored the thread. > Reply to this email directly, view it on GitHub > <https://github.com/apache/spark/pull/24940?email_source=notifications&email_token=ABJ72Q745ECJ3DT76KWHXHDP4AKNTA5CNFSM4H22AB32YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYLLCKI#issuecomment-504803625>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/ABJ72Q47QQR5ZPAEWTNBIMTP4AKNTANCNFSM4H22AB3Q> > . > -- Thanks, Tony Zhang
---------------------------------------------------------------- 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]
