summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/innodb_icp.result4
-rw-r--r--mysql-test/r/range_vs_index_merge.result2
-rw-r--r--mysql-test/r/range_vs_index_merge_innodb.result2
-rw-r--r--mysql-test/r/selectivity.result85
-rw-r--r--mysql-test/r/selectivity_innodb.result85
-rw-r--r--mysql-test/suite/innodb/r/innodb_stats_persistent.result2
-rw-r--r--mysql-test/suite/innodb_fts/r/concurrent_insert.result23
-rw-r--r--mysql-test/suite/innodb_fts/r/innodb_fts_misc.result14
-rw-r--r--mysql-test/suite/innodb_fts/t/concurrent_insert.test31
-rw-r--r--mysql-test/suite/innodb_fts/t/innodb_fts_misc.test7
-rw-r--r--mysql-test/t/selectivity.test81
11 files changed, 325 insertions, 11 deletions
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index 9ba98ba5b13..77995a53383 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -679,7 +679,7 @@ EXPLAIN
SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
HAVING t1.c != 5 ORDER BY t1.c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort
1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where
SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
HAVING t1.c != 5 ORDER BY t1.c;
@@ -690,7 +690,7 @@ EXPLAIN
SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
HAVING t1.c != 5 ORDER BY t1.c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort
1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where
SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
HAVING t1.c != 5 ORDER BY t1.c;
diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index bc46a4fdd0b..4f3c36b7660 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -1795,7 +1795,7 @@ SELECT * FROM t1 FORCE KEY (state,capital)
WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range state,capital state 71 NULL 12 Using index condition; Using where
+1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where
SELECT * FROM t1 FORCE KEY (state,capital)
WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result
index ce90f522d6e..581f512768c 100644
--- a/mysql-test/r/range_vs_index_merge_innodb.result
+++ b/mysql-test/r/range_vs_index_merge_innodb.result
@@ -1796,7 +1796,7 @@ SELECT * FROM t1 FORCE KEY (state,capital)
WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range state,capital state 71 NULL 12 Using index condition; Using where
+1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where
SELECT * FROM t1 FORCE KEY (state,capital)
WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index eb0ae313231..10ab981acc8 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1673,5 +1673,90 @@ drop table t1;
set use_stat_tables= @save_use_stat_tables;
set @@histogram_size=@save_histogram_size;
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1)
+#
+set @@optimizer_use_condition_selectivity=2;
+set names utf8;
+CREATE DATABASE world;
+use world;
+CREATE TABLE Country (
+Code char(3) NOT NULL default '',
+Name char(52) NOT NULL default '',
+SurfaceArea float(10,2) NOT NULL default '0.00',
+Population int(11) NOT NULL default '0',
+Capital int(11) default NULL,
+PRIMARY KEY (Code),
+UNIQUE INDEX (Name)
+);
+CREATE TABLE City (
+ID int(11) NOT NULL auto_increment,
+Name char(35) NOT NULL default '',
+Country char(3) NOT NULL default '',
+Population int(11) NOT NULL default '0',
+PRIMARY KEY (ID),
+INDEX (Population),
+INDEX (Country)
+);
+CREATE TABLE CountryLanguage (
+Country char(3) NOT NULL default '',
+Language char(30) NOT NULL default '',
+Percentage float(3,1) NOT NULL default '0.0',
+PRIMARY KEY (Country, Language),
+INDEX (Percentage)
+);
+CREATE INDEX Name ON City(Name);
+CREATE INDEX CountryPopulation ON City(Country,Population);
+CREATE INDEX CountryName ON City(Country,Name);
+set @@optimizer_use_condition_selectivity=2;
+EXPLAIN
+SELECT * FROM City WHERE Country='FIN';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE City ref Country,CountryPopulation,CountryName CountryName 3 const 5 Using index condition
+DROP DATABASE world;
+use test;
+CREATE TABLE t1 (
+a INT,
+b INT NOT NULL,
+c char(100),
+KEY (b, c),
+KEY (b, a, c)
+) ENGINE=MyISAM
+DEFAULT CHARSET = utf8;
+INSERT INTO t1 VALUES
+(1, 1, 1),
+(2, 2, 2),
+(3, 3, 3),
+(4, 4, 4),
+(5, 5, 5),
+(6, 6, 6),
+(7, 7, 7),
+(8, 8, 8),
+(9, 9, 9);
+INSERT INTO t1 SELECT a + 10, b, c FROM t1;
+INSERT INTO t1 SELECT a + 20, b, c FROM t1;
+INSERT INTO t1 SELECT a + 40, b, c FROM t1;
+INSERT INTO t1 SELECT a + 80, b, c FROM t1;
+INSERT INTO t1 SELECT a + 160, b, c FROM t1;
+INSERT INTO t1 SELECT a + 320, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80;
+EXPLAIN
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+a
+2071
+2061
+2051
+2041
+2031
+2021
+2011
+2001
+1991
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+DROP TABLE t1;
# End of 10.1 tests
set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 9773b122e75..b9b281c7dc4 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -1683,6 +1683,91 @@ drop table t1;
set use_stat_tables= @save_use_stat_tables;
set @@histogram_size=@save_histogram_size;
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1)
+#
+set @@optimizer_use_condition_selectivity=2;
+set names utf8;
+CREATE DATABASE world;
+use world;
+CREATE TABLE Country (
+Code char(3) NOT NULL default '',
+Name char(52) NOT NULL default '',
+SurfaceArea float(10,2) NOT NULL default '0.00',
+Population int(11) NOT NULL default '0',
+Capital int(11) default NULL,
+PRIMARY KEY (Code),
+UNIQUE INDEX (Name)
+);
+CREATE TABLE City (
+ID int(11) NOT NULL auto_increment,
+Name char(35) NOT NULL default '',
+Country char(3) NOT NULL default '',
+Population int(11) NOT NULL default '0',
+PRIMARY KEY (ID),
+INDEX (Population),
+INDEX (Country)
+);
+CREATE TABLE CountryLanguage (
+Country char(3) NOT NULL default '',
+Language char(30) NOT NULL default '',
+Percentage float(3,1) NOT NULL default '0.0',
+PRIMARY KEY (Country, Language),
+INDEX (Percentage)
+);
+CREATE INDEX Name ON City(Name);
+CREATE INDEX CountryPopulation ON City(Country,Population);
+CREATE INDEX CountryName ON City(Country,Name);
+set @@optimizer_use_condition_selectivity=2;
+EXPLAIN
+SELECT * FROM City WHERE Country='FIN';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 7 Using index condition
+DROP DATABASE world;
+use test;
+CREATE TABLE t1 (
+a INT,
+b INT NOT NULL,
+c char(100),
+KEY (b, c),
+KEY (b, a, c)
+) ENGINE=MyISAM
+DEFAULT CHARSET = utf8;
+INSERT INTO t1 VALUES
+(1, 1, 1),
+(2, 2, 2),
+(3, 3, 3),
+(4, 4, 4),
+(5, 5, 5),
+(6, 6, 6),
+(7, 7, 7),
+(8, 8, 8),
+(9, 9, 9);
+INSERT INTO t1 SELECT a + 10, b, c FROM t1;
+INSERT INTO t1 SELECT a + 20, b, c FROM t1;
+INSERT INTO t1 SELECT a + 40, b, c FROM t1;
+INSERT INTO t1 SELECT a + 80, b, c FROM t1;
+INSERT INTO t1 SELECT a + 160, b, c FROM t1;
+INSERT INTO t1 SELECT a + 320, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80;
+EXPLAIN
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+a
+2071
+2061
+2051
+2041
+2031
+2021
+2011
+2001
+1991
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+DROP TABLE t1;
# End of 10.1 tests
set @@global.histogram_size=@save_histogram_size;
set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
diff --git a/mysql-test/suite/innodb/r/innodb_stats_persistent.result b/mysql-test/suite/innodb/r/innodb_stats_persistent.result
index f4de4b6b82e..41893bf47c1 100644
--- a/mysql-test/suite/innodb/r/innodb_stats_persistent.result
+++ b/mysql-test/suite/innodb/r/innodb_stats_persistent.result
@@ -29,7 +29,7 @@ connection default;
# DELETE must not affect statistics before COMMIT.
EXPLAIN SELECT * FROM t1 WHERE val=4;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref val val 4 const 16 Using index
+1 SIMPLE t1 ref val val 4 const 1 Using index
connection con1;
COUNT(*)
0
diff --git a/mysql-test/suite/innodb_fts/r/concurrent_insert.result b/mysql-test/suite/innodb_fts/r/concurrent_insert.result
index 5644075038a..9871d43119a 100644
--- a/mysql-test/suite/innodb_fts/r/concurrent_insert.result
+++ b/mysql-test/suite/innodb_fts/r/concurrent_insert.result
@@ -9,3 +9,26 @@ disconnect dml;
connection default;
SET DEBUG_SYNC = 'RESET';
DROP TABLE t1;
+#
+# MDEV-19529 InnoDB hang on DROP FULLTEXT INDEX
+#
+CREATE TABLE t1(f1 CHAR(100), FULLTEXT(f1))ENGINE=InnoDB;
+INSERT INTO t1 VALUES('test');
+CREATE TABLE t2 (f1 char(100), FULLTEXT idx1(f1))ENGINE=InnoDB;
+INSERT INTO t2 VALUES('mariadb');
+connection default;
+SET GLOBAL debug_dbug ='+d,fts_instrument_sync_request,ib_optimize_wq_hang';
+SET DEBUG_SYNC= 'fts_instrument_sync_request
+ SIGNAL drop_index_start WAIT_FOR sync_op';
+INSERT INTO t1 VALUES('Keyword');
+connect con1,localhost,root,,,;
+SET DEBUG_SYNC='now WAIT_FOR drop_index_start';
+SET DEBUG_SYNC= 'norebuild_fts_drop SIGNAL sync_op WAIT_FOR fts_drop_index';
+ALTER TABLE t2 drop index idx1;
+connection default;
+set DEBUG_SYNC= 'now SIGNAL fts_drop_index';
+connection con1;
+SET global DEBUG_DBUG=RESET;
+drop table t1, t2;
+connection default;
+set DEBUG_SYNC=RESET;
diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result b/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result
index 74ade61c940..99ead1a5825 100644
--- a/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result
+++ b/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result
@@ -734,15 +734,21 @@ count(*)
DROP TABLE t1;
"----------Test27---------"
CREATE TABLE t1 (id INT,char_column VARCHAR(60));
+CREATE TABLE t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, a TEXT)ENGINE=InnoDB;
+ALTER TABLE t2 DROP a;
SET @@autocommit=0;
CREATE FULLTEXT INDEX i ON t1 (char_column);
INSERT INTO t1 values (1,'aaa');
"restart server..."
-# Restart the server
---source include/restart_mysqld.inc
-DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb')
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`FTS_DOC_ID`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb');
SET @@autocommit=1;
-DROP TABLE t1;
+DROP TABLE t1, t2;
"----------Test28---------"
drop table if exists `fts_test`;
Warnings:
diff --git a/mysql-test/suite/innodb_fts/t/concurrent_insert.test b/mysql-test/suite/innodb_fts/t/concurrent_insert.test
index e5d61cd8b05..77097d44dc5 100644
--- a/mysql-test/suite/innodb_fts/t/concurrent_insert.test
+++ b/mysql-test/suite/innodb_fts/t/concurrent_insert.test
@@ -18,3 +18,34 @@ reap;
SET DEBUG_SYNC = 'RESET';
DROP TABLE t1;
+
+--echo #
+--echo # MDEV-19529 InnoDB hang on DROP FULLTEXT INDEX
+--echo #
+
+CREATE TABLE t1(f1 CHAR(100), FULLTEXT(f1))ENGINE=InnoDB;
+INSERT INTO t1 VALUES('test');
+CREATE TABLE t2 (f1 char(100), FULLTEXT idx1(f1))ENGINE=InnoDB;
+INSERT INTO t2 VALUES('mariadb');
+
+connection default;
+SET GLOBAL debug_dbug ='+d,fts_instrument_sync_request,ib_optimize_wq_hang';
+SET DEBUG_SYNC= 'fts_instrument_sync_request
+ SIGNAL drop_index_start WAIT_FOR sync_op';
+send INSERT INTO t1 VALUES('Keyword');
+
+connect(con1,localhost,root,,,);
+SET DEBUG_SYNC='now WAIT_FOR drop_index_start';
+SET DEBUG_SYNC= 'norebuild_fts_drop SIGNAL sync_op WAIT_FOR fts_drop_index';
+send ALTER TABLE t2 drop index idx1;
+
+connection default;
+reap;
+set DEBUG_SYNC= 'now SIGNAL fts_drop_index';
+
+connection con1;
+reap;
+SET global DEBUG_DBUG=RESET;
+drop table t1, t2;
+connection default;
+set DEBUG_SYNC=RESET;
diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test b/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test
index 8347d29d052..90bbe02174f 100644
--- a/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test
+++ b/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test
@@ -669,15 +669,18 @@ DROP TABLE t1;
--echo "----------Test27---------"
#27 Crash after server restart
CREATE TABLE t1 (id INT,char_column VARCHAR(60));
+CREATE TABLE t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, a TEXT)ENGINE=InnoDB;
+ALTER TABLE t2 DROP a;
SET @@autocommit=0;
CREATE FULLTEXT INDEX i ON t1 (char_column);
INSERT INTO t1 values (1,'aaa');
-echo "restart server..."
+echo "restart server...";
# Restart the server
--source include/restart_mysqld.inc
+SHOW CREATE TABLE t2;
DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb');
SET @@autocommit=1;
-DROP TABLE t1;
+DROP TABLE t1, t2;
--echo "----------Test28---------"
drop table if exists `fts_test`;
diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test
index b517b26bc1b..d3fafc2ae52 100644
--- a/mysql-test/t/selectivity.test
+++ b/mysql-test/t/selectivity.test
@@ -1130,6 +1130,87 @@ drop table t1;
set use_stat_tables= @save_use_stat_tables;
set @@histogram_size=@save_histogram_size;
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+--echo #
+--echo # MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1)
+--echo #
+
+set @@optimizer_use_condition_selectivity=2;
+
+set names utf8;
+
+CREATE DATABASE world;
+
+use world;
+
+--source include/world_schema.inc
+
+--disable_query_log
+--disable_result_log
+--disable_warnings
+--source include/world.inc
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+CREATE INDEX Name ON City(Name);
+CREATE INDEX CountryPopulation ON City(Country,Population);
+CREATE INDEX CountryName ON City(Country,Name);
+
+--disable_query_log
+--disable_result_log
+--disable_warnings
+ANALYZE TABLE City;
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+set @@optimizer_use_condition_selectivity=2;
+
+EXPLAIN
+SELECT * FROM City WHERE Country='FIN';
+
+DROP DATABASE world;
+
+use test;
+
+CREATE TABLE t1 (
+ a INT,
+ b INT NOT NULL,
+ c char(100),
+ KEY (b, c),
+ KEY (b, a, c)
+) ENGINE=MyISAM
+DEFAULT CHARSET = utf8;
+
+INSERT INTO t1 VALUES
+(1, 1, 1),
+(2, 2, 2),
+(3, 3, 3),
+(4, 4, 4),
+(5, 5, 5),
+(6, 6, 6),
+(7, 7, 7),
+(8, 8, 8),
+(9, 9, 9);
+
+INSERT INTO t1 SELECT a + 10, b, c FROM t1;
+INSERT INTO t1 SELECT a + 20, b, c FROM t1;
+INSERT INTO t1 SELECT a + 40, b, c FROM t1;
+INSERT INTO t1 SELECT a + 80, b, c FROM t1;
+INSERT INTO t1 SELECT a + 160, b, c FROM t1;
+INSERT INTO t1 SELECT a + 320, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80;
+
+EXPLAIN
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1;
+
--echo # End of 10.1 tests
#
View Lorry Controller Status