MySQL Bugs: #111505: Expression triggers an error in `UPDATE` but not in `SELECT`

Bug #111505	Expression triggers an error in `UPDATE` but not in `SELECT`
Submitted:	21 Jun 2023 3:12	Modified:	21 Jun 2023 12:29
Reporter:	chi zhang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	last commit version in github	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Consider the following program:

```
CREATE TABLE t0(c2 BIGINT );
INSERT INTO t0(c2) VALUES (1);

UPDATE t0 SET t0.c2 = 2 WHERE (~ ('n') = NULL);
```

This `UPDATE` statement triggers an error: `Truncated incorrect INTEGER value: 'n'`.

But when I put this predicate in `SELECT` like `SELECT * FROM t0 WHERE (~ ('n') =  NULL);` then it can execute without any error.

I found there is a report about the same error message https://bugs.mysql.com/bug.php?id=43437. I think the difference is that in this program, the expression should have the same behavior in `UPDATE` and `SELECT`.

How to repeat:
I download the latest commit version of MySQL from GitHub. https://github.com/mysql/mysql-server/commit/ea7087d885006918ad54458e7aad215b1650312c

Hi Mr. zhang,

Thank you for your bug report.

However, it is not a bug.

MySQL is not strict data type checking RDBMS. Hence in SELECTs, it does what it can to accommodate what you are asking for. However, in writing DMLs, it is too dangerous to allow such changes, as your entire table can be erased or changed in the very unpredictable manners.

Strict data typing will not be implemented soon , since millions of applications depend on that behaviour.

There are many reports like yours, so it is a duplicate of the following report:

https://bugs.mysql.com/bug.php?id=111483