diff options
Diffstat (limited to 'mysql-test')
45 files changed, 2627 insertions, 212 deletions
diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 72b65d4287c..e2168b7f273 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -930,7 +930,7 @@ sub executable_setup () { } $exe_mysqlcheck= mtr_exe_exists("$path_client_bindir/mysqlcheck"); $exe_mysqldump= mtr_exe_exists("$path_client_bindir/mysqldump"); - $exe_mysqlimport= mtr_exe_exists("$path_client_bindir/mysqlimport"); + $exe_mysqlimport= mtr_exe_exists("$path_client_bindir/mysqlimport"); $exe_mysqlshow= mtr_exe_exists("$path_client_bindir/mysqlshow"); $exe_mysqlbinlog= mtr_exe_exists("$path_client_bindir/mysqlbinlog"); $exe_mysqladmin= mtr_exe_exists("$path_client_bindir/mysqladmin"); @@ -947,6 +947,7 @@ sub executable_setup () { $path_client_bindir= mtr_path_exists("$glob_basedir/bin"); $exe_mysqlcheck= mtr_exe_exists("$path_client_bindir/mysqlcheck"); $exe_mysqldump= mtr_exe_exists("$path_client_bindir/mysqldump"); + $exe_mysqlimport= mtr_exe_exists("$path_client_bindir/mysqlimport"); $exe_mysqlshow= mtr_exe_exists("$path_client_bindir/mysqlshow"); $exe_mysqlbinlog= mtr_exe_exists("$path_client_bindir/mysqlbinlog"); $exe_mysqladmin= mtr_exe_exists("$path_client_bindir/mysqladmin"); @@ -2008,7 +2009,7 @@ sub mysqld_arguments ($$$$$) { mtr_add_arg($args, "%s--basedir=%s", $prefix, $path_my_basedir); mtr_add_arg($args, "%s--character-sets-dir=%s", $prefix, $path_charsetsdir); mtr_add_arg($args, "%s--core", $prefix); - mtr_add_arg($args, "%s--log-bin-trust-routine-creators", $prefix); + mtr_add_arg($args, "%s--log-bin-trust-function-creators", $prefix); mtr_add_arg($args, "%s--default-character-set=latin1", $prefix); mtr_add_arg($args, "%s--language=%s", $prefix, $path_language); mtr_add_arg($args, "%s--tmpdir=$opt_tmpdir", $prefix); @@ -2131,7 +2132,7 @@ sub mysqld_arguments ($$$$$) { mtr_add_arg($args, "%s--key_buffer_size=1M", $prefix); mtr_add_arg($args, "%s--sort_buffer=256K", $prefix); mtr_add_arg($args, "%s--max_heap_table_size=1M", $prefix); - mtr_add_arg($args, "%s--log-bin-trust-routine-creators", $prefix); + mtr_add_arg($args, "%s--log-bin-trust-function-creators", $prefix); if ( $opt_ssl_supported ) { diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh index 037040bf0de..2cb462831fa 100644 --- a/mysql-test/mysql-test-run.sh +++ b/mysql-test/mysql-test-run.sh @@ -1275,7 +1275,7 @@ start_master() --language=$LANGUAGE \ --innodb_data_file_path=ibdata1:128M:autoextend \ --open-files-limit=1024 \ - --log-bin-trust-routine-creators \ + --log-bin-trust-function-creators \ $MASTER_40_ARGS \ $SMALL_SERVER \ $EXTRA_MASTER_OPT $EXTRA_MASTER_MYSQLD_OPT \ @@ -1296,7 +1296,7 @@ start_master() --tmpdir=$MYSQL_TMP_DIR \ --language=$LANGUAGE \ --innodb_data_file_path=ibdata1:128M:autoextend \ - --log-bin-trust-routine-creators \ + --log-bin-trust-function-creators \ $MASTER_40_ARGS \ $SMALL_SERVER \ $EXTRA_MASTER_OPT $EXTRA_MASTER_MYSQLD_OPT \ @@ -1429,7 +1429,7 @@ start_slave() --report-port=$slave_port \ --master-retry-count=10 \ -O slave_net_timeout=10 \ - --log-bin-trust-routine-creators \ + --log-bin-trust-function-creators \ $SMALL_SERVER \ $EXTRA_SLAVE_OPT $EXTRA_SLAVE_MYSQLD_OPT" CUR_MYERR=$slave_err diff --git a/mysql-test/mysql_test_run_new.c b/mysql-test/mysql_test_run_new.c index 33a69eba872..79db71fa274 100644 --- a/mysql-test/mysql_test_run_new.c +++ b/mysql-test/mysql_test_run_new.c @@ -486,7 +486,7 @@ void start_master() #endif add_arg(&al, "--local-infile"); add_arg(&al, "--core"); - add_arg(&al, "--log-bin-trust-routine-creators"); + add_arg(&al, "--log-bin-trust-function-creators"); add_arg(&al, "--datadir=%s", master_dir); #ifndef __WIN__ add_arg(&al, "--pid-file=%s", master_pid); diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index ca9a2662f94..84779858b75 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1, t2; select 0,256,00000000000000065536,2147483647,-2147483648,2147483648,+4294967296; 0 256 00000000000000065536 2147483647 -2147483648 2147483648 4294967296 0 256 65536 2147483647 -2147483648 2147483648 4294967296 diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 1f8c6cb464d..a201af78518 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1,4 +1,4 @@ -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,t5; drop database if exists mysqltest; create table t1 (b char(0)); insert into t1 values (""),(null); @@ -654,3 +654,92 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +CREATE TABLE t2 ( +a int(11) default NULL +); +insert into t2 values(111); +create table t1 ( +a varchar(12) charset utf8 collate utf8_bin not null, +b int not null, primary key (a) +) select a, 1 as b from t2 ; +Warnings: +Warning 1364 Field 'a' doesn't have a default value +Warning 1364 Field 'b' doesn't have a default value +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(12) character set utf8 collate utf8_bin NOT NULL, + `b` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 ( +a varchar(12) charset utf8 collate utf8_bin not null, +b int not null, primary key (a) +) select 'a' as a , 1 as b from t2 ; +Warnings: +Warning 1364 Field 'a' doesn't have a default value +Warning 1364 Field 'b' doesn't have a default value +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(12) character set utf8 collate utf8_bin NOT NULL, + `b` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 ( +a varchar(12) charset utf8 collate utf8_bin, +b int not null, primary key (a) +) select 'a' as a , 1 as b from t2 ; +Warnings: +Warning 1364 Field 'b' doesn't have a default value +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(12) character set utf8 collate utf8_bin NOT NULL default '', + `b` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; +create table t1 ( +a1 int not null, +a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int +); +insert into t1 values (1,1,1, 1,1,1, 1,1,1); +create table t2 ( +a1 varchar(12) charset utf8 collate utf8_bin not null, +a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, +primary key (a1) +) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ; +Warnings: +Warning 1364 Field 'a1' doesn't have a default value +drop table t2; +create table t2 ( +a1 varchar(12) charset utf8 collate utf8_bin, +a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int +) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1; +drop table t1, t2; +create table t1 ( +a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int +); +insert into t1 values (1,1,1, 1,1,1, 1,1,1); +create table t2 ( +a1 varchar(12) charset utf8 collate utf8_bin not null, +a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, +primary key (a1) +) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ; +Warnings: +Warning 1364 Field 'a1' doesn't have a default value +drop table t2; +create table t2 ( a int default 3, b int default 3) +select a1,a2 from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) default '3', + `b` int(11) default '3', + `a1` int(11) default NULL, + `a2` int(11) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result index ea0d34271b5..2e3d11ad461 100644 --- a/mysql-test/r/csv.result +++ b/mysql-test/r/csv.result @@ -4929,3 +4929,50 @@ Warnings: Note 1051 Unknown table 't2' Note 1051 Unknown table 't3' Note 1051 Unknown table 't4' +DROP TABLE IF EXISTS bug13894; +CREATE TABLE bug13894 ( val integer ) ENGINE = CSV; +INSERT INTO bug13894 VALUES (5); +INSERT INTO bug13894 VALUES (10); +INSERT INTO bug13894 VALUES (11); +INSERT INTO bug13894 VALUES (10); +SELECT * FROM bug13894; +val +5 +10 +11 +10 +UPDATE bug13894 SET val=6 WHERE val=10; +SELECT * FROM bug13894; +val +5 +11 +6 +6 +DROP TABLE bug13894; +DROP TABLE IF EXISTS bug14672; +CREATE TABLE bug14672 (c1 integer) engine = CSV; +INSERT INTO bug14672 VALUES (1), (2), (3); +SELECT * FROM bug14672; +c1 +1 +2 +3 +DELETE FROM bug14672 WHERE c1 = 2; +SELECT * FROM bug14672; +c1 +1 +3 +INSERT INTO bug14672 VALUES (4); +SELECT * FROM bug14672; +c1 +1 +3 +4 +INSERT INTO bug14672 VALUES (5); +SELECT * FROM bug14672; +c1 +1 +3 +4 +5 +DROP TABLE bug14672; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index d013de58a88..0b193b2107c 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -648,12 +648,16 @@ drop function sub1; select table_name from information_schema.views where table_schema='test'; table_name +v2 +v3 Warnings: Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select table_name from information_schema.views where table_schema='test'; table_name +v2 +v3 Warnings: Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them @@ -670,6 +674,16 @@ f1_key select constraint_name from information_schema.table_constraints where table_schema='test'; constraint_name +show create view v2; +View Create View +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `test`.`t1`.`f1` AS `c` from `t1` +Warnings: +Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +show create table v3; +View Create View +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select sql_no_cache `test`.`sub1`(1) AS `c` +Warnings: +Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them drop view v2; drop view v3; drop table t4; @@ -723,6 +737,7 @@ information_schema ROUTINES SQL_MODE information_schema TRIGGERS ACTION_CONDITION information_schema TRIGGERS ACTION_STATEMENT information_schema TRIGGERS SQL_MODE +information_schema TRIGGERS DEFINER information_schema VIEWS VIEW_DEFINITION select table_name, column_name, data_type from information_schema.columns where data_type = 'datetime'; @@ -801,45 +816,45 @@ set @fired:= "Yes"; end if; end| show triggers; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 begin if new.j > 10 then set new.j := 10; end if; -end BEFORE NULL +end BEFORE NULL root@localhost trg2 UPDATE t1 begin if old.i % 2 = 0 then set new.j := -1; end if; -end BEFORE NULL +end BEFORE NULL root@localhost trg3 UPDATE t1 begin if new.j = -1 then set @fired:= "Yes"; end if; -end AFTER NULL +end AFTER NULL root@localhost select * from information_schema.triggers; -TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER NULL test trg1 INSERT NULL test t1 0 NULL begin if new.j > 10 then set new.j := 10; end if; -end ROW BEFORE NULL NULL OLD NEW NULL +end ROW BEFORE NULL NULL OLD NEW NULL root@localhost NULL test trg2 UPDATE NULL test t1 0 NULL begin if old.i % 2 = 0 then set new.j := -1; end if; -end ROW BEFORE NULL NULL OLD NEW NULL +end ROW BEFORE NULL NULL OLD NEW NULL root@localhost NULL test trg3 UPDATE NULL test t1 0 NULL begin if new.j = -1 then set @fired:= "Yes"; end if; -end ROW AFTER NULL NULL OLD NEW NULL +end ROW AFTER NULL NULL OLD NEW NULL root@localhost drop trigger trg1; drop trigger trg2; drop trigger trg3; diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 9d514be76e8..c4dd6cf9a86 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -1403,3 +1403,67 @@ SELECT v2.x FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d); ERROR 42S22: Unknown column 'v2.x' in 'field list' DROP VIEW v1, v2; DROP TABLE t1, t2, t3, t4, t5, t6; +create table t1 (id1 int(11) not null); +insert into t1 values (1),(2); +create table t2 (id2 int(11) not null); +insert into t2 values (1),(2),(3),(4); +create table t3 (id3 char(16) not null); +insert into t3 values ('100'); +create table t4 (id2 int(11) not null, id3 char(16)); +create table t5 (id1 int(11) not null, key (id1)); +insert into t5 values (1),(2),(1); +create view v1 as +select t4.id3 from t4 join t2 on t4.id2 = t2.id2; +select t1.id1 from t1 inner join (t3 left join v1 on t3.id3 = v1.id3); +id1 +1 +2 +drop view v1; +drop table t1, t2, t3, t4, t5; +create table t0 (a int); +insert into t0 values (0),(1),(2),(3); +create table t1(a int); +insert into t1 select A.a + 10*(B.a) from t0 A, t0 B; +create table t2 (a int, b int); +insert into t2 values (1,1), (2,2), (3,3); +create table t3(a int, b int, filler char(200), key(a)); +insert into t3 select a,a,'filler' from t1; +insert into t3 select a,a,'filler' from t1; +create table t4 like t3; +insert into t4 select * from t3; +insert into t4 select * from t3; +create table t5 like t4; +insert into t5 select * from t4; +insert into t5 select * from t4; +create table t6 like t5; +insert into t6 select * from t5; +insert into t6 select * from t5; +create table t7 like t6; +insert into t7 select * from t6; +insert into t7 select * from t6; +explain select * from t4 join +t2 left join (t3 join t5 on t5.a=t3.b) on t3.a=t2.b where t4.a<=>t3.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL X +1 SIMPLE t3 ref a a 5 test.t2.b X +1 SIMPLE t5 ref a a 5 test.t3.b X +1 SIMPLE t4 ref a a 5 test.t3.b X Using where +explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b +join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL X +1 SIMPLE t3 ref a a 5 test.t2.b X Using where +1 SIMPLE t4 ref a a 5 test.t3.b X +1 SIMPLE t6 ref a a 5 test.t4.b X +1 SIMPLE t5 ref a a 5 test.t2.b X +1 SIMPLE t7 ref a a 5 test.t5.b X +explain select * from t2 left join +(t3 left join (t4 join t6 on t6.a=t4.b) on t4.a=t3.b +join t5 on t5.a=t3.b) on t3.a=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL X +1 SIMPLE t3 ref a a 5 test.t2.b X +1 SIMPLE t4 ref a a 5 test.t3.b X +1 SIMPLE t6 ref a a 5 test.t4.b X +1 SIMPLE t5 ref a a 5 test.t3.b X +drop table t0, t1, t2, t4, t5, t6; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index b4265a99604..7da55b66376 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -701,6 +701,24 @@ t1 1 a 2 b A 0 NULL NULL YES BTREE t1 1 a 3 c A 0 NULL NULL YES BTREE t1 1 a 4 d A 0 NULL NULL YES BTREE set myisam_stats_method=DEFAULT; +drop table t1; +create table t1( +cip INT NOT NULL, +time TIME NOT NULL, +score INT NOT NULL DEFAULT 0, +bob TINYBLOB +); +insert into t1 (cip, time) VALUES (1, '00:01'), (2, '00:02'), (3,'00:03'); +insert into t1 (cip, bob, time) VALUES (4, 'a', '00:04'), (5, 'b', '00:05'), +(6, 'c', '00:06'); +select * from t1 where bob is null and cip=1; +cip time score bob +1 00:01:00 0 NULL +create index bug on t1 (bob(22), cip, time); +select * from t1 where bob is null and cip=1; +cip time score bob +1 00:01:00 0 NULL +drop table t1; set storage_engine=MyISAM; drop table if exists t1,t2,t3; --- Testing varchar --- diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 82a761252b5..3b3db08303a 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -1614,6 +1614,16 @@ mysqldump: Couldn't find table: "t\1" mysqldump: Couldn't find table: "t/1" +mysqldump: Couldn't find table: "T_1" + +mysqldump: Couldn't find table: "T%1" + +mysqldump: Couldn't find table: "T'1" + +mysqldump: Couldn't find table: "T_1" + +mysqldump: Couldn't find table: "T_" + test_sequence ------ Testing with illegal database names ------ mysqldump: Got error: 1049: Unknown database 'mysqldump_test_d' when selecting the database @@ -1926,23 +1936,23 @@ end if; end| set sql_mode=default| show triggers like "t1"; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 begin if new.a > 10 then set new.a := 10; set new.a := 11; end if; -end BEFORE 0000-00-00 00:00:00 +end BEFORE 0000-00-00 00:00:00 root@localhost trg2 UPDATE t1 begin if old.a % 2 = 0 then set new.b := 12; end if; -end BEFORE 0000-00-00 00:00:00 +end BEFORE 0000-00-00 00:00:00 root@localhost trg3 UPDATE t1 begin if new.a = -1 then set @fired:= "Yes"; end if; -end AFTER 0000-00-00 00:00:00 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER +end AFTER 0000-00-00 00:00:00 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost INSERT INTO t1 (a) VALUES (1),(2),(3),(22); update t1 set a = 4 where a=3; @@ -2085,29 +2095,29 @@ Tables_in_test t1 t2 show triggers; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 begin if new.a > 10 then set new.a := 10; set new.a := 11; end if; -end BEFORE # +end BEFORE # root@localhost trg2 UPDATE t1 begin if old.a % 2 = 0 then set new.b := 12; end if; -end BEFORE # +end BEFORE # root@localhost trg3 UPDATE t1 begin if new.a = -1 then set @fired:= "Yes"; end if; -end AFTER # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER +end AFTER # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost trg4 INSERT t2 begin if new.a > 10 then set @fired:= "No"; end if; -end BEFORE # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER +end BEFORE # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER root@localhost DROP TABLE t1, t2; --port=1234 --port=1234 @@ -2130,9 +2140,9 @@ SELECT * FROM `test2`; a2 1 SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer testref INSERT test1 BEGIN -INSERT INTO test2 SET a2 = NEW.a1; END BEFORE NULL +INSERT INTO test2 SET a2 = NEW.a1; END BEFORE NULL root@localhost SELECT * FROM `test1`; a1 1 @@ -2147,6 +2157,7 @@ DROP FUNCTION IF EXISTS bug9056_func1; DROP FUNCTION IF EXISTS bug9056_func2; DROP PROCEDURE IF EXISTS bug9056_proc1; DROP PROCEDURE IF EXISTS bug9056_proc2; +DROP PROCEDURE IF EXISTS `a'b`; CREATE TABLE t1 (id int); INSERT INTO t1 VALUES(1), (2), (3), (4), (5); CREATE FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11) RETURN a+b // diff --git a/mysql-test/r/read_only.result b/mysql-test/r/read_only.result new file mode 100644 index 00000000000..55a14bcaec8 --- /dev/null +++ b/mysql-test/r/read_only.result @@ -0,0 +1,43 @@ +DROP TABLE IF EXISTS t1,t2,t3; +grant CREATE, SELECT, DROP on *.* to test@localhost; +set global read_only=0; +create table t1 (a int); +insert into t1 values(1); +create table t2 select * from t1; +set global read_only=1; +create table t3 (a int); +drop table t3; +select @@global.read_only; +@@global.read_only +1 +create table t3 (a int); +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +insert into t1 values(1); +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +update t1 set a=1 where 1=0; +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +update t1,t2 set t1.a=t2.a+1 where t1.a=t2.a; +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +delete t1,t2 from t1,t2 where t1.a=t2.a; +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +create temporary table t3 (a int); +create temporary table t4 (a int) select * from t3; +insert into t3 values(1); +insert into t4 select * from t3; +update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a; +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +update t1,t3 set t3.a=t1.a+1 where t1.a=t3.a; +update t4,t3 set t4.a=t3.a+1 where t4.a=t3.a; +delete t1 from t1,t3 where t1.a=t3.a; +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +delete t3 from t1,t3 where t1.a=t3.a; +delete t4 from t3,t4 where t4.a=t3.a; +create temporary table t1 (a int); +insert into t1 values(1); +update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a; +delete t1 from t1,t3 where t1.a=t3.a; +drop table t1; +insert into t1 values(1); +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +drop table t1,t2; +drop user test@localhost; diff --git a/mysql-test/r/rpl_ddl.result b/mysql-test/r/rpl_ddl.result index 2a97da63c64..4d8f2f11d4a 100644 --- a/mysql-test/r/rpl_ddl.result +++ b/mysql-test/r/rpl_ddl.result @@ -1465,13 +1465,13 @@ flush logs; -------- switch to master ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode -trg1 INSERT t1 SET @a:=1 BEFORE NULL +Trigger Event Table Statement Timing Created sql_mode Definer +trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost -------- switch to slave ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode -trg1 INSERT t1 SET @a:=1 BEFORE NULL +Trigger Event Table Statement Timing Created sql_mode Definer +trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost ######## DROP TRIGGER trg1 ######## @@ -1520,11 +1520,11 @@ flush logs; -------- switch to master ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer -------- switch to slave ------- SHOW TRIGGERS; -Trigger Event Table Statement Timing Created sql_mode +Trigger Event Table Statement Timing Created sql_mode Definer ######## CREATE USER user1@localhost ######## diff --git a/mysql-test/r/rpl_sp.result b/mysql-test/r/rpl_sp.result index 5f1c3afd14d..ba840caf6c2 100644 --- a/mysql-test/r/rpl_sp.result +++ b/mysql-test/r/rpl_sp.result @@ -4,16 +4,11 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; -create database if not exists mysqltest1; +drop database if exists mysqltest1; +create database mysqltest1; use mysqltest1; create table t1 (a varchar(100)); use mysqltest1; -drop procedure if exists foo; -drop procedure if exists foo2; -drop procedure if exists foo3; -drop procedure if exists foo4; -drop procedure if exists bar; -drop function if exists fn1; create procedure foo() begin declare b int; @@ -21,21 +16,9 @@ set b = 8; insert into t1 values (b); insert into t1 values (unix_timestamp()); end| -ERROR HY000: This routine has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) -show binlog events from 98| -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # create database if not exists mysqltest1 -master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a varchar(100)) -create procedure foo() deterministic -begin -declare b int; -set b = 8; -insert into t1 values (b); -insert into t1 values (unix_timestamp()); -end| select * from mysql.proc where name='foo' and db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment -mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL YES DEFINER begin +mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin declare b int; set b = 8; insert into t1 values (b); @@ -43,7 +26,7 @@ insert into t1 values (unix_timestamp()); end root@localhost # # select * from mysql.proc where name='foo' and db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment -mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL YES DEFINER begin +mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin declare b int; set b = 8; insert into t1 values (b); @@ -51,17 +34,6 @@ insert into t1 values (unix_timestamp()); end @ # # set timestamp=1000000000; call foo(); -show binlog events from 308; -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo() deterministic -begin -declare b int; -set b = 8; -insert into t1 values (b); -insert into t1 values (unix_timestamp()); -end -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8)) -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (unix_timestamp()) select * from t1; a 8 @@ -72,22 +44,10 @@ a 1000000000 delete from t1; create procedure foo2() -not deterministic -reads sql data select * from mysqltest1.t1; call foo2(); a -show binlog events from 518; -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8)) -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (unix_timestamp()) -master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 -master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo2() -not deterministic -reads sql data -select * from mysqltest1.t1 alter procedure foo2 contains sql; -ERROR HY000: This routine has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) drop table t1; create table t1 (a int); create table t2 like t1; @@ -99,57 +59,21 @@ grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1; create procedure foo4() deterministic -insert into t1 values (10); -ERROR HY000: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) -set global log_bin_trust_routine_creators=1; -create procedure foo4() -deterministic begin insert into t2 values(3); insert into t1 values (5); end| call foo4(); Got one of the listed errors -show warnings; -Level Code Message -Error 1142 INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1' -Warning 1417 A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes call foo3(); show warnings; Level Code Message call foo4(); Got one of the listed errors -show warnings; -Level Code Message -Error 1142 INSERT command denied to user 'zedjzlcsjhd'@'127.0.0.1' for table 't1' -Warning 1417 A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes alter procedure foo4 sql security invoker; call foo4(); show warnings; Level Code Message -show binlog events from 990; -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query 1 # use `mysqltest1`; drop table t1 -master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a int) -master-bin.000001 # Query 1 # use `mysqltest1`; create table t2 like t1 -master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo3() -deterministic -insert into t1 values (15) -master-bin.000001 # Query 1 # use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1 -master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1 -master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1 -master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo4() -deterministic -begin -insert into t2 values(3); -insert into t1 values (5); -end -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (15) -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) -master-bin.000001 # Query 1 # use `mysqltest1`; alter procedure foo4 sql security invoker -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) -master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (5) select * from t1; a 15 @@ -168,11 +92,29 @@ a 3 3 3 +delete from t2; +alter table t2 add unique (a); +drop procedure foo4; +create procedure foo4() +deterministic +begin +insert into t2 values(20),(20); +end| +call foo4(); +ERROR 23000: Duplicate entry '20' for key 1 +show warnings; +Level Code Message +Error 1062 Duplicate entry '20' for key 1 +select * from t2; +a +20 +select * from t2; +a +20 select * from mysql.proc where name="foo4" and db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment -mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES INVOKER begin -insert into t2 values(3); -insert into t1 values (5); +mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES DEFINER begin +insert into t2 values(20),(20); end @ # # drop procedure foo4; select * from mysql.proc where name="foo4" and db='mysqltest1'; @@ -184,6 +126,13 @@ drop procedure foo2; drop procedure foo3; create function fn1(x int) returns int +begin +insert into t1 values (x); +return x+2; +end| +ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) +create function fn1(x int) +returns int deterministic begin insert into t1 values (x); @@ -211,17 +160,54 @@ a drop function fn1; create function fn1() returns int -deterministic +no sql begin return unix_timestamp(); end| +alter function fn1 contains sql; +ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) delete from t1; set timestamp=1000000000; insert into t1 values(fn1()); +create function fn2() +returns int +no sql +begin +return unix_timestamp(); +end| +ERROR HY000: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) +set global log_bin_trust_routine_creators=1; +Warnings: +Warning 1287 'log_bin_trust_routine_creators' is deprecated; use 'log_bin_trust_function_creators' instead +set global log_bin_trust_function_creators=0; +set global log_bin_trust_function_creators=1; +set global log_bin_trust_function_creators=1; +create function fn2() +returns int +no sql +begin +return unix_timestamp(); +end| +create function fn3() +returns int +not deterministic +reads sql data +begin +return 0; +end| +select fn3(); +fn3() +0 select * from mysql.proc where db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment -mysqltest1 fn1 FUNCTION fn1 SQL CONTAINS_SQL YES DEFINER int(11) begin +mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin +return unix_timestamp(); +end root@localhost # # +mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); +end zedjzlcsjhd@localhost # # +mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin +return 0; end root@localhost # # select * from t1; a @@ -232,12 +218,34 @@ a 1000000000 select * from mysql.proc where db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment -mysqltest1 fn1 FUNCTION fn1 SQL CONTAINS_SQL YES DEFINER int(11) begin +mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin +return unix_timestamp(); +end @ # # +mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); end @ # # +mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin +return 0; +end @ # # +delete from t2; +alter table t2 add unique (a); +drop function fn1; +create function fn1() +returns int +begin +insert into t2 values(20),(20); +return 10; +end| +select fn1(); +ERROR 23000: Duplicate entry '20' for key 1 +select * from t2; +a +20 +select * from t2; +a +20 create trigger trg before insert on t1 for each row set new.a= 10; ERROR 42000: Access denied; you need the SUPER privilege for this operation -flush logs; delete from t1; create trigger trg before insert on t1 for each row set new.a= 10; insert into t1 values (1); @@ -253,14 +261,106 @@ insert into t1 values (1); select * from t1; a 1 -show binlog events in 'master-bin.000002' from 98; +show binlog events in 'master-bin.000001' from 98; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1 -master-bin.000002 # Query 1 # use `mysqltest1`; create trigger trg before insert on t1 for each row set new.a= 10 -master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1) -master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1 -master-bin.000002 # Query 1 # use `mysqltest1`; drop trigger trg -master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1) +master-bin.000001 # Query 1 # drop database if exists mysqltest1 +master-bin.000001 # Query 1 # create database mysqltest1 +master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a varchar(100)) +master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo() +begin +declare b int; +set b = 8; +insert into t1 values (b); +insert into t1 values (unix_timestamp()); +end +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8)) +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (unix_timestamp()) +master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 +master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo2() +select * from mysqltest1.t1 +master-bin.000001 # Query 1 # use `mysqltest1`; alter procedure foo2 contains sql +master-bin.000001 # Query 1 # use `mysqltest1`; drop table t1 +master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a int) +master-bin.000001 # Query 1 # use `mysqltest1`; create table t2 like t1 +master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo3() +deterministic +insert into t1 values (15) +master-bin.000001 # Query 1 # use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1 +master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1 +master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1 +master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo4() +deterministic +begin +insert into t2 values(3); +insert into t1 values (5); +end +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (15) +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) +master-bin.000001 # Query 1 # use `mysqltest1`; alter procedure foo4 sql security invoker +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (5) +master-bin.000001 # Query 1 # use `mysqltest1`; delete from t2 +master-bin.000001 # Query 1 # use `mysqltest1`; alter table t2 add unique (a) +master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo4 +master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo4() +deterministic +begin +insert into t2 values(20),(20); +end +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(20),(20) +master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo4 +master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo +master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo2 +master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo3 +master-bin.000001 # Query 1 # use `mysqltest1`; create function fn1(x int) +returns int +deterministic +begin +insert into t1 values (x); +return x+2; +end +master-bin.000001 # Query 1 # use `mysqltest1`; delete t1,t2 from t1,t2 +master-bin.000001 # Query 1 # use `mysqltest1`; DO `fn1`(20) +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(fn1(21)) +master-bin.000001 # Query 1 # use `mysqltest1`; drop function fn1 +master-bin.000001 # Query 1 # use `mysqltest1`; create function fn1() +returns int +no sql +begin +return unix_timestamp(); +end +master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values(fn1()) +master-bin.000001 # Query 1 # use `mysqltest1`; create function fn2() +returns int +no sql +begin +return unix_timestamp(); +end +master-bin.000001 # Query 1 # use `mysqltest1`; create function fn3() +returns int +not deterministic +reads sql data +begin +return 0; +end +master-bin.000001 # Query 1 # use `mysqltest1`; delete from t2 +master-bin.000001 # Query 1 # use `mysqltest1`; alter table t2 add unique (a) +master-bin.000001 # Query 1 # use `mysqltest1`; drop function fn1 +master-bin.000001 # Query 1 # use `mysqltest1`; create function fn1() +returns int +begin +insert into t2 values(20),(20); +return 10; +end +master-bin.000001 # Query 1 # use `mysqltest1`; DO `fn1`() +master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 +master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger trg before insert on t1 for each row set new.a= 10 +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (1) +master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 +master-bin.000001 # Query 1 # use `mysqltest1`; drop trigger trg +master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (1) select * from t1; a 1 diff --git a/mysql-test/r/rpl_trigger.result b/mysql-test/r/rpl_trigger.result index db824c9c423..999af131b8b 100644 --- a/mysql-test/r/rpl_trigger.result +++ b/mysql-test/r/rpl_trigger.result @@ -89,8 +89,24 @@ insert into t1 set a = now(); select a=b && a=c from t1; a=b && a=c 1 +SELECT routine_name, definer +FROM information_schema.routines; +routine_name definer +bug12480 root@localhost +SELECT trigger_name, definer +FROM information_schema.triggers; +trigger_name definer +t1_first root@localhost --- On slave -- +SELECT routine_name, definer +FROM information_schema.routines; +routine_name definer +bug12480 @ +SELECT trigger_name, definer +FROM information_schema.triggers; +trigger_name definer +t1_first root@localhost select a=b && a=c from t1; a=b && a=c 1 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 318cf8e7b65..8a126b7ddfb 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3241,3 +3241,45 @@ f1 f2 Warnings: Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 drop table t1; +create table t1 (f1 int, f2 int); +insert into t1 values (1, 30), (2, 20), (3, 10); +create algorithm=merge view v1 as select f1, f2 from t1; +create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1; +create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1; +select t1.f1 as x1, f1 from t1 order by t1.f1; +x1 f1 +1 1 +2 2 +3 3 +select v1.f1 as x1, f1 from v1 order by v1.f1; +x1 f1 +1 1 +2 2 +3 3 +select v2.f1 as x1, f1 from v2 order by v2.f1; +x1 f1 +10 10 +20 20 +30 30 +select v3.f1 as x1, f1 from v3 order by v3.f1; +x1 f1 +10 10 +20 20 +30 30 +select f1, f2, v1.f1 as x1 from v1 order by v1.f1; +f1 f2 x1 +1 30 1 +2 20 2 +3 10 3 +select f1, f2, v2.f1 as x1 from v2 order by v2.f1; +f1 f2 x1 +10 3 10 +20 2 20 +30 1 30 +select f1, f2, v3.f1 as x1 from v3 order by v3.f1; +f1 f2 x1 +10 3 10 +20 2 20 +30 1 30 +drop table t1; +drop view v1, v2, v3; diff --git a/mysql-test/r/skip_grants.result b/mysql-test/r/skip_grants.result index 4d723f8e12a..5dc770a7363 100644 --- a/mysql-test/r/skip_grants.result +++ b/mysql-test/r/skip_grants.result @@ -4,7 +4,7 @@ drop procedure if exists f1; use test; create table t1 (field1 INT); CREATE VIEW v1 AS SELECT field1 FROM t1; -ERROR HY000: View definer is not fully qualified +ERROR HY000: Definer is not fully qualified drop table t1; create procedure f1() select 1; drop procedure f1; diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 50ff7ea264a..fabbf13a96b 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -916,3 +916,16 @@ ERROR 42S22: Unknown column 'bug13037_foo' in 'field list' DROP PROCEDURE bug13037_p1; DROP PROCEDURE bug13037_p2; DROP PROCEDURE bug13037_p3; +create database mysqltest1; +create database mysqltest2; +use mysqltest1; +drop database mysqltest1; +create procedure mysqltest2.p1() select version(); +create procedure p2() select version(); +ERROR 3D000: No database selected +use mysqltest2; +show procedure status; +Db Name Type Definer Modified Created Security_type Comment +mysqltest2 p1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER +drop database mysqltest2; +use test; diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index a23b584e510..4dec7882d58 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -172,3 +172,75 @@ group by country; countrycount smcnt country total_funds 1 1200 USA 1200 drop table t1; +CREATE TABLE `t1` ( +`t3_id` int NOT NULL, +`t1_id` int NOT NULL, +PRIMARY KEY (`t1_id`) +); +CREATE TABLE `t2` ( +`t2_id` int NOT NULL, +`t1_id` int NOT NULL, +`b` int NOT NULL, +PRIMARY KEY (`t2_id`), +UNIQUE KEY `idx_t2_t1_b` (`t1_id`,`b`) +) ENGINE=InnoDB; +CREATE TABLE `t3` ( +`t3_id` int NOT NULL +); +INSERT INTO `t3` VALUES (3); +select +(SELECT rs.t2_id +FROM t2 rs +WHERE rs.t1_id= +(SELECT lt.t1_id +FROM t1 lt +WHERE lt.t3_id=a.t3_id) +ORDER BY b DESC LIMIT 1) +from t3 AS a; +(SELECT rs.t2_id +FROM t2 rs +WHERE rs.t1_id= +(SELECT lt.t1_id +FROM t1 lt +WHERE lt.t3_id=a.t3_id) +ORDER BY b DESC LIMIT 1) +NULL +DROP PROCEDURE IF EXISTS p1; +create procedure p1() +begin +declare done int default 3; +repeat +select +(SELECT rs.t2_id +FROM t2 rs +WHERE rs.t1_id= +(SELECT lt.t1_id +FROM t1 lt +WHERE lt.t3_id=a.t3_id) +ORDER BY b DESC LIMIT 1) as x +from t3 AS a; +set done= done-1; +until done <= 0 end repeat; +end// +call p1(); +x +NULL +x +NULL +x +NULL +call p1(); +x +NULL +x +NULL +x +NULL +call p1(); +x +NULL +x +NULL +x +NULL +drop tables t1,t2,t3; diff --git a/mysql-test/r/trigger-compat.result b/mysql-test/r/trigger-compat.result new file mode 100644 index 00000000000..5c104a2d2d5 --- /dev/null +++ b/mysql-test/r/trigger-compat.result @@ -0,0 +1,40 @@ +DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; +FLUSH PRIVILEGES; +DROP DATABASE IF EXISTS mysqltest_db1; +CREATE DATABASE mysqltest_db1; +CREATE USER mysqltest_dfn@localhost; +CREATE USER mysqltest_inv@localhost; +GRANT SUPER ON *.* TO mysqltest_dfn@localhost; +GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; + +---> connection: wl2818_definer_con +CREATE TABLE t1(num_value INT); +CREATE TABLE t2(user_str TEXT); +CREATE TRIGGER wl2818_trg1 BEFORE INSERT ON t1 +FOR EACH ROW +INSERT INTO t2 VALUES(CURRENT_USER()); + +---> patching t1.TRG... + +CREATE TRIGGER wl2818_trg2 AFTER INSERT ON t1 +FOR EACH ROW +INSERT INTO t2 VALUES(CURRENT_USER()); +Warnings: +Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'wl2818_trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. + +SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; +trigger_name definer +wl2818_trg1 +wl2818_trg2 mysqltest_dfn@localhost +Warnings: +Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'wl2818_trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. + +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER +NULL mysqltest_db1 wl2818_trg1 INSERT NULL mysqltest_db1 t1 0 NULL +INSERT INTO t2 VALUES(CURRENT_USER()) ROW BEFORE NULL NULL OLD NEW NULL +NULL mysqltest_db1 wl2818_trg2 INSERT NULL mysqltest_db1 t1 0 NULL +INSERT INTO t2 VALUES(CURRENT_USER()) ROW AFTER NULL NULL OLD NEW NULL mysqltest_dfn@localhost diff --git a/mysql-test/r/trigger-grant.result b/mysql-test/r/trigger-grant.result new file mode 100644 index 00000000000..eda1adfdf65 --- /dev/null +++ b/mysql-test/r/trigger-grant.result @@ -0,0 +1,238 @@ +DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; +FLUSH PRIVILEGES; +DROP DATABASE IF EXISTS mysqltest_db1; +CREATE DATABASE mysqltest_db1; +CREATE USER mysqltest_dfn@localhost; +CREATE USER mysqltest_inv@localhost; +GRANT SUPER ON *.* TO mysqltest_dfn@localhost; +GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; + +---> connection: wl2818_definer_con +CREATE TABLE t1(num_value INT); +CREATE TABLE t2(user_str TEXT); +CREATE TRIGGER trg1 AFTER INSERT ON t1 +FOR EACH ROW +INSERT INTO t2 VALUES(CURRENT_USER()); + +---> connection: default +GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; +GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; +GRANT ALL PRIVILEGES ON mysqltest_db1.t1 +TO 'mysqltest_inv'@localhost; +GRANT SELECT ON mysqltest_db1.t2 +TO 'mysqltest_inv'@localhost; + +---> connection: wl2818_definer_con +use mysqltest_db1; +INSERT INTO t1 VALUES(1); +SELECT * FROM t1; +num_value +1 +SELECT * FROM t2; +user_str +mysqltest_dfn@localhost + +---> connection: wl2818_invoker_con +use mysqltest_db1; +INSERT INTO t1 VALUES(2); +SELECT * FROM t1; +num_value +1 +2 +SELECT * FROM t2; +user_str +mysqltest_dfn@localhost +mysqltest_dfn@localhost + +---> connection: default +use mysqltest_db1; +REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost; + +---> connection: wl2818_invoker_con +use mysqltest_db1; +INSERT INTO t1 VALUES(3); +ERROR 42000: INSERT command denied to user 'mysqltest_dfn'@'localhost' for table 't2' +SELECT * FROM t1; +num_value +1 +2 +3 +SELECT * FROM t2; +user_str +mysqltest_dfn@localhost +mysqltest_dfn@localhost + +---> connection: default +use mysqltest_db1; +REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; + +---> connection: wl2818_definer_con +use mysqltest_db1; +DROP TRIGGER trg1; +SET @new_sum = 0; +SET @old_sum = 0; +---> INSERT INTO statement; BEFORE timing +CREATE TRIGGER trg1 BEFORE INSERT ON t1 +FOR EACH ROW +SET @new_sum = @new_sum + NEW.num_value; +INSERT INTO t1 VALUES(4); +ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> INSERT INTO statement; AFTER timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 AFTER INSERT ON t1 +FOR EACH ROW +SET @new_sum = @new_sum + NEW.num_value; +INSERT INTO t1 VALUES(5); +ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> UPDATE statement; BEFORE timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 BEFORE UPDATE ON t1 +FOR EACH ROW +SET @old_sum = @old_sum + OLD.num_value; +UPDATE t1 SET num_value = 10; +ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> UPDATE statement; AFTER timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 AFTER UPDATE ON t1 +FOR EACH ROW +SET @new_sum = @new_sum + NEW.num_value; +UPDATE t1 SET num_value = 20; +ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> DELETE statement; BEFORE timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 BEFORE DELETE ON t1 +FOR EACH ROW +SET @old_sum = @old_sum + OLD.num_value; +DELETE FROM t1; +ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> DELETE statement; AFTER timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 AFTER DELETE ON t1 +FOR EACH ROW +SET @old_sum = @old_sum + OLD.num_value; +DELETE FROM t1; +ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1' + +---> connection: default +use mysqltest_db1; +GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; +REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; + +---> connection: wl2818_definer_con +use mysqltest_db1; +DROP TRIGGER trg1; +SET @new_sum = 0; +SET @old_sum = 0; +---> INSERT INTO statement; BEFORE timing +CREATE TRIGGER trg1 BEFORE INSERT ON t1 +FOR EACH ROW +SET @new_sum = @new_sum + NEW.num_value; +INSERT INTO t1 VALUES(4); +ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> INSERT INTO statement; AFTER timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 AFTER INSERT ON t1 +FOR EACH ROW +SET @new_sum = @new_sum + NEW.num_value; +INSERT INTO t1 VALUES(5); +ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> UPDATE statement; BEFORE timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 BEFORE UPDATE ON t1 +FOR EACH ROW +SET @old_sum = @old_sum + OLD.num_value; +UPDATE t1 SET num_value = 10; +ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> UPDATE statement; AFTER timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 AFTER UPDATE ON t1 +FOR EACH ROW +SET @new_sum = @new_sum + NEW.num_value; +UPDATE t1 SET num_value = 20; +ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> DELETE statement; BEFORE timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 BEFORE DELETE ON t1 +FOR EACH ROW +SET @old_sum = @old_sum + OLD.num_value; +DELETE FROM t1; +ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' +---> DELETE statement; AFTER timing +DROP TRIGGER trg1; +CREATE TRIGGER trg1 AFTER DELETE ON t1 +FOR EACH ROW +SET @old_sum = @old_sum + OLD.num_value; +DELETE FROM t1; +ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1' + +---> connection: wl2818_definer_con +use mysqltest_db1; +DROP TRIGGER trg1; +CREATE DEFINER='mysqltest_inv'@'localhost' + TRIGGER trg1 BEFORE INSERT ON t1 +FOR EACH ROW +SET @new_sum = 0; +CREATE DEFINER='mysqltest_nonexs'@'localhost' + TRIGGER trg2 AFTER INSERT ON t1 +FOR EACH ROW +SET @new_sum = 0; +Warnings: +Note 1449 There is no 'mysqltest_nonexs'@'localhost' registered +INSERT INTO t1 VALUES(6); +ERROR 42000: Access denied; you need the SUPER privilege for this operation +SHOW TRIGGERS; +Trigger Event Table Statement Timing Created sql_mode Definer +trg1 INSERT t1 +SET @new_sum = 0 BEFORE NULL mysqltest_inv@localhost +trg2 INSERT t1 +SET @new_sum = 0 AFTER NULL mysqltest_nonexs@localhost +DROP TRIGGER trg1; +DROP TRIGGER trg2; +CREATE TRIGGER trg1 BEFORE INSERT ON t1 +FOR EACH ROW +SET @a = 1; +CREATE TRIGGER trg2 AFTER INSERT ON t1 +FOR EACH ROW +SET @a = 2; +CREATE TRIGGER trg3 BEFORE UPDATE ON t1 +FOR EACH ROW +SET @a = 3; +CREATE TRIGGER trg4 AFTER UPDATE ON t1 +FOR EACH ROW +SET @a = 4; +CREATE TRIGGER trg5 BEFORE DELETE ON t1 +FOR EACH ROW +SET @a = 5; + +SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; +trigger_name definer +trg1 +trg2 @ +trg3 @abc@def@@ +trg4 @hostname +trg5 @abcdef@@@hostname +Warnings: +Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. + +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER +NULL mysqltest_db1 trg1 INSERT NULL mysqltest_db1 t1 0 NULL +SET @a = 1 ROW BEFORE NULL NULL OLD NEW NULL +NULL mysqltest_db1 trg2 INSERT NULL mysqltest_db1 t1 0 NULL +SET @a = 2 ROW AFTER NULL NULL OLD NEW NULL @ +NULL mysqltest_db1 trg3 UPDATE NULL mysqltest_db1 t1 0 NULL +SET @a = 3 ROW BEFORE NULL NULL OLD NEW NULL @abc@def@@ +NULL mysqltest_db1 trg4 UPDATE NULL mysqltest_db1 t1 0 NULL +SET @a = 4 ROW AFTER NULL NULL OLD NEW NULL @hostname +NULL mysqltest_db1 trg5 DELETE NULL mysqltest_db1 t1 0 NULL +SET @a = 5 ROW BEFORE NULL NULL OLD NEW NULL @abcdef@@@hostname + +---> connection: default +DROP USER mysqltest_dfn@localhost; +DROP USER mysqltest_inv@localhost; +DROP DATABASE mysqltest_db1; +Warnings: +Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index b305691fa18..d3fbb56e493 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -611,9 +611,9 @@ select @a; @a 10 show triggers; -Trigger Event Table Statement Timing Created sql_mode -t1_bi INSERT t1 set new."t1 column" = 5 BEFORE # REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI -t1_af INSERT t1 set @a=10 AFTER # +Trigger Event Table Statement Timing Created sql_mode Definer +t1_bi INSERT t1 set new."t1 column" = 5 BEFORE # REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI root@localhost +t1_af INSERT t1 set @a=10 AFTER # root@localhost drop table t1; set sql_mode="traditional"; create table t1 (a date); @@ -633,8 +633,8 @@ t1 CREATE TABLE `t1` ( `a` date default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show triggers; -Trigger Event Table Statement Timing Created sql_mode -t1_bi INSERT t1 set new.a = '2004-01-00' BEFORE # +Trigger Event Table Statement Timing Created sql_mode Definer +t1_bi INSERT t1 set new.a = '2004-01-00' BEFORE # root@localhost drop table t1; create table t1 (id int); create trigger t1_ai after insert on t1 for each row flush tables; diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index 1ed4135d52b..7d7fe7efa35 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1020,6 +1020,416 @@ cast(@non_existing_user_var/2 as DECIMAL) NULL create table t (d decimal(0,10)); ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'd'). +CREATE TABLE t1 ( +my_float FLOAT, +my_double DOUBLE, +my_varchar VARCHAR(50), +my_decimal DECIMAL(65,30) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `my_float` float default NULL, + `my_double` double default NULL, + `my_varchar` varchar(50) default NULL, + `my_decimal` decimal(65,30) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 SET my_float = 1.175494345e-32, +my_double = 1.175494345e-32, +my_varchar = '1.175494345e-32'; +INSERT INTO t1 SET my_float = 1.175494345e-31, +my_double = 1.175494345e-31, +my_varchar = '1.175494345e-31'; +INSERT INTO t1 SET my_float = 1.175494345e-30, +my_double = 1.175494345e-30, +my_varchar = '1.175494345e-30'; +INSERT INTO t1 SET my_float = 1.175494345e-29, +my_double = 1.175494345e-29, +my_varchar = '1.175494345e-29'; +INSERT INTO t1 SET my_float = 1.175494345e-28, +my_double = 1.175494345e-28, +my_varchar = '1.175494345e-28'; +INSERT INTO t1 SET my_float = 1.175494345e-27, +my_double = 1.175494345e-27, +my_varchar = '1.175494345e-27'; +INSERT INTO t1 SET my_float = 1.175494345e-26, +my_double = 1.175494345e-26, +my_varchar = '1.175494345e-26'; +INSERT INTO t1 SET my_float = 1.175494345e-25, +my_double = 1.175494345e-25, +my_varchar = '1.175494345e-25'; +INSERT INTO t1 SET my_float = 1.175494345e-24, +my_double = 1.175494345e-24, +my_varchar = '1.175494345e-24'; +INSERT INTO t1 SET my_float = 1.175494345e-23, +my_double = 1.175494345e-23, +my_varchar = '1.175494345e-23'; +INSERT INTO t1 SET my_float = 1.175494345e-22, +my_double = 1.175494345e-22, +my_varchar = '1.175494345e-22'; +INSERT INTO t1 SET my_float = 1.175494345e-21, +my_double = 1.175494345e-21, +my_varchar = '1.175494345e-21'; +INSERT INTO t1 SET my_float = 1.175494345e-20, +my_double = 1.175494345e-20, +my_varchar = '1.175494345e-20'; +INSERT INTO t1 SET my_float = 1.175494345e-19, +my_double = 1.175494345e-19, +my_varchar = '1.175494345e-19'; +INSERT INTO t1 SET my_float = 1.175494345e-18, +my_double = 1.175494345e-18, +my_varchar = '1.175494345e-18'; +INSERT INTO t1 SET my_float = 1.175494345e-17, +my_double = 1.175494345e-17, +my_varchar = '1.175494345e-17'; +INSERT INTO t1 SET my_float = 1.175494345e-16, +my_double = 1.175494345e-16, +my_varchar = '1.175494345e-16'; +INSERT INTO t1 SET my_float = 1.175494345e-15, +my_double = 1.175494345e-15, +my_varchar = '1.175494345e-15'; +INSERT INTO t1 SET my_float = 1.175494345e-14, +my_double = 1.175494345e-14, +my_varchar = '1.175494345e-14'; +INSERT INTO t1 SET my_float = 1.175494345e-13, +my_double = 1.175494345e-13, +my_varchar = '1.175494345e-13'; +INSERT INTO t1 SET my_float = 1.175494345e-12, +my_double = 1.175494345e-12, +my_varchar = '1.175494345e-12'; +INSERT INTO t1 SET my_float = 1.175494345e-11, +my_double = 1.175494345e-11, +my_varchar = '1.175494345e-11'; +INSERT INTO t1 SET my_float = 1.175494345e-10, +my_double = 1.175494345e-10, +my_varchar = '1.175494345e-10'; +INSERT INTO t1 SET my_float = 1.175494345e-9, +my_double = 1.175494345e-9, +my_varchar = '1.175494345e-9'; +INSERT INTO t1 SET my_float = 1.175494345e-8, +my_double = 1.175494345e-8, +my_varchar = '1.175494345e-8'; +INSERT INTO t1 SET my_float = 1.175494345e-7, +my_double = 1.175494345e-7, +my_varchar = '1.175494345e-7'; +INSERT INTO t1 SET my_float = 1.175494345e-6, +my_double = 1.175494345e-6, +my_varchar = '1.175494345e-6'; +INSERT INTO t1 SET my_float = 1.175494345e-5, +my_double = 1.175494345e-5, +my_varchar = '1.175494345e-5'; +INSERT INTO t1 SET my_float = 1.175494345e-4, +my_double = 1.175494345e-4, +my_varchar = '1.175494345e-4'; +INSERT INTO t1 SET my_float = 1.175494345e-3, +my_double = 1.175494345e-3, +my_varchar = '1.175494345e-3'; +INSERT INTO t1 SET my_float = 1.175494345e-2, +my_double = 1.175494345e-2, +my_varchar = '1.175494345e-2'; +INSERT INTO t1 SET my_float = 1.175494345e-1, +my_double = 1.175494345e-1, +my_varchar = '1.175494345e-1'; +SELECT my_float, my_double, my_varchar FROM t1; +my_float my_double my_varchar +1.17549e-32 1.175494345e-32 1.175494345e-32 +1.17549e-31 1.175494345e-31 1.175494345e-31 +1.17549e-30 1.175494345e-30 1.175494345e-30 +1.17549e-29 1.175494345e-29 1.175494345e-29 +1.17549e-28 1.175494345e-28 1.175494345e-28 +1.17549e-27 1.175494345e-27 1.175494345e-27 +1.17549e-26 1.175494345e-26 1.175494345e-26 +1.17549e-25 1.175494345e-25 1.175494345e-25 +1.17549e-24 1.175494345e-24 1.175494345e-24 +1.17549e-23 1.175494345e-23 1.175494345e-23 +1.17549e-22 1.175494345e-22 1.175494345e-22 +1.17549e-21 1.175494345e-21 1.175494345e-21 +1.17549e-20 1.175494345e-20 1.175494345e-20 +1.17549e-19 1.175494345e-19 1.175494345e-19 +1.17549e-18 1.175494345e-18 1.175494345e-18 +1.17549e-17 1.175494345e-17 1.175494345e-17 +1.17549e-16 1.175494345e-16 1.175494345e-16 +1.17549e-15 1.175494345e-15 1.175494345e-15 +1.17549e-14 1.175494345e-14 1.175494345e-14 +1.17549e-13 1.175494345e-13 1.175494345e-13 +1.17549e-12 1.175494345e-12 1.175494345e-12 +1.17549e-11 1.175494345e-11 1.175494345e-11 +1.17549e-10 1.175494345e-10 1.175494345e-10 +1.17549e-09 1.175494345e-09 1.175494345e-9 +1.17549e-08 1.175494345e-08 1.175494345e-8 +1.17549e-07 1.175494345e-07 1.175494345e-7 +1.17549e-06 1.175494345e-06 1.175494345e-6 +1.17549e-05 1.175494345e-05 1.175494345e-5 +0.000117549 0.0001175494345 1.175494345e-4 +0.00117549 0.001175494345 1.175494345e-3 +0.0117549 0.01175494345 1.175494345e-2 +0.117549 0.1175494345 1.175494345e-1 +SELECT CAST(my_float AS DECIMAL(65,30)), my_float FROM t1; +CAST(my_float AS DECIMAL(65,30)) my_float +0.000000000000000000000000000000 1.17549e-32 +0.000000000000000000000000000000 1.17549e-31 +0.000000000000000000000000000001 1.17549e-30 +0.000000000000000000000000000012 1.17549e-29 +0.000000000000000000000000000118 1.17549e-28 +0.000000000000000000000000001175 1.17549e-27 +0.000000000000000000000000011755 1.17549e-26 +0.000000000000000000000000117549 1.17549e-25 +0.000000000000000000000001175494 1.17549e-24 +0.000000000000000000000011754943 1.17549e-23 +0.000000000000000000000117549438 1.17549e-22 +0.000000000000000000001175494332 1.17549e-21 +0.000000000000000000011754943324 1.17549e-20 +0.000000000000000000117549434853 1.17549e-19 +0.000000000000000001175494374380 1.17549e-18 +0.000000000000000011754943743802 1.17549e-17 +0.000000000000000117549432474939 1.17549e-16 +0.000000000000001175494324749389 1.17549e-15 +0.000000000000011754943671010360 1.17549e-14 +0.000000000000117549429933840000 1.17549e-13 +0.000000000001175494380653563000 1.17549e-12 +0.000000000011754943372854760000 1.17549e-11 +0.000000000117549428524377200000 1.17549e-10 +0.000000001175494368510499000000 1.17549e-09 +0.000000011754943685104990000000 1.17549e-08 +0.000000117549433298336200000000 1.17549e-07 +0.000001175494389826781000000000 1.17549e-06 +0.000011754943443520460000000000 1.17549e-05 +0.000117549432616215200000000000 0.000117549 +0.001175494398921728000000000000 0.00117549 +0.011754943057894710000000000000 0.0117549 +0.117549434304237400000000000000 0.117549 +SELECT CAST(my_double AS DECIMAL(65,30)), my_double FROM t1; +CAST(my_double AS DECIMAL(65,30)) my_double +0.000000000000000000000000000000 1.175494345e-32 +0.000000000000000000000000000000 1.175494345e-31 +0.000000000000000000000000000001 1.175494345e-30 +0.000000000000000000000000000012 1.175494345e-29 +0.000000000000000000000000000118 1.175494345e-28 +0.000000000000000000000000001175 1.175494345e-27 +0.000000000000000000000000011755 1.175494345e-26 +0.000000000000000000000000117549 1.175494345e-25 +0.000000000000000000000001175494 1.175494345e-24 +0.000000000000000000000011754943 1.175494345e-23 +0.000000000000000000000117549435 1.175494345e-22 +0.000000000000000000001175494345 1.175494345e-21 +0.000000000000000000011754943450 1.175494345e-20 +0.000000000000000000117549434500 1.175494345e-19 +0.000000000000000001175494345000 1.175494345e-18 +0.000000000000000011754943450000 1.175494345e-17 +0.000000000000000117549434500000 1.175494345e-16 +0.000000000000001175494345000000 1.175494345e-15 +0.000000000000011754943450000000 1.175494345e-14 +0.000000000000117549434500000000 1.175494345e-13 +0.000000000001175494345000000000 1.175494345e-12 +0.000000000011754943450000000000 1.175494345e-11 +0.000000000117549434500000000000 1.175494345e-10 +0.000000001175494345000000000000 1.175494345e-09 +0.000000011754943450000000000000 1.175494345e-08 +0.000000117549434500000000000000 1.175494345e-07 +0.000001175494345000000000000000 1.175494345e-06 +0.000011754943450000000000000000 1.175494345e-05 +0.000117549434500000000000000000 0.0001175494345 +0.001175494345000000000000000000 0.001175494345 +0.011754943450000000000000000000 0.01175494345 +0.117549434500000000000000000000 0.1175494345 +SELECT CAST(my_varchar AS DECIMAL(65,30)), my_varchar FROM t1; +CAST(my_varchar AS DECIMAL(65,30)) my_varchar +0.000000000000000000000000000000 1.175494345e-32 +0.000000000000000000000000000000 1.175494345e-31 +0.000000000000000000000000000001 1.175494345e-30 +0.000000000000000000000000000012 1.175494345e-29 +0.000000000000000000000000000118 1.175494345e-28 +0.000000000000000000000000001175 1.175494345e-27 +0.000000000000000000000000011755 1.175494345e-26 +0.000000000000000000000000117549 1.175494345e-25 +0.000000000000000000000001175494 1.175494345e-24 +0.000000000000000000000011754943 1.175494345e-23 +0.000000000000000000000117549435 1.175494345e-22 +0.000000000000000000001175494345 1.175494345e-21 +0.000000000000000000011754943450 1.175494345e-20 +0.000000000000000000117549434500 1.175494345e-19 +0.000000000000000001175494345000 1.175494345e-18 +0.000000000000000011754943450000 1.175494345e-17 +0.000000000000000117549434500000 1.175494345e-16 +0.000000000000001175494345000000 1.175494345e-15 +0.000000000000011754943450000000 1.175494345e-14 +0.000000000000117549434500000000 1.175494345e-13 +0.000000000001175494345000000000 1.175494345e-12 +0.000000000011754943450000000000 1.175494345e-11 +0.000000000117549434500000000000 1.175494345e-10 +0.000000001175494345000000000000 1.175494345e-9 +0.000000011754943450000000000000 1.175494345e-8 +0.000000117549434500000000000000 1.175494345e-7 +0.000001175494345000000000000000 1.175494345e-6 +0.000011754943450000000000000000 1.175494345e-5 +0.000117549434500000000000000000 1.175494345e-4 +0.001175494345000000000000000000 1.175494345e-3 +0.011754943450000000000000000000 1.175494345e-2 +0.117549434500000000000000000000 1.175494345e-1 +UPDATE t1 SET my_decimal = my_float; +Warnings: +Note 1265 Data truncated for column 'my_decimal' at row 1 +Note 1265 Data truncated for column 'my_decimal' at row 2 +Note 1265 Data truncated for column 'my_decimal' at row 3 +Note 1265 Data truncated for column 'my_decimal' at row 4 +Note 1265 Data truncated for column 'my_decimal' at row 5 +Note 1265 Data truncated for column 'my_decimal' at row 6 +Note 1265 Data truncated for column 'my_decimal' at row 7 +Note 1265 Data truncated for column 'my_decimal' at row 8 +Note 1265 Data truncated for column 'my_decimal' at row 9 +Note 1265 Data truncated for column 'my_decimal' at row 10 +Note 1265 Data truncated for column 'my_decimal' at row 11 +Note 1265 Data truncated for column 'my_decimal' at row 12 +Note 1265 Data truncated for column 'my_decimal' at row 13 +Note 1265 Data truncated for column 'my_decimal' at row 14 +Note 1265 Data truncated for column 'my_decimal' at row 15 +Note 1265 Data truncated for column 'my_decimal' at row 16 +Note 1265 Data truncated for column 'my_decimal' at row 17 +Note 1265 Data truncated for column 'my_decimal' at row 19 +Note 1265 Data truncated for column 'my_decimal' at row 20 +Note 1265 Data truncated for column 'my_decimal' at row 21 +Note 1265 Data truncated for column 'my_decimal' at row 22 +Note 1265 Data truncated for column 'my_decimal' at row 23 +Note 1265 Data truncated for column 'my_decimal' at row 26 +Note 1265 Data truncated for column 'my_decimal' at row 27 +Note 1265 Data truncated for column 'my_decimal' at row 30 +Note 1265 Data truncated for column 'my_decimal' at row 31 +Note 1265 Data truncated for column 'my_decimal' at row 32 +SELECT my_decimal, my_float FROM t1; +my_decimal my_float +0.000000000000000000000000000000 1.17549e-32 +0.000000000000000000000000000000 1.17549e-31 +0.000000000000000000000000000001 1.17549e-30 +0.000000000000000000000000000012 1.17549e-29 +0.000000000000000000000000000118 1.17549e-28 +0.000000000000000000000000001175 1.17549e-27 +0.000000000000000000000000011755 1.17549e-26 +0.000000000000000000000000117549 1.17549e-25 +0.000000000000000000000001175494 1.17549e-24 +0.000000000000000000000011754943 1.17549e-23 +0.000000000000000000000117549438 1.17549e-22 +0.000000000000000000001175494332 1.17549e-21 +0.000000000000000000011754943324 1.17549e-20 +0.000000000000000000117549434853 1.17549e-19 +0.000000000000000001175494374380 1.17549e-18 +0.000000000000000011754943743802 1.17549e-17 +0.000000000000000117549432474939 1.17549e-16 +0.000000000000001175494324749389 1.17549e-15 +0.000000000000011754943671010360 1.17549e-14 +0.000000000000117549429933840000 1.17549e-13 +0.000000000001175494380653563000 1.17549e-12 +0.000000000011754943372854760000 1.17549e-11 +0.000000000117549428524377200000 1.17549e-10 +0.000000001175494368510499000000 1.17549e-09 +0.000000011754943685104990000000 1.17549e-08 +0.000000117549433298336200000000 1.17549e-07 +0.000001175494389826781000000000 1.17549e-06 +0.000011754943443520460000000000 1.17549e-05 +0.000117549432616215200000000000 0.000117549 +0.001175494398921728000000000000 0.00117549 +0.011754943057894710000000000000 0.0117549 +0.117549434304237400000000000000 0.117549 +UPDATE t1 SET my_decimal = my_double; +Warnings: +Note 1265 Data truncated for column 'my_decimal' at row 1 +Note 1265 Data truncated for column 'my_decimal' at row 2 +Note 1265 Data truncated for column 'my_decimal' at row 3 +Note 1265 Data truncated for column 'my_decimal' at row 4 +Note 1265 Data truncated for column 'my_decimal' at row 5 +Note 1265 Data truncated for column 'my_decimal' at row 6 +Note 1265 Data truncated for column 'my_decimal' at row 7 +Note 1265 Data truncated for column 'my_decimal' at row 8 +Note 1265 Data truncated for column 'my_decimal' at row 9 +Note 1265 Data truncated for column 'my_decimal' at row 10 +Note 1265 Data truncated for column 'my_decimal' at row 11 +Note 1265 Data truncated for column 'my_decimal' at row 13 +Note 1265 Data truncated for column 'my_decimal' at row 14 +Note 1265 Data truncated for column 'my_decimal' at row 16 +Note 1265 Data truncated for column 'my_decimal' at row 18 +Note 1265 Data truncated for column 'my_decimal' at row 20 +Note 1265 Data truncated for column 'my_decimal' at row 31 +SELECT my_decimal, my_double FROM t1; +my_decimal my_double +0.000000000000000000000000000000 1.175494345e-32 +0.000000000000000000000000000000 1.175494345e-31 +0.000000000000000000000000000001 1.175494345e-30 +0.000000000000000000000000000012 1.175494345e-29 +0.000000000000000000000000000118 1.175494345e-28 +0.000000000000000000000000001175 1.175494345e-27 +0.000000000000000000000000011755 1.175494345e-26 +0.000000000000000000000000117549 1.175494345e-25 +0.000000000000000000000001175494 1.175494345e-24 +0.000000000000000000000011754943 1.175494345e-23 +0.000000000000000000000117549435 1.175494345e-22 +0.000000000000000000001175494345 1.175494345e-21 +0.000000000000000000011754943450 1.175494345e-20 +0.000000000000000000117549434500 1.175494345e-19 +0.000000000000000001175494345000 1.175494345e-18 +0.000000000000000011754943450000 1.175494345e-17 +0.000000000000000117549434500000 1.175494345e-16 +0.000000000000001175494345000000 1.175494345e-15 +0.000000000000011754943450000000 1.175494345e-14 +0.000000000000117549434500000000 1.175494345e-13 +0.000000000001175494345000000000 1.175494345e-12 +0.000000000011754943450000000000 1.175494345e-11 +0.000000000117549434500000000000 1.175494345e-10 +0.000000001175494345000000000000 1.175494345e-09 +0.000000011754943450000000000000 1.175494345e-08 +0.000000117549434500000000000000 1.175494345e-07 +0.000001175494345000000000000000 1.175494345e-06 +0.000011754943450000000000000000 1.175494345e-05 +0.000117549434500000000000000000 0.0001175494345 +0.001175494345000000000000000000 0.001175494345 +0.011754943450000000000000000000 0.01175494345 +0.117549434500000000000000000000 0.1175494345 +UPDATE t1 SET my_decimal = my_varchar; +Warnings: +Note 1265 Data truncated for column 'my_decimal' at row 1 +Note 1265 Data truncated for column 'my_decimal' at row 2 +Note 1265 Data truncated for column 'my_decimal' at row 3 +Note 1265 Data truncated for column 'my_decimal' at row 4 +Note 1265 Data truncated for column 'my_decimal' at row 5 +Note 1265 Data truncated for column 'my_decimal' at row 6 +Note 1265 Data truncated for column 'my_decimal' at row 7 +Note 1265 Data truncated for column 'my_decimal' at row 8 +Note 1265 Data truncated for column 'my_decimal' at row 9 +Note 1265 Data truncated for column 'my_decimal' at row 10 +Note 1265 Data truncated for column 'my_decimal' at row 11 +SELECT my_decimal, my_varchar FROM t1; +my_decimal my_varchar +0.000000000000000000000000000000 1.175494345e-32 +0.000000000000000000000000000000 1.175494345e-31 +0.000000000000000000000000000001 1.175494345e-30 +0.000000000000000000000000000012 1.175494345e-29 +0.000000000000000000000000000118 1.175494345e-28 +0.000000000000000000000000001175 1.175494345e-27 +0.000000000000000000000000011755 1.175494345e-26 +0.000000000000000000000000117549 1.175494345e-25 +0.000000000000000000000001175494 1.175494345e-24 +0.000000000000000000000011754943 1.175494345e-23 +0.000000000000000000000117549435 1.175494345e-22 +0.000000000000000000001175494345 1.175494345e-21 +0.000000000000000000011754943450 1.175494345e-20 +0.000000000000000000117549434500 1.175494345e-19 +0.000000000000000001175494345000 1.175494345e-18 +0.000000000000000011754943450000 1.175494345e-17 +0.000000000000000117549434500000 1.175494345e-16 +0.000000000000001175494345000000 1.175494345e-15 +0.000000000000011754943450000000 1.175494345e-14 +0.000000000000117549434500000000 1.175494345e-13 +0.000000000001175494345000000000 1.175494345e-12 +0.000000000011754943450000000000 1.175494345e-11 +0.000000000117549434500000000000 1.175494345e-10 +0.000000001175494345000000000000 1.175494345e-9 +0.000000011754943450000000000000 1.175494345e-8 +0.000000117549434500000000000000 1.175494345e-7 +0.000001175494345000000000000000 1.175494345e-6 +0.000011754943450000000000000000 1.175494345e-5 +0.000117549434500000000000000000 1.175494345e-4 +0.001175494345000000000000000000 1.175494345e-3 +0.011754943450000000000000000000 1.175494345e-2 +0.117549434500000000000000000000 1.175494345e-1 +DROP TABLE t1; create table t1 (c1 decimal(64)); insert into t1 values( 89000000000000000000000000000000000000000000000000000000000000000000000000000000000000000); diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index a74feb4de17..97df059c86a 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1,4 +1,4 @@ -drop table if exists t1,t2,t9,`t1a``b`,v1,v2,v3,v4,v5,v6; +drop table if exists t1,t2,t3,t4,t9,`t1a``b`,v1,v2,v3,v4,v5,v6; drop view if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6; drop database if exists mysqltest; use test; @@ -2199,10 +2199,10 @@ r_object_id users_names drop view v1, v2; drop table t1, t2; create definer=some_user@`` sql security invoker view v1 as select 1; -ERROR HY000: View definer is not fully qualified +ERROR HY000: Definer is not fully qualified create definer=some_user@localhost sql security invoker view v1 as select 1; Warnings: -Note 1449 There is not some_user@localhost registered +Note 1449 There is no 'some_user'@'localhost' registered show create view v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` @@ -2335,3 +2335,53 @@ f1 group_concat(f2 order by f2 desc) 1 3,2,1 drop view v1,v2; drop table t1; +create table t1 (x int, y int); +create table t2 (x int, y int, z int); +create table t3 (x int, y int, z int); +create table t4 (x int, y int, z int); +create view v1 as +select t1.x +from ( +(t1 join t2 on ((t1.y = t2.y))) +join +(t3 left join t4 on (t3.y = t4.y) and (t3.z = t4.z)) +); +prepare stmt1 from "select count(*) from v1 where x = ?"; +set @parm1=1; +execute stmt1 using @parm1; +count(*) +0 +execute stmt1 using @parm1; +count(*) +0 +drop view v1; +drop table t1,t2,t3,t4; +CREATE TABLE t1(id INT); +CREATE VIEW v1 AS SELECT id FROM t1; +OPTIMIZE TABLE v1; +Table Op Msg_type Msg_text +test.v1 optimize note Unknown table 'test.v1' +ANALYZE TABLE v1; +Table Op Msg_type Msg_text +test.v1 analyze note Unknown table 'test.v1' +REPAIR TABLE v1; +Table Op Msg_type Msg_text +test.v1 repair note Unknown table 'test.v1' +DROP TABLE t1; +OPTIMIZE TABLE v1; +Table Op Msg_type Msg_text +test.v1 optimize note Unknown table 'test.v1' +Warnings: +Error 1146 Table 'test.t1' doesn't exist +Error 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +DROP VIEW v1; +create definer = current_user() sql security invoker view v1 as select 1; +show create view v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` +drop view v1; +create definer = current_user sql security invoker view v1 as select 1; +show create view v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1` +drop view v1; diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 43df5c29f92..89067ec23a2 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -13,7 +13,7 @@ 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 HY000: You need the SUPER privilege for creation view with root@localhost definer +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' diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index d9c1abd9ba9..7871b3647e3 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -2,7 +2,7 @@ # Initialize --disable_warnings -drop table if exists t1; +drop table if exists t1, t2; --enable_warnings # diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 67ad3058153..470a7bcbb59 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -3,7 +3,7 @@ # --disable_warnings -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,t5; drop database if exists mysqltest; --enable_warnings @@ -554,4 +554,80 @@ create table t1 ( show create table t1; drop table t1; +# +# BUG#14480: assert failure in CREATE ... SELECT because of wrong +# calculation of number of NULLs. +# +CREATE TABLE t2 ( + a int(11) default NULL +); +insert into t2 values(111); + +--warning 1364 +create table t1 ( + a varchar(12) charset utf8 collate utf8_bin not null, + b int not null, primary key (a) +) select a, 1 as b from t2 ; +show create table t1; +drop table t1; + +--warning 1364 +create table t1 ( + a varchar(12) charset utf8 collate utf8_bin not null, + b int not null, primary key (a) +) select 'a' as a , 1 as b from t2 ; +show create table t1; +drop table t1; + +--warning 1364 +create table t1 ( + a varchar(12) charset utf8 collate utf8_bin, + b int not null, primary key (a) +) select 'a' as a , 1 as b from t2 ; +show create table t1; +drop table t1, t2; + +create table t1 ( + a1 int not null, + a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int +); +insert into t1 values (1,1,1, 1,1,1, 1,1,1); + +--warning 1364 +create table t2 ( + a1 varchar(12) charset utf8 collate utf8_bin not null, + a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, + primary key (a1) +) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ; +drop table t2; + +--warning 1364 +create table t2 ( + a1 varchar(12) charset utf8 collate utf8_bin, + a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int +) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1; + +drop table t1, t2; +--warning 1364 +create table t1 ( + a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int +); +insert into t1 values (1,1,1, 1,1,1, 1,1,1); + +--warning 1364 +create table t2 ( + a1 varchar(12) charset utf8 collate utf8_bin not null, + a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, + primary key (a1) +) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ; + +# Test the default value +drop table t2; + +create table t2 ( a int default 3, b int default 3) + select a1,a2 from t1; +show create table t2; + +drop table t1, t2; + # End of 4.1 tests diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test index 2ac46d75f9a..5b693335a43 100644 --- a/mysql-test/t/csv.test +++ b/mysql-test/t/csv.test @@ -1314,4 +1314,41 @@ select period from t1; drop table if exists t1,t2,t3,t4; +# +# Bug #13894 Server crashes on update of CSV table +# + +--disable_warnings +DROP TABLE IF EXISTS bug13894; +--enable_warnings + +CREATE TABLE bug13894 ( val integer ) ENGINE = CSV; +INSERT INTO bug13894 VALUES (5); +INSERT INTO bug13894 VALUES (10); +INSERT INTO bug13894 VALUES (11); +INSERT INTO bug13894 VALUES (10); +SELECT * FROM bug13894; +UPDATE bug13894 SET val=6 WHERE val=10; +SELECT * FROM bug13894; +DROP TABLE bug13894; + +# +# Bug #14672 Bug in deletion +# + +--disable_warnings +DROP TABLE IF EXISTS bug14672; +--enable_warnings + +CREATE TABLE bug14672 (c1 integer) engine = CSV; +INSERT INTO bug14672 VALUES (1), (2), (3); +SELECT * FROM bug14672; +DELETE FROM bug14672 WHERE c1 = 2; +SELECT * FROM bug14672; +INSERT INTO bug14672 VALUES (4); +SELECT * FROM bug14672; +INSERT INTO bug14672 VALUES (5); +SELECT * FROM bug14672; +DROP TABLE bug14672; + # End of 4.1 tests diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 8a28e8bbf02..1cd55926e80 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -379,6 +379,8 @@ where table_schema='test'; select index_name from information_schema.statistics where table_schema='test'; select constraint_name from information_schema.table_constraints where table_schema='test'; +show create view v2; +show create table v3; drop view v2; drop view v3; drop table t4; diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index 0592ec3152f..9dbf153ec55 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -832,3 +832,71 @@ SELECT v2.x FROM v2 JOIN t2 ON e=b JOIN t3 ON e=c JOIN t4 USING(d); DROP VIEW v1, v2; DROP TABLE t1, t2, t3, t4, t5, t6; + +# +# BUG#13126 -test case from bug report +# +create table t1 (id1 int(11) not null); +insert into t1 values (1),(2); + +create table t2 (id2 int(11) not null); +insert into t2 values (1),(2),(3),(4); + +create table t3 (id3 char(16) not null); +insert into t3 values ('100'); + +create table t4 (id2 int(11) not null, id3 char(16)); + +create table t5 (id1 int(11) not null, key (id1)); +insert into t5 values (1),(2),(1); + +create view v1 as + select t4.id3 from t4 join t2 on t4.id2 = t2.id2; + +select t1.id1 from t1 inner join (t3 left join v1 on t3.id3 = v1.id3); + +drop view v1; +drop table t1, t2, t3, t4, t5; + +create table t0 (a int); +insert into t0 values (0),(1),(2),(3); +create table t1(a int); +insert into t1 select A.a + 10*(B.a) from t0 A, t0 B; + +create table t2 (a int, b int); +insert into t2 values (1,1), (2,2), (3,3); + +create table t3(a int, b int, filler char(200), key(a)); +insert into t3 select a,a,'filler' from t1; +insert into t3 select a,a,'filler' from t1; + +create table t4 like t3; +insert into t4 select * from t3; +insert into t4 select * from t3; + +create table t5 like t4; +insert into t5 select * from t4; +insert into t5 select * from t4; + +create table t6 like t5; +insert into t6 select * from t5; +insert into t6 select * from t5; + +create table t7 like t6; +insert into t7 select * from t6; +insert into t7 select * from t6; + +--replace_column 9 X +explain select * from t4 join + t2 left join (t3 join t5 on t5.a=t3.b) on t3.a=t2.b where t4.a<=>t3.b; + +--replace_column 9 X +explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b + join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b; + +--replace_column 9 X +explain select * from t2 left join + (t3 left join (t4 join t6 on t6.a=t4.b) on t4.a=t3.b + join t5 on t5.a=t3.b) on t3.a=t2.b; + +drop table t0, t1, t2, t4, t5, t6; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index f15b4d54275..d510ef66677 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -648,6 +648,24 @@ analyze table t1; show index from t1; set myisam_stats_method=DEFAULT; +drop table t1; + +# BUG#13814 - key value packed incorrectly for TINYBLOBs + +create table t1( + cip INT NOT NULL, + time TIME NOT NULL, + score INT NOT NULL DEFAULT 0, + bob TINYBLOB +); + +insert into t1 (cip, time) VALUES (1, '00:01'), (2, '00:02'), (3,'00:03'); +insert into t1 (cip, bob, time) VALUES (4, 'a', '00:04'), (5, 'b', '00:05'), + (6, 'c', '00:06'); +select * from t1 where bob is null and cip=1; +create index bug on t1 (bob(22), cip, time); +select * from t1 where bob is null and cip=1; +drop table t1; # End of 4.1 tests # diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 91a87e2c774..83085823729 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -658,19 +658,19 @@ select '------ Testing with illegal table names ------' as test_sequence ; --exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "t/1" 2>&1 --error 6 ---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T_1" +--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T_1" 2>&1 --error 6 ---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T%1" +--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T%1" 2>&1 --error 6 ---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T'1" +--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T'1" 2>&1 --error 6 ---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T_1" +--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T_1" 2>&1 --error 6 ---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T_" +--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T_" 2>&1 --disable_query_log select '------ Testing with illegal database names ------' as test_sequence ; @@ -882,6 +882,7 @@ DROP FUNCTION IF EXISTS bug9056_func1; DROP FUNCTION IF EXISTS bug9056_func2; DROP PROCEDURE IF EXISTS bug9056_proc1; DROP PROCEDURE IF EXISTS bug9056_proc2; +DROP PROCEDURE IF EXISTS `a'b`; --enable_warnings CREATE TABLE t1 (id int); diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test new file mode 100644 index 00000000000..0861951a6a1 --- /dev/null +++ b/mysql-test/t/read_only.test @@ -0,0 +1,103 @@ +# Test of the READ_ONLY global variable: +# check that it blocks updates unless they are only on temporary tables. + +--disable_warnings +DROP TABLE IF EXISTS t1,t2,t3; +--enable_warnings + +# READ_ONLY does nothing to SUPER users +# so we use a non-SUPER one: + +grant CREATE, SELECT, DROP on *.* to test@localhost; + +connect (con1,localhost,test,,test); + +connection default; + +set global read_only=0; + +connection con1; + +create table t1 (a int); + +insert into t1 values(1); + +create table t2 select * from t1; + +connection default; + +set global read_only=1; + +# We check that SUPER can: + +create table t3 (a int); +drop table t3; + +connection con1; + +select @@global.read_only; + +--error 1290 +create table t3 (a int); + +--error 1290 +insert into t1 values(1); + +# if a statement, after parse stage, looks like it will update a +# non-temp table, it will be rejected, even if at execution it would +# have turned out that 0 rows would be updated +--error 1290 +update t1 set a=1 where 1=0; + +# multi-update is special (see sql_parse.cc) so we test it +--error 1290 +update t1,t2 set t1.a=t2.a+1 where t1.a=t2.a; + +# check multi-delete to be sure +--error 1290 +delete t1,t2 from t1,t2 where t1.a=t2.a; + +# With temp tables updates should be accepted: + +create temporary table t3 (a int); + +create temporary table t4 (a int) select * from t3; + +insert into t3 values(1); + +insert into t4 select * from t3; + +# a non-temp table updated: +--error 1290 +update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a; + +# no non-temp table updated (just swapped): +update t1,t3 set t3.a=t1.a+1 where t1.a=t3.a; + +update t4,t3 set t4.a=t3.a+1 where t4.a=t3.a; + +--error 1290 +delete t1 from t1,t3 where t1.a=t3.a; + +delete t3 from t1,t3 where t1.a=t3.a; + +delete t4 from t3,t4 where t4.a=t3.a; + +# and even homonymous ones + +create temporary table t1 (a int); + +insert into t1 values(1); + +update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a; + +delete t1 from t1,t3 where t1.a=t3.a; + +drop table t1; + +--error 1290 +insert into t1 values(1); + +connection default; +drop table t1,t2; +drop user test@localhost; diff --git a/mysql-test/t/rpl_drop_db.test b/mysql-test/t/rpl_drop_db.test index 61354198c83..98afc6e3d02 100644 --- a/mysql-test/t/rpl_drop_db.test +++ b/mysql-test/t/rpl_drop_db.test @@ -13,6 +13,7 @@ insert into mysqltest1.t1 values (1); select * from mysqltest1.t1 into outfile 'mysqltest1/f1.txt'; create table mysqltest1.t2 (n int); create table mysqltest1.t3 (n int); +--replace_result \\ / --error 1010 drop database mysqltest1; use mysqltest1; @@ -29,6 +30,7 @@ while ($1) } --enable_query_log +--replace_result \\ / --error 1010 drop database mysqltest1; use mysqltest1; diff --git a/mysql-test/t/rpl_sp-slave.opt b/mysql-test/t/rpl_sp-slave.opt index 709a224fd92..611ee1f33be 100644 --- a/mysql-test/t/rpl_sp-slave.opt +++ b/mysql-test/t/rpl_sp-slave.opt @@ -1 +1 @@ ---log_bin_trust_routine_creators=0 +--log_bin_trust_routine_creators=0 --slave-skip-errors=1062 diff --git a/mysql-test/t/rpl_sp.test b/mysql-test/t/rpl_sp.test index e62a6c73c0a..e7a3afca9cb 100644 --- a/mysql-test/t/rpl_sp.test +++ b/mysql-test/t/rpl_sp.test @@ -1,10 +1,18 @@ # Test of replication of stored procedures (WL#2146 for MySQL 5.0) +# Modified by WL#2971. + +# Note that in the .opt files we still use the old variable name +# log-bin-trust-routine-creators so that this test checks that it's +# still accepted (this test also checks that the new name is +# accepted). The old name could be removed in 5.1 or 6.0. source include/master-slave.inc; -# First let's test replication of current_user() (that's a related thing) # we need a db != test, where we don't have automatic grants -create database if not exists mysqltest1; +--disable_warnings +drop database if exists mysqltest1; +--enable_warnings +create database mysqltest1; use mysqltest1; create table t1 (a varchar(100)); sync_slave_with_master; @@ -16,30 +24,15 @@ use mysqltest1; # (same definer, same properties...) connection master; -# cleanup ---disable_warnings -drop procedure if exists foo; -drop procedure if exists foo2; -drop procedure if exists foo3; -drop procedure if exists foo4; -drop procedure if exists bar; -drop function if exists fn1; ---enable_warnings delimiter |; ---error 1418 # not deterministic -create procedure foo() -begin - declare b int; - set b = 8; - insert into t1 values (b); - insert into t1 values (unix_timestamp()); -end| ---replace_column 2 # 5 # -show binlog events from 98| # check that not there +# Stored procedures don't have the limitations that functions have +# regarding binlogging: it's ok to create a procedure as not +# deterministic and updating data, while it's not ok to create such a +# function. We test this. -create procedure foo() deterministic +create procedure foo() begin declare b int; set b = 8; @@ -54,38 +47,29 @@ delimiter ;| --replace_column 13 # 14 # select * from mysql.proc where name='foo' and db='mysqltest1'; sync_slave_with_master; +# You will notice in the result that the definer does not match what +# it is on master, it is a known bug on which Alik is working --replace_result localhost.localdomain localhost 127.0.0.1 localhost --replace_column 13 # 14 # select * from mysql.proc where name='foo' and db='mysqltest1'; -# Now when we call it, does the CALL() get into binlog, -# or the substatements? connection master; # see if timestamp used in SP on slave is same as on master set timestamp=1000000000; call foo(); ---replace_column 2 # 5 # -show binlog events from 308; select * from t1; sync_slave_with_master; select * from t1; -# Now a SP which is supposed to not update tables (CALL should not be -# binlogged) as it's "read sql data", so should not give error even if -# non-deterministic. +# Now a SP which is not updating tables connection master; delete from t1; create procedure foo2() - not deterministic - reads sql data select * from mysqltest1.t1; call foo2(); -# verify CALL is not in binlog ---replace_column 2 # 5 # -show binlog events from 518; ---error 1418 +# check that this is allowed (it's not for functions): alter procedure foo2 contains sql; # SP with definer's right @@ -106,15 +90,7 @@ grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1; connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,); connection con1; ---error 1419 # only full-global-privs user can create a routine -create procedure foo4() - deterministic - insert into t1 values (10); - -connection master; -set global log_bin_trust_routine_creators=1; -connection con1; - +# this routine will fail in the second INSERT because of privileges delimiter |; create procedure foo4() deterministic @@ -128,29 +104,22 @@ delimiter ;| # I add ,0 so that it does not print the error in the test output, # because this error is hostname-dependent --error 1142,0 -call foo4(); # invoker has no INSERT grant on table => failure -show warnings; +call foo4(); # invoker has no INSERT grant on table t1 => failure connection master; call foo3(); # success (definer == root) show warnings; ---replace_result localhost.localdomain localhost 127.0.0.1 localhost --error 1142,0 call foo4(); # definer's rights => failure -show warnings; # we test replication of ALTER PROCEDURE alter procedure foo4 sql security invoker; call foo4(); # invoker's rights => success show warnings; -# Check that only successful CALLs are in binlog ---replace_column 2 # 5 # -show binlog events from 990; - -# Note that half-failed CALLs are not in binlog, which is a known -# bug. If we compare t2 on master and slave we see they differ: +# Note that half-failed procedure calls are ok with binlogging; +# if we compare t2 on master and slave we see they are identical: select * from t1; select * from t2; @@ -158,6 +127,30 @@ sync_slave_with_master; select * from t1; select * from t2; +# Let's check another failing-in-the-middle procedure +connection master; +delete from t2; +alter table t2 add unique (a); + +drop procedure foo4; +delimiter |; +create procedure foo4() + deterministic + begin + insert into t2 values(20),(20); + end| + +delimiter ;| + +--error 1062 +call foo4(); +show warnings; + +select * from t2; +sync_slave_with_master; +# check that this failed-in-the-middle replicated right: +select * from t2; + # Test of DROP PROCEDURE --replace_result localhost.localdomain localhost 127.0.0.1 localhost @@ -177,6 +170,14 @@ drop procedure foo2; drop procedure foo3; delimiter |; +# check that needs "deterministic" +--error 1418 +create function fn1(x int) + returns int +begin + insert into t1 values (x); + return x+2; +end| create function fn1(x int) returns int deterministic @@ -202,15 +203,69 @@ drop function fn1; create function fn1() returns int - deterministic + no sql begin return unix_timestamp(); end| + delimiter ;| +# check that needs "deterministic" +--error 1418 +alter function fn1 contains sql; + delete from t1; set timestamp=1000000000; insert into t1 values(fn1()); +connection con1; + +delimiter |; +--error 1419 # only full-global-privs user can create a function +create function fn2() + returns int + no sql +begin + return unix_timestamp(); +end| +delimiter ;| +connection master; +# test old variable name: +set global log_bin_trust_routine_creators=1; +# now use new name: +set global log_bin_trust_function_creators=0; +set global log_bin_trust_function_creators=1; +# slave needs it too otherwise will not execute what master allowed: +connection slave; +set global log_bin_trust_function_creators=1; + +connection con1; + +delimiter |; +create function fn2() + returns int + no sql +begin + return unix_timestamp(); +end| +delimiter ;| + +connection master; + +# Now a function which is supposed to not update tables +# as it's "reads sql data", so should not give error even if +# non-deterministic. + +delimiter |; +create function fn3() + returns int + not deterministic + reads sql data +begin + return 0; +end| +delimiter ;| + +select fn3(); --replace_result localhost.localdomain localhost 127.0.0.1 localhost --replace_column 13 # 14 # select * from mysql.proc where db='mysqltest1'; @@ -223,18 +278,43 @@ select * from t1; --replace_column 13 # 14 # select * from mysql.proc where db='mysqltest1'; -# And now triggers +# Let's check a failing-in-the-middle function +connection master; +delete from t2; +alter table t2 add unique (a); + +drop function fn1; + +delimiter |; +create function fn1() + returns int +begin + insert into t2 values(20),(20); + return 10; +end| + +delimiter ;| + +# Because of BUG#14769 the following statement requires that we start +# slave with --slave-skip-errors=1062. When that bug is fixed, that +# option can be removed. + +--error 1062 +select fn1(); + +select * from t2; +sync_slave_with_master; + +# check that this failed-in-the-middle replicated right: +select * from t2; + +# ********************** PART 3 : TRIGGERS *************** connection con1; --error 1227 create trigger trg before insert on t1 for each row set new.a= 10; connection master; -# fn1() above uses timestamps, so in !ps-protocol, the timezone will be -# binlogged, but in --ps-protocol it will not be (BUG#9359) so -# the binlog offsets get shifted which spoils SHOW BINLOG EVENTS. -# To be immune, we take a new binlog. -flush logs; delete from t1; # TODO: when triggers can contain an update, test that this update # does not go into binlog. @@ -253,7 +333,7 @@ drop trigger trg; insert into t1 values (1); select * from t1; --replace_column 2 # 5 # -show binlog events in 'master-bin.000002' from 98; +show binlog events in 'master-bin.000001' from 98; sync_slave_with_master; select * from t1; diff --git a/mysql-test/t/rpl_trigger.test b/mysql-test/t/rpl_trigger.test index 715222f0314..bf2836b6049 100644 --- a/mysql-test/t/rpl_trigger.test +++ b/mysql-test/t/rpl_trigger.test @@ -87,12 +87,35 @@ insert into t1 set a = now(); select a=b && a=c from t1; let $time=`select a from t1`; +# Check that definer attribute is replicated properly: +# - dump definers on the master; +# - wait for the slave to synchronize with the master; +# - dump definers on the slave; + +SELECT routine_name, definer +FROM information_schema.routines; + +SELECT trigger_name, definer +FROM information_schema.triggers; + save_master_pos; connection slave; sync_with_master; --disable_query_log select "--- On slave --" as ""; --enable_query_log + +# XXX: Definers of stored procedures and functions are not replicated. WL#2897 +# (Complete definer support in the stored routines) addresses this issue. So, +# the result file is expected to be changed after implementation of this WL +# item. + +SELECT routine_name, definer +FROM information_schema.routines; + +SELECT trigger_name, definer +FROM information_schema.triggers; + select a=b && a=c from t1; --disable_query_log eval select a='$time' as 'test' from t1; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index e7e6d899a66..33a3ff578f8 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2729,3 +2729,23 @@ select * from t1 where f2 >= '2005-09-3a'; select * from t1 where f2 <= '2005-09-31'; select * from t1 where f2 <= '2005-09-3a'; drop table t1; + +# +# Bug ##14662 ORDER BY on column of a view, with an alias of the same +# column causes ambiguous +# + +create table t1 (f1 int, f2 int); +insert into t1 values (1, 30), (2, 20), (3, 10); +create algorithm=merge view v1 as select f1, f2 from t1; +create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1; +create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1; +select t1.f1 as x1, f1 from t1 order by t1.f1; +select v1.f1 as x1, f1 from v1 order by v1.f1; +select v2.f1 as x1, f1 from v2 order by v2.f1; +select v3.f1 as x1, f1 from v3 order by v3.f1; +select f1, f2, v1.f1 as x1 from v1 order by v1.f1; +select f1, f2, v2.f1 as x1 from v2 order by v2.f1; +select f1, f2, v3.f1 as x1 from v3 order by v3.f1; +drop table t1; +drop view v1, v2, v3; diff --git a/mysql-test/t/skip_grants.test b/mysql-test/t/skip_grants.test index 7a729f98661..16b0fbc4d25 100644 --- a/mysql-test/t/skip_grants.test +++ b/mysql-test/t/skip_grants.test @@ -9,7 +9,7 @@ use test; # test that we can create VIEW if privileges check switched off # create table t1 (field1 INT); --- error ER_NO_VIEW_USER +-- error ER_MALFORMED_DEFINER CREATE VIEW v1 AS SELECT field1 FROM t1; drop table t1; diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index e2343cd905c..3c1efe73c18 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1337,6 +1337,22 @@ DROP PROCEDURE bug13037_p1; DROP PROCEDURE bug13037_p2; DROP PROCEDURE bug13037_p3; +# +# Bug#14569 "editing a stored procedure kills mysqld-nt" +# +create database mysqltest1; +create database mysqltest2; +use mysqltest1; +drop database mysqltest1; +create procedure mysqltest2.p1() select version(); +--error ER_NO_DB_ERROR +create procedure p2() select version(); +use mysqltest2; +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show procedure status; +drop database mysqltest2; +use test; + # BUG#NNNN: New bug synopsis # diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index a07cc93ad68..4bfc4d17588 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -183,3 +183,57 @@ group by country; drop table t1; +# +# BUG#14342: wrong placement of subquery internals in complex queries +# +CREATE TABLE `t1` ( + `t3_id` int NOT NULL, + `t1_id` int NOT NULL, + PRIMARY KEY (`t1_id`) +); +CREATE TABLE `t2` ( + `t2_id` int NOT NULL, + `t1_id` int NOT NULL, + `b` int NOT NULL, + PRIMARY KEY (`t2_id`), + UNIQUE KEY `idx_t2_t1_b` (`t1_id`,`b`) +) ENGINE=InnoDB; +CREATE TABLE `t3` ( + `t3_id` int NOT NULL +); +INSERT INTO `t3` VALUES (3); +select + (SELECT rs.t2_id + FROM t2 rs + WHERE rs.t1_id= + (SELECT lt.t1_id + FROM t1 lt + WHERE lt.t3_id=a.t3_id) + ORDER BY b DESC LIMIT 1) +from t3 AS a; +# repeat above query in SP +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings +delimiter //; +create procedure p1() +begin + declare done int default 3; + repeat + select + (SELECT rs.t2_id + FROM t2 rs + WHERE rs.t1_id= + (SELECT lt.t1_id + FROM t1 lt + WHERE lt.t3_id=a.t3_id) + ORDER BY b DESC LIMIT 1) as x + from t3 AS a; + set done= done-1; + until done <= 0 end repeat; +end// +delimiter ;// +call p1(); +call p1(); +call p1(); +drop tables t1,t2,t3; diff --git a/mysql-test/t/trigger-compat.test b/mysql-test/t/trigger-compat.test new file mode 100644 index 00000000000..ace18639172 --- /dev/null +++ b/mysql-test/t/trigger-compat.test @@ -0,0 +1,83 @@ +# Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not +# supported in embedded server. So, this test should not be run on embedded +# server. + +-- source include/not_embedded.inc + +########################################################################### +# +# Tests for WL#2818: +# - Check that triggers created w/o DEFINER information work well: +# - create the first trigger; +# - manually remove definer information from corresponding TRG file; +# - create the second trigger (the first trigger will be reloaded; check +# that we receive a warning); +# - check that the triggers loaded correctly; +# +########################################################################### + +# +# Prepare environment. +# + +DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; +FLUSH PRIVILEGES; + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest_db1; +--enable_warnings + +CREATE DATABASE mysqltest_db1; + +CREATE USER mysqltest_dfn@localhost; +CREATE USER mysqltest_inv@localhost; + +GRANT SUPER ON *.* TO mysqltest_dfn@localhost; +GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; + +# +# Create a table and the first trigger. +# + +--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) +--connection wl2818_definer_con +--echo +--echo ---> connection: wl2818_definer_con + +CREATE TABLE t1(num_value INT); +CREATE TABLE t2(user_str TEXT); + +CREATE TRIGGER wl2818_trg1 BEFORE INSERT ON t1 + FOR EACH ROW + INSERT INTO t2 VALUES(CURRENT_USER()); + +# +# Remove definers from TRG file. +# + +--echo +--echo ---> patching t1.TRG... + +--exec grep --text -v 'definers=' $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG > $MYSQL_TEST_DIR/var/tmp/t1.TRG +--exec mv $MYSQL_TEST_DIR/var/tmp/t1.TRG $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG + +# +# Create a new trigger. +# + +--echo + +CREATE TRIGGER wl2818_trg2 AFTER INSERT ON t1 + FOR EACH ROW + INSERT INTO t2 VALUES(CURRENT_USER()); + +--echo + +SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; + +--echo + +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; diff --git a/mysql-test/t/trigger-grant.test b/mysql-test/t/trigger-grant.test new file mode 100644 index 00000000000..c058816ee75 --- /dev/null +++ b/mysql-test/t/trigger-grant.test @@ -0,0 +1,475 @@ +# Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not +# supported in embedded server. So, this test should not be run on embedded +# server. + +-- source include/not_embedded.inc + +########################################################################### +# +# Tests for WL#2818: +# - Check that triggers are executed under the authorization of the definer. +# - Check that if trigger contains NEW/OLD variables, the definer must have +# SELECT privilege on the subject table. +# - Check DEFINER clause of CREATE TRIGGER statement; +# - Check that SUPER privilege required to create a trigger with different +# definer. +# - Check that if the user specified as DEFINER does not exist, a warning +# is emitted. +# - Check that the definer of a trigger does not exist, the trigger will +# not be activated. +# - Check that SHOW TRIGGERS statement provides "Definer" column. +# +# Let's also check that user name part of definer can contain '@' symbol (to +# check that triggers are not affected by BUG#13310 "incorrect user parsing +# by SP"). +# +########################################################################### + +# +# Prepare environment. +# + +DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; +DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; +FLUSH PRIVILEGES; + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest_db1; +--enable_warnings + +CREATE DATABASE mysqltest_db1; + +CREATE USER mysqltest_dfn@localhost; +CREATE USER mysqltest_inv@localhost; + +GRANT SUPER ON *.* TO mysqltest_dfn@localhost; +GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; + +# +# Check that triggers are executed under the authorization of the definer: +# - create two tables under "definer"; +# - grant all privileges on the test db to "definer"; +# - grant all privileges on the first table to "invoker"; +# - grant only select privilege on the second table to "invoker"; +# - create a trigger, which inserts a row into the second table after +# inserting into the first table. +# - insert a row into the first table under "invoker". A row also should be +# inserted into the second table. +# + +--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) +--connection wl2818_definer_con +--echo +--echo ---> connection: wl2818_definer_con + +CREATE TABLE t1(num_value INT); +CREATE TABLE t2(user_str TEXT); + +CREATE TRIGGER trg1 AFTER INSERT ON t1 + FOR EACH ROW + INSERT INTO t2 VALUES(CURRENT_USER()); + +--connection default +--echo +--echo ---> connection: default + +# Setup definer's privileges. + +GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; +GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; + +# Setup invoker's privileges. + +GRANT ALL PRIVILEGES ON mysqltest_db1.t1 + TO 'mysqltest_inv'@localhost; + +GRANT SELECT ON mysqltest_db1.t2 + TO 'mysqltest_inv'@localhost; + +--connection wl2818_definer_con +--echo +--echo ---> connection: wl2818_definer_con + +use mysqltest_db1; + +INSERT INTO t1 VALUES(1); + +SELECT * FROM t1; +SELECT * FROM t2; + +--connect (wl2818_invoker_con,localhost,mysqltest_inv,,mysqltest_db1) +--connection wl2818_invoker_con +--echo +--echo ---> connection: wl2818_invoker_con + +use mysqltest_db1; + +INSERT INTO t1 VALUES(2); + +SELECT * FROM t1; +SELECT * FROM t2; + +# +# Check that if definer lost some privilege required to execute (activate) a +# trigger, the trigger will not be activated: +# - create a trigger on insert into the first table, which will insert a row +# into the second table; +# - revoke INSERT privilege on the second table from the definer; +# - insert a row into the first table; +# - check that an error has been risen; +# - check that no row has been inserted into the second table; +# + +--connection default +--echo +--echo ---> connection: default + +use mysqltest_db1; + +REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost; + +--connection wl2818_invoker_con +--echo +--echo ---> connection: wl2818_invoker_con + +use mysqltest_db1; + +--error ER_TABLEACCESS_DENIED_ERROR +INSERT INTO t1 VALUES(3); + +SELECT * FROM t1; +SELECT * FROM t2; + +# +# Check that if trigger contains NEW/OLD variables, the definer must have +# SELECT/UPDATE privilege on the subject table: +# - drop the trigger; +# - create a new trigger, which will use NEW variable; +# - create another new trigger, which will use OLD variable; +# - revoke SELECT/UPDATE privilege on the first table from "definer"; +# - insert a row into the first table; +# - analyze error code; +# + +# +# SELECT privilege. +# + +--connection default +--echo +--echo ---> connection: default + +use mysqltest_db1; + +REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; + +--connection wl2818_definer_con +--echo +--echo ---> connection: wl2818_definer_con + +use mysqltest_db1; + +DROP TRIGGER trg1; + +SET @new_sum = 0; +SET @old_sum = 0; + +# INSERT INTO statement; BEFORE timing + +--echo ---> INSERT INTO statement; BEFORE timing + +CREATE TRIGGER trg1 BEFORE INSERT ON t1 + FOR EACH ROW + SET @new_sum = @new_sum + NEW.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +INSERT INTO t1 VALUES(4); + +# INSERT INTO statement; AFTER timing + +--echo ---> INSERT INTO statement; AFTER timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 AFTER INSERT ON t1 + FOR EACH ROW + SET @new_sum = @new_sum + NEW.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +INSERT INTO t1 VALUES(5); + +# UPDATE statement; BEFORE timing + +--echo ---> UPDATE statement; BEFORE timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 BEFORE UPDATE ON t1 + FOR EACH ROW + SET @old_sum = @old_sum + OLD.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +UPDATE t1 SET num_value = 10; + +# UPDATE statement; AFTER timing + +--echo ---> UPDATE statement; AFTER timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 AFTER UPDATE ON t1 + FOR EACH ROW + SET @new_sum = @new_sum + NEW.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +UPDATE t1 SET num_value = 20; + +# DELETE statement; BEFORE timing + +--echo ---> DELETE statement; BEFORE timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 BEFORE DELETE ON t1 + FOR EACH ROW + SET @old_sum = @old_sum + OLD.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +DELETE FROM t1; + +# DELETE statement; AFTER timing + +--echo ---> DELETE statement; AFTER timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 AFTER DELETE ON t1 + FOR EACH ROW + SET @old_sum = @old_sum + OLD.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +DELETE FROM t1; + +# +# UPDATE privilege +# +# NOTE: At the moment, UPDATE privilege is required if the trigger contains +# NEW/OLD variables, whenever the trigger modifies them or not. Moreover, +# UPDATE privilege is checked for whole table, not for individual columns. +# +# The following test cases should be changed when full support of UPDATE +# privilege will be done. +# + +--connection default +--echo +--echo ---> connection: default + +use mysqltest_db1; + +GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; +REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; + +--connection wl2818_definer_con +--echo +--echo ---> connection: wl2818_definer_con + +use mysqltest_db1; + +DROP TRIGGER trg1; + +SET @new_sum = 0; +SET @old_sum = 0; + +# INSERT INTO statement; BEFORE timing + +--echo ---> INSERT INTO statement; BEFORE timing + +CREATE TRIGGER trg1 BEFORE INSERT ON t1 + FOR EACH ROW + SET @new_sum = @new_sum + NEW.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +INSERT INTO t1 VALUES(4); + +# INSERT INTO statement; AFTER timing + +--echo ---> INSERT INTO statement; AFTER timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 AFTER INSERT ON t1 + FOR EACH ROW + SET @new_sum = @new_sum + NEW.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +INSERT INTO t1 VALUES(5); + +# UPDATE statement; BEFORE timing + +--echo ---> UPDATE statement; BEFORE timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 BEFORE UPDATE ON t1 + FOR EACH ROW + SET @old_sum = @old_sum + OLD.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +UPDATE t1 SET num_value = 10; + +# UPDATE statement; AFTER timing + +--echo ---> UPDATE statement; AFTER timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 AFTER UPDATE ON t1 + FOR EACH ROW + SET @new_sum = @new_sum + NEW.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +UPDATE t1 SET num_value = 20; + +# DELETE statement; BEFORE timing + +--echo ---> DELETE statement; BEFORE timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 BEFORE DELETE ON t1 + FOR EACH ROW + SET @old_sum = @old_sum + OLD.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +DELETE FROM t1; + +# DELETE statement; AFTER timing + +--echo ---> DELETE statement; AFTER timing + +DROP TRIGGER trg1; + +CREATE TRIGGER trg1 AFTER DELETE ON t1 + FOR EACH ROW + SET @old_sum = @old_sum + OLD.num_value; + +--error ER_TABLEACCESS_DENIED_ERROR +DELETE FROM t1; + +# +# Check DEFINER clause of CREATE TRIGGER statement. +# +# NOTE: there is no dedicated TRIGGER privilege for CREATE TRIGGER statement. +# SUPER privilege is used instead. I.e., if one invokes CREATE TRIGGER, it should +# have SUPER privilege, so this test is meaningless right now. +# +# - Check that SUPER privilege required to create a trigger with different +# definer: +# - try to create a trigger with DEFINER="definer@localhost" under +# "invoker"; +# - analyze error code; +# - Check that if the user specified as DEFINER does not exist, a warning is +# emitted: +# - create a trigger with DEFINER="non_existent_user@localhost" from +# "definer"; +# - check that a warning emitted; +# - Check that the definer of a trigger does not exist, the trigger will not +# be activated: +# - activate just created trigger; +# - check error code; +# + +--connection wl2818_definer_con +--echo +--echo ---> connection: wl2818_definer_con + +use mysqltest_db1; + +DROP TRIGGER trg1; + +# Check that SUPER is required to specify different DEFINER. +# NOTE: meaningless at the moment + +CREATE DEFINER='mysqltest_inv'@'localhost' + TRIGGER trg1 BEFORE INSERT ON t1 + FOR EACH ROW + SET @new_sum = 0; + +# Create with non-existent user. + +CREATE DEFINER='mysqltest_nonexs'@'localhost' + TRIGGER trg2 AFTER INSERT ON t1 + FOR EACH ROW + SET @new_sum = 0; + +# Check that trg2 will not be activated. + +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +INSERT INTO t1 VALUES(6); + +# +# Check that SHOW TRIGGERS statement provides "Definer" column. +# + +SHOW TRIGGERS; + +# +# Check that weird definer values do not break functionality. I.e. check the +# following definer values: +# - ''; +# - '@'; +# - '@abc@def@@'; +# - '@hostname'; +# - '@abc@def@@@hostname'; +# + +DROP TRIGGER trg1; +DROP TRIGGER trg2; + +CREATE TRIGGER trg1 BEFORE INSERT ON t1 + FOR EACH ROW + SET @a = 1; + +CREATE TRIGGER trg2 AFTER INSERT ON t1 + FOR EACH ROW + SET @a = 2; + +CREATE TRIGGER trg3 BEFORE UPDATE ON t1 + FOR EACH ROW + SET @a = 3; + +CREATE TRIGGER trg4 AFTER UPDATE ON t1 + FOR EACH ROW + SET @a = 4; + +CREATE TRIGGER trg5 BEFORE DELETE ON t1 + FOR EACH ROW + SET @a = 5; + +--exec egrep --text -v '^definers=' $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG > $MYSQL_TEST_DIR/var/tmp/t1.TRG +--exec echo "definers='' '@' '@abc@def@@' '@hostname' '@abcdef@@@hostname'" >> $MYSQL_TEST_DIR/var/tmp/t1.TRG +--exec mv $MYSQL_TEST_DIR/var/tmp/t1.TRG $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG + +--echo + +SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; + +--echo + +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; + +# +# Cleanup +# + +--connection default +--echo +--echo ---> connection: default + +DROP USER mysqltest_dfn@localhost; +DROP USER mysqltest_inv@localhost; + +DROP DATABASE mysqltest_db1; diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index 93a1a1afa45..ad71ffa02e5 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1052,6 +1052,41 @@ select cast(@non_existing_user_var/2 as DECIMAL); create table t (d decimal(0,10)); # +# Bug #14268 (bad FLOAT->DECIMAL conversion) +# + +CREATE TABLE t1 ( + my_float FLOAT, + my_double DOUBLE, + my_varchar VARCHAR(50), + my_decimal DECIMAL(65,30) +); +SHOW CREATE TABLE t1; + +let $max_power= 32; +while ($max_power) +{ + eval INSERT INTO t1 SET my_float = 1.175494345e-$max_power, + my_double = 1.175494345e-$max_power, + my_varchar = '1.175494345e-$max_power'; + dec $max_power; +} +SELECT my_float, my_double, my_varchar FROM t1; + +SELECT CAST(my_float AS DECIMAL(65,30)), my_float FROM t1; +SELECT CAST(my_double AS DECIMAL(65,30)), my_double FROM t1; +SELECT CAST(my_varchar AS DECIMAL(65,30)), my_varchar FROM t1; + +UPDATE t1 SET my_decimal = my_float; +SELECT my_decimal, my_float FROM t1; +UPDATE t1 SET my_decimal = my_double; +SELECT my_decimal, my_double FROM t1; +UPDATE t1 SET my_decimal = my_varchar; +SELECT my_decimal, my_varchar FROM t1; + +DROP TABLE t1; + +# # Bug #13573 (Wrong data inserted for too big values) # diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 4338d0f8f9a..b0acb1d18f6 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -1,5 +1,5 @@ --disable_warnings -drop table if exists t1,t2,t9,`t1a``b`,v1,v2,v3,v4,v5,v6; +drop table if exists t1,t2,t3,t4,t9,`t1a``b`,v1,v2,v3,v4,v5,v6; drop view if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6; drop database if exists mysqltest; --enable_warnings @@ -2085,7 +2085,7 @@ drop table t1, t2; # # DEFINER information check # --- error ER_NO_VIEW_USER +-- error ER_MALFORMED_DEFINER create definer=some_user@`` sql security invoker view v1 as select 1; create definer=some_user@localhost sql security invoker view v1 as select 1; show create view v1; @@ -2204,3 +2204,56 @@ select * from v1; select * from v2; drop view v1,v2; drop table t1; + +# +# BUG#14026 Crash on second PS execution when using views +# +create table t1 (x int, y int); +create table t2 (x int, y int, z int); +create table t3 (x int, y int, z int); +create table t4 (x int, y int, z int); + +create view v1 as +select t1.x +from ( + (t1 join t2 on ((t1.y = t2.y))) + join + (t3 left join t4 on (t3.y = t4.y) and (t3.z = t4.z)) +); + +prepare stmt1 from "select count(*) from v1 where x = ?"; +set @parm1=1; + +execute stmt1 using @parm1; +execute stmt1 using @parm1; +drop view v1; +drop table t1,t2,t3,t4; + +# +# Bug #14540: OPTIMIZE, ANALYZE, REPAIR applied to not a view +# + +CREATE TABLE t1(id INT); +CREATE VIEW v1 AS SELECT id FROM t1; + +OPTIMIZE TABLE v1; +ANALYZE TABLE v1; +REPAIR TABLE v1; + +DROP TABLE t1; +OPTIMIZE TABLE v1; + +DROP VIEW v1; + + +# +# BUG#14719: Views DEFINER grammar is incorrect +# + +create definer = current_user() sql security invoker view v1 as select 1; +show create view v1; +drop view v1; + +create definer = current_user sql security invoker view v1 as select 1; +show create view v1; +drop view v1; diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 7f0cb6d9406..b4f367c2065 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -24,7 +24,7 @@ grant create view,select on test.* to mysqltest_1@localhost; connect (user1,localhost,mysqltest_1,,test); connection user1; --- error ER_VIEW_OTHER_USER +-- error ER_SPECIFIC_ACCESS_DENIED 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 |