Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.5.12, 10.6.4
-
None
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?
Attachments
Issue Links
- relates to
-
-
- Closed
-
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.