[MDEV-18304] sql_safe_updates does not work with OR clauses Created: 2019-01-18  Updated: 2022-03-14  Resolved: 2022-03-14

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3.11
Fix Version/s: 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3

Type: Bug Priority: Major
Reporter: Vincent Milum Jr Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-26745 sql_safe_updates blocks queries that ... Closed

 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



 Comments   
Comment by Elena Stepanova [ 2019-01-19 ]

Thanks for the report.
Same happens on all of MariaDB 5.5-10.4 and MySQL 5.6-8.0 didn't try MySQL 5.5, but most likely there, too).

There is definitely room for improvement here.

First of all, documentation in regard to sql_safe_updates is insufficient, there is definitely more to it than the description says. MySQL manual is somewhat better in this regard, as it at least has a separate section for safe updates, but it's also incomplete, as this report shows. The feature is vague, it needs to be described as precisely as possible.

Further, for the particular example from the bug description, one can argue that the keys aren't actually used here, as the table is empty. It can be seen if we switch off sql_slave_updates and run EXPLAIN, e.g. on 10.3:

MariaDB [test]> CREATE TABLE `bork` (`a` INT, `b` INT, PRIMARY KEY (`a`), KEY (`b`));
 
Query OK, 0 rows affected (0.18 sec)
 
 
 
MariaDB [test]> EXPLAIN DELETE FROM bork WHERE `a`=1 or `b`=2;
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | bork  | ALL  | PRIMARY,b     | NULL | NULL    | NULL |    1 | Using where |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
1 row in set (0.02 sec)

Even so, the error message is bad:

MariaDB [test]> SET sql_safe_updates= ON;
 
Query OK, 0 rows affected (0.00 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

It's all wrong, the UPDATE has a WHERE clause which uses key columns; optimizer chooses not to use keys, but that's another story.

But further, even if we insert some data into the table and make DELETE use the keys, it still doesn't work:

MariaDB [test]> SET sql_safe_updates= OFF;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> CREATE TABLE `bork` (`a` INT, `b` INT, PRIMARY KEY (`a`), KEY (`b`)) ENGINE=MyISAM;
 
Query OK, 0 rows affected (0.05 sec)
 
 
 
MariaDB [test]> INSERT INTO bork VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
 
Query OK, 8 rows affected (0.00 sec)
 
Records: 8  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> EXPLAIN DELETE FROM bork WHERE `a`=1 or `b`=2;
 
+------+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
 
| id   | select_type | table | type        | possible_keys | key       | key_len | ref  | rows | Extra                               |
 
+------+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
 
|    1 | SIMPLE      | bork  | index_merge | PRIMARY,b     | PRIMARY,b | 4,5     | NULL |    2 | Using union(PRIMARY,b); Using where |
 
+------+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SET sql_safe_updates= ON;
 
Query OK, 0 rows affected (0.00 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

So, obviously there the condition is stricter than just using a key. Maybe the problem is that it uses index_merge, more than one key. I'm not sure if it's intentional or not, but it does need to be either fixed or documented.

Finally, on a somewhat separate but related note, it's inconvenient that EXPLAIN doesn't work either:

MariaDB [test]> SET sql_safe_updates= ON;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> EXPLAIN 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

This part was actually fixed recently in upstream 5.7+, in the scope of bug #91080.

So, to summarize,

  • documentation definitely needs improvement;
  • the error message could be made better;
  • the code itself might need to be fixed or not, depending on what the intended behavior is;
  • EXPLAIN would be good to fix.
Generated at Thu Feb 08 08:43:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.