1# The include statement below is a temp one for tests that are yet to 2#be ported to run with InnoDB, 3#but needs to be kept for tests that would need MyISAM in future. 4--source include/force_myisam_default.inc 5 6# 7# test of safe selects 8# 9 10--disable_warnings 11drop table if exists t1; 12--enable_warnings 13 14SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, MAX_JOIN_SIZE=9; 15create table t1 (a int auto_increment primary key, b char(20)); 16insert into t1 values(1,"test"); 17SELECT SQL_BUFFER_RESULT * from t1; 18update t1 set b="a" where a=1; 19delete from t1 where a=1; 20insert into t1 values(1,"test"),(2,"test2"); 21SELECT SQL_BUFFER_RESULT * from t1; 22update t1 set b="a" where a=1; 23select 1 from t1,t1 as t2,t1 as t3; 24 25# The following should give errors: 26--error 1175 27update t1 set b="a"; 28--error 1175 29update t1 set b="a" where b="test"; 30--error 1175 31delete from t1; 32--error 1175 33delete from t1 where b="test"; 34--error 1175 35delete from t1 where a+0=1; 36--error 1104 37select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5; 38 39# The following should be ok: 40update t1 set b="a" limit 1; 41update t1 set b="a" where b="b" limit 2; 42delete from t1 where b="test" limit 1; 43delete from t1 where a+0=1 limit 2; 44 45# Test SQL_BIG_SELECTS 46 47alter table t1 add key b (b); 48SET MAX_JOIN_SIZE=2; 49SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS; 50insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); 51--error 1104 52SELECT * from t1 order by a; 53SET SQL_BIG_SELECTS=1; 54SELECT * from t1 order by a; 55SET MAX_JOIN_SIZE=2; 56--error 1104 57SELECT * from t1; 58SET MAX_JOIN_SIZE=DEFAULT; 59SELECT * from t1; 60 61# 62# Test MAX_SEEKS_FOR_KEY 63# 64analyze table t1; 65insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); 66explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; 67set MAX_SEEKS_FOR_KEY=1; 68explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; 69SET MAX_SEEKS_FOR_KEY=DEFAULT; 70 71drop table t1; 72 73# BUG#8726 74create table t1 (a int); 75insert into t1 values (1),(2),(3),(4),(5); 76insert into t1 select * from t1; 77insert into t1 select * from t1; 78insert into t1 select * from t1; 79 80set local max_join_size=8; 81--error 1104 82select * from (select * from t1) x; 83 84set local max_join_size=1; 85--error 1104 86select * from (select a.a as aa, b.a as ba from t1 a, t1 b) x; 87 88set local max_join_size=1; 89--error 1104 90select * from (select 1 union select 2 union select 3) x; 91drop table t1; 92 93SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, MAX_JOIN_SIZE=DEFAULT; 94 95# End of 4.1 tests 96 97--echo # 98--echo # Bug #28145710: SQL_SAFE_UPDATES ERROR IS INSUFFICIENTLY INFORMATIVE 99--echo # 100 101CREATE TABLE t1 (c1 INT NOT NULL, c2 VARCHAR(200) NOT NULL, 102 UNIQUE KEY idx1 (c1), UNIQUE KEY idx2 (c2)); 103CREATE TABLE t2 (c1 INT NOT NULL, c2 VARCHAR(200) NOT NULL, 104 UNIQUE KEY idx1 (c1)); 105INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'); 106INSERT INTO t2 VALUES (11, 'a'), (12, 'b'), (3, 'c'), (14, 'd'); 107ANALYZE TABLE t1, t2; 108 109SET SESSION sql_safe_updates=1; 110 111SET RANGE_OPTIMIZER_MAX_MEM_SIZE= 1; 112#DELETE with range_optimizer_max_mem_size warning 113let query1= DELETE FROM t1 WHERE c1 IN (1,22); 114#UPDATE with range_optimizer_max_mem_size warning 115let query2= UPDATE t1 SET c1=20 WHERE c1 IN (1,22); 116#multi-table DELETE with conversion warning and target table uses table scan 117let query3= DELETE t1 FROM t1 JOIN t2 ON t1.c2 = t2.c1; 118#multi-table UPDATE with conversion warning and target table uses table scan 119let query4= UPDATE t1, t2 SET t1.c1=20 WHERE t1.c2 = t2.c1; 120#multi-table DELETE with target table uses eq_ref (no error) 121let query5= DELETE t2 FROM t1 JOIN t2 ON t1.c2 = t2.c1; 122#DELETE with conversion warning 123let query6= DELETE FROM t1 WHERE c2 IN(1,2222); 124#DELETE with conversion warning 125let query7= UPDATE t1 SET c1=20 WHERE c2 IN(1,2222); 126#DELETE with non-indexed column returns error 127let query8= DELETE FROM t2 WHERE c2 IN('a','e'); 128#DELETE full table and test with binlog disabled 129let query9= DELETE FROM t2; 130 131 132 133eval EXPLAIN $query1; 134--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE 135eval $query1; 136 137eval EXPLAIN $query2; 138--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE 139eval $query2; 140SET RANGE_OPTIMIZER_MAX_MEM_SIZE= default; 141 142eval EXPLAIN $query3; 143--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE 144eval $query3; 145 146eval EXPLAIN $query4; 147--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE 148eval $query4; 149 150eval EXPLAIN $query5; 151eval $query5; 152 153eval EXPLAIN $query6; 154--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE 155eval $query6; 156 157eval EXPLAIN $query7; 158--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE 159eval $query7; 160 161eval EXPLAIN $query8; 162--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE 163eval $query8; 164 165eval EXPLAIN $query9; 166--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE 167eval $query9; 168 169SET sql_log_bin= 0; 170--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE 171eval $query9; 172SET sql_log_bin= default; 173 174DROP TABLE t1, t2; 175SET SESSION sql_safe_updates=default; 176