MySQL Bugs: #75896: Undesired boolean coercion: WHERE id = 1 OR 2

Bug #75896	Undesired boolean coercion: WHERE id = 1 OR 2
Submitted:	13 Feb 2015 15:12	Modified:	13 May 2015 9:38
Reporter:	Morgan Tocker	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.7.6, 5.7.8, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL has an extension to SQL where it can coerce integers to booleans.  This behavior is not usually desired, and it's existance can cause accidental bugs and downtime.

Reported via:
https://twitter.com/lukaseder/status/560772255811403776

How to repeat:
mysql> CREATE TABLE my_data (id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO my_data VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> DELETE FROM my_data WHERE id = 1 OR 2;
Query OK, 5 rows affected (0.01 sec) <-  All rows deleted

Suggested fix:
It would be nice to disable this by default in a major new release, but (if possible) have a configuration directive to re-enable it for legacy applications that need more time when upgrading.

Hello Morgan,

Thank you for report.

Thanks,
Umesh

Note: I think the where clause here is 

where (id = 1) or 2    as = has higher precedence over 'or'
(see: https://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html)

and ideally  the 2 should not match as a boolean comparison so maybe should fail.

Some implicit type conversions just cause trouble. MySQL is famous for this and usually it works but when it doesn't you may not notice and you may not get the results you expect.