[ 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)