MySQL Bugs: #106782: Sql_Safe_Updates errors if target table is empty with update join

Bug #106782	Sql_Safe_Updates errors if target table is empty with update join
Submitted:	18 Mar 2022 16:47	Modified:	25 Mar 2022 8:19
Reporter:	Andy Cadley	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If the target table of an UPDATE JOIN statement is empty, statement will fail with error 1175 even though it would normally succeed.

How to repeat:
drop table if exists test1;
drop table if exists test2;

create table test1(id int primary key, value varchar(10));
insert into test1(id,value)
select 1 as id, 'test' as value 
union all select 2 as id, 'test2' as value 
union all select 3 as id, 'test3' as value ;

create table test2(id int primary key, value varchar(10));
insert into test2(id,value)
select 1 as id, 'xxx' as value;

# succeeds
update test1 t1
join test2 t2 using(id)
set
	t1.value = t2.value;

# succeeds
update test1 t1
join test2 t2
set
	t1.value = t2.value
where t1.id = t2.id;

truncate table test1;

# this will fail with error 1175, despite being the same as previous query
update test1 t1
join test2 t2 using(id)
set
	t1.value = t2.value;

# this will fail with error 1175, despite being the same as previous query
update test1 t1
join test2 t2
set
	t1.value = t2.value
where t1.id = t2.id;

	

Suggested fix:
sql_safe_update error should not be triggered simply because the result set would empty

Hi Mr. Cadley,

Thank you for your bug report.

Are you sure that you get error 1175 ????

If you use our client, you should get full textual error message. 

Please, confirm.

The full text of the error in MySQL workbench is:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

But obviously this doesn't really make much sense when the exact same query works fine if the table is not empty. Turning Safe SQL Updates off will make the problem go away, but that effectively means you always have to have it disabled if there is the possibility of an empty target table, which makes the feature fairly pointless.

Thank you for the feedback.