diff options
Diffstat (limited to 'mysql-test/t')
108 files changed, 6245 insertions, 293 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index bae94656125..f4245abfe86 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -340,7 +340,9 @@ drop table t1; # # BUG 12207 alter table ... discard table space on MyISAM table causes ERROR 2013 (HY000) # +# Some platforms (Mac OS X, Windows) will send the error message using small letters. CREATE TABLE T12207(a int) ENGINE=MYISAM; +--replace_result t12207 T12207 --error 1031 ALTER TABLE T12207 DISCARD TABLESPACE; DROP TABLE T12207; @@ -401,3 +403,12 @@ use test; drop table t1; # End of 4.1 tests + +# +# Bug #14693 (ALTER SET DEFAULT doesn't work) +# + +create table t1 (mycol int(10) not null); +alter table t1 alter column mycol set default 0; +desc t1; +drop table t1; diff --git a/mysql-test/t/analyse.test b/mysql-test/t/analyse.test index 4060892c389..a248c671c18 100644 --- a/mysql-test/t/analyse.test +++ b/mysql-test/t/analyse.test @@ -65,5 +65,30 @@ insert into t1 values (100000); select * from t1 procedure analyse (1,1); drop table t1; +# +# Bug #14138 ROLLUP and PROCEDURE ANALYSE() hang server +# +create table t1 (product varchar(32), country_id int not null, year int, + profit int); +insert into t1 values ( 'Computer', 2,2000, 1200), + ( 'TV', 1, 1999, 150), + ( 'Calculator', 1, 1999,50), + ( 'Computer', 1, 1999,1500), + ( 'Computer', 1, 2000,1500), + ( 'TV', 1, 2000, 150), + ( 'TV', 2, 2000, 100), + ( 'TV', 2, 2000, 100), + ( 'Calculator', 1, 2000,75), + ( 'Calculator', 2, 2000,75), + ( 'TV', 1, 1999, 100), + ( 'Computer', 1, 1999,1200), + ( 'Computer', 2, 2000,1500), + ( 'Calculator', 2, 2000,75), + ( 'Phone', 3, 2003,10) + ; +create table t2 (country_id int primary key, country char(20) not null); +insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland'); +select product, sum(profit),avg(profit) from t1 group by product with rollup procedure analyse(); +drop table t1,t2; # End of 4.1 tests diff --git a/mysql-test/t/analyze.test b/mysql-test/t/analyze.test index 3c3b3933bc3..656ff752c9b 100644 --- a/mysql-test/t/analyze.test +++ b/mysql-test/t/analyze.test @@ -39,4 +39,27 @@ check table t1; drop table t1; +# Bug #14902 ANALYZE TABLE fails to recognize up-to-date tables +# minimal test case to get an error. +# The problem is happening when analysing table with FT index that +# contains stopwords only. The first execution of analyze table should +# mark index statistics as up to date so that next execution of this +# statement will end up with Table is up to date status. +create table t1 (a mediumtext, fulltext key key1(a)) charset utf8 collate utf8_general_ci engine myisam; +insert into t1 values ('hello'); + +analyze table t1; +analyze table t1; + +drop table t1; + +# +# procedure in PS BUG#13673 +# +CREATE TABLE t1 (a int); +prepare stmt1 from "SELECT * FROM t1 PROCEDURE ANALYSE()"; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + # End of 4.1 tests diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index ac2061eeb0b..ba2ad3ed8aa 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1347,6 +1347,11 @@ SELECT * FROM t2; # Just test syntax, we will never know if the output is right or wrong # Must be the last test INSERT DELAYED INTO t2 VALUES (4,011403,37,'intercepted','audiology','tinily',''); + +# Adding test for alter table +ALTER TABLE t2 DROP COLUMN fld6; +SHOW CREATE TABLE t2; +SELECT * from t2; # # Cleanup, test is over # diff --git a/mysql-test/t/archive_gis.test b/mysql-test/t/archive_gis.test new file mode 100644 index 00000000000..ffbad923173 --- /dev/null +++ b/mysql-test/t/archive_gis.test @@ -0,0 +1,3 @@ +--source include/have_archive.inc +SET storage_engine=archive; +--source include/gis_generic.inc diff --git a/mysql-test/t/backup.test b/mysql-test/t/backup.test index 3034129ad4b..40a9fa73b60 100644 --- a/mysql-test/t/backup.test +++ b/mysql-test/t/backup.test @@ -52,5 +52,6 @@ unlock tables; connection con1; reap; drop table t5; +--system rm $MYSQL_TEST_DIR/var/tmp/t?.* # End of 4.1 tests diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test index bf72a4555b8..d017d91bfb1 100644 --- a/mysql-test/t/bdb.test +++ b/mysql-test/t/bdb.test @@ -938,7 +938,25 @@ SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera"); SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd"); DROP TABLE t1; -# End of 4.1 tests +# +# Bug #15536: Crash when DELETE with subquery using BDB tables +# +create table t1 (a int, key(a)) engine=bdb; +create table t2 (b int, key(b)) engine=bdb; +insert into t1 values (1),(1),(2),(3),(4); +insert into t2 values (1),(5),(6),(7); +delete from t1 where (a in (select b from t2)); +select count(*) from t1; +# INSERT also blows up +--error 1242 +insert into t1 set a=(select b from t2); +select count(*) from t1; +# UPDATE also blows up +update t1 set a = a + 1 where (a in (select b from t2)); +select count(*) from t1; +drop table t1, t2; + +--echo End of 4.1 tests # # alter temp table @@ -962,6 +980,7 @@ source include/varchar.inc; # Some errors/warnings on create # +--replace_result 1024 MAX_KEY_LENGTH 3072 MAX_KEY_LENGTH create table t1 (v varchar(65530), key(v)); drop table if exists t1; create table t1 (v varchar(65536)); @@ -975,6 +994,23 @@ drop table t1; eval set storage_engine=$default; # +# Test that we can create a large key +# +create table t1 (a varchar(255) character set utf8, + b varchar(255) character set utf8, + c varchar(255) character set utf8, + d varchar(255) character set utf8, + key (a,b,c,d)) engine=bdb; +drop table t1; +--error ER_TOO_LONG_KEY +create table t1 (a varchar(255) character set utf8, + b varchar(255) character set utf8, + c varchar(255) character set utf8, + d varchar(255) character set utf8, + e varchar(255) character set utf8, + key (a,b,c,d,e)) engine=bdb; + +# # Bug #14212: Server crash after COMMIT + ALTER TABLE # set autocommit=0; @@ -983,4 +1019,4 @@ commit; alter table t1 add primary key(a); drop table t1; -# End of 5.0 tests +--echo End of 5.0 tests diff --git a/mysql-test/t/bdb_gis.test b/mysql-test/t/bdb_gis.test new file mode 100644 index 00000000000..88dcbb7cbe9 --- /dev/null +++ b/mysql-test/t/bdb_gis.test @@ -0,0 +1,3 @@ +-- source include/have_bdb.inc +SET storage_engine=bdb; +--source include/gis_generic.inc 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/cast.test b/mysql-test/t/cast.test index 70eb87131df..dc7f695e38e 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -64,7 +64,7 @@ select cast(_latin1'a ' AS char) as c2, cast(_latin1'abc' AS char(2)) as c3, cast(_latin1'a ' AS char(2)) as c4, - cast(_latin1'a' AS char(2)) as c5; + hex(cast(_latin1'a' AS char(2))) as c5; select cast(1000 as CHAR(3)); create table t1 select @@ -73,7 +73,7 @@ create table t1 select cast(_latin1'abc' AS char(2)) as c3, cast(_latin1'a ' AS char(2)) as c4, cast(_latin1'a' AS char(2)) as c5; -select * from t1; +select c1,c2,c3,c4,hex(c5) from t1; show create table t1; drop table t1; diff --git a/mysql-test/t/compress.test b/mysql-test/t/compress.test new file mode 100644 index 00000000000..46244edd2a8 --- /dev/null +++ b/mysql-test/t/compress.test @@ -0,0 +1,15 @@ +# Turn on compression between the client and server +# and run a number of tests + +-- source include/have_compress.inc + +connect (comp_con,localhost,root,,,,,COMPRESS); + +# Check compression turned on +SHOW STATUS LIKE 'Compression'; + +# Source select test case +-- source include/common-tests.inc + +# Check compression turned on +SHOW STATUS LIKE 'Compression'; diff --git a/mysql-test/t/connect.test b/mysql-test/t/connect.test index 60ac7b88bbe..fef9d4552e6 100644 --- a/mysql-test/t/connect.test +++ b/mysql-test/t/connect.test @@ -1,7 +1,6 @@ # This test is to check various cases of connections -# with right and wrong password, with and without database -# Unfortunately the check is incomplete as we can't handle errors on connect -# Also we can't connect without database +# with right and wrong password, with and without database +# Unfortunately the check is incomplete as we can't connect without database # This test makes no sense with the embedded server --source include/not_embedded.inc @@ -10,69 +9,72 @@ drop table if exists t1,t2; --enable_warnings + #connect (con1,localhost,root,,""); #show tables; connect (con1,localhost,root,,mysql); show tables; -connect (con1,localhost,root,,test); +connect (con2,localhost,root,,test); show tables; -# Re enable this one day if error handling on connect will take place - -#connect (con1,localhost,root,z,test2); -#--error 1045 -#connect (con1,localhost,root,z,); -#--error 1045 +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error 1045 +connect (fail_con,localhost,root,z,test2); +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error 1045 +connect (fail_con,localhost,root,z,); grant ALL on *.* to test@localhost identified by "gambling"; grant ALL on *.* to test@127.0.0.1 identified by "gambling"; # Now check this user with different databases - #connect (con1,localhost,test,gambling,""); #show tables; -connect (con1,localhost,test,gambling,mysql); +connect (con3,localhost,test,gambling,mysql); show tables; -connect (con1,localhost,test,gambling,test); +connect (con4,localhost,test,gambling,test); show tables; -# Re enable this one day if error handling on connect will take place - -#connect (con1,localhost,test,,test2); -#--error 1045 -#connect (con1,localhost,test,,""); -#--error 1045 -#connect (con1,localhost,test,zorro,test2); -#--error 1045 -#connect (con1,localhost,test,zorro,); -#--error 1045 +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error 1045 +connect (fail_con,localhost,test,,test2); +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error 1045 +connect (fail_con,localhost,test,,""); +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error 1045 +connect (fail_con,localhost,test,zorro,test2); +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error 1045 +connect (fail_con,localhost,test,zorro,); # check if old password version also works update mysql.user set password=old_password("gambling2") where user=_binary"test"; flush privileges; -#connect (con1,localhost,test,gambling2,""); -#show tables; -connect (con1,localhost,test,gambling2,mysql); +connect (con10,localhost,test,gambling2,); +connect (con5,localhost,test,gambling2,mysql); set password=""; --error 1372 set password='gambling3'; set password=old_password('gambling3'); show tables; -connect (con1,localhost,test,gambling3,test); +connect (con6,localhost,test,gambling3,test); show tables; -# Re enable this one day if error handling on connect will take place - -#connect (con1,localhost,test,,test2); -#--error 1045 -#connect (con1,localhost,test,,); -#--error 1045 -#connect (con1,localhost,test,zorro,test2); -#--error 1045 -#connect (con1,localhost,test,zorro,); -#--error 1045 +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error 1045 +connect (fail_con,localhost,test,,test2); +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error 1045 +connect (fail_con,localhost,test,,); +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error 1045 +connect (fail_con,localhost,test,zorro,test2); +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error 1045 +connect (fail_con,localhost,test,zorro,); # remove user 'test' so that other tests which may use 'test' @@ -84,13 +86,13 @@ flush privileges; # # Bug#12517: Clear user variables and replication events before # closing temp tables in thread cleanup. -connect (con2,localhost,root,,test); -connection con2; +connect (con7,localhost,root,,test); +connection con7; create table t1 (id integer not null auto_increment primary key); create temporary table t2(id integer not null auto_increment primary key); set @id := 1; delete from t1 where id like @id; -disconnect con2; +disconnect con7; --sleep 5 connection default; drop table t1; diff --git a/mysql-test/t/count_distinct3.test b/mysql-test/t/count_distinct3.test index 52a4f271dac..f817b2c635d 100644 --- a/mysql-test/t/count_distinct3.test +++ b/mysql-test/t/count_distinct3.test @@ -17,7 +17,7 @@ while ($1) SET @rnd= RAND(); SET @id = CAST(@rnd * @rnd_max AS UNSIGNED); SET @id_rev= @rnd_max - @id; - SET @grp= CAST(128.0 * @rnd AS UNSIGNED); + SET @grp= CAST(127.0 * @rnd AS UNSIGNED); INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); dec $1; } diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index b72dc49e89a..162db3d0c0a 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 @@ -218,13 +218,6 @@ create table if not exists t1 select 1,2,3,4; create table if not exists t1 select 1; select * from t1; drop table t1; -create table t1 select 1,2,3; -create table if not exists t1 select 1,2; ---error 1136 -create table if not exists t1 select 1,2,3,4; -create table if not exists t1 select 1; -select * from t1; -drop table t1; # # Test create table if not exists with duplicate key error @@ -545,4 +538,119 @@ create table t1 (a int); create table if not exists t1 (a int); drop table t1; +# BUG#14139 +create table t1 ( + a varchar(112) charset utf8 collate utf8_bin not null, + primary key (a) +) select 'test' as a ; +--warning 1364 +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, 1 as c 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 null, primary key (a) +) select a, 1 as c 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 + +# +# Bug #14155: Maximum value of MAX_ROWS handled incorrectly on 64-bit +# platforms +# +create table t1 (i int) engine=myisam max_rows=100000000000; +show create table t1; +alter table t1 max_rows=100; +show create table t1; +alter table t1 max_rows=100000000000; +show create table t1; +drop table t1; + +# End of 5.0 tests diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test index 2ac46d75f9a..a028f6ced6d 100644 --- a/mysql-test/t/csv.test +++ b/mysql-test/t/csv.test @@ -1314,4 +1314,73 @@ 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 + +# +# BUG#13406 - incorrect amount of "records deleted" +# + +create table t1 (a int) engine=csv; +insert t1 values (1); +--enable_info +delete from t1; -- delete_row +delete from t1; -- delete_all_rows +--disable_info +insert t1 values (1),(2); +--enable_info +delete from t1; -- delete_all_rows +--disable_info +insert t1 values (1),(2),(3); +flush tables; +--enable_info +delete from t1; -- delete_row +--disable_info +insert t1 values (1),(2),(3),(4); +flush tables; +select count(*) from t1; +--enable_info +delete from t1; -- delete_all_rows +--disable_info +insert t1 values (1),(2),(3),(4),(5); +--enable_info +truncate table t1; -- truncate +--disable_info +drop table t1; + diff --git a/mysql-test/t/ctype_cp1250_ch.test b/mysql-test/t/ctype_cp1250_ch.test index ea4b35a44a3..2d1e5f0bf9d 100644 --- a/mysql-test/t/ctype_cp1250_ch.test +++ b/mysql-test/t/ctype_cp1250_ch.test @@ -23,4 +23,25 @@ SELECT * FROM t1 WHERE popisek = '2005-01-1'; SELECT * FROM t1 WHERE popisek LIKE '2005-01-1'; drop table t1; +# +# Bug#13347: empty result from query with like and cp1250 charset +# +set names cp1250; +CREATE TABLE t1 +( + id INT AUTO_INCREMENT PRIMARY KEY, + str VARCHAR(32) CHARACTER SET cp1250 COLLATE cp1250_czech_cs NOT NULL default '', + UNIQUE KEY (str) +); + +INSERT INTO t1 VALUES (NULL, 'a'); +INSERT INTO t1 VALUES (NULL, 'aa'); +INSERT INTO t1 VALUES (NULL, 'aaa'); +INSERT INTO t1 VALUES (NULL, 'aaaa'); +INSERT INTO t1 VALUES (NULL, 'aaaaa'); +INSERT INTO t1 VALUES (NULL, 'aaaaaa'); +INSERT INTO t1 VALUES (NULL, 'aaaaaaa'); +select * from t1 where str like 'aa%'; +drop table t1; + # End of 4.1 tests diff --git a/mysql-test/t/ctype_cp932_binlog.test b/mysql-test/t/ctype_cp932_binlog.test new file mode 100644 index 00000000000..270e27cf27f --- /dev/null +++ b/mysql-test/t/ctype_cp932_binlog.test @@ -0,0 +1,34 @@ +-- source include/not_embedded.inc +-- source include/have_cp932.inc + +--character_set cp932 +--disable_warnings +drop table if exists t1; +--enable_warnings + +set names cp932; +set character_set_database = cp932; + +# Test prepared statement with 0x8300 sequence in parameter while +# running with cp932 client character set. +RESET MASTER; +CREATE TABLE t1(f1 blob); +PREPARE stmt1 FROM 'INSERT INTO t1 VALUES(?)'; +SET @var1= x'8300'; +# TODO: Note that this doesn't actually test the code which was added for +# bug#11338 because this syntax for prepared statements causes the PS to +# be replicated differently than if we executed the PS from C or Java. +# Using this syntax, variable names are inserted into the binlog instead +# of values. The real goal of this test is to check the code that was +# added to Item_param::query_val_str() in order to do hex encoding of +# PS parameters when the client character set is cp932; +# Bug#11338 has an example java program which can be used to verify this +# code (and I have used it to test the fix) until there is some way to +# exercise this code from mysql-test-run. +EXECUTE stmt1 USING @var1; +SHOW BINLOG EVENTS FROM 98; +SELECT HEX(f1) FROM t1; +DROP table t1; +# end test for bug#11338 + +# End of 4.1 tests diff --git a/mysql-test/t/ctype_recoding.test b/mysql-test/t/ctype_recoding.test index 9949ef88da4..5648cea7fd3 100644 --- a/mysql-test/t/ctype_recoding.test +++ b/mysql-test/t/ctype_recoding.test @@ -144,8 +144,7 @@ create table t1 (a char(10) character set cp1251); insert into t1 values (_koi8r'×ÁÓÑ'); # this is possible: select * from t1 where a=_koi8r'×ÁÓÑ'; -# this is not possible, because we have a function, not just a constant: ---error 1267 +# this is possible, because we have a function with constant arguments: select * from t1 where a=concat(_koi8r'×ÁÓÑ'); # this is not posible, cannot convert _latin1'×ÁÓÑ' into cp1251: --error 1267 @@ -154,6 +153,14 @@ drop table t1; set names latin1; # +# Bug#10446 Illegal mix of collations +# +create table t1 (a char(10) character set utf8 collate utf8_bin); +insert into t1 values (' xxx'); +select * from t1 where a=lpad('xxx',10,' '); +drop table t1; + +# # Check more automatic conversion # set names koi8r; diff --git a/mysql-test/t/ctype_uca.test b/mysql-test/t/ctype_uca.test index 6d8713f4910..3e49b9de883 100644 --- a/mysql-test/t/ctype_uca.test +++ b/mysql-test/t/ctype_uca.test @@ -212,6 +212,7 @@ select group_concat(c1 order by c1) from t1 group by c1 collate utf8_slovak_ci; select group_concat(c1 order by c1) from t1 group by c1 collate utf8_spanish2_ci; select group_concat(c1 order by c1) from t1 group by c1 collate utf8_roman_ci; select group_concat(c1 order by c1) from t1 group by c1 collate utf8_esperanto_ci; +select group_concat(c1 order by c1) from t1 group by c1 collate utf8_hungarian_ci; drop table t1; diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index 626c7e0e1b6..04de13f8228 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -421,6 +421,14 @@ insert into t1 values (0x005b); select hex(a) from t1; drop table t1; +# +# Bug #14583 Bug on query using a LIKE on indexed field with ucs2_bin collation +# +create table t1(f1 varchar(5) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL) engine=InnoDB; +insert into t1 values('a'); +create index t1f1 on t1(f1); +select f1 from t1 where f1 like 'a%'; +drop table t1; # End of 4.1 tests # diff --git a/mysql-test/t/ctype_ujis.test b/mysql-test/t/ctype_ujis.test index c1d6c67387b..77d250b5c45 100644 --- a/mysql-test/t/ctype_ujis.test +++ b/mysql-test/t/ctype_ujis.test @@ -1170,7 +1170,7 @@ INSERT INTO t1 VALUES(_ujis 0xA4A2); DELIMITER |; CREATE PROCEDURE sp1() BEGIN - DECLARE a CHAR(1); + DECLARE a CHAR(2) CHARSET ujis; DECLARE cur1 CURSOR FOR SELECT c1 FROM t1; OPEN cur1; FETCH cur1 INTO a; diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 5670e9efbf9..a96564f4e76 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -884,7 +884,9 @@ SELECT DISTINCT id FROM t1 ORDER BY id; DROP TABLE t1; # -# Bugs#10504: Character set does not support traditional mode +# Bug#10504: Character set does not support traditional mode +# Bug#14146: CHAR(...USING ...) and CONVERT(CHAR(...) USING...) +# produce different results # set names utf8; # correct value @@ -894,12 +896,14 @@ select char(0xd18f using utf8); select char(53647 using utf8); # incorrect value: return with warning select char(0xff,0x8f using utf8); +select convert(char(0xff,0x8f) using utf8); # incorrect value in strict mode: return NULL with "Error" level warning set sql_mode=traditional; select char(0xff,0x8f using utf8); select char(195 using utf8); select char(196 using utf8); select char(2557 using utf8); +select convert(char(0xff,0x8f) using utf8); # # Check convert + char + using diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test index f76f51fd12d..c007c2f5205 100644 --- a/mysql-test/t/date_formats.test +++ b/mysql-test/t/date_formats.test @@ -261,4 +261,12 @@ select str_to_date("2003-04-05 g", "%Y-%m-%d") as f1, str_to_date("2003-04-05 10:11:12.101010234567", "%Y-%m-%d %H:%i:%S.%f") as f2; --enable_ps_protocol +# +# Bug #14016 +# +create table t1 (f1 datetime); +insert into t1 (f1) values ("2005-01-01"); +insert into t1 (f1) values ("2005-02-01"); +select date_format(f1, "%m") as d1, date_format(f1, "%M") as d2 from t1 order by date_format(f1, "%M"); +drop table t1; # End of 4.1 tests diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index bc81f716ec7..a209088f202 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -11,8 +11,5 @@ ############################################################################## sp-goto : GOTO is currently is disabled - will be fixed in the future -rpl_relayrotate : Unstable test case, bug#12429 -rpl_until : Unstable test case, bug#12429 -rpl_deadlock : Unstable test case, bug#12429 -kill : Unstable test case, bug#9712 -federated : Broken test case, bug#14272 +subselect : Bug#15706 +type_time : Bug#15805 diff --git a/mysql-test/t/drop_temp_table.test b/mysql-test/t/drop_temp_table.test index 38c13e3e5e4..bc06de4096c 100644 --- a/mysql-test/t/drop_temp_table.test +++ b/mysql-test/t/drop_temp_table.test @@ -1,6 +1,10 @@ # Embedded server doesn't support binlog -- source include/not_embedded.inc +--disable_warnings +drop database if exists `drop-temp+table-test`; +--enable_warnings + connect (con1,localhost,root,,); connect (con2,localhost,root,,); connection con1; diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index 9880cd78653..b6b3b90c083 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -1188,5 +1188,40 @@ connection slave; DROP TABLE federated.normal_table; # END ALTER TEST +# +# Test BUG #14532 - bit columns broken in federated +# storage engine +# +--disable_warnings +DROP TABLE IF EXISTS federated.t1; +--enable_warnings +CREATE TABLE federated.t1 ( + `bitty` bit(3) +) DEFAULT CHARSET=latin1; + +connection master; + +--disable_warnings +DROP TABLE IF EXISTS federated.t1; +--enable_warnings + +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval CREATE TABLE federated.t1 ( + `bitty` bit(3) +) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 + CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; + +INSERT INTO federated.t1 VALUES (b'001'); +INSERT INTO federated.t1 VALUES (b'010'); +INSERT INTO federated.t1 VALUES (b'011'); +INSERT INTO federated.t1 VALUES (b'100'); +INSERT INTO federated.t1 VALUES (b'101'); +INSERT INTO federated.t1 VALUES (b'110'); +INSERT INTO federated.t1 VALUES (b'111'); +select * FROM federated.t1; +drop table federated.t1; + +connection slave; +drop table federated.t1; source include/federated_cleanup.inc; diff --git a/mysql-test/t/flush_read_lock_kill.test b/mysql-test/t/flush_read_lock_kill.test index de2576300dc..19a47b2893a 100644 --- a/mysql-test/t/flush_read_lock_kill.test +++ b/mysql-test/t/flush_read_lock_kill.test @@ -42,7 +42,7 @@ connection con1; # debug build running without our --debug=make_global..., will be # error 0 (no error). The only important thing to test is that on # debug builds with our --debug=make_global... we don't hang forever. ---error 0,1053 +--error 0,1053,2013 reap; connection con2; diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 08648f4e0a6..ea92ec944ed 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -348,6 +348,13 @@ INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); SET myisam_repair_threads=2; REPAIR TABLE t1; SET myisam_repair_threads=@@global.myisam_repair_threads; + +# +# BUG#5686 - #1034 - Incorrect key file for table - only utf8 +# +INSERT INTO t1 VALUES('testword\'\''); +SELECT a FROM t1 WHERE MATCH a AGAINST('testword' IN BOOLEAN MODE); +SELECT a FROM t1 WHERE MATCH a AGAINST('testword\'\'' IN BOOLEAN MODE); DROP TABLE t1; # End of 4.1 tests diff --git a/mysql-test/t/fulltext2.test b/mysql-test/t/fulltext2.test index bcd39b9ea04..7a7b572d58f 100644 --- a/mysql-test/t/fulltext2.test +++ b/mysql-test/t/fulltext2.test @@ -179,7 +179,37 @@ update t1 set a='aaaxxx' where a = 'aaayyy'; select count(*) from t1 where match a against ('aaaxxx' in boolean mode); select count(*) from t1 where match a against ('aaayyy' in boolean mode); select count(*) from t1 where match a against ('aaazzz' in boolean mode); +drop table t1; + +# +# BUG#11336 +# +# for uca collation isalnum and strnncollsp don't agree on whether +# 0xC2A0 is a space (strnncollsp is right, isalnum is wrong). +# +# they still don't, the bug was fixed by avoiding strnncollsp +# +set names utf8; +create table t1(a text,fulltext(a)) collate=utf8_swedish_ci; +insert into t1 values('test test '),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'), +('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'); +delete from t1 limit 1; drop table t1; +set names latin1; # End of 4.1 tests diff --git a/mysql-test/t/func_equal.test b/mysql-test/t/func_equal.test index 4c88ed170a1..1c219af0254 100644 --- a/mysql-test/t/func_equal.test +++ b/mysql-test/t/func_equal.test @@ -34,4 +34,13 @@ select * from t1 where value <=> value; select * from t1 where id <=> value or value<=>id; drop table t1,t2; +# +# Bug #12612: quoted bigint unsigned value and the use of "in" in where clause +# +create table t1 (a bigint unsigned); +insert into t1 values (4828532208463511553); +select * from t1 where a = '4828532208463511553'; +select * from t1 where a in ('4828532208463511553'); +drop table t1; + # End of 4.1 tests diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index a519d51e0b5..058df9af56b 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -69,7 +69,7 @@ set group_concat_max_len = 1024; # Test errors --error 1111 -select group_concat(sum(a)) from t1 group by grp; +select group_concat(sum(c)) from t1 group by grp; --error 1054 select grp,group_concat(c order by 2) from t1 group by grp; @@ -390,3 +390,19 @@ SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY id; DROP TABLE t1; # End of 4.1 tests + +# +# Bug#8568 "GROUP_CONCAT returns string, unless in a UNION in which case +# returns BLOB": add a test case, the bug can not be repeated any more. +# + +set names latin1; +create table t1 (a char, b char); +insert into t1 values ('a', 'a'), ('a', 'b'), ('b', 'a'), ('b', 'b'); +create table t2 select group_concat(b) as a from t1 where a = 'a'; +create table t3 (select group_concat(a) as a from t1 where a = 'a') union + (select group_concat(b) as a from t1 where a = 'b'); +select charset(a) from t2; +select charset(a) from t3; +drop table t1, t2, t3; +set names default; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 9237205eeb5..c667f90940c 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -2,8 +2,6 @@ # simple test of all group functions # ---source include/have_innodb.inc - --disable_warnings drop table if exists t1,t2; --enable_warnings @@ -545,10 +543,12 @@ DROP TABLE t1; # Bug #12882 min/max inconsistent on empty table # +--disable_warnings create table t1m (a int) engine=myisam; create table t1i (a int) engine=innodb; create table t2m (a int) engine=myisam; create table t2i (a int) engine=innodb; +--enable_warnings insert into t2m values (5); insert into t2i values (5); diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 2935f24f2d7..24dd18daab1 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -116,6 +116,16 @@ insert into t1 values (1); select rand(i) from t1; drop table t1; +# +# Bug #14009: use of abs() on null value causes problems with filesort +# +# InnoDB is required to reproduce the fault, but it is okay if we default to +# MyISAM when testing. +create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8; +insert into t1 values ('http://www.foo.com/', now()); +select a from t1 where a='http://www.foo.com/' order by abs(timediff(ts, 0)); +drop table t1; + # End of 4.1 tests # diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 142bd29fa2d..bb3f621d194 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -372,6 +372,9 @@ insert into t1 values (pointfromtext('point(1,1)')); drop table t1; +select (asWKT(geomfromwkb((0x000000000140240000000000004024000000000000)))); +select (asWKT(geomfromwkb((0x010100000000000000000024400000000000002440)))); + # End of 4.1 tests # diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index 635c7a3f4e7..e806df5e91c 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -487,4 +487,16 @@ flush privileges; set @user123="non-existent"; select * from mysql.db where user=@user123; +set names koi8r; +create database ÂÄ; +grant select on ÂÄ.* to root@localhost; +select hex(Db) from mysql.db where Db='ÂÄ'; +show grants for root@localhost; +flush privileges; +show grants for root@localhost; +drop database ÂÄ; +revoke all privileges on ÂÄ.* from root@localhost; +show grants for root@localhost; +set names latin1; + # End of 4.1 tests diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test index 2fa82ce5dce..c19bb1482d6 100644 --- a/mysql-test/t/grant2.test +++ b/mysql-test/t/grant2.test @@ -155,6 +155,38 @@ flush privileges; drop database mysqltest; use test; + +# +# Bug #15775: "drop user" command does not refresh acl_check_hosts +# + +# Create some test users +create user mysqltest_1@host1; +create user mysqltest_2@host2; +create user mysqltest_3@host3; +create user mysqltest_4@host4; +create user mysqltest_5@host5; +create user mysqltest_6@host6; +create user mysqltest_7@host7; +flush privileges; + +# Drop one user +drop user mysqltest_3@host3; + +# This connect failed before fix since the acl_check_hosts list was corrupted by the "drop user" +connect (con8,127.0.0.1,root,,test,$MASTER_MYPORT,); +disconnect con8; +connection default; + +# Clean up - Drop all of the remaining users at once +drop user mysqltest_1@host1, mysqltest_2@host2, mysqltest_4@host4, + mysqltest_5@host5, mysqltest_6@host6, mysqltest_7@host7; + +# Check that it's still possible to connect +connect (con9,127.0.0.1,root,,test,$MASTER_MYPORT,); +disconnect con9; +connection default; + # # Create and drop user # diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index 3d751f4a571..e15ef92116c 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -693,3 +693,25 @@ create table t1(a int, key(a)) engine=innodb; insert into t1 values(1); select a, count(a) from t1 group by a with rollup; drop table t1; + +# +# Bug #13293 Wrongly used index results in endless loop. +# +create table t1 (f1 int, f2 char(1), primary key(f1,f2)) engine=innodb; +insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d"); +alter table t1 drop primary key, add primary key (f2, f1); +explain select distinct f1 a, f1 b from t1; +explain select distinct f1, f2 from t1; +drop table t1; + +# +# Bug #14920 Ordering aggregated result sets with composite primary keys +# corrupts resultset +# +create table t1 (c1 int not null,c2 int not null, primary key(c1,c2)); +insert into t1 (c1,c2) values +(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9); +select distinct c1, c2 from t1 order by c2; +select c1,min(c2) as c2 from t1 group by c1 order by c2; +select c1,c2 from t1 group by c1,c2 order by c2; +drop table t1; diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test index 1bb9b1d3504..3fb09df5f2f 100644 --- a/mysql-test/t/handler.test +++ b/mysql-test/t/handler.test @@ -3,7 +3,7 @@ # --disable_warnings -drop table if exists t1; +drop table if exists t1,t3,t4,t5; --enable_warnings create table t1 (a int, b char(10), key a(a), key b(a,b)); @@ -347,4 +347,79 @@ drop table t3; drop table t4; drop table t5; +# +# Bug#14397 - OPTIMIZE TABLE with an open HANDLER causes a crash +# +create table t1 (c1 int); +insert into t1 values (1); +# client 1 +handler t1 open; +handler t1 read first; +# client 2 +connect (con2,localhost,root,,); +connection con2; +--exec echo send the below to another connection, do not wait for the result +send optimize table t1; +--sleep 1 +# client 1 +--exec echo proceed with the normal connection +connection default; +handler t1 read next; +handler t1 close; +# client 2 +--exec echo read the result from the other connection +connection con2; +reap; +# client 1 +--exec echo proceed with the normal connection +connection default; +drop table t1; + # End of 4.1 tests + +# +# Addendum to Bug#14397 - OPTIMIZE TABLE with an open HANDLER causes a crash +# Show that DROP TABLE can no longer deadlock against +# FLUSH TABLES WITH READ LOCK. This is a 5.0 issue. +# +create table t1 (c1 int); +insert into t1 values (14397); +flush tables with read lock; +# The thread with the global read lock cannot drop the table itself: +--error 1223 +drop table t1; +# +# client 2 +# We need a second connection to try the drop. +# The drop waits for the global read lock to go away. +# Without the addendum fix it locked LOCK_open before entering the wait loop. +connection con2; +--exec echo send the below to another connection, do not wait for the result +send drop table t1; +--sleep 1 +# +# client 1 +# Now we need something that wants LOCK_open. A simple table access which +# opens the table does the trick. +--exec echo proceed with the normal connection +connection default; +# This would hang on LOCK_open without the 5.0 addendum fix. +select * from t1; +# Release the read lock. This should make the DROP go through. +unlock tables; +# +# client 2 +# Read the result of the drop command. +connection con2; +--exec echo read the result from the other connection +reap; +# +# client 1 +# Now back to normal operation. The table should not exist any more. +--exec echo proceed with the normal connection +connection default; +--error 1146 +select * from t1; +# Just to be sure and not confuse the next test case writer. +drop table if exists t1; + diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 9fb57fc187b..f835a7148a2 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; @@ -499,8 +501,8 @@ drop table t1; # grant select on test.* to mysqltest_4@localhost; -connect (user4,localhost,mysqltest_4,,); -connection user4; +connect (user10261,localhost,mysqltest_4,,); +connection user10261; SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='TABLE_NAME'; connection default; @@ -727,3 +729,64 @@ use information_schema; select 1 from (select 1 from test.t1) a; use test; drop table t1; + +# +# Bug #14290: character_maximum_length for text fields +# + +create table t1(a blob, b text charset utf8, c text charset ucs2); +select data_type, character_octet_length, character_maximum_length + from information_schema.columns where table_name='t1'; +drop table t1; + +# +# Bug#14476 `information_schema`.`TABLES`.`TABLE_TYPE` with empty value +# +create table t1 (f1 int(11)); +create view v1 as select * from t1; +drop table t1; +select table_type from information_schema.tables +where table_name="v1"; +drop view v1; + +# +# Bug #14387 SHOW COLUMNS doesn't work on temporary tables +# Bug #15224 SHOW INDEX from temporary table doesn't work +# Bug #12770 DESC cannot display the info. about temporary table +# +create temporary table t1(f1 int, index(f1)); +show columns from t1; +describe t1; +show indexes from t1; +drop table t1; + +# +# Bug#14271 I_S: columns has no size for (var)binary columns +# +create table t1(f1 binary(32), f2 varbinary(64)); +select character_maximum_length, character_octet_length +from information_schema.columns where table_name='t1'; +drop table t1; + +# +# Bug#15533 crash, information_schema, function, view +# +CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), f3 BIGINT); +INSERT INTO t1 SET f1 = 1, f2 = 'Schoenenbourg', f3 = 1; + +CREATE FUNCTION func2() RETURNS BIGINT RETURN 1; + +delimiter //; +CREATE FUNCTION func1() RETURNS BIGINT +BEGIN + RETURN ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS); +END// +delimiter ;// + +CREATE VIEW v1 AS SELECT 1 FROM t1 + WHERE f3 = (SELECT func2 ()); +SELECT func1(); +DROP TABLE t1; +DROP VIEW v1; +DROP FUNCTION func1; +DROP FUNCTION func2; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index cf0e08e2e66..887d8193157 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -1356,8 +1356,8 @@ source include/varchar.inc; # Clean up filename -- embedded server reports whole path without .frm, # regular server reports relative path with .frm (argh!) --replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / t1.frm t1 ---error 1005 create table t1 (v varchar(65530), key(v)); +drop table t1; create table t1 (v varchar(65536)); show create table t1; drop table t1; @@ -1485,7 +1485,7 @@ CREATE TEMPORARY TABLE t2 DROP TABLE t1; # -# Test that index column max sizes are checked (bug #13315) +# Test that index column max sizes are honored (bug #13315) # # prefix index @@ -1512,22 +1512,36 @@ create table t8 (col1 blob, index(col1(767))) create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2)) character set = latin1 engine = innodb; +show create table t9; + drop table t1, t2, t3, t4, t5, t6, t7, t8, t9; ---error 1005 -create table t1 (col1 varchar(768), index (col1)) +# these should have their index length trimmed +create table t1 (col1 varchar(768), index(col1)) character set = latin1 engine = innodb; ---error 1005 -create table t2 (col1 varchar(768) primary key) +create table t2 (col1 varbinary(768), index(col1)) character set = latin1 engine = innodb; ---error 1005 -create table t3 (col1 varbinary(768) primary key) +create table t3 (col1 text, index(col1(768))) character set = latin1 engine = innodb; ---error 1005 -create table t4 (col1 text, index(col1(768))) +create table t4 (col1 blob, index(col1(768))) character set = latin1 engine = innodb; ---error 1005 -create table t5 (col1 blob, index(col1(768))) + +show create table t1; + +drop table t1, t2, t3, t4; + +# these should be refused +--error 1071 +create table t1 (col1 varchar(768) primary key) + character set = latin1 engine = innodb; +--error 1071 +create table t2 (col1 varbinary(768) primary key) + character set = latin1 engine = innodb; +--error 1071 +create table t3 (col1 text, primary key(col1(768))) + character set = latin1 engine = innodb; +--error 1071 +create table t4 (col1 blob, primary key(col1(768))) character set = latin1 engine = innodb; # @@ -1751,3 +1765,106 @@ insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken'); drop table t1; drop table t2; commit; + +# tests for bugs #9802 and #13778 + +# test that FKs between invalid types are not accepted + +set foreign_key_checks=0; +create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb; +-- error 1005 +create table t1(a char(10) primary key, b varchar(20)) engine = innodb; +set foreign_key_checks=1; +drop table t2; + +# test that FKs between different charsets are not accepted in CREATE even +# when f_k_c is 0 + +set foreign_key_checks=0; +create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1; +-- error 1005 +create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8; +set foreign_key_checks=1; +drop table t1; + +# test that invalid datatype conversions with ALTER are not allowed + +set foreign_key_checks=0; +create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb; +create table t1(a varchar(10) primary key) engine = innodb; +-- error 1025,1025 +alter table t1 modify column a int; +set foreign_key_checks=1; +drop table t2,t1; + +# test that charset conversions with ALTER are allowed when f_k_c is 0 + +set foreign_key_checks=0; +create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1; +create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1; +alter table t1 convert to character set utf8; +set foreign_key_checks=1; +drop table t2,t1; + +# test that RENAME does not allow invalid charsets when f_k_c is 0 + +set foreign_key_checks=0; +create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1; +create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8; +-- error 1025 +rename table t3 to t1; +set foreign_key_checks=1; +drop table t2,t3; + +# +# Test that we can create a large (>1K) key +# +create table t1 (a varchar(255) character set utf8, + b varchar(255) character set utf8, + c varchar(255) character set utf8, + d varchar(255) character set utf8, + key (a,b,c,d)) engine=innodb; +drop table t1; +--error ER_TOO_LONG_KEY +create table t1 (a varchar(255) character set utf8, + b varchar(255) character set utf8, + c varchar(255) character set utf8, + d varchar(255) character set utf8, + e varchar(255) character set utf8, + key (a,b,c,d,e)) engine=innodb; + +# test that foreign key errors are reported correctly (Bug #15550) + +create table t1(a int primary key) row_format=redundant engine=innodb; +create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb; +create table t3(a int primary key) row_format=compact engine=innodb; +create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb; + +insert into t1 values(1); +insert into t3 values(1); +-- error 1452 +insert into t2 values(2); +-- error 1452 +insert into t4 values(2); +insert into t2 values(1); +insert into t4 values(1); +-- error 1451 +update t1 set a=2; +-- error 1452 +update t2 set a=2; +-- error 1451 +update t3 set a=2; +-- error 1452 +update t4 set a=2; +-- error 1451 +truncate t1; +-- error 1451 +truncate t3; +truncate t2; +truncate t4; +truncate t1; +truncate t3; + +drop table t4,t3,t2,t1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/innodb_gis.test b/mysql-test/t/innodb_gis.test new file mode 100644 index 00000000000..142b526af92 --- /dev/null +++ b/mysql-test/t/innodb_gis.test @@ -0,0 +1,3 @@ +--source include/have_innodb.inc +SET storage_engine=innodb; +--source include/gis_generic.inc diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 7116a25ff39..5dd6f338865 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -202,4 +202,26 @@ insert into t1 select t2.a from t2 on duplicate key update t2.a= a + t2.b; insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b; drop table t1,t2,t3; +# +# Bug #12695 Item_func_isnull::update_used_tables() did not update +# const_item_cache +create table t1(f1 varchar(5) key); +insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1; +insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1; +insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1; +select * from t1; +drop table t1; + +# +# Bug #13392 values() fails with 'ambiguous' or returns NULL +# with ON DUPLICATE and SELECT +create table t1(x int, y int); +create table t2(x int, z int); +insert into t1(x,y) select x,z from t2 on duplicate key update x=values(x); +--error 1054 +insert into t1(x,y) select x,z from t2 on duplicate key update x=values(z); +--error 1054 +insert into t1(x,y) select x,z from t2 on duplicate key update x=values(t2.x); +drop table t1,t2; + # End of 4.1 tests diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index bb82a93c6c4..553aaf987bb 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -334,3 +334,210 @@ select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i drop table t1,t2,t3; # End of 4.1 tests + +# +# Tests for WL#2486 Natural/using join according to SQL:2003. +# +# NOTICE: +# - The tests are designed so that all statements, except MySQL +# extensions run on any SQL server. Please do no change. +# - Tests marked with TODO will be submitted as bugs. +# + +create table t1 (c int, b int); +create table t2 (a int, b int); +create table t3 (b int, c int); +create table t4 (y int, c int); +create table t5 (y int, z int); +create table t6 (a int, c int); + +insert into t1 values (10,1); +insert into t1 values (3 ,1); +insert into t1 values (3 ,2); +insert into t2 values (2, 1); +insert into t3 values (1, 3); +insert into t3 values (1,10); +insert into t4 values (11,3); +insert into t4 values (2, 3); +insert into t5 values (11,4); +insert into t6 values (2, 3); + +-- Views with simple natural join. +create algorithm=merge view v1a as +select * from t1 natural join t2; +-- as above, but column names are cross-renamed: a->c, c->b, b->a +create algorithm=merge view v1b(a,b,c) as +select * from t1 natural join t2; +-- as above, but column names are aliased: a->c, c->b, b->a +create algorithm=merge view v1c as +select b as a, c as b, a as c from t1 natural join t2; +-- as above, but column names are cross-renamed, and aliased +-- a->c->b, c->b->a, b->a->c +create algorithm=merge view v1d(b, a, c) as +select a as c, c as b, b as a from t1 natural join t2; + +-- Views with JOIN ... ON +create algorithm=merge view v2a as +select t1.c, t1.b, t2.a from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c; +create algorithm=merge view v2b as +select t1.c as b, t1.b as a, t2.a as c +from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c; + +-- Views with bigger natural join +create algorithm=merge view v3a as +select * from t1 natural join t2 natural join t3; +create algorithm=merge view v3b as +select * from t1 natural join (t2 natural join t3); + +-- View over views with mixed natural join and join ... on +create algorithm=merge view v4 as +select * from v2a natural join v3a; + +-- Nested natural/using joins. +select * from (t1 natural join t2) natural join (t3 natural join t4); +select * from (t1 natural join t2) natural left join (t3 natural join t4); +select * from (t3 natural join t4) natural right join (t1 natural join t2); +select * from (t1 natural left join t2) natural left join (t3 natural left join t4); +select * from (t4 natural right join t3) natural right join (t2 natural right join t1); +select * from t1 natural join t2 natural join t3 natural join t4; +select * from ((t1 natural join t2) natural join t3) natural join t4; +select * from t1 natural join (t2 natural join (t3 natural join t4)); +-- BUG#15355: this query fails in 'prepared statements' mode +-- select * from ((t3 natural join (t1 natural join t2)) natural join t4) natural join t5; +-- select * from ((t3 natural left join (t1 natural left join t2)) natural left join t4) natural left join t5; +select * from t5 natural right join (t4 natural right join ((t2 natural right join t1) natural right join t3)); +select * from (t1 natural join t2), (t3 natural join t4); +-- MySQL extension - nested comma ',' operator instead of cross join. +-- BUG#15357 - natural join with nested cross-join results in incorrect columns +-- select * from t5 natural join ((t1 natural join t2), (t3 natural join t4)); +-- select * from ((t1 natural join t2), (t3 natural join t4)) natural join t5; +-- select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4)); +-- select * from ((t1 natural join t2) cross join (t3 natural join t4)) natural join t5; + +select * from (t1 join t2 using (b)) join (t3 join t4 using (c)) using (c); +select * from (t1 join t2 using (b)) natural join (t3 join t4 using (c)); + + +-- Other clauses refer to NJ columns. +select a,b,c from (t1 natural join t2) natural join (t3 natural join t4) +where b + 1 = y or b + 10 = y group by b,c,a having min(b) < max(y) order by a; +select * from (t1 natural join t2) natural left join (t3 natural join t4) +where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y; +select * from (t3 natural join t4) natural right join (t1 natural join t2) +where b + 1 = y or b + 10 = y group by b,c,a,y having min(b) < max(y) order by a, y; + +-- Qualified column references to NJ columns. +select * from t1 natural join t2 where t1.c > t2.a; +select * from t1 natural join t2 where t1.b > t2.b; +select * from t1 natural left join (t4 natural join t5) where t5.z is not NULL; + +-- Nested 'join ... on' - name resolution of ON conditions +select * from t1 join (t2 join t4 on b + 1 = y) on t1.c = t4.c; +select * from (t2 join t4 on b + 1 = y) join t1 on t1.c = t4.c; +select * from t1 natural join (t2 join t4 on b + 1 = y); +select * from (t1 cross join t2) join (t3 cross join t4) on (a < y and t2.b < t3.c); + +-- MySQL extension - 'join ... on' over nested comma operator +select * from (t1, t2) join (t3, t4) on (a < y and t2.b < t3.c); +select * from (t1 natural join t2) join (t3 natural join t4) on a = y; +select * from ((t3 join (t1 join t2 on c > a) on t3.b < t2.a) join t4 on y > t1.c) join t5 on z = t1.b + 3; + +-- MySQL extension - refererence qualified coalesced columns +select * from t1 natural join t2 where t1.b > 0; +select * from t1 natural join (t4 natural join t5) where t4.y > 7; +select * from (t4 natural join t5) natural join t1 where t4.y > 7; +select * from t1 natural left join (t4 natural join t5) where t4.y > 7; +select * from (t4 natural join t5) natural right join t1 where t4.y > 7; +select * from (t1 natural join t2) join (t3 natural join t4) on t1.b = t3.b; + +-- MySQL extension - select qualified columns of NJ columns +select t1.*, t2.* from t1 natural join t2; +select t1.*, t2.*, t3.*, t4.* from (t1 natural join t2) natural join (t3 natural join t4); + +-- Queries over subselects in the FROM clause +select * from (select * from t1 natural join t2) as t12 + natural join + (select * from t3 natural join t4) as t34; +select * from (select * from t1 natural join t2) as t12 + natural left join + (select * from t3 natural join t4) as t34; +select * from (select * from t3 natural join t4) as t34 + natural right join + (select * from t1 natural join t2) as t12; + +-- Queries over views +select * from v1a; +select * from v1b; +select * from v1c; +select * from v1d; +select * from v2a; +select * from v2b; +select * from v3a; +select * from v3b; +select * from v4; +select * from v1a natural join v2a; +select v2a.* from v1a natural join v2a; +select * from v1b join v2a on v1b.b = v2a.c; +select * from v1c join v2a on v1c.b = v2a.c; +select * from v1d join v2a on v1d.a = v2a.c; +select * from v1a join (t3 natural join t4) on a = y; + +-- TODO: add tests with correlated subqueries for natural join/join on. +-- related to BUG#15269 + + +---------------------------------------------------------------------- +-- Negative tests (tests for errors) +---------------------------------------------------------------------- +-- error 1052 +select * from t1 natural join (t3 cross join t4); -- works in Oracle - bug +-- error 1052 +select * from (t3 cross join t4) natural join t1; -- works in Oracle - bug +-- error 1052 +select * from t1 join (t2, t3) using (b); +-- error 1052 +select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6; +-- error 1052 +select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6; +-- error 1052 +-- BUG#15357: doesn't detect non-unique column 'c', as in the above query. +-- select * from t6 natural join ((t1 natural join t2), (t3 natural join t4)); +-- error 1052 +select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4); +-- error 1052 +select * from (t3 natural join t4) natural join (t1 join t2 on t1.b=t2.b); +-- this one is OK, the next equivalent one is incorrect (bug in Oracle) +-- error 1052 +select * from (t3 join (t4 natural join t5) on (b < z)) + natural join + (t1 natural join t2); +-- error 1052 +-- BUG#15357: this query should return an ambiguous column error +-- Expected result: the query must return error with duplicate column 'c' +--select * from (t1 natural join t2) +-- natural join +-- (t3 join (t4 natural join t5) on (b < z)); + +-- error 1054 +select t1.b from v1a; +-- error 1054 +select * from v1a join v1b on t1.b = t2.b; + +drop table t1; +drop table t2; +drop table t3; +drop table t4; +drop table t5; +drop table t6; + +drop view v1a; +drop view v1b; +drop view v1c; +drop view v1d; +drop view v2a; +drop view v2b; +drop view v3a; +drop view v3b; +drop view v4; + +# End of tests for WL#2486 - natural/using join diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index 0592ec3152f..9f23e2d0e2f 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, t3, t4, t5, t6, t7; diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test index 7f3a9932d31..c50c35825fc 100644 --- a/mysql-test/t/kill.test +++ b/mysql-test/t/kill.test @@ -25,11 +25,18 @@ select ((@id := kill_id) - kill_id) from t1; kill @id; connection con1; ---sleep 1 +--sleep 2 -# this statement should fail ---error 2006,2013 +--disable_query_log +--disable_result_log +# One of the following statements should fail +--error 0,2006,2013 select 1; +--error 0,2006,2013 +select 1; +--enable_query_log +--enable_result_log + --enable_reconnect # this should work, and we should have a new connection_id() select ((@id := kill_id) - kill_id) from t1; diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index cd3a8f0fd92..09d97a42714 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -31,7 +31,6 @@ load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated select * from t1; drop table t1; - # # Bug #12053 LOAD DATA INFILE ignores NO_AUTO_VALUE_ON_ZERO setting # @@ -59,6 +58,15 @@ select * from t1; SET @@SQL_MODE=@OLD_SQL_MODE; drop table t1; +# +# Bug #11203: LOAD DATA does not accept same characters for ESCAPED and +# ENCLOSED +# +create table t1 (a varchar(20), b varchar(20)); +load data infile '../../std_data/loaddata_dq.dat' into table t1 fields terminated by ',' enclosed by '"' escaped by '"' (a,b); +select * from t1; +drop table t1; + # End of 4.1 tests # @@ -104,3 +112,5 @@ select * from t1; # cleanup drop table t1, t2; + +# End of 5.0 tests diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index ff05867b7c1..060f1ea167b 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -352,4 +352,30 @@ INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=3; SELECT b FROM t2; DROP TABLE t1, t2; + +# +# BUG#5390 - problems with merge tables +# Problem #1: INSERT...SELECT +# +#drop table if exists t1, t2, t3; +create table t1(a int); +create table t2(a int); +insert into t1 values (1); +insert into t2 values (2); +create table t3 (a int) engine=merge union=(t1, t2) insert_method=first; +select * from t3; +# +insert t2 select * from t2; +select * from t2; +# +insert t3 select * from t1; +select * from t3; +# +insert t1 select * from t3; +select * from t1; +select * from t2; +select * from t3; +check table t1, t2; +drop table t1, t2, t3; + # End of 4.1 tests diff --git a/mysql-test/t/mix_innodb_myisam_binlog.test b/mysql-test/t/mix_innodb_myisam_binlog.test index 2e804f4c986..658584b625e 100644 --- a/mysql-test/t/mix_innodb_myisam_binlog.test +++ b/mysql-test/t/mix_innodb_myisam_binlog.test @@ -227,7 +227,37 @@ select (@after:=unix_timestamp())*0; # always give repeatable output # the bug, the reap would return immediately after the insert into t2. select (@after-@before) >= 2; -# cleanup drop table t1,t2; +commit; + +# test for BUG#7947 - DO RELEASE_LOCK() not written to binlog on rollback in the middle +# of a transaction + +connection con2; +begin; +create temporary table ti (a int) engine=innodb; +rollback; +insert into ti values(1); +set autocommit=0; +create temporary table t1 (a int) engine=myisam; +commit; +insert t1 values (1); +rollback; +create table t0 (n int); +insert t0 select * from t1; +set autocommit=1; +insert into t0 select GET_LOCK("lock1",null); +set autocommit=0; +create table t2 (n int) engine=innodb; +insert into t2 values (3); +disconnect con2; +connection con3; +select get_lock("lock1",60); +--replace_column 5 # +--replace_result "xid=208" "xid=105" "xid=227" "xid=114" "xid=230" "xid=115" "xid=234" "xid=117" "xid=261" "xid=132" +show binlog events from 98; +do release_lock("lock1"); +drop table t0,t2; + # End of 4.1 tests diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 73afcab5e27..fb90c16bb86 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -477,6 +477,18 @@ explain select distinct t1.a from t1,t2 order by t2.a; drop table t1,t2; # +# Bug#14616 - Freshly imported table returns error 124 when using LIMIT +# +create table t1 ( + c1 varchar(32), + key (c1) +) engine=myisam; +alter table t1 disable keys; +insert into t1 values ('a'), ('b'); +select c1 from t1 order by c1 limit 1; +drop table t1; + +# # Test RTREE index # --error 1235, 1289 @@ -629,6 +641,43 @@ show index from t1; drop table t1; +# WL#2609, CSC#XXXX: MyISAM +set myisam_stats_method=nulls_ignored; +show variables like 'myisam_stats_method'; + +create table t1 ( + a char(3), b char(4), c char(5), d char(6), + key(a,b,c,d) +); +insert into t1 values ('bcd','def1', NULL, 'zz'); +insert into t1 values ('bcd','def2', NULL, 'zz'); +insert into t1 values ('bce','def1', 'yuu', NULL); +insert into t1 values ('bce','def2', NULL, 'quux'); +analyze table t1; +show index from t1; +delete from t1; +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 # @@ -703,6 +752,7 @@ insert into t1 values (10),(11),(12); select * from t1; check table t1; drop table t1; +disconnect con1; # Same test with dynamic record length create table t1 (a int, b varchar(30) default "hello"); @@ -727,8 +777,10 @@ insert into t1 (a) values (10),(11),(12); select a from t1; check table t1; drop table t1; +disconnect con1; set global concurrent_insert=@save_concurrent_insert; + # BUG#9622 - ANALYZE TABLE and ALTER TABLE .. ENABLE INDEX produce # different statistics on the same table with NULL values. create table t1 (a int, key(a)); diff --git a/mysql-test/t/mysql_client_test.test b/mysql-test/t/mysql_client_test.test index ccf5e0bf66a..66b57dd5fb7 100644 --- a/mysql-test/t/mysql_client_test.test +++ b/mysql-test/t/mysql_client_test.test @@ -6,7 +6,7 @@ # var/log/mysql_client_test.trace --disable_result_log ---exec echo $MYSQL_CLIENT_TEST ---exec $MYSQL_CLIENT_TEST +--exec echo $MYSQL_CLIENT_TEST --getopt-ll-test=25600M +--exec $MYSQL_CLIENT_TEST --getopt-ll-test=25600M # End of 4.1 tests diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 31957e0db0d..e54bf6386c5 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -605,6 +605,7 @@ select * from t2 order by a; drop table t1, t2; drop database db1; + # # Bug #9558 mysqldump --no-data db t1 t2 format still dumps data # @@ -644,7 +645,7 @@ select '------ Testing with illegal table names ------' as test_sequence ; --error 6 --exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "\\t1" 2>&1 - + --error 6 --exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "\\\\t1" 2>&1 @@ -658,19 +659,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 ; @@ -685,6 +686,7 @@ drop table t1, t2, t3; drop database mysqldump_test_db; use test; + # # Bug #9657 mysqldump xml ( -x ) does not format NULL fields correctly # @@ -697,6 +699,18 @@ insert into t2 (a, b) values (NULL, NULL),(10, NULL),(NULL, "twenty"),(30, "thir --exec $MYSQL_DUMP --skip-comments --xml --no-create-info test drop table t1, t2; +# +# BUG #12123 +# +create table t1 (a text character set utf8, b text character set latin1); +insert t1 values (0x4F736E616272C3BC636B, 0x4BF66C6E); +select * from t1; +--exec $MYSQL_DUMP --tab=$MYSQL_TEST_DIR/var/tmp/ test +--exec $MYSQL test < $MYSQL_TEST_DIR/var/tmp/t1.sql +--exec $MYSQL_IMPORT test $MYSQL_TEST_DIR/var/tmp/t1.txt +select * from t1; + +drop table t1; # End of 4.1 tests # @@ -870,6 +884,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); @@ -905,6 +920,7 @@ DROP FUNCTION bug9056_func1; DROP FUNCTION bug9056_func2; DROP PROCEDURE bug9056_proc1; DROP PROCEDURE bug9056_proc2; +DROP PROCEDURE `a'b`; drop table t1; # @@ -985,3 +1001,38 @@ drop view v2; drop view v0; drop view v1; drop table t1; + +# +# BUG#14554 - mysqldump does not separate words "ROW" and "BEGIN" +# for tables with trigger created in the IGNORE_SPACE sql mode. +# + +SET @old_sql_mode = @@SQL_MODE; +SET SQL_MODE = IGNORE_SPACE; + +CREATE TABLE t1 (a INT); +DELIMITER |; +CREATE TRIGGER tr1 BEFORE INSERT ON t1 + FOR EACH ROW + BEGIN + SET new.a = 0; + END| +DELIMITER ;| + +SET SQL_MODE = @old_sql_mode; + +--exec $MYSQL_DUMP --skip-comments --databases test + +DROP TRIGGER tr1; +DROP TABLE t1; + +# +# Bug #13318: Bad result with empty field and --hex-blob +# +create table t1 (a binary(1), b blob); +insert into t1 values ('',''); +--exec $MYSQL_DUMP --skip-comments --skip-extended-insert --hex-blob test t1 +--exec $MYSQL_DUMP --skip-comments --hex-blob test t1 +drop table t1; + +# End of 4.1 tests diff --git a/mysql-test/t/mysqlshow.test b/mysql-test/t/mysqlshow.test index 1e2e97a4e07..78c4ae2b531 100644 --- a/mysql-test/t/mysqlshow.test +++ b/mysql-test/t/mysqlshow.test @@ -2,7 +2,7 @@ -- source include/not_embedded.inc --disable_warnings -DROP TABLE IF EXISTS t1,t2; +DROP TABLE IF EXISTS t1,t2,test1,test2; --enable_warnings # diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index 11fbb023963..440a7787985 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -358,11 +358,11 @@ select 3 from t1 ; # Missing delimiter # The comment will be "sucked into" the sleep command since # delimiter is missing until after "show status" ---system echo "sleep 4" > var/log/mysqltest.sql ---system echo "# A comment" >> var/log/mysqltest.sql ---system echo "show status;" >> var/log/mysqltest.sql +--system echo "sleep 4" > var/tmp/mysqltest.sql +--system echo "# A comment" >> var/tmp/mysqltest.sql +--system echo "show status;" >> var/tmp/mysqltest.sql --error 1 ---exec $MYSQL_TEST < var/log/mysqltest.sql 2>&1 +--exec $MYSQL_TEST < var/tmp/mysqltest.sql 2>&1 # # Extra delimiter @@ -806,6 +806,66 @@ select "a" as col1, "c" as col2; --error 1 --exec echo "save_master_pos; sync_with_master a;" | $MYSQL_TEST 2>&1 +# ---------------------------------------------------------------------------- +# Test connect +# ---------------------------------------------------------------------------- + +--error 1 +--exec echo "connect;" | $MYSQL_TEST 2>&1 +--error 1 +--exec echo "connect ();" | $MYSQL_TEST 2>&1 +--error 1 +--exec echo "connect (con2);" | $MYSQL_TEST 2>&1 +--error 1 +--exec echo "connect (con2,);" | $MYSQL_TEST 2>&1 +--error 1 +--exec echo "connect (con2,localhost);" | $MYSQL_TEST 2>&1 +--error 1 +--exec echo "connect (con2, localhost, root);" | $MYSQL_TEST 2>&1 +--error 1 +--exec echo "connect (con2, localhost, root,);" | $MYSQL_TEST 2>&1 +--error 1 +--exec echo "connect (con2,localhost,root,,illegal_db);" | $MYSQL_TEST 2>&1 +--error 1 +--exec echo "connect (con1,localhost,root,,,illegal_port,);" | $MYSQL_TEST 2>&1 +--error 1 +--exec echo "connect (con1,localhost,root,,,,,SMTP POP);" | $MYSQL_TEST 2>&1 + +# Repeat connect/disconnect +--exec echo "let \$i=100;" > var/tmp/con.sql +--exec echo "while (\$i)" >> var/tmp/con.sql +--exec echo "{" >> var/tmp/con.sql +--exec echo " connect (test_con1,localhost,root,,); " >> var/tmp/con.sql +--exec echo " disconnect test_con1; " >> var/tmp/con.sql +--exec echo " dec \$i; " >> var/tmp/con.sql +--exec echo "}" >> var/tmp/con.sql +--exec echo "source var/tmp/con.sql;" | $MYSQL_TEST 2>&1 + +# Repeat connect/disconnect, exceed max number of connections +--exec echo "let \$i=200;" > var/tmp/con.sql +--exec echo "while (\$i)" >> var/tmp/con.sql +--exec echo "{" >> var/tmp/con.sql +--exec echo " connect (test_con1,localhost,root,,); " >> var/tmp/con.sql +--exec echo " disconnect test_con1; " >> var/tmp/con.sql +--exec echo " dec \$i; " >> var/tmp/con.sql +--exec echo "}" >> var/tmp/con.sql +--error 1 +--exec echo "source var/tmp/con.sql;" | $MYSQL_TEST 2>&1 + +# Select disconnected connection +--exec echo "connect (test_con1,localhost,root,,);" > var/tmp/con.sql +--exec echo "disconnect test_con1; " >> var/tmp/con.sql +--exec echo "connection test_con1;" >> var/tmp/con.sql +--error 1 +--exec echo "source var/tmp/con.sql;" | $MYSQL_TEST 2>&1 + +# Connection name already used +--exec echo "connect (test_con1,localhost,root,,);" > var/tmp/con.sql +--exec echo "connect (test_con1,localhost,root,,);" >> var/tmp/con.sql +--error 1 +--exec echo "source var/tmp/con.sql;" | $MYSQL_TEST 2>&1 + + # ---------------------------------------------------------------------------- # Test mysqltest arguments @@ -885,3 +945,53 @@ select "this will not be executed"; --enable_parsing select "this will be executed"; --enable_query_log + + +# +# Bug #11731 mysqltest in multi-statement queries ignores errors in +# non-1st queries +# + +# Failing multi statement query +# PS does not support multi statement +--exec echo "--disable_ps_protocol" > var/tmp/bug11731.sql +--exec echo "delimiter ||||;" >> var/tmp/bug11731.sql +--exec echo "create table t1 (a int primary key);" >> var/tmp/bug11731.sql +--exec echo "insert into t1 values (1);" >> var/tmp/bug11731.sql +--exec echo "select 'select-me';" >> var/tmp/bug11731.sql +--exec echo "insertz 'error query'||||" >> var/tmp/bug11731.sql +--exec echo "delimiter ;||||" >> var/tmp/bug11731.sql + +--error 1 +--exec $MYSQL_TEST -x $MYSQL_TEST_DIR/var/tmp/bug11731.sql 2>&1 +drop table t1; + +--error 1 +--exec $MYSQL_TEST --record -x $MYSQL_TEST_DIR/var/tmp/bug11731.sql -R $MYSQL_TEST_DIR/var/tmp/bug11731.out +# The .out file should be empty +--error 1 +--exec test -s $MYSQL_TEST_DIR/var/tmp/bug11731.out +drop table t1; + + +# Using expected error +# PS does not support multi statement +--exec echo "--disable_ps_protocol" > var/tmp/bug11731.sql +--exec echo "delimiter ||||;" >> var/tmp/bug11731.sql +--exec echo "--error 1064" >> var/tmp/bug11731.sql +--exec echo "create table t1 (a int primary key);" >> var/tmp/bug11731.sql +--exec echo "insert into t1 values (1);" >> var/tmp/bug11731.sql +--exec echo "select 'select-me';" >> var/tmp/bug11731.sql +--exec echo "insertz "error query"||||" >> var/tmp/bug11731.sql +--exec echo "delimiter ;||||" >> var/tmp/bug11731.sql + +# These two should work since the error is expected +--exec $MYSQL_TEST -x $MYSQL_TEST_DIR/var/tmp/bug11731.sql 2>&1 +drop table t1; + +--exec $MYSQL_TEST --record -x $MYSQL_TEST_DIR/var/tmp/bug11731.sql -R $MYSQL_TEST_DIR/var/tmp/bug11731.out +--exec cat $MYSQL_TEST_DIR/var/tmp/bug11731.out +drop table t1; + + + diff --git a/mysql-test/t/ndb_alter_table.test b/mysql-test/t/ndb_alter_table.test index 357f658a296..0a0211c8c83 100644 --- a/mysql-test/t/ndb_alter_table.test +++ b/mysql-test/t/ndb_alter_table.test @@ -142,6 +142,7 @@ INSERT INTO t1 VALUES (1,2,0),(18,19,4),(20,21,0); select c from t1 order by c; drop table t1; +--disable_ps_protocol create table t1 ( a int primary key, b varchar(10), c varchar(10), index (b) ) engine=ndb; insert into t1 values (1,'one','one'), (2,'two','two'), (3,'three','three'); @@ -151,10 +152,13 @@ select * from t1 where b = 'two'; connection server1; alter table t1 drop index c; connection server2; +# This should fail since index information is not automatically refreshed +--error 1015 select * from t1 where b = 'two'; select * from t1 where b = 'two'; connection server1; drop table t1; +--enable_ps_protocol #--disable_warnings #DROP TABLE IF EXISTS t2; diff --git a/mysql-test/t/ndb_basic.test b/mysql-test/t/ndb_basic.test index 1c78a4b8744..12aca73d82b 100644 --- a/mysql-test/t/ndb_basic.test +++ b/mysql-test/t/ndb_basic.test @@ -606,6 +606,14 @@ select * from t1 order by counter; drop table t1; +# +# BUG#14514 Creating table with packed key fails silently +# + +CREATE TABLE t1 ( b INT ) PACK_KEYS = 0 ENGINE = ndb; +select * from t1; +drop table t1; + # End of 4.1 tests # @@ -615,3 +623,14 @@ create table atablewithareallylongandirritatingname (a int); insert into atablewithareallylongandirritatingname values (2); select * from atablewithareallylongandirritatingname; drop table atablewithareallylongandirritatingname; + +# +# Bug#15682 +# +create table t1 (f1 varchar(50), f2 text,f3 int, primary key(f1)) engine=NDB; +insert into t1 (f1,f2,f3)VALUES("111111","aaaaaa",1); +insert into t1 (f1,f2,f3)VALUES("222222","bbbbbb",2); +select * from t1 order by f1; +select * from t1 order by f2; +select * from t1 order by f3; +drop table t1; diff --git a/mysql-test/t/ndb_charset.test b/mysql-test/t/ndb_charset.test index fb43e1831f3..5941e5750db 100644 --- a/mysql-test/t/ndb_charset.test +++ b/mysql-test/t/ndb_charset.test @@ -237,13 +237,18 @@ drop table t1; #select a,b,length(a),length(b) from t1 where a='c' and b='c'; #drop table t1; -# bug +# bug#14007 create table t1 ( a char(10) primary key -) engine=ndb; -insert into t1 values ('jonas % '); -replace into t1 values ('jonas % '); -replace into t1 values ('jonas % '); +) engine=ndbcluster default charset=latin1; + +insert into t1 values ('aaabb'); +select * from t1; +replace into t1 set a = 'AAABB'; +select * from t1; +replace into t1 set a = 'aAaBb'; +select * from t1; +replace into t1 set a = 'aaabb'; select * from t1; drop table t1; diff --git a/mysql-test/t/ndb_gis.test b/mysql-test/t/ndb_gis.test new file mode 100644 index 00000000000..e14f462c32d --- /dev/null +++ b/mysql-test/t/ndb_gis.test @@ -0,0 +1,5 @@ +--source include/have_ndb.inc +SET storage_engine=ndbcluster; +--source include/gis_generic.inc +set engine_condition_pushdown = on; +--source include/gis_generic.inc diff --git a/mysql-test/t/ndb_multi.test b/mysql-test/t/ndb_multi.test index 760150c6f6a..1183f2b283f 100644 --- a/mysql-test/t/ndb_multi.test +++ b/mysql-test/t/ndb_multi.test @@ -40,6 +40,7 @@ connection server1; # Currently a retry is required remotely --error 1296 select * from t1; +flush table t1; select * from t1; # Connect to server2 and use the tables from there diff --git a/mysql-test/t/ndb_read_multi_range.test b/mysql-test/t/ndb_read_multi_range.test index 9d1f918fef0..b1bf7fe4258 100644 --- a/mysql-test/t/ndb_read_multi_range.test +++ b/mysql-test/t/ndb_read_multi_range.test @@ -2,7 +2,7 @@ -- source include/not_embedded.inc --disable_warnings -DROP TABLE IF EXISTS t1, r1; +DROP TABLE IF EXISTS t1, t2, r1; --enable_warnings # diff --git a/mysql-test/t/not_embedded_server-master.opt b/mysql-test/t/not_embedded_server-master.opt new file mode 100644 index 00000000000..35fcc5f30c6 --- /dev/null +++ b/mysql-test/t/not_embedded_server-master.opt @@ -0,0 +1 @@ +--loose-to-force-a-restart diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test index e04c77ddf45..359b8b69a4d 100644 --- a/mysql-test/t/openssl_1.test +++ b/mysql-test/t/openssl_1.test @@ -1,6 +1,6 @@ # We test openssl. Result set is optimized to be compiled with --with-openssl. # Use mysql-test-run with --with-openssl option. --- source include/have_openssl_1.inc +-- source include/have_openssl.inc --disable_warnings drop table if exists t1; @@ -13,27 +13,36 @@ grant select on test.* to ssl_user2@localhost require cipher "DHE-RSA-AES256-SHA grant select on test.* to ssl_user3@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/L=Uppsala/O=MySQL AB/CN=MySQL Client/Email=abstract.mysql.developer@mysql.com"; grant select on test.* to ssl_user4@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/L=Uppsala/O=MySQL AB/CN=MySQL Client/Email=abstract.mysql.developer@mysql.com" ISSUER "/C=SE/L=Uppsala/O=MySQL AB/CN=Abstract MySQL Developer/Email=abstract.mysql.developer@mysql.com"; flush privileges; -connect (con1,localhost,ssl_user1,,); -connect (con2,localhost,ssl_user2,,); -connect (con3,localhost,ssl_user3,,); -connect (con4,localhost,ssl_user4,,); + +connect (con1,localhost,ssl_user1,,,,,SSL); +connect (con2,localhost,ssl_user2,,,,,SSL); +connect (con3,localhost,ssl_user3,,,,,SSL); +connect (con4,localhost,ssl_user4,,,,,SSL); connection con1; +# Check ssl turned on +SHOW STATUS LIKE 'Ssl_cipher'; select * from t1; --error 1142 delete from t1; connection con2; +# Check ssl turned on +SHOW STATUS LIKE 'Ssl_cipher'; select * from t1; --error 1142 delete from t1; connection con3; +# Check ssl turned on +SHOW STATUS LIKE 'Ssl_cipher'; select * from t1; --error 1142 delete from t1; connection con4; +# Check ssl turned on +SHOW STATUS LIKE 'Ssl_cipher'; select * from t1; --error 1142 delete from t1; diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 94ee2b1ca39..22d0abf645e 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -842,4 +842,44 @@ set @@tx_isolation=default; execute stmt; deallocate prepare stmt; +# +# Bug#14410 "Crash in Enum or Set type in CREATE TABLE and PS/SP" +# +# Part I. Make sure the typelib for ENUM is created in the statement memory +# root. +prepare stmt from "create temporary table t1 (letter enum('','a','b','c') +not null)"; +execute stmt; +drop table t1; +execute stmt; +drop table t1; +execute stmt; +drop table t1; +# Part II. Make sure that when the default value is converted to UTF-8, +# the new item is # created in the statement memory root. +set names latin1; +prepare stmt from "create table t1 (a enum('test') default 'test') + character set utf8"; +execute stmt; +drop table t1; +execute stmt; +drop table t1; +execute stmt; +drop table t1; +# Cleanup +set names default; +deallocate prepare stmt; + # End of 4.1 tests + +# +# Bug #14956: ROW_COUNT() returns incorrect result after EXECUTE of prepared +# statement +# +create table t1 (id int); +prepare ins_call from "insert into t1 (id) values (1)"; +execute ins_call; +select row_count(); +drop table t1; + +# End of 5.0 tests diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 646151a5aae..a9f171ff5b5 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -743,6 +743,19 @@ show status like "Qcache_hits"; drop table t1; # +# BUG#14652: Queries with leading '(' characters. +# +create table t1 (a int); +flush status; +(select a from t1) union (select a from t1); +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +(select a from t1) union (select a from t1); +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +drop table t1; # SP cursors and selects with query cache (BUG#9715) # create table t1 (a 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/rpl000001.test b/mysql-test/t/rpl000001.test index 3d03823d474..45d621b730f 100644 --- a/mysql-test/t/rpl000001.test +++ b/mysql-test/t/rpl000001.test @@ -92,7 +92,7 @@ kill @id; # We don't drop t3 as this is a temporary table drop table t2; connection master; ---error 1053 +--error 1053,2013 reap; connection slave; # The SQL slave thread should now have stopped because the query was killed on diff --git a/mysql-test/t/rpl_deadlock.test b/mysql-test/t/rpl_deadlock.test index d2a8fc0c844..c74ed989ece 100644 --- a/mysql-test/t/rpl_deadlock.test +++ b/mysql-test/t/rpl_deadlock.test @@ -58,7 +58,7 @@ while ($1) enable_query_log; select * from t1 for update; start slave; ---sleep 3 # hope that slave is blocked now +--real_sleep 3 # hope that slave is blocked now insert into t2 values(22); # provoke deadlock, slave should be victim commit; sync_with_master; @@ -76,7 +76,7 @@ change master to master_log_pos=532; # the BEGIN log event begin; select * from t2 for update; # hold lock start slave; ---sleep 10 # slave should have blocked, and be retrying +--real_sleep 10 # slave should have blocked, and be retrying commit; sync_with_master; select * from t1; # check that slave succeeded finally @@ -97,12 +97,12 @@ change master to master_log_pos=532; begin; select * from t2 for update; start slave; ---sleep 10 +--real_sleep 10 commit; sync_with_master; select * from t1; select * from t2; ---replace_column 1 # 8 # 9 # 23 # 33 # +--replace_column 1 # 8 # 9 # 11 # 23 # 33 # --replace_result $MASTER_MYPORT MASTER_MYPORT show slave status; 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_error_ignored_table.test b/mysql-test/t/rpl_error_ignored_table.test index cb4137c49e0..339d966dbb3 100644 --- a/mysql-test/t/rpl_error_ignored_table.test +++ b/mysql-test/t/rpl_error_ignored_table.test @@ -45,7 +45,7 @@ select (@id := id) - id from t3; kill @id; drop table t2,t3; connection master; ---error 0,1053 +--error 0,1053,2013 reap; connection master1; --replace_column 2 # 5 # diff --git a/mysql-test/t/rpl_openssl.test b/mysql-test/t/rpl_openssl.test index 3c151721d8e..e15eb9b179a 100644 --- a/mysql-test/t/rpl_openssl.test +++ b/mysql-test/t/rpl_openssl.test @@ -1,4 +1,4 @@ -source include/have_openssl_1.inc; +source include/have_openssl.inc; source include/master-slave.inc; # We don't test all types of ssl auth params here since it's a bit hard diff --git a/mysql-test/t/rpl_relayrotate.test b/mysql-test/t/rpl_relayrotate.test index b66cf7a6e0d..04f03367e20 100644 --- a/mysql-test/t/rpl_relayrotate.test +++ b/mysql-test/t/rpl_relayrotate.test @@ -52,13 +52,9 @@ start slave; # which proves that the transaction restarted at # the right place. # We must wait for the transaction to commit before -# reading, MASTER_POS_WAIT() will do it for sure -# (the only statement with position>=3000 is COMMIT). -select master_pos_wait('master-bin.001',3000)>=0; +# reading: +sync_with_master; select max(a) from t1; ---replace_column 1 # 8 # 9 # 23 # 33 # ---replace_result $MASTER_MYPORT MASTER_MYPORT -show slave status; connection master; # The following DROP is a very important cleaning task: 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..8fa330584e2 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 @@ -103,18 +87,18 @@ grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1; 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); +# ToDo: BUG#14931: There is a race between the last grant binlogging, and +# the binlogging in the new connection made below, causing sporadic test +# failures due to switched statement order in binlog. To fix this we do +# SELECT 1 in the first connection before starting the second, ensuring +# that binlogging is done in the expected order. +# Please remove this SELECT 1 when BUG#14931 is fixed. +SELECT 1; -connection master; -set global log_bin_trust_routine_creators=1; +connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,); connection con1; +# this routine will fail in the second INSERT because of privileges delimiter |; create procedure foo4() deterministic @@ -128,29 +112,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 +135,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 +178,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 +211,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 +286,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 +341,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; @@ -280,4 +368,40 @@ connection master; drop function fn1; drop database mysqltest1; drop user "zedjzlcsjhd"@127.0.0.1; +use test; +sync_slave_with_master; +use test; + +# +# Bug#14077 "Failure to replicate a stored function with a cursor": +# verify that stored routines with cursors work on slave. +# +connection master; +--disable_warnings +drop function if exists f1; +--enable_warnings +delimiter |; +create function f1() returns int reads sql data +begin + declare var integer; + declare c cursor for select a from v1; + open c; + fetch c into var; + close c; + return var; +end| +delimiter ;| +create view v1 as select 1 as a; +create table t1 (a int); +insert into t1 (a) values (f1()); +select * from t1; +drop view v1; +drop function f1; sync_slave_with_master; +connection slave; +select * from t1; + +# cleanup +connection master; +drop table t1; +reset master; diff --git a/mysql-test/t/rpl_trigger.test b/mysql-test/t/rpl_trigger.test index 715222f0314..fa6054372c7 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; @@ -111,6 +134,35 @@ drop function bug12480; drop table t1; # +# #14614: Replication of tables with trigger generates error message if databases is changed +# Note. The error message is emitted by _myfree() using fprintf() to the stderr +# and because of that does not fall into the .result file. +# + +create table t1 (i int); +create table t2 (i int); + +delimiter |; +create trigger tr1 before insert on t1 for each row +begin + insert into t2 values (1); +end| +delimiter ;| + +create database other; +use other; +insert into test.t1 values (1); + +save_master_pos; +connection slave; +sync_with_master; + +connection master; +use test; +drop table t1,t2; +drop database other; + +# # End of test # save_master_pos; diff --git a/mysql-test/t/rpl_until.test b/mysql-test/t/rpl_until.test index 5bc7a040b1b..e0ecf981fea 100644 --- a/mysql-test/t/rpl_until.test +++ b/mysql-test/t/rpl_until.test @@ -30,7 +30,7 @@ wait_for_slave_to_stop; # here table should be still not deleted select * from t1; --replace_result $MASTER_MYPORT MASTER_MYPORT ---replace_column 1 # 9 # 23 # 33 # +--replace_column 1 # 9 # 11 # 23 # 33 # show slave status; # this should fail right after start @@ -40,7 +40,7 @@ select * from t1; sleep 2; wait_for_slave_to_stop; --replace_result $MASTER_MYPORT MASTER_MYPORT ---replace_column 1 # 9 # 23 # 33 # +--replace_column 1 # 9 # 11 # 23 # 33 # show slave status; # try replicate all until second insert to t2; @@ -49,7 +49,7 @@ sleep 2; wait_for_slave_to_stop; select * from t2; --replace_result $MASTER_MYPORT MASTER_MYPORT ---replace_column 1 # 9 # 23 # 33 # +--replace_column 1 # 9 # 11 # 23 # 33 # show slave status; # clean up diff --git a/mysql-test/t/schema.test b/mysql-test/t/schema.test index d9bd607b2db..a08d9b38935 100644 --- a/mysql-test/t/schema.test +++ b/mysql-test/t/schema.test @@ -1,6 +1,12 @@ # # Just a couple of tests to make sure that schema works. # +# Drop mysqltest1 database, as it can left from the previous tests. +# + +--disable_warnings +drop database if exists mysqltest1; +--enable_warnings create schema foo; show create schema foo; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index a3d83c531d2..a85b82a7767 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2244,6 +2244,26 @@ SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2,t1 WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; DROP TABLE IF EXISTS t1, t2; +# +# Bug #13855 select distinct with group by caused server crash +# +create table t1 (f1 int primary key, f2 int); +create table t2 (f3 int, f4 int, primary key(f3,f4)); +insert into t1 values (1,1); +insert into t2 values (1,1),(1,2); +select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1; +drop table t1,t2; + +# +# Bug #14482 Server crash when subselecting from the same table +# +create table t1 (f1 int,f2 int); +insert into t1 values(1,1); +create table t2 (f3 int, f4 int, primary key(f3,f4)); +insert into t2 values(1,1); +select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2)); +drop table t1,t2; + # End of 4.1 tests # @@ -2702,3 +2722,96 @@ select * from t1 join t2 left join t3 on (t1.a=t3.c); select * from t1 join t2 right join t3 on (t1.a=t3.c); select * from t1 join t2 straight_join t3 on (t1.a=t3.c); drop table t1, t2 ,t3; + +# +# Bug #14093 Query takes a lot of time when date format is not valid +# fix optimizes execution. so here we just check that returned set is +# correct. +create table t1(f1 int, f2 date); +insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), + (4,'2005-10-01'),(5,'2005-12-30'); +# should return all records +select * from t1 where f2 >= 0; +select * from t1 where f2 >= '0000-00-00'; +# should return 4,5 +select * from t1 where f2 >= '2005-09-31'; +select * from t1 where f2 >= '2005-09-3a'; +# should return 1,2,3 +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; + +# +# Bug #15106: lost equality predicate of the form field=const in a join query +# + +CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a)); +CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a)); +CREATE table t3(key_a int4 NOT NULL, key_b int4 NOT NULL, foo varchar(32), + PRIMARY KEY(key_a,key_b)); + +INSERT INTO t1 VALUES (0,''); +INSERT INTO t1 VALUES (1,'i'); +INSERT INTO t1 VALUES (2,'j'); +INSERT INTO t1 VALUES (3,'k'); + +INSERT INTO t2 VALUES (1,'r'); +INSERT INTO t2 VALUES (2,'s'); +INSERT INTO t2 VALUES (3,'t'); + +INSERT INTO t3 VALUES (1,5,'x'); +INSERT INTO t3 VALUES (1,6,'y'); +INSERT INTO t3 VALUES (2,5,'xx'); +INSERT INTO t3 VALUES (2,6,'yy'); +INSERT INTO t3 VALUES (2,7,'zz'); +INSERT INTO t3 VALUES (3,5,'xxx'); + +SELECT t2.key_a,foo + FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a + INNER JOIN t3 ON t1.key_a = t3.key_a + WHERE t2.key_a=2 and key_b=5; +EXPLAIN SELECT t2.key_a,foo + FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a + INNER JOIN t3 ON t1.key_a = t3.key_a + WHERE t2.key_a=2 and key_b=5; + +SELECT t2.key_a,foo + FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a + INNER JOIN t3 ON t1.key_a = t3.key_a + WHERE t2.key_a=2 and key_b=5; +EXPLAIN SELECT t2.key_a,foo + FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a + INNER JOIN t3 ON t1.key_a = t3.key_a + WHERE t2.key_a=2 and key_b=5; + +DROP TABLE t1,t2,t3; + +# +# Bug#15268 Unchecked null value caused server crash +# +create table t1(f1 char, f2 char not null); +insert into t1 values(null,'a'); +create table t2 (f2 char not null); +insert into t2 values('b'); +select * from t1 left join t2 on f1=t2.f2 where t1.f2='a'; +drop table t1,t2; diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 89d281a2c58..4d418303e6d 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -10,6 +10,7 @@ drop table if exists t1,t2; drop table if exists t1aa,t2aa; drop database if exists mysqltest; +drop database if exists mysqltest1; delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; 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/skip_name_resolve.test b/mysql-test/t/skip_name_resolve.test index b67869692d2..3f732c8912b 100644 --- a/mysql-test/t/skip_name_resolve.test +++ b/mysql-test/t/skip_name_resolve.test @@ -15,6 +15,6 @@ DROP USER mysqltest_1@'127.0.0.1/255.255.255.255'; connect (con1, 127.0.0.1, root, , test, $MASTER_MYPORT, ); --replace_column 1 # select user(); ---replace_column 1 # 6 # 3 # +--replace_column 1 <id> 3 <host> 5 <command> 6 <time> 7 <state> 8 <info> show processlist; connection default; diff --git a/mysql-test/t/sp-big.test b/mysql-test/t/sp-big.test index 389a6f04504..90a3a79dd53 100644 --- a/mysql-test/t/sp-big.test +++ b/mysql-test/t/sp-big.test @@ -52,6 +52,9 @@ while ($1) --enable_query_log select count(*) from t1; select count(*) from t2; +--disable_warnings +drop procedure if exists p1; +--enable_warnings delimiter |; create procedure p1() begin diff --git a/mysql-test/t/sp-code.test b/mysql-test/t/sp-code.test new file mode 100644 index 00000000000..6644bc3ab43 --- /dev/null +++ b/mysql-test/t/sp-code.test @@ -0,0 +1,49 @@ +# +# Test the debugging feature "show procedure/function code <name>" +# + +-- source include/is_debug_build.inc + +create procedure empty() +begin +end; +show procedure code empty; +drop procedure empty; + +create function almost_empty() + returns int + return 0; +show function code almost_empty; +drop function almost_empty; + +delimiter //; +create procedure code_sample(x int, out err int, out nulls int) +begin + declare count int default 0; + + set nulls = 0; + begin + declare c cursor for select name from t1; + declare exit handler for not found close c; + + open c; + loop + begin + declare n varchar(20); + declare continue handler for sqlexception set err=1; + + fetch c into n; + if isnull(n) then + set nulls = nulls + 1; + else + set count = count + 1; + update t2 set idx = count where name=n; + end if; + end; + end loop; + end; + select t.name, t.idx from t2 t order by idx asc; +end// +delimiter ;// +show procedure code code_sample; +drop procedure code_sample; diff --git a/mysql-test/t/sp-destruct.test b/mysql-test/t/sp-destruct.test new file mode 100644 index 00000000000..a2a66090866 --- /dev/null +++ b/mysql-test/t/sp-destruct.test @@ -0,0 +1,124 @@ +# +# Destructive stored procedure tests +# +# We do horrible things to the mysql.proc table here, so any unexpected +# failures here might leave it in an undetermined state. +# +# In the case of trouble you might want to skip this. +# + +# We're using --system things that probably doesn't work on Windows. +--source include/not_windows.inc + +# Backup proc table +--system rm -rf var/master-data/mysql/backup +--system mkdir var/master-data/mysql/backup +--system cp var/master-data/mysql/proc.* var/master-data/mysql/backup/ + +use test; + +--disable_warnings +drop procedure if exists bug14233; +drop function if exists bug14233; +drop table if exists t1; +drop view if exists v1; +--enable_warnings + +create procedure bug14233() + set @x = 42; + +create function bug14233_f() returns int + return 42; + +create table t1 (id int); +create trigger t1_ai after insert on t1 for each row call bug14233(); + +# Unsupported tampering with the mysql.proc definition +alter table mysql.proc drop type; +--error ER_SP_PROC_TABLE_CORRUPT +call bug14233(); +--error ER_SP_PROC_TABLE_CORRUPT +create view v1 as select bug14233_f(); +--error ER_SP_PROC_TABLE_CORRUPT +insert into t1 values (0); + +flush table mysql.proc; + +# Thrashing the .frm file +--system echo 'saljdlfa' > var/master-data/mysql/proc.frm +--error ER_NOT_FORM_FILE +call bug14233(); +--error ER_NOT_FORM_FILE +create view v1 as select bug14233_f(); +--error ER_NOT_FORM_FILE +insert into t1 values (0); + + +flush table mysql.proc; + +# Drop the mysql.proc table +--system rm var/master-data/mysql/proc.* +--error ER_NO_SUCH_TABLE +call bug14233(); +--error ER_NO_SUCH_TABLE +create view v1 as select bug14233_f(); +--error ER_NO_SUCH_TABLE +insert into t1 values (0); + +# Restore mysql.proc +--system mv var/master-data/mysql/backup/* var/master-data/mysql/ +--system rmdir var/master-data/mysql/backup + +flush table mysql.proc; +flush privileges; + +delete from mysql.proc where name like 'bug14233%'; + +# Unsupported editing of mysql.proc, circumventing checks in "create ..." +insert into mysql.proc +( + db, name, type, specific_name, language, sql_data_access, is_deterministic, + security_type, param_list, returns, body, definer, created, modified, + sql_mode, comment +) +values +( + 'test', 'bug14233_1', 'FUNCTION', 'bug14233_1', 'SQL', 'READS_SQL_DATA', 'NO', + 'DEFINER', '', 'int(10)', + 'select count(*) from mysql.user', + 'root@localhost', NOW() , '0000-00-00 00:00:00', '', '' +), +( + 'test', 'bug14233_2', 'FUNCTION', 'bug14233_2', 'SQL', 'READS_SQL_DATA', 'NO', + 'DEFINER', '', 'int(10)', + 'begin declare x int; select count(*) into x from mysql.user; end', + 'root@localhost', NOW() , '0000-00-00 00:00:00', '', '' +), +( + 'test', 'bug14233_3', 'PROCEDURE', 'bug14233_3', 'SQL', 'READS_SQL_DATA','NO', + 'DEFINER', '', '', + 'alksj wpsj sa ^#!@ ', + 'root@localhost', NOW() , '0000-00-00 00:00:00', '', '' +); + +--error ER_SP_PROC_TABLE_CORRUPT +select bug14233_1(); +--error ER_SP_PROC_TABLE_CORRUPT +create view v1 as select bug14233_1(); + +--error ER_SP_PROC_TABLE_CORRUPT +select bug14233_2(); +--error ER_SP_PROC_TABLE_CORRUPT +create view v1 as select bug14233_2(); + +--error ER_SP_PROC_TABLE_CORRUPT +call bug14233_3(); +drop trigger t1_ai; +create trigger t1_ai after insert on t1 for each row call bug14233_3(); +--error ER_SP_PROC_TABLE_CORRUPT +insert into t1 values (0); + +# Clean-up +delete from mysql.proc where name like 'bug14233%'; +drop trigger t1_ai; +drop table t1; diff --git a/mysql-test/t/sp-dynamic.test b/mysql-test/t/sp-dynamic.test index e9816ee3ef0..6546a5ab548 100644 --- a/mysql-test/t/sp-dynamic.test +++ b/mysql-test/t/sp-dynamic.test @@ -1,4 +1,10 @@ delimiter |; + +--disable_warnings +drop procedure if exists p1| +drop procedure if exists p2| +--enable_warnings + ###################################################################### # Test Dynamic SQL in stored procedures. ############################# ###################################################################### @@ -26,18 +32,29 @@ begin execute stmt; end| prepare stmt from "call p1()"| +# Allow SP resursion to be show that it has not influence here +set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth| +set @@max_sp_recursion_depth=100| --error ER_PS_NO_RECURSION execute stmt| --error ER_PS_NO_RECURSION execute stmt| --error ER_PS_NO_RECURSION execute stmt| ---error ER_SP_NO_RECURSION +--error ER_PS_NO_RECURSION call p1()| ---error ER_SP_NO_RECURSION +--error ER_PS_NO_RECURSION +call p1()| +--error ER_PS_NO_RECURSION call p1()| ---error ER_SP_NO_RECURSION +set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS| +--error ER_SP_RECURSION_LIMIT call p1()| +--error ER_SP_RECURSION_LIMIT +call p1()| +--error ER_SP_RECURSION_LIMIT +call p1()| + drop procedure p1| # # C. Create/drop a stored procedure in Dynamic SQL. diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index e2343cd905c..cf8f8dfc79c 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1044,10 +1044,11 @@ begin call bug11394(i - 1,(select 1)); end if; end| -# Again if we allow recursion for stored procedures (without -# additional efforts) the following statement will crash the server. ---error 1424 +--error ER_SP_RECURSION_LIMIT +call bug11394(2, 1)| +set @@max_sp_recursion_depth=10| call bug11394(2, 1)| +set @@max_sp_recursion_depth=default| drop procedure bug11394| delimiter ;| @@ -1094,7 +1095,7 @@ delimiter |; --disable_warnings DROP FUNCTION IF EXISTS bug12953| --enable_warnings ---error ER_SP_BADSTATEMENT +--error ER_SP_NO_RETSET CREATE FUNCTION bug12953() RETURNS INT BEGIN OPTIMIZE TABLE t1; @@ -1233,6 +1234,10 @@ begin select password; end| +# Check that an error message is sent +--error ER_PARSE_ERROR +set names='foo2'| + --error ER_SP_BAD_VAR_SHADOW create procedure bug13510_2() begin @@ -1263,8 +1268,167 @@ call bug13510_4()| drop procedure bug13510_3| drop procedure bug13510_4| + + +# +# Test that statements which implicitly commit transaction are prohibited +# in stored function and triggers. Attempt to create function or trigger +# containing such statement should produce error (includes test for +# bug #13627). +# +--disable_warnings +drop function if exists bug_13627_f| +--enable_warnings + +CREATE TABLE t1 (a int)| +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DROP TRIGGER test1; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN DROP TRIGGER test1; return 1; END | + +-- error ER_SP_BADSTATEMENT +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN load table t1 from master; END | +-- error ER_SP_BADSTATEMENT +CREATE FUNCTION bug_13627_f() returns int BEGIN load table t1 from master; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create table t2 (a int); END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN create table t2 (a int); return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create index t1_i on t1 (a); END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN create index t1_i on t1 (a); return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter table t1 add column b int; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN alter table t1 add column b int; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename table t1 to t2; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN rename table t1 to t2; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN truncate table t1; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN truncate table t1; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop table t1; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN drop table t1; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop index t1_i on t1; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN drop index t1_i on t1; return 1; END | + +-- error ER_SP_BADSTATEMENT +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN unlock tables; END | +-- error ER_SP_BADSTATEMENT +CREATE FUNCTION bug_13627_f() returns int BEGIN unlock tables; return 1; END | + +-- error ER_SP_BADSTATEMENT +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN LOCK TABLE t1 READ; END | +-- error ER_SP_BADSTATEMENT +CREATE FUNCTION bug_13627_f() returns int BEGIN LOCK TABLE t1 READ; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create database mysqltest; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN create database mysqltest; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop database mysqltest; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN drop database mysqltest; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create user 'mysqltest_1'; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN create user 'mysqltest_1'; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop user 'mysqltest_1'; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN drop user 'mysqltest_1'; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as select 1; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END | + +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop view v1; END | +-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION bug_13627_f() returns int BEGIN drop view v1; return 1; END | + +-- error ER_SP_NO_RECURSIVE_CREATE +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create trigger tr2 before insert on t1 for each row do select 1; END | +-- error ER_SP_NO_RECURSIVE_CREATE +CREATE FUNCTION bug_13627_f() returns int BEGIN create trigger tr2 before insert on t1 for each row do select 1; return 1; END | + +-- error ER_SP_NO_DROP_SP +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop function bug_13627_f; END | +-- error ER_SP_NO_DROP_SP +CREATE FUNCTION bug_13627_f() returns int BEGIN drop function bug_13627_f; return 1; END | + +-- error ER_SP_NO_RECURSIVE_CREATE +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create function f2 () returns int return 1; END | +-- error ER_SP_NO_RECURSIVE_CREATE +CREATE FUNCTION bug_13627_f() returns int BEGIN create function f2 () returns int return 1; return 1; END | + +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW + BEGIN + CREATE TEMPORARY TABLE t2 (a int); + DROP TEMPORARY TABLE t2; + END | +CREATE FUNCTION bug_13627_f() returns int + BEGIN + CREATE TEMPORARY TABLE t2 (a int); + DROP TEMPORARY TABLE t2; + return 1; + END | + +drop table t1| +drop function bug_13627_f| + delimiter ;| +# BUG#12329: "Bogus error msg when executing PS with stored procedure after +# SP was re-created". See also test for related bug#13399 in trigger.test +drop function if exists bug12329; +--enable_warnings +create table t1 as select 1 a; +create table t2 as select 1 a; +create function bug12329() returns int return (select a from t1); +prepare stmt1 from 'select bug12329()'; +execute stmt1; +drop function bug12329; +create function bug12329() returns int return (select a+100 from t2); +select bug12329(); +# Until we implement proper mechanism for invalidation of PS/SP when table +# or SP's are changed the following statement will fail with 'Table ... was +# not locked' error (this mechanism should be based on the new TDC). +--error 1100 +execute stmt1; +deallocate prepare stmt1; +drop function bug12329; +drop table t1, t2; + # # Bug#13514 "server crash when create a stored procedure before choose a # database" and @@ -1337,6 +1501,121 @@ 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#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server" +# +delimiter |; +--disable_warnings +DROP FUNCTION IF EXISTS bug13012| +--enable_warnings +--error ER_SP_NO_RETSET +CREATE FUNCTION bug13012() RETURNS INT +BEGIN + REPAIR TABLE t1; + RETURN 1; +END| +--error ER_SP_NO_RETSET +CREATE FUNCTION bug13012() RETURNS INT +BEGIN + BACKUP TABLE t1 TO '/tmp'; + RETURN 1; +END| +--error ER_SP_NO_RETSET +CREATE FUNCTION bug13012() RETURNS INT +BEGIN + RESTORE TABLE t1 FROM '/tmp'; + RETURN 1; +END| +create table t1 (a int)| +CREATE PROCEDURE bug13012_1() REPAIR TABLE t1| +CREATE FUNCTION bug13012_2() RETURNS INT +BEGIN + CALL bug13012_1(); + RETURN 1; +END| +--error ER_SP_NO_RETSET +SELECT bug13012_2()| +drop table t1| +drop procedure bug13012_1| +drop function bug13012_2| +delimiter ;| + +# BUG#11555 "Stored procedures: current SP tables locking make +# impossible view security". We should not expose names of tables +# which are implicitly used by view (via stored routines/triggers). +# +# Note that SQL standard assumes that you simply won't be able drop table +# and leave some objects (routines/views/triggers) which were depending on +# it. Such objects should be dropped in advance (by default) or will be +# dropped simultaneously with table (DROP TABLE with CASCADE clause). +# So these tests probably should go away once we will implement standard +# behavior. +--disable_warnings +drop function if exists bug11555_1; +drop function if exists bug11555_2; +drop view if exists v1, v2, v3, v4; +--enable_warnings +create function bug11555_1() returns int return (select max(i) from t1); +create function bug11555_2() returns int return bug11555_1(); +# It is OK to report name of implicitly used table which is missing +# when we create view. +--error ER_NO_SUCH_TABLE +create view v1 as select bug11555_1(); +--error ER_NO_SUCH_TABLE +create view v2 as select bug11555_2(); +# But we should hide name of missing implicitly used table when we use view +create table t1 (i int); +create view v1 as select bug11555_1(); +create view v2 as select bug11555_2(); +create view v3 as select * from v1; +drop table t1; +--error ER_VIEW_INVALID +select * from v1; +--error ER_VIEW_INVALID +select * from v2; +--error ER_VIEW_INVALID +select * from v3; +# Note that creation of view which depends on broken view is yet +# another form of view usage. +--error ER_VIEW_INVALID +create view v4 as select * from v1; +drop view v1, v2, v3; +# We also should hide details about broken triggers which are +# invoked for view. +drop function bug11555_1; +drop function bug11555_2; +create table t1 (i int); +create table t2 (i int); +create trigger t1_ai after insert on t1 for each row insert into t2 values (new.i); +create view v1 as select * from t1; +drop table t2; +--error ER_VIEW_INVALID +insert into v1 values (1); +drop trigger t1_ai; +create function bug11555_1() returns int return (select max(i) from t2); +create trigger t1_ai after insert on t1 for each row set @a:=bug11555_1(); +--error ER_VIEW_INVALID +insert into v1 values (2); +drop function bug11555_1; +drop table t1; +drop view v1; + # BUG#NNNN: New bug synopsis # diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index afbc383a17a..8d4f99abd71 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -336,6 +336,7 @@ connection user1; do 1; use test; +disconnect user1; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; drop function bug_9503; diff --git a/mysql-test/t/sp-vars.test b/mysql-test/t/sp-vars.test new file mode 100644 index 00000000000..81504904797 --- /dev/null +++ b/mysql-test/t/sp-vars.test @@ -0,0 +1,1273 @@ +########################################################################### +# +# Cleanup. +# +########################################################################### + +--disable_warnings + +# Drop stored routines (if any) for general SP-vars test cases. These routines +# are created in include/sp-vars.inc file. + +DROP PROCEDURE IF EXISTS sp_vars_check_dflt; +DROP PROCEDURE IF EXISTS sp_vars_check_assignment; +DROP FUNCTION IF EXISTS sp_vars_check_ret1; +DROP FUNCTION IF EXISTS sp_vars_check_ret2; +DROP FUNCTION IF EXISTS sp_vars_check_ret3; +DROP FUNCTION IF EXISTS sp_vars_check_ret4; + +--enable_warnings + +########################################################################### +# +# Some general tests for SP-vars functionality. +# +########################################################################### + +# Create the procedure in ANSI mode. Check that all necessary warnings are +# emitted properly. + +SET @@sql_mode = 'ansi'; + +--source include/sp-vars.inc + +--echo +--echo --------------------------------------------------------------- +--echo Calling the routines, created in ANSI mode. +--echo --------------------------------------------------------------- +--echo + +CALL sp_vars_check_dflt(); + +CALL sp_vars_check_assignment(); + +SELECT sp_vars_check_ret1(); + +SELECT sp_vars_check_ret2(); + +SELECT sp_vars_check_ret3(); + +SELECT sp_vars_check_ret4(); + +# Check that changing sql_mode after creating a store procedure does not +# matter. + +SET @@sql_mode = 'traditional'; + +--echo +--echo --------------------------------------------------------------- +--echo Calling in TRADITIONAL mode the routines, created in ANSI mode. +--echo --------------------------------------------------------------- +--echo + +CALL sp_vars_check_dflt(); + +CALL sp_vars_check_assignment(); + +SELECT sp_vars_check_ret1(); + +SELECT sp_vars_check_ret2(); + +SELECT sp_vars_check_ret3(); + +SELECT sp_vars_check_ret4(); + +# Create the procedure in TRADITIONAL mode. Check that error will be thrown on +# execution. + +DROP PROCEDURE sp_vars_check_dflt; +DROP PROCEDURE sp_vars_check_assignment; +DROP FUNCTION sp_vars_check_ret1; +DROP FUNCTION sp_vars_check_ret2; +DROP FUNCTION sp_vars_check_ret3; +DROP FUNCTION sp_vars_check_ret4; + +--source include/sp-vars.inc + +--echo +--echo --------------------------------------------------------------- +--echo Calling the routines, created in TRADITIONAL mode. +--echo --------------------------------------------------------------- +--echo + +--error ER_WARN_DATA_OUT_OF_RANGE +CALL sp_vars_check_dflt(); + +--error ER_WARN_DATA_OUT_OF_RANGE +CALL sp_vars_check_assignment(); + +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT sp_vars_check_ret1(); + +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT sp_vars_check_ret2(); + +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +SELECT sp_vars_check_ret3(); + +# TODO: Is it an error, that only a warning is emitted here? Check the same +# behaviour with tables. + +SELECT sp_vars_check_ret4(); + +SET @@sql_mode = 'ansi'; + +# +# Cleanup. +# + +DROP PROCEDURE sp_vars_check_dflt; +DROP PROCEDURE sp_vars_check_assignment; +DROP FUNCTION sp_vars_check_ret1; +DROP FUNCTION sp_vars_check_ret2; +DROP FUNCTION sp_vars_check_ret3; +DROP FUNCTION sp_vars_check_ret4; + +########################################################################### +# +# Tests for BIT data type. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BIT data type tests +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + DECLARE v1 BIT; + DECLARE v2 BIT(1); + DECLARE v3 BIT(3) DEFAULT b'101'; + DECLARE v4 BIT(64) DEFAULT 0x5555555555555555; + DECLARE v5 BIT(3); + DECLARE v6 BIT(64); + DECLARE v7 BIT(8) DEFAULT 128; + DECLARE v8 BIT(8) DEFAULT '128'; + DECLARE v9 BIT(8) DEFAULT ' 128'; + DECLARE v10 BIT(8) DEFAULT 'x 128'; + + SET v1 = v4; + SET v2 = 0; + SET v5 = v4; # check overflow + SET v6 = v3; # check padding + + SELECT HEX(v1); + SELECT HEX(v2); + SELECT HEX(v3); + SELECT HEX(v4); + SELECT HEX(v5); + SELECT HEX(v6); + SELECT HEX(v7); + SELECT HEX(v8); + SELECT HEX(v9); + SELECT HEX(v10); +END| +delimiter ;| + +CALL p1(); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Tests for CASE statements functionality: +# - test for general functionality (scopes, nested cases, CASE in loops); +# - test that if type of the CASE expression is changed on each iteration, +# the execution will be correct. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo CASE expression tests. +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP TABLE IF EXISTS t1; + +# +# Test case. +# + +CREATE TABLE t1(log_msg VARCHAR(1024)); + +delimiter |; + +CREATE PROCEDURE p1(arg VARCHAR(255)) +BEGIN + INSERT INTO t1 VALUES('p1: step1'); + + CASE arg * 10 + WHEN 10 * 10 THEN + INSERT INTO t1 VALUES('p1: case1: on 10'); + WHEN 10 * 10 + 10 * 10 THEN + BEGIN + CASE arg / 10 + WHEN 1 THEN + INSERT INTO t1 VALUES('p1: case1: case2: on 1'); + WHEN 2 THEN + BEGIN + DECLARE i TINYINT DEFAULT 10; + + WHILE i > 0 DO + INSERT INTO t1 VALUES(CONCAT('p1: case1: case2: loop: i: ', i)); + + CASE MOD(i, 2) + WHEN 0 THEN + INSERT INTO t1 VALUES('p1: case1: case2: loop: i is even'); + WHEN 1 THEN + INSERT INTO t1 VALUES('p1: case1: case2: loop: i is odd'); + ELSE + INSERT INTO t1 VALUES('p1: case1: case2: loop: ERROR'); + END CASE; + + SET i = i - 1; + END WHILE; + END; + ELSE + INSERT INTO t1 VALUES('p1: case1: case2: ERROR'); + END CASE; + + CASE arg + WHEN 10 THEN + INSERT INTO t1 VALUES('p1: case1: case3: on 10'); + WHEN 20 THEN + INSERT INTO t1 VALUES('p1: case1: case3: on 20'); + ELSE + INSERT INTO t1 VALUES('p1: case1: case3: ERROR'); + END CASE; + END; + ELSE + INSERT INTO t1 VALUES('p1: case1: ERROR'); + END CASE; + + CASE arg * 10 + WHEN 10 * 10 THEN + INSERT INTO t1 VALUES('p1: case4: on 10'); + WHEN 10 * 10 + 10 * 10 THEN + BEGIN + CASE arg / 10 + WHEN 1 THEN + INSERT INTO t1 VALUES('p1: case4: case5: on 1'); + WHEN 2 THEN + BEGIN + DECLARE i TINYINT DEFAULT 10; + + WHILE i > 0 DO + INSERT INTO t1 VALUES(CONCAT('p1: case4: case5: loop: i: ', i)); + + CASE MOD(i, 2) + WHEN 0 THEN + INSERT INTO t1 VALUES('p1: case4: case5: loop: i is even'); + WHEN 1 THEN + INSERT INTO t1 VALUES('p1: case4: case5: loop: i is odd'); + ELSE + INSERT INTO t1 VALUES('p1: case4: case5: loop: ERROR'); + END CASE; + + SET i = i - 1; + END WHILE; + END; + ELSE + INSERT INTO t1 VALUES('p1: case4: case5: ERROR'); + END CASE; + + CASE arg + WHEN 10 THEN + INSERT INTO t1 VALUES('p1: case4: case6: on 10'); + WHEN 20 THEN + INSERT INTO t1 VALUES('p1: case4: case6: on 20'); + ELSE + INSERT INTO t1 VALUES('p1: case4: case6: ERROR'); + END CASE; + END; + ELSE + INSERT INTO t1 VALUES('p1: case4: ERROR'); + END CASE; +END| + +CREATE PROCEDURE p2() +BEGIN + DECLARE i TINYINT DEFAULT 3; + + WHILE i > 0 DO + IF MOD(i, 2) = 0 THEN + SET @_test_session_var = 10; + ELSE + SET @_test_session_var = 'test'; + END IF; + + CASE @_test_session_var + WHEN 10 THEN + INSERT INTO t1 VALUES('p2: case: numerical type'); + WHEN 'test' THEN + INSERT INTO t1 VALUES('p2: case: string type'); + ELSE + INSERT INTO t1 VALUES('p2: case: ERROR'); + END CASE; + + SET i = i - 1; + END WHILE; +END| + +delimiter ;| + +CALL p1(10); +CALL p1(20); + +CALL p2(); + +SELECT * FROM t1; + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; + +########################################################################### +# +# Test case for BUG#14161: Stored procedure cannot retrieve bigint unsigned. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#14161 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(col BIGINT UNSIGNED); + +INSERT INTO t1 VALUE(18446744073709551614); + +delimiter |; +CREATE PROCEDURE p1(IN arg BIGINT UNSIGNED) +BEGIN + SELECT arg; + SELECT * FROM t1; + SELECT * FROM t1 WHERE col = arg; +END| +delimiter ;| + +CALL p1(18446744073709551614); + +# +# Cleanup. +# + +DROP TABLE t1; +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#13705: parameters to stored procedures are not verified. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13705 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1(x VARCHAR(10), y CHAR(3)) READS SQL DATA +BEGIN + SELECT x, y; +END| +delimiter ;| + +CALL p1('alpha', 'abc'); +CALL p1('alpha', 'abcdef'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#13675: DATETIME/DATE type in store proc param seems to be +# converted as varbinary. +# +# TODO: test case failed. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13675 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1(x DATETIME) +BEGIN + CREATE TABLE t1 SELECT x; + SHOW CREATE TABLE t1; + DROP TABLE t1; +END| +delimiter ;| + +CALL p1(NOW()); + +CALL p1('test'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#12976: Boolean values reversed in stored procedures? +# +# TODO: test case failed. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#12976 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(b BIT(1)); + +INSERT INTO t1(b) VALUES(b'0'), (b'1'); + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + SELECT HEX(b), + b = 0, + b = FALSE, + b IS FALSE, + b = 1, + b = TRUE, + b IS TRUE + FROM t1; +END| + +CREATE PROCEDURE p2() +BEGIN + DECLARE vb BIT(1); + SELECT b INTO vb FROM t1 WHERE b = 0; + + SELECT HEX(vb), + vb = 0, + vb = FALSE, + vb IS FALSE, + vb = 1, + vb = TRUE, + vb IS TRUE; + + SELECT b INTO vb FROM t1 WHERE b = 1; + + SELECT HEX(vb), + vb = 0, + vb = FALSE, + vb IS FALSE, + vb = 1, + vb = TRUE, + vb IS TRUE; +END| +delimiter ;| + +# The expected and correct result. + +call p1(); + +# The wrong result. Note that only hex(vb) works, but is printed with two +# digits for some reason in this case. + +call p2(); + +# +# Cleanup. +# + +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +########################################################################### +# +# Test case for BUG#9572: Stored procedures: variable type declarations +# ignored. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#9572 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; + +DROP PROCEDURE IF EXISTS p4; +DROP PROCEDURE IF EXISTS p5; +DROP PROCEDURE IF EXISTS p6; +--enable_warnings + +# +# Test case. +# + +SET @@sql_mode = 'traditional'; + +delimiter |; + +CREATE PROCEDURE p1() +BEGIN + DECLARE v TINYINT DEFAULT 1e200; + SELECT v; +END| + +CREATE PROCEDURE p2() +BEGIN + DECLARE v DECIMAL(5) DEFAULT 1e200; + SELECT v; +END| + +CREATE PROCEDURE p3() +BEGIN + DECLARE v CHAR(5) DEFAULT 'abcdef'; + SELECT v LIKE 'abc___'; +END| + +CREATE PROCEDURE p4(arg VARCHAR(2)) +BEGIN + DECLARE var VARCHAR(1); + SET var := arg; + SELECT arg, var; +END| + +CREATE PROCEDURE p5(arg CHAR(2)) +BEGIN + DECLARE var CHAR(1); + SET var := arg; + SELECT arg, var; +END| + +CREATE PROCEDURE p6(arg DECIMAL(2)) +BEGIN + DECLARE var DECIMAL(1); + SET var := arg; + SELECT arg, var; +END| + +delimiter ;| + +--error ER_WARN_DATA_OUT_OF_RANGE +CALL p1(); +--error ER_WARN_DATA_OUT_OF_RANGE +CALL p2(); +--error ER_DATA_TOO_LONG +CALL p3(); + +--error ER_DATA_TOO_LONG +CALL p4('aaa'); +--error ER_DATA_TOO_LONG +CALL p5('aa'); +--error ER_WARN_DATA_OUT_OF_RANGE +CALL p6(10); + +# +# Cleanup. +# + +SET @@sql_mode = 'ansi'; + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; + +DROP PROCEDURE p4; +DROP PROCEDURE p5; +DROP PROCEDURE p6; + +########################################################################### +# +# Test case for BUG#9078: STORED PROCDURE: Decimal digits are not displayed +# when we use DECIMAL datatype. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#9078 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1 (arg DECIMAL(64,2)) +BEGIN + DECLARE var DECIMAL(64,2); + + SET var = arg; + SELECT var; +END| +delimiter ;| + +CALL p1(1929); +CALL p1(1929.00); +CALL p1(1929.003); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#8768: Functions: For any unsigned data type, -ve values can +# be passed and returned. +# +# TODO: there is a bug here -- the function created in ANSI mode should not +# throw errors instead of warnings if called in TRADITIONAL mode. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#8768 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +# Create a function in ANSI mode. + +delimiter |; +CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT +BEGIN + RETURN arg; +END| +delimiter ;| + +SELECT f1(-2500); + +# Call in TRADITIONAL mode the function created in ANSI mode. + +SET @@sql_mode = 'traditional'; + +# TODO: a warning should be emitted here. +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT f1(-2500); + +# Recreate the function in TRADITIONAL mode. + +DROP FUNCTION f1; + +delimiter |; +CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT +BEGIN + RETURN arg; +END| +delimiter ;| + +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT f1(-2500); + +# +# Cleanup. +# + +SET @@sql_mode = 'ansi'; + +DROP FUNCTION f1; + +########################################################################### +# +# Test case for BUG#8769: Functions: For Int datatypes, out of range values can +# be passed and returned. +# +# TODO: there is a bug here -- the function created in ANSI mode should not +# throw errors instead of warnings if called in TRADITIONAL mode. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#8769 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +# Create a function in ANSI mode. + +delimiter |; +CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT +BEGIN + RETURN arg; +END| +delimiter ;| + +SELECT f1(8388699); + +# Call in TRADITIONAL mode the function created in ANSI mode. + +SET @@sql_mode = 'traditional'; + +# TODO: a warning should be emitted here. +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT f1(8388699); + +# Recreate the function in TRADITIONAL mode. + +DROP FUNCTION f1; + +delimiter |; +CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT +BEGIN + RETURN arg; +END| +delimiter ;| + +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT f1(8388699); + +# +# Cleanup. +# + +SET @@sql_mode = 'ansi'; + +DROP FUNCTION f1; + +########################################################################### +# +# Test case for BUG#8702: Stored Procedures: No Error/Warning shown for +# inappropriate data type matching. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#8702 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(col VARCHAR(255)); + +INSERT INTO t1(col) VALUES('Hello, world!'); + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + DECLARE sp_var INTEGER; + + SELECT col INTO sp_var FROM t1 LIMIT 1; + SET @user_var = sp_var; + + SELECT sp_var; + SELECT @user_var; +END| +delimiter ;| + +CALL p1(); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP TABLE t1; + +########################################################################### +# +# Test case for BUG#12903: upper function does not work inside a function. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#12903 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(txt VARCHAR(255)); + +delimiter |; +CREATE FUNCTION f1(arg VARCHAR(255)) RETURNS VARCHAR(255) +BEGIN + DECLARE v1 VARCHAR(255); + DECLARE v2 VARCHAR(255); + + SET v1 = CONCAT(LOWER(arg), UPPER(arg)); + SET v2 = CONCAT(LOWER(v1), UPPER(v1)); + + INSERT INTO t1 VALUES(v1), (v2); + + RETURN CONCAT(LOWER(arg), UPPER(arg)); +END| +delimiter ;| + +SELECT f1('_aBcDe_'); + +SELECT * FROM t1; + +# +# Cleanup. +# + +DROP FUNCTION f1; +DROP TABLE t1; + +########################################################################### +# +# Test case for BUG#13808: ENUM type stored procedure parameter accepts +# non-enumerated data. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13808 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +delimiter |; + +CREATE PROCEDURE p1(arg ENUM('a', 'b')) +BEGIN + SELECT arg; +END| + +CREATE PROCEDURE p2(arg ENUM('a', 'b')) +BEGIN + DECLARE var ENUM('c', 'd') DEFAULT arg; + + SELECT arg, var; +END| + +CREATE FUNCTION f1(arg ENUM('a', 'b')) RETURNS ENUM('c', 'd') +BEGIN + RETURN arg; +END| + +delimiter ;| + +CALL p1('c'); + +CALL p2('a'); + +SELECT f1('a'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP FUNCTION f1; + +########################################################################### +# +# Test case for BUG#13909: Varchar Stored Procedure Parameter always BINARY +# string (ignores CHARACTER SET). +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13909 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings + +# +# Test case. +# + +delimiter |; + +CREATE PROCEDURE p1(arg VARCHAR(255)) +BEGIN + SELECT CHARSET(arg); +END| + +CREATE PROCEDURE p2(arg VARCHAR(255) CHARACTER SET UTF8) +BEGIN + SELECT CHARSET(arg); +END| + +delimiter ;| + +CALL p1('t'); +CALL p1(_UTF8 't'); + + +CALL p2('t'); +CALL p2(_LATIN1 't'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +########################################################################### +# +# Test case for BUG#14188: BINARY variables have no 0x00 padding. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#14188 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1(arg1 BINARY(2), arg2 VARBINARY(2)) +BEGIN + DECLARE var1 BINARY(2) DEFAULT 0x41; + DECLARE var2 VARBINARY(2) DEFAULT 0x42; + + SELECT HEX(arg1), HEX(arg2); + SELECT HEX(var1), HEX(var2); +END| +delimiter ;| + +CALL p1(0x41, 0x42); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#15148: Stored procedure variables accept non-scalar values. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#15148 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(col1 TINYINT, col2 TINYINT); + +INSERT INTO t1 VALUES(1, 2), (11, 12); + +delimiter |; +CREATE PROCEDURE p1(arg TINYINT) +BEGIN + SELECT arg; +END| +delimiter ;| + +--error ER_OPERAND_COLUMNS +CALL p1((1, 2)); + +--error ER_OPERAND_COLUMNS +CALL p1((SELECT * FROM t1 LIMIT 1)); + +--error ER_OPERAND_COLUMNS +CALL p1((SELECT col1, col2 FROM t1 LIMIT 1)); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP TABLE t1; + +########################################################################### +# +# Test case for BUG#13613: substring function in stored procedure. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13613 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +delimiter |; + +CREATE PROCEDURE p1(x VARCHAR(50)) +BEGIN + SET x = SUBSTRING(x, 1, 3); + SELECT x; +END| + +CREATE FUNCTION f1(x VARCHAR(50)) RETURNS VARCHAR(50) +BEGIN + RETURN SUBSTRING(x, 1, 3); +END| + +delimiter ;| + +CALL p1('abcdef'); + +SELECT f1('ABCDEF'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP FUNCTION f1; + +########################################################################### +# +# Test case for BUG#13665: concat with '' produce incorrect results in SP. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13665 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE FUNCTION f1() RETURNS VARCHAR(20000) +BEGIN + DECLARE var VARCHAR(2000); + + SET var = ''; + SET var = CONCAT(var, 'abc'); + SET var = CONCAT(var, ''); + + RETURN var; +END| +delimiter ;| + +SELECT f1(); + +# +# Cleanup. +# + +DROP FUNCTION f1; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index ab57139bb77..f73288f04ba 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -13,6 +13,8 @@ # Tests that require multiple connections, except security/privilege tests, # go to sp-thread. # Tests that uses 'goto' to into sp-goto.test (currently disabled) +# Tests that destroys system tables (e.g. mysql.proc) for error testing +# go to sp-destruct. use test; @@ -365,6 +367,7 @@ create function sub3(i int) returns int call sub1("sub1a", (select 7))| call sub1("sub1b", (select max(i) from t2))| +--error ER_OPERAND_COLUMNS call sub1("sub1c", (select i,d from t2 limit 1))| call sub1("sub1d", (select 1 from (select 1) a))| call sub2("sub2")| @@ -1211,15 +1214,13 @@ begin end if; end| select f5(1)| -# This should generate an error about insuficient number of tables locked -# Now this crash server ---disable_parsing # until bug#11394 fix ---error 1100 +# Since currently recursive functions are disallowed ER_SP_NO_RECURSION +# error will be returned, once we will allow them error about +# insufficient number of locked tables will be returned instead. +--error ER_SP_NO_RECURSION select f5(2)| -# But now it simply miserably fails because we are trying to use the same -# lex on the next iteration :/ It should generate some error too... +--error ER_SP_NO_RECURSION select f5(3)| ---enable_parsing # OTOH this should work create function f6() returns int @@ -1265,13 +1266,12 @@ select * from v1| # views and functions ? create function f1() returns int return (select sum(data) from t1) + (select sum(data) from v1)| -# This queries will crash server because we can't use LEX in -# reenterable fashion yet. Patch disabling recursion will heal this. ---disable_parsing +--error ER_SP_NO_RECURSION select f1()| +--error ER_SP_NO_RECURSION select * from v1| +--error ER_SP_NO_RECURSION select * from v2| ---enable_parsing # Back to the normal cases drop function f1| create function f1() returns int @@ -1289,9 +1289,7 @@ select *, f0() from v0| # # Let us test how well prelocking works with explicit LOCK TABLES. # -# Nowdays we have to lock mysql.proc to be able to read SP definitions. -# But Monty was going to fix this. -lock tables t1 read, t1 as t11 read, mysql.proc read| +lock tables t1 read, t1 as t11 read| # These should work well select f3()| select id, f3() from t1 as t11| @@ -1481,9 +1479,6 @@ show procedure status like '%p%'| # Fibonacci, for recursion test. (Yet Another Numerical series :) # -# This part of test is disabled until we implement support for -# recursive stored procedures. ---disable_parsing --disable_warnings drop table if exists fib| --enable_warnings @@ -1512,6 +1507,9 @@ begin end if; end| +# Enable recursion +set @@max_sp_recursion_depth= 20| + # Minimum test: recursion of 3 levels insert into fib values (0), (1)| @@ -1531,7 +1529,7 @@ call fib(20)| select * from fib order by f asc| drop table fib| drop procedure fib| ---enable_parsing +set @@max_sp_recursion_depth= 0| # # Comment & suid @@ -1800,16 +1798,8 @@ select @x2| drop procedure bug2260| # -# BUG#2267 +# BUG#2267 "Lost connect if stored procedure has SHOW FUNCTION STATUS" # -# NOTE: This test case will be fixed as soon as Monty -# will allow to open mysql.proc table under LOCK TABLES -# without mentioning in lock list. -# -# FIXME: Other solution would be to use preopened proc table -# instead of opening it anew. -# ---disable_parsing --disable_warnings drop procedure if exists bug2267_1| --enable_warnings @@ -1836,11 +1826,13 @@ end| --disable_warnings drop procedure if exists bug2267_4| +drop function if exists bug2267_4| --enable_warnings create procedure bug2267_4() begin - show create function fac; + show create function bug2267_4; end| +create function bug2267_4() returns int return 100| --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' call bug2267_1()| @@ -1853,7 +1845,7 @@ drop procedure bug2267_1| drop procedure bug2267_2| drop procedure bug2267_3| drop procedure bug2267_4| ---enable_parsing +drop function bug2267_4| # # BUG#2227 @@ -1873,23 +1865,16 @@ call bug2227(9)| drop procedure bug2227| # -# BUG#2614 +# BUG#2614 "Stored procedure with INSERT ... SELECT that does not +# contain any tables crashes server" # -# QQ The second insert doesn't work with temporary tables (it was an -# QQ ordinary table before we changed the locking scheme). It results -# QQ in an error: 1137: Can't reopen table: 't3' -# QQ which is a known limit with temporary tables. -# QQ For this reason we can't run this test any more (i.e., if we modify -# QQ it, it's no longer a test case for the bug), but we keep it here -# QQ anyway, for tracability. ---disable_parsing --disable_warnings drop procedure if exists bug2614| --enable_warnings create procedure bug2614() begin - drop temporary table if exists t3; - create temporary table t3 (id int default '0' not null); + drop table if exists t3; + create table t3 (id int default '0' not null); insert into t3 select 12; insert into t3 select * from t3; end| @@ -1898,9 +1883,8 @@ end| call bug2614()| --enable_warnings call bug2614()| -drop temporary table t3| +drop table t3| drop procedure bug2614| ---enable_parsing # # BUG#2674 @@ -4291,6 +4275,9 @@ call bug12589_1()| # No warnings here call bug12589_2()| call bug12589_3()| +drop procedure bug12589_1| +drop procedure bug12589_2| +drop procedure bug12589_3| # # BUG#7049: Stored procedure CALL errors are ignored @@ -4488,6 +4475,608 @@ DROP TABLE IF EXISTS bug13095_t1; delimiter |; +# +# BUG#14210: "Simple query with > operator on large table gives server +# crash" +# Check that cursors work in case when HEAP tables are converted to +# MyISAM +# +--disable_warnings +drop procedure if exists bug14210| +--enable_warnings +set @@session.max_heap_table_size=16384| +select @@session.max_heap_table_size| +# To trigger the memory corruption the original table must be InnoDB. +# No harm if it's not, so don't warn if the suite is run with --skip-innodb +--disable_warnings +create table t3 (a char(255)) engine=InnoDB| +--enable_warnings +create procedure bug14210_fill_table() +begin + declare table_size, max_table_size int default 0; + select @@session.max_heap_table_size into max_table_size; + delete from t3; + insert into t3 (a) values (repeat('a', 255)); + repeat + insert into t3 select a from t3; + select count(*)*255 from t3 into table_size; + until table_size > max_table_size*2 end repeat; +end| +call bug14210_fill_table()| +drop procedure bug14210_fill_table| +create table t4 like t3| + +create procedure bug14210() +begin + declare a char(255); + declare done int default 0; + declare c cursor for select * from t3; + declare continue handler for sqlstate '02000' set done = 1; + open c; + repeat + fetch c into a; + if not done then + insert into t4 values (upper(a)); + end if; + until done end repeat; + close c; +end| +call bug14210()| +select count(*) from t4| + +drop table t3, t4| +drop procedure bug14210| +set @@session.max_heap_table_size=default| + + +# +# BUG#1473: Dumping of stored functions seems to cause corruption in +# the function body +# +--disable_warnings +drop function if exists bug14723| +drop procedure if exists bug14723| +--enable_warnings + +delimiter ;;| +/*!50003 create function bug14723() + returns bigint(20) +main_loop: begin + return 42; +end */;; +show create function bug14723;; +select bug14723();; + +/*!50003 create procedure bug14723() +main_loop: begin + select 42; +end */;; +show create procedure bug14723;; +call bug14723();; + +delimiter |;; + +drop function bug14723| +drop procedure bug14723| + +# +# Bug#14845 "mysql_stmt_fetch returns MYSQL_NO_DATA when COUNT(*) is 0" +# Check that when fetching from a cursor, COUNT(*) works properly. +# +create procedure bug14845() +begin + declare a char(255); + declare done int default 0; + declare c cursor for select count(*) from t1 where 1 = 0; + declare continue handler for sqlstate '02000' set done = 1; + open c; + repeat + fetch c into a; + if not done then + select a; + end if; + until done end repeat; + close c; +end| +call bug14845()| +drop procedure bug14845| + +# +# BUG#13549 "Server crash with nested stored procedures". +# Server should not crash when during execution of stored procedure +# we have to parse trigger/function definition and this new trigger/ +# function has more local variables declared than invoking stored +# procedure and last of these variables is used in argument of NOT +# operator. +# +--disable_warnings +drop procedure if exists bug13549_1| +drop procedure if exists bug13549_2| +--enable_warnings +CREATE PROCEDURE `bug13549_2`() +begin + call bug13549_1(); +end| +CREATE PROCEDURE `bug13549_1`() +begin + declare done int default 0; + set done= not done; +end| +CALL bug13549_2()| +drop procedure bug13549_2| +drop procedure bug13549_1| + +# +# BUG#10100: function (and stored procedure?) recursivity problem +# +--disable_warnings +drop function if exists bug10100f| +drop procedure if exists bug10100p| +drop procedure if exists bug10100t| +drop procedure if exists bug10100pt| +drop procedure if exists bug10100pv| +drop procedure if exists bug10100pd| +drop procedure if exists bug10100pc| +--enable_warnings +# routines with simple recursion +create function bug10100f(prm int) returns int +begin + if prm > 1 then + return prm * bug10100f(prm - 1); + end if; + return 1; +end| +create procedure bug10100p(prm int, inout res int) +begin + set res = res * prm; + if prm > 1 then + call bug10100p(prm - 1, res); + end if; +end| +create procedure bug10100t(prm int) +begin + declare res int; + set res = 1; + call bug10100p(prm, res); + select res; +end| + +# a procedure which use tables and recursion +create table t3 (a int)| +insert into t3 values (0)| +create view v1 as select a from t3; +create procedure bug10100pt(level int, lim int) +begin + if level < lim then + update t3 set a=level; + FLUSH TABLES; + call bug10100pt(level+1, lim); + else + select * from t3; + end if; +end| +# view & recursion +create procedure bug10100pv(level int, lim int) +begin + if level < lim then + update v1 set a=level; + FLUSH TABLES; + call bug10100pv(level+1, lim); + else + select * from v1; + end if; +end| +# dynamic sql & recursion +prepare stmt2 from "select * from t3;"; +create procedure bug10100pd(level int, lim int) +begin + if level < lim then + select level; + prepare stmt1 from "update t3 set a=a+2"; + execute stmt1; + FLUSH TABLES; + execute stmt1; + FLUSH TABLES; + execute stmt1; + FLUSH TABLES; + deallocate prepare stmt1; + execute stmt2; + select * from t3; + call bug10100pd(level+1, lim); + else + execute stmt2; + end if; +end| +# cursor & recursion +create procedure bug10100pc(level int, lim int) +begin + declare lv int; + declare c cursor for select a from t3; + open c; + if level < lim then + select level; + fetch c into lv; + select lv; + update t3 set a=level+lv; + FLUSH TABLES; + call bug10100pc(level+1, lim); + else + select * from t3; + end if; + close c; +end| + +set @@max_sp_recursion_depth=4| +select @@max_sp_recursion_depth| +-- error ER_SP_NO_RECURSION +select bug10100f(3)| +-- error ER_SP_NO_RECURSION +select bug10100f(6)| +call bug10100t(5)| +call bug10100pt(1,5)| +call bug10100pv(1,5)| +update t3 set a=1| +call bug10100pd(1,5)| +select * from t3| +update t3 set a=1| +call bug10100pc(1,5)| +select * from t3| +set @@max_sp_recursion_depth=0| +select @@max_sp_recursion_depth| +-- error ER_SP_NO_RECURSION +select bug10100f(5)| +-- error ER_SP_RECURSION_LIMIT +call bug10100t(5)| + +#end of the stack checking +set @@max_sp_recursion_depth=255| +set @var=1| +#disable log because error about stack overrun contains numbers which +#depend on a system +-- disable_result_log +-- error ER_STACK_OVERRUN_NEED_MORE +call bug10100p(255, @var)| +-- error ER_STACK_OVERRUN_NEED_MORE +call bug10100pt(1,255)| +-- error ER_STACK_OVERRUN_NEED_MORE +call bug10100pv(1,255)| +-- error ER_STACK_OVERRUN_NEED_MORE +call bug10100pd(1,255)| +-- error ER_STACK_OVERRUN_NEED_MORE +call bug10100pc(1,255)| +-- enable_result_log +set @@max_sp_recursion_depth=0| + +deallocate prepare stmt2| + +drop function bug10100f| +drop procedure bug10100p| +drop procedure bug10100t| +drop procedure bug10100pt| +drop procedure bug10100pv| +drop procedure bug10100pd| +drop procedure bug10100pc| +drop view v1| + +# +# BUG#13729: Stored procedures: packet error after exception handled +# +--disable_warnings +drop procedure if exists bug13729| +drop table if exists t3| +--enable_warnings + +create table t3 (s1 int, primary key (s1))| + +insert into t3 values (1),(2)| + +create procedure bug13729() +begin + declare continue handler for sqlexception select 55; + + update t3 set s1 = 1; +end| + +call bug13729()| +# Used to cause Packets out of order +select * from t3| + +drop procedure bug13729| +drop table t3| + +# +# BUG#14643: Stored Procedure: Continuing after failed var. initialization +# crashes server. +# +--disable_warnings +drop procedure if exists bug14643_1| +drop procedure if exists bug14643_2| +--enable_warnings + +create procedure bug14643_1() +begin + declare continue handler for sqlexception select 'boo' as 'Handler'; + + begin + declare v int default undefined_var; + + if v = 1 then + select 1; + else + select v, isnull(v); + end if; + end; +end| + +create procedure bug14643_2() +begin + declare continue handler for sqlexception select 'boo' as 'Handler'; + + case undefined_var + when 1 then + select 1; + else + select 2; + end case; + + select undefined_var; +end| + +call bug14643_1()| +call bug14643_2()| + +drop procedure bug14643_1| +drop procedure bug14643_2| + +# +# BUG#14304: auto_increment field incorrect set in SP +# +--disable_warnings +drop procedure if exists bug14304| +drop table if exists t3, t4| +--enable_warnings + +create table t3(a int primary key auto_increment)| +create table t4(a int primary key auto_increment)| + +create procedure bug14304() +begin + insert into t3 set a=null; + insert into t4 set a=null; + insert into t4 set a=null; + insert into t4 set a=null; + insert into t4 set a=null; + insert into t4 set a=null; + insert into t4 select null as a; + + insert into t3 set a=null; + insert into t3 set a=null; + + select * from t3; +end| + +call bug14304()| + +drop procedure bug14304| +drop table t3, t4| + +# +# BUG#14376: MySQL crash on scoped variable (re)initialization +# +--disable_warnings +drop procedure if exists bug14376| +--enable_warnings + +create procedure bug14376() +begin + declare x int default x; +end| + +# Not the error we want, but that's what we got for now... +--error ER_BAD_FIELD_ERROR +call bug14376()| +drop procedure bug14376| + +create procedure bug14376() +begin + declare x int default 42; + + begin + declare x int default x; + + select x; + end; +end| + +call bug14376()| + +drop procedure bug14376| + +create procedure bug14376(x int) +begin + declare x int default x; + + select x; +end| + +call bug14376(4711)| + +drop procedure bug14376| + +# +# Bug#5967 "Stored procedure declared variable used instead of column" +# The bug should be fixed later. +# Test precedence of names of parameters, variable declarations, +# variable declarations in nested compound statements, table columns, +# table columns in cursor declarations. +# According to the standard, table columns take precedence over +# variable declarations. In MySQL 5.0 it's vice versa. +# + +--disable_warnings +drop procedure if exists bug5967| +drop table if exists t3| +--enable_warnings +create table t3 (a varchar(255))| +insert into t3 (a) values ("a - table column")| +create procedure bug5967(a varchar(255)) +begin + declare i varchar(255); + declare c cursor for select a from t3; + select a; + select a from t3 into i; + select i as 'Parameter takes precedence over table column'; open c; + fetch c into i; + close c; + select i as 'Parameter takes precedence over table column in cursors'; + begin + declare a varchar(255) default 'a - local variable'; + declare c1 cursor for select a from t3; + select a as 'A local variable takes precedence over parameter'; + open c1; + fetch c1 into i; + close c1; + select i as 'A local variable takes precedence over parameter in cursors'; + begin + declare a varchar(255) default 'a - local variable in a nested compound statement'; + declare c2 cursor for select a from t3; + select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement'; + select a from t3 into i; + select i as 'A local variable in a nested compound statement takes precedence over table column'; + open c2; + fetch c2 into i; + close c2; + select i as 'A local variable in a nested compound statement takes precedence over table column in cursors'; + end; + end; +end| +call bug5967("a - stored procedure parameter")| +drop procedure bug5967| + +# +# Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server" +# +--disable_warnings +drop procedure if exists bug13012| +--enable_warnings +create procedure bug13012() +BEGIN + REPAIR TABLE t1; + BACKUP TABLE t1 to '../tmp'; + DROP TABLE t1; + RESTORE TABLE t1 FROM '../tmp'; +END| +call bug13012()| +drop procedure bug13012| +create view v1 as select * from t1| +create procedure bug13012() +BEGIN + REPAIR TABLE t1,t2,t3,v1; + OPTIMIZE TABLE t1,t2,t3,v1; + ANALYZE TABLE t1,t2,t3,v1; +END| +call bug13012()| +call bug13012()| +call bug13012()| +drop procedure bug13012| +drop view v1; +select * from t1| + +# +# A test case for Bug#15392 "Server crashes during prepared statement +# execute": make sure that stored procedure check for error conditions +# properly and do not continue execution if an error has been set. +# +# It's necessary to use several DBs because in the original code +# the successful return of mysql_change_db overrode the error from +# execution. +drop schema if exists mysqltest1| +drop schema if exists mysqltest2| +drop schema if exists mysqltest3| +create schema mysqltest1| +create schema mysqltest2| +create schema mysqltest3| +use mysqltest3| + +create procedure mysqltest1.p1 (out prequestid varchar(100)) +begin + call mysqltest2.p2('call mysqltest3.p3(1, 2)'); +end| + +create procedure mysqltest2.p2(in psql text) +begin + declare lsql text; + set @lsql= psql; + prepare lstatement from @lsql; + execute lstatement; + deallocate prepare lstatement; +end| + +create procedure mysqltest3.p3(in p1 int) +begin + select p1; +end| + +--error ER_SP_WRONG_NO_OF_ARGS +call mysqltest1.p1(@rs)| +--error ER_SP_WRONG_NO_OF_ARGS +call mysqltest1.p1(@rs)| +--error ER_SP_WRONG_NO_OF_ARGS +call mysqltest1.p1(@rs)| +drop schema if exists mysqltest1| +drop schema if exists mysqltest2| +drop schema if exists mysqltest3| +use test| + +# +# Bug#15441 "Running SP causes Server to Crash": check that an SP variable +# can not be used in VALUES() function. +# +--disable_warnings +drop table if exists t3| +drop procedure if exists bug15441| +--enable_warnings +create table t3 (id int not null primary key, county varchar(25))| +insert into t3 (id, county) values (1, 'York')| + +# First check that a stored procedure that refers to a parameter in VALUES() +# function won't parse. + +create procedure bug15441(c varchar(25)) +begin + update t3 set id=2, county=values(c); +end| +--error ER_BAD_FIELD_ERROR +call bug15441('county')| +drop procedure bug15441| + +# Now check the case when there is an ambiguity between column names +# and stored procedure parameters: the parser shall resolve the argument +# of VALUES() function to the column name. + +# It's hard to deduce what county refers to in every case (INSERT statement): +# 1st county refers to the column +# 2nd county refers to the procedure parameter +# 3d and 4th county refers to the column, again, but +# for 4th county it has the value of SP parameter + +# In UPDATE statement, just check that values() function returns NULL for +# non- INSERT...UPDATE statements, as stated in the manual. + +create procedure bug15441(county varchar(25)) +begin + declare c varchar(25) default "hello"; + + insert into t3 (id, county) values (1, county) + on duplicate key update county= values(county); + select * from t3; + + update t3 set id=2, county=values(id); + select * from t3; +end| +call bug15441('Yale')| +drop table t3| +drop procedure bug15441| # # BUG#NNNN: New bug synopsis diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test index 82e1cd2f1c9..308d4ad5c33 100644 --- a/mysql-test/t/sp_trans.test +++ b/mysql-test/t/sp_trans.test @@ -176,6 +176,250 @@ drop table t1, t2| # +# BUG#13825 "Triggers: crash if release savepoint". +# Also general test for handling of savepoints in stored routines. +# +# According to SQL standard we should establish new savepoint +# level before executing stored function/trigger and destroy +# this savepoint level after execution. Stored procedures by +# default should be executed using the same savepoint level +# as their caller (to execute stored procedure using new +# savepoint level one should explicitly specify NEW SAVEPOINT +# LEVEL clause in procedure creation statement which MySQL +# does not support yet). +--disable_warnings +drop function if exists bug13825_0| +drop function if exists bug13825_1| +drop function if exists bug13825_2| +drop function if exists bug13825_3| +drop function if exists bug13825_4| +drop function if exists bug13825_5| +drop procedure if exists bug13825_0| +drop procedure if exists bug13825_1| +drop procedure if exists bug13825_2| +drop table if exists t1| +--enable_warnings +create table t1 (i int) engine=innodb| +create table t2 (i int) engine=innodb| +create function bug13825_0() returns int +begin + rollback to savepoint x; + return 1; +end| +create function bug13825_1() returns int +begin + release savepoint x; + return 1; +end| +create function bug13825_2() returns int +begin + insert into t1 values (2); + savepoint x; + insert into t1 values (3); + rollback to savepoint x; + insert into t1 values (4); + return 1; +end| +create procedure bug13825_0() +begin + rollback to savepoint x; +end| +create procedure bug13825_1() +begin + release savepoint x; +end| +create procedure bug13825_2() +begin + savepoint x; +end| +insert into t2 values (1)| +create trigger t2_bi before insert on t2 for each row + rollback to savepoint x| +create trigger t2_bu before update on t2 for each row + release savepoint x| +create trigger t2_bd before delete on t2 for each row +begin + insert into t1 values (2); + savepoint x; + insert into t1 values (3); + rollback to savepoint x; + insert into t1 values (4); +end| +create function bug13825_3(rb int) returns int +begin + insert into t1 values(1); + savepoint x; + insert into t1 values(2); + if rb then + rollback to savepoint x; + end if; + insert into t1 values(3); + return rb; +end| +create function bug13825_4() returns int +begin + savepoint x; + insert into t1 values(2); + rollback to savepoint x; + return 0; +end| +create function bug13825_5(p int) returns int +begin + savepoint x; + insert into t2 values(p); + rollback to savepoint x; + insert into t2 values(p+1); + return p; +end| +set autocommit= 0| +# Test of savepoint level handling for stored functions and triggers +begin | +insert into t1 values (1)| +savepoint x| +--error ER_SP_DOES_NOT_EXIST +set @a:= bug13825_0()| +--error ER_SP_DOES_NOT_EXIST +insert into t2 values (2)| +--error ER_SP_DOES_NOT_EXIST +set @a:= bug13825_1()| +--error ER_SP_DOES_NOT_EXIST +update t2 set i = 2| +set @a:= bug13825_2()| +select * from t1| +rollback to savepoint x| +select * from t1| +delete from t2| +select * from t1| +rollback to savepoint x| +select * from t1| +# Of course savepoints set in function should not be visible from its caller +release savepoint x| +set @a:= bug13825_2()| +select * from t1| +--error ER_SP_DOES_NOT_EXIST +rollback to savepoint x| +delete from t1| +commit| +# Test of savepoint level handling for stored procedures +begin| +insert into t1 values (5)| +savepoint x| +insert into t1 values (6)| +call bug13825_0()| +select * from t1| +call bug13825_1()| +--error ER_SP_DOES_NOT_EXIST +rollback to savepoint x| +savepoint x| +insert into t1 values (7)| +call bug13825_2()| +rollback to savepoint x| +select * from t1| +delete from t1| +commit| +set autocommit= 1| +# Let us test that savepoints work inside of functions +# even in auto-commit mode +select bug13825_3(0)| +select * from t1| +delete from t1| +select bug13825_3(1)| +select * from t1| +delete from t1| +# Curious case: rolling back to savepoint which is set by first +# statement in function should not rollback whole transaction. +set autocommit= 0| +begin| +insert into t1 values (1)| +set @a:= bug13825_4()| +select * from t1| +delete from t1| +commit| +set autocommit= 1| +# Other curious case: savepoint in the middle of statement +drop table t2| +create table t2 (i int) engine=innodb| +insert into t1 values (1), (bug13825_5(2)), (3)| +select * from t1| +select * from t2| +# Cleanup +drop function bug13825_0| +drop function bug13825_1| +drop function bug13825_2| +drop function bug13825_3| +drop function bug13825_4| +drop function bug13825_5| +drop procedure bug13825_0| +drop procedure bug13825_1| +drop procedure bug13825_2| +drop table t1, t2| + + +# +# BUG#14840: CONTINUE handler problem +# +--disable_warnings +drop table if exists t3| +drop procedure if exists bug14840_1| +drop procedure if exists bug14840_2| +--enable_warnings + +create table t3 +( + x int, + y int, + primary key (x) +) engine=InnoDB| + +# This used to hang the client since the insert returned with an +# error status (left over from the update) even though it succeeded, +# which caused the execution to end at that point. +create procedure bug14840_1() +begin + declare err int default 0; + declare continue handler for sqlexception + set err = err + 1; + + start transaction; + update t3 set x = 1, y = 42 where x = 2; + insert into t3 values (3, 4711); + if err > 0 then + rollback; + else + commit; + end if; + select * from t3; +end| + +# A simpler (non-transactional) case: insert at select should be done +create procedure bug14840_2() +begin + declare err int default 0; + declare continue handler for sqlexception + begin + set err = err + 1; + select err as 'Ping'; + end; + + update t3 set x = 1, y = 42 where x = 2; + update t3 set x = 1, y = 42 where x = 2; + insert into t3 values (3, 4711); + select * from t3; +end| + +insert into t3 values (1, 3), (2, 5)| +call bug14840_1()| + +delete from t3| +insert into t3 values (1, 3), (2, 5)| +call bug14840_2()| + +drop procedure bug14840_1| +drop procedure bug14840_2| +drop table t3| + + +# # BUG#NNNN: New bug synopsis # #--disable_warnings diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test index b11afe9e59d..8ae9ced6c68 100644 --- a/mysql-test/t/sql_mode.test +++ b/mysql-test/t/sql_mode.test @@ -1,5 +1,6 @@ --disable_warnings -drop table if exists t1; +drop table if exists t1,t2,v1,v2; +drop view if exists t1,t2,v1,v2; --enable_warnings CREATE TABLE `t1` ( diff --git a/mysql-test/t/ssl.test b/mysql-test/t/ssl.test new file mode 100644 index 00000000000..de88569d74a --- /dev/null +++ b/mysql-test/t/ssl.test @@ -0,0 +1,17 @@ +# Turn on ssl between the client and server +# and run a number of tests + +-- source include/have_openssl.inc + +connect (ssl_con,localhost,root,,,,,SSL); + +# Check ssl turned on +SHOW STATUS LIKE 'Ssl_cipher'; + +# Source select test case +-- source include/common-tests.inc + +# Check ssl turned on +SHOW STATUS LIKE 'Ssl_cipher'; + + diff --git a/mysql-test/t/ssl_compress.test b/mysql-test/t/ssl_compress.test new file mode 100644 index 00000000000..f5fe86e9a81 --- /dev/null +++ b/mysql-test/t/ssl_compress.test @@ -0,0 +1,22 @@ +# Turn on compression between the client and server +# and run a number of tests + +-- source include/have_openssl.inc +-- source include/have_compress.inc + +connect (ssl_compress_con,localhost,root,,,,,SSL COMPRESS); + +# Check ssl turned on +SHOW STATUS LIKE 'Ssl_cipher'; + +# Check compression turned on +SHOW STATUS LIKE 'Compression'; + +# Source select test case +-- source include/common-tests.inc + +# Check ssl turned on +SHOW STATUS LIKE 'Ssl_cipher'; + +# Check compression turned on +SHOW STATUS LIKE 'Compression'; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index cc621fb5835..762ff36ba63 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1968,3 +1968,86 @@ drop table t1; purge master logs before (select adddate(current_timestamp(), interval -4 day)); +# +# Test for bug #11762: subquery with an aggregate function in HAVING +# + +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (c int, d int); +CREATE TABLE t3 (e int); + +INSERT INTO t1 VALUES + (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40); +INSERT INTO t2 VALUES + (2,10), (2,20), (4,10), (5,10), (3,20), (2,40); +INSERT INTO t3 VALUES (10), (30), (10), (20) ; + +SELECT a, MAX(b), MIN(b) FROM t1 GROUP BY a; +SELECT * FROM t2; +SELECT * FROM t3; + +SELECT a FROM t1 GROUP BY a + HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20); +SELECT a FROM t1 GROUP BY a + HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d); +SELECT a FROM t1 GROUP BY a + HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d); +SELECT a FROM t1 GROUP BY a + HAVING a IN (SELECT c FROM t2 + WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e)); +SELECT a FROM t1 GROUP BY a + HAVING a IN (SELECT c FROM t2 + WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d)); +SELECT a FROM t1 GROUP BY a + HAVING a IN (SELECT c FROM t2 + WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e)); +SELECT a FROM t1 GROUP BY a + HAVING a IN (SELECT c FROM t2 + WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d)); +SELECT a FROM t1 GROUP BY a + HAVING a IN (SELECT c FROM t2 + WHERE MIN(b) < d AND + EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d)); + +SELECT a, SUM(a) FROM t1 GROUP BY a; + +SELECT a FROM t1 + WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a; +SELECT a FROM t1 GROUP BY a + HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c); + +SELECT a FROM t1 + WHERE a < 3 AND + EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a; +SELECT a FROM t1 + WHERE a < 3 AND + EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c); + +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c + HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e + HAVING SUM(t1.a+t2.c) < t3.e/4)); +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING t1.a > ALL(SELECT t2.c FROM t2 + WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e + HAVING SUM(t1.a+t2.c) < t3.e/4)); +-- error 1111 +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING t1.a > ALL(SELECT t2.c FROM t2 + WHERE EXISTS(SELECT t3.e FROM t3 + WHERE SUM(t1.a+t2.c) < t3.e/4)); +-- error 1111 +SELECT t1.a from t1 GROUP BY t1.a HAVING AVG(SUM(t1.b)) > 20; + +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c + HAVING AVG(t2.c+SUM(t1.b)) > 20); +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c + HAVING AVG(SUM(t1.b)) > 20); + +SELECT t1.a, SUM(b) AS sum FROM t1 GROUP BY t1.a + HAVING t1.a IN (SELECT t2.c FROM t2 GROUP BY t2.c + HAVING t2.c+sum > 20); + +DROP TABLE t1,t2,t3; 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/symlink.test b/mysql-test/t/symlink.test index b7a7e83d569..6a6dd305006 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -3,6 +3,8 @@ disable_query_log; show variables like "have_symlink"; enable_query_log; +--source include/not_windows.inc + --disable_warnings drop table if exists t1,t2,t7,t8,t9; drop database if exists mysqltest; diff --git a/mysql-test/t/trigger-compat.test b/mysql-test/t/trigger-compat.test new file mode 100644 index 00000000000..c2acc235135 --- /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 -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..deeaeacbccc --- /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 -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/trigger.test b/mysql-test/t/trigger.test index cd79eb82ace..f2d9bb6c856 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -13,6 +13,8 @@ drop procedure if exists p1; # Create additional connections used through test connect (addconroot1, localhost, root,,); connect (addconroot2, localhost, root,,); +# Connection without current database set +connect (addconwithoutdb, localhost, root,,*NO-ONE*); connection default; create table t1 (i int); @@ -743,8 +745,12 @@ create trigger t1_ai after insert on t1 for each row insert into t2 values (new.f1+1); create trigger t2_ai after insert on t2 for each row insert into t1 values (new.f2+1); +# Allow SP resursion to be show that it has not influence here +set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth; +set @@max_sp_recursion_depth=100; --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG insert into t1 values (1); +set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS; select * from t1; select * from t2; drop trigger t1_ai; @@ -875,3 +881,80 @@ drop function f1; drop view v1; drop table t1, t2, t3; --enable_parsing + +# +# Test for bug #13399 "Crash when executing PS/SP which should activate +# trigger which is now dropped". See also test for similar bug for stored +# routines in sp-error.test (#12329). +create table t1 (id int); +create table t2 (id int); +create trigger t1_bi before insert on t1 for each row insert into t2 values (new.id); +prepare stmt1 from "insert into t1 values (10)"; +create procedure p1() insert into t1 values (10); +call p1(); +# Actually it is enough to do FLUSH TABLES instead of DROP TRIGGER +drop trigger t1_bi; +# Server should not crash on these two statements +execute stmt1; +call p1(); +deallocate prepare stmt1; +drop procedure p1; + +# Let us test more complex situation when we alter trigger in such way that +# it uses different set of tables (or simply add new trigger). +create table t3 (id int); +create trigger t1_bi after insert on t1 for each row insert into t2 values (new.id); +prepare stmt1 from "insert into t1 values (10)"; +create procedure p1() insert into t1 values (10); +call p1(); +# Altering trigger forcing it use different set of tables +drop trigger t1_bi; +create trigger t1_bi after insert on t1 for each row insert into t3 values (new.id); +# Until we implement proper mechanism for invalidation of PS/SP when table +# or SP's are changed these two statements will fail with 'Table ... was +# not locked' error (this mechanism should be based on the new TDC). +--error 1100 +execute stmt1; +--error 1100 +call p1(); +deallocate prepare stmt1; +drop procedure p1; +drop table t1, t2, t3; + +# +# BUG#13549 "Server crash with nested stored procedures". +# Server should not crash when during execution of stored procedure +# we have to parse trigger/function definition and this new trigger/ +# function has more local variables declared than invoking stored +# procedure and last of these variables is used in argument of NOT +# operator. +# +create table t1 (a int); +DELIMITER //; +CREATE PROCEDURE `p1`() +begin + insert into t1 values (1); +end// +create trigger trg before insert on t1 for each row +begin + declare done int default 0; + set done= not done; +end// +DELIMITER ;// +CALL p1(); +drop procedure p1; +drop table t1; + +# +# Test for bug #14863 "Triggers: crash if create and there is no current +# database". We should not crash and give proper error when database for +# trigger or its table is not specified and there is no current database. +# +connection addconwithoutdb; +--error ER_NO_DB_ERROR +create trigger t1_bi before insert on test.t1 for each row set @a:=0; +--error ER_NO_DB_ERROR +create trigger test.t1_bi before insert on t1 for each row set @a:=0; +--error ER_NO_DB_ERROR +drop trigger t1_bi; +connection default; diff --git a/mysql-test/t/type_binary.test b/mysql-test/t/type_binary.test index b5928cb14c4..1639aff4711 100644 --- a/mysql-test/t/type_binary.test +++ b/mysql-test/t/type_binary.test @@ -65,3 +65,30 @@ select hex(s1) from t1 where s1=0x0120; select hex(s1) from t1 where s1=0x0100; select count(distinct s1) from t1; drop table t1; + +# check that cast appends trailing zeros +select hex(cast(0x10 as binary(2))); + +# +# Bug #14299: BINARY space truncation should cause warning or error +# +create table t1 (b binary(2), vb varbinary(2)); +insert into t1 values(0x4120, 0x4120); +insert into t1 values(0x412020, 0x412020); +drop table t1; +create table t1 (c char(2), vc varchar(2)); +insert into t1 values(0x4120, 0x4120); +insert into t1 values(0x412020, 0x412020); +drop table t1; + +set @old_sql_mode= @@sql_mode, sql_mode= 'traditional'; +create table t1 (b binary(2), vb varbinary(2)); +insert into t1 values(0x4120, 0x4120); +--error ER_DATA_TOO_LONG +insert into t1 values(0x412020, NULL); +--error ER_DATA_TOO_LONG +insert into t1 values(NULL, 0x412020); +drop table t1; +set @@sql_mode= @old_sql_mode; + +--echo End of 5.0 tests diff --git a/mysql-test/t/type_newdecimal-big.test b/mysql-test/t/type_newdecimal-big.test new file mode 100644 index 00000000000..9a1104e4fe6 --- /dev/null +++ b/mysql-test/t/type_newdecimal-big.test @@ -0,0 +1,50 @@ +--source include/big_test.inc + +--disable_warnings +drop procedure if exists sp1; +--enable_warnings + +# +#-- 2. Adding (one millionth) one million times should be the same as +#-- adding 1. So a stored procedure with many iterations will show if +#-- small errors accumulate. +# + +delimiter //; +# +CREATE PROCEDURE sp1() +BEGIN + DECLARE v1, v2, v3, v4 DECIMAL(28,12); + DECLARE v3_2, v4_2 DECIMAL(28, 12); + DECLARE counter INT; + + SET v1 = 1; + SET v2 = 2; + SET v3 = 1000000000000; + SET v4 = 2000000000000; + SET counter = 0; + + WHILE counter < 100000 DO + SET v1 = v1 + 0.000000000001; + SET v2 = v2 - 0.000000000001; + SET v3 = v3 + 1; + SET v4 = v4 - 1; + SET counter = counter + 1; + END WHILE; + + SET v3_2 = v3 * 0.000000000001; + SET v4_2 = v4 * 0.000000000001; + + SELECT v1, v2, v3, v3_2, v4, v4_2; +END// +# +call sp1()// +#-- should return +# -- v1=1.0000001 +# -- v2=1.999999900000 +# -- v3=1.0000001 +# -- v4=1.999999900000 +# +delimiter ;// +# +drop procedure sp1; diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index a7087d46dca..e2fe9767432 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -473,7 +473,7 @@ drop table wl1612_4; # #-- Additional tests for WL#1612 Precision math # -#-- 1. Comparisons should show that a number is +#-- Comparisons should show that a number is #-- exactly equal to its value as displayed. # set sql_mode=''; @@ -487,34 +487,9 @@ select 18.3=18.3; select -18.3=18.3; # select 0.8 = 0.7 + 0.1; + # -#-- 2. Adding (one millionth) one million times should be the same as -#-- adding 1. So a stored procedure with many iterations will show if -#-- small errors accumulate. -# -#drop procedure p1; -# -delimiter //; -# -create procedure p1 () begin - declare v1, v2, v3, v4 decimal(16,12); declare v5 int; - set v1 = 1; set v2 = 2; set v3 = 1000000000000; set v4 = 2000000000000; set v5 = 0; - while v5 < 100000 do - set v1 = v1 + 0.000000000001; set v2 = v2 - 0.000000000001; set v3 = v3 + 1; set v4 = v4 - 1; set v5 = v5 + 1; - end while; select v1, v2, v3 * 0.000000000001, v4 * 0.000000000001; end;// -# -call p1()// -#-- should return -# -- v1=1.0000001 -# -- v2=1.999999900000 -# -- v3=1.0000001 -# -- v4=1.999999900000 -# -delimiter ;// -# -drop procedure p1; -# -#-- 3. It should be possible to define a column +#-- It should be possible to define a column #-- with up to 38 digits precision either before #-- or after the decimal point. Any number which #-- is inserted, if it's within the range, should @@ -565,7 +540,7 @@ select * from t1; # drop table t1; # -#-- 4. The usual arithmetic operators / * + - should work. +#-- The usual arithmetic operators / * + - should work. # #select 77777777777777777777777777777777777777 / 7777777777777777777777777777777777777 = 10; #-- should return 0 (false). @@ -668,7 +643,7 @@ select truncate(99999999999999999999999999999999999999,-31); #drop procedure p1; #drop table t1; # -#-- 7. When I say DECIMAL(x) I should be able to store x digits. +#-- When I say DECIMAL(x) I should be able to store x digits. #-- If I can't, there should be an error at CREATE time. # #drop table if exists t1; @@ -676,7 +651,8 @@ select truncate(99999999999999999999999999999999999999,-31); #create table t1 (col1 decimal(254)); #-- should return SQLSTATE 22003 numeric value out of range # -#-- 8. When I say DECIMAL(x,y) there should be no silent change of precision or scale. +#-- When I say DECIMAL(x,y) there should be no silent change of precision or +#-- scale. # #drop table if exists t1; # @@ -694,7 +670,7 @@ select truncate(99999999999999999999999999999999999999,-31); # #drop table t1; # -#-- 9. From WL#1612 "The future" point 2.: +#-- From WL#1612 "The future" point 2.: #-- The standard requires that we treat numbers like "0.5" as #-- DECIMAL or NUMERIC, not as floating-point. # @@ -715,7 +691,7 @@ show create table t1; # drop table t1; # -#-- 10. From WL#1612, "The future", point 3.: We have to start rounding correctly. +#-- From WL#1612, "The future", point 3.: We have to start rounding correctly. # select round(1.5),round(2.5); #-- should return: @@ -725,13 +701,13 @@ select round(1.5),round(2.5); #| 2 | 3 | #+------------+------------+ # -#-- 11. From WL#1612, "The future", point 4.: "select 0.07 * 0.07;" should return 0.0049, not 0.00. +#-- From WL#1612, "The future", point 4.: "select 0.07 * 0.07;" should return 0.0049, not 0.00. #-- If operand#1 has scale X and operand#2 has scale Y, then result should have scale (X+Y). # select 0.07 * 0.07; #-- should return 0.0049 # -#-- 12. From WL#1612, "The future", point 5.: Division by zero is an error. +#-- From WL#1612, "The future", point 5.: Division by zero is an error. # set sql_mode='traditional'; # @@ -752,7 +728,7 @@ select 1 / 0; #+-------+ #1 row in set, 1 warning (0.00 sec) # -#-- 13. From WL#1612 "The future" point 6.: Overflow is an error. +#-- From WL#1612 "The future" point 6.: Overflow is an error. # #set sql_mode=''; # @@ -793,7 +769,8 @@ select 1 / 0; #drop table t2; #drop table t1; # -#-- 15. From WL#1612 "The future" point 8.: Stop storing leading "+" signs and leading "0"s. +#-- From WL#1612 "The future" point 8.: Stop storing leading "+" signs and +# leading "0"s. # #drop table if exists t1; # @@ -805,7 +782,7 @@ select 1 / 0; # #drop table t1; # -#-- 16. From WL#1612, The future" point 9.: +#-- From WL#1612, The future" point 9.: #-- Accept the data type and precision and scale as the user #-- asks, or return an error, but don't change to something else. # @@ -817,7 +794,7 @@ select 1 / 0; # #drop table t1; # -#-- 17. The scripts in the following bugs should work: +#-- The scripts in the following bugs should work: # #BUG#559 Maximum precision for DECIMAL column ... @@ -833,7 +810,7 @@ select 1 / 0; #BUG#6048 Stored procedure causes operating system reboot #BUG#6053 DOUBLE PRECISION literal --- 18. Tests from 'traditional' mode tests +-- Tests from 'traditional' mode tests # set sql_mode='ansi,traditional'; # @@ -1050,3 +1027,61 @@ select cast(@non_existing_user_var/2 as DECIMAL); # --error 1427 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; + +# We have to disable warnings here as the test in +# Field_new_decimal::store(double): +# if (nr2 != nr) +# fails randomly depending on compiler options + +--disable_warnings +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; +--enable_warnings +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) +# + +create table t1 (c1 decimal(64)); +--disable_ps_protocol +insert into t1 values( +89000000000000000000000000000000000000000000000000000000000000000000000000000000000000000); +insert into t1 values( +99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * +99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999); +--enable_ps_protocol +insert into t1 values(1e100); +select * from t1; +drop table t1; diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test index 7ae3f65f7cc..9abfe914335 100644 --- a/mysql-test/t/type_time.test +++ b/mysql-test/t/type_time.test @@ -21,4 +21,18 @@ select t, time_to_sec(t),sec_to_time(time_to_sec(t)) from t1; select sec_to_time(time_to_sec(t)) from t1; drop table t1; +# +# BUG #12440: Incorrect processing of time values containing +# long fraction part and/or large exponent part. +# +# These must return normal result: +SELECT CAST(235959.123456 AS TIME); +SELECT CAST(0.235959123456e+6 AS TIME); +SELECT CAST(235959123456e-6 AS TIME); +# These must cut fraction part and produce warning: +SELECT CAST(235959.1234567 AS TIME); +SELECT CAST(0.2359591234567e6 AS TIME); +# This must return NULL and produce warning: +SELECT CAST(0.2359591234567e+30 AS TIME); + # End of 4.1 tests diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index daa83ef0fa4..6de90dd446d 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -753,6 +753,24 @@ create table t2 select a from t1 union select b from t1; show columns from t2; drop table t2, t1; +# +# Bug #14216: UNION + DECIMAL wrong values in result +# +create table t1 (f1 decimal(60,25), f2 decimal(60,25)); +insert into t1 values (0.0,0.0); +select f1 from t1 union all select f2 from t1; +select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1 +union all +select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1; +drop table t1; +create table t1 (f1 decimal(60,24), f2 decimal(60,24)); +insert into t1 values (0.0,0.0); +select f1 from t1 union all select f2 from t1; +select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1 +union all +select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1; +drop table t1; + # # Test that union with VARCHAR produces dynamic row tables # diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 13ced76079f..d0496b48c7a 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -228,15 +228,16 @@ delete from t1 where count(*)=1; drop table t1; # BUG#12915: Optimize "DELETE|UPDATE ... ORDER BY ... LIMIT n" to use an index -create table t1 ( a int, index (a) ); -insert into t1 values (0),(0),(0),(0),(0),(0),(0),(0); +create table t1 ( a int, b int default 0, index (a) ); +insert into t1 (a) values (0),(0),(0),(0),(0),(0),(0),(0); flush status; select a from t1 order by a limit 1; show status like 'handler_read%'; flush status; -update t1 set a=unix_timestamp() order by a limit 1; +update t1 set a=9999 order by a limit 1; +update t1 set b=9999 order by a limit 1; show status like 'handler_read%'; flush status; @@ -253,7 +254,37 @@ flush status; delete from t1 order by a limit 1; show status like 'handler_read%'; -select count(*) from t1; +select * from t1; +update t1 set a=a+10,b=1 order by a limit 3; +update t1 set a=a+11,b=2 order by a limit 3; +update t1 set a=a+12,b=3 order by a limit 3; +select * from t1 order by a; drop table t1; + +# +# Bug#14186 select datefield is null not updated +# +create table t1 (f1 date not null); +insert into t1 values('2000-01-01'),('0000-00-00'); +update t1 set f1='2002-02-02' where f1 is null; +select * from t1; +drop table t1; + +# +# Bug#15028 Multitable update returns different numbers of matched rows +# depending on table order +create table t1 (f1 int); +create table t2 (f2 int); +insert into t1 values(1),(2); +insert into t2 values(1),(1); +--enable_info +update t1,t2 set f1=3,f2=3 where f1=f2 and f1=1; +--disable_info +update t2 set f2=1; +update t1 set f1=1 where f1=3; +--enable_info +update t2,t1 set f1=3,f2=3 where f1=f2 and f1=1; +--disable_info +drop table t1,t2; # End of 4.1 tests diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index e3b5b4ef33e..61861c26ea8 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -70,6 +70,10 @@ create table t1 (i int not null); insert t1 values (1),(2),(2),(3),(3),(3); select @a:=0; select @a, @a:=@a+count(*), count(*), @a from t1 group by i; select @a:=0; select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i; + +set @a=0; +select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i; +select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i; drop table t1; # diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index f23cc0152c1..2d0e2dbc9c9 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -237,6 +237,10 @@ set global rpl_recovery_rank=100; set global server_id=100; set global slow_launch_time=100; set sort_buffer_size=100; +set @@max_sp_recursion_depth=10; +select @@max_sp_recursion_depth; +set @@max_sp_recursion_depth=0; +select @@max_sp_recursion_depth; set sql_auto_is_null=1; select @@sql_auto_is_null; set @@sql_auto_is_null=0; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index dd773c4c650..db6c12fdacb 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 @@ -147,11 +147,9 @@ insert into t1 values (1), (2), (3); create view v1 (a) as select a+1 from t1; create view v2 (a) as select a-1 from t1; ---disable_parsing # WL #2486 should enable these tests select * from t1 natural left join v1; select * from v2 natural left join t1; select * from v2 natural left join v1; ---enable_parsing drop view v1, v2; drop table t1; @@ -1746,7 +1744,6 @@ drop function f1; CHECK TABLE v1, v2, v3, v4, v5, v6; create function f1 () returns int return (select max(col1) from t1); DROP TABLE t1; -# following will show underlying table until BUG#11555 fix CHECK TABLE v1, v2, v3, v4, v5, v6; drop function f1; drop function f2; @@ -2081,8 +2078,8 @@ drop table t1, t2; # # DEFINER information check # --- error ER_NO_VIEW_USER -create definer=some_user@__% sql security invoker view v1 as select 1; +-- 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; drop view v1; @@ -2189,4 +2186,156 @@ EXPLAIN SELECT * FROM v2 WHERE a=1; DROP VIEW v1,v2; DROP TABLE t1,t2,t3; +# +# Bug #13622 Wrong view .frm created if some field's alias contain \n +# +create table t1 (f1 int); +create view v1 as select t1.f1 as '123 +456' from t1; +select * from v1; +drop view v1; +drop table t1; + +# Bug #14466 lost sort order in GROUP_CONCAT() in a view +# +create table t1 (f1 int, f2 int); +insert into t1 values(1,1),(1,2),(1,3); +create view v1 as select f1 ,group_concat(f2 order by f2 asc) from t1 group by f1; +create view v2 as select f1 ,group_concat(f2 order by f2 desc) from t1 group by f1; +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; + +# +# Bug #14816 test_if_order_by_key() expected only Item_fields. +# +create table t1 (id INT, primary key(id)); +insert into t1 values (1),(2); +create view v1 as select * from t1; +explain select id from v1 order by id; +drop view v1; +drop table t1; + +# +# Bug #14850 Item_ref's values wasn't updated +# +create table t1(f1 int, f2 int); +insert into t1 values (null, 10), (null,2); +select f1, sum(f2) from t1 group by f1; +create view v1 as select * from t1; +select f1, sum(f2) from v1 group by f1; +drop view v1; +drop table t1; + +# +# BUG#14885: incorrect SOURCE in view created in a procedure +# TODO: here SOURCE string must be shown when it will be possible +# +--disable_warnings +drop procedure if exists p1; +--enable_warnings +delimiter //; +create procedure p1 () deterministic +begin +create view v1 as select 1; +end; +// +delimiter ;// +call p1(); +show create view v1; +drop view v1; +drop procedure p1; + +# +# BUG#15096: using function with view for view creation +# +CREATE VIEW v1 AS SELECT 42 AS Meaning; +--disable_warnings +DROP FUNCTION IF EXISTS f1; +--enable_warnings +DELIMITER //; +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN + DECLARE retn INTEGER; + SELECT Meaning FROM v1 INTO retn; + RETURN retn; +END +// +DELIMITER ;// +CREATE VIEW v2 AS SELECT f1(); +select * from v2; +drop view v2,v1; +drop function f1; + +# +# Bug#14861: aliased column names are not preserved. +# +create table t1 (id numeric, warehouse_id numeric); +create view v1 as select id from t1; +create view v2 as +select t1.warehouse_id, v1.id as receipt_id +from t1, v1 where t1.id = v1.id; + +insert into t1 (id, warehouse_id) values(3, 2); +insert into t1 (id, warehouse_id) values(4, 2); +insert into t1 (id, warehouse_id) values(5, 1); + +select v2.receipt_id as alias1, v2.receipt_id as alias2 from v2 +order by v2.receipt_id; + +drop view v2, v1; +drop table t1; diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index ea93345e894..04d6d2f323b 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 @@ -401,8 +401,302 @@ grant all privileges on mysqltest.* to mysqltest_1@localhost; connection user1; use mysqltest; create view v1 as select * from t1; +use test; connection root; revoke all privileges on mysqltest.* from mysqltest_1@localhost; drop database mysqltest; +# +# view definer grants revoking +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +create table mysqltest.t1 (a int, b int); + +grant select on mysqltest.t1 to mysqltest_1@localhost; +grant create view,select on test.* to mysqltest_1@localhost; + +connection user1; + +create view v1 as select * from mysqltest.t1; + +connection root; +# check view definer information +show create view v1; +revoke select on mysqltest.t1 from mysqltest_1@localhost; +-- error ER_VIEW_INVALID +select * from v1; +grant select on mysqltest.t1 to mysqltest_1@localhost; +select * from v1; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop view v1; +drop database mysqltest; + +# +# rights on execution of view underlying functiond (BUG#9505) +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +use mysqltest; +create table t1 (a int); +insert into t1 values (1); +create table t2 (s1 int); +--disable_warnings +drop function if exists f2; +--enable_warnings +delimiter //; +create function f2 () returns int begin declare v int; select s1 from t2 +into v; return v; end// +delimiter ;// +create algorithm=TEMPTABLE view v1 as select f2() from t1; +create algorithm=MERGE view v2 as select f2() from t1; +create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; +create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; +create SQL SECURITY INVOKER view v5 as select * from v4; +grant select on v1 to mysqltest_1@localhost; +grant select on v2 to mysqltest_1@localhost; +grant select on v3 to mysqltest_1@localhost; +grant select on v4 to mysqltest_1@localhost; +grant select on v5 to mysqltest_1@localhost; + +connection user1; +use mysqltest; +select * from v1; +select * from v2; +-- error ER_VIEW_INVALID +select * from v3; +-- error ER_VIEW_INVALID +select * from v4; +-- error ER_VIEW_INVALID +select * from v5; +use test; + +connection root; +drop view v1, v2, v3, v4, v5; +drop function f2; +drop table t1, t2; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop database mysqltest; + +# +# revertion of previous test, definer of view lost his/her rights to execute +# function +# + +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +use mysqltest; +create table t1 (a int); +insert into t1 values (1); +create table t2 (s1 int); +--disable_warnings +drop function if exists f2; +--enable_warnings +delimiter //; +create function f2 () returns int begin declare v int; select s1 from t2 +into v; return v; end// +delimiter ;// +grant select on t1 to mysqltest_1@localhost; +grant execute on function f2 to mysqltest_1@localhost; +grant create view on mysqltest.* to mysqltest_1@localhost; + +connection user1; +use mysqltest; +create algorithm=TEMPTABLE view v1 as select f2() from t1; +create algorithm=MERGE view v2 as select f2() from t1; +create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; +create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; +use test; + +connection root; +create view v5 as select * from v1; +revoke execute on function f2 from mysqltest_1@localhost; +-- error ER_VIEW_INVALID +select * from v1; +-- error ER_VIEW_INVALID +select * from v2; +select * from v3; +select * from v4; +-- error ER_VIEW_INVALID +select * from v5; + +drop view v1, v2, v3, v4, v5; +drop function f2; +drop table t1, t2; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop database mysqltest; + +# +# definer/invoker rights for columns +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +use mysqltest; +create table t1 (a int); +create table v1 (a int); +insert into t1 values (1); +grant select on t1 to mysqltest_1@localhost; +grant select on v1 to mysqltest_1@localhost; +grant create view on mysqltest.* to mysqltest_1@localhost; +drop table v1; + +connection user1; +use mysqltest; +create algorithm=TEMPTABLE view v1 as select *, a as b from t1; +create algorithm=MERGE view v2 as select *, a as b from t1; +create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; +create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; +create view v5 as select * from v1; +use test; + +connection root; +revoke select on t1 from mysqltest_1@localhost; +-- error ER_VIEW_INVALID +select * from v1; +-- error ER_VIEW_INVALID +select * from v2; +select * from v3; +select * from v4; +-- error ER_VIEW_INVALID +select * from v5; + +#drop view v1, v2, v3, v4, v5; +drop table t1; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop database mysqltest; + + +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +use mysqltest; +create table t1 (a int); +insert into t1 values (1); +create algorithm=TEMPTABLE view v1 as select *, a as b from t1; +create algorithm=MERGE view v2 as select *, a as b from t1; +create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; +create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; +create SQL SECURITY INVOKER view v5 as select * from v4; +grant select on v1 to mysqltest_1@localhost; +grant select on v2 to mysqltest_1@localhost; +grant select on v3 to mysqltest_1@localhost; +grant select on v4 to mysqltest_1@localhost; +grant select on v5 to mysqltest_1@localhost; + +connection user1; +use mysqltest; +select * from v1; +select * from v2; +-- error ER_VIEW_INVALID +select * from v3; +-- error ER_VIEW_INVALID +select * from v4; +-- error ER_VIEW_INVALID +select * from v5; +use test; + +connection root; +drop view v1, v2, v3, v4, v5; +drop table t1; +use test; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop database mysqltest; + +# +# BUG#14256: definer in view definition is not fully qualified +# +--disable_warnings +drop view if exists v1; +--enable_warnings + +# Backup anonymous users and remove them. (They get in the way of +# the one we test with here otherwise.) +create table t1 as select * from mysql.user where user=''; +delete from mysql.user where user=''; +flush privileges; + +# Create the test user +grant all on test.* to 'test14256'@'%'; + +connect (test14256,localhost,test14256,,test); +connection test14256; +use test; + +create view v1 as select 42; +show create view v1; + +select definer into @v1def1 from information_schema.views + where table_schema = 'test' and table_name='v1'; +drop view v1; + +create definer=`test14256`@`%` view v1 as select 42; +show create view v1; + +select definer into @v1def2 from information_schema.views + where table_schema = 'test' and table_name='v1'; +drop view v1; + +select @v1def1, @v1def2, @v1def1=@v1def2; + +connection root; +drop user test14256; + +# Restore the anonymous users. +insert into mysql.user select * from t1; +flush privileges; + +drop table t1; + +# +# BUG#14726: freeing stack variable in case of an error of opening +# a view when we have locked tables with LOCK TABLES statement. +# +connection root; +--disable_warnings +create database mysqltest; +--enable_warnings + +use mysqltest; +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost; + +connection user1; + +use mysqltest; +LOCK TABLES v1 READ; +-- error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE TABLE v1; +UNLOCK TABLES; +use test; + +connection root; +use test; +drop user mysqltest_1@localhost; +drop database mysqltest; + +# +# switch to default connaction +# +disconnect user1; +disconnect root; +connection default; diff --git a/mysql-test/t/view_query_cache.test b/mysql-test/t/view_query_cache.test index 81994407641..d4ebe45b7ac 100644 --- a/mysql-test/t/view_query_cache.test +++ b/mysql-test/t/view_query_cache.test @@ -96,4 +96,35 @@ unlock tables; set query_cache_wlock_invalidate=default; drop view v1; drop table t1; + +# +# BUG#15119: returning temptable view from the query cache. +# +flush status; +create table t1 (a int, b int); +create algorithm=temptable view v1 as select * from t1; +select * from v1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from v1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +insert into t1 values (1,1); +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from v1; +select * from v1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +drop view v1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +drop table t1; + +# Reset default environment. set GLOBAL query_cache_size=default; |