Description:
Hi.
In my tests, I found inconsistencies between select statements and update statements for the same expression.It works in select statements, but reports error in update statements. I think this creates an illusion for users, which can lead to other errors.
Here is my test case.It is wrong in MySQL, but correct in Mariadb.
In MySQL
--- ini ---
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)>=('-'));
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '-'
In Mariadb
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)
Thank for your reply.
How to repeat:
--- ini ---
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)>=('-'));
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '-'
Suggested fix:
I think you can take a look at Mariadb's solution. Thank you!
Description: Hi. In my tests, I found inconsistencies between select statements and update statements for the same expression.It works in select statements, but reports error in update statements. I think this creates an illusion for users, which can lead to other errors. Here is my test case.It is wrong in MySQL, but correct in Mariadb. In MySQL --- ini --- 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)>=('-')); ERROR 1292 (22007): Truncated incorrect DOUBLE value: '-' In Mariadb 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) Thank for your reply. How to repeat: --- ini --- 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)>=('-')); ERROR 1292 (22007): Truncated incorrect DOUBLE value: '-' Suggested fix: I think you can take a look at Mariadb's solution. Thank you!