From b8ec8196c3df6c1de6fdc547a2b208093babfb01 Mon Sep 17 00:00:00 2001 From: Shipra Jain Date: Sat, 5 Nov 2016 16:52:05 +0100 Subject: Bug#24918325: backport bug11756699 in this patch --- mysql-test/t/disabled.def | 1 - mysql-test/t/log_tables-big.test | 4 ++++ 2 files changed, 4 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 81600642c15..ef973912b63 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -13,5 +13,4 @@ lowercase_table3 : Bug#11762269 2010-06-30 alik main.lowercase_table3 on read_many_rows_innodb : Bug#11748886 2010-11-15 mattiasj report already exists sum_distinct-big : Bug#11764126 2010-11-15 mattiasj was not tested archive-big : Bug#11817185 2011-03-10 Anitha Disabled since this leads to timeout on Solaris Sparc -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 diff --git a/mysql-test/t/log_tables-big.test b/mysql-test/t/log_tables-big.test index 8c956fa6f55..4a10ea12edf 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,,); @@ -33,3 +35,5 @@ connection default; disconnect con1; disconnect con2; + +set @@global.log_output=default; -- cgit v1.2.1 From 2f2103d540f86e326b22cef39183e8e0985da138 Mon Sep 17 00:00:00 2001 From: Shipra Jain Date: Wed, 9 Nov 2016 19:27:35 +0100 Subject: Bug#22874167 : fix for MAIN.LOG_TABLES-BIG UNSTABLE ON LOADED HOSTS --- mysql-test/t/log_tables-big.test | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/log_tables-big.test b/mysql-test/t/log_tables-big.test index 4a10ea12edf..8936a163d73 100644 --- a/mysql-test/t/log_tables-big.test +++ b/mysql-test/t/log_tables-big.test @@ -20,15 +20,15 @@ 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; -- cgit v1.2.1 From e541c414816e904124ba7ab3075c3851da0addda Mon Sep 17 00:00:00 2001 From: Dyre Tjeldvoll Date: Thu, 24 Nov 2016 09:57:54 +0100 Subject: Bug#25092566: CREATE TABLE WITH DATA DIRECTORY CLAUSE DOES NOT REQUIRE SPECIAL PRIVILEGES Require FILE privilege when creating tables using external data directory or index directory. --- mysql-test/t/partition_symlink.test | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/partition_symlink.test b/mysql-test/t/partition_symlink.test index 5fdde8e0abc..9e57d616213 100644 --- a/mysql-test/t/partition_symlink.test +++ b/mysql-test/t/partition_symlink.test @@ -32,6 +32,8 @@ DROP DATABASE IF EXISTS mysqltest2; -- echo # test.t1 have partitions in mysqltest2-directory! -- echo # user root: CREATE USER mysqltest_1@localhost; +-- echo # Need FILE permission to use external datadir or indexdir. + GRANT FILE ON *.* TO mysqltest_1@localhost; CREATE DATABASE mysqltest2; USE mysqltest2; CREATE TABLE t1 (a INT) ENGINE = MyISAM; -- cgit v1.2.1 From 30a59a8d78f10f738aef124fe6736851275192bd Mon Sep 17 00:00:00 2001 From: Sreeharsha Ramanavarapu Date: Tue, 13 Dec 2016 16:41:05 +0530 Subject: Bug #24595937: INCORRECT BEHAVIOR WHEN LOADING DATA TO VIEW Issue: ------ While using the LOAD statement to insert data into an updateable view, the check to verify whether a column is actually updatable is missing. Solution for 5.5 and 5.6: ------------------------- For a view whose column-list in specified in the LOAD command, this check is not performed. This fix adds the check. This is a partial backport of Bug#21097485. Solution for 5.7 and trunk: --------------------------- For a view whose column-list is specified in the LOAD command, this check is already performed. This fix adds the same check when no column-list is specified. --- mysql-test/t/loaddata.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index aa7be52484e..db21058daea 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -292,7 +292,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 ',' -- cgit v1.2.1 From e7a3ccf6b1b1b41e0fa191d26e9e775517c6deee Mon Sep 17 00:00:00 2001 From: Horst Hunger Date: Tue, 3 Jan 2017 09:06:19 +0100 Subject: Bug#25335897: Modified the year of the date value from 2017 to 2037 in first event. --- mysql-test/t/events_2.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/events_2.test b/mysql-test/t/events_2.test index 3d609654b21..d90ec100e42 100644 --- a/mysql-test/t/events_2.test +++ b/mysql-test/t/events_2.test @@ -13,7 +13,7 @@ use events_test; # mysql.event intact checking end # -create event e_26 on schedule at '2017-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 -- cgit v1.2.1 From 7849a27cfb1f175888878704d8f6708a23714538 Mon Sep 17 00:00:00 2001 From: Dyre Tjeldvoll Date: Wed, 22 Feb 2017 20:12:25 +0100 Subject: Bug#25514146: DB_NAME IS IGNORED WHEN CREATING TABLE WITH DATA DIRECTORY Problem: CREATE TABLE using a fully qualified name with INDEX DIR/DATA DIR option reports an error when the current database is not SET. check_access() was incorrectly called with NULL as the database argument in a situation where the database name was not needed for the particular privilege being checked. This will cause the current database to be used, or an error to be reported if there is no current database. Fix: Call check_access() with any_db as the database argument in this situation. --- mysql-test/t/symlink.test | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index 5109137e564..5916d04875b 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -298,3 +298,19 @@ show create table 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; -- cgit v1.2.1 From 6fa5e0814662d691be1a29bf88332348ec7c50c9 Mon Sep 17 00:00:00 2001 From: Bharathy Satish Date: Fri, 17 Mar 2017 08:41:31 +0100 Subject: Bug #25717383: MYSQLDUMP MAY EXECUTE ANY ARBITRARY QUERY While writing comments if database object names has a new line character, then next line is considered a command, rather than a comment. This patch fixes the way comments are constructed in mysqldump. (cherry picked from commit 1099f9d17b1c697c2760f86556f5bae7d202b444) --- mysql-test/t/mysqldump.test | 50 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 50 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 11d766c3293..da958f83c48 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -2425,3 +2425,53 @@ SELECT * FROM t2; DROP TABLE t1; DROP TABLE t2; DROP DATABASE db_20772273; + +--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; +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; +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; -- cgit v1.2.1 From b0395d8701ec49f49ad23f9917a3b2369bb49e7a Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 4 Apr 2017 10:04:52 -0700 Subject: Fixed the bug mdev-12429 and its duplicates mdev-12145 and mdev-9886. Also fixed a wrong result for a test case for mdev-7691 (the alternative one). The test cases for all these bug have materialized semi-joins used inside dependent sub-queries. The patch actually reverts the change inroduced by Monty in 2003. It looks like this change is not valid anymore after the implementation of semi-joins. Adjusted output from EXPLAIN for many other test cases. --- mysql-test/t/subselect_sj_mat.test | 87 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 87 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index dd30b21201d..cd71ae5c901 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1857,4 +1857,91 @@ 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 ); + +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 @save_optimizer_switch= @@optimizer_switch; +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); + +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 @save_optimizer_switch= @@optimizer_switch; +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 -- cgit v1.2.1 From 97fb1f26797828427ad850b0420aaafc74205e71 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 21 Apr 2017 14:34:24 -0700 Subject: Fixed bug mdev-10053. The implementation of the walk method for the class Item_in_subselect was missing. As a result the method never traversed the left operand of any IN subquery predicate. Item_exists_subselect::exists2in_processor() that performs the Exist-To-In transformation calls the walk method to collect info on outer references. As the walk method did not traverse the left operands of the IN subqueries the outer references there were not taken into account and some subqueries that were actually correlated were marked as uncorrelated. It could lead to an attempt of the materialization of such a subquery. Also added a cleanup for some test cases merged from 5.5. --- mysql-test/t/subselect_exists2in.test | 28 ++++++++++++++++++++++++++++ mysql-test/t/subselect_sj_mat.test | 12 ++++++++---- 2 files changed, 36 insertions(+), 4 deletions(-) (limited to 'mysql-test/t') 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_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 5276502190e..7b6c5818a04 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1861,7 +1861,6 @@ drop table t1; --echo # MDEV-12429: IN subquery used in WHERE of EXISTS subquery --echo # -set optimizer_switch='exists_to_in=off'; 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); @@ -1870,17 +1869,20 @@ 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 @save_optimizer_switch= @@optimizer_switch; 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; @@ -1889,7 +1891,6 @@ DROP TABLE t1; --echo # MDEV-12145: IN subquery used in WHERE of EXISTS subquery --echo # -set optimizer_switch='exists_to_in=off'; CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (4),(6); @@ -1899,6 +1900,9 @@ 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 ) ); @@ -1908,11 +1912,11 @@ SELECT * FROM t1 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); --echo # this checks the result set above -set @save_optimizer_switch= @@optimizer_switch; 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; -- cgit v1.2.1 From fac2a7a85dab10ef6cc7628f635fe5a3736514a6 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Sat, 22 Apr 2017 22:51:43 +0400 Subject: MDEV-12495 Conditional jump depends on uninitialised value for: SELECT NULL UNION geom_expression --- mysql-test/t/gis.test | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 9378ea55377..f689902533a 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1492,3 +1492,16 @@ SELECT ASTEXT(p) FROM v1; DROP VIEW v1; # --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 # -- cgit v1.2.1 From 2e7ba70a94b5950a7d1d733c177d1b2a24916213 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 22 Apr 2017 10:30:55 -0700 Subject: Fixed the bug mdev-10693. The code that chooses between materialization of a non-correlated IN subquery and its transformation into an EXISTS correlated subquery assumes that the execution plan for the outer select has been already built. However it was not always so if subqueries occurred in the expressions used for ref access to tables of the outer select. A call of the function create_ref_for_key() in get_best_combination() could trigger a premature execution of the above mentioned code when the execution plan structures for the outer select were not fully built. This could cause a crash of the server. The fix postpones the calls of create_ref_for_key() until the structures for the execution plan is fully built. --- mysql-test/t/subselect_innodb.test | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index d764c435c82..3945c6b10b9 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -453,3 +453,25 @@ SELECT * FROM v1, t2 WHERE ( f1, f2 ) IN ( SELECT f1, f1 FROM t1 ); set join_cache_level = default; 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; + -- cgit v1.2.1