drop table if exists t1;
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, MAX_JOIN_SIZE=8;
create table t1 (a int auto_increment primary key, b char(20)) charset utf8mb4;
insert into t1 values(1,"test");
SELECT SQL_BUFFER_RESULT * from t1;
a	b
1	test
update t1 set b="a" where a=1;
delete from t1 where a=1;
insert into t1 values(1,"test"),(2,"test2");
SELECT SQL_BUFFER_RESULT * from t1;
a	b
1	test
2	test2
update t1 set b="a" where a=1;
SET SQL_BIG_SELECTS=1;
select 1 from t1,t1 as t2,t1 as t3;
1
1
1
1
1
update t1 set b="a";
ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
update t1 set b="a" where b="test";
ERROR 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;
ERROR 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 b="test";
ERROR 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 a+0=1;
ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
update t1 set b="a" limit 1;
update t1 set b="a" where b="b" limit 2;
delete from t1 where b="test" limit 1;
delete from t1 where a+0=1 limit 2;
alter table t1 add key b (b);
SET MAX_JOIN_SIZE=1;
SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS;
@@MAX_JOIN_SIZE	@@SQL_BIG_SELECTS
1	0
insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
SELECT * from t1 order by a;
ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
SET SQL_BIG_SELECTS=1;
SELECT * from t1 order by a;
a	b
2	test2
3	a
4	a
5	a
SET MAX_JOIN_SIZE=1;
SELECT * from t1;
ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
SET MAX_JOIN_SIZE=DEFAULT;
SELECT * from t1;
a	b
3	a
4	a
5	a
6	a
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	b	b	81	NULL	22	100.00	Using where; Using index
1	SIMPLE	t2	NULL	ref	b	b	81	test.t1.b	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t1` `t2` where (`test`.`t2`.`b` = `test`.`t1`.`b`)
set MAX_SEEKS_FOR_KEY=1;
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	b	b	81	NULL	22	100.00	Using where; Using index
1	SIMPLE	t2	NULL	ref	b	b	81	test.t1.b	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t1` `t2` where (`test`.`t2`.`b` = `test`.`t1`.`b`)
SET MAX_SEEKS_FOR_KEY=DEFAULT;
drop table t1;
create table t1 (a int);
insert into t1 values (1),(2),(3),(4),(5);
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
set local  max_join_size=1;
select * from (select a.a as aa, b.a as ba from t1 a, t1 b) x;
ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
set local  max_join_size=1;
select * from (select 1 union select 2 union select 3) x;
ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
drop table t1;
SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, MAX_JOIN_SIZE=DEFAULT;
#
# Bug #28145710: SQL_SAFE_UPDATES ERROR IS INSUFFICIENTLY INFORMATIVE
#
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	Error	Table 'test.t1' doesn't exist
test.t1	analyze	status	Operation failed
CREATE TABLE t1 (c1 INT NOT NULL, c2 VARCHAR(200) NOT NULL,
UNIQUE KEY idx1 (c1), UNIQUE KEY idx2 (c2));
CREATE TABLE t2 (c1 INT NOT NULL, c2 VARCHAR(200) NOT NULL,
UNIQUE KEY idx1 (c1));
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
INSERT INTO t2 VALUES (11, 'a'), (12, 'b'), (3, 'c'), (14, 'd');
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SET SESSION sql_safe_updates=1;
SET RANGE_OPTIMIZER_MAX_MEM_SIZE= 1;
EXPLAIN DELETE FROM t1 WHERE c1 IN (1,22);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
Warnings:
Warning	3170	Memory capacity of 1 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.
Note	1003	delete from `test`.`t1` where (`test`.`t1`.`c1` in (1,22))
DELETE FROM t1 WHERE c1 IN (1,22);
ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Memory capacity of 1 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.
EXPLAIN UPDATE t1 SET c1=20 WHERE c1 IN (1,22);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	NULL	index	NULL	idx1	4	NULL	4	100.00	Using where; Using temporary
Warnings:
Warning	3170	Memory capacity of 1 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.
Note	1003	update `test`.`t1` set `test`.`t1`.`c1` = 20 where (`test`.`t1`.`c1` in (1,22))
UPDATE t1 SET c1=20 WHERE c1 IN (1,22);
ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Memory capacity of 1 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.
SET RANGE_OPTIMIZER_MAX_MEM_SIZE= default;
EXPLAIN DELETE t1 FROM t1 JOIN t2 ON t1.c2 = t2.c1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t1	NULL	ALL	idx2	NULL	NULL	NULL	4	100.00	NULL
1	SIMPLE	t2	NULL	eq_ref	idx1	idx1	4	test.t1.c2	1	100.00	Using where; Using index
Warnings:
Warning	1739	Cannot use ref access on index 'idx2' due to type or collation conversion on field 'c2'
Note	1003	delete `test`.`t1` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`c2` = `test`.`t2`.`c1`)
DELETE t1 FROM t1 JOIN t2 ON t1.c2 = t2.c1;
ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Cannot use ref access on index 'idx2' due to type or collation conversion on field 'c2'
EXPLAIN UPDATE t1, t2 SET t1.c1=20 WHERE t1.c2 = t2.c1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	NULL	ALL	idx2	NULL	NULL	NULL	4	100.00	NULL
1	SIMPLE	t2	NULL	eq_ref	idx1	idx1	4	test.t1.c2	1	100.00	Using where; Using index
Warnings:
Warning	1739	Cannot use ref access on index 'idx2' due to type or collation conversion on field 'c2'
Note	1003	update `test`.`t1` join `test`.`t2` set `test`.`t1`.`c1` = 20 where (`test`.`t1`.`c2` = `test`.`t2`.`c1`)
UPDATE t1, t2 SET t1.c1=20 WHERE t1.c2 = t2.c1;
ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Cannot use ref access on index 'idx2' due to type or collation conversion on field 'c2'
EXPLAIN DELETE t2 FROM t1 JOIN t2 ON t1.c2 = t2.c1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	idx2	idx2	802	NULL	4	100.00	Using index
1	DELETE	t2	NULL	eq_ref	idx1	idx1	4	test.t1.c2	1	100.00	Using where
Warnings:
Warning	1739	Cannot use ref access on index 'idx2' due to type or collation conversion on field 'c2'
Note	1003	delete `test`.`t2` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`c2` = `test`.`t2`.`c1`)
DELETE t2 FROM t1 JOIN t2 ON t1.c2 = t2.c1;
Warnings:
Warning	1739	Cannot use ref access on index 'idx2' due to type or collation conversion on field 'c2'
EXPLAIN DELETE FROM t1 WHERE c2 IN(1,2222);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
Warnings:
Warning	1739	Cannot use range access on index 'idx2' due to type or collation conversion on field 'c2'
Note	1003	delete from `test`.`t1` where (`test`.`t1`.`c2` in (1,2222))
DELETE FROM t1 WHERE c2 IN(1,2222);
ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Cannot use range access on index 'idx2' due to type or collation conversion on field 'c2'
EXPLAIN UPDATE t1 SET c1=20 WHERE c2 IN(1,2222);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	NULL	index	NULL	idx1	4	NULL	4	100.00	Using where; Using temporary
Warnings:
Warning	1739	Cannot use range access on index 'idx2' due to type or collation conversion on field 'c2'
Note	1003	update `test`.`t1` set `test`.`t1`.`c1` = 20 where (`test`.`t1`.`c2` in (1,2222))
UPDATE t1 SET c1=20 WHERE c2 IN(1,2222);
ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Cannot use range access on index 'idx2' due to type or collation conversion on field 'c2'
EXPLAIN DELETE FROM t2 WHERE c2 IN('a','e');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t2	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
Warnings:
Note	1003	delete from `test`.`t2` where (`test`.`t2`.`c2` in ('a','e'))
DELETE FROM t2 WHERE c2 IN('a','e');
ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
EXPLAIN DELETE FROM t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t2	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	#
Warnings:
Note	1003	delete from `test`.`t2`
DELETE FROM t2;
ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
SET sql_log_bin= 0;
DELETE FROM t2;
ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
SET sql_log_bin= default;
DROP TABLE t1, t2;
SET SESSION sql_safe_updates=default;