Bug #82011 and operation returns weird results

• Submitted: 27 Jun 2016 7:56
• Modified: 24 Dec 2019 13:53
• Reporter: 帅 Bang
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6, 5.5.50, 5.6.31,5.7.13
• OS: Linux
• CPU Architecture: Any

[27 Jun 2016 7:56] 帅 Bang

Description:
mysql> drop table sb;
Query OK, 0 rows affected (0.01 sec)

mysql> create table sb(a int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into sb values(3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from sb where true and 0.1;
Empty set (0.00 sec)

it indicates that mysql treats true and 0.1 as false, so empty result set returns.

But, But, But, when I type this , I was shocked !

mysql> select true and 0.1;
+--------------+
| true and 0.1 |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

faint....

How to repeat:
 drop table sb;
 create table sb(a int);
 insert into sb values(3);
 select * from sb where true and 0.1;
 select true and 0.1;

Suggested fix:
and operation returns consistent results wherever it is

[27 Jun 2016 8:52] MySQL Verification Team

Hello Bang,

Thank you for the report and test case.
Observed this with 5.5.50, 5.6.31 and 5.7.13.

Thanks,
Umesh

[24 Dec 2019 13:53] Roy Lyseng

Posted by developer:
 
Fixed in 8.0.18. Notice that

  select true and 0.1;

is now rewritten internally as

  select true and (0 <> 0.1);