diff options
Diffstat (limited to 'mysql-test/t')
| -rw-r--r-- | mysql-test/t/derived_view.test | 93 | ||||
| -rw-r--r-- | mysql-test/t/join_cache.test | 132 | ||||
| -rw-r--r-- | mysql-test/t/kill_processlist-6619.test | 17 | ||||
| -rw-r--r-- | mysql-test/t/sp-bugs.test | 13 | ||||
| -rw-r--r-- | mysql-test/t/variables.test | 8 |
5 files changed, 261 insertions, 2 deletions
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index de08b1c4d0d..67899837bb2 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1731,6 +1731,99 @@ drop table t1,t2; set optimizer_switch=@save_optimizer_switch5740; --echo # +--echo # Bug mdev-5721: possible long key access to a materialized derived table +--echo # (see also the test case for Bug#13261277 that is actually the same bug) +--echo # + +CREATE TABLE t1 ( + id varchar(255) NOT NULL DEFAULT '', + familyid int(11) DEFAULT NULL, + withdrawndate date DEFAULT NULL, + KEY index_td_familyid_id (familyid,id) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + +CREATE TABLE t2 ( + id int(11) NOT NULL AUTO_INCREMENT, + activefromts datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + shortdescription text, + useraccessfamily varchar(512) DEFAULT NULL, + serialized longtext, + PRIMARY KEY (id) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + +insert into t1 values ('picture/89/1369722032695.pmd',89,NULL); +insert into t1 values ('picture/90/1369832057370.pmd',90,NULL); +insert into t2 values (38,'2013-03-04 07:49:22','desc','CODE','string'); + +EXPLAIN +SELECT * FROM t2 x, +(SELECT t2.useraccessfamily, t2.serialized AS picturesubuser, COUNT(*) + FROM t2, t1 GROUP BY t2.useraccessfamily, picturesubuser) y +WHERE x.useraccessfamily = y.useraccessfamily; + +SELECT * FROM t2 x, +(SELECT t2.useraccessfamily, t2.serialized AS picturesubuser, COUNT(*) + FROM t2, t1 GROUP BY t2.useraccessfamily, picturesubuser) y +WHERE x.useraccessfamily = y.useraccessfamily; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug#13261277: Unchecked key length caused missing records. +--echo # + +CREATE TABLE t1 ( + col_varchar varchar(1024) CHARACTER SET utf8 DEFAULT NULL, + stub1 varchar(1024) CHARACTER SET utf8 DEFAULT NULL, + stub2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL, + stub3 varchar(1024) CHARACTER SET utf8 DEFAULT NULL +); + +INSERT INTO t1 VALUES + ('d','d','l','ther'), + (NULL,'s','NJBIQ','trzetuchv'), + (-715390976,'coul','MYWFB','cfhtrzetu'), + (1696792576,'f','i\'s','c'), + (1,'i','ltpemcfhtr','gsltpemcf'), + (-663027712,'mgsltpemcf','sa','amgsltpem'), + (-1686700032,'JPRVK','i','vamgsltpe'), + (NULL,'STUNB','UNVJV','u'), + (5,'oka','qyihvamgsl','AXSMD'), + (NULL,'tqwmqyihva','h','yntqwmqyi'), + (3,'EGMJN','e','e'); + +CREATE TABLE t2 ( + col_varchar varchar(10) DEFAULT NULL, + col_int INT DEFAULT NULL +); + +INSERT INTO t2 VALUES ('d',9); + +set optimizer_switch='derived_merge=off,derived_with_keys=on'; + +SET @save_heap_size= @@max_heap_table_size; +SET @@max_heap_table_size= 16384; + +SELECT t2.col_int +FROM t2 + RIGHT JOIN ( SELECT * FROM t1 ) AS dt + ON t2.col_varchar = dt.col_varchar +WHERE t2.col_int IS NOT NULL ; + +--echo # Shouldn't use auto_key0 for derived table +EXPLAIN +SELECT t2.col_int +FROM t2 + RIGHT JOIN ( SELECT * FROM t1 ) AS dt + ON t2.col_varchar = dt.col_varchar +WHERE t2.col_int IS NOT NULL ; + +SET @@max_heap_table_size= @save_heap_size; +SET optimizer_switch=@save_optimizer_switch; + +DROP TABLE t1,t2; + +--echo # --echo # end of 5.3 tests --echo # diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index c60a06f0b0b..0e4610b9f54 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3589,7 +3589,7 @@ DROP TABLE t1,t2,t3; --echo # --echo # Bug #1058071: LEFT JOIN using blobs ---echo # (mdev-564) when join buffer size is small +--echo # (MDEV-564) when join buffer size is small --echo # CREATE TABLE t1 ( @@ -3656,6 +3656,134 @@ explain select * from t0,t1 left join t2 on t1.b=t2.b order by t0.a, t1.a; drop table t0,t1,t2; +--echo # MDEV-6292: huge performance degradation for a sequence +--echo # of LEFT JOIN operations when using join buffer +--echo # + +--source include/have_innodb.inc + +CREATE TABLE t1 ( + id int(11) NOT NULL AUTO_INCREMENT, + col1 varchar(255) NOT NULL DEFAULT '', + PRIMARY KEY (id) +) ENGINE=INNODB; + +CREATE TABLE t2 ( + id int(11) NOT NULL AUTO_INCREMENT, + parent_id smallint(3) NOT NULL DEFAULT '0', + col2 varchar(25) NOT NULL DEFAULT '', + PRIMARY KEY (id) +) ENGINE=INNODB; + +set join_buffer_size=8192; + +set join_cache_level=0; + +set @init_time:=now(); +SELECT t.* +FROM + t1 t + LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" + LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val" + LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val" + LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val" + LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val" + LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val" + LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val" + LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val" + LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val" + LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val" + LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val" + LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val" + LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val" + LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val" + LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val" + LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val" + LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val" + LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val" + LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val" + LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val" + LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val" + LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val" + LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" + LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" + LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" +ORDER BY + col1; +select timestampdiff(second, @init_time, now()) <= 1; + +set join_cache_level=2; + +set @init_time:=now(); +SELECT t.* +FROM + t1 t + LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" + LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val" + LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val" + LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val" + LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val" + LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val" + LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val" + LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val" + LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val" + LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val" + LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val" + LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val" + LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val" + LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val" + LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val" + LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val" + LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val" + LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val" + LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val" + LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val" + LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val" + LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val" + LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" + LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" + LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" +ORDER BY + col1; +select timestampdiff(second, @init_time, now()) <= 1; + +EXPLAIN +SELECT t.* +FROM + t1 t + LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" + LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val" + LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val" + LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val" + LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val" + LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val" + LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val" + LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val" + LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val" + LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val" + LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val" + LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val" + LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val" + LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val" + LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val" + LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val" + LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val" + LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val" + LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val" + LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val" + LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val" + LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val" + LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" + LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" + LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" +ORDER BY + col1; + +set join_buffer_size=default; +set join_cache_level = default; + +DROP TABLE t1,t2; + --echo # --echo # MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins. --echo # @@ -3701,7 +3829,7 @@ drop table t1,t2,t3; set expensive_subquery_limit=default; --echo # ---echo # mdev-6071: EXPLAIN chooses to use join buffer while execution turns it down +--echo # MDEV-6071: EXPLAIN chooses to use join buffer while execution turns it down --echo # create table t1 (a int); diff --git a/mysql-test/t/kill_processlist-6619.test b/mysql-test/t/kill_processlist-6619.test new file mode 100644 index 00000000000..2333f02eac6 --- /dev/null +++ b/mysql-test/t/kill_processlist-6619.test @@ -0,0 +1,17 @@ +# +# MDEV-6619 SHOW PROCESSLIST returns empty result set after KILL QUERY +# +--source include/not_embedded.inc +--enable_connect_log +--connect (con1,localhost,root,,) +--let $con_id = `SELECT CONNECTION_ID()` +--replace_column 1 # 3 # 6 # 7 # +SHOW PROCESSLIST; +--connection default +--replace_result $con_id con_id +eval KILL QUERY $con_id; +--connection con1 +--error ER_QUERY_INTERRUPTED +SHOW PROCESSLIST; +--replace_column 1 # 3 # 6 # 7 # +SHOW PROCESSLIST; diff --git a/mysql-test/t/sp-bugs.test b/mysql-test/t/sp-bugs.test index 8e6a25709aa..4671aee11e1 100644 --- a/mysql-test/t/sp-bugs.test +++ b/mysql-test/t/sp-bugs.test @@ -294,3 +294,16 @@ DELIMITER ;$$ CALL test_5531(1); DROP PROCEDURE test_5531; DROP TABLE t1; + +# +# MDEV-6601 Assertion `!thd->in_active_multi_stmt_transa ction() || thd->in_multi_stmt_transaction_mode()' failed on executing a stored procedure with commit +# +delimiter |; +create procedure sp() begin + commit; +end| +delimiter ;| +start transaction; +call sp(); +drop procedure sp; + diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index e7e621081d6..79cd5e1b24d 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -1527,4 +1527,12 @@ set session rand_seed1=DEFAULT; --error ER_BAD_FIELD_ERROR set autocommit = values(v); +# +# MDEV-6673 I_S.SESSION_VARIABLES shows global values +# +set session sql_mode=ansi_quotes; +select * from information_schema.session_variables where variable_name='sql_mode'; +show global status like 'foobar'; +select * from information_schema.session_variables where variable_name='sql_mode'; + --echo End of 5.5 tests |