Details
Description
MariaDB [test]> CREATE TABLE `bork` (`a` INT, `b` INT, PRIMARY KEY (`a`), KEY (`b`));
|
Query OK, 0 rows affected (0.112 sec)
|
|
|
MariaDB [test]> DELETE FROM bork WHERE `a`=1 or `b`=2;
|
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
|
The error message says that a KEY was not used in the delete statement. Both sides of the OR are keys, however the OR apparently is preventing the optimizer from realizing this.
These types of statements are comment when creating a one-to-many or many-to-many linking tables, and attempting to either UPDATE or DELETE these links.
Current work-around is to use an arbitrarily large LIMIT on the query, as this bypasses the WHERE safety check.
It also appears that the optimizer assumes that a LIMIT of 0xFFFFFFFFFFFFFFFF or (2^64)-1 is the same as having no limit at all.
-- 0xFFFFFFFFFFFFFFFE : (2 ^ 64) - 2
|
MariaDB [test]> DELETE FROM bork WHERE `a`=1 or `b`=2 LIMIT 18446744073709551614;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
-- 0xFFFFFFFFFFFFFFFF : (2 ^ 64) - 1
|
MariaDB [test]> DELETE FROM bork WHERE `a`=1 or `b`=2 LIMIT 18446744073709551615;
|
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
|
|
|
-- 0x10000000000000000 : (2 ^ 64)
|
MariaDB [test]> DELETE FROM bork WHERE `a`=1 or `b`=2 LIMIT 18446744073709551616;
|
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '18446744073709551616' at line 1
|
Attachments
Issue Links
- relates to
-
MDEV-26745 sql_safe_updates blocks queries that actually do use a key column in WHERE
-
- Closed
-
- links to