Bug #115274 Abnormal behavior of strings in expressions
Submitted: 11 Jun 3:19 Modified: 11 Jun 9:21
Reporter: Huicong Xu Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.36 OS:Any (22.04)
Assigned to: CPU Architecture:Any

[11 Jun 3:19] Huicong Xu
Description:
Hi.
As you said in this question(https://bugs.mysql.com/bug.php?id=115270), the SQL standard forbids comparing columns or constants of different data types. 
"You should get the error when you compare columns or constants of different data types. Actually, it is prescribed as a very hard error by the latest SQL Standard.
MySQL goes a step further and allows read-only queries to compare the incomparable values. But, only in read-only DML statements. 
Allowing changing data in rows by totally incompatible data types is very grave error. Hence, that is why MySQL will not allow your data to be ruined by the erroneous comparisons of totally different data types."

But I did not get an error when comparing strings and constants.
Here is my test case.
CREATE TABLE t0(c0 TEXT , c1 TEXT , c2 BLOB );
INSERT INTO t0 VALUES ('48GgpQsi55', '48GgpQsi55', '48GgpQsi55');
INSERT INTO t0(c0, c1, c2) VALUES ('5wAwqHMpNS', 'mwAwqHMpNS', 'mwAwqHMpNS');
INSERT INTO t0 VALUES ('NyKQLJBNcK', 'NyKQLJBNcK', 'NyKQLJBNcK');
CREATE INDEX i0 ON t0(c1(149) ASC, c2(296) DESC);

mysql> UPDATE t0 SET c1='KdjFwhw3Og' WHERE (t0.c0 > 0);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> select * from t0;
+------------+------------+------------+
| c0         | c1         | c2         |
+------------+------------+------------+
| 48GgpQsi55 | KdjFwhw3Og | 48GgpQsi55 |
| 5wAwqHMpNS | KdjFwhw3Og | mwAwqHMpNS |
| NyKQLJBNcK | NyKQLJBNcK | NyKQLJBNcK |
+------------+------------+------------+
3 rows in set (0.00 sec)

In the previous question.
mysql> UPDATE t0 SET c1='KdjFwhw3Og' WHERE ('-' > 0);
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '-'

Why do these two behave differently.
Thank for your reply.

How to repeat:
CREATE TABLE t0(c0 TEXT , c1 TEXT , c2 BLOB );
INSERT INTO t0 VALUES ('48GgpQsi55', '48GgpQsi55', '48GgpQsi55');
INSERT INTO t0(c0, c1, c2) VALUES ('5wAwqHMpNS', 'mwAwqHMpNS', 'mwAwqHMpNS');
INSERT INTO t0 VALUES ('NyKQLJBNcK', 'NyKQLJBNcK', 'NyKQLJBNcK');
CREATE INDEX i0 ON t0(c1(149) ASC, c2(296) DESC);

mysql> UPDATE t0 SET c1='KdjFwhw3Og' WHERE (t0.c0 > 0);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> select * from t0;
+------------+------------+------------+
| c0         | c1         | c2         |
+------------+------------+------------+
| 48GgpQsi55 | KdjFwhw3Og | 48GgpQsi55 |
| 5wAwqHMpNS | KdjFwhw3Og | mwAwqHMpNS |
| NyKQLJBNcK | NyKQLJBNcK | NyKQLJBNcK |
+------------+------------+------------+
3 rows in set (0.00 sec)
[11 Jun 9:21] MySQL Verification Team
Hi Mr. Xu,

Thank you for your bug report.

However, this is not a forum for asking questions.

Your text case is OK. DMLs which work, do so  because it is possible to convert a string to the floating point.

In order to see how it truly works, you can use CAST() functionality ......

A DML that does not work can not do a conversion .......

Not a bug.