Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
10.6
-
ubuntu22.04
Description
Hi.
In the update statement, I found it possible to compare columns and constants of different data types. But SQL Standard is very clear on this issue. I should get the error when I compare columns or constants of different data types. Actually, it is prescribed as a very hard error by the latest SQL Standard. Allowing changing data in rows by totally incompatible data types is very grave error.
Here's a MySQL developer's explanation.(https://bugs.mysql.com/bug.php?id=115270)
Here is my test case.
In Mariadb
CREATE TABLE t0(c0 TEXT , c1 DECIMAL , c2 BLOB );
|
INSERT INTO t0 VALUES ('zeGTVmzYyL', -10723, 'zeGTVmzYyL');
|
INSERT INTO t0(c2, c1, c0) VALUES ('m6MkaimUzq', -10811, 'm6MkaimUzq');
|
INSERT IGNORE INTO t0(c0) VALUES ('u3pHrhF5LF');
|
|
|
mysql> SELECT * FROM t0 WHERE ((435600036)>=('-'));
|
+------------+--------+------------+
|
| c0 | c1 | c2 |
|
+------------+--------+------------+
|
| zeGTVmzYyL | -10723 | zeGTVmzYyL |
|
| m6MkaimUzq | -10811 | m6MkaimUzq |
|
| u3pHrhF5LF | NULL | NULL |
|
+------------+--------+------------+
|
3 rows in set, 1 warning (0.00 sec)
|
|
|
mysql> UPDATE t0 SET c0='e9pvumJh2h', c1=3268, c2='e9pvumJh2h' WHERE ((435600036)>=('-'));
|
Query OK, 3 rows affected, 1 warning (0.00 sec)
|
Rows matched: 3 Changed: 3 Warnings: 1
|
|
|
mysql> SELECT * FROM t0 WHERE ((435600036)>=('-'));
|
+------------+------+------------+
|
| c0 | c1 | c2 |
|
+------------+------+------------+
|
| e9pvumJh2h | 3268 | e9pvumJh2h |
|
| e9pvumJh2h | 3268 | e9pvumJh2h |
|
| e9pvumJh2h | 3268 | e9pvumJh2h |
|
+------------+------+------------+
|
3 rows in set, 1 warning (0.00 sec)
|
But in Mysql.
mysql> SELECT * FROM t0 WHERE ((435600036)>=('-'));
|
+------------+--------+------------+
|
| c0 | c1 | c2 |
|
+------------+--------+------------+
|
| zeGTVmzYyL | -10723 | zeGTVmzYyL |
|
| m6MkaimUzq | -10811 | m6MkaimUzq |
|
| u3pHrhF5LF | NULL | NULL |
|
+------------+--------+------------+
|
3 rows in set, 1 warning (0.00 sec)
|
|
|
mysql> UPDATE t0 SET c0='e9pvumJh2h', c1=3268, c2='e9pvumJh2h' WHERE ((435600036)>=('-'));
|
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '-'
|
Thank for your reply.