Edit File: select_safe.result
drop table if exists t1; SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, MAX_JOIN_SIZE=9; create table t1 (a int auto_increment primary key, b char(20)); 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; 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 select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5; 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 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=2; SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS; @@MAX_JOIN_SIZE @@SQL_BIG_SELECTS 2 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=2; 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 2 test2 3 a 4 a 5 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"); explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL b NULL NULL NULL 21 1 SIMPLE t2 ref b b 21 test.t1.b 6 Using where set MAX_SEEKS_FOR_KEY=1; explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL b NULL NULL NULL 21 1 SIMPLE t2 ref b b 21 test.t1.b 6 Using where 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=8; select * from (select * from t1) 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 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;
Back to File Manager