Presto allows cast from out-of-range floating point value to bigint and returns "bogus" result

[mbasmanova]

presto> select cast(REAL '2.0523825839131036E23' as bigint);
        _col0
---------------------
 9223372036854775807
(1 row)

Prestissimo throws:

VeloxUserError:  Cannot cast REAL '2.0523335847491578E23' to BIGINT. Loss of precision during arithmetic conversion: (long) 2.0523335847491578E23 cast((field_0) as BIGINT)

CC: @rschlussel @spershin @tdcmeehan @aditi-pandit @kgpai @kagamiori @gggrace14 @amitkdutta

[mbasmanova]

CC: @Yuhta

[mbasmanova]

I wonder if Presto's behavior is by design. It seems to return max (or min) bigint when input value is out of range.

presto> select cast(REAL '2.0523825839131036E23' as bigint);
        _col0
---------------------
 9223372036854775807
(1 row)

presto> select cast(REAL '-2.0523825839131036E23' as bigint);
        _col0
----------------------
 -9223372036854775808
(1 row)

[elharo]

What does the ANSI SQL spec say should happen?

[tdcmeehan]

See reference from the spec below (see 6.13, cast specification). Postgres also throws. To me, this seems like a bug.

9. If TD is approximate numeric, then
   Case:
   a) If SD is numeric, then
   Case:
   i) If there is a representation of SV in the data type TD that does not lose any leading significant digits after rounding or truncating if necessary, then TV is that representation. The choice of whether to round or truncate is implementation-defined.
   ii) Otherwise, an exception condition is raised: data exception — numeric value out of range.