MySQL Bugs: #114549: CAST from DOUBLE to INTEGER sometimes returns 1, sometimes 2

Bug #114549	CAST from DOUBLE to INTEGER sometimes returns 1, sometimes 2
Submitted:	4 Apr 2024 10:15	Modified:	13 Jun 2024 21:51
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.3, 8.0.36	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
I used mysql-trunk pulled from github today, revision 824e2b4064053f7daf17d7f3f84b7a3ed92e5fb4 . I verified this on 8.0.34 too.

This mtr test shows the problem:

# Casting the DOUBLE 1.9 to signed INTEGER
select cast(cast(1.9 as double) as signed) ;

# Should return the same, using a DOUBLE column
create table k(a double);
insert into k values (1.9);
select * from  k;
show create table  k;
select cast(a as signed) from k;

# Should return the same, using a DOUBLE literal
select cast(1.9e0 as signed) from k;
# And here a DOUBLE column of a derived table
select cast(y as signed) from (select cast(1.9 as double)) as der(y);

# For reference, when using a DECIMAL input
select cast(1.9 as signed) from k;

The first query with CAST returns 1, all others return 2.
The problem is inconsistency: depending on where the 1.9 DOUBLE value comes from, it ends up as 1 or 2.
So
  select cast(cast(1.9 as double) as signed) = cast(1.9e0 as signed) ;
is false, which cannot be correct.

According to the std, how DOUBLE casts to INT (by rounding or truncating) is implementation-defined, which means that it's ok to make a choice, but this choice has to be specified.

How to repeat:
see above

Suggested fix:
Looking in code, when converting a literal DOUBLE like 1.9e0 to an int, Item_float::val_int() calls rint() so it rounds. When converting a DOUBLE column, Field_double::val_int() calls rint() too. But when converting the CAST-AS-DOUBLE, Item_func_typecast_real::val_int() calls Item_func::val_int_from_real(), which does not round.

Hello Guilhem,

Thank you for the report and test case.

regards,
Umesh

Documented fix as follows in the MySQL 9.0.0 changelog:

    In some cases, casting a double to an integer value used
    rounding, and in others, with truncation, which led to
    inconsistent results. Now rounding up is used in all such cases.

Closed.