[MDEV-26745] sql_safe_updates blocks queries that actually do use a key column in WHERE Created: 2021-10-01  Updated: 2021-10-12  Resolved: 2021-10-12

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.12, 10.6.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Hartmut Holzgraefe Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-18304 sql_safe_updates does not work with O... Closed

 Description   

Create a simple test table, and enable sql_safe_updates:

create table t1(id int primary key, val int);
 
insert into t1 values(1,1),(2,2),(3,3);
 
set session sql_safe_updates=ON;

DELETE without WHERE fails now, as expected:

delete from t1;
 
-- ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

DELETE with a simple WHERE 1=1 also fails as expected:

delete from t1 where 1=1;
 
-- ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

Now the following two queries fail, too, even though the primary key column is clearly referenced in them:

delete from t1 where id = id;
 
-- ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
 
 
 
delete from t1 where id is not null;
 
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 one does work as expected though:

delete from t1 where id > 0;
 
-- Query OK, 3 rows affected (0.001 sec)

What I assume happening here is that the optimizer already optimized "id = id" and "id IS NOT NULL" away, knowing that these will always return "true" anyway, before the SQL_SAFE_UPDATES does even kick in?

 Comments   
Comment by Sergei Golubchik [ 2021-10-12 ]

Yes, this is correct. Unless you specify a LIMIT clause, the statement must actually use an index to find rows. Simply mentioning an indexed column in the WHERE clause is not enough.

Generated at Thu Feb 08 09:47:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.