From f031f0ae67d65b1804f028551e48b96bcbcd8dad Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Tue, 11 Mar 2014 17:37:46 +0200 Subject: Fixed test failure (5.5 had different test result than 10.0) --- mysql-test/r/stack-crash.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/stack-crash.result b/mysql-test/r/stack-crash.result index be5b6464b9a..d0a25837675 100644 --- a/mysql-test/r/stack-crash.result +++ b/mysql-test/r/stack-crash.result @@ -1,7 +1,7 @@ drop table if exists t1,t2; Warnings: -Note 1051 Unknown table 'test.t1' -Note 1051 Unknown table 'test.t2' +Note 1051 Unknown table 't1' +Note 1051 Unknown table 't2' CREATE TABLE t1 ( `sspo_id` int(11) NOT NULL AUTO_INCREMENT, `sspo_uid` int(11) NOT NULL DEFAULT '0', -- cgit v1.2.1 From cb5b6c7e3902f28bf1108cbf86734e78a1c1336c Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 11 Mar 2014 16:45:08 +0100 Subject: MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703) (This is attempt at fix #2) (re-commit with fixed typo) - Moved the testcase from partition_test to partition_innodb.test where it can really work. - Made ordered index scans over ha_partition tables to satisfy ROR property for the case where underlying table uses extended keys. --- mysql-test/r/partition.result | 39 ------------------------------------ mysql-test/r/partition_innodb.result | 39 ++++++++++++++++++++++++++++++++++++ mysql-test/r/partition_order.result | 6 +++--- 3 files changed, 42 insertions(+), 42 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 521d31ae114..4379cf17ec0 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -2494,45 +2494,6 @@ i 4 DROP TABLE t1; # -# MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703) -# -create table t1 ( -a int not null, -b int not null, -pk int not null, -primary key (pk), -key(a), -key(b) -) partition by hash(pk) partitions 10; -insert into t1 values (1,2,4); -insert into t1 values (1,0,17); -insert into t1 values (1,2,25); -insert into t1 values (10,20,122); -insert into t1 values (10,20,123); -create table t2 (a int); -insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -insert into t1 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t2 A, t2 B, t2 C; -insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a, -10+A.a + 10*B.a + 100*C.a + 1000*D.a, -2000 + A.a + 10*B.a + 100*C.a + 1000*D.a -from t2 A, t2 B, t2 C ,t2 D; -explain select * from t1 where a=1 and b=2 and pk between 1 and 999999 ; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY,a,b b 4 const 982 Using where -create temporary table t3 as -select * from t1 where a=1 and b=2 and pk between 1 and 999 ; -select count(*) from t3; -count(*) -802 -drop table t3; -create temporary table t3 as -select * from t1 ignore index(a,b) where a=1 and b=2 and pk between 1 and 999 ; -select count(*) from t3; -count(*) -802 -drop table t3; -drop table t1,t2; -# # MDEV-5555: Incorrect index_merge on BTREE indices # CREATE TABLE t1 ( diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index 45b406a5ad0..30a225cadeb 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -639,4 +639,43 @@ col1 col2 col3 1 2 2013-03-11 16:33:04 1 2 2013-03-11 16:33:24 DROP TABLE t1; +# +# MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703) +# +create table t1 ( +a int not null, +b int not null, +pk int not null, +primary key (pk), +key(a), +key(b) +) engine=innodb partition by hash(pk) partitions 10; +insert into t1 values (1,2,4); +insert into t1 values (1,0,17); +insert into t1 values (1,2,25); +insert into t1 values (10,20,122); +insert into t1 values (10,20,123); +create table t2 (a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t1 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t2 A, t2 B, t2 C; +insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a, +10+A.a + 10*B.a + 100*C.a + 1000*D.a, +2000 + A.a + 10*B.a + 100*C.a + 1000*D.a +from t2 A, t2 B, t2 C ,t2 D; +explain select * from t1 where a=1 and b=2 and pk between 1 and 999999 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge PRIMARY,a,b b,a 4,4 NULL # Using intersect(b,a); Using where; Using index +create temporary table t3 as +select * from t1 where a=1 and b=2 and pk between 1 and 999 ; +select count(*) from t3; +count(*) +802 +drop table t3; +create temporary table t3 as +select * from t1 ignore index(a,b) where a=1 and b=2 and pk between 1 and 999 ; +select count(*) from t3; +count(*) +802 +drop table t3; +drop table t1,t2; set global default_storage_engine=default; diff --git a/mysql-test/r/partition_order.result b/mysql-test/r/partition_order.result index 06c1b63a382..cecfc90eefb 100644 --- a/mysql-test/r/partition_order.result +++ b/mysql-test/r/partition_order.result @@ -734,8 +734,8 @@ a b 7 1 35 2 3 3 -2 4 30 4 +2 4 4 5 6 6 select * from t1 force index (b) where b < 10 ORDER BY b; @@ -744,16 +744,16 @@ a b 7 1 35 2 3 3 -2 4 30 4 +2 4 4 5 6 6 select * from t1 force index (b) where b < 10 ORDER BY b DESC; a b 6 6 4 5 -30 4 2 4 +30 4 3 3 35 2 7 1 -- cgit v1.2.1 From 1f2ef5740360148961151a71603faae497919aa6 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 6 Mar 2014 13:56:34 -0800 Subject: Fixed bug mdev-5686. The calls of the function remove_eq_conds() may change the and/or structure of the where conditions. So JOIN::equal_cond should be updated for non-recursive calls of remove_eq_conds(). --- mysql-test/r/subselect4.result | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 6c314c448cb..6ab62c801f7 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2375,5 +2375,27 @@ ORDER BY v2.id; id a2 a3 id a2 a3 DROP VIEW v2; DROP TABLE t1,t2; +# +# MDEV-5686: degenerate disjunct in NOT IN subquery +# +CREATE TABLE t1 (a int, b int, c varchar(3)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'CAN'),(2,2,'AUS'); +CREATE TABLE t2 (f int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3); +EXPLAIN EXTENDED +SELECT * FROM t2 +WHERE f NOT IN (SELECT b FROM t1 +WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select 3 AS `f` from `test`.`t2` where (not(<3>((3,(select `test`.`t1`.`b` from `test`.`t1` where (((`test`.`t1`.`c` = 'USA') or (`test`.`t1`.`c` <> 'USA')) and trigcond((((3) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`))) and (`test`.`t1`.`b` = `test`.`t1`.`a`)) having trigcond((`test`.`t1`.`b`))))))) +SELECT * FROM t2 +WHERE f NOT IN (SELECT b FROM t1 +WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b); +f +3 +DROP TABLE t1,t2; SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; -- cgit v1.2.1 From 321ff25f3d419bc1a1fae2390450d1b4db30b64c Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 7 Mar 2014 13:57:07 +0200 Subject: MDEV-5740: Assertion `!derived->first_select()->exclude_from_table_unique_test || derived->outer_select()-> exclude_from_table_unique_test' failed on 2nd execution of PS with derived_merge Do not check tables of executed units. Debug info about stages of derived tables execution added. --- mysql-test/r/derived_view.result | 40 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 40 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index acce2afb124..e958bfbdca4 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2356,6 +2356,46 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DEPENDENT SUBQUERY pi ref gallery_id gallery_id 4 test.gal.id 4 Using temporary; Using filesort drop table galleries, pictures; # +# MDEV-5740: Assertion +#`!derived->first_select()->exclude_from_table_unique_test || +#derived->outer_select()-> exclude_from_table_unique_test' +#failed on 2nd execution of PS with derived_merge +# +set @save_optimizer_switch5740=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on'; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); +PREPARE stmt FROM ' + INSERT INTO t1 SELECT * FROM t2 UNION SELECT * FROM (SELECT * FROM t1) AS sq +'; +EXECUTE stmt; +select * from t1; +a +1 +2 +3 +4 +1 +2 +EXECUTE stmt; +select * from t1; +a +1 +2 +3 +4 +1 +2 +3 +4 +1 +2 +deallocate prepare stmt; +drop table t1,t2; +set optimizer_switch=@save_optimizer_switch5740; +# # end of 5.3 tests # set optimizer_switch=@exit_optimizer_switch; -- cgit v1.2.1 From d8ea8a3d13d2a754a1e80d11619ba3347bb7ee5f Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 12 Mar 2014 12:34:16 +0200 Subject: MDEV-5717: Server crash with insert statement containing DEFAULT into view Item_default_value::arg can be NULL so walk() should take it into consideration. --- mysql-test/r/view.result | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 8e5c082c6c1..1435b0c7a97 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4790,6 +4790,22 @@ v1_field1 deallocate prepare my_stmt; DROP VIEW v1,v2; DROP TABLE t1,t2,t3,t4; +# +#MDEV-5717: Server crash with insert statement containing DEFAULT into +#view +# +CREATE TABLE t1 ( +`id` int(10) unsigned NOT NULL AUTO_INCREMENT, +`test` tinyint(3) unsigned NOT NULL DEFAULT '0', +PRIMARY KEY (`id`) +); +CREATE VIEW v1 AS (select t1.id AS id, t1.test AS test from t1); +INSERT INTO v1 SET test = DEFAULT; +select * from v1; +id test +1 0 +drop view v1; +drop table t1; # ----------------------------------------------------------------- # -- End of 5.3 tests. # ----------------------------------------------------------------- -- cgit v1.2.1 From 8c04dd33dd7ab1c261bd15ace2009832af7a2f98 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Thu, 13 Mar 2014 12:20:57 +0100 Subject: MDEV-5811: Server crashes in best_access_path with materialization+semijoin and big_tables=ON - With big_tables=ON, materialized table will use Aria (or MyISAM) SE, which allows prefix key reads. However, the temp.table has rec_per_key=NULL which causes the optimizer to crash when attempting to read index statistics for a prefix index read. - Fixed by providing a rec_per_key array with zeros (i.e. "no statistics data") --- mysql-test/r/subselect_mat.result | 14 ++++++++++++++ mysql-test/r/subselect_sj_mat.result | 14 ++++++++++++++ 2 files changed, 28 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index c47881d5434..7743e804f17 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2041,6 +2041,20 @@ EXECUTE stmt; a DROP TABLE t1, t2; DROP VIEW v2; +# +# MDEV-5811: Server crashes in best_access_path with materialization+semijoin and big_tables=ON +# +SET @tmp_mdev5811= @@big_tables; +SET big_tables = ON; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); +SELECT * FROM t1 AS t1_1, t1 AS t1_2 +WHERE ( t1_1.a, t1_2.a ) IN ( SELECT MAX(b), MIN(b) FROM t2 ); +a a +DROP TABLE t1,t2; +SET big_tables=@tmp_mdev5811; # End of 5.3 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index f267f32c887..9f3cb5ce0b8 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2081,4 +2081,18 @@ EXECUTE stmt; a DROP TABLE t1, t2; DROP VIEW v2; +# +# MDEV-5811: Server crashes in best_access_path with materialization+semijoin and big_tables=ON +# +SET @tmp_mdev5811= @@big_tables; +SET big_tables = ON; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); +SELECT * FROM t1 AS t1_1, t1 AS t1_2 +WHERE ( t1_1.a, t1_2.a ) IN ( SELECT MAX(b), MIN(b) FROM t2 ); +a a +DROP TABLE t1,t2; +SET big_tables=@tmp_mdev5811; # End of 5.3 tests -- cgit v1.2.1 From 5616bd55973cd1c4f4005e7033e7adff7397214d Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 13 Mar 2014 16:35:14 +0100 Subject: unix_socket bypasses make_if_fail by not doing any network reads --- mysql-test/r/failed_auth_unixsocket.result | 12 ++++++++++++ 1 file changed, 12 insertions(+) create mode 100644 mysql-test/r/failed_auth_unixsocket.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/failed_auth_unixsocket.result b/mysql-test/r/failed_auth_unixsocket.result new file mode 100644 index 00000000000..7e2499795cb --- /dev/null +++ b/mysql-test/r/failed_auth_unixsocket.result @@ -0,0 +1,12 @@ +update mysql.user set plugin='unix_socket'; +flush privileges; +connect(localhost,USER,,test,MASTER_PORT,MASTER_SOCKET); +ERROR HY000: Plugin 'unix_socket' is not loaded +ERROR HY000: Plugin 'unix_socket' is not loaded +install plugin unix_socket soname 'auth_socket.so'; +connect(localhost,USER,,test,MASTER_PORT,MASTER_SOCKET); +ERROR 28000: Access denied for user 'USER'@'localhost' (using password: NO) +ERROR 28000: Access denied for user 'USER'@'localhost' (using password: NO) +update mysql.user set plugin=''; +flush privileges; +uninstall plugin unix_socket; -- cgit v1.2.1 From 5c8ed7dd5473c398f788aba3aff5c168a50df83a Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 14 Mar 2014 11:38:17 +0200 Subject: MDEV-5446: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' fails on EXPLAIN EXTENDED with VALUES function field_index should be set correctly for null field created by Item_insert_value::fix_fields(). --- mysql-test/r/func_misc.result | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 56d27ff78be..83513446edf 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -272,3 +272,19 @@ ERROR HY000: Incorrect arguments to NAME_CONST SELECT NAME_CONST('a', -(1)) OR 1; NAME_CONST('a', -(1)) OR 1 1 +# +#MDEV-5446: Assertion `!table || (!table->read_set || +#bitmap_is_set(table->read_set, field_index))' fails on +#EXPLAIN EXTENDED with VALUES function +# +CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,10); +CREATE VIEW v1 AS SELECT * FROM t1; +EXPLAIN EXTENDED SELECT VALUES(b) FROM v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select values(10) AS `VALUES(b)` from `test`.`t1` +drop view v1; +drop table t1; +End of 5.3 tests -- cgit v1.2.1 From 63d0918807739dd19578ce9db80acbfd8ea158e6 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Sun, 23 Mar 2014 16:02:56 +0400 Subject: MDEV-5783 Assertion `0' failed in make_sortkey(SORTPARAM*, uchar*, uchar*) on ORDER BY HEX( UNCOMPRESSED_LENGTH( pk ) ) --- mysql-test/r/func_compress.result | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result index 37f7c475148..105f2a53926 100644 --- a/mysql-test/r/func_compress.result +++ b/mysql-test/r/func_compress.result @@ -127,3 +127,23 @@ NULL 825307441 EXPLAIN EXTENDED SELECT * FROM (SELECT UNCOMPRESSED_LENGTH(c1) FROM t1) AS s; DROP TABLE t1; End of 5.0 tests +# +# Start of 5.3 tests +# +# +# MDEV-5783 Assertion `0' failed in make_sortkey(SORTPARAM*, uchar*, uchar*) on ORDER BY HEX(UNCOMPRESSED_LENGTH(pk)) +# +CREATE TABLE t1 (pk INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); +SELECT UNCOMPRESSED_LENGTH(pk) FROM t1; +UNCOMPRESSED_LENGTH(pk) +NULL +NULL +Warnings: +Error 1259 ZLIB: Input data corrupted +Error 1259 ZLIB: Input data corrupted +SELECT * FROM t1 ORDER BY HEX(UNCOMPRESSED_LENGTH(pk)); +DROP TABLE t1; +# +# End of 5.3 tests +# -- cgit v1.2.1 From 44002a34e680c79c01df879b540458c2885e97e8 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Wed, 26 Mar 2014 19:56:23 +0100 Subject: MDEV-5955 Server crashes in handler::ha_external_lock or assertion `m_lock_type == 2' fails in handler::ha_close on disconnect with a locked temporary table first unlock locked tables, then close and remove temporary --- mysql-test/r/locked_temporary-5955.result | 2 ++ 1 file changed, 2 insertions(+) create mode 100644 mysql-test/r/locked_temporary-5955.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/locked_temporary-5955.result b/mysql-test/r/locked_temporary-5955.result new file mode 100644 index 00000000000..8999bdd39c4 --- /dev/null +++ b/mysql-test/r/locked_temporary-5955.result @@ -0,0 +1,2 @@ +CREATE TEMPORARY TABLE tmp (i INT) ENGINE=InnoDB; +LOCK TABLES tmp AS p WRITE; -- cgit v1.2.1