Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.2, 10.3, 10.4, 10.5, 10.6
-
None
Description
SET SQL_MODE=CONCAT(@@sql_mode,',STRICT_ALL_TABLES'); |
|
CREATE OR REPLACE TABLE t (a INT, b INT, c INT, KEY(a)); |
|
INSERT INTO t VALUES (1,1,1),(2,2,2); |
|
UPDATE t SET c = 10 WHERE a = 'foo'; |
UPDATE t SET c = 20 WHERE b = 'foo'; |
|
DELETE FROM t WHERE a = 'foo'; |
DELETE FROM t WHERE b = 'foo'; |
|
INSERT INTO t VALUES (0,0,0); |
|
UPDATE t SET c = 10 WHERE a = 'foo'; |
UPDATE t SET c = 20 WHERE b = 'foo'; |
|
DELETE FROM t WHERE a = 'foo'; |
|
INSERT INTO t VALUES (0,0,0); |
|
DELETE FROM t WHERE b = 'foo'; |
|
|
DROP TABLE t; |
10.2 ceb40ef4 |
MariaDB [test]> INSERT INTO t VALUES (1,1,1),(2,2,2); |
Query OK, 2 rows affected (0.024 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]>
|
MariaDB [test]> UPDATE t SET c = 10 WHERE a = 'foo'; |
Query OK, 0 rows affected (0.020 sec) |
Rows matched: 0 Changed: 0 Warnings: 0 |
|
MariaDB [test]> UPDATE t SET c = 20 WHERE b = 'foo'; |
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'foo' |
MariaDB [test]>
|
MariaDB [test]> DELETE FROM t WHERE a = 'foo'; |
Query OK, 0 rows affected (0.000 sec) |
|
MariaDB [test]> DELETE FROM t WHERE b = 'foo'; |
Query OK, 0 rows affected, 1 warning (0.000 sec) |
|
MariaDB [test]>
|
MariaDB [test]> INSERT INTO t VALUES (0,0,0); |
Query OK, 1 row affected (0.008 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> UPDATE t SET c = 10 WHERE a = 'foo'; |
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'foo' |
MariaDB [test]> UPDATE t SET c = 20 WHERE b = 'foo'; |
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'foo' |
MariaDB [test]>
|
MariaDB [test]> DELETE FROM t WHERE a = 'foo'; |
Query OK, 1 row affected, 1 warning (0.007 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> INSERT INTO t VALUES (0,0,0); |
Query OK, 1 row affected (0.007 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> DELETE FROM t WHERE b = 'foo'; |
Query OK, 1 row affected, 1 warning (0.007 sec)
|
So, first updates and deletes are performed when there is no matching row.
- UPDATE/DELETE with search by indexed column doesn't produce warnings or errors;
- UPDATE with search by non-indexed column produces an error;
- DELETE with search by non-indexed column produces a warning;
Then updates and deletes are performed when there is a matching row.
- UPDATE produces an error, regardless whether the search column is indexed or not;
- DELETE produces a warning, regardless whether the search column is indexed or not.