diff options
Diffstat (limited to 'mysql-test/suite/pbxt')
| -rw-r--r-- | mysql-test/suite/pbxt/r/endspace.result | 15 | ||||
| -rw-r--r-- | mysql-test/suite/pbxt/r/heap.result | 733 | ||||
| -rw-r--r-- | mysql-test/suite/pbxt/r/select_safe.result | 12 | ||||
| -rw-r--r-- | mysql-test/suite/pbxt/r/view_grant.result | 958 | ||||
| -rw-r--r-- | mysql-test/suite/pbxt/t/endspace.test | 12 | ||||
| -rw-r--r-- | mysql-test/suite/pbxt/t/heap.test | 476 | ||||
| -rw-r--r-- | mysql-test/suite/pbxt/t/view_grant.test | 1224 |
7 files changed, 23 insertions, 3407 deletions
diff --git a/mysql-test/suite/pbxt/r/endspace.result b/mysql-test/suite/pbxt/r/endspace.result index c9a2db8bbfe..641ed14023d 100644 --- a/mysql-test/suite/pbxt/r/endspace.result +++ b/mysql-test/suite/pbxt/r/endspace.result @@ -25,10 +25,11 @@ insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); check table t1; Table Op Msg_type Msg_text test.t1 check status OK -select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%'; +select * from t1 ignore key (key1) where text1='teststring' or +text1 like 'teststring_%' ORDER BY text1; text1 -teststring teststring +teststring select * from t1 where text1='teststring' or text1 like 'teststring_%'; text1 teststring @@ -48,10 +49,11 @@ alter table t1 modify text1 char(32) binary not null; check table t1; Table Op Msg_type Msg_text test.t1 check status OK -select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%'; +select * from t1 ignore key (key1) where text1='teststring' or +text1 like 'teststring_%' ORDER BY text1; text1 -teststring teststring +teststring select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%'; concat('|', text1, '|') |teststring | @@ -132,10 +134,11 @@ concat('|', text1, '|') drop table t1; create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap; insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); -select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%'; +select * from t1 ignore key (key1) where text1='teststring' or +text1 like 'teststring_%' ORDER BY text1; text1 -teststring teststring +teststring select * from t1 where text1='teststring' or text1 like 'teststring_%'; text1 teststring diff --git a/mysql-test/suite/pbxt/r/heap.result b/mysql-test/suite/pbxt/r/heap.result deleted file mode 100644 index 366258882ad..00000000000 --- a/mysql-test/suite/pbxt/r/heap.result +++ /dev/null @@ -1,733 +0,0 @@ -drop table if exists t1,t2,t3; -create table t1 (a int not null,b int not null, primary key (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100; -insert into t1 values(1,1),(2,2),(3,3),(4,4); -delete from t1 where a=1 or a=0; -show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 PRIMARY 1 a NULL 3 NULL NULL HASH -select * from t1; -a b -2 2 -3 3 -4 4 -select * from t1 where a=4; -a b -4 4 -update t1 set b=5 where a=4; -update t1 set b=b+1 where a>=3; -replace t1 values (3,3); -select * from t1; -a b -2 2 -3 3 -4 6 -alter table t1 add c int not null, add key (c,a); -drop table t1; -create table t1 (a int not null,b int not null, primary key (a)) engine=memory comment="testing heaps"; -insert into t1 values(1,1),(2,2),(3,3),(4,4); -delete from t1 where a > 0; -select * from t1; -a b -drop table t1; -create table t1 (a int not null,b int not null, primary key (a)) engine=heap comment="testing heaps"; -insert into t1 values(1,1),(2,2),(3,3),(4,4); -alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table"; -select * from t1; -a b -1 1 -2 2 -3 3 -4 4 -drop table t1; -create table t1 (a int not null) engine=heap; -insert into t1 values (869751),(736494),(226312),(802616),(728912); -select * from t1 where a > 736494; -a -869751 -802616 -alter table t1 add unique uniq_id(a); -select * from t1 where a > 736494; -a -869751 -802616 -select * from t1 where a = 736494; -a -736494 -select * from t1 where a=869751 or a=736494; -a -736494 -869751 -select * from t1 where a in (869751,736494,226312,802616); -a -226312 -736494 -802616 -869751 -alter table t1 engine=myisam; -explain select * from t1 where a in (869751,736494,226312,802616); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uniq_id uniq_id 4 NULL 4 Using where; Using index -drop table t1; -create table t1 (x int not null, y int not null, key x (x), unique y (y)) -engine=heap; -insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6); -select * from t1 where x=1; -x y -1 3 -1 1 -select * from t1,t1 as t2 where t1.x=t2.y; -x y x y -1 1 1 1 -2 2 2 2 -1 3 1 1 -2 4 2 2 -2 5 2 2 -2 6 2 2 -explain select * from t1,t1 as t2 where t1.x=t2.y; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL x NULL NULL NULL 6 -1 SIMPLE t2 eq_ref y y 4 test.t1.x 1 -drop table t1; -create table t1 (a int) engine=heap; -insert into t1 values(1); -select max(a) from t1; -max(a) -1 -drop table t1; -CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key(a), key(b) ) ENGINE=HEAP; -insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); -select * from t1 where a=1; -a b -1 6 -1 5 -1 4 -1 3 -1 2 -1 1 -insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); -select * from t1 where a=1; -a b -1 6 -1 5 -1 4 -1 3 -1 2 -1 1 -1 6 -1 5 -1 4 -1 3 -1 2 -1 1 -drop table t1; -create table t1 (id int unsigned not null, primary key (id)) engine=HEAP; -insert into t1 values(1); -select max(id) from t1; -max(id) -1 -insert into t1 values(2); -select max(id) from t1; -max(id) -2 -replace into t1 values(1); -drop table t1; -create table t1 (n int) engine=heap; -drop table t1; -create table t1 (n int) engine=heap; -drop table if exists t1; -CREATE table t1(f1 int not null,f2 char(20) not -null,index(f2)) engine=heap; -INSERT into t1 set f1=12,f2="bill"; -INSERT into t1 set f1=13,f2="bill"; -INSERT into t1 set f1=14,f2="bill"; -INSERT into t1 set f1=15,f2="bill"; -INSERT into t1 set f1=16,f2="ted"; -INSERT into t1 set f1=12,f2="ted"; -INSERT into t1 set f1=12,f2="ted"; -INSERT into t1 set f1=12,f2="ted"; -INSERT into t1 set f1=12,f2="ted"; -delete from t1 where f2="bill"; -select * from t1; -f1 f2 -16 ted -12 ted -12 ted -12 ted -12 ted -drop table t1; -create table t1 (btn char(10) not null, key(btn)) engine=heap; -insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i"); -explain select * from t1 where btn like "q%"; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where -select * from t1 where btn like "q%"; -btn -alter table t1 add column new_col char(1) not null, add key (btn,new_col), drop key btn; -update t1 set new_col=left(btn,1); -explain select * from t1 where btn="a"; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where -explain select * from t1 where btn="a" and new_col="a"; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref btn btn 11 const,const 2 Using where -drop table t1; -CREATE TABLE t1 ( -a int default NULL, -b int default NULL, -KEY a (a), -UNIQUE b (b) -) engine=heap; -INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3); -SELECT * FROM t1 WHERE a=NULL; -a b -explain SELECT * FROM t1 WHERE a IS NULL; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 5 const 2 Using where -SELECT * FROM t1 WHERE a<=>NULL; -a b -NULL 99 -SELECT * FROM t1 WHERE b=NULL; -a b -explain SELECT * FROM t1 WHERE b IS NULL; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b b 5 const 1 Using where -SELECT * FROM t1 WHERE b<=>NULL; -a b -99 NULL -INSERT INTO t1 VALUES (1,3); -ERROR 23000: Duplicate entry '3' for key 'b' -DROP TABLE t1; -CREATE TABLE t1 ( -a int default NULL, -key a (a) -) ENGINE=HEAP; -INSERT INTO t1 VALUES (10), (10), (10); -EXPLAIN SELECT * FROM t1 WHERE a=10; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 5 const 3 -SELECT * FROM t1 WHERE a=10; -a -10 -10 -10 -DROP TABLE t1; -CREATE TABLE t1 (a int not null, primary key(a)) engine=heap; -INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); -DELETE from t1 where a < 100; -SELECT * from t1; -a -DROP TABLE t1; -CREATE TABLE `job_titles` ( -`job_title_id` int(6) unsigned NOT NULL default '0', -`job_title` char(18) NOT NULL default '', -PRIMARY KEY (`job_title_id`), -UNIQUE KEY `job_title_id` (`job_title_id`,`job_title`) -) ENGINE=HEAP; -SELECT MAX(job_title_id) FROM job_titles; -MAX(job_title_id) -NULL -DROP TABLE job_titles; -CREATE TABLE t1 (a INT NOT NULL, B INT, KEY(B)) ENGINE=HEAP; -INSERT INTO t1 VALUES(1,1), (1,NULL); -SELECT * FROM t1 WHERE B is not null; -a B -1 1 -DROP TABLE t1; -CREATE TABLE t1 (pseudo char(35) PRIMARY KEY, date int(10) unsigned NOT NULL) ENGINE=HEAP; -INSERT INTO t1 VALUES ('massecot',1101106491),('altec',1101106492),('stitch+',1101106304),('Seb Corgan',1101106305),('beerfilou',1101106263),('flaker',1101106529),('joce8',5),('M4vrick',1101106418),('gabay008',1101106525),('Vamp irX',1101106291),('ZoomZip',1101106546),('rip666',1101106502),('CBP ',1101106397),('guezpard',1101106496); -DELETE FROM t1 WHERE date<1101106546; -SELECT * FROM t1; -pseudo date -ZoomZip 1101106546 -DROP TABLE t1; -create table t1(a char(2)) engine=memory; -insert into t1 values (NULL), (NULL); -delete from t1 where a is null; -insert into t1 values ('2'), ('3'); -select * from t1; -a -3 -2 -drop table t1; -set storage_engine=HEAP; -create table t1 (v varchar(10), c char(10), t varchar(50)); -insert into t1 values('+ ', '+ ', '+ '); -set @a=repeat(' ',20); -insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); -Warnings: -Note 1265 Data truncated for column 'v' at row 1 -select concat('*',v,'*',c,'*',t,'*') from t1; -concat('*',v,'*',c,'*',t,'*') -*+ *+*+ * -*+ *+*+ * -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `v` varchar(10) DEFAULT NULL, - `c` char(10) DEFAULT NULL, - `t` varchar(50) DEFAULT NULL -) ENGINE=MEMORY DEFAULT CHARSET=latin1 -create table t2 like t1; -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `v` varchar(10) DEFAULT NULL, - `c` char(10) DEFAULT NULL, - `t` varchar(50) DEFAULT NULL -) ENGINE=MEMORY DEFAULT CHARSET=latin1 -create table t3 select * from t1; -show create table t3; -Table Create Table -t3 CREATE TABLE `t3` ( - `v` varchar(10) DEFAULT NULL, - `c` char(10) DEFAULT NULL, - `t` varchar(50) DEFAULT NULL -) ENGINE=MEMORY DEFAULT CHARSET=latin1 -alter table t1 modify c varchar(10); -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `v` varchar(10) DEFAULT NULL, - `c` varchar(10) DEFAULT NULL, - `t` varchar(50) DEFAULT NULL -) ENGINE=MEMORY DEFAULT CHARSET=latin1 -alter table t1 modify v char(10); -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `v` char(10) DEFAULT NULL, - `c` varchar(10) DEFAULT NULL, - `t` varchar(50) DEFAULT NULL -) ENGINE=MEMORY DEFAULT CHARSET=latin1 -alter table t1 modify t varchar(10); -Warnings: -Warning 1265 Data truncated for column 't' at row 2 -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `v` char(10) DEFAULT NULL, - `c` varchar(10) DEFAULT NULL, - `t` varchar(10) DEFAULT NULL -) ENGINE=MEMORY DEFAULT CHARSET=latin1 -select concat('*',v,'*',c,'*',t,'*') from t1; -concat('*',v,'*',c,'*',t,'*') -*+*+*+ * -*+*+*+ * -drop table t1,t2,t3; -create table t1 (v varchar(10), c char(10), t varchar(50), key(v), key(c), key(t(10))); -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `v` varchar(10) DEFAULT NULL, - `c` char(10) DEFAULT NULL, - `t` varchar(50) DEFAULT NULL, - KEY `v` (`v`), - KEY `c` (`c`), - KEY `t` (`t`(10)) -) ENGINE=MEMORY DEFAULT CHARSET=latin1 -select count(*) from t1; -count(*) -270 -insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); -select count(*) from t1 where v='a'; -count(*) -10 -select count(*) from t1 where c='a'; -count(*) -10 -select count(*) from t1 where t='a'; -count(*) -10 -select count(*) from t1 where v='a '; -count(*) -10 -select count(*) from t1 where c='a '; -count(*) -10 -select count(*) from t1 where t='a '; -count(*) -10 -select count(*) from t1 where v between 'a' and 'a '; -count(*) -10 -select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; -count(*) -10 -select count(*) from t1 where v like 'a%'; -count(*) -11 -select count(*) from t1 where c like 'a%'; -count(*) -11 -select count(*) from t1 where t like 'a%'; -count(*) -11 -select count(*) from t1 where v like 'a %'; -count(*) -9 -explain select count(*) from t1 where v='a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 13 const 10 Using where -explain select count(*) from t1 where c='a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c c 11 const 10 Using where -explain select count(*) from t1 where t='a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref t t 13 const 10 Using where -explain select count(*) from t1 where v like 'a%'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL v NULL NULL NULL 271 Using where -explain select count(*) from t1 where v between 'a' and 'a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 13 const 10 Using where -explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 13 const 10 Using where -alter table t1 add unique(v); -ERROR 23000: Duplicate entry '{ ' for key 'v_2' -select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*')); -qq -*a*a*a* -*a *a*a * -*a *a*a * -*a *a*a * -*a *a*a * -*a *a*a * -*a *a*a * -*a *a*a * -*a *a*a * -*a *a*a * -explain select * from t1 where v='a'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 13 const 10 Using where -select v,count(*) from t1 group by v limit 10; -v count(*) -a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 -select v,count(t) from t1 group by v limit 10; -v count(t) -a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 -select v,count(c) from t1 group by v limit 10; -v count(c) -a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 -select sql_big_result trim(v),count(t) from t1 group by v limit 10; -trim(v) count(t) -a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 -select sql_big_result trim(v),count(c) from t1 group by v limit 10; -trim(v) count(c) -a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 -select c,count(*) from t1 group by c limit 10; -c count(*) -a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 -select c,count(t) from t1 group by c limit 10; -c count(t) -a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 -select sql_big_result c,count(t) from t1 group by c limit 10; -c count(t) -a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 -select t,count(*) from t1 group by t limit 10; -t count(*) -a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 -select t,count(t) from t1 group by t limit 10; -t count(t) -a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 -select sql_big_result trim(t),count(t) from t1 group by t limit 10; -trim(t) count(t) -a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 -drop table t1; -create table t1 (a char(10), unique (a)); -insert into t1 values ('a'); -insert into t1 values ('a '); -ERROR 23000: Duplicate entry 'a' for key 'a' -alter table t1 modify a varchar(10); -insert into t1 values ('a '),('a '),('a '),('a '); -ERROR 23000: Duplicate entry 'a ' for key 'a' -insert into t1 values ('a '); -ERROR 23000: Duplicate entry 'a ' for key 'a' -insert into t1 values ('a '); -ERROR 23000: Duplicate entry 'a ' for key 'a' -insert into t1 values ('a '); -ERROR 23000: Duplicate entry 'a ' for key 'a' -update t1 set a='a ' where a like 'a '; -update t1 set a='a ' where a like 'a '; -drop table t1; -create table t1 (v varchar(10), c char(10), t varchar(50), key using btree (v), key using btree (c), key using btree (t(10))); -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `v` varchar(10) DEFAULT NULL, - `c` char(10) DEFAULT NULL, - `t` varchar(50) DEFAULT NULL, - KEY `v` (`v`) USING BTREE, - KEY `c` (`c`) USING BTREE, - KEY `t` (`t`(10)) USING BTREE -) ENGINE=MEMORY DEFAULT CHARSET=latin1 -select count(*) from t1; -count(*) -270 -insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); -select count(*) from t1 where v='a'; -count(*) -10 -select count(*) from t1 where c='a'; -count(*) -10 -select count(*) from t1 where t='a'; -count(*) -10 -select count(*) from t1 where v='a '; -count(*) -10 -select count(*) from t1 where c='a '; -count(*) -10 -select count(*) from t1 where t='a '; -count(*) -10 -select count(*) from t1 where v between 'a' and 'a '; -count(*) -10 -select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; -count(*) -10 -explain select count(*) from t1 where v='a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 13 const # Using where -explain select count(*) from t1 where c='a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c c 11 const # Using where -explain select count(*) from t1 where t='a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref t t 13 const # Using where -explain select count(*) from t1 where v like 'a%'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range v v 13 NULL # Using where -explain select count(*) from t1 where v between 'a' and 'a '; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 13 const # Using where -explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 13 const # Using where -alter table t1 add unique(v); -ERROR 23000: Duplicate entry '{ ' for key 'v_2' -select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*')); -qq -*a*a*a* -*a *a*a * -*a *a*a * -*a *a*a * -*a *a*a * -*a *a*a * -*a *a*a * -*a *a*a * -*a *a*a * -*a *a*a * -explain select * from t1 where v='a'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 13 const # Using where -drop table t1; -create table t1 (a char(10), unique using btree (a)) engine=heap; -insert into t1 values ('a'); -insert into t1 values ('a '); -ERROR 23000: Duplicate entry 'a' for key 'a' -alter table t1 modify a varchar(10); -insert into t1 values ('a '),('a '),('a '),('a '); -ERROR 23000: Duplicate entry 'a ' for key 'a' -insert into t1 values ('a '); -ERROR 23000: Duplicate entry 'a ' for key 'a' -insert into t1 values ('a '); -ERROR 23000: Duplicate entry 'a ' for key 'a' -insert into t1 values ('a '); -ERROR 23000: Duplicate entry 'a ' for key 'a' -update t1 set a='a ' where a like 'a '; -update t1 set a='a ' where a like 'a '; -drop table t1; -create table t1 (v varchar(10), c char(10), t varchar(50), key(v(5)), key(c(5)), key(t(5))); -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `v` varchar(10) DEFAULT NULL, - `c` char(10) DEFAULT NULL, - `t` varchar(50) DEFAULT NULL, - KEY `v` (`v`(5)), - KEY `c` (`c`(5)), - KEY `t` (`t`(5)) -) ENGINE=MEMORY DEFAULT CHARSET=latin1 -drop table t1; -create table t1 (v varchar(65530), key(v(10))); -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `v` varchar(65530) DEFAULT NULL, - KEY `v` (`v`(10)) -) ENGINE=MEMORY DEFAULT CHARSET=latin1 -insert into t1 values(repeat('a',65530)); -select length(v) from t1 where v=repeat('a',65530); -length(v) -65530 -drop table t1; -set storage_engine=PBXT; -create table t1 (a bigint unsigned auto_increment primary key, b int, -key (b, a)) engine=heap; -insert t1 (b) values (1),(1),(1),(1),(1),(1),(1),(1); -select * from t1; -a b -1 1 -2 1 -3 1 -4 1 -5 1 -6 1 -7 1 -8 1 -drop table t1; -create table t1 (a int not null, b int not null auto_increment, -primary key(a, b), key(b)) engine=heap; -insert t1 (a) values (1),(1),(1),(1),(1),(1),(1),(1); -select * from t1; -a b -1 1 -1 2 -1 3 -1 4 -1 5 -1 6 -1 7 -1 8 -drop table t1; -create table t1 (a int not null, b int not null auto_increment, -primary key(a, b)) engine=heap; -ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key -create table t1 (c char(255), primary key(c(90))); -insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"); -insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"); -ERROR 23000: Duplicate entry 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijkl' for key 'PRIMARY' -drop table t1; -CREATE TABLE t1 (a int, key(a)) engine=heap; -insert into t1 values (0); -delete from t1; -select * from t1; -a -insert into t1 values (0), (1); -select * from t1 where a = 0; -a -0 -drop table t1; -create table t1 (c char(10)) engine=memory; -create table t2 (c varchar(10)) engine=memory; -show table status like 't_'; -Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t1 MEMORY 10 Fixed 0 11 0 # 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL -t2 MEMORY 10 Fixed 0 12 0 # 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL -drop table t1, t2; -CREATE TABLE t1(a VARCHAR(1), b VARCHAR(2), c VARCHAR(256), -KEY(a), KEY(b), KEY(c)) ENGINE=MEMORY; -INSERT INTO t1 VALUES('a','aa',REPEAT('a', 256)),('a','aa',REPEAT('a',256)); -SELECT COUNT(*) FROM t1 WHERE a='a'; -COUNT(*) -2 -SELECT COUNT(*) FROM t1 WHERE b='aa'; -COUNT(*) -2 -SELECT COUNT(*) FROM t1 WHERE c=REPEAT('a',256); -COUNT(*) -2 -DROP TABLE t1; diff --git a/mysql-test/suite/pbxt/r/select_safe.result b/mysql-test/suite/pbxt/r/select_safe.result index 4d078e96cc2..25ac99dd17c 100644 --- a/mysql-test/suite/pbxt/r/select_safe.result +++ b/mysql-test/suite/pbxt/r/select_safe.result @@ -35,7 +35,7 @@ ERROR HY000: You are using safe update mode and you tried to update a table with 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 SQL_MAX_JOIN_SIZE=# if the SELECT is okay +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" order by a limit 1; update t1 set b="a" where b="b" order by a limit 2; delete from t1 where b="test" order by a limit 1; @@ -47,7 +47,7 @@ SELECT @@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 SQL_MAX_JOIN_SIZE=# if the SELECT is okay +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 @@ -57,7 +57,7 @@ a b 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 SQL_MAX_JOIN_SIZE=# if the SELECT is okay +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 @@ -87,12 +87,12 @@ 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 SQL_MAX_JOIN_SIZE=# if the SELECT is okay +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 SQL_MAX_JOIN_SIZE=# if the SELECT is okay +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 SQL_MAX_JOIN_SIZE=# if the SELECT is okay +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, SQL_MAX_JOIN_SIZE=DEFAULT; diff --git a/mysql-test/suite/pbxt/r/view_grant.result b/mysql-test/suite/pbxt/r/view_grant.result deleted file mode 100644 index 23484398203..00000000000 --- a/mysql-test/suite/pbxt/r/view_grant.result +++ /dev/null @@ -1,958 +0,0 @@ -drop database if exists mysqltest; -drop view if exists v1,v2,v3; -grant create view on test.* to test@localhost; -show grants for test@localhost; -Grants for test@localhost -GRANT USAGE ON *.* TO 'test'@'localhost' -GRANT CREATE VIEW ON `test`.* TO 'test'@'localhost' -revoke create view on test.* from test@localhost; -show grants for test@localhost; -Grants for test@localhost -GRANT USAGE ON *.* TO 'test'@'localhost' -drop user test@localhost; -create database mysqltest; -create table mysqltest.t1 (a int, b int); -create table mysqltest.t2 (a int, b int); -grant select on mysqltest.t1 to mysqltest_1@localhost; -grant create view,select on test.* to mysqltest_1@localhost; -create definer=root@localhost view v1 as select * from mysqltest.t1; -ERROR 42000: Access denied; you need the SUPER privilege for this operation -create view v1 as select * from mysqltest.t1; -alter view v1 as select * from mysqltest.t1; -ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' -create or replace view v1 as select * from mysqltest.t1; -ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 'v1' -create view mysqltest.v2 as select * from mysqltest.t1; -ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' -create view v2 as select * from mysqltest.t2; -ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for table 't2' -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` latin1 latin1_swedish_ci -grant create view,drop,select on test.* to mysqltest_1@localhost; -use test; -alter view v1 as select * from mysqltest.t1; -create or replace view v1 as select * from mysqltest.t1; -revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; -revoke all privileges on test.* from mysqltest_1@localhost; -drop database mysqltest; -drop view test.v1; -create database mysqltest; -create table mysqltest.t1 (a int, b int); -create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; -grant select (c) on mysqltest.v1 to mysqltest_1@localhost; -select c from mysqltest.v1; -c -select d from mysqltest.v1; -ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1' -revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -drop database mysqltest; -create database mysqltest; -create table mysqltest.t1 (a int, b int); -create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; -grant select (c) on mysqltest.v1 to mysqltest_1@localhost; -select c from mysqltest.v1; -c -select d from mysqltest.v1; -ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v1' -revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -drop database mysqltest; -create database mysqltest; -create table mysqltest.t1 (a int, b int); -create table mysqltest.t2 (a int, b int); -create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; -create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1; -create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2; -create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2; -grant select on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.v2 to mysqltest_1@localhost; -grant select on mysqltest.v3 to mysqltest_1@localhost; -grant select on mysqltest.v4 to mysqltest_1@localhost; -select c from mysqltest.v1; -c -select c from mysqltest.v2; -c -select c from mysqltest.v3; -c -select c from mysqltest.v4; -c -show columns from mysqltest.v1; -Field Type Null Key Default Extra -c bigint(12) YES NULL -d bigint(12) YES NULL -show columns from mysqltest.v2; -Field Type Null Key Default Extra -c bigint(12) YES NULL -d bigint(12) YES NULL -explain select c from mysqltest.v1; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table -show create view mysqltest.v1; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' -explain select c from mysqltest.v2; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table -show create view mysqltest.v2; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' -explain select c from mysqltest.v3; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table -show create view mysqltest.v3; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3' -explain select c from mysqltest.v4; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table -show create view mysqltest.v4; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4' -grant select on mysqltest.t1 to mysqltest_1@localhost; -explain select c from mysqltest.v1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 -show create view mysqltest.v1; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' -explain select c from mysqltest.v2; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -2 DERIVED t1 ALL NULL NULL NULL NULL 0 -show create view mysqltest.v2; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2' -explain select c from mysqltest.v3; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table -show create view mysqltest.v3; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v3' -explain select c from mysqltest.v4; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table -show create view mysqltest.v4; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v4' -grant show view on mysqltest.* to mysqltest_1@localhost; -explain select c from mysqltest.v1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 -show create view mysqltest.v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v1` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` latin1 latin1_swedish_ci -explain select c from mysqltest.v2; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -2 DERIVED t1 ALL NULL NULL NULL NULL 0 -show create view mysqltest.v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v2` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1) AS `d` from `mysqltest`.`t1` latin1 latin1_swedish_ci -explain select c from mysqltest.v3; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 0 -show create view mysqltest.v3; -View Create View character_set_client collation_connection -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v3` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2` latin1 latin1_swedish_ci -explain select c from mysqltest.v4; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -2 DERIVED t2 ALL NULL NULL NULL NULL 0 -show create view mysqltest.v4; -View Create View character_set_client collation_connection -v4 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v4` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1) AS `d` from `mysqltest`.`t2` latin1 latin1_swedish_ci -revoke all privileges on mysqltest.* from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -drop database mysqltest; -create database mysqltest; -create table mysqltest.t1 (a int, b int, primary key(a)); -insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10); -create table mysqltest.t2 (x int); -insert into mysqltest.t2 values (3), (4), (5), (6); -create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; -create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; -create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1; -grant update (a) on mysqltest.v2 to mysqltest_1@localhost; -grant update on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.* to mysqltest_1@localhost; -use mysqltest; -update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c; -select * from t1 order by a; -a b -13 2 -24 3 -35 4 -46 5 -50 10 -update v1 set a=a+c; -select * from t1 order by a; -a b -16 2 -28 3 -40 4 -52 5 -61 10 -update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c; -select * from t1 order by a; -a b -16 2 -31 3 -44 4 -57 5 -61 10 -update v2 set a=a+c; -select * from t1 order by a; -a b -18 2 -34 3 -48 4 -62 5 -71 10 -update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; -ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for column 'c' in table 'v2' -update v2 set c=a+c; -ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for column 'c' in table 'v2' -update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c; -ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 'v3' -update v3 set a=a+c; -ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 'v3' -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; -create database mysqltest; -create table mysqltest.t1 (a int, b int, primary key(a)); -insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10); -create table mysqltest.t2 (x int); -insert into mysqltest.t2 values (3), (4), (5), (6); -create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; -create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1; -grant delete on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.* to mysqltest_1@localhost; -use mysqltest; -delete from v1 where c < 4; -select * from t1; -a b -2 3 -3 4 -4 5 -5 10 -delete v1 from t2,v1 where t2.x=v1.c; -select * from t1; -a b -5 10 -delete v2 from t2,v2 where t2.x=v2.c; -ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v2' -delete from v2 where c < 4; -ERROR 42000: DELETE command denied to user 'mysqltest_1'@'localhost' for table 'v2' -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; -create database mysqltest; -create table mysqltest.t1 (a int, b int, primary key(a)); -insert into mysqltest.t1 values (1,2), (2,3); -create table mysqltest.t2 (x int, y int); -insert into mysqltest.t2 values (3,4); -create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1; -create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; -grant insert on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.* to mysqltest_1@localhost; -use mysqltest; -insert into v1 values (5,6); -select * from t1; -a b -1 2 -2 3 -5 6 -insert into v1 select x,y from t2; -select * from t1; -a b -1 2 -2 3 -5 6 -3 4 -insert into v2 values (5,6); -ERROR 42000: INSERT command denied to user 'mysqltest_1'@'localhost' for table 'v2' -insert into v2 select x,y from t2; -ERROR 42000: INSERT command denied to user 'mysqltest_1'@'localhost' for table 'v2' -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; -create database mysqltest; -create table mysqltest.t1 (a int, b int); -create table mysqltest.t2 (a int, b int); -grant update on mysqltest.t1 to mysqltest_1@localhost; -grant update(b) on mysqltest.t2 to mysqltest_1@localhost; -grant create view,update on test.* to mysqltest_1@localhost; -create view v1 as select * from mysqltest.t1; -create view v2 as select b from mysqltest.t2; -create view mysqltest.v1 as select * from mysqltest.t1; -ERROR 42000: CREATE VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' -create view v3 as select a from mysqltest.t2; -ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for column 'a' in table 't2' -create table mysqltest.v3 (b int); -grant create view on mysqltest.v3 to mysqltest_1@localhost; -drop table mysqltest.v3; -create view mysqltest.v3 as select b from mysqltest.t2; -grant create view, update on mysqltest.v3 to mysqltest_1@localhost; -drop view mysqltest.v3; -create view mysqltest.v3 as select b from mysqltest.t2; -create view v4 as select b+1 from mysqltest.t2; -ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't2' -grant create view,update,select on test.* to mysqltest_1@localhost; -create view v4 as select b+1 from mysqltest.t2; -ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't2' -grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost; -create view v4 as select b+1 from mysqltest.t2; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; -drop view v1,v2,v4; -create database mysqltest; -create table mysqltest.t1 (a int); -grant all privileges on mysqltest.* to mysqltest_1@localhost; -use mysqltest; -create view v1 as select * from t1; -use test; -revoke all privileges on mysqltest.* from mysqltest_1@localhost; -drop database mysqltest; -create database mysqltest; -create table mysqltest.t1 (a int, b int); -grant select on mysqltest.t1 to mysqltest_1@localhost; -grant create view,select on test.* to mysqltest_1@localhost; -create view v1 as select * from mysqltest.t1; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `mysqltest`.`t1`.`a` AS `a`,`mysqltest`.`t1`.`b` AS `b` from `mysqltest`.`t1` latin1 latin1_swedish_ci -revoke select on mysqltest.t1 from mysqltest_1@localhost; -select * from v1; -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -grant select on mysqltest.t1 to mysqltest_1@localhost; -select * from v1; -a b -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop view v1; -drop database mysqltest; -create database mysqltest; -use mysqltest; -create table t1 (a int); -insert into t1 values (1); -create table t2 (s1 int); -drop function if exists f2; -create function f2 () returns int begin declare v int; select s1 from t2 -into v; return v; end// -create algorithm=TEMPTABLE view v1 as select f2() from t1; -create algorithm=MERGE view v2 as select f2() from t1; -create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; -create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; -create SQL SECURITY INVOKER view v5 as select * from v4; -grant select on v1 to mysqltest_1@localhost; -grant select on v2 to mysqltest_1@localhost; -grant select on v3 to mysqltest_1@localhost; -grant select on v4 to mysqltest_1@localhost; -grant select on v5 to mysqltest_1@localhost; -use mysqltest; -select * from v1; -f2() -NULL -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -select * from v2; -f2() -NULL -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -select * from v3; -ERROR HY000: View 'mysqltest.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -select * from v4; -ERROR HY000: View 'mysqltest.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -select * from v5; -ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -use test; -drop view v1, v2, v3, v4, v5; -drop function f2; -drop table t1, t2; -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; -create database mysqltest; -use mysqltest; -create table t1 (a int); -insert into t1 values (1); -create table t2 (s1 int); -drop function if exists f2; -create function f2 () returns int begin declare v int; select s1 from t2 -into v; return v; end// -grant select on t1 to mysqltest_1@localhost; -grant execute on function f2 to mysqltest_1@localhost; -grant create view on mysqltest.* to mysqltest_1@localhost; -use mysqltest; -create algorithm=TEMPTABLE view v1 as select f2() from t1; -create algorithm=MERGE view v2 as select f2() from t1; -create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; -create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; -use test; -create view v5 as select * from v1; -revoke execute on function f2 from mysqltest_1@localhost; -select * from v1; -ERROR HY000: View 'mysqltest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -select * from v2; -ERROR HY000: View 'mysqltest.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -select * from v3; -f2() -NULL -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -select * from v4; -f2() -NULL -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -select * from v5; -ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -drop view v1, v2, v3, v4, v5; -drop function f2; -drop table t1, t2; -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; -create database mysqltest; -use mysqltest; -create table t1 (a int); -create table v1 (a int); -insert into t1 values (1); -grant select on t1 to mysqltest_1@localhost; -grant select on v1 to mysqltest_1@localhost; -grant create view on mysqltest.* to mysqltest_1@localhost; -drop table v1; -use mysqltest; -create algorithm=TEMPTABLE view v1 as select *, a as b from t1; -create algorithm=MERGE view v2 as select *, a as b from t1; -create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; -create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; -create view v5 as select * from v1; -use test; -revoke select on t1 from mysqltest_1@localhost; -select * from v1; -ERROR HY000: View 'mysqltest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -select * from v2; -ERROR HY000: View 'mysqltest.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -select * from v3; -a b -1 1 -select * from v4; -a b -1 1 -select * from v5; -ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -drop table t1; -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; -create database mysqltest; -use mysqltest; -create table t1 (a int); -insert into t1 values (1); -create algorithm=TEMPTABLE view v1 as select *, a as b from t1; -create algorithm=MERGE view v2 as select *, a as b from t1; -create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; -create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; -create SQL SECURITY INVOKER view v5 as select * from v4; -grant select on v1 to mysqltest_1@localhost; -grant select on v2 to mysqltest_1@localhost; -grant select on v3 to mysqltest_1@localhost; -grant select on v4 to mysqltest_1@localhost; -grant select on v5 to mysqltest_1@localhost; -use mysqltest; -select * from v1; -a b -1 1 -select * from v2; -a b -1 1 -select * from v3; -ERROR HY000: View 'mysqltest.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -select * from v4; -ERROR HY000: View 'mysqltest.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -select * from v5; -ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -use test; -drop view v1, v2, v3, v4, v5; -drop table t1; -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; -drop view if exists v1; -create table t1 as select * from mysql.user where user=''; -delete from mysql.user where user=''; -flush privileges; -grant all on test.* to 'test14256'@'%'; -use test; -create view v1 as select 42; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42` latin1 latin1_swedish_ci -select definer into @v1def1 from information_schema.views -where table_schema = 'test' and table_name='v1'; -drop view v1; -create definer=`test14256`@`%` view v1 as select 42; -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`test14256`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 42 AS `42` latin1 latin1_swedish_ci -select definer into @v1def2 from information_schema.views -where table_schema = 'test' and table_name='v1'; -drop view v1; -select @v1def1, @v1def2, @v1def1=@v1def2; -@v1def1 @v1def2 @v1def1=@v1def2 -test14256@% test14256@% 1 -drop user test14256; -insert into mysql.user select * from t1; -flush privileges; -drop table t1; -create database mysqltest; -use mysqltest; -CREATE TABLE t1 (i INT); -CREATE VIEW v1 AS SELECT * FROM t1; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci -GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost; -use mysqltest; -LOCK TABLES v1 READ; -SHOW CREATE TABLE v1; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1' -UNLOCK TABLES; -use test; -use test; -drop user mysqltest_1@localhost; -drop database mysqltest; -create definer=some_user@`` sql security invoker view v1 as select 1; -Warnings: -Note 1449 The user specified as a definer ('some_user'@'') does not exist -create definer=some_user@localhost sql security invoker view v2 as select 1; -Warnings: -Note 1449 The user specified as a definer ('some_user'@'localhost') does not exist -show create view v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` latin1 latin1_swedish_ci -show create view v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select 1 AS `1` latin1 latin1_swedish_ci -drop view v1; -drop view v2; -CREATE DATABASE mysqltest1; -CREATE USER readonly@localhost; -CREATE TABLE mysqltest1.t1 (x INT); -INSERT INTO mysqltest1.t1 VALUES (1), (2); -CREATE SQL SECURITY INVOKER VIEW mysqltest1.v_t1 AS SELECT * FROM mysqltest1.t1; -CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ts AS SELECT * FROM mysqltest1.t1; -CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ti AS SELECT * FROM mysqltest1.t1; -CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tu AS SELECT * FROM mysqltest1.t1; -CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tus AS SELECT * FROM mysqltest1.t1; -CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_td AS SELECT * FROM mysqltest1.t1; -CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tds AS SELECT * FROM mysqltest1.t1; -GRANT SELECT, INSERT, UPDATE, DELETE ON mysqltest1.v_t1 TO readonly@localhost; -GRANT SELECT ON mysqltest1.v_ts TO readonly@localhost; -GRANT INSERT ON mysqltest1.v_ti TO readonly@localhost; -GRANT UPDATE ON mysqltest1.v_tu TO readonly@localhost; -GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly@localhost; -GRANT DELETE ON mysqltest1.v_td TO readonly@localhost; -GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly@localhost; -SELECT * FROM mysqltest1.v_t1; -ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -INSERT INTO mysqltest1.v_t1 VALUES(4); -ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -DELETE FROM mysqltest1.v_t1 WHERE x = 1; -ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -UPDATE mysqltest1.v_t1 SET x = 3 WHERE x = 2; -ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -UPDATE mysqltest1.v_t1 SET x = 3; -ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -DELETE FROM mysqltest1.v_t1; -ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -SELECT 1 FROM mysqltest1.v_t1; -ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -SELECT * FROM mysqltest1.t1; -ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 't1' -SELECT * FROM mysqltest1.v_ts; -x -1 -2 -SELECT * FROM mysqltest1.v_ts, mysqltest1.t1 WHERE mysqltest1.t1.x = mysqltest1.v_ts.x; -ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 't1' -SELECT * FROM mysqltest1.v_ti; -ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 'v_ti' -INSERT INTO mysqltest1.v_ts VALUES (100); -ERROR 42000: INSERT command denied to user 'readonly'@'localhost' for table 'v_ts' -INSERT INTO mysqltest1.v_ti VALUES (100); -UPDATE mysqltest1.v_ts SET x= 200 WHERE x = 100; -ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table 'v_ts' -UPDATE mysqltest1.v_ts SET x= 200; -ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table 'v_ts' -UPDATE mysqltest1.v_tu SET x= 200 WHERE x = 100; -UPDATE mysqltest1.v_tus SET x= 200 WHERE x = 100; -UPDATE mysqltest1.v_tu SET x= 200; -DELETE FROM mysqltest1.v_ts WHERE x= 200; -ERROR 42000: DELETE command denied to user 'readonly'@'localhost' for table 'v_ts' -DELETE FROM mysqltest1.v_ts; -ERROR 42000: DELETE command denied to user 'readonly'@'localhost' for table 'v_ts' -DELETE FROM mysqltest1.v_td WHERE x= 200; -ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for column 'x' in table 'v_td' -DELETE FROM mysqltest1.v_tds WHERE x= 200; -DELETE FROM mysqltest1.v_td; -DROP VIEW mysqltest1.v_tds; -DROP VIEW mysqltest1.v_td; -DROP VIEW mysqltest1.v_tus; -DROP VIEW mysqltest1.v_tu; -DROP VIEW mysqltest1.v_ti; -DROP VIEW mysqltest1.v_ts; -DROP VIEW mysqltest1.v_t1; -DROP TABLE mysqltest1.t1; -DROP USER readonly@localhost; -DROP DATABASE mysqltest1; -CREATE TABLE t1 (a INT PRIMARY KEY); -INSERT INTO t1 VALUES (1), (2), (3); -CREATE DEFINER = 'no-such-user'@localhost VIEW v AS SELECT a from t1; -Warnings: -Note 1449 The user specified as a definer ('no-such-user'@'localhost') does not exist -SHOW CREATE VIEW v; -View Create View character_set_client collation_connection -v CREATE ALGORITHM=UNDEFINED DEFINER=`no-such-user`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `test`.`t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci -Warnings: -Note 1449 The user specified as a definer ('no-such-user'@'localhost') does not exist -SELECT * FROM v; -ERROR HY000: The user specified as a definer ('no-such-user'@'localhost') does not exist -DROP VIEW v; -DROP TABLE t1; -USE test; -CREATE USER mysqltest_db1@localhost identified by 'PWD'; -GRANT ALL ON mysqltest_db1.* TO mysqltest_db1@localhost WITH GRANT OPTION; -CREATE SCHEMA mysqltest_db1 ; -USE mysqltest_db1 ; -CREATE TABLE t1 (f1 INTEGER); -CREATE VIEW view1 AS -SELECT * FROM t1; -SHOW CREATE VIEW view1; -View Create View character_set_client collation_connection -view1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci -CREATE VIEW view2 AS -SELECT * FROM view1; -# Here comes a suspicious warning -SHOW CREATE VIEW view2; -View Create View character_set_client collation_connection -view2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view2` AS select `view1`.`f1` AS `f1` from `view1` latin1 latin1_swedish_ci -# But the view view2 is usable -SELECT * FROM view2; -f1 -CREATE VIEW view3 AS -SELECT * FROM view2; -SELECT * from view3; -f1 -DROP VIEW mysqltest_db1.view3; -DROP VIEW mysqltest_db1.view2; -DROP VIEW mysqltest_db1.view1; -DROP TABLE mysqltest_db1.t1; -DROP SCHEMA mysqltest_db1; -DROP USER mysqltest_db1@localhost; -CREATE DATABASE test1; -CREATE DATABASE test2; -CREATE TABLE test1.t0 (a VARCHAR(20)) engine=myisam; -CREATE TABLE test2.t1 (a VARCHAR(20)); -CREATE VIEW test2.t3 AS SELECT * FROM test1.t0; -CREATE OR REPLACE VIEW test.v1 AS -SELECT ta.a AS col1, tb.a AS col2 FROM test2.t3 ta, test2.t1 tb; -DROP VIEW test.v1; -DROP VIEW test2.t3; -DROP TABLE test2.t1, test1.t0; -DROP DATABASE test2; -DROP DATABASE test1; -DROP VIEW IF EXISTS v1; -DROP VIEW IF EXISTS v2; -DROP VIEW IF EXISTS v3; -DROP FUNCTION IF EXISTS f1; -DROP FUNCTION IF EXISTS f2; -DROP PROCEDURE IF EXISTS p1; -CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT CURRENT_USER() AS cu; -CREATE FUNCTION f1() RETURNS VARCHAR(77) SQL SECURITY INVOKER -RETURN CURRENT_USER(); -CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT f1() AS cu; -CREATE PROCEDURE p1(OUT cu VARCHAR(77)) SQL SECURITY INVOKER -SET cu= CURRENT_USER(); -CREATE FUNCTION f2() RETURNS VARCHAR(77) SQL SECURITY INVOKER -BEGIN -DECLARE cu VARCHAR(77); -CALL p1(cu); -RETURN cu; -END| -CREATE SQL SECURITY DEFINER VIEW v3 AS SELECT f2() AS cu; -CREATE USER mysqltest_u1@localhost; -GRANT ALL ON test.* TO mysqltest_u1@localhost; - -The following tests should all return 1. - -SELECT CURRENT_USER() = 'mysqltest_u1@localhost'; -CURRENT_USER() = 'mysqltest_u1@localhost' -1 -SELECT f1() = 'mysqltest_u1@localhost'; -f1() = 'mysqltest_u1@localhost' -1 -CALL p1(@cu); -SELECT @cu = 'mysqltest_u1@localhost'; -@cu = 'mysqltest_u1@localhost' -1 -SELECT f2() = 'mysqltest_u1@localhost'; -f2() = 'mysqltest_u1@localhost' -1 -SELECT cu = 'root@localhost' FROM v1; -cu = 'root@localhost' -1 -SELECT cu = 'root@localhost' FROM v2; -cu = 'root@localhost' -1 -SELECT cu = 'root@localhost' FROM v3; -cu = 'root@localhost' -1 -DROP VIEW v3; -DROP FUNCTION f2; -DROP PROCEDURE p1; -DROP FUNCTION f1; -DROP VIEW v2; -DROP VIEW v1; -DROP USER mysqltest_u1@localhost; -CREATE DATABASE db17254; -USE db17254; -CREATE TABLE t1 (f1 INT); -INSERT INTO t1 VALUES (10),(20); -CREATE USER def_17254@localhost; -GRANT SELECT ON db17254.* TO def_17254@localhost; -CREATE USER inv_17254@localhost; -GRANT SELECT ON db17254.t1 TO inv_17254@localhost; -GRANT CREATE VIEW ON db17254.* TO def_17254@localhost; -CREATE VIEW v1 AS SELECT * FROM t1; -DROP USER def_17254@localhost; -for a user -SELECT * FROM v1; -ERROR 42000: SELECT command denied to user 'inv_17254'@'localhost' for table 'v1' -for a superuser -SELECT * FROM v1; -ERROR HY000: The user specified as a definer ('def_17254'@'localhost') does not exist -DROP USER inv_17254@localhost; -DROP DATABASE db17254; -DROP DATABASE IF EXISTS mysqltest_db1; -DROP DATABASE IF EXISTS mysqltest_db2; -DROP USER mysqltest_u1; -DROP USER mysqltest_u2; -CREATE USER mysqltest_u1@localhost; -CREATE USER mysqltest_u2@localhost; -CREATE DATABASE mysqltest_db1; -CREATE DATABASE mysqltest_db2; -GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost WITH GRANT OPTION; -GRANT ALL ON mysqltest_db2.* TO mysqltest_u2@localhost; -CREATE TABLE t1 (i INT); -INSERT INTO t1 VALUES (1); -CREATE VIEW v1 AS SELECT i FROM t1 WHERE 1 IN (SELECT * FROM t1); -CREATE TABLE t2 (s CHAR(7)); -INSERT INTO t2 VALUES ('public'); -GRANT SELECT ON v1 TO mysqltest_u2@localhost; -GRANT SELECT ON t2 TO mysqltest_u2@localhost; -SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2; -i s -1 public -PREPARE stmt1 FROM "SELECT * FROM mysqltest_db1.t2"; -EXECUTE stmt1; -s -public -PREPARE stmt2 FROM "SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2"; -EXECUTE stmt2; -i s -1 public -REVOKE SELECT ON t2 FROM mysqltest_u2@localhost; -UPDATE t2 SET s = 'private' WHERE s = 'public'; -SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2; -ERROR 42000: SELECT command denied to user 'mysqltest_u2'@'localhost' for table 't2' -EXECUTE stmt1; -ERROR 42000: SELECT command denied to user 'mysqltest_u2'@'localhost' for table 't2' -EXECUTE stmt2; -ERROR 42000: SELECT command denied to user 'mysqltest_u2'@'localhost' for table 't2' -REVOKE ALL ON mysqltest_db1.* FROM mysqltest_u1@localhost; -REVOKE ALL ON mysqltest_db2.* FROM mysqltest_u2@localhost; -DROP DATABASE mysqltest_db1; -DROP DATABASE mysqltest_db2; -DROP USER mysqltest_u1@localhost; -DROP USER mysqltest_u2@localhost; -CREATE DATABASE db26813; -USE db26813; -CREATE TABLE t1(f1 INT, f2 INT); -CREATE VIEW v1 AS SELECT f1 FROM t1; -CREATE VIEW v2 AS SELECT f1 FROM t1; -CREATE VIEW v3 AS SELECT f1 FROM t1; -CREATE USER u26813@localhost; -GRANT DROP ON db26813.v1 TO u26813@localhost; -GRANT CREATE VIEW ON db26813.v2 TO u26813@localhost; -GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost; -GRANT SELECT ON db26813.t1 TO u26813@localhost; -ALTER VIEW v1 AS SELECT f2 FROM t1; -ERROR 42000: Access denied; you need the SUPER privilege for this operation -ALTER VIEW v2 AS SELECT f2 FROM t1; -ERROR 42000: Access denied; you need the SUPER privilege for this operation -ALTER VIEW v3 AS SELECT f2 FROM t1; -ERROR 42000: Access denied; you need the SUPER privilege for this operation -SHOW CREATE VIEW v3; -View Create View character_set_client collation_connection -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci -DROP USER u26813@localhost; -DROP DATABASE db26813; -# -# Bug#29908: A user can gain additional access through the ALTER VIEW. -# -CREATE DATABASE mysqltest_29908; -USE mysqltest_29908; -CREATE TABLE t1(f1 INT, f2 INT); -CREATE USER u29908_1@localhost; -CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1; -CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS -SELECT f1 FROM t1; -GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost; -GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost; -GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost; -CREATE USER u29908_2@localhost; -GRANT DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost; -GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost; -GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost; -ALTER VIEW v1 AS SELECT f2 FROM t1; -ERROR 42000: Access denied; you need the SUPER privilege for this operation -ALTER VIEW v2 AS SELECT f2 FROM t1; -ERROR 42000: Access denied; you need the SUPER privilege for this operation -SHOW CREATE VIEW v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci -ALTER VIEW v1 AS SELECT f2 FROM t1; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f2` AS `f2` from `t1` latin1 latin1_swedish_ci -ALTER VIEW v2 AS SELECT f2 FROM t1; -SHOW CREATE VIEW v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f2` AS `f2` from `t1` latin1 latin1_swedish_ci -ALTER VIEW v1 AS SELECT f1 FROM t1; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci -ALTER VIEW v2 AS SELECT f1 FROM t1; -SHOW CREATE VIEW v2; -View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci -DROP USER u29908_1@localhost; -DROP USER u29908_2@localhost; -DROP DATABASE mysqltest_29908; -####################################################################### -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; -CREATE DATABASE mysqltest1; -CREATE DATABASE mysqltest2; -CREATE TABLE mysqltest1.t1(c1 INT); -CREATE TABLE mysqltest1.t2(c2 INT); -CREATE TABLE mysqltest1.t3(c3 INT); -CREATE TABLE mysqltest1.t4(c4 INT); -INSERT INTO mysqltest1.t1 VALUES (11), (12), (13), (14); -INSERT INTO mysqltest1.t2 VALUES (21), (22), (23), (24); -INSERT INTO mysqltest1.t3 VALUES (31), (32), (33), (34); -INSERT INTO mysqltest1.t4 VALUES (41), (42), (43), (44); -GRANT SELECT ON mysqltest1.t1 TO mysqltest_u1@localhost; -GRANT INSERT ON mysqltest1.t2 TO mysqltest_u1@localhost; -GRANT SELECT, UPDATE ON mysqltest1.t3 TO mysqltest_u1@localhost; -GRANT SELECT, DELETE ON mysqltest1.t4 TO mysqltest_u1@localhost; -GRANT ALL PRIVILEGES ON mysqltest2.* TO mysqltest_u1@localhost; - ----> connection: bug24040_con -SELECT * FROM mysqltest1.t1; -c1 -11 -12 -13 -14 -INSERT INTO mysqltest1.t2 VALUES(25); -UPDATE mysqltest1.t3 SET c3 = 331 WHERE c3 = 31; -DELETE FROM mysqltest1.t4 WHERE c4 = 44; -CREATE VIEW v1 AS SELECT * FROM mysqltest1.t1; -CREATE VIEW v2 AS SELECT * FROM mysqltest1.t2; -CREATE VIEW v3 AS SELECT * FROM mysqltest1.t3; -CREATE VIEW v4 AS SELECT * FROM mysqltest1.t4; -SELECT * FROM v1; -c1 -11 -12 -13 -14 -INSERT INTO v2 VALUES(26); -UPDATE v3 SET c3 = 332 WHERE c3 = 32; -DELETE FROM v4 WHERE c4 = 43; -CREATE VIEW v12 AS SELECT c1, c2 FROM mysqltest1.t1, mysqltest1.t2; -ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c2' in table 'v12' -CREATE VIEW v13 AS SELECT c1, c3 FROM mysqltest1.t1, mysqltest1.t3; -CREATE VIEW v14 AS SELECT c1, c4 FROM mysqltest1.t1, mysqltest1.t4; -CREATE VIEW v21 AS SELECT c2, c1 FROM mysqltest1.t2, mysqltest1.t1; -ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c1' in table 'v21' -CREATE VIEW v23 AS SELECT c2, c3 FROM mysqltest1.t2, mysqltest1.t3; -ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c3' in table 'v23' -CREATE VIEW v24 AS SELECT c2, c4 FROM mysqltest1.t2, mysqltest1.t4; -ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c4' in table 'v24' -CREATE VIEW v31 AS SELECT c3, c1 FROM mysqltest1.t3, mysqltest1.t1; -CREATE VIEW v32 AS SELECT c3, c2 FROM mysqltest1.t3, mysqltest1.t2; -ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c2' in table 'v32' -CREATE VIEW v34 AS SELECT c3, c4 FROM mysqltest1.t3, mysqltest1.t4; -CREATE VIEW v41 AS SELECT c4, c1 FROM mysqltest1.t4, mysqltest1.t1; -CREATE VIEW v42 AS SELECT c4, c2 FROM mysqltest1.t4, mysqltest1.t2; -ERROR 42000: create view command denied to user 'mysqltest_u1'@'localhost' for column 'c2' in table 'v42' -CREATE VIEW v43 AS SELECT c4, c3 FROM mysqltest1.t4, mysqltest1.t3; - ----> connection: default -SELECT * FROM mysqltest1.t1; -c1 -11 -12 -13 -14 -SELECT * FROM mysqltest1.t2; -c2 -21 -22 -23 -24 -25 -26 -SELECT * FROM mysqltest1.t3 order by c3; -c3 -33 -34 -331 -332 -SELECT * FROM mysqltest1.t4; -c4 -41 -42 -DROP DATABASE mysqltest1; -DROP DATABASE mysqltest2; -DROP USER mysqltest_u1@localhost; -End of 5.0 tests. -DROP VIEW IF EXISTS v1; -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (i INT); -CREATE VIEW v1 AS SELECT * FROM t1; -ALTER VIEW v1 AS SELECT * FROM t1; -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci -ALTER DEFINER=no_such@user_1 VIEW v1 AS SELECT * FROM t1; -Warnings: -Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci -Warnings: -Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist -ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; -Warnings: -Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=MERGE DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci -Warnings: -Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist -ALTER ALGORITHM=TEMPTABLE DEFINER=no_such@user_2 VIEW v1 AS SELECT * FROM t1; -Warnings: -Note 1449 The user specified as a definer ('no_such'@'user_2') does not exist -SHOW CREATE VIEW v1; -View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`no_such`@`user_2` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci -Warnings: -Note 1449 The user specified as a definer ('no_such'@'user_2') does not exist -DROP VIEW v1; -DROP TABLE t1; -End of 5.1 tests. diff --git a/mysql-test/suite/pbxt/t/endspace.test b/mysql-test/suite/pbxt/t/endspace.test index f2dcc1225a4..a2962544a43 100644 --- a/mysql-test/suite/pbxt/t/endspace.test +++ b/mysql-test/suite/pbxt/t/endspace.test @@ -16,7 +16,8 @@ drop table if exists t1; create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)); insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); check table t1; -select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%'; +select * from t1 ignore key (key1) where text1='teststring' or + text1 like 'teststring_%' ORDER BY text1; select * from t1 where text1='teststring' or text1 like 'teststring_%'; select * from t1 where text1='teststring' or text1 > 'teststring\t'; select * from t1 order by text1; @@ -24,7 +25,8 @@ explain select * from t1 order by text1; alter table t1 modify text1 char(32) binary not null; check table t1; -select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%'; +select * from t1 ignore key (key1) where text1='teststring' or + text1 like 'teststring_%' ORDER BY text1; select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%'; select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; select text1, length(text1) from t1 order by text1; @@ -57,7 +59,8 @@ drop table t1; create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap; insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); -select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%'; +select * from t1 ignore key (key1) where text1='teststring' or + text1 like 'teststring_%' ORDER BY text1; select * from t1 where text1='teststring' or text1 like 'teststring_%'; select * from t1 where text1='teststring' or text1 >= 'teststring\t'; select * from t1 order by text1; @@ -90,12 +93,13 @@ alter table t1 modify text1 text not null, pack_keys=1; select * from t1 where text1 like 'teststring_%'; # The following gives wrong result in InnoDB +--sorted_result select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%'; +--sorted_result select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t'; select concat('|', text1, '|') from t1 order by text1; drop table t1; - --disable_query_log drop database pbxt; --enable_query_log diff --git a/mysql-test/suite/pbxt/t/heap.test b/mysql-test/suite/pbxt/t/heap.test deleted file mode 100644 index 2d863827b2b..00000000000 --- a/mysql-test/suite/pbxt/t/heap.test +++ /dev/null @@ -1,476 +0,0 @@ -# -# Test of heap tables. -# - ---disable_warnings -drop table if exists t1,t2,t3; ---enable_warnings - -create table t1 (a int not null,b int not null, primary key (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100; -insert into t1 values(1,1),(2,2),(3,3),(4,4); -delete from t1 where a=1 or a=0; -#show table status like "t1"; -show keys from t1; -select * from t1; -select * from t1 where a=4; -update t1 set b=5 where a=4; -update t1 set b=b+1 where a>=3; -replace t1 values (3,3); -select * from t1; -alter table t1 add c int not null, add key (c,a); -drop table t1; - -create table t1 (a int not null,b int not null, primary key (a)) engine=memory comment="testing heaps"; -insert into t1 values(1,1),(2,2),(3,3),(4,4); -delete from t1 where a > 0; -select * from t1; -drop table t1; - -create table t1 (a int not null,b int not null, primary key (a)) engine=heap comment="testing heaps"; -insert into t1 values(1,1),(2,2),(3,3),(4,4); -alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table"; -#show table status like "t1"; -select * from t1; -drop table t1; - -create table t1 (a int not null) engine=heap; -insert into t1 values (869751),(736494),(226312),(802616),(728912); -select * from t1 where a > 736494; -alter table t1 add unique uniq_id(a); -select * from t1 where a > 736494; -select * from t1 where a = 736494; -select * from t1 where a=869751 or a=736494; -select * from t1 where a in (869751,736494,226312,802616); -alter table t1 engine=myisam; -explain select * from t1 where a in (869751,736494,226312,802616); -drop table t1; - -create table t1 (x int not null, y int not null, key x (x), unique y (y)) -engine=heap; -insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6); -select * from t1 where x=1; -select * from t1,t1 as t2 where t1.x=t2.y; -explain select * from t1,t1 as t2 where t1.x=t2.y; -drop table t1; - -create table t1 (a int) engine=heap; -insert into t1 values(1); -select max(a) from t1; -drop table t1; - -CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key(a), key(b) ) ENGINE=HEAP; -insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); -select * from t1 where a=1; -insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); -select * from t1 where a=1; -drop table t1; - -create table t1 (id int unsigned not null, primary key (id)) engine=HEAP; -insert into t1 values(1); -select max(id) from t1; -insert into t1 values(2); -select max(id) from t1; -replace into t1 values(1); -drop table t1; - -create table t1 (n int) engine=heap; -drop table t1; - -create table t1 (n int) engine=heap; -drop table if exists t1; - -# Test of non unique index - -CREATE table t1(f1 int not null,f2 char(20) not -null,index(f2)) engine=heap; -INSERT into t1 set f1=12,f2="bill"; -INSERT into t1 set f1=13,f2="bill"; -INSERT into t1 set f1=14,f2="bill"; -INSERT into t1 set f1=15,f2="bill"; -INSERT into t1 set f1=16,f2="ted"; -INSERT into t1 set f1=12,f2="ted"; -INSERT into t1 set f1=12,f2="ted"; -INSERT into t1 set f1=12,f2="ted"; -INSERT into t1 set f1=12,f2="ted"; -delete from t1 where f2="bill"; -select * from t1; -drop table t1; - -# -# Test when using part key searches -# - -create table t1 (btn char(10) not null, key(btn)) engine=heap; -insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i"); -explain select * from t1 where btn like "q%"; -select * from t1 where btn like "q%"; -alter table t1 add column new_col char(1) not null, add key (btn,new_col), drop key btn; -update t1 set new_col=left(btn,1); -explain select * from t1 where btn="a"; -explain select * from t1 where btn="a" and new_col="a"; -drop table t1; - -# -# Test of NULL keys -# - -CREATE TABLE t1 ( - a int default NULL, - b int default NULL, - KEY a (a), - UNIQUE b (b) -) engine=heap; -INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3); -SELECT * FROM t1 WHERE a=NULL; -explain SELECT * FROM t1 WHERE a IS NULL; -SELECT * FROM t1 WHERE a<=>NULL; -SELECT * FROM t1 WHERE b=NULL; -explain SELECT * FROM t1 WHERE b IS NULL; -SELECT * FROM t1 WHERE b<=>NULL; - ---error ER_DUP_ENTRY -INSERT INTO t1 VALUES (1,3); -DROP TABLE t1; - -CREATE TABLE t1 ( - a int default NULL, - key a (a) -) ENGINE=HEAP; -INSERT INTO t1 VALUES (10), (10), (10); -EXPLAIN SELECT * FROM t1 WHERE a=10; -SELECT * FROM t1 WHERE a=10; -DROP TABLE t1; - -# -# Test when deleting all rows -# - -CREATE TABLE t1 (a int not null, primary key(a)) engine=heap; -INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); -DELETE from t1 where a < 100; -SELECT * from t1; -DROP TABLE t1; - -# -# Bug#4411 Server hangs when trying to SELECT MAX(id) from an empty HEAP table -# -CREATE TABLE `job_titles` ( - `job_title_id` int(6) unsigned NOT NULL default '0', - `job_title` char(18) NOT NULL default '', - PRIMARY KEY (`job_title_id`), - UNIQUE KEY `job_title_id` (`job_title_id`,`job_title`) -) ENGINE=HEAP; - -SELECT MAX(job_title_id) FROM job_titles; - -DROP TABLE job_titles; - -# -# Test of delete with NOT NULL -# (Bug #6082) -# - -CREATE TABLE t1 (a INT NOT NULL, B INT, KEY(B)) ENGINE=HEAP; -INSERT INTO t1 VALUES(1,1), (1,NULL); -SELECT * FROM t1 WHERE B is not null; -DROP TABLE t1; - -# -# Bug #6748 -# heap_rfirst() doesn't work (and never did!) -# -CREATE TABLE t1 (pseudo char(35) PRIMARY KEY, date int(10) unsigned NOT NULL) ENGINE=HEAP; -INSERT INTO t1 VALUES ('massecot',1101106491),('altec',1101106492),('stitch+',1101106304),('Seb Corgan',1101106305),('beerfilou',1101106263),('flaker',1101106529),('joce8',5),('M4vrick',1101106418),('gabay008',1101106525),('Vamp irX',1101106291),('ZoomZip',1101106546),('rip666',1101106502),('CBP ',1101106397),('guezpard',1101106496); -DELETE FROM t1 WHERE date<1101106546; -SELECT * FROM t1; -DROP TABLE t1; - -# -# Bug #6878: a problem with small length records -# - -create table t1(a char(2)) engine=memory; -insert into t1 values (NULL), (NULL); -delete from t1 where a is null; -insert into t1 values ('2'), ('3'); -select * from t1; -drop table t1; - -# -# Test varchar -# We can't use varchar.inc becasue heap doesn't support blob's -# - -let $default=`select @@storage_engine`; -set storage_engine=HEAP; - -# -# Simple basic test that endspace is saved -# - -create table t1 (v varchar(10), c char(10), t varchar(50)); -insert into t1 values('+ ', '+ ', '+ '); -set @a=repeat(' ',20); -insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); -select concat('*',v,'*',c,'*',t,'*') from t1; - -# Check how columns are copied -show create table t1; -create table t2 like t1; -show create table t2; -create table t3 select * from t1; -show create table t3; -alter table t1 modify c varchar(10); -show create table t1; -alter table t1 modify v char(10); -show create table t1; -alter table t1 modify t varchar(10); -show create table t1; -select concat('*',v,'*',c,'*',t,'*') from t1; -drop table t1,t2,t3; - -# -# Testing of keys -# -create table t1 (v varchar(10), c char(10), t varchar(50), key(v), key(c), key(t(10))); -show create table t1; -disable_query_log; -let $1=10; -while ($1) -{ - let $2=27; - eval set @space=repeat(' ',10-$1); - while ($2) - { - eval set @char=char(ascii('a')+$2-1); - insert into t1 values(concat(@char,@space),concat(@char,@space),concat(@char,@space)); - dec $2; - } - dec $1; -} -enable_query_log; -select count(*) from t1; -insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); -select count(*) from t1 where v='a'; -select count(*) from t1 where c='a'; -select count(*) from t1 where t='a'; -select count(*) from t1 where v='a '; -select count(*) from t1 where c='a '; -select count(*) from t1 where t='a '; -select count(*) from t1 where v between 'a' and 'a '; -select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; -select count(*) from t1 where v like 'a%'; -select count(*) from t1 where c like 'a%'; -select count(*) from t1 where t like 'a%'; -select count(*) from t1 where v like 'a %'; -explain select count(*) from t1 where v='a '; -explain select count(*) from t1 where c='a '; -explain select count(*) from t1 where t='a '; -explain select count(*) from t1 where v like 'a%'; -explain select count(*) from t1 where v between 'a' and 'a '; -explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; - ---error ER_DUP_ENTRY -alter table t1 add unique(v); -select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*')); -explain select * from t1 where v='a'; - -# GROUP BY - -select v,count(*) from t1 group by v limit 10; -select v,count(t) from t1 group by v limit 10; -select v,count(c) from t1 group by v limit 10; -select sql_big_result trim(v),count(t) from t1 group by v limit 10; -select sql_big_result trim(v),count(c) from t1 group by v limit 10; -select c,count(*) from t1 group by c limit 10; -select c,count(t) from t1 group by c limit 10; -select sql_big_result c,count(t) from t1 group by c limit 10; -select t,count(*) from t1 group by t limit 10; -select t,count(t) from t1 group by t limit 10; -select sql_big_result trim(t),count(t) from t1 group by t limit 10; -drop table t1; - -# -# Test unique keys -# - -create table t1 (a char(10), unique (a)); -insert into t1 values ('a'); ---error ER_DUP_ENTRY -insert into t1 values ('a '); - -alter table t1 modify a varchar(10); ---error ER_DUP_ENTRY -insert into t1 values ('a '),('a '),('a '),('a '); ---error ER_DUP_ENTRY -insert into t1 values ('a '); ---error ER_DUP_ENTRY -insert into t1 values ('a '); ---error ER_DUP_ENTRY -insert into t1 values ('a '); -update t1 set a='a ' where a like 'a '; -update t1 set a='a ' where a like 'a '; -drop table t1; - -# -# Testing of btree keys -# - -create table t1 (v varchar(10), c char(10), t varchar(50), key using btree (v), key using btree (c), key using btree (t(10))); -show create table t1; -disable_query_log; -let $1=10; -while ($1) -{ - let $2=27; - eval set @space=repeat(' ',10-$1); - while ($2) - { - eval set @char=char(ascii('a')+$2-1); - insert into t1 values(concat(@char,@space),concat(@char,@space),concat(@char,@space)); - dec $2; - } - dec $1; -} -enable_query_log; -select count(*) from t1; -insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); -select count(*) from t1 where v='a'; -select count(*) from t1 where c='a'; -select count(*) from t1 where t='a'; -select count(*) from t1 where v='a '; -select count(*) from t1 where c='a '; -select count(*) from t1 where t='a '; -select count(*) from t1 where v between 'a' and 'a '; ---replace_column 9 # -select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; ---replace_column 9 # -explain select count(*) from t1 where v='a '; ---replace_column 9 # -explain select count(*) from t1 where c='a '; ---replace_column 9 # -explain select count(*) from t1 where t='a '; ---replace_column 9 # -explain select count(*) from t1 where v like 'a%'; ---replace_column 9 # -explain select count(*) from t1 where v between 'a' and 'a '; ---replace_column 9 # -explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; - ---error ER_DUP_ENTRY -alter table t1 add unique(v); -select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*')); -# Number of rows is not constant for b-trees keys ---replace_column 9 # -explain select * from t1 where v='a'; - -drop table t1; - -# -# Test unique btree keys -# - -create table t1 (a char(10), unique using btree (a)) engine=heap; -insert into t1 values ('a'); ---error ER_DUP_ENTRY -insert into t1 values ('a '); - -alter table t1 modify a varchar(10); ---error ER_DUP_ENTRY -insert into t1 values ('a '),('a '),('a '),('a '); ---error ER_DUP_ENTRY -insert into t1 values ('a '); ---error ER_DUP_ENTRY -insert into t1 values ('a '); ---error ER_DUP_ENTRY -insert into t1 values ('a '); -update t1 set a='a ' where a like 'a '; -update t1 set a='a ' where a like 'a '; -drop table t1; - -# -# test show create table -# - -create table t1 (v varchar(10), c char(10), t varchar(50), key(v(5)), key(c(5)), key(t(5))); -show create table t1; -drop table t1; - -create table t1 (v varchar(65530), key(v(10))); -show create table t1; -insert into t1 values(repeat('a',65530)); -select length(v) from t1 where v=repeat('a',65530); -drop table t1; - -# -# Reset varchar test -# -eval set storage_engine=$default; - -# -# Bug #8489: Strange auto_increment behaviour -# - -create table t1 (a bigint unsigned auto_increment primary key, b int, - key (b, a)) engine=heap; -insert t1 (b) values (1),(1),(1),(1),(1),(1),(1),(1); -select * from t1; -drop table t1; - -create table t1 (a int not null, b int not null auto_increment, - primary key(a, b), key(b)) engine=heap; -insert t1 (a) values (1),(1),(1),(1),(1),(1),(1),(1); -select * from t1; -drop table t1; - ---error 1075 -create table t1 (a int not null, b int not null auto_increment, - primary key(a, b)) engine=heap; - -# -# Bug #10566: Verify that we can create a prefixed key with length > 255 -# -create table t1 (c char(255), primary key(c(90))); -insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"); ---error ER_DUP_ENTRY -insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"); -drop table t1; - -# -# Bug 12796: Record doesn't show when selecting through index -# -CREATE TABLE t1 (a int, key(a)) engine=heap; -insert into t1 values (0); -delete from t1; -select * from t1; -insert into t1 values (0), (1); -select * from t1 where a = 0; -drop table t1; - -# End of 4.1 tests - -# -# Bug #3094: Row format of memory tables should always be reported as Fixed -# -create table t1 (c char(10)) engine=memory; -create table t2 (c varchar(10)) engine=memory; ---replace_column 8 # -show table status like 't_'; -drop table t1, t2; - -# -# BUG#18233 - Memory tables INDEX USING HASH (a,b) returns 1 row on -# SELECT WHERE a= AND b= -# -CREATE TABLE t1(a VARCHAR(1), b VARCHAR(2), c VARCHAR(256), - KEY(a), KEY(b), KEY(c)) ENGINE=MEMORY; -INSERT INTO t1 VALUES('a','aa',REPEAT('a', 256)),('a','aa',REPEAT('a',256)); -SELECT COUNT(*) FROM t1 WHERE a='a'; -SELECT COUNT(*) FROM t1 WHERE b='aa'; -SELECT COUNT(*) FROM t1 WHERE c=REPEAT('a',256); -DROP TABLE t1; - ---disable_query_log -drop database pbxt; ---enable_query_log -# End of 5.0 tests diff --git a/mysql-test/suite/pbxt/t/view_grant.test b/mysql-test/suite/pbxt/t/view_grant.test deleted file mode 100644 index 6a105da61f8..00000000000 --- a/mysql-test/suite/pbxt/t/view_grant.test +++ /dev/null @@ -1,1224 +0,0 @@ -# Can't test with embedded server --- source include/not_embedded.inc - ---disable_warnings -drop database if exists mysqltest; -drop view if exists v1,v2,v3; ---enable_warnings - - -# simple test of grants -grant create view on test.* to test@localhost; -show grants for test@localhost; -revoke create view on test.* from test@localhost; -show grants for test@localhost; -# The grant above creates a new user test@localhost, delete it -drop user test@localhost; - -# grant create view test -# -connect (root,localhost,root,,test); -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int); -create table mysqltest.t2 (a int, b int); - -grant select on mysqltest.t1 to mysqltest_1@localhost; -grant create view,select on test.* to mysqltest_1@localhost; - -connect (user1,localhost,mysqltest_1,,test); -connection user1; - --- error ER_SPECIFIC_ACCESS_DENIED_ERROR -create definer=root@localhost view v1 as select * from mysqltest.t1; -create view v1 as select * from mysqltest.t1; -# try to modify view without DROP privilege on it --- error 1142 -alter view v1 as select * from mysqltest.t1; --- error 1142 -create or replace view v1 as select * from mysqltest.t1; -# no CRETE VIEW privilege --- error 1142 -create view mysqltest.v2 as select * from mysqltest.t1; -# no SELECT privilege --- error 1142 -create view v2 as select * from mysqltest.t2; - -connection root; -# check view definer information -show create view v1; - -grant create view,drop,select on test.* to mysqltest_1@localhost; - -connection user1; -# following 'use' command is workaround of bug #9582 and should be removed -# when that bug will be fixed -use test; -alter view v1 as select * from mysqltest.t1; -create or replace view v1 as select * from mysqltest.t1; - -connection root; -revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; -revoke all privileges on test.* from mysqltest_1@localhost; - -drop database mysqltest; -drop view test.v1; - -# -# grants per columns -# -# MERGE algorithm ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int); -create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; -grant select (c) on mysqltest.v1 to mysqltest_1@localhost; - -connection user1; -select c from mysqltest.v1; -# there are no privileges on column 'd' --- error 1143 -select d from mysqltest.v1; - -connection root; -revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -drop database mysqltest; - -# TEMPORARY TABLE algorithm ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int); -create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; -grant select (c) on mysqltest.v1 to mysqltest_1@localhost; - -connection user1; -select c from mysqltest.v1; -# there are no privileges on column 'd' --- error 1143 -select d from mysqltest.v1; - -connection root; -revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -drop database mysqltest; - -# -# EXPLAIN rights -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings -#prepare views and tables -create table mysqltest.t1 (a int, b int); -create table mysqltest.t2 (a int, b int); -create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; -create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1; -create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2; -create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2; -grant select on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.v2 to mysqltest_1@localhost; -grant select on mysqltest.v3 to mysqltest_1@localhost; -grant select on mysqltest.v4 to mysqltest_1@localhost; - -connection user1; -# all selects works -select c from mysqltest.v1; -select c from mysqltest.v2; -select c from mysqltest.v3; -select c from mysqltest.v4; -# test of show coluns -show columns from mysqltest.v1; -show columns from mysqltest.v2; -# but explain/show do not --- error 1345 -explain select c from mysqltest.v1; --- error 1142 -show create view mysqltest.v1; --- error 1345 -explain select c from mysqltest.v2; --- error 1142 -show create view mysqltest.v2; --- error 1345 -explain select c from mysqltest.v3; --- error 1142 -show create view mysqltest.v3; --- error 1345 -explain select c from mysqltest.v4; --- error 1142 -show create view mysqltest.v4; - -# allow to see one of underlying table -connection root; -grant select on mysqltest.t1 to mysqltest_1@localhost; -connection user1; -# EXPLAIN of view on above table works -explain select c from mysqltest.v1; --- error 1142 -show create view mysqltest.v1; -explain select c from mysqltest.v2; --- error 1142 -show create view mysqltest.v2; -# but other EXPLAINs do not --- error 1345 -explain select c from mysqltest.v3; --- error 1142 -show create view mysqltest.v3; --- error 1345 -explain select c from mysqltest.v4; --- error 1142 -show create view mysqltest.v4; - -# allow to see any view in mysqltest database -connection root; -grant show view on mysqltest.* to mysqltest_1@localhost; -connection user1; -explain select c from mysqltest.v1; -show create view mysqltest.v1; -explain select c from mysqltest.v2; -show create view mysqltest.v2; -explain select c from mysqltest.v3; -show create view mysqltest.v3; -explain select c from mysqltest.v4; -show create view mysqltest.v4; - -connection root; -revoke all privileges on mysqltest.* from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -drop database mysqltest; - -# -# UPDATE privileges on VIEW columns and whole VIEW -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int, primary key(a)); -insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10); -create table mysqltest.t2 (x int); -insert into mysqltest.t2 values (3), (4), (5), (6); -create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; -create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; -create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1; - -grant update (a) on mysqltest.v2 to mysqltest_1@localhost; -grant update on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.* to mysqltest_1@localhost; - -connection user1; -use mysqltest; -# update with rights on VIEW column -update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c; -select * from t1 order by a; # PBXT : required for order -update v1 set a=a+c; -select * from t1 order by a; # PBXT : required for order -# update with rights on whole VIEW -update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c; -select * from t1 order by a; # PBXT : required for order -update v2 set a=a+c; -select * from t1 order by a; # PBXT : required for order -# no rights on column --- error 1143 -update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; --- error 1143 -update v2 set c=a+c; -# no rights for view --- error 1142 -update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c; --- error 1142 -update v3 set a=a+c; - -use test; -connection root; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; - -# -# DELETE privileges on VIEW -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int, primary key(a)); -insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10); -create table mysqltest.t2 (x int); -insert into mysqltest.t2 values (3), (4), (5), (6); -create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; -create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1; - -grant delete on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.* to mysqltest_1@localhost; - -connection user1; -use mysqltest; -# update with rights on VIEW column -delete from v1 where c < 4; -select * from t1; -delete v1 from t2,v1 where t2.x=v1.c; -select * from t1; -# no rights for view --- error 1142 -delete v2 from t2,v2 where t2.x=v2.c; --- error 1142 -delete from v2 where c < 4; - -use test; -connection root; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; - -# -# insert privileges on VIEW -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int, primary key(a)); -insert into mysqltest.t1 values (1,2), (2,3); -create table mysqltest.t2 (x int, y int); -insert into mysqltest.t2 values (3,4); -create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1; -create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; - -grant insert on mysqltest.v1 to mysqltest_1@localhost; -grant select on mysqltest.* to mysqltest_1@localhost; - -connection user1; -use mysqltest; -# update with rights on VIEW column -insert into v1 values (5,6); -select * from t1; -insert into v1 select x,y from t2; -select * from t1; -# no rights for view --- error 1142 -insert into v2 values (5,6); --- error 1142 -insert into v2 select x,y from t2; - -use test; -connection root; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; - -# -# test of CREATE VIEW privileges if we have limited privileges -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int); -create table mysqltest.t2 (a int, b int); - -grant update on mysqltest.t1 to mysqltest_1@localhost; -grant update(b) on mysqltest.t2 to mysqltest_1@localhost; -grant create view,update on test.* to mysqltest_1@localhost; - -connection user1; - -create view v1 as select * from mysqltest.t1; -create view v2 as select b from mysqltest.t2; -# There are not rights on mysqltest.v1 --- error 1142 -create view mysqltest.v1 as select * from mysqltest.t1; -# There are not any rights on mysqltest.t2.a --- error 1143 -create view v3 as select a from mysqltest.t2; - -# give CREATE VIEW privileges (without any privileges for result column) -connection root; -create table mysqltest.v3 (b int); -grant create view on mysqltest.v3 to mysqltest_1@localhost; -drop table mysqltest.v3; -connection user1; -create view mysqltest.v3 as select b from mysqltest.t2; - -# give UPDATE privileges -connection root; -grant create view, update on mysqltest.v3 to mysqltest_1@localhost; -drop view mysqltest.v3; -connection user1; -create view mysqltest.v3 as select b from mysqltest.t2; - - -# Expression need select privileges --- error 1143 -create view v4 as select b+1 from mysqltest.t2; - -connection root; -grant create view,update,select on test.* to mysqltest_1@localhost; -connection user1; --- error 1143 -create view v4 as select b+1 from mysqltest.t2; - -connection root; -grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost; -connection user1; -create view v4 as select b+1 from mysqltest.t2; - -connection root; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; -drop view v1,v2,v4; - -# -# user with global DB privileges -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings -create table mysqltest.t1 (a int); -grant all privileges on mysqltest.* to mysqltest_1@localhost; - -connection user1; -use mysqltest; -create view v1 as select * from t1; -use test; - -connection root; -revoke all privileges on mysqltest.* from mysqltest_1@localhost; -drop database mysqltest; - -# -# view definer grants revoking -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -create table mysqltest.t1 (a int, b int); - -grant select on mysqltest.t1 to mysqltest_1@localhost; -grant create view,select on test.* to mysqltest_1@localhost; - -connection user1; - -create view v1 as select * from mysqltest.t1; - -connection root; -# check view definer information -show create view v1; -revoke select on mysqltest.t1 from mysqltest_1@localhost; --- error ER_VIEW_INVALID -select * from v1; -grant select on mysqltest.t1 to mysqltest_1@localhost; -select * from v1; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop view v1; -drop database mysqltest; - -# -# rights on execution of view underlying functiond (BUG#9505) -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -use mysqltest; -create table t1 (a int); -insert into t1 values (1); -create table t2 (s1 int); ---disable_warnings -drop function if exists f2; ---enable_warnings -delimiter //; -create function f2 () returns int begin declare v int; select s1 from t2 -into v; return v; end// -delimiter ;// -create algorithm=TEMPTABLE view v1 as select f2() from t1; -create algorithm=MERGE view v2 as select f2() from t1; -create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; -create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; -create SQL SECURITY INVOKER view v5 as select * from v4; -grant select on v1 to mysqltest_1@localhost; -grant select on v2 to mysqltest_1@localhost; -grant select on v3 to mysqltest_1@localhost; -grant select on v4 to mysqltest_1@localhost; -grant select on v5 to mysqltest_1@localhost; - -connection user1; -use mysqltest; -select * from v1; -select * from v2; --- error ER_VIEW_INVALID -select * from v3; --- error ER_VIEW_INVALID -select * from v4; --- error ER_VIEW_INVALID -select * from v5; -use test; - -connection root; -drop view v1, v2, v3, v4, v5; -drop function f2; -drop table t1, t2; -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; - -# -# revertion of previous test, definer of view lost his/her rights to execute -# function -# - -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -use mysqltest; -create table t1 (a int); -insert into t1 values (1); -create table t2 (s1 int); ---disable_warnings -drop function if exists f2; ---enable_warnings -delimiter //; -create function f2 () returns int begin declare v int; select s1 from t2 -into v; return v; end// -delimiter ;// -grant select on t1 to mysqltest_1@localhost; -grant execute on function f2 to mysqltest_1@localhost; -grant create view on mysqltest.* to mysqltest_1@localhost; - -connection user1; -use mysqltest; -create algorithm=TEMPTABLE view v1 as select f2() from t1; -create algorithm=MERGE view v2 as select f2() from t1; -create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; -create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; -use test; - -connection root; -create view v5 as select * from v1; -revoke execute on function f2 from mysqltest_1@localhost; --- error ER_VIEW_INVALID -select * from v1; --- error ER_VIEW_INVALID -select * from v2; -select * from v3; -select * from v4; --- error ER_VIEW_INVALID -select * from v5; - -drop view v1, v2, v3, v4, v5; -drop function f2; -drop table t1, t2; -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; - -# -# definer/invoker rights for columns -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -use mysqltest; -create table t1 (a int); -create table v1 (a int); -insert into t1 values (1); -grant select on t1 to mysqltest_1@localhost; -grant select on v1 to mysqltest_1@localhost; -grant create view on mysqltest.* to mysqltest_1@localhost; -drop table v1; - -connection user1; -use mysqltest; -create algorithm=TEMPTABLE view v1 as select *, a as b from t1; -create algorithm=MERGE view v2 as select *, a as b from t1; -create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; -create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; -create view v5 as select * from v1; -use test; - -connection root; -revoke select on t1 from mysqltest_1@localhost; --- error ER_VIEW_INVALID -select * from v1; --- error ER_VIEW_INVALID -select * from v2; -select * from v3; -select * from v4; --- error ER_VIEW_INVALID -select * from v5; - -#drop view v1, v2, v3, v4, v5; -drop table t1; -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; - - -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -use mysqltest; -create table t1 (a int); -insert into t1 values (1); -create algorithm=TEMPTABLE view v1 as select *, a as b from t1; -create algorithm=MERGE view v2 as select *, a as b from t1; -create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; -create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; -create SQL SECURITY INVOKER view v5 as select * from v4; -grant select on v1 to mysqltest_1@localhost; -grant select on v2 to mysqltest_1@localhost; -grant select on v3 to mysqltest_1@localhost; -grant select on v4 to mysqltest_1@localhost; -grant select on v5 to mysqltest_1@localhost; - -connection user1; -use mysqltest; -select * from v1; -select * from v2; --- error ER_VIEW_INVALID -select * from v3; --- error ER_VIEW_INVALID -select * from v4; --- error ER_VIEW_INVALID -select * from v5; -use test; - -connection root; -drop view v1, v2, v3, v4, v5; -drop table t1; -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; -drop database mysqltest; - -# -# BUG#14256: definer in view definition is not fully qualified -# ---disable_warnings -drop view if exists v1; ---enable_warnings - -# Backup anonymous users and remove them. (They get in the way of -# the one we test with here otherwise.) -create table t1 as select * from mysql.user where user=''; -delete from mysql.user where user=''; -flush privileges; - -# Create the test user -grant all on test.* to 'test14256'@'%'; - -connect (test14256,localhost,test14256,,test); -connection test14256; -use test; - -create view v1 as select 42; -show create view v1; - -select definer into @v1def1 from information_schema.views - where table_schema = 'test' and table_name='v1'; -drop view v1; - -create definer=`test14256`@`%` view v1 as select 42; -show create view v1; - -select definer into @v1def2 from information_schema.views - where table_schema = 'test' and table_name='v1'; -drop view v1; - -select @v1def1, @v1def2, @v1def1=@v1def2; - -connection root; -drop user test14256; - -# Restore the anonymous users. -insert into mysql.user select * from t1; -flush privileges; - -drop table t1; - -# -# BUG#14726: freeing stack variable in case of an error of opening -# a view when we have locked tables with LOCK TABLES statement. -# -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings - -use mysqltest; -CREATE TABLE t1 (i INT); -CREATE VIEW v1 AS SELECT * FROM t1; -SHOW CREATE VIEW v1; -GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost; - -connection user1; - -use mysqltest; -LOCK TABLES v1 READ; --- error ER_TABLEACCESS_DENIED_ERROR -SHOW CREATE TABLE v1; -UNLOCK TABLES; -use test; - -connection root; -use test; -drop user mysqltest_1@localhost; -drop database mysqltest; - -# -# switch to default connaction -# -disconnect user1; -disconnect root; -connection default; - -# -# DEFINER information check -# -create definer=some_user@`` sql security invoker view v1 as select 1; -create definer=some_user@localhost sql security invoker view v2 as select 1; -show create view v1; -show create view v2; -drop view v1; -drop view v2; - -# -# Bug#18681: View privileges are broken -# -CREATE DATABASE mysqltest1; -CREATE USER readonly@localhost; -CREATE TABLE mysqltest1.t1 (x INT); -INSERT INTO mysqltest1.t1 VALUES (1), (2); -CREATE SQL SECURITY INVOKER VIEW mysqltest1.v_t1 AS SELECT * FROM mysqltest1.t1; -CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ts AS SELECT * FROM mysqltest1.t1; -CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ti AS SELECT * FROM mysqltest1.t1; -CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tu AS SELECT * FROM mysqltest1.t1; -CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tus AS SELECT * FROM mysqltest1.t1; -CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_td AS SELECT * FROM mysqltest1.t1; -CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tds AS SELECT * FROM mysqltest1.t1; -GRANT SELECT, INSERT, UPDATE, DELETE ON mysqltest1.v_t1 TO readonly@localhost; -GRANT SELECT ON mysqltest1.v_ts TO readonly@localhost; -GRANT INSERT ON mysqltest1.v_ti TO readonly@localhost; -GRANT UPDATE ON mysqltest1.v_tu TO readonly@localhost; -GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly@localhost; -GRANT DELETE ON mysqltest1.v_td TO readonly@localhost; -GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly@localhost; - -CONNECT (n1,localhost,readonly,,); -CONNECTION n1; - ---error 1356 -SELECT * FROM mysqltest1.v_t1; ---error 1356 -INSERT INTO mysqltest1.v_t1 VALUES(4); ---error 1356 -DELETE FROM mysqltest1.v_t1 WHERE x = 1; ---error 1356 -UPDATE mysqltest1.v_t1 SET x = 3 WHERE x = 2; ---error 1356 -UPDATE mysqltest1.v_t1 SET x = 3; ---error 1356 -DELETE FROM mysqltest1.v_t1; ---error 1356 -SELECT 1 FROM mysqltest1.v_t1; ---error 1142 -SELECT * FROM mysqltest1.t1; - -SELECT * FROM mysqltest1.v_ts; ---error 1142 -SELECT * FROM mysqltest1.v_ts, mysqltest1.t1 WHERE mysqltest1.t1.x = mysqltest1.v_ts.x; ---error 1142 -SELECT * FROM mysqltest1.v_ti; - ---error 1142 -INSERT INTO mysqltest1.v_ts VALUES (100); -INSERT INTO mysqltest1.v_ti VALUES (100); - ---error 1142 -UPDATE mysqltest1.v_ts SET x= 200 WHERE x = 100; ---error 1142 -UPDATE mysqltest1.v_ts SET x= 200; -UPDATE mysqltest1.v_tu SET x= 200 WHERE x = 100; -UPDATE mysqltest1.v_tus SET x= 200 WHERE x = 100; -UPDATE mysqltest1.v_tu SET x= 200; - ---error 1142 -DELETE FROM mysqltest1.v_ts WHERE x= 200; ---error 1142 -DELETE FROM mysqltest1.v_ts; ---error 1143 -DELETE FROM mysqltest1.v_td WHERE x= 200; -DELETE FROM mysqltest1.v_tds WHERE x= 200; -DELETE FROM mysqltest1.v_td; - -CONNECTION default; -DROP VIEW mysqltest1.v_tds; -DROP VIEW mysqltest1.v_td; -DROP VIEW mysqltest1.v_tus; -DROP VIEW mysqltest1.v_tu; -DROP VIEW mysqltest1.v_ti; -DROP VIEW mysqltest1.v_ts; -DROP VIEW mysqltest1.v_t1; -DROP TABLE mysqltest1.t1; -DROP USER readonly@localhost; -DROP DATABASE mysqltest1; - -# -# BUG#14875: Bad view DEFINER makes SHOW CREATE VIEW fail -# -CREATE TABLE t1 (a INT PRIMARY KEY); -INSERT INTO t1 VALUES (1), (2), (3); -CREATE DEFINER = 'no-such-user'@localhost VIEW v AS SELECT a from t1; -#--warning 1448 -SHOW CREATE VIEW v; ---error 1449 -SELECT * FROM v; -DROP VIEW v; -DROP TABLE t1; -USE test; - -# -# Bug#20363: Create view on just created view is now denied -# -eval CREATE USER mysqltest_db1@localhost identified by 'PWD'; -eval GRANT ALL ON mysqltest_db1.* TO mysqltest_db1@localhost WITH GRANT OPTION; - -# The session with the non root user is needed. ---replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK -connect (session1,localhost,mysqltest_db1,PWD,test); - -CREATE SCHEMA mysqltest_db1 ; -USE mysqltest_db1 ; - -CREATE TABLE t1 (f1 INTEGER); - -CREATE VIEW view1 AS -SELECT * FROM t1; -SHOW CREATE VIEW view1; - -CREATE VIEW view2 AS -SELECT * FROM view1; ---echo # Here comes a suspicious warning -SHOW CREATE VIEW view2; ---echo # But the view view2 is usable -SELECT * FROM view2; - -CREATE VIEW view3 AS -SELECT * FROM view2; - -SELECT * from view3; - -connection default; -DROP VIEW mysqltest_db1.view3; -DROP VIEW mysqltest_db1.view2; -DROP VIEW mysqltest_db1.view1; -DROP TABLE mysqltest_db1.t1; -DROP SCHEMA mysqltest_db1; -DROP USER mysqltest_db1@localhost; -# -# BUG#20482: failure on Create join view with sources views/tables -# in different schemas -# ---disable_warnings -CREATE DATABASE test1; -CREATE DATABASE test2; ---enable_warnings - -CREATE TABLE test1.t0 (a VARCHAR(20)) engine=myisam; # PBXT can't mix databases; -CREATE TABLE test2.t1 (a VARCHAR(20)); -CREATE VIEW test2.t3 AS SELECT * FROM test1.t0; -CREATE OR REPLACE VIEW test.v1 AS - SELECT ta.a AS col1, tb.a AS col2 FROM test2.t3 ta, test2.t1 tb; - -DROP VIEW test.v1; -DROP VIEW test2.t3; -DROP TABLE test2.t1, test1.t0; -DROP DATABASE test2; -DROP DATABASE test1; - - -# -# BUG#20570: CURRENT_USER() in a VIEW with SQL SECURITY DEFINER -# returns invoker name -# ---disable_warnings -DROP VIEW IF EXISTS v1; -DROP VIEW IF EXISTS v2; -DROP VIEW IF EXISTS v3; -DROP FUNCTION IF EXISTS f1; -DROP FUNCTION IF EXISTS f2; -DROP PROCEDURE IF EXISTS p1; ---enable_warnings - -CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT CURRENT_USER() AS cu; - -CREATE FUNCTION f1() RETURNS VARCHAR(77) SQL SECURITY INVOKER - RETURN CURRENT_USER(); -CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT f1() AS cu; - -CREATE PROCEDURE p1(OUT cu VARCHAR(77)) SQL SECURITY INVOKER - SET cu= CURRENT_USER(); -delimiter |; -CREATE FUNCTION f2() RETURNS VARCHAR(77) SQL SECURITY INVOKER -BEGIN - DECLARE cu VARCHAR(77); - CALL p1(cu); - RETURN cu; -END| -delimiter ;| -CREATE SQL SECURITY DEFINER VIEW v3 AS SELECT f2() AS cu; - -CREATE USER mysqltest_u1@localhost; -GRANT ALL ON test.* TO mysqltest_u1@localhost; - -connect (conn1, localhost, mysqltest_u1,,); - ---echo ---echo The following tests should all return 1. ---echo -SELECT CURRENT_USER() = 'mysqltest_u1@localhost'; -SELECT f1() = 'mysqltest_u1@localhost'; -CALL p1(@cu); -SELECT @cu = 'mysqltest_u1@localhost'; -SELECT f2() = 'mysqltest_u1@localhost'; -SELECT cu = 'root@localhost' FROM v1; -SELECT cu = 'root@localhost' FROM v2; -SELECT cu = 'root@localhost' FROM v3; - -disconnect conn1; -connection default; - -DROP VIEW v3; -DROP FUNCTION f2; -DROP PROCEDURE p1; -DROP FUNCTION f1; -DROP VIEW v2; -DROP VIEW v1; -DROP USER mysqltest_u1@localhost; - - -# -# Bug#17254: Error for DEFINER security on VIEW provides too much info -# -connect (root,localhost,root,,); -connection root; -CREATE DATABASE db17254; -USE db17254; -CREATE TABLE t1 (f1 INT); -INSERT INTO t1 VALUES (10),(20); -CREATE USER def_17254@localhost; -GRANT SELECT ON db17254.* TO def_17254@localhost; -CREATE USER inv_17254@localhost; -GRANT SELECT ON db17254.t1 TO inv_17254@localhost; -GRANT CREATE VIEW ON db17254.* TO def_17254@localhost; - -connect (def,localhost,def_17254,,db17254); -connection def; -CREATE VIEW v1 AS SELECT * FROM t1; - -connection root; -DROP USER def_17254@localhost; - -connect (inv,localhost,inv_17254,,db17254); -connection inv; ---echo for a user ---error 1142 -SELECT * FROM v1; - -connection root; ---echo for a superuser ---error 1449 -SELECT * FROM v1; -DROP USER inv_17254@localhost; -DROP DATABASE db17254; -disconnect def; -disconnect inv; - - -# -# BUG#24404: strange bug with view+permission+prepared statement -# ---disable_warnings -DROP DATABASE IF EXISTS mysqltest_db1; -DROP DATABASE IF EXISTS mysqltest_db2; ---enable_warnings ---error 0,ER_CANNOT_USER -DROP USER mysqltest_u1; ---error 0,ER_CANNOT_USER -DROP USER mysqltest_u2; - -CREATE USER mysqltest_u1@localhost; -CREATE USER mysqltest_u2@localhost; - -CREATE DATABASE mysqltest_db1; -CREATE DATABASE mysqltest_db2; - -GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost WITH GRANT OPTION; -GRANT ALL ON mysqltest_db2.* TO mysqltest_u2@localhost; - -connect (conn1, localhost, mysqltest_u1, , mysqltest_db1); - -CREATE TABLE t1 (i INT); -INSERT INTO t1 VALUES (1); - -# Use view with subquery for better coverage. -CREATE VIEW v1 AS SELECT i FROM t1 WHERE 1 IN (SELECT * FROM t1); - -CREATE TABLE t2 (s CHAR(7)); -INSERT INTO t2 VALUES ('public'); - -GRANT SELECT ON v1 TO mysqltest_u2@localhost; -GRANT SELECT ON t2 TO mysqltest_u2@localhost; - -connect (conn2, localhost, mysqltest_u2, , mysqltest_db2); - -SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2; -PREPARE stmt1 FROM "SELECT * FROM mysqltest_db1.t2"; -EXECUTE stmt1; -PREPARE stmt2 FROM "SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2"; -EXECUTE stmt2; - -connection conn1; -# Make table 't2' private. -REVOKE SELECT ON t2 FROM mysqltest_u2@localhost; -UPDATE t2 SET s = 'private' WHERE s = 'public'; - -connection conn2; ---error ER_TABLEACCESS_DENIED_ERROR -SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2; ---error ER_TABLEACCESS_DENIED_ERROR -EXECUTE stmt1; -# Original bug was here: the statement didn't fail. ---error ER_TABLEACCESS_DENIED_ERROR -EXECUTE stmt2; - -# Cleanup. -disconnect conn2; -disconnect conn1; -connection default; -REVOKE ALL ON mysqltest_db1.* FROM mysqltest_u1@localhost; -REVOKE ALL ON mysqltest_db2.* FROM mysqltest_u2@localhost; -DROP DATABASE mysqltest_db1; -DROP DATABASE mysqltest_db2; -DROP USER mysqltest_u1@localhost; -DROP USER mysqltest_u2@localhost; - -# -# Bug#26813: The SUPER privilege is wrongly required to alter a view created -# by another user. -# -connection root; -CREATE DATABASE db26813; -USE db26813; -CREATE TABLE t1(f1 INT, f2 INT); -CREATE VIEW v1 AS SELECT f1 FROM t1; -CREATE VIEW v2 AS SELECT f1 FROM t1; -CREATE VIEW v3 AS SELECT f1 FROM t1; -CREATE USER u26813@localhost; -GRANT DROP ON db26813.v1 TO u26813@localhost; -GRANT CREATE VIEW ON db26813.v2 TO u26813@localhost; -GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost; -GRANT SELECT ON db26813.t1 TO u26813@localhost; - -connect (u1,localhost,u26813,,db26813); -connection u1; ---error ER_SPECIFIC_ACCESS_DENIED_ERROR -ALTER VIEW v1 AS SELECT f2 FROM t1; ---error ER_SPECIFIC_ACCESS_DENIED_ERROR -ALTER VIEW v2 AS SELECT f2 FROM t1; ---error ER_SPECIFIC_ACCESS_DENIED_ERROR -ALTER VIEW v3 AS SELECT f2 FROM t1; - -connection root; -SHOW CREATE VIEW v3; - -DROP USER u26813@localhost; -DROP DATABASE db26813; -disconnect u1; - ---echo # ---echo # Bug#29908: A user can gain additional access through the ALTER VIEW. ---echo # -connection root; -CREATE DATABASE mysqltest_29908; -USE mysqltest_29908; -CREATE TABLE t1(f1 INT, f2 INT); -CREATE USER u29908_1@localhost; -CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1; -CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS - SELECT f1 FROM t1; -GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost; -GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost; -GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost; -CREATE USER u29908_2@localhost; -GRANT DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost; -GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost; -GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost; - -connect (u2,localhost,u29908_2,,mysqltest_29908); ---error ER_SPECIFIC_ACCESS_DENIED_ERROR -ALTER VIEW v1 AS SELECT f2 FROM t1; ---error ER_SPECIFIC_ACCESS_DENIED_ERROR -ALTER VIEW v2 AS SELECT f2 FROM t1; -SHOW CREATE VIEW v2; - -connect (u1,localhost,u29908_1,,mysqltest_29908); -ALTER VIEW v1 AS SELECT f2 FROM t1; -SHOW CREATE VIEW v1; -ALTER VIEW v2 AS SELECT f2 FROM t1; -SHOW CREATE VIEW v2; - -connection root; -ALTER VIEW v1 AS SELECT f1 FROM t1; -SHOW CREATE VIEW v1; -ALTER VIEW v2 AS SELECT f1 FROM t1; -SHOW CREATE VIEW v2; - -DROP USER u29908_1@localhost; -DROP USER u29908_2@localhost; -DROP DATABASE mysqltest_29908; -disconnect u1; -disconnect u2; ---echo ####################################################################### - -# -# BUG#24040: Create View don't succed with "all privileges" on a database. -# - -# Prepare. - ---disable_warnings -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; ---enable_warnings - -CREATE DATABASE mysqltest1; -CREATE DATABASE mysqltest2; - -# Test. - -CREATE TABLE mysqltest1.t1(c1 INT); -CREATE TABLE mysqltest1.t2(c2 INT); -CREATE TABLE mysqltest1.t3(c3 INT); -CREATE TABLE mysqltest1.t4(c4 INT); - -INSERT INTO mysqltest1.t1 VALUES (11), (12), (13), (14); -INSERT INTO mysqltest1.t2 VALUES (21), (22), (23), (24); -INSERT INTO mysqltest1.t3 VALUES (31), (32), (33), (34); -INSERT INTO mysqltest1.t4 VALUES (41), (42), (43), (44); - -GRANT SELECT ON mysqltest1.t1 TO mysqltest_u1@localhost; -GRANT INSERT ON mysqltest1.t2 TO mysqltest_u1@localhost; -GRANT SELECT, UPDATE ON mysqltest1.t3 TO mysqltest_u1@localhost; -GRANT SELECT, DELETE ON mysqltest1.t4 TO mysqltest_u1@localhost; - -GRANT ALL PRIVILEGES ON mysqltest2.* TO mysqltest_u1@localhost; - ---connect (bug24040_con,localhost,mysqltest_u1,,mysqltest2) ---echo ---echo ---> connection: bug24040_con - -SELECT * FROM mysqltest1.t1; -INSERT INTO mysqltest1.t2 VALUES(25); -UPDATE mysqltest1.t3 SET c3 = 331 WHERE c3 = 31; -DELETE FROM mysqltest1.t4 WHERE c4 = 44; - -CREATE VIEW v1 AS SELECT * FROM mysqltest1.t1; -CREATE VIEW v2 AS SELECT * FROM mysqltest1.t2; -CREATE VIEW v3 AS SELECT * FROM mysqltest1.t3; -CREATE VIEW v4 AS SELECT * FROM mysqltest1.t4; - -SELECT * FROM v1; -INSERT INTO v2 VALUES(26); -UPDATE v3 SET c3 = 332 WHERE c3 = 32; -DELETE FROM v4 WHERE c4 = 43; - ---error ER_COLUMNACCESS_DENIED_ERROR -CREATE VIEW v12 AS SELECT c1, c2 FROM mysqltest1.t1, mysqltest1.t2; -CREATE VIEW v13 AS SELECT c1, c3 FROM mysqltest1.t1, mysqltest1.t3; -CREATE VIEW v14 AS SELECT c1, c4 FROM mysqltest1.t1, mysqltest1.t4; - ---error ER_COLUMNACCESS_DENIED_ERROR -CREATE VIEW v21 AS SELECT c2, c1 FROM mysqltest1.t2, mysqltest1.t1; ---error ER_COLUMNACCESS_DENIED_ERROR -CREATE VIEW v23 AS SELECT c2, c3 FROM mysqltest1.t2, mysqltest1.t3; ---error ER_COLUMNACCESS_DENIED_ERROR -CREATE VIEW v24 AS SELECT c2, c4 FROM mysqltest1.t2, mysqltest1.t4; - -CREATE VIEW v31 AS SELECT c3, c1 FROM mysqltest1.t3, mysqltest1.t1; ---error ER_COLUMNACCESS_DENIED_ERROR -CREATE VIEW v32 AS SELECT c3, c2 FROM mysqltest1.t3, mysqltest1.t2; -CREATE VIEW v34 AS SELECT c3, c4 FROM mysqltest1.t3, mysqltest1.t4; - -CREATE VIEW v41 AS SELECT c4, c1 FROM mysqltest1.t4, mysqltest1.t1; ---error ER_COLUMNACCESS_DENIED_ERROR -CREATE VIEW v42 AS SELECT c4, c2 FROM mysqltest1.t4, mysqltest1.t2; -CREATE VIEW v43 AS SELECT c4, c3 FROM mysqltest1.t4, mysqltest1.t3; - ---connection default ---echo ---echo ---> connection: default - -SELECT * FROM mysqltest1.t1; -SELECT * FROM mysqltest1.t2; -SELECT * FROM mysqltest1.t3 order by c3; # PBXT: order required -SELECT * FROM mysqltest1.t4; - -# Cleanup. - --- disconnect bug24040_con - -DROP DATABASE mysqltest1; -DROP DATABASE mysqltest2; -DROP USER mysqltest_u1@localhost; - ---echo End of 5.0 tests. - - -# -# Test that ALTER VIEW accepts DEFINER and ALGORITHM, see bug#16425. -# -connection default; ---disable_warnings -DROP VIEW IF EXISTS v1; -DROP TABLE IF EXISTS t1; ---enable_warnings - -CREATE TABLE t1 (i INT); -CREATE VIEW v1 AS SELECT * FROM t1; - -ALTER VIEW v1 AS SELECT * FROM t1; -SHOW CREATE VIEW v1; -ALTER DEFINER=no_such@user_1 VIEW v1 AS SELECT * FROM t1; -SHOW CREATE VIEW v1; -ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; -SHOW CREATE VIEW v1; -ALTER ALGORITHM=TEMPTABLE DEFINER=no_such@user_2 VIEW v1 AS SELECT * FROM t1; -SHOW CREATE VIEW v1; - -DROP VIEW v1; -DROP TABLE t1; - - ---disable_query_log -drop database pbxt; ---enable_query_log ---echo End of 5.1 tests. |