select_safe.test

# The include statement below is a temp one for tests that are yet to
#be ported to run with InnoDB,
#but needs to be kept for tests that would need MyISAM in future.
--source include/force_myisam_default.inc

#
# test of safe selects
#

--disable_warnings
drop table if exists t1;
--enable_warnings

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;
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;
update t1 set b="a" where a=1;
select 1 from t1,t1 as t2,t1 as t3;

# The following should give errors:
--error 1175
update t1 set b="a";
--error 1175
update t1 set b="a" where b="test";
--error 1175
delete from t1;
--error 1175
delete from t1 where b="test";
--error 1175
delete from t1 where a+0=1;
--error 1104
select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5;

# The following should be ok:
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;

# Test SQL_BIG_SELECTS

alter table t1 add key b (b);
SET MAX_JOIN_SIZE=2;
SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS;
insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
--error 1104
SELECT * from t1 order by a;
SET SQL_BIG_SELECTS=1;
SELECT * from t1 order by a;
SET MAX_JOIN_SIZE=2;
--error 1104
SELECT * from t1;
SET MAX_JOIN_SIZE=DEFAULT;
SELECT * from t1;

#
# Test MAX_SEEKS_FOR_KEY
#
analyze table t1;
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;
set MAX_SEEKS_FOR_KEY=1;
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
SET MAX_SEEKS_FOR_KEY=DEFAULT;

drop table t1;

# BUG#8726
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;
--error 1104
select * from (select * from t1) x;

set local  max_join_size=1;
--error 1104
select * from (select a.a as aa, b.a as ba from t1 a, t1 b) x;

set local  max_join_size=1;
--error 1104
select * from (select 1 union select 2 union select 3) x;
drop table t1;

SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, MAX_JOIN_SIZE=DEFAULT;

# End of 4.1 tests

--echo #
--echo # Bug #28145710: SQL_SAFE_UPDATES ERROR IS INSUFFICIENTLY INFORMATIVE
--echo #

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;

SET SESSION sql_safe_updates=1;

SET RANGE_OPTIMIZER_MAX_MEM_SIZE= 1;
#DELETE with range_optimizer_max_mem_size warning
let query1= DELETE FROM t1 WHERE c1 IN (1,22);
#UPDATE with range_optimizer_max_mem_size warning
let query2= UPDATE t1 SET c1=20 WHERE c1 IN (1,22);
#multi-table DELETE with conversion warning and target table uses table scan
let query3= DELETE t1 FROM t1 JOIN t2 ON t1.c2 = t2.c1;
#multi-table UPDATE with conversion warning and target table uses table scan
let query4= UPDATE t1, t2 SET t1.c1=20 WHERE t1.c2 = t2.c1;
#multi-table DELETE with target table uses eq_ref (no error)
let query5= DELETE t2 FROM t1 JOIN t2 ON t1.c2 = t2.c1;
#DELETE with conversion warning
let query6= DELETE FROM t1 WHERE c2 IN(1,2222);
#DELETE with conversion warning
let query7= UPDATE t1 SET c1=20 WHERE c2 IN(1,2222);
#DELETE with non-indexed column returns error
let query8= DELETE FROM t2 WHERE c2 IN('a','e');
#DELETE full table and test with binlog disabled
let query9= DELETE FROM t2;



eval EXPLAIN $query1;
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
eval $query1;

eval EXPLAIN $query2;
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
eval $query2;
SET RANGE_OPTIMIZER_MAX_MEM_SIZE= default;

eval EXPLAIN $query3;
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
eval $query3;

eval EXPLAIN $query4;
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
eval $query4;

eval EXPLAIN $query5;
eval $query5;

eval EXPLAIN $query6;
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
eval $query6;

eval EXPLAIN $query7;
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
eval $query7;

eval EXPLAIN $query8;
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
eval $query8;

eval EXPLAIN $query9;
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
eval $query9;

SET sql_log_bin= 0;
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
eval $query9;
SET sql_log_bin= default;

DROP TABLE t1, t2;
SET SESSION sql_safe_updates=default;