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)
