Bug #100301 LOG result used as predicate causes the conversion to boolean inconsistent

• Submitted: 23 Jul 2020 5:16
• Modified: 23 Jul 2020 5:21
• 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: having, log, where

[23 Jul 2020 5:16] Yushan ZHANG

Description:

-- incorrect, missing one row
mysql> select col from t1 where LOG( col ); 
+--------+
| col    |
+--------+
| 6.1806 |
+--------+
1 row in set, 1 warning (0.00 sec)

-- correct
mysql> select col from t1 where LOG( col ) IS TRUE;
+--------+
| col    |
+--------+
|  1.009 |
| 6.1806 |
+--------+

-- incorrect
mysql> select col from t1 having LOG( col ); 
+--------+
| col    |
+--------+
| 6.1806 |
+--------+
1 row in set, 1 warning (0.00 sec)

-- correct
mysql> select col from t1 having LOG( col ) IS TRUE;
+--------+
| col    |
+--------+
|  1.009 |
| 6.1806 |
+--------+
2 rows in set, 1 warning (0.00 sec)

For this bug, one row is select and one row is missing; in the previous bug (#100270), no row is selected.

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

insert into t1 values (0, -9.183), (1, 6.1806), (2, 1.009);

-- wrong
select col from t1 where LOG( col ); 

-- correct
select col from t1 where LOG( col ) IS TRUE;

-- wrong
select col from t1 having LOG( col ); 

-- correct
select col from t1 having LOG( col ) IS TRUE;

Suggested fix:
fix the implicit conversion from float to Boolean to be consistent

[23 Jul 2020 5:21] 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