diff options
Diffstat (limited to 'mysql-test')
42 files changed, 879 insertions, 138 deletions
diff --git a/mysql-test/disabled.def b/mysql-test/disabled.def index e82ddcf92d1..b489139a59f 100644 --- a/mysql-test/disabled.def +++ b/mysql-test/disabled.def @@ -12,7 +12,6 @@ tablespace : disabled in MariaDB (no TABLESPACE table attribute) events_time_zone : Test is not predictable as it depends on precise timing. read_many_rows_innodb : Bug#11748886 2010-11-15 mattiasj report already exists -log_tables-big : Bug#11756699 2010-11-15 mattiasj report already exists mysql_embedded : Bug#12561297 2011-05-14 Anitha Dependent on PB2 changes - eventum#41836 #show_explain : Psergey: random timeout in range-checked-for-each record query. ssl_crl_clients_valid : broken upstream diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 42fd0ff7397..088dd3aae2c 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -2792,13 +2792,10 @@ sub mysql_server_start($) { if (! $opt_start_dirty) # If dirty, keep possibly grown system db { # Copy datadir from installed system db - for my $path ( "$opt_vardir", "$opt_vardir/..") { - my $install_db= "$path/install.db"; - copytree($install_db, $datadir) - if -d $install_db; - } - mtr_error("Failed to copy system db to '$datadir'") - unless -d $datadir; + my $path= ($opt_parallel == 1) ? "$opt_vardir" : "$opt_vardir/.."; + my $install_db= "$path/install.db"; + copytree($install_db, $datadir) if -d $install_db; + mtr_error("Failed to copy system db to '$datadir'") unless -d $datadir; } } else diff --git a/mysql-test/r/events_2.result b/mysql-test/r/events_2.result index ab5d47b5118..6d2ebda03d0 100644 --- a/mysql-test/r/events_2.result +++ b/mysql-test/r/events_2.result @@ -2,10 +2,10 @@ set sql_mode=""; drop database if exists events_test; create database events_test; use events_test; -create event e_26 on schedule at '2027-01-01 00:00:00' disable do set @a = 5; +create event e_26 on schedule at '2037-01-01 00:00:00' disable do set @a = 5; select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event; db name body definer convert_tz(execute_at, 'UTC', 'SYSTEM') on_completion -events_test e_26 set @a = 5 root@localhost 2027-01-01 00:00:00 DROP +events_test e_26 set @a = 5 root@localhost 2037-01-01 00:00:00 DROP drop event e_26; create event e_26 on schedule at NULL disable do set @a = 5; ERROR HY000: Incorrect AT value: 'NULL' diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result index 1af67cf6a4e..1e95e983253 100644 --- a/mysql-test/r/explain_json.result +++ b/mysql-test/r/explain_json.result @@ -825,7 +825,7 @@ EXPLAIN "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", - "attached_condition": "t2.b <> outer_t1.a and trigcond(<cache>(outer_t1.a) = t1.a or t1.a is null)" + "attached_condition": "t2.b <> outer_t1.a" } } } @@ -1573,7 +1573,7 @@ EXPLAIN "access_type": "ALL", "rows": 10, "filtered": 100, - "attached_condition": "t0.a < 5 and t1.b < t0.a" + "attached_condition": "t1.b < t0.a" } } } diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index b9ee2a9136f..31335dc2af6 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -1702,7 +1702,19 @@ SELECT ASTEXT(p) FROM v1; ASTEXT(p) POINT(1 1) DROP VIEW v1; -End of 5.5 tests +# +# Start of 10.0 tests +# +# +# MDEV-12495 Conditional jump depends on uninitialised value for: SELECT NULL UNION geom_expression +# +SELECT AsText(g) FROM (SELECT NULL AS g UNION SELECT Point(1,1)) AS t1; +AsText(g) +NULL +POINT(1 1) +# +# End 10.0 tests +# SHOW CREATE TABLE information_schema.geometry_columns; Table Create Table GEOMETRY_COLUMNS CREATE TEMPORARY TABLE `GEOMETRY_COLUMNS` ( diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result index 207317c1add..8a58a769ed9 100644 --- a/mysql-test/r/innodb_icp.result +++ b/mysql-test/r/innodb_icp.result @@ -432,7 +432,7 @@ WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using where -2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); pk i diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index ffeddc989ec..468aa3b2dc0 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1743,7 +1743,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using temporary; Using filesort 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.pk 1 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.pk 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.t2.pk' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`pk` AS `pk`,<expr_cache><`test`.`t2`.`pk`>((select `test`.`t3`.`pk` + if(`test`.`t4`.`pk` is null,0,`test`.`t4`.`pk`) from `test`.`t3` left join `test`.`t4` on(`test`.`t4`.`pk` = `test`.`t3`.`pk`) where `test`.`t3`.`pk` = `test`.`t2`.`pk` + 1000 limit 1)) AS `t` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`pk` = `test`.`t1`.`pk` + 1000 and `test`.`t1`.`pk` > 1000 group by `test`.`t2`.`pk` diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 4ffca891061..bcb8fb21947 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1754,7 +1754,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using temporary; Using filesort 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.pk 1 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.pk 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.t2.pk' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`pk` AS `pk`,<expr_cache><`test`.`t2`.`pk`>((select `test`.`t3`.`pk` + if(`test`.`t4`.`pk` is null,0,`test`.`t4`.`pk`) from `test`.`t3` left join `test`.`t4` on(`test`.`t4`.`pk` = `test`.`t3`.`pk`) where `test`.`t3`.`pk` = `test`.`t2`.`pk` + 1000 limit 1)) AS `t` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`pk` = `test`.`t1`.`pk` + 1000 and `test`.`t1`.`pk` > 1000 group by `test`.`t2`.`pk` diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index b91dd1b87ae..4f59e9cdbe3 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -340,7 +340,7 @@ FIELDS ESCAPED BY '\\' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (c0, c2); -ERROR HY000: Invalid column reference (v2.c0) in LOAD DATA +ERROR HY000: Column 'c0' is not updatable LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v3 FIELDS ESCAPED BY '\\' diff --git a/mysql-test/r/loadxml.result b/mysql-test/r/loadxml.result index 711fea9e854..34719a27afc 100644 --- a/mysql-test/r/loadxml.result +++ b/mysql-test/r/loadxml.result @@ -129,8 +129,8 @@ DROP TABLE t1; CREATE TABLE t1 (c1 TEXT); CREATE VIEW v1 AS SELECT CONCAT(c1,'') AS c1, NULL AS c2 FROM t1; LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c1); -ERROR HY000: Invalid column reference (v1.c1) in LOAD DATA +ERROR HY000: Column 'c1' is not updatable LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c2); -ERROR HY000: Invalid column reference (v1.c2) in LOAD DATA +ERROR HY000: Column 'c2' is not updatable DROP VIEW v1; DROP TABLE t1; diff --git a/mysql-test/r/log_tables-big.result b/mysql-test/r/log_tables-big.result index 9b81127c825..1e189a7726f 100644 --- a/mysql-test/r/log_tables-big.result +++ b/mysql-test/r/log_tables-big.result @@ -1,29 +1,31 @@ +set @@global.log_output = 'TABLE'; set session long_query_time=10; select get_lock('bug27638', 1); get_lock('bug27638', 1) 1 set session long_query_time=1; -truncate table mysql.slow_log; select get_lock('bug27638', 2); get_lock('bug27638', 2) 0 -select if (query_time between '00:00:01' and '00:00:10', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; +select if (query_time >= '00:00:01', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +where sql_text = 'select get_lock(\'bug27638\', 2)'; qt sql_text OK select get_lock('bug27638', 2) -truncate table mysql.slow_log; select get_lock('bug27638', 60); get_lock('bug27638', 60) 0 -select if (query_time between '00:00:59' and '00:01:10', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; +select if (query_time >= '00:00:59', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +where sql_text = 'select get_lock(\'bug27638\', 60)'; qt sql_text OK select get_lock('bug27638', 60) -truncate table mysql.slow_log; select get_lock('bug27638', 101); get_lock('bug27638', 101) 0 -select if (query_time between '00:01:40' and '00:01:50', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; +select if (query_time >= '00:01:40', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log +where sql_text = 'select get_lock(\'bug27638\', 101)'; qt sql_text OK select get_lock('bug27638', 101) select release_lock('bug27638'); release_lock('bug27638') 1 +set @@global.log_output=default; diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result index 458d0195a19..6a70521cd72 100644 --- a/mysql-test/r/myisam_icp.result +++ b/mysql-test/r/myisam_icp.result @@ -430,7 +430,7 @@ WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using index condition -2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); pk i @@ -804,7 +804,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL PRIMARY,c NULL NULL NULL 64 Using where 1 PRIMARY t2 ref g g 5 test.t.c 19 Using where 2 DEPENDENT SUBQUERY t1 index PRIMARY PRIMARY 4 NULL 64 Using where; Using index -2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index condition; Using where +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where SELECT COUNT(*) FROM t1 AS t, t2 WHERE c = g AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b) diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 66b501e8278..5a8c1e7a8ae 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -5330,6 +5330,69 @@ DROP TABLE t1; DROP TABLE t2; DROP DATABASE db_20772273; USE test; +# +# Bug #25717383: MYSQLDUMP MAY EXECUTE ANY ARBITRARY QUERY +# +CREATE DATABASE bug25717383; +use bug25717383; +CREATE TABLE `tab +one` (a int); +CREATE VIEW `view +one` as SELECT * FROM `tab +one`; +CREATE PROCEDURE `proc +one`() SELECT * from `tab +one`; +CREATE TEMPORARY TABLE `temp +one` (id INT); +CREATE TRIGGER `trig +one` BEFORE INSERT ON `tab +one` FOR EACH ROW SET NEW.a = 1; +CREATE EVENT `event +one` ON SCHEDULE AT '2030-01-01 00:00:00' DO SET @a=5; +SHOW TABLES FROM bug25717383; +Tables_in_bug25717383 +tab +one +view +one +SHOW TRIGGERS FROM bug25717383; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +trig +one INSERT tab +one SET NEW.a = 1 BEFORE # root@localhost utf8 utf8_general_ci latin1_swedish_ci +SHOW EVENTS FROM bug25717383; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +bug25717383 event +one root@localhost SYSTEM ONE TIME # NULL NULL NULL NULL ENABLED 1 utf8 utf8_general_ci latin1_swedish_ci +SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA='bug25717383' AND ROUTINE_TYPE= 'PROCEDURE' + ORDER BY ROUTINE_NAME; +ROUTINE_NAME +proc +one +SHOW TABLES FROM bug25717383; +Tables_in_bug25717383 +tab +one +view +one +SHOW TRIGGERS FROM bug25717383; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +trig +one INSERT tab +one SET NEW.a = 1 BEFORE # root@localhost utf8 utf8_general_ci latin1_swedish_ci +SHOW EVENTS FROM bug25717383; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +bug25717383 event +one root@localhost SYSTEM ONE TIME # NULL NULL NULL NULL ENABLED 1 utf8 utf8_general_ci latin1_swedish_ci +SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA='bug25717383' AND ROUTINE_TYPE= 'PROCEDURE' + ORDER BY ROUTINE_NAME; +ROUTINE_NAME +proc +one +DROP DATABASE bug25717383; Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS] diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result index f8f0b8e379d..237f04d6d28 100644 --- a/mysql-test/r/mysqltest.result +++ b/mysql-test/r/mysqltest.result @@ -684,7 +684,9 @@ drop table t1; y txt b is b and more is more -txt +txt2 +b is b or more is more +txt3 a is a and less is more sflfdt 'ABCDfF bbddff h' bs txt; txt diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 673b53b9c04..9ed067c0bdc 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -267,7 +267,7 @@ from t2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key -2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where `test`.`t1`.`c` = `test`.`t2`.`oref` and trigcond(trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null)) and trigcond(trigcond(<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b` or `test`.`t1`.`b` is null)) having trigcond(`test`.`t1`.`a` is null) and trigcond(`test`.`t1`.`b` is null)))) AS `Z` from `test`.`t2` @@ -1242,19 +1242,19 @@ explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where -2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where -2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) explain select straight_join * from t2 X, t2 Y where X.a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where 1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where -2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) create table t0 (a int, b int); insert into t0 values(1,1); explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 06a5e2c5aa4..2a1fc0df957 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -277,7 +277,7 @@ from t2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key -2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where `test`.`t1`.`c` = `test`.`t2`.`oref` and trigcond(trigcond(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null)) and trigcond(trigcond(<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b` or `test`.`t1`.`b` is null)) having trigcond(`test`.`t1`.`a` is null) and trigcond(`test`.`t1`.`b` is null)))) AS `Z` from `test`.`t2` @@ -1252,19 +1252,19 @@ explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where -2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where -2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) explain select straight_join * from t2 X, t2 Y where X.a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where 1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where -2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) create table t0 (a int, b int); insert into t0 values(1,1); explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 3d0dfd189a8..699a1b27b78 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -19,7 +19,7 @@ SELECT 1 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY count(*); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL a 5 NULL 2 Using where; Using index +1 PRIMARY t1 index NULL a 5 NULL 2 Using index 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 3 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found # should not crash the next statement @@ -1409,7 +1409,7 @@ GROUP BY f9; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 FROM t2 JOIN t1 ON t1.f3 @@ -1425,7 +1425,7 @@ ORDER BY f9; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 FROM t2 JOIN t1 ON t1.f3 @@ -2435,9 +2435,11 @@ WHERE EXISTS ( )"; EXECUTE stmt; i +4 6 EXECUTE stmt; i +4 6 drop table t1, t2, t3; # diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result index 07b5557506b..d5e61d4beca 100644 --- a/mysql-test/r/subselect_exists2in.result +++ b/mysql-test/r/subselect_exists2in.result @@ -884,5 +884,55 @@ a deallocate prepare stmt; drop view v1; drop table t1,t2; +# +#MDEV-10053: EXIST to IN transformation turned down +# +CREATE TABLE t1 ( +pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) +ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); +set @optimizer_switch_save=@@optimizer_switch; +set optimizer_switch='exists_to_in=off'; +explain extended SELECT STRAIGHT_JOIN sq1.f2 +FROM ( SELECT * FROM t1 ) AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +3 DEPENDENT SUBQUERY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 +3 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +4 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1276 Field or reference 'sq1.pk' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'sq1.f1' of SELECT #3 was resolved in SELECT #1 +Note 1003 select straight_join `test`.`t1`.`f2` AS `f2` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`,`test`.`t1`.`pk`>(exists(select 1 from `test`.`t1` `sq2` semi join (`test`.`t1`) where `test`.`sq2`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`pk` = `test`.`t1`.`f1`)) +SELECT STRAIGHT_JOIN sq1.f2 +FROM ( SELECT * FROM t1 ) AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set optimizer_switch='exists_to_in=on'; +explain extended SELECT STRAIGHT_JOIN sq1.f2 +FROM ( SELECT * FROM t1 ) AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +3 DEPENDENT SUBQUERY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 +3 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +4 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1276 Field or reference 'sq1.pk' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'sq1.f1' of SELECT #3 was resolved in SELECT #1 +Note 1003 select straight_join `test`.`t1`.`f2` AS `f2` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`f1`,<exists>(select `test`.`sq2`.`f1` from `test`.`t1` `sq2` semi join (`test`.`t1`) where `test`.`t1`.`pk` = `test`.`t1`.`f1` and <cache>(`test`.`t1`.`f1`) = `test`.`sq2`.`f1`))) +SELECT STRAIGHT_JOIN sq1.f2 +FROM ( SELECT * FROM t1 ) AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set optimizer_switch= @optimizer_switch_save; +DROP TABLE t1; # End of 10.0 tests set optimizer_switch=default; diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index 11221c797ff..01257c33361 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -312,7 +312,7 @@ EXPLAIN SELECT 1 FROM t1 WHERE NOT EXISTS id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where 2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY,d PRIMARY 1 func 1 Using where -3 DEPENDENT SUBQUERY t2 index NULL d 2 NULL 1 Using where; Using index +3 DEPENDENT SUBQUERY t2 index NULL d 2 NULL 1 Using index DROP TABLE t2; CREATE TABLE t2 (b INT, c INT, UNIQUE KEY (b), UNIQUE KEY (b, c )) ENGINE=INNODB; INSERT INTO t2 VALUES (1, 1); @@ -509,6 +509,20 @@ set join_cache_level = default; drop view v1; drop table t1,t2; # +# MDEV-10693: cost-based choice between materialization and in-to-exists +# for a subquery from the expression used in ref access +# +CREATE TABLE t1 (i1 INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (i2 INT) ENGINE=InnoDB; +CREATE TABLE t3 (i3 INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t3 VALUES (3); +SELECT * FROM t1 +WHERE NULL IN ( SELECT i2 FROM t2 +WHERE i1 IN ( i2 IN ( SELECT i3 FROM t3 ) ) AND i2 = 2 ); +i1 +DROP TABLE t1,t2,t3; +# # MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding # create table t1(a int) engine=innodb; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 59f9d3c1876..ede18083b85 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2192,6 +2192,117 @@ execute stmt; a 0 drop table t1; +# +# MDEV-12429: IN subquery used in WHERE of EXISTS subquery +# +CREATE TABLE t1 ( +pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=off'; +EXPLAIN +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY sq1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +2 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +# this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1; +# +# MDEV-12145: IN subquery used in WHERE of EXISTS subquery +# +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(7),(1); +CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=off'; +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +f1 +6 +EXPLAIN EXTENDED +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 DEPENDENT SUBQUERY t2 index i2 i2 5 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t3 ref i3 i3 5 test.t2.i2 2 100.00 Using index +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 100.00 +Warnings: +Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`f1` = `test`.`t3`.`f3`)) +# this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +f1 +6 +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1,t2,t3; +# +# MDEV-9686: IN subquery used in WHERE of a subquery from select list +# +CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT); +INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3); +CREATE TABLE t2 (f2 INT); +INSERT INTO t2 VALUES (1),(2),(3),(4),(5); +# t1.pk is always IN ( SELECT f2 FROM t2 ), +# so the IN condition should be true for every row, +# and thus COUNT(*) should always return 5 +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +pk f1 sq +1 4 5 +2 3 5 +3 3 5 +4 6 5 +5 3 5 +EXPLAIN EXTENDED +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 +Warnings: +Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((select count(0) from `test`.`t2` semi join (`test`.`t2`) where `test`.`t1`.`pk` = `test`.`t2`.`f2`)) AS `sq` from `test`.`t1` +# this checks the result set above +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +pk f1 sq +1 4 5 +2 3 5 +3 3 5 +4 6 5 +5 3 5 +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1,t2; # End of 5.5 tests # # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index c7b0f0d46c6..08a5d019bab 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -400,3 +400,45 @@ id select_type table type possible_keys key key_len ref rows Extra select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); c1 c2 drop table t1, t2; +# +# MDEV-12673: cost-based choice between materialization and in-to-exists +# +CREATE TABLE t1 ( +pk1 int, a1 varchar(3), b1 varchar(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo'); +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'abc'),(2,'xyz'),(3,'foo'); +SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); +'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ) +0 +SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); +'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ) +1 +EXPLAIN +SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 const PRIMARY,a2 PRIMARY 4 const 1 +2 SUBQUERY t1 ref a1,b1 b1 6 const 1 Using where +DROP TABLE t1,t2; +CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (i2 int, c2 varchar(3), KEY(i2,c2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'abc'),(2,'foo'); +CREATE TABLE t3 (pk3 int PRIMARY KEY, c3 varchar(3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (1,'foo'),(2,'bar'); +SELECT * FROM t1 WHERE i1 NOT IN ( +SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 +); +i1 +1 +EXPLAIN +SELECT * FROM t1 WHERE i1 NOT IN ( +SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t3 const PRIMARY PRIMARY 4 const 1 +2 DEPENDENT SUBQUERY t2 index NULL i2 11 NULL 2 Using where; Using index +DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 91d05a31dfa..9ee5d8d2806 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -178,7 +178,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) where t1.a < 5; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 09d88c9996f..d20c6251f85 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -191,7 +191,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) 2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) 2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) -2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) +2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) where t1.a < 5; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index a3f3cc4213c..72f875cf8a7 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2232,6 +2232,117 @@ execute stmt; a 0 drop table t1; +# +# MDEV-12429: IN subquery used in WHERE of EXISTS subquery +# +CREATE TABLE t1 ( +pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=off'; +EXPLAIN +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY sq1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +2 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +# this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1; +# +# MDEV-12145: IN subquery used in WHERE of EXISTS subquery +# +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(7),(1); +CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=off'; +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +f1 +6 +EXPLAIN EXTENDED +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 DEPENDENT SUBQUERY t2 index i2 i2 5 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t3 ref i3 i3 5 test.t2.i2 2 100.00 Using index +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 100.00 +Warnings: +Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`f1` = `test`.`t3`.`f3`)) +# this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +f1 +6 +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1,t2,t3; +# +# MDEV-9686: IN subquery used in WHERE of a subquery from select list +# +CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT); +INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3); +CREATE TABLE t2 (f2 INT); +INSERT INTO t2 VALUES (1),(2),(3),(4),(5); +# t1.pk is always IN ( SELECT f2 FROM t2 ), +# so the IN condition should be true for every row, +# and thus COUNT(*) should always return 5 +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +pk f1 sq +1 4 5 +2 3 5 +3 3 5 +4 6 5 +5 3 5 +EXPLAIN EXTENDED +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 +Warnings: +Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((select count(0) from `test`.`t2` semi join (`test`.`t2`) where `test`.`t1`.`pk` = `test`.`t2`.`f2`)) AS `sq` from `test`.`t1` +# this checks the result set above +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +pk f1 sq +1 4 5 +2 3 5 +3 3 5 +4 6 5 +5 3 5 +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1,t2; # End of 5.5 tests # # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 2385e3887c4..1764ea6a7dd 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -226,3 +226,14 @@ t2 CREATE TABLE `t2` ( PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop tables t1, t2; +# +# Test for bug #25514146 DB_NAME IS IGNORED WHEN CREATING TABLE +# WITH DATA DIRECTORY +# +# Make sure we have no current database +CREATE DATABASE x; +USE x; +DROP DATABASE x; +CREATE TABLE test.t1(id INT(11)) ENGINE MYISAM +DATA DIRECTORY "MYSQLTEST_VARDIR/tmp"; +DROP TABLE test.t1; diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index 0ebdd726b4e..d22e705e6de 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -432,7 +432,7 @@ WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using index condition -2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); pk i @@ -806,7 +806,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL PRIMARY,c NULL NULL NULL 64 Using where 1 PRIMARY t2 ref g g 5 test.t.c 19 Using where 2 DEPENDENT SUBQUERY t1 index PRIMARY PRIMARY 4 NULL 64 Using where; Using index -2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index condition; Using where +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where SELECT COUNT(*) FROM t1 AS t, t2 WHERE c = g AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b) diff --git a/mysql-test/suite/sys_vars/r/sysvars_wsrep.result b/mysql-test/suite/sys_vars/r/sysvars_wsrep.result index 802ee3b1c6d..68ab8bd27a0 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_wsrep.result +++ b/mysql-test/suite/sys_vars/r/sysvars_wsrep.result @@ -367,7 +367,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME WSREP_PATCH_VERSION SESSION_VALUE NULL -GLOBAL_VALUE wsrep_25.19 +GLOBAL_VALUE wsrep_MAJVER.MINVER GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE NULL VARIABLE_SCOPE GLOBAL diff --git a/mysql-test/suite/sys_vars/t/sysvars_wsrep.test b/mysql-test/suite/sys_vars/t/sysvars_wsrep.test index 700b129fd62..c127115efe1 100644 --- a/mysql-test/suite/sys_vars/t/sysvars_wsrep.test +++ b/mysql-test/suite/sys_vars/t/sysvars_wsrep.test @@ -5,7 +5,7 @@ --replace_result $datadir DATADIR --let $hostname_regex=/^$hostname\$/HOSTNAME/ ---replace_regex $hostname_regex +--replace_regex $hostname_regex /wsrep_[0-9]{2}\.[0-9]{1,2}/wsrep_MAJVER.MINVER/ --vertical_results select * from information_schema.system_variables where variable_name like 'wsrep%' diff --git a/mysql-test/t/events_2.test b/mysql-test/t/events_2.test index 20f17966b29..12ce9210b0a 100644 --- a/mysql-test/t/events_2.test +++ b/mysql-test/t/events_2.test @@ -15,7 +15,7 @@ use events_test; # mysql.event intact checking end # -create event e_26 on schedule at '2027-01-01 00:00:00' disable do set @a = 5; +create event e_26 on schedule at '2037-01-01 00:00:00' disable do set @a = 5; select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event; drop event e_26; --error ER_WRONG_VALUE diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 3bb0044c7fb..0c8bbbfc819 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1453,7 +1453,20 @@ SHOW CREATE VIEW v1; SELECT ASTEXT(p) FROM v1; DROP VIEW v1; ---echo End of 5.5 tests +# --echo End of 5.5 tests + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-12495 Conditional jump depends on uninitialised value for: SELECT NULL UNION geom_expression +--echo # +SELECT AsText(g) FROM (SELECT NULL AS g UNION SELECT Point(1,1)) AS t1; + +--echo # +--echo # End 10.0 tests +--echo # SHOW CREATE TABLE information_schema.geometry_columns; SHOW CREATE TABLE information_schema.spatial_ref_sys; diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index 88c4a363a57..4e355e5c3a7 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -294,7 +294,7 @@ SELECT * FROM v2; DELETE FROM t1; --echo ---error ER_LOAD_DATA_INVALID_COLUMN +--error ER_NONUPDATEABLE_COLUMN LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v2 FIELDS ESCAPED BY '\\' TERMINATED BY ',' diff --git a/mysql-test/t/loadxml.test b/mysql-test/t/loadxml.test index b26a2eacc00..0bd97a81649 100644 --- a/mysql-test/t/loadxml.test +++ b/mysql-test/t/loadxml.test @@ -137,9 +137,9 @@ DROP TABLE t1; CREATE TABLE t1 (c1 TEXT); CREATE VIEW v1 AS SELECT CONCAT(c1,'') AS c1, NULL AS c2 FROM t1; ---error ER_LOAD_DATA_INVALID_COLUMN +--error ER_NONUPDATEABLE_COLUMN LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c1); ---error ER_LOAD_DATA_INVALID_COLUMN, +--error ER_NONUPDATEABLE_COLUMN LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c2); DROP VIEW v1; DROP TABLE t1; diff --git a/mysql-test/t/log_tables-big.test b/mysql-test/t/log_tables-big.test index 8c956fa6f55..8936a163d73 100644 --- a/mysql-test/t/log_tables-big.test +++ b/mysql-test/t/log_tables-big.test @@ -7,6 +7,8 @@ # check that CSV engine was compiled in --source include/have_csv.inc +set @@global.log_output = 'TABLE'; + connect (con1,localhost,root,,); connect (con2,localhost,root,,); @@ -18,18 +20,20 @@ set session long_query_time=10; select get_lock('bug27638', 1); connection con2; set session long_query_time=1; -truncate table mysql.slow_log; select get_lock('bug27638', 2); -select if (query_time between '00:00:01' and '00:00:10', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; -truncate table mysql.slow_log; +select if (query_time >= '00:00:01', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log + where sql_text = 'select get_lock(\'bug27638\', 2)'; select get_lock('bug27638', 60); -select if (query_time between '00:00:59' and '00:01:10', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; -truncate table mysql.slow_log; +select if (query_time >= '00:00:59', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log + where sql_text = 'select get_lock(\'bug27638\', 60)'; select get_lock('bug27638', 101); -select if (query_time between '00:01:40' and '00:01:50', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log; +select if (query_time >= '00:01:40', 'OK', 'WRONG') as qt, sql_text from mysql.slow_log + where sql_text = 'select get_lock(\'bug27638\', 101)'; connection con1; select release_lock('bug27638'); connection default; disconnect con1; disconnect con2; + +set @@global.log_output=default; diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 6d4e0e3f2b9..9f74f4ad9c0 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -2490,6 +2490,58 @@ DROP TABLE t2; DROP DATABASE db_20772273; USE test; +--echo # +--echo # Bug #25717383: MYSQLDUMP MAY EXECUTE ANY ARBITRARY QUERY +--echo # + + +CREATE DATABASE bug25717383; +use bug25717383; + +CREATE TABLE `tab +one` (a int); +CREATE VIEW `view +one` as SELECT * FROM `tab +one`; + +CREATE PROCEDURE `proc +one`() SELECT * from `tab +one`; + +CREATE TEMPORARY TABLE `temp +one` (id INT); + +CREATE TRIGGER `trig +one` BEFORE INSERT ON `tab +one` FOR EACH ROW SET NEW.a = 1; + +CREATE EVENT `event +one` ON SCHEDULE AT '2030-01-01 00:00:00' DO SET @a=5; + +SHOW TABLES FROM bug25717383; +--replace_column 6 # +SHOW TRIGGERS FROM bug25717383; +--replace_column 6 # +SHOW EVENTS FROM bug25717383; + +SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES + WHERE ROUTINE_SCHEMA='bug25717383' AND ROUTINE_TYPE= 'PROCEDURE' + ORDER BY ROUTINE_NAME; + +--exec $MYSQL_DUMP --triggers --events --routines --add-drop-database --databases bug25717383 > $MYSQLTEST_VARDIR/tmp/bug25717383.sql + +SHOW TABLES FROM bug25717383; +--replace_column 6 # +SHOW TRIGGERS FROM bug25717383; +--replace_column 6 # +SHOW EVENTS FROM bug25717383; + +SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES + WHERE ROUTINE_SCHEMA='bug25717383' AND ROUTINE_TYPE= 'PROCEDURE' + ORDER BY ROUTINE_NAME; + +DROP DATABASE bug25717383; + # # MDEV-6091 mysqldump goes in a loop and segfaults if --dump-slave is specified and it cannot connect to the server # diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index 4cc480b473e..2f1296560d0 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -2081,9 +2081,11 @@ drop table t1; --let $patt= /a /b / /less/more/ --replace_regex $patt select "a is a and less is more" as txt; +--replace_regex $patt /and /or / +select "a is a and less is more" as txt2; --let $patt= --replace_regex $patt -select "a is a and less is more" as txt; +select "a is a and less is more" as txt3; --enable_query_log # diff --git a/mysql-test/t/subselect_exists2in.test b/mysql-test/t/subselect_exists2in.test index 9450ef71494..a4fdbe5c50b 100644 --- a/mysql-test/t/subselect_exists2in.test +++ b/mysql-test/t/subselect_exists2in.test @@ -758,6 +758,34 @@ deallocate prepare stmt; drop view v1; drop table t1,t2; +--echo # +--echo #MDEV-10053: EXIST to IN transformation turned down +--echo # + +CREATE TABLE t1 ( + pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) +ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); + +let $q= +SELECT STRAIGHT_JOIN sq1.f2 + FROM ( SELECT * FROM t1 ) AS sq1 + WHERE EXISTS ( SELECT * FROM t1 AS sq2 + WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); + +set @optimizer_switch_save=@@optimizer_switch; + +set optimizer_switch='exists_to_in=off'; +eval explain extended $q; +eval $q; +set optimizer_switch='exists_to_in=on'; +eval explain extended $q; +eval $q; + +set optimizer_switch= @optimizer_switch_save; + +DROP TABLE t1; + --echo # End of 10.0 tests #restore defaults diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index b26c5036f3f..2451bc60fee 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -503,6 +503,27 @@ drop view v1; drop table t1,t2; --echo # +--echo # MDEV-10693: cost-based choice between materialization and in-to-exists +--echo # for a subquery from the expression used in ref access +--echo # + +--source include/have_innodb.inc + +CREATE TABLE t1 (i1 INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (i2 INT) ENGINE=InnoDB; + +CREATE TABLE t3 (i3 INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t3 VALUES (3); + +SELECT * FROM t1 + WHERE NULL IN ( SELECT i2 FROM t2 + WHERE i1 IN ( i2 IN ( SELECT i3 FROM t3 ) ) AND i2 = 2 ); + +DROP TABLE t1,t2,t3; + +--echo # --echo # MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding --echo # create table t1(a int) engine=innodb; diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test index 8205e94b203..316ac707bef 100644 --- a/mysql-test/t/subselect_mat_cost_bugs.test +++ b/mysql-test/t/subselect_mat_cost_bugs.test @@ -424,3 +424,42 @@ explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); drop table t1, t2; + +--echo # +--echo # MDEV-12673: cost-based choice between materialization and in-to-exists +--echo # + +CREATE TABLE t1 ( + pk1 int, a1 varchar(3), b1 varchar(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo'); + +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'abc'),(2,'xyz'),(3,'foo'); + +SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); +SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); +EXPLAIN +SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); + +DROP TABLE t1,t2; + +CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 (i2 int, c2 varchar(3), KEY(i2,c2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'abc'),(2,'foo'); + +CREATE TABLE t3 (pk3 int PRIMARY KEY, c3 varchar(3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (1,'foo'),(2,'bar'); + +SELECT * FROM t1 WHERE i1 NOT IN ( + SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 +); + +EXPLAIN +SELECT * FROM t1 WHERE i1 NOT IN ( + SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 +); + +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index e20291cfa67..b3c603728ee 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1857,6 +1857,99 @@ execute stmt; drop table t1; +--echo # +--echo # MDEV-12429: IN subquery used in WHERE of EXISTS subquery +--echo # + +CREATE TABLE t1 ( + pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); + +SELECT sq1.f2 FROM t1 AS sq1 + WHERE EXISTS ( SELECT * FROM t1 AS sq2 + WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); + +set @save_optimizer_switch= @@optimizer_switch; + +set optimizer_switch='exists_to_in=off'; +EXPLAIN +SELECT sq1.f2 FROM t1 AS sq1 + WHERE EXISTS ( SELECT * FROM t1 AS sq2 + WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); + +--echo # this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT sq1.f2 FROM t1 AS sq1 + WHERE EXISTS ( SELECT * FROM t1 AS sq2 + WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); + +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1; + +--echo # +--echo # MDEV-12145: IN subquery used in WHERE of EXISTS subquery +--echo # + +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); + +CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(7),(1); + +CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); + +set @save_optimizer_switch= @@optimizer_switch; + +set optimizer_switch='exists_to_in=off'; +SELECT * FROM t1 + WHERE EXISTS ( SELECT * FROM t2, t3 + WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +EXPLAIN EXTENDED +SELECT * FROM t1 + WHERE EXISTS ( SELECT * FROM t2, t3 + WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); + +--echo # this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT * FROM t1 + WHERE EXISTS ( SELECT * FROM t2, t3 + WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); + +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-9686: IN subquery used in WHERE of a subquery from select list +--echo # + +CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT); +INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3); + +CREATE TABLE t2 (f2 INT); +INSERT INTO t2 VALUES (1),(2),(3),(4),(5); + +--echo # t1.pk is always IN ( SELECT f2 FROM t2 ), +--echo # so the IN condition should be true for every row, +--echo # and thus COUNT(*) should always return 5 + +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 + WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +EXPLAIN EXTENDED +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 + WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; + +--echo # this checks the result set above +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 + WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1,t2; + --echo # End of 5.5 tests --echo # --echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index 065be302237..e17ea07ca3c 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -300,3 +300,19 @@ select create_options from information_schema.tables where table_name='t1'; create table t2 like t1; show create table t2; drop tables t1, t2; + +--echo # +--echo # Test for bug #25514146 DB_NAME IS IGNORED WHEN CREATING TABLE +--echo # WITH DATA DIRECTORY +--echo # + +--echo # Make sure we have no current database +CREATE DATABASE x; +USE x; +DROP DATABASE x; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE test.t1(id INT(11)) ENGINE MYISAM +DATA DIRECTORY "$MYSQLTEST_VARDIR/tmp"; + +DROP TABLE test.t1; diff --git a/mysql-test/unstable-tests b/mysql-test/unstable-tests index 4812456839d..091aeff6b7c 100644 --- a/mysql-test/unstable-tests +++ b/mysql-test/unstable-tests @@ -23,88 +23,79 @@ # ############################################################################## -main.alter_table : Modified in 10.1.22 +main.alter_table : MDEV-12625 - Valgrind warnings, also modified in 10.1.22 main.alter_table_trans : MDEV-11805 - timeout main.analyze_format_json : MDEV-11866 - Wrong result; also uses analyze-format.inc modified in 10.1.22 main.analyze_stmt_orderby : MDEV-11866 - Wrong result; also uses analyze-format.inc modified in 10.1.22 -main.analyze_stmt_slow_query_log : MDEV-12237 - Wrong result; also modified in 10.1.21 -main.cast : Modified in 10.1.21 -main.create : Modified in 10.1.21 +main.analyze_stmt_slow_query_log : MDEV-12237 - Wrong result main.create_delayed : MDEV-10605 - failed with timeout -main.ctype_ucs : Modified in 10.1.21 -main.ctype_ucs2_def : Modified in 10.1.21 -main.ctype_ucs2_query_cache : Modified in 10.1.21 -main.ctype_utf16 : Modified in 10.1.21 -main.ctype_utf16_def : Modified in 10.1.21 +main.ctype_upgrade : Modified in 10.1.23 main.ctype_utf16le : MDEV-10675: timeout or extra warnings -main.ctype_utf32 : Modified in 10.1.21 main.derived : Modified in 10.1.22 main.drop_bad_db_type : Modified in 10.1.22 +main.drop-no_root : MDEV-12633 - Valgrind warnings +main.events_2 : Modified in 10.1.23 main.events_restart : MDEV-12236 - Server shutdown problem -main.events_slowlog : Added in 10.1.21 main.explain_json : Uses analyze-format.inc modified in 10.1.22 main.explain_json_format_partitions : Uses analyze-format.inc modified in 10.1.22 -main.func_time : Modified in 10.1.21 -main.gis : Modified in 10.1.22 +main.gis : Modified in 10.1.23 main.grant : Modified in 10.1.22 main.host_cache_size_functionality : MDEV-10606 - sporadic failure on shutdown main.index_intersect_innodb : MDEV-10643 - failed with timeout -main.index_merge_innodb : MDEV-7142 - Wrong execution plan, also modified in 10.1.21 -main.information_schema_part : Modified in 10.1.21 +main.index_merge_innodb : MDEV-7142 - Wrong execution plan main.innodb_mysql_lock : MDEV-7861 - sporadic lock detection failure -main.join_cache : Modified in 10.1.21 -main.join_nested : MOdified in 10.1.22 +main.join_nested : Modified in 10.1.22 main.kill_processlist-6619 : MDEV-10793 - wrong result in processlist -main.loaddata : Modified in 10.1.21 -main.log_slow : Modified in 10.1.21 +main.loaddata : Modified in 10.1.23 +main.log_tables-big : Modified in 10.1.23 main.mdev-504 : MDEV-10607 - sporadic "can't connect" main.mdev375 : MDEV-10607 - sporadic "can't connect" main.merge : MDEV-10607 - sporadic "can't connect" +main.mysqlcheck : MDEV-12633 - Valgrind warnings main.mysqldump : Modified in 10.1.22 +main.mysqlhotcopy_myisam : Uses mysqlhotcopy.inc modified in 10.1.23 main.mysqlslap : MDEV-11801 - timeout main.mysqltest : MDEV-9269 - fails on Alpha +main.mysql_client_test : MDEV-12633 - Valgrind warnings +main.mysql_client_test_comp : MDEV-12633 - Valgrind warnings +main.mysql_client_test_nonblock : MDEV-12633 - Valgrind warnings main.mysql_upgrade_noengine : MDEV-12233 - Wrong result; added in 10.1.22 -main.order_by : Modified in 10.1.21 +main.mysqldump : Modified in 10.1.23 +main.mysqltest : Modified in 10.1.23 main.order_by_optimizer_innodb : MDEV-10683 - wrong execution plan main.partition_column : Modified in 10.1.22 main.partition_innodb : Modified in 10.1.22 main.partition_myisam : Modified in 10.1.22 -main.pool_of_threads : Modified in 10.1.21 main.ps : MDEV-11017 - sporadic wrong Prepared_stmt_count; also modified in 10.1.22 main.range_vs_index_merge : Modified in 10.1.22 +main.range_vs_index_merge_innodb : MDEV-12637 - Timeout main.repair_symlink-5543 : MDEV-12215 - Wrong error codes; also modified in 10.1.22 +main.show_check : MDEV-12633 - Valgrind warnings main.show_explain : MDEV-10674 - sporadic failure -main.signal_demo3 : MDEV-11720 - Thread stack overrun on labrador main.sp : Modified in 10.1.22 -main.sp-prelocking : Modified in 10.1.21 main.sp-security : MDEV-10607 - sporadic "can't connect" main.stat_tables_par : Modified in 10.1.22 main.status : MDEV-8510 - sporadic wrong result -main.subselect : Modified in 10.1.21 -main.subselect2 : Modified in 10.1.21 -main.subselect4 : Modified in 10.1.22 +main.subselect4 : Modified in 10.1.23 main.subselect_cache : Modified in 10.1.22 -main.subselect_innodb : MDEV-10614 - sporadic wrong results; also modified in 10.1.22 -main.subselect_no_exists_to_in : Uses subselect.test modified in 10.1.21 -main.subselect_no_mat : Uses subselect.test modified in 10.1.21 -main.subselect_no_opts : Uses subselect.test modified in 10.1.21 -main.subselect_no_scache : Uses subselect.test modified in 10.1.21 -main.subselect_no_semijoin : Uses subselect.test modified in 10.1.21 +main.subselect_exists2in : Modified in 10.1.23 +main.subselect_innodb : MDEV-10614 - sporadic wrong results; also modified in 10.1.23 +main.subselect_sj_mat : Modified in 10.1.23 +main.symlink : Modified in 10.1.23 main.symlink-aria-11902 : MDEV-12215 - Unexpected errors; also added in 10.1.22 -main.symlink-myisam-11902 : MDEV-12215 - Unexpected errors; also added in 10.1.22 +main.symlink-myisam-11902 : MDEV-12215 - Unexpected errors; modified in 10.1.23 main.table_elim : Modified in 10.1.22 -main.trigger_null-8605 : Modified in 10.1.21 main.trigger_no_defaults-11698 : Modified in 10.1.22 main.type_datetime_hires : MDEV-10687 - timeout main.type_newdecimal : Modified in 10.1.22 -main.union : Modified in 10.1.21 main.update_innodb : Modified in 10.1.22 -main.view : Modified in 10.1.22 +main.view : Modified in 10.1.23 #---------------------------------------------------------------- archive.archive-big : MDEV-10615 - table is marked as crashed -archive.discover : MDEV-10510 - table is marked as crashed; modified in 10.1.21 +archive.discover : MDEV-10510 - table is marked as crashed +archive.mysqlhotcopy_archive : Uses mysqlhotcopy.inc modified in 10.1.23 #---------------------------------------------------------------- @@ -117,44 +108,47 @@ binlog.binlog_xa_recover : MDEV-8517 - Extra checkpoint binlog_encryption.* : Added in 10.1.20, still unstable (valgrind errors and such) binlog_encryption.rpl_parallel : MDEV-10653 - Timeout -binlog_encryption.rpl_semi_sync : MDEV-11220 - Wrong result +binlog_encryption.rpl_semi_sync : MDEV-11220 - Wrong result, MDEV-11673 - Valgrind warning binlog_encryption.rpl_ssl : MDEV-11542 - Server crash #---------------------------------------------------------------- -connect.jdbc : Modified in 10.1.21 -connect.jdbc_new : Modified in 10.1.21 +connect.secure_file_priv : Modified in 10.1.23 connect.tbl : MDEV-9844, MDEV-10179 - sporadic crashes, valgrind warnings, wrong results connect.xml_zip : Added in 10.1.22 -connect.zip : Added in 10.1.22 +connect.zip : MDEV-12631 - Valgrind warnings; added in 10.1.22 #---------------------------------------------------------------- encryption.create_or_replace : MDEV-9359 - Assertion failure -encryption.encrypt_and_grep : MDEV-11222 - InnoDB error -encryption.innodb-bad-key-change : Modified in 10.1.22 -encryption.innodb-bad-key-change2 : Modified in 10.1.22 -encryption.innodb-bad-key-change3 : Modified in 10.1.21 -encryption.innodb-bad-key-change4 : Modified in 10.1.22 -encryption.innodb-bad-key-change5 : Modified in 10.1.22 -encryption.innodb-bad-key-shutdown : MDEV-9105 - valgrind warnings, assertion failures; also modified in 10.1.22 -encryption.innodb-discard-import : Modified in 10.1.21 -encryption.innodb-discard-import-change : Modified in 10.1.21 -encryption.innodb-encryption-disable : Modified in 10.1.22 -encryption.innodb_encryption_discard_import : MDEV-11218 - wrong result, also modified in 10.1.21 +encryption.debug_key_management : Modified in 10.1.23 +encryption.encrypt_and_grep : Modified in 10.1.23 +encryption.innodb-bad-key-change : Modified in 10.1.23 +encryption.innodb-bad-key-change2 : MDEV-12632 - Valgrind warnings, modified in 10.1.23 +encryption.innodb-bad-key-change4 : Modified in 10.1.23 +encryption.innodb-compressed-blob : Added in 10.1.23 +encryption.innodb-discard-import-change : MDEV-12632 - Valgrind warnings +encryption.innodb_encryption : MDEV-12623 - Assertion failure +encryption.innodb-encryption-disable : Modified in 10.1.23 +encryption.innodb_encryption_discard_import : MDEV-11218 - wrong result encryption.innodb_encryption_filekeys : MDEV-9962 - timeouts -encryption.innodb_encryption-page-compression : MDEV-11420 - Trying to access missing tablespace -encryption.innodb_encryption_tables : MDEV-9359 - Assertion failure +encryption.innodb_encryption-page-compression : MDEV-11420 - Trying to access missing tablespace, MDEV-12630 - assertion failure, modified in 10.1.23 +encryption.innodb_encryption_tables : MDEV-9359 - Assertion failure, MDEV-12623 - assertion failure, MDEV-12624 - timeout encryption.innodb_first_page : MDEV-10689 - crashes -encryption.innodb-log-encrypt : Modified in 10.1.21 -encryption.innodb_lotoftables : MDEV-11531 - InnoDB error, also modified in 10.1.21 -encryption.innodb-missing-key : MDEV-9359 - assertion failure; also modified in 10.1.22 -encryption.innodb_onlinealter_encryption : MDEV-10099 - wrong results +encryption.innodb-force-corrupt : Added in 10.1.23 +encryption.innodb-key-rotation-disable : Added in 10.1.23 +encryption.innodb_lotoftables : MDEV-11531 - InnoDB error +encryption.innodb-missing-key : MDEV-9359 - assertion failure; also modified in 10.1.23 +encryption.innodb_onlinealter_encryption : MDEV-10099 - wrong results; also modified in 10.1.23 encryption.innodb-page_encryption : MDEV-10641 - mutex problem +encryption.innodb-page_encryption_compression : Modified in 10.1.23 +encryption.innodb-page_encryption_log_encryption : Modified in 10.1.23 +encryption.innodb-redo-badkey : Added in 10.1.23 +encryption.innodb-redo-nokeys : Added in 10.1.23 encryption.innodb-read-only : Added in 10.1.22 -encryption.innodb_scrub : MDEV-8139, also was modified in 10.1.21 -encryption.innodb_scrub_background : MDEV_8139, also was modified in 10.1.21 -encryption.innodb_scrub_compressed : MDEV-8139; also was modified and re-enabled in 10.1.21 +encryption.innodb_scrub : MDEV-8139 - scrubbing tests need fixing +encryption.innodb_scrub_background : MDEV-8139 - scrubbing tests need fixing +encryption.innodb_scrub_compressed : MDEV-8139 - scrubbing tests need fixing #---------------------------------------------------------------- @@ -174,7 +168,6 @@ federated.federatedx : MDEV-10617 - Wrong checksum, timeouts funcs_1.is_columns_mysql : Modified in 10.1.22 funcs_1.processlist_val_no_prot : MDEV-11223 - Wrong result -funcs_2.innodb_charset : Modified in 10.1.21 funcs_2.memory_charset : MDEV-10290 - Timeout funcs_2.myisam_charset : MDEV-11535 - Timeout @@ -182,40 +175,78 @@ funcs_2.myisam_charset : MDEV-11535 - Timeout galera.* : suite.pm modified in 10.1.22 +galera.ev51914 : Added in 10.1.23 +galera.GAL-401 : Added in 10.1.23 +galera.GAL-419 : Added in 10.1.23 +galera.GAL-480 : Added in 10.1.23 +galera.galera_account_management : Modified in 10.1.23 +galera.galera_admin : Added in 10.1.23 +galera.galera_bf_abort : Modified in 10.1.23 +galera.galera_desync_overlapped : Added in 10.1.23 +galera.galera_gcache_recover : Added in 10.1.23 +galera.galera_gcache_recover_full_gcache : Added in 10.1.23 +galera.galera_gcache_recover_manytrx : Added in 10.1.23 +galera.galera_many_tables_pk : Modified in 10.1.23 galera.galera_mdev_10812 : Added in 10.1.22 -galera.galera_var_cluster_address : Modified in 10.1.21 -galera.galera_var_dirty_reads : Modified in 10.1.21 -galera.MW-284 : Modified in 10.1.21 -galera.galera_split_brain : Modified in 10.1.21 +galera.galera_restart_on_unknown_option : Added in 10.1.23 +galera.galera_roles : Modified in 10.1.23 +galera.galera_toi_ddl_fk_update : Added in 10.1.23 +galera.galera_toi_drop_database : Added in 10.1.23 +galera.galera_toi_truncate : Added in 10.1.23 galera.galera_var_certify_nonPK_off : Modified in 10.1.22 +galera.galera_var_cluster_address : Modified in 10.1.23 galera.galera_var_max_ws_rows : Modified in 10.1.22 +galera.galera_var_retry_autocommit : Added in 10.1.23 +galera.galera_var_slave_threads : Modified in 10.1.23 +galera.galera_wsrep_log_conficts : Added in 10.1.23 +galera.galera_wsrep_provider_options_syntax : Added in 10.1.23 +galera.galera#414 : Modified in 10.1.23 +galera.lp1376747 : Added in 10.1.23 +galera.lp1376747-2 : Added in 10.1.23 +galera.lp1376747-3 : Added in 10.1.23 +galera.lp1376747-4 : Added in 10.1.23 +galera.MW-258 : Modified in 10.1.23 +galera.MW-313 : Added in 10.1.23 +galera.MW-328A : Added in 10.1.23 +galera.MW-328B : Added in 10.1.23 +galera.MW-328C : Added in 10.1.23 +galera.MW-328D : Added in 10.1.23 +galera.MW-328E : Added in 10.1.23 +galera.MW-329 : Added in 10.1.23 +galera.pxc-421 : Added in 10.1.23 galera_3nodes.* : MDEV-11490 - Warnings not suppressed +galera_3nodes.galera_safe_to_bootstrap : Added in 10.1.23 + #---------------------------------------------------------------- innodb.101_compatibility : Modified in 10.1.22 innodb.alter_key_block_size-11757 : Added in 10.1.22 +innodb.autoinc_debug : Added in 10.1.23 innodb.binlog_consistent : MDEV-10618 - Server fails to start innodb.doublewrite : Modified in 10.1.22 -innodb.group_commit_crash : Modified in 10.1.21 -innodb.group_commit_crash_no_optimize_thread : Modified in 10.1.21 innodb.innodb-32k-crash : Modified in 10.1.22 innodb.innodb-64k-crash : Modified in 10.1.22 +innodb.innodb-alter-debug : Added in 10.1.23 +innodb.innodb-alter-nullable : Added in 10.1.23 innodb.innodb-alter-table : MDEV-10619 - Testcase timeout innodb.innodb-blob : MDEV-12053 - Client crash; also modified in 10.1.22 innodb.innodb_blob_unrecoverable_crash : Modified in 10.1.22 innodb.innodb-bug-14068765 : MDEV-9105 - valgrind warnings, assertion failures innodb.innodb-bug-14084530 : MDEV-9105 - valgrind warnings, assertion failures innodb.innodb_bug11754376 : Modified in 10.1.22 -innodb.innodb_bug14147491 : MDEV-11808, also modified in 10.1.22 -innodb.innodb_bug14676111 : MDEV-11802 - wrong result; also modified in 10.1.22 +innodb.innodb_bug14147491 : MDEV-11808, MDEV-12628 - wrong error codes, also modified in 10.1.23 +innodb.innodb_bug14676111 : Modified in 10.1.23 innodb.innodb_bug30423 : MDEV-7311 - Wrong number of rows in the plan +innodb.innodb_bug53290 : MDEV-12634 - Valgrind warnings innodb.innodb_bug53756 : Modified in 10.1.22 innodb.innodb_bug56947 : Modified in 10.1.22 innodb.innodb_bug59641 : Modified in 10.1.22 -innodb.innodb-change-buffer-recovery : Modified in 10.1.21 +innodb.innodb_defragment : Modified in 10.1.23 +innodb.innodb_defragment_small : Added in 10.1.23 innodb.innodb-get-fk : Modified in 10.1.22 +innodb.innodb-index-online-norebuild : Added in 10.1.23 innodb.innodb-page_compression_default : Added in 10.1.22 innodb.innodb-page_compression_zip : MDEV-10641 - mutex problem innodb.innodb_stats : MDEV-10682 - wrong result @@ -224,17 +255,30 @@ innodb.innodb-wl5522 : MDEV-9105 - valgrind warnings, assertion innodb.innodb-wl5522-1 : MDEV-9105 - valgrind warnings, assertion failures innodb.innodb-wl5522-debug : Modified in 10.1.22 innodb.innodb-wl5522-debug-zip : Modified in 10.1.22 -innodb.log_data_file_size : Added in 10.1.21 -innodb.log_file_size : Added in 10.1.22 +innodb.log_file_size : MDEV-12635 - Valgrind warnings, added in 10.1.22 innodb.read_only_recovery : Added in 10.1.22 innodb.xa_recovery : Modified in 10.1.22 innodb_fts.crash_recovery : Added in 10.1.22 +innodb_fts.create : MDEV-12625 - Valgrind warnings +innodb_fts.fulltext_misc : MDEV-12636 - Valgrind warnings +innodb_fts.innodb-fts-ddl : MDEV-12625 - Valgrind warnings +innodb_fts.innodb-fts-fic : MDEV-12625 - Valgrind warnings +innodb_fts.innodb_fts_misc : MDEV-12625 - Valgrind warnings +innodb_fts.innodb_fts_misc_1 : MDEV-12625 - Valgrind warnings +innodb_fts.innodb_fts_multiple_index : MDEV-12625 - Valgrind warnings +innodb_fts.innodb_fts_proximity : MDEV-12625 - Valgrind warnings innodb_fts.innodb_fts_result_cache_limit : Modified in 10.1.22 +innodb_fts.innodb_fts_stopword_charset : MDEV-12625 - Valgrind warnings; modified in 10.1.23 +innodb_fts.innodb_fts_transaction : MDEV-12625 - Valgrind warnings innodb_fts.misc_debug : Added in 10.1.22 #---------------------------------------------------------------- +mariabackup.* : Added in 10.1.23 + +#---------------------------------------------------------------- + mroonga/storage.column_datetime_32bit_2038 : Wrong result on Alpha mroonga/storage.column_datetime_32bit_before_unix_epoch : Wrong result on Alpha mroonga/storage.column_datetime_32bit_max : Wrong result on Alpha @@ -247,7 +291,7 @@ mroonga/storage.repair_table_no_index_file : MDEV-9364 - #---------------------------------------------------------------- multi_source.gtid : MDEV-10417 - Fails on Mips -multi_source.info_logs : MDEV-10042 - wrong result +multi_source.info_logs : MDEV-10042 - Wrong result, MDEV-12629 - Valgrind warnings multi_source.multisource : MDEV-10417 - Fails on Mips multi_source.reset_slave : MDEV-10690 - wrong result multi_source.simple : MDEV-4633 - Wrong slave status output @@ -287,11 +331,17 @@ perfschema.merge_table_io : Uses table_io_result_helper.inc m perfschema.multi_table_io : Uses table_io_result_helper.inc modified in 10.1.22 perfschema.myisam_table_io : Uses table_io_result_helper.inc modified in 10.1.22 perfschema.part_table_io : Uses table_io_result_helper.inc modified in 10.1.22 +perfschema.pfs_upgrade_event : Modified in 10.1.23 +perfschema.pfs_upgrade_func : Modified in 10.1.23 +perfschema.pfs_upgrade_proc : Modified in 10.1.23 +perfschema.pfs_upgrade_table : Modified in 10.1.23 +perfschema.pfs_upgrade_view : Modified in 10.1.23 perfschema.privilege_table_io : Uses table_io_result_helper.inc modified in 10.1.22 perfschema.rollback_table_io : Uses table_io_result_helper.inc modified in 10.1.22 perfschema.setup_actors : MDEV-10679 - rare crash perfschema.socket_summary_by_event_name_func : MDEV-10622 - Socket summary tables do not match perfschema.stage_mdl_global : MDEV-11803 - wrong result on slow builders +perfschema.stage_mdl_table : MDEV-12639 - Wrong result perfschema.table_name : Modified in 10.1.22 perfschema.temp_table_io : Uses table_io_result_helper.inc modified in 10.1.22 perfschema.threads_mysql : MDEV-10677 - sporadic wrong result; also modified in 10.1.22 @@ -303,6 +353,7 @@ perfschema.view_table_io : Uses table_io_result_helper.inc m plugins.auth_ed25519 : Added in 10.1.22 plugins.cracklib_password_check : MDEV-11650 - valgrind warnings plugins.feedback_plugin_send : MDEV-7932 - ssl failed for url +plugins.pam : MDEV-10940 - valgrind warnings plugins.server_audit : MDEV-9562 - crashes on sol10-sparc plugins.thread_pool_server_audit : MDEV-9562 - crashes on sol10-sparc plugins.two_password_validations : MDEV-11650 - valgrind warnings @@ -310,7 +361,6 @@ plugins.two_password_validations : MDEV-11650 - valgrind warnings #---------------------------------------------------------------- rpl.last_insert_id : MDEV-10625 - warnings in error log -rpl.rpl_alter_extra_persistent : Added in 10.1.21 rpl.rpl_auto_increment : MDEV-10417 - Fails on Mips rpl.rpl_auto_increment_bug45679 : MDEV-10417 - Fails on Mips rpl.rpl_auto_increment_update_failure : MDEV-10625 - warnings in error log @@ -321,7 +371,7 @@ rpl.rpl_domain_id_filter_restart : MDEV-10684 - Wrong result rpl.rpl_gtid_basic : MDEV-10681 - server startup problem rpl.rpl_gtid_crash : MDEV-9501 - Warning: failed registering on master rpl.rpl_gtid_mdev9033 : MDEV-10680 - warnings -rpl.rpl_gtid_stop_start : MDEV-10629 - Crash on shutdown +rpl.rpl_gtid_stop_start : MDEV-10629 - Crash on shutdown, MDEV-12629 - Valgrind warnings rpl.rpl_gtid_until : MDEV-10625 - warnings in error log rpl.rpl_heartbeat_basic : Modified in 10.1.22 rpl.rpl_innodb_bug30888 : MDEV-10417 - Fails on Mips @@ -331,6 +381,7 @@ rpl.rpl_invoked_features : MDEV-10417 - Fails on Mips rpl.rpl_mariadb_slave_capability : MDEV-11018 - sporadic wrong events in binlog rpl.rpl_mdev6020 : MDEV-10630, MDEV-10417 - Timeouts, fails on Mips rpl.rpl_mdev6386 : Modified in 10.1.22 +rpl.rpl_mysql_upgrade : Modified in 10.1.23 rpl.rpl_parallel : MDEV-10653 - Timeouts rpl.rpl_parallel_optimistic : MDEV-10511 - timeout rpl.rpl_parallel_retry : MDEV-11119 - Server crash @@ -338,7 +389,6 @@ rpl.rpl_parallel_temptable : MDEV-10356 - Crash in close_thread_table rpl.rpl_partition_innodb : MDEV-10417 - Fails on Mips rpl.rpl_password_boundaries : MDEV-11534 - Slave IO warnings rpl.rpl_row_log_innodb : MDEV-10688 - Wrong result -rpl.rpl_row_mysqlbinlog : Modified in 10.1.21 rpl.rpl_row_sp001 : MDEV-9329 - Fails on Ubuntu/s390x rpl.rpl_semi_sync : MDEV-11220 - Wrong result rpl.rpl_semi_sync_event_after_sync : MDEV-11806 - warnings @@ -347,10 +397,8 @@ rpl.rpl_semi_sync_wait_point : MDEV-11807 - timeout in wait condition rpl.rpl_show_slave_hosts : MDEV-10681 - server startup problem rpl.rpl_skip_replication : MDEV-9268 - Fails with timeout in sync_slave_with_master on Alpha rpl.rpl_slave_grp_exec : MDEV-10514 - Unexpected deadlock -rpl.rpl_special_charset : Modified in 10.1.21 rpl.rpl_sync : MDEV-10633 - Database page corruption rpl.rpl_temporary_error2 : MDEV-10634 - Wrong number of retries -rpl.sec_behind_master-5114 : Modified in 10.1.21 #---------------------------------------------------------------- @@ -361,6 +409,7 @@ spider/bg.direct_aggregate_part : MDEV-7098 - Trying to unlock mutex that wasn't spider/bg.ha : MDEV-7914, MDEV-9329 - Crash, failures on s390x spider/bg.ha_part : MDEV-9329 - Fails on Ubuntu/s390x spider/bg.spider_fixes : MDEV-7098, MDEV-9329 - Mutex problem, failures on s390x +spider/bg.spider3_fixes : MDEV-12639 - Packets out of order spider/bg.vp_fixes : MDEV-9329 - Fails on Ubuntu/s390x spider/handler.* : MDEV-10987, MDEV-10990 - Tests have not been maintained @@ -389,17 +438,24 @@ sys_vars.rpl_init_slave_func : MDEV-10149 - wrong results sys_vars.secure_file_priv : Modified in 10.1.22 sys_vars.sysvars_innodb : MDEV-6958 - error-prone rdiffs sys_vars.sysvars_server_embedded : MDEV-6958 - error-prone rdiffs +sys_vars.sysvars_wsrep : MDEV-12522 - Dependency on specific wsrep_provider #---------------------------------------------------------------- +tokudb.change_column_all_1000_10 : MDEV-12640 - Crash +tokudb.change_column_bin : MDEV-12640 - Crash tokudb.cluster_filter : MDEV-10678 - Wrong execution plan tokudb.cluster_filter_hidden : MDEV-10678 - Wrong execution plan tokudb.cluster_filter_unpack_varchar : MDEV-10636 - Wrong execution plan tokudb.dir_per_db : MDEV-11537 - Wrong result tokudb.dir_per_db_rename_to_nonexisting_schema : Added in 10.1.22 tokudb.gap_lock_error : Added in 10.1.22 +tokudb.hotindex-insert-bigchar : MDEV-12640 - Crash +tokudb.hotindex-update-1 : MDEV-12640 - Crash tokudb.locks-select-update-3 : Modified in 10.1.22 tokudb.percona_kill_idle_trx_tokudb : Modified in 10.1.22 +tokudb.rows-32m-rand-insert : MDEV-12640 - Crash +tokudb.rows-32m-seq-insert : MDEV-12640 - Crash tokudb_backup.* : suite.opt modified in 10.1.22 @@ -421,6 +477,7 @@ vcol.vcol_select_myisam : Modified in 10.1.22 wsrep.binlog_format : MDEV-11532 - WSREP has not yet prepared node wsrep.pool_of_threads : MDEV-12234 - Library problem on Power +wsrep.wsrep_rpl : Modified in 10.1.23 wsrep_info.* : suite.pm changed in 10.1.22 wsrep_info.plugin : MDEV-12232 - Crash on Power diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index eb03eae6045..96a6e2ea1c2 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -1,5 +1,5 @@ # Copyright (c) 2005, 2015, Oracle and/or its affiliates. -# Copyright (c) 2008, 2016, MariaDB +# Copyright (c) 2008, 2017, MariaDB # # This program is free software; you can redistribute it and/or # modify it under the terms of the GNU Library General Public |