MySQL Bugs: #62554: Update bug

Bug #62554	Update bug
Submitted:	27 Sep 2011 17:23	Modified:	28 Sep 2011 13:14
Reporter:	Kenan Bektas	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.56-log	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The following statement work allright even if there is a typo.

delete from invoices where invoice_no and location=123;

How to repeat:
N/A

Please, send the output of

show create table invoices\G

I am almost (99.9%) sure this is not a bug, but your exact column types will help to prove this faster...

Hint:

mysql> select 1 from dual where 1 and 2;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.02 sec)

mysql> select 1 from dual where 0 and 2;
Empty set (0.00 sec)

mysql> select 1 from dual where 'a' and 2;
Empty set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect INTEGER value: 'a'
1 row in set (0.00 sec)

mysql> show create table invoices;

invoices | CREATE TABLE `invoices` (
  `invoice_no` bigint(20) DEFAULT NULL,
  `location` bigint(20) DEFAULT NULL,
  `remark` varchar(200) DEFAULT NULL,
  `recid` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`recid`),
  KEY `invoices_location_ndx` (`location`),
  KEY `invoices_no_ndx` (`invoice_no`)
) ENGINE=MyISAM AUTO_INCREMENT=2124 DEFAULT CHARSET=latin1

(in case you would need..)

mysql> select 1 from dual where 1 and 2;
+---+
| 1 |
+---+
| 1 |
+---+

mysql> select 1 from dual where 'a' and 2;
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'a' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

OK, so both columns are BIGINT, integer numbers. MySQL interprets their values as boolean for AND logical operator, with 0 representing FALSE and any other number representing TRUE:

mysql> select 1 from dual where 1 and 2;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select 1 from dual where 1 and 0;
Empty set (0.00 sec)

Hence the result you get. Read http://dev.mysql.com/doc/refman/5.1/en/logical-operators.html for the details.

This is not a bug.

But it is not a proper SQL parsing either. In where clause I have fields (field1=value1 and/or field2=value2) to compare not assign field values (1=2, if value1=1 and value2=2) then compare.