Attila Zsolt Piros created HIVE-21916:
-----------------------------------------
Summary: Avoid overflow because of casting in case of the "ceil",
"ceiling" and "floor" SQL functions
Key: HIVE-21916
URL: https://issues.apache.org/jira/browse/HIVE-21916
Project: Hive
Issue Type: Improvement
Affects Versions: 4.0.0
Reporter: Attila Zsolt Piros
The ceil, ceiling and floor SQL functions return type is long and this leads to
overflow:
{code:java}
hive> select version(), ceil(1.2345678901234e+200),
ceiling(1.2345678901234e+200), floor(1.2345678901234e+200);
OK
4.0.0-SNAPSHOT r11f78562ab36333cc1d0a3f6051d9846c9c92132 9223372036854775807
9223372036854775807 9223372036854775807
{code}
Meanwhile at other SQL engines.
*PostgreSQL*:
{{postgres=# select version(), ceil(1.2345678901234e+200),
ceiling(1.2345678901234e+200), floor(1.2345678901234e+200); version | ceil |
ceiling | 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*{{:}}{{}}
{code:java}
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)
{code}
*Presto*:
{code:java}
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){code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
