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/r/log_tables-big.result | 2 ++ mysql-test/t/disabled.def | 1 - mysql-test/t/log_tables-big.test | 4 ++++ 3 files changed, 6 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/log_tables-big.result b/mysql-test/r/log_tables-big.result index 9b81127c825..42df9e8fe16 100644 --- a/mysql-test/r/log_tables-big.result +++ b/mysql-test/r/log_tables-big.result @@ -1,3 +1,4 @@ +set @@global.log_output = 'TABLE'; set session long_query_time=10; select get_lock('bug27638', 1); get_lock('bug27638', 1) @@ -27,3 +28,4 @@ OK select get_lock('bug27638', 101) select release_lock('bug27638'); release_lock('bug27638') 1 +set @@global.log_output=default; 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/r/log_tables-big.result | 12 ++++++------ mysql-test/t/log_tables-big.test | 12 ++++++------ 2 files changed, 12 insertions(+), 12 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/log_tables-big.result b/mysql-test/r/log_tables-big.result index 42df9e8fe16..1e189a7726f 100644 --- a/mysql-test/r/log_tables-big.result +++ b/mysql-test/r/log_tables-big.result @@ -4,25 +4,25 @@ 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'); 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/r/partition_symlink.result | 2 ++ mysql-test/t/partition_symlink.test | 2 ++ 2 files changed, 4 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/partition_symlink.result b/mysql-test/r/partition_symlink.result index f26a1642a52..91cc78f1361 100644 --- a/mysql-test/r/partition_symlink.result +++ b/mysql-test/r/partition_symlink.result @@ -4,6 +4,8 @@ DROP DATABASE IF EXISTS mysqltest2; # test.t1 have partitions in mysqltest2-directory! # user root: CREATE USER mysqltest_1@localhost; +# 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; 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 6786caed623a051a364a73549181806c9d6ca757 Mon Sep 17 00:00:00 2001 From: Pavan Naik Date: Mon, 5 Dec 2016 10:17:40 +0530 Subject: BUG#25147154 : MTR TRIES TO COPY CONTENTS FROM /TMP/DATA Description : ============= When a MTR test run is started, it initializes the server and creates the datadir under '$MYSQL_TEST_DIR/var'('/tmp/var' or '/dev/shm/var' if --mem option is used) location and then copies it to the datadir location of server(s). If $parallel == 1, datadir location of the server is '$MYSQL_TEST_DIR/var/data'. If $parallel > 1, datadir location of any server is '$MYSQL_TEST_DIR/var//data'. This is the reason MTR searches for the initialized datadir in 2 locations('$opt_vardir' and '$opt_vardir/..') from the current vardir location.. But this can cause few problems. If a directory with the name 'data' already exists under '$MYSQL_TEST_DIR' and if the MTR run is started with parallel value 1, then 1. copytree($install_db, '$opt_vardir/..') command will fail if the user doesn't have the access permission to '$MYSQL_TEST_DIR/data' directory. 2. Unnecessary contents from '$MYSQL_TEST_DIR/data' directory will be copied to server datadir location and this might affect the server startup. Fix : ===== Depending on the $parallel value decide whether the path for the initialize datadir is "$opt_vardir"(i.e $parallel = 1) or "$opt_vardir/.."(i.e $parallel > 1). Reviewed-by: Deepa Dixit Reviewed-by: Srikanth B R RB: 14773 --- mysql-test/mysql-test-run.pl | 16 +++++++--------- 1 file changed, 7 insertions(+), 9 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 3eb70c1bdb9..86d37a56835 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -5388,16 +5388,14 @@ sub start_servers($) { my $mysqld_basedir= $mysqld->value('basedir'); if ( $basedir eq $mysqld_basedir ) { - if (! $opt_start_dirty) # If dirty, keep possibly grown system db + 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; + # Copy datadir from installed system db + 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 -- 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/r/loaddata.result | 2 +- mysql-test/t/loaddata.test | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 2d67d24bedd..6942ec6b332 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -316,7 +316,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/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/r/events_2.result | 4 ++-- mysql-test/t/events_2.test | 2 +- 2 files changed, 3 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/events_2.result b/mysql-test/r/events_2.result index 66ec00d7357..31a501d48a6 100644 --- a/mysql-test/r/events_2.result +++ b/mysql-test/r/events_2.result @@ -1,10 +1,10 @@ drop database if exists events_test; create database events_test; use events_test; -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; db name body definer convert_tz(execute_at, 'UTC', 'SYSTEM') on_completion -events_test e_26 set @a = 5 root@localhost 2017-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/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/r/symlink.result | 11 +++++++++++ mysql-test/t/symlink.test | 16 ++++++++++++++++ 2 files changed, 27 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 20736aec47f..22b64cc346c 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -213,3 +213,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/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 18b3aa112348c0ee3fe89764cbb78a72e29e58d2 Mon Sep 17 00:00:00 2001 From: Arun Kuruvila Date: Fri, 24 Feb 2017 16:04:34 +0530 Subject: Bug#25608828: I_MAIN.VARIABLES-BUG21503595 FAILS SPORADICALLY ON PB2-5.5 FOR LINUX-VALGRIND Description: Sporadic failure of variables-bug21503595 test on pb2-5.5 for linux-valgrind platform. Fix: This is a issue related to libc and not related to MySQL code. During dlclose few blocks of memory left unfreed. This is a known issue in libc and needs to be suppressed. Fix: Added a valgrind suppression. --- mysql-test/valgrind.supp | 11 ++++++++++- 1 file changed, 10 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index 72fcd3ef787..27204b83fd0 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -1,4 +1,4 @@ -# Copyright (c) 2005, 2015, Oracle and/or its affiliates. All rights reserved. +# Copyright (c) 2005, 2017, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or # modify it under the terms of the GNU Library General Public @@ -487,6 +487,15 @@ fun:dl_open_worker } +{ + libc pthread_exit 9 + Memcheck:Leak + fun:malloc + fun:_dl_close_worker + fun:_dl_close + fun:_dl_catch_error +} + # # This is seen internally in the system libraries on 64-bit RHAS3. # -- cgit v1.2.1 From 060b1eadf4913f7066484ea34ec62feead1bca44 Mon Sep 17 00:00:00 2001 From: Ramil Kalimullin Date: Fri, 10 Mar 2017 01:19:50 +0400 Subject: BUG#25575605: SETTING --SSL-MODE=REQUIRED SENDS CREDENTIALS BEFORE VERIFYING SSL CONNECTION MYSQL_OPT_SSL_MODE option introduced. It is set in case of --ssl-mode=REQUIRED and permits only SSL connection. (cherry picked from commit 3b2d28578c526f347f5cfe763681eff365731f99) --- mysql-test/r/ssl_mode.result | 6 +++--- mysql-test/r/ssl_mode_no_ssl.result | 22 +++++++++++----------- 2 files changed, 14 insertions(+), 14 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/ssl_mode.result b/mysql-test/r/ssl_mode.result index 38fc4e1dca2..c02a50bdcbd 100644 --- a/mysql-test/r/ssl_mode.result +++ b/mysql-test/r/ssl_mode.result @@ -37,8 +37,8 @@ DROP TABLE t1; # mysql Unknown value to --ssl-mode: ''. Use --ssl-mode=REQUIRED Unknown value to --ssl-mode: 'DERIUQER'. Use --ssl-mode=REQUIRED -ERROR 2026 (HY000): --ssl-mode=REQUIRED option forbids non SSL connections -ERROR 2026 (HY000): --ssl-mode=REQUIRED option forbids non SSL connections -ERROR 2026 (HY000): --ssl-mode=REQUIRED option forbids non SSL connections +ERROR 2026 (HY000): SSL connection error: Client is not configured to use SSL +ERROR 2026 (HY000): SSL connection error: Client is not configured to use SSL +ERROR 2026 (HY000): SSL connection error: Client is not configured to use SSL End of tests diff --git a/mysql-test/r/ssl_mode_no_ssl.result b/mysql-test/r/ssl_mode_no_ssl.result index 409b7a0fa1b..831bb3b71ab 100644 --- a/mysql-test/r/ssl_mode_no_ssl.result +++ b/mysql-test/r/ssl_mode_no_ssl.result @@ -1,22 +1,22 @@ # negative client tests # mysql -ERROR 2026 (HY000): --ssl-mode=REQUIRED option forbids non SSL connections -ERROR 2026 (HY000): --ssl-mode=REQUIRED option forbids non SSL connections -ERROR 2026 (HY000): --ssl-mode=REQUIRED option forbids non SSL connections -ERROR 2026 (HY000): --ssl-mode=REQUIRED option forbids non SSL connections +ERROR 2026 (HY000): SSL connection error: Server doesn't support SSL +ERROR 2026 (HY000): SSL connection error: Server doesn't support SSL +ERROR 2026 (HY000): SSL connection error: Server doesn't support SSL +ERROR 2026 (HY000): SSL connection error: Server doesn't support SSL # mysqldump -mysqldump: Got error: 2026: --ssl-mode=REQUIRED option forbids non SSL connections when trying to connect +mysqldump: Got error: 2026: SSL connection error: Server doesn't support SSL when trying to connect # mysqladmin -mysqladmin: error: '--ssl-mode=REQUIRED option forbids non SSL connections' +mysqladmin: error: 'SSL connection error: Server doesn't support SSL' # mysqlcheck -mysqlcheck: Got error: 2026: --ssl-mode=REQUIRED option forbids non SSL connections when trying to connect +mysqlcheck: Got error: 2026: SSL connection error: Server doesn't support SSL when trying to connect # mysqlimport -mysqlimport: Error: 2026 --ssl-mode=REQUIRED option forbids non SSL connections +mysqlimport: Error: 2026 SSL connection error: Server doesn't support SSL # mysqlshow -mysqlshow: --ssl-mode=REQUIRED option forbids non SSL connections +mysqlshow: SSL connection error: Server doesn't support SSL # mysqlslap -mysqlslap: Error when connecting to server: --ssl-mode=REQUIRED option forbids non SSL connections +mysqlslap: Error when connecting to server: SSL connection error: Server doesn't support SSL # mysqltest -mysqltest: Could not open connection 'default': 2026 --ssl-mode=REQUIRED option forbids non SSL connections +mysqltest: Could not open connection 'default': 2026 SSL connection error: Server doesn't support SSL End of tests -- 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/r/mysqldump.result | 63 +++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/mysqldump.test | 50 ++++++++++++++++++++++++++++++++++ 2 files changed, 113 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index c578f9e8df6..24746a3a51d 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -5283,3 +5283,66 @@ a DROP TABLE t1; DROP TABLE t2; DROP DATABASE db_20772273; +# +# 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 NULL 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 NULL 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; 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/r/innodb_icp,innodb_plugin.rdiff | 4 +- mysql-test/r/innodb_icp.result | 2 +- mysql-test/r/join_outer.result | 2 +- mysql-test/r/join_outer_jcl6.result | 2 +- mysql-test/r/myisam_icp.result | 4 +- mysql-test/r/subselect3.result | 8 +- mysql-test/r/subselect3_jcl6.result | 8 +- mysql-test/r/subselect4.result | 8 +- mysql-test/r/subselect_innodb.result | 2 +- mysql-test/r/subselect_mat.result | 109 ++++++++++++++++++++++++++++ mysql-test/r/subselect_sj.result | 2 +- mysql-test/r/subselect_sj_jcl6.result | 2 +- mysql-test/r/subselect_sj_mat.result | 109 ++++++++++++++++++++++++++++ mysql-test/t/subselect_sj_mat.test | 87 ++++++++++++++++++++++ 14 files changed, 328 insertions(+), 21 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb_icp,innodb_plugin.rdiff b/mysql-test/r/innodb_icp,innodb_plugin.rdiff index 3cb85b79ece..f8ac1186f4a 100644 --- a/mysql-test/r/innodb_icp,innodb_plugin.rdiff +++ b/mysql-test/r/innodb_icp,innodb_plugin.rdiff @@ -1,5 +1,5 @@ ---- r/innodb_icp.result 2013-07-16 17:01:00.000000000 +0400 -+++ r/innodb_icp,innodb_plugin.reject 2013-07-16 17:16:53.000000000 +0400 +--- innodb_icp.result 2017-04-02 23:39:45.063149325 -0700 ++++ innodb_icp,innodb_plugin.result 2017-04-02 23:36:33.279164925 -0700 @@ -213,7 +213,7 @@ EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result index e34a4469de2..9ca02595939 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 40abc197a36..5dbb0f1d8b6 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1744,7 +1744,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`,<`test`.`t2`.`pk`>((select (`test`.`t3`.`pk` + if(isnull(`test`.`t4`.`pk`),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 81395612269..bdc4be0cb8c 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1755,7 +1755,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`,<`test`.`t2`.`pk`>((select (`test`.`t3`.`pk` + if(isnull(`test`.`t4`.`pk`),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/myisam_icp.result b/mysql-test/r/myisam_icp.result index 2c157102270..9b31bca7932 100644 --- a/mysql-test/r/myisam_icp.result +++ b/mysql-test/r/myisam_icp.result @@ -436,7 +436,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 @@ -797,7 +797,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/subselect3.result b/mysql-test/r/subselect3.result index 487ffe655c1..796f4a6f872 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -266,7 +266,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`,<`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(((`test`.`t2`.`a`,`test`.`t2`.`b`),(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond((((`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond((((`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond((`test`.`t1`.`a`)) and trigcond((`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2` @@ -1240,19 +1240,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 9cd3018718c..f3f1ee546ff 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -276,7 +276,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`,<`test`.`t2`.`a`,`test`.`t2`.`b`,`test`.`t2`.`oref`>(((`test`.`t2`.`a`,`test`.`t2`.`b`),(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond((((`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond((((`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond((`test`.`t1`.`a`)) and trigcond((`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2` @@ -1250,19 +1250,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 2228609b82c..d3c63ff9a2f 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; Using temporary +1 PRIMARY t1 index NULL a 5 NULL 2 Using index; Using temporary 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 @@ -1418,7 +1418,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 @@ -1434,7 +1434,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 @@ -2444,9 +2444,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_innodb.result b/mysql-test/r/subselect_innodb.result index e8a545778aa..06cfb8b45d2 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -273,7 +273,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 d 2 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); diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index e4843929fcb..ffa37b025eb 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2163,6 +2163,115 @@ 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 +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 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 @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 ); +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); +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 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 <`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 @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 ) ); +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 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`,<`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 set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 1662d4eb14a..50a70a6614a 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -177,7 +177,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 96bae673184..ccf348fa311 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -190,7 +190,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 cfcbf612030..47f578fb589 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2203,4 +2203,113 @@ 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 +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 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 @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 ); +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); +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 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 <`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 @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 ) ); +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 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`,<`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 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 5c579482eb2dd33c7fea80ea1ab412977606458a Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 7 Apr 2017 16:25:02 -0700 Subject: Adjusted test results after the fix for mdev-12429. --- mysql-test/suite/maria/icp.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index 9552580bb88..0ebf7cf1da8 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -438,7 +438,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 @@ -799,7 +799,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) -- cgit v1.2.1 From d185f1d68bb1f37bea10d8ac6188e5a04faf4522 Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Wed, 19 Apr 2017 14:30:52 +0200 Subject: Fix use of `require` in mysql-test-run. The motivation for this is that Perl is moving towards not having current directory ./ in @INC by default. This is causing mysql-test-run.pl to fail in latest Debian Unstable: https://lists.debian.org/debian-devel-announce/2016/08/msg00013.html However, we have `use "lib"`, there is no need for current directory in @INC, except for a gross hack. In mtr_cases.pm, there is a `require "mtr_misc.pl"`, which hides mtr_misc.pl away in mtr_cases namespace. And things only work because mysql-test-run.pl loads it with a different name, `require "lib/mtr_misc.pl"`! (Perl will `require` only once for each unique filename). Fix this by only using `require` in main program, and referencing functions with :: scope from other namespaces. For multi-use in different namespaces, proper `use` modules should be used. Signed-off-by: Kristian Nielsen --- mysql-test/lib/mtr_cases.pm | 4 +--- mysql-test/lib/mtr_report.pm | 3 +-- mysql-test/mysql-test-run.pl | 10 +++++----- 3 files changed, 7 insertions(+), 10 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/lib/mtr_cases.pm b/mysql-test/lib/mtr_cases.pm index 870df115f58..642cc6ce282 100644 --- a/mysql-test/lib/mtr_cases.pm +++ b/mysql-test/lib/mtr_cases.pm @@ -60,8 +60,6 @@ use My::Test; use My::Find; use My::Suite; -require "mtr_misc.pl"; - # locate plugin suites, depending on whether it's a build tree or installed my @plugin_suitedirs; my $plugin_suitedir_regex; @@ -1098,7 +1096,7 @@ sub get_tags_from_file($$) { $file_to_tags{$file}= $tags; $file_to_master_opts{$file}= $master_opts; $file_to_slave_opts{$file}= $slave_opts; - $file_combinations{$file}= [ uniq(@combinations) ]; + $file_combinations{$file}= [ ::uniq(@combinations) ]; $file_in_overlay{$file} = 1 if $in_overlay; return @{$tags}; } diff --git a/mysql-test/lib/mtr_report.pm b/mysql-test/lib/mtr_report.pm index 9ab82c454ed..97ace54f0fb 100644 --- a/mysql-test/lib/mtr_report.pm +++ b/mysql-test/lib/mtr_report.pm @@ -34,7 +34,6 @@ use mtr_match; use My::Platform; use POSIX qw[ _exit ]; use IO::Handle qw[ flush ]; -require "mtr_io.pl"; use mtr_results; my $tot_real_time= 0; @@ -92,7 +91,7 @@ sub mtr_report_test_passed ($) { my $timer_str= ""; if ( $timer and -f "$::opt_vardir/log/timer" ) { - $timer_str= mtr_fromfile("$::opt_vardir/log/timer"); + $timer_str= ::mtr_fromfile("$::opt_vardir/log/timer"); $tinfo->{timer}= $timer_str; resfile_test_info('duration', $timer_str) if $::opt_resfile; } diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 02ca2fc4a83..0d9626b77e1 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -102,11 +102,11 @@ use mtr_results; use IO::Socket::INET; use IO::Select; -require "lib/mtr_process.pl"; -require "lib/mtr_io.pl"; -require "lib/mtr_gcov.pl"; -require "lib/mtr_gprof.pl"; -require "lib/mtr_misc.pl"; +require "mtr_process.pl"; +require "mtr_io.pl"; +require "mtr_gcov.pl"; +require "mtr_gprof.pl"; +require "mtr_misc.pl"; $SIG{INT}= sub { mtr_error("Got ^C signal"); }; $SIG{HUP}= sub { mtr_error("Hangup detected on controlling terminal"); }; -- cgit v1.2.1