select_safe.test 2.32 KB
Newer Older
1 2 3 4
#
# test of safe selects
#

5
--disable_warnings
6
drop table if exists t1;
7 8
--enable_warnings

9
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9;
unknown's avatar
unknown committed
10
create table t1 (a int auto_increment primary key, b char(20));
11 12 13 14 15 16 17
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;
18
select 1 from t1,t1 as t2,t1 as t3;
19 20

# The following should give errors:
unknown's avatar
unknown committed
21 22 23 24 25 26 27 28 29 30 31 32
--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;
33 34 35 36 37 38

# 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;
unknown's avatar
unknown committed
39 40 41

# Test SQL_BIG_SELECTS

unknown's avatar
unknown committed
42
alter table t1 add key b (b);
unknown's avatar
unknown committed
43 44 45 46
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
unknown's avatar
unknown committed
47
SELECT * from t1 order by a;
unknown's avatar
unknown committed
48
SET SQL_BIG_SELECTS=1;
unknown's avatar
unknown committed
49
SELECT * from t1 order by a;
unknown's avatar
unknown committed
50 51 52 53 54 55
SET MAX_JOIN_SIZE=2;
--error 1104
SELECT * from t1;
SET MAX_JOIN_SIZE=DEFAULT;
SELECT * from t1;

56 57 58 59 60
#
# 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");
61
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
62
set MAX_SEEKS_FOR_KEY=1;
63
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
64 65
SET MAX_SEEKS_FOR_KEY=DEFAULT;

66 67
drop table t1;

68 69 70 71 72 73 74 75 76 77 78 79 80
# 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
81
select * from (select a.a as aa, b.a as ba from t1 a, t1 b) x;
82 83 84 85 86 87

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

88
SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT;
89 90

# End of 4.1 tests