Bug #94535 server ignore syntax error
Submitted: 3 Mar 2019 16:00 Modified: 4 Mar 2019 14:14
Reporter: mohamed atef Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.7 OS:Windows
Assigned to: CPU Architecture:Any
Tags: server ignore syntax error

[3 Mar 2019 16:00] mohamed atef 
Description:
server ignore syntax error

How to repeat:
CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

use db;
CREATE TABLE `products` (
  `ID` bigint(20) NOT NULL ,
    `BASE_PRICE` double(10,4) NOT NULL DEFAULT '0.0000',
  `BASE_DISCOUNT` decimal(10,4) NOT NULL DEFAULT '0.0000',
    `BASE_COST` double(10,4) NOT NULL DEFAULT '0.0000',
    `CreatedBy` bigint(20) NOT NULL,
  `ModifiedBy` bigint(20) DEFAULT NULL,
    `ModIn` int(6) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`)
  
) ENGINE=InnoDB   DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO db.products (`ID`,`BASE_PRICE`,`BASE_DISCOUNT`,`BASE_COST`,`CreatedBy`,`ModifiedBy`,`ModIn`) VALUES (10001,28.0000,0.0000,1.0000,1,1,9);
INSERT INTO db.products (`ID`,`BASE_PRICE`,`BASE_DISCOUNT`,`BASE_COST`,`CreatedBy`,`ModifiedBy`,`ModIn`) VALUES (10002,28.0000,0.0000,0.0000,1,1,9);
INSERT INTO db.products (`ID`,`BASE_PRICE`,`BASE_DISCOUNT`,`BASE_COST`,`CreatedBy`,`ModifiedBy`,`ModIn`) VALUES (10003,28.0000,0.0000,0.0000,1,1,9);
INSERT INTO db.products (`ID`,`BASE_PRICE`,`BASE_DISCOUNT`,`BASE_COST`,`CreatedBy`,`ModifiedBy`,`ModIn`) VALUES (10004,28.0000,0.0000,0.0000,1,1,9);

SET SQL_SAFE_UPDATES = 0;
UPDATE PRODUCTS SET  ModifiedBy=1,ModIn=9,BASE_PRICE =28,BASE_DISCOUNT=0,BASE_COST=22 and  ID=10001;
### response
4 row(s) affected Rows matched: 4  Changed: 4  Warnings: 0

Suggested fix:
i dont know
[4 Mar 2019 13:55] MySQL Verification Team 
Hi,

Thank you for your bug report.

Luckily, this is not a bug.

I presume that you thought that this expression in UPDATE statement "BASE_COST=22 and  ID=10001" should return the syntax error. Actually, this is not a bug but a feature !!!!!

MySQL allows that boolean values are used in all DML statements. Hence the above expression will reduce to 0 or 1, depending on the values of the above two columns.

This is all fully explained in our Reference Manual.
[4 Mar 2019 14:12] MySQL Verification Team 
Let me help you to understand why this is not a syntax error and, hence, definitely not a bug, although this is truly very well described in our documentation.

In the UPDATE that you used, BASE_COST will be updated to 0 or 1, after evaluating the following boolean expression:

22 A?nd  ID=10001

Very simple and straightforward.
[4 Mar 2019 14:14] mohamed atef 
thank you
[4 Mar 2019 14:17] MySQL Verification Team 
You are truly welcome.