summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/events_2.result4
-rw-r--r--mysql-test/r/innodb_icp.result2
-rw-r--r--mysql-test/r/join_outer.result2
-rw-r--r--mysql-test/r/join_outer_jcl6.result2
-rw-r--r--mysql-test/r/loaddata.result2
-rw-r--r--mysql-test/r/log_tables-big.result14
-rw-r--r--mysql-test/r/myisam_icp.result4
-rw-r--r--mysql-test/r/mysqldump.result63
-rw-r--r--mysql-test/r/subselect3.result8
-rw-r--r--mysql-test/r/subselect3_jcl6.result8
-rw-r--r--mysql-test/r/subselect4.result8
-rw-r--r--mysql-test/r/subselect_innodb.result2
-rw-r--r--mysql-test/r/subselect_mat.result111
-rw-r--r--mysql-test/r/subselect_sj.result2
-rw-r--r--mysql-test/r/subselect_sj_jcl6.result2
-rw-r--r--mysql-test/r/subselect_sj_mat.result111
-rw-r--r--mysql-test/r/symlink.result11
17 files changed, 328 insertions, 28 deletions
diff --git a/mysql-test/r/events_2.result b/mysql-test/r/events_2.result
index 06075b0e66e..65a2de3bae5 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 '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/innodb_icp.result b/mysql-test/r/innodb_icp.result
index 33a8027adf1..fb6cdefa14f 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 ac838997e41..4ea6f4ba192 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1745,7 +1745,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(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 38518c45eae..c8305ccfd2d 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1756,7 +1756,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(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/loaddata.result b/mysql-test/r/loaddata.result
index 9f90184a3f9..f446a503317 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/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 95befeb5a42..ea57146dad2 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
@@ -800,7 +800,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 6bf8759de98..2d26bc774cb 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -5310,6 +5310,69 @@ 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;
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/subselect3.result b/mysql-test/r/subselect3.result
index 0c03959a96a..9d88d0da778 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 isnull(`test`.`t1`.`a`)))) and trigcond(trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`))))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2`
@@ -1241,19 +1241,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 415963af882..069b9a32ae5 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 isnull(`test`.`t1`.`a`)))) and trigcond(trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`))))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2`
@@ -1251,19 +1251,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 32ebf41472d..2b9e952652e 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
@@ -1405,7 +1405,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
@@ -1421,7 +1421,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
@@ -2431,9 +2431,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 fe69dac17a0..be666547f26 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -314,7 +314,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);
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 1ea71b6ee32..f7fddf675ea 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2163,6 +2163,117 @@ execute stmt;
a
0
drop table t1;
+#
+# MDEV-12429: IN subquery used in WHERE of EXISTS subquery
+#
+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);
+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 <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 @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
+#
+set optimizer_switch='exists_to_in=off';
+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 <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 @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 <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_sj.result b/mysql-test/r/subselect_sj.result
index 3ca3f0d35fb..325578b27fd 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 ed6bf8bc11b..595252daafe 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 fee63d8204e..02c89b7d884 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2203,6 +2203,117 @@ execute stmt;
a
0
drop table t1;
+#
+# MDEV-12429: IN subquery used in WHERE of EXISTS subquery
+#
+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);
+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 <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 @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
+#
+set optimizer_switch='exists_to_in=off';
+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 <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 @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 <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 279af1e44d2..3218de4b225 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;
View Lorry Controller Status