Bug #100270 Use MOD result (float number) in HAVING/WHERE is not considered as TRUE
Submitted: 21 Jul 4:30 Modified: 21 Jul 6:33
Reporter: Yushan ZHANG Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.31, 5.6.48 OS:Any
Assigned to: CPU Architecture:Any
Tags: FLOAT, having, where

[21 Jul 4:30] Yushan ZHANG
Description:
-- wrong
mysql> SELECT `col_float_key_signed` FROM table_10_utf8_undef WHERE MOD( `col_float_key_signed`, ( `col_float_key_signed` = `col_float_key_signed` ) ) ;
Empty set (0.00 sec)

mysql> SELECT `col_float_key_signed` FROM table_10_utf8_undef WHERE MOD( `col_float_key_signed`, ( `col_float_key_signed` = `col_float_key_signed` ) ) IS TRUE;
+----------------------+
| col_float_key_signed |
+----------------------+
|               -9.183 |
|               6.1806 |
+----------------------+
2 rows in set (0.00 sec)

-- worong
mysql> SELECT `col_float_key_signed` FROM table_10_utf8_undef HAVING MOD( `col_float_key_signed`, ( `col_float_key_signed` = `col_float_key_signed` ) ) ;
Empty set (0.00 sec)

mysql> SELECT `col_float_key_signed` FROM table_10_utf8_undef HAVING MOD( `col_float_key_signed`, ( `col_float_key_signed` = `col_float_key_signed` ) ) IS TRUE;
+----------------------+
| col_float_key_signed |
+----------------------+
|               -9.183 |
|               6.1806 |
+----------------------+
2 rows in set (0.00 sec)

How to repeat:
create table table_10_utf8_undef (
`pk` int primary key,
`col_float_key_signed` float  ,
key (`col_float_key_signed`)
) character set utf8 ;

insert into table_10_utf8_undef values (0, -9.183), (1, 6.1806);

-- wrong
SELECT `col_float_key_signed` FROM table_10_utf8_undef WHERE MOD( `col_float_key_signed`, ( `col_float_key_signed` = `col_float_key_signed` ) ) ;

-- correct
SELECT `col_float_key_signed` FROM table_10_utf8_undef WHERE MOD( `col_float_key_signed`, ( `col_float_key_signed` = `col_float_key_signed` ) ) IS TRUE;

-- wrong
SELECT `col_float_key_signed` FROM table_10_utf8_undef HAVING MOD( `col_float_key_signed`, ( `col_float_key_signed` = `col_float_key_signed` ) ) ;

-- correct
SELECT `col_float_key_signed` FROM table_10_utf8_undef HAVING MOD( `col_float_key_signed`, ( `col_float_key_signed` = `col_float_key_signed` ) ) IS TRUE;

Suggested fix:
fix the implicit conversion from float to boolean
[21 Jul 6:33] MySQL Verification Team
Hello Yushan ZHANG,

Thank you for the report and test case.
Observed that 5.6.48, 5.7.31 are affected(8.0.21 seems to be fine).

regards,
Umesh