[
https://issues.apache.org/jira/browse/HIVE-21916?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Attila Zsolt Piros updated HIVE-21916:
--------------------------------------
Description:
The ceil, ceiling and floor SQL functions return type is bigint 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}
The explain returned:
{code:java}
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| STAGE DEPENDENCIES: |
| Stage-0 is a root stage |
| |
| STAGE PLANS: |
| Stage: Stage-0 |
| Fetch Operator |
| limit: -1 |
| Processor Tree: |
| TableScan |
| alias: _dummy_table |
| Row Limit Per Split: 1 |
| Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats:
COMPLETE |
| Select Operator |
| expressions: '4.0.0-SNAPSHOT r11f78562ab36333cc1d0a3f6051d9846c9c92132'
(type: string), 9223372036854775807L (type: bigint), 9223372036854775807L
(type: bigint), 9223372036854775807L (type: bigint) |
| outputColumnNames: _col0, _col1, _col2, _col3 |
| Statistics: Num rows: 1 Data size: 164 Basic stats: COMPLETE Column stats:
COMPLETE |
| ListSink |
| |
+----------------------------------------------------+
{code}
Meanwhile at other SQL engines.
*PostgreSQL:*
{code:java}
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)
{code}
*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}
was:
The ceil, ceiling and floor SQL functions return type is bigint 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}
The explain returned:
{code}
expressions: '4.0.0-SNAPSHOT r11f78562ab36333cc1d0a3f6051d9846c9c92132' (type:
string), 9223372036854775807L (type: bigint), 9223372036854775807L (type:
bigint), 9223372036854775807L (type: bigint)
{code}
Meanwhile at other SQL engines.
*PostgreSQL:*
{code:java}
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)
{code}
*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}
> Avoid overflow as a result of casting to bigint at 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
> Priority: Major
>
> The ceil, ceiling and floor SQL functions return type is bigint 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}
> The explain returned:
> {code:java}
> +----------------------------------------------------+
> | Explain |
> +----------------------------------------------------+
> | STAGE DEPENDENCIES: |
> | Stage-0 is a root stage |
> | |
> | STAGE PLANS: |
> | Stage: Stage-0 |
> | Fetch Operator |
> | limit: -1 |
> | Processor Tree: |
> | TableScan |
> | alias: _dummy_table |
> | Row Limit Per Split: 1 |
> | Statistics: Num rows: 1 Data size: 10 Basic stats: COMPLETE Column stats:
> COMPLETE |
> | Select Operator |
> | expressions: '4.0.0-SNAPSHOT r11f78562ab36333cc1d0a3f6051d9846c9c92132'
> (type: string), 9223372036854775807L (type: bigint), 9223372036854775807L
> (type: bigint), 9223372036854775807L (type: bigint) |
> | outputColumnNames: _col0, _col1, _col2, _col3 |
> | Statistics: Num rows: 1 Data size: 164 Basic stats: COMPLETE Column stats:
> COMPLETE |
> | ListSink |
> | |
> +----------------------------------------------------+
> {code}
> Meanwhile at other SQL engines.
> *PostgreSQL:*
> {code:java}
> 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)
> {code}
> *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)
