Bug #100270 Use MOD result (float number) in HAVING/WHERE is not considered as TRUE

• Submitted: 21 Jul 2020 4:30
• Modified: 21 Jul 2020 6:33
• Reporter: Yushan ZHANG
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7.31, 5.6.48
• OS: Any
• CPU Architecture: Any
• Tags: FLOAT, having, where

[21 Jul 2020 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 2020 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