summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/derived.result23
-rw-r--r--mysql-test/r/grant.result70
-rw-r--r--mysql-test/r/join_nested.result95
-rw-r--r--mysql-test/r/join_nested_jcl6.result95
-rw-r--r--mysql-test/r/myisam-system.result2
-rw-r--r--mysql-test/r/mysqldump.result1
-rw-r--r--mysql-test/r/partition_innodb.result91
-rw-r--r--mysql-test/r/partition_myisam.result16
-rw-r--r--mysql-test/r/ps.result72
-rw-r--r--mysql-test/r/range_vs_index_merge.result46
-rw-r--r--mysql-test/r/range_vs_index_merge_innodb.result48
-rw-r--r--mysql-test/r/symlink-aria-11902.result39
-rw-r--r--mysql-test/r/symlink-myisam-11902.result38
-rw-r--r--mysql-test/r/table_elim.result5
-rw-r--r--mysql-test/r/view.result83
15 files changed, 690 insertions, 34 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 0b392305bbf..ce94fe49f5c 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -1014,4 +1014,27 @@ David Yes 210
Edward Yes 150
DROP TABLE example1463;
set sql_mode= @save_sql_mode;
+#
+# MDEV-9028: SELECT DISTINCT constant column of derived table
+# used as the second operand of LEFT JOIN
+#
+create table t1 (id int, data varchar(255));
+insert into t1 values (1,'yes'),(2,'yes');
+select distinct t1.id, tt.id, tt.data
+from t1
+left join
+(select t1.id, 'yes' as data from t1) as tt
+on t1.id = tt.id;
+id id data
+1 1 yes
+2 2 yes
+select distinct t1.id, tt.id, tt.data
+from t1
+left join
+(select t1.id, 'yes' as data from t1 where id > 1) as tt
+on t1.id = tt.id;
+id id data
+2 2 yes
+1 NULL NULL
+drop table t1;
# end of 5.5
diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result
index 3194573e2ef..0b17e82e5a9 100644
--- a/mysql-test/r/grant.result
+++ b/mysql-test/r/grant.result
@@ -2527,6 +2527,54 @@ DROP USER mysqltest_u1@localhost;
# End of Bug#38347.
#
+# BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES
+# DIFFERENTLY'.
+#
+drop database if exists mysqltest_db1;
+create database mysqltest_db1;
+create user mysqltest_u1;
+# Both GRANT statements below should fail with the same error.
+grant execute on function mysqltest_db1.f1 to mysqltest_u1;
+ERROR 42000: FUNCTION or PROCEDURE f1 does not exist
+grant execute on procedure mysqltest_db1.p1 to mysqltest_u1;
+ERROR 42000: FUNCTION or PROCEDURE p1 does not exist
+# Let us show that GRANT behaviour for routines is consistent
+# with GRANT behaviour for tables. Attempt to grant privilege
+# on non-existent table also results in an error.
+grant select on mysqltest_db1.t1 to mysqltest_u1;
+ERROR 42S02: Table 'mysqltest_db1.t1' doesn't exist
+show grants for mysqltest_u1;
+Grants for mysqltest_u1@%
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'%'
+drop database mysqltest_db1;
+drop user mysqltest_u1;
+#
+# Bug#12766319 - 61865: RENAME USER DOES NOT WORK CORRECTLY -
+# REQUIRES FLUSH PRIVILEGES
+#
+CREATE USER foo@'127.0.0.1';
+GRANT ALL ON *.* TO foo@'127.0.0.1';
+# First attempt, should connect successfully
+SELECT user(), current_user();
+user() current_user()
+foo@localhost foo@127.0.0.1
+# Rename the user
+RENAME USER foo@'127.0.0.1' to foo@'127.0.0.0/255.0.0.0';
+# Second attempt, should connect successfully as its valid mask
+# This was failing without fix
+SELECT user(), current_user();
+user() current_user()
+foo@localhost foo@127.0.0.0/255.0.0.0
+# Rename the user back to original
+RENAME USER foo@'127.0.0.0/255.0.0.0' to foo@'127.0.0.1';
+# Third attempt, should connect successfully
+SELECT user(), current_user();
+user() current_user()
+foo@localhost foo@127.0.0.1
+# Clean-up
+DROP USER foo@'127.0.0.1';
+# End of Bug#12766319
+#
# Bug#11756966 - 48958: STORED PROCEDURES CAN BE LEVERAGED TO BYPASS
# DATABASE SECURITY
#
@@ -2552,25 +2600,3 @@ ERROR 42000: Access denied for user 'untrusted'@'localhost' to database 'secret'
# Connection default
DROP USER untrusted@localhost;
DROP DATABASE secret;
-#
-# BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES
-# DIFFERENTLY'.
-#
-drop database if exists mysqltest_db1;
-create database mysqltest_db1;
-create user mysqltest_u1;
-# Both GRANT statements below should fail with the same error.
-grant execute on function mysqltest_db1.f1 to mysqltest_u1;
-ERROR 42000: FUNCTION or PROCEDURE f1 does not exist
-grant execute on procedure mysqltest_db1.p1 to mysqltest_u1;
-ERROR 42000: FUNCTION or PROCEDURE p1 does not exist
-# Let us show that GRANT behaviour for routines is consistent
-# with GRANT behaviour for tables. Attempt to grant privilege
-# on non-existent table also results in an error.
-grant select on mysqltest_db1.t1 to mysqltest_u1;
-ERROR 42S02: Table 'mysqltest_db1.t1' doesn't exist
-show grants for mysqltest_u1;
-Grants for mysqltest_u1@%
-GRANT USAGE ON *.* TO 'mysqltest_u1'@'%'
-drop database mysqltest_db1;
-drop user mysqltest_u1;
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index 84b6ff640e9..6ddd39cbfec 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -1870,4 +1870,99 @@ f4
NULL
NULL
DROP TABLE t1,t2,t3,t4,t5;
+#
+# MDEV-7992: Nested left joins + 'not exists' optimization
+#
+CREATE TABLE t1(
+K1 INT PRIMARY KEY,
+Name VARCHAR(15)
+);
+INSERT INTO t1 VALUES
+(1,'T1Row1'), (2,'T1Row2');
+CREATE TABLE t2(
+K2 INT PRIMARY KEY,
+K1r INT,
+rowTimestamp DATETIME,
+Event VARCHAR(15)
+);
+INSERT INTO t2 VALUES
+(1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'),
+(2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'),
+(3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3');
+SELECT t1a.*, t2a.*,
+t2i.K2 AS K2B, t2i.K1r AS K1rB,
+t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB
+FROM
+t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1
+LEFT JOIN
+( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1)
+ON (t1i.K1 = 1) AND
+(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR
+(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))
+OR (t2i.K2 IS NULL))
+WHERE
+t2a.K1r = 1 AND t2i.K2 IS NULL;
+K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB
+1 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL
+EXPLAIN EXTENDED SELECT t1a.*, t2a.*,
+t2i.K2 AS K2B, t2i.K1r AS K1rB,
+t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB
+FROM
+t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1
+LEFT JOIN
+( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1)
+ON (t1i.K1 = 1) AND
+(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR
+(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))
+OR (t2i.K2 IS NULL))
+WHERE
+t2a.K1r = 1 AND t2i.K2 IS NULL;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1a const PRIMARY PRIMARY 4 const 1 100.00
+1 SIMPLE t2a ALL NULL NULL NULL NULL 3 100.00 Using where
+1 SIMPLE t1i const PRIMARY PRIMARY 4 const 1 100.00 Using index
+1 SIMPLE t2i ALL NULL NULL NULL NULL 3 100.00 Using where; Not exists
+Warnings:
+Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`test`.`t2a`.`K2` AS `K2`,`test`.`t2a`.`K1r` AS `K1r`,`test`.`t2a`.`rowTimestamp` AS `rowTimestamp`,`test`.`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on((`test`.`t2i`.`K1r` = 1))) on(((`test`.`t1i`.`K1` = 1) and (((`test`.`t2i`.`K1r` = 1) and (`test`.`t2i`.`rowTimestamp` > `test`.`t2a`.`rowTimestamp`)) or ((`test`.`t2i`.`rowTimestamp` = `test`.`t2a`.`rowTimestamp`) and (`test`.`t2i`.`K2` > `test`.`t2a`.`K2`)) or isnull(`test`.`t2i`.`K2`)))) where ((`test`.`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`))
+CREATE VIEW v1 AS
+SELECT t2i.*
+FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1
+WHERE t1i.K1 = 1 ;
+SELECT
+t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB,
+t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB
+FROM
+t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1
+LEFT JOIN
+v1 as t2b
+ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR
+(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2))
+OR (t2b.K2 IS NULL)
+WHERE
+t1a.K1 = 1 AND
+t2b.K2 IS NULL;
+K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB
+1 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL
+EXPLAIN EXTENDED SELECT
+t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB,
+t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB
+FROM
+t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1
+LEFT JOIN
+v1 as t2b
+ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR
+(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2))
+OR (t2b.K2 IS NULL)
+WHERE
+t1a.K1 = 1 AND
+t2b.K2 IS NULL;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1a const PRIMARY PRIMARY 4 const 1 100.00
+1 SIMPLE t2a ALL NULL NULL NULL NULL 3 100.00 Using where
+1 SIMPLE t1i const PRIMARY PRIMARY 4 const 1 100.00 Using index
+1 SIMPLE t2i ALL NULL NULL NULL NULL 3 100.00 Using where; Not exists
+Warnings:
+Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS `K1r`,`t2a`.`rowTimestamp` AS `rowTimestamp`,`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on((`test`.`t2i`.`K1r` = 1))) on(((`test`.`t1i`.`K1` = 1) and (((`test`.`t2i`.`K1r` = 1) and (`test`.`t2i`.`rowTimestamp` > `t2a`.`rowTimestamp`)) or ((`test`.`t2i`.`rowTimestamp` = `t2a`.`rowTimestamp`) and (`test`.`t2i`.`K2` > `t2a`.`K2`)) or isnull(`test`.`t2i`.`K2`)))) where ((`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`))
+DROP VIEW v1;
+DROP TABLE t1,t2;
set optimizer_search_depth= @tmp_mdev621;
diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result
index 3b47645ca79..bac8e1cb7db 100644
--- a/mysql-test/r/join_nested_jcl6.result
+++ b/mysql-test/r/join_nested_jcl6.result
@@ -1881,6 +1881,101 @@ f4
NULL
NULL
DROP TABLE t1,t2,t3,t4,t5;
+#
+# MDEV-7992: Nested left joins + 'not exists' optimization
+#
+CREATE TABLE t1(
+K1 INT PRIMARY KEY,
+Name VARCHAR(15)
+);
+INSERT INTO t1 VALUES
+(1,'T1Row1'), (2,'T1Row2');
+CREATE TABLE t2(
+K2 INT PRIMARY KEY,
+K1r INT,
+rowTimestamp DATETIME,
+Event VARCHAR(15)
+);
+INSERT INTO t2 VALUES
+(1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'),
+(2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'),
+(3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3');
+SELECT t1a.*, t2a.*,
+t2i.K2 AS K2B, t2i.K1r AS K1rB,
+t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB
+FROM
+t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1
+LEFT JOIN
+( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1)
+ON (t1i.K1 = 1) AND
+(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR
+(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))
+OR (t2i.K2 IS NULL))
+WHERE
+t2a.K1r = 1 AND t2i.K2 IS NULL;
+K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB
+1 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL
+EXPLAIN EXTENDED SELECT t1a.*, t2a.*,
+t2i.K2 AS K2B, t2i.K1r AS K1rB,
+t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB
+FROM
+t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1
+LEFT JOIN
+( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1)
+ON (t1i.K1 = 1) AND
+(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR
+(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))
+OR (t2i.K2 IS NULL))
+WHERE
+t2a.K1r = 1 AND t2i.K2 IS NULL;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1a const PRIMARY PRIMARY 4 const 1 100.00
+1 SIMPLE t2a ALL NULL NULL NULL NULL 3 100.00 Using where
+1 SIMPLE t1i const PRIMARY PRIMARY 4 const 1 100.00 Using index
+1 SIMPLE t2i ALL NULL NULL NULL NULL 3 100.00 Using where; Not exists
+Warnings:
+Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`test`.`t2a`.`K2` AS `K2`,`test`.`t2a`.`K1r` AS `K1r`,`test`.`t2a`.`rowTimestamp` AS `rowTimestamp`,`test`.`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on((`test`.`t2i`.`K1r` = 1))) on(((`test`.`t1i`.`K1` = 1) and (((`test`.`t2i`.`K1r` = 1) and (`test`.`t2i`.`rowTimestamp` > `test`.`t2a`.`rowTimestamp`)) or ((`test`.`t2i`.`rowTimestamp` = `test`.`t2a`.`rowTimestamp`) and (`test`.`t2i`.`K2` > `test`.`t2a`.`K2`)) or isnull(`test`.`t2i`.`K2`)))) where ((`test`.`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`))
+CREATE VIEW v1 AS
+SELECT t2i.*
+FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1
+WHERE t1i.K1 = 1 ;
+SELECT
+t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB,
+t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB
+FROM
+t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1
+LEFT JOIN
+v1 as t2b
+ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR
+(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2))
+OR (t2b.K2 IS NULL)
+WHERE
+t1a.K1 = 1 AND
+t2b.K2 IS NULL;
+K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB
+1 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL
+EXPLAIN EXTENDED SELECT
+t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB,
+t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB
+FROM
+t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1
+LEFT JOIN
+v1 as t2b
+ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR
+(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2))
+OR (t2b.K2 IS NULL)
+WHERE
+t1a.K1 = 1 AND
+t2b.K2 IS NULL;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1a const PRIMARY PRIMARY 4 const 1 100.00
+1 SIMPLE t2a ALL NULL NULL NULL NULL 3 100.00 Using where
+1 SIMPLE t1i const PRIMARY PRIMARY 4 const 1 100.00 Using index
+1 SIMPLE t2i ALL NULL NULL NULL NULL 3 100.00 Using where; Not exists
+Warnings:
+Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS `K1r`,`t2a`.`rowTimestamp` AS `rowTimestamp`,`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on((`test`.`t2i`.`K1r` = 1))) on(((`test`.`t1i`.`K1` = 1) and (((`test`.`t2i`.`K1r` = 1) and (`test`.`t2i`.`rowTimestamp` > `t2a`.`rowTimestamp`)) or ((`test`.`t2i`.`rowTimestamp` = `t2a`.`rowTimestamp`) and (`test`.`t2i`.`K2` > `t2a`.`K2`)) or isnull(`test`.`t2i`.`K2`)))) where ((`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`))
+DROP VIEW v1;
+DROP TABLE t1,t2;
set optimizer_search_depth= @tmp_mdev621;
CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
diff --git a/mysql-test/r/myisam-system.result b/mysql-test/r/myisam-system.result
index af5de8f2749..65684a3c07b 100644
--- a/mysql-test/r/myisam-system.result
+++ b/mysql-test/r/myisam-system.result
@@ -5,7 +5,7 @@ Warnings:
Warning 2 Can't find file: './test/t1.MYI' (errno: 2 "No such file or directory")
create table t1 (a int) engine=myisam;
select * from t1;
-ERROR HY000: Can't find file: './test/t1.MYI' (errno: 2 "No such file or directory")
+ERROR HY000: Can't find file: './test/t1.MYI' (errno: 20 "Not a directory")
drop table t1;
Warnings:
Warning 2 Can't find file: './test/t1.MYI' (errno: 2 "No such file or directory")
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index cb3c28f42cd..6bf8759de98 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -5340,3 +5340,4 @@ DELIMITER ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
ALTER DATABASE `a\"'``b` CHARACTER SET utf8 COLLATE utf8_general_ci ;
DROP DATABASE `a\"'``b`;
+FOUND /Database: mysql/ in bug11505.sql
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index e1bc075f14a..f863ec5522a 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -732,6 +732,97 @@ SELECT * FROM t1 WHERE d = '1991-01-01';
d
1991-01-01
DROP TABLE t1;
+set global default_storage_engine=default;
+#
+# MDEV-9455: [ERROR] mysqld got signal 11
+#
+CREATE TABLE `t1` (
+`DIARY_TOTAL_DAY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+`IMORY_ID` bigint(20) NOT NULL,
+`NAME` varchar(75) DEFAULT NULL,
+`DATETIME` varchar(10) NOT NULL DEFAULT '',
+`DAILY_CALL_CNT` int(11) DEFAULT NULL,
+`DAILY_SMS_CNT` int(11) DEFAULT NULL,
+`NUMBER` varchar(64) DEFAULT NULL,
+`DURATION` varchar(16) DEFAULT NULL,
+PRIMARY KEY (`DIARY_TOTAL_DAY_SEQ`,`DATETIME`),
+KEY `IDX_t1_01` (`IMORY_ID`,`DATETIME`)
+) AUTO_INCREMENT=328702514 DEFAULT CHARSET=utf8mb4
+PARTITION BY RANGE COLUMNS(`DATETIME`)
+(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
+PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB,
+PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB,
+PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
+PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB,
+PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB,
+PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
+PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB,
+PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB,
+PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB,
+PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB)
+;
+CREATE TABLE `t2` (
+`DIARY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+`IMORY_ID` bigint(20) NOT NULL,
+`CALL_TYPE` varchar(1) DEFAULT NULL,
+`DATA_TYPE` varchar(1) DEFAULT NULL,
+`FEATURES` varchar(1) DEFAULT NULL,
+`NAME` varchar(75) DEFAULT NULL,
+`NUMBER` varchar(64) DEFAULT NULL,
+`DATETIME` datetime NOT NULL,
+`REG_DATE` datetime NOT NULL,
+`TITLE` varchar(50) DEFAULT NULL,
+`BODY` varchar(4200) DEFAULT NULL,
+`MIME_TYPE` varchar(32) DEFAULT NULL,
+`DURATION` varchar(16) DEFAULT NULL,
+`DEVICE_ID` varchar(64) DEFAULT NULL,
+`DEVICE_NAME` varchar(32) DEFAULT NULL,
+PRIMARY KEY (`DIARY_SEQ`,`DATETIME`,`REG_DATE`),
+KEY `IDX_TB_DIARY_01` (`IMORY_ID`,`DATETIME`,`CALL_TYPE`,`NUMBER`),
+KEY `IDX_TB_DIARY_02` (`REG_DATE`)
+) AUTO_INCREMENT=688799006 DEFAULT CHARSET=utf8mb4
+PARTITION BY RANGE COLUMNS(REG_DATE)
+(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
+PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB,
+PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB,
+PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
+PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB,
+PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB,
+PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
+PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB,
+PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB,
+PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB,
+PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB)
+;
+SELECT
+A.IMORY_ID,
+A.NUMBER,
+A.NAME,
+DATE_FORMAT(A.DATETIME, '%Y-%m-%d') AS TARGET_DATE,
+SUM( CASE WHEN A.DATA_TYPE='1' THEN 1 ELSE 0 END) AS CALL_CNT,
+SUM( CASE WHEN A.DATA_TYPE IN ('2', '3') THEN 1 ELSE 0 END) AS SMS_CNT,
+SUM(CAST(A.DURATION AS INT)) AS DURATION,
+( SELECT COUNT(*)
+FROM t1
+WHERE IMORY_ID=A.IMORY_ID
+AND NUMBER=A.NUMBER
+AND NAME=A.NAME
+AND DATETIME = DATE_FORMAT(A.DATETIME, '%Y-%m-%d')
+) STATS_COUNT
+FROM t2 A
+WHERE A.IMORY_ID = 55094102
+AND A.DATETIME LIKE (
+SELECT CONCAT (DATE_FORMAT(DATETIME, '%Y-%m-%d') ,'%')
+FROM t2
+WHERE IMORY_ID=55094102
+AND DIARY_SEQ IN ( 608351221, 608351225, 608351229 )
+group by DATE_FORMAT(DATETIME, '%Y-%m-%d')
+)
+GROUP BY A.IMORY_ID, A.NUMBER, A.NAME, DATE_FORMAT(A.DATETIME, '%Y-%m-%d')
+;
+IMORY_ID NUMBER NAME TARGET_DATE CALL_CNT SMS_CNT DURATION STATS_COUNT
+drop table t2, t1;
+set global default_storage_engine='innodb';
#
# MDEV-5963: InnoDB: Assertion failure in file row0sel.cc line 2503,
# Failing assertion: 0 with "key ptr now exceeds key end by 762 bytes"
diff --git a/mysql-test/r/partition_myisam.result b/mysql-test/r/partition_myisam.result
index bb1a7b19a9d..08a5bf72f64 100644
--- a/mysql-test/r/partition_myisam.result
+++ b/mysql-test/r/partition_myisam.result
@@ -230,6 +230,22 @@ PARTITION pMax VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES (1, "Partition p1, first row");
DROP TABLE t1;
#
+# MDEV-10418 Assertion `m_extra_cache' failed
+# in ha_partition::late_extra_cache(uint)
+#
+CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 INT) ENGINE=MyISAM PARTITION BY RANGE(f2) (PARTITION pmax VALUES LESS THAN MAXVALUE);
+INSERT INTO t2 VALUES (8);
+CREATE ALGORITHM = MERGE VIEW v AS SELECT f2 FROM t2, t1;
+UPDATE v SET f2 = 1;
+SELECT * FROM t2;
+f2
+1
+DROP VIEW v;
+DROP TABLE t2;
+DROP TABLE t1;
+#
# bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED
# TABLE CORRUPTS MYISAM
DROP TABLE if exists `t1`;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index eb5c8ca9377..c8fa17a4e9e 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -4107,4 +4107,76 @@ NULL
NULL
deallocate prepare stmt;
drop table t1,t2,t3,t4;
+#
+# MDEV-11859: the plans for the first and the second executions
+# of PS are not the same
+#
+create table t1 (id int, c varchar(3), key idx(c))engine=myisam;
+insert into t1 values (3,'bar'), (1,'xxx'), (2,'foo'), (5,'yyy');
+prepare stmt1 from
+"explain extended
+ select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'";
+execute stmt1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ref idx idx 6 const 1 100.00 Using index condition
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`c` = 'foo')
+execute stmt1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ref idx idx 6 const 1 100.00 Using index condition
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`c` = 'foo')
+deallocate prepare stmt1;
+prepare stmt1 from
+"select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'";
+flush status;
+execute stmt1;
+id c
+2 foo
+show status like '%Handler_read%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 1
+Handler_read_last 0
+Handler_read_next 1
+Handler_read_prev 0
+Handler_read_retry 0
+Handler_read_rnd 0
+Handler_read_rnd_deleted 0
+Handler_read_rnd_next 0
+flush status;
+execute stmt1;
+id c
+2 foo
+show status like '%Handler_read%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 1
+Handler_read_last 0
+Handler_read_next 1
+Handler_read_prev 0
+Handler_read_retry 0
+Handler_read_rnd 0
+Handler_read_rnd_deleted 0
+Handler_read_rnd_next 0
+deallocate prepare stmt1;
+prepare stmt2 from
+"explain extended
+ select * from t1 where (1, 2) in ( select 3, 4 )";
+execute stmt2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0
+execute stmt2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0
+deallocate prepare stmt2;
+drop table t1;
# End of 5.5 tests
diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index 9af359a55f3..6813c40a5cf 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -60,11 +60,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City
WHERE Population > 100000 AND Name LIKE 'Aba%' OR
-Country IN ('CAN', 'ARG') AND ID < 3800 OR
-Country < 'U' AND Name LIKE 'Zhu%' OR
-ID BETWEEN 3800 AND 3810;
+Country IN ('CAN', 'ARG') AND ID BETWEEN 120 AND 130 OR
+Country <= 'ALB' AND Name LIKE 'L%' OR
+ID BETWEEN 3807 AND 3810;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 35,3,4 NULL 132 Using sort_union(Name,Country,PRIMARY); Using where
+1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,PRIMARY,Country 35,4,3 NULL 31 Using sort_union(Name,PRIMARY,Country); Using where
EXPLAIN
SELECT * FROM City
WHERE (Population > 101000 AND Population < 115000);
@@ -1769,4 +1769,42 @@ a b
167 9999
168 10000
DROP TABLE t1;
+#
+# MDEV-8603: Wrong result OR/AND condition over index fields
+#
+CREATE TABLE t1 (
+id INT NOT NULL,
+state VARCHAR(64),
+capital VARCHAR(64),
+UNIQUE KEY (id),
+KEY state (state,id),
+KEY capital (capital, id)
+);
+INSERT INTO t1 VALUES
+(1,'Arizona','Phoenix'),
+(2,'Hawaii','Honolulu'),
+(3,'Georgia','Atlanta'),
+(4,'Florida','Tallahassee'),
+(5,'Alaska','Juneau'),
+(6,'Michigan','Lansing'),
+(7,'Pennsylvania','Harrisburg'),
+(8,'Virginia','Richmond')
+;
+EXPLAIN
+SELECT * FROM t1 FORCE KEY (state,capital)
+WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
+OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range state,capital state 71 NULL 12 Using index condition; Using where
+SELECT * FROM t1 FORCE KEY (state,capital)
+WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
+OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
+id state capital
+4 Florida Tallahassee
+3 Georgia Atlanta
+2 Hawaii Honolulu
+6 Michigan Lansing
+7 Pennsylvania Harrisburg
+8 Virginia Richmond
+DROP TABLE t1;
set session optimizer_switch='index_merge_sort_intersection=default';
diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result
index 601ae9b7613..13fbc0ac3ef 100644
--- a/mysql-test/r/range_vs_index_merge_innodb.result
+++ b/mysql-test/r/range_vs_index_merge_innodb.result
@@ -61,11 +61,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City
WHERE Population > 100000 AND Name LIKE 'Aba%' OR
-Country IN ('CAN', 'ARG') AND ID < 3800 OR
-Country < 'U' AND Name LIKE 'Zhu%' OR
-ID BETWEEN 3800 AND 3810;
+Country IN ('CAN', 'ARG') AND ID BETWEEN 120 AND 130 OR
+Country <= 'ALB' AND Name LIKE 'L%' OR
+ID BETWEEN 3807 AND 3810;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 35,7,4 NULL 123 Using sort_union(Name,Country,PRIMARY); Using where
+1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 35,3,4 NULL 33 Using sort_union(Name,Country,PRIMARY); Using where
EXPLAIN
SELECT * FROM City
WHERE (Population > 101000 AND Population < 115000);
@@ -369,7 +369,7 @@ WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG')))
OR ((ID BETWEEN 100 AND 200) AND
(Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000)));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 200 Using where
+1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Population,PRIMARY 39,4,4 NULL 305 Using sort_union(Name,Population,PRIMARY); Using where
SELECT * FROM City USE INDEX ()
WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
OR ((ID BETWEEN 100 AND 110) AND
@@ -1770,5 +1770,43 @@ a b
167 9999
168 10000
DROP TABLE t1;
+#
+# MDEV-8603: Wrong result OR/AND condition over index fields
+#
+CREATE TABLE t1 (
+id INT NOT NULL,
+state VARCHAR(64),
+capital VARCHAR(64),
+UNIQUE KEY (id),
+KEY state (state,id),
+KEY capital (capital, id)
+);
+INSERT INTO t1 VALUES
+(1,'Arizona','Phoenix'),
+(2,'Hawaii','Honolulu'),
+(3,'Georgia','Atlanta'),
+(4,'Florida','Tallahassee'),
+(5,'Alaska','Juneau'),
+(6,'Michigan','Lansing'),
+(7,'Pennsylvania','Harrisburg'),
+(8,'Virginia','Richmond')
+;
+EXPLAIN
+SELECT * FROM t1 FORCE KEY (state,capital)
+WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
+OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range state,capital state 71 NULL 10 Using index condition; Using where
+SELECT * FROM t1 FORCE KEY (state,capital)
+WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
+OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
+id state capital
+4 Florida Tallahassee
+3 Georgia Atlanta
+2 Hawaii Honolulu
+6 Michigan Lansing
+7 Pennsylvania Harrisburg
+8 Virginia Richmond
+DROP TABLE t1;
set session optimizer_switch='index_merge_sort_intersection=default';
SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/r/symlink-aria-11902.result b/mysql-test/r/symlink-aria-11902.result
new file mode 100644
index 00000000000..66405b1c25f
--- /dev/null
+++ b/mysql-test/r/symlink-aria-11902.result
@@ -0,0 +1,39 @@
+set default_storage_engine=Aria;
+call mtr.add_suppression("File.*t1.* not found");
+create table mysql.t1 (a int, b char(16), index(a));
+insert mysql.t1 values (100, 'test'),(101,'test');
+create table t1 (a int, b char(16), index(a))
+data directory="MYSQLTEST_VARDIR/tmp/foo";
+insert t1 values (200, 'some'),(201,'some');
+select * from t1;
+a b
+200 some
+201 some
+flush tables;
+set debug_sync='mi_open_datafile SIGNAL ok WAIT_FOR go';
+select * from t1;
+set debug_sync='now WAIT_FOR ok';
+set debug_sync='now SIGNAL go';
+ERROR HY000: File 'MYSQLTEST_VARDIR/tmp/foo/t1.MAD' not found (Errcode: 20 "Not a directory")
+flush tables;
+drop table if exists t1;
+create table t1 (a int, b char(16), index (a))
+index directory="MYSQLTEST_VARDIR/tmp/foo";
+insert t1 values (200, 'some'),(201,'some');
+explain select a from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 5 NULL 2 Using index
+select a from t1;
+a
+200
+201
+flush tables;
+set debug_sync='mi_open_kfile SIGNAL waiting WAIT_FOR run';
+select a from t1;
+set debug_sync='now WAIT_FOR waiting';
+set debug_sync='now SIGNAL run';
+ERROR HY000: Can't find file: './test/t1.MAI' (errno: 20 "Not a directory")
+flush tables;
+drop table if exists t1;
+drop table mysql.t1;
+set debug_sync='RESET';
diff --git a/mysql-test/r/symlink-myisam-11902.result b/mysql-test/r/symlink-myisam-11902.result
new file mode 100644
index 00000000000..4b07aa3f4a7
--- /dev/null
+++ b/mysql-test/r/symlink-myisam-11902.result
@@ -0,0 +1,38 @@
+call mtr.add_suppression("File.*t1.* not found");
+create table mysql.t1 (a int, b char(16), index(a));
+insert mysql.t1 values (100, 'test'),(101,'test');
+create table t1 (a int, b char(16), index(a))
+data directory="MYSQLTEST_VARDIR/tmp/foo";
+insert t1 values (200, 'some'),(201,'some');
+select * from t1;
+a b
+200 some
+201 some
+flush tables;
+set debug_sync='mi_open_datafile SIGNAL ok WAIT_FOR go';
+select * from t1;
+set debug_sync='now WAIT_FOR ok';
+set debug_sync='now SIGNAL go';
+ERROR HY000: File 'MYSQLTEST_VARDIR/tmp/foo/t1.MYD' not found (Errcode: 20 "Not a directory")
+flush tables;
+drop table if exists t1;
+create table t1 (a int, b char(16), index (a))
+index directory="MYSQLTEST_VARDIR/tmp/foo";
+insert t1 values (200, 'some'),(201,'some');
+explain select a from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 5 NULL 2 Using index
+select a from t1;
+a
+200
+201
+flush tables;
+set debug_sync='mi_open_kfile SIGNAL waiting WAIT_FOR run';
+select a from t1;
+set debug_sync='now WAIT_FOR waiting';
+set debug_sync='now SIGNAL run';
+ERROR HY000: Can't find file: './test/t1.MYI' (errno: 20 "Not a directory")
+flush tables;
+drop table if exists t1;
+drop table mysql.t1;
+set debug_sync='RESET';
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index c633261bcd3..3e68a877f02 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -597,7 +597,8 @@ CREATE TABLE t1 (a int(11), b varchar(1)) ;
INSERT IGNORE INTO t1 VALUES (0,'g');
CREATE TABLE t3 ( a varchar(1)) ;
INSERT IGNORE INTO t3 VALUES ('g');
-CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)) ;
+CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (9), (10);
create view v1 as SELECT t1.* FROM t1 LEFT JOIN t2 ON ( t1.a = t2.a ) WHERE t2.a <> 0;
SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
a b
@@ -606,7 +607,7 @@ EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 system NULL NULL NULL NULL 1
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using index
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
drop view v1;
DROP TABLE t1,t2,t3;
#
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 6530fd10a6a..3c6eb235a57 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -5536,6 +5536,89 @@ Warnings:
Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
drop view v1;
drop table t1,t2;
+#
+# MDEV-12099: usage of mergeable view with LEFT JOIN
+# that can be converted to INNER JOIN
+#
+create table t1 (a int, b int, key(a)) engine=myisam;
+insert into t1 values
+(3,20), (7,10), (2,10), (4,30), (8,70),
+(7,70), (9,100), (9,60), (8,80), (7,60);
+create table t2 (c int, d int, key (c)) engine=myisam;
+insert into t2 values
+(50,100), (20, 200), (10,300),
+(150,100), (120, 200), (110,300),
+(250,100), (220, 200), (210,300);
+create table t3(e int, f int not null, key(e), unique (f)) engine=myisam;
+insert into t3 values
+(100, 3), (300, 5), (400, 4), (300,7),
+(300,2), (600, 13), (800, 15), (700, 14),
+(600, 23), (800, 25), (700, 24);
+create view v1 as
+select * from t2 left join t3 on t3.e=t2.d where t3.f is not null;
+select *
+from t1 left join v1 on v1.c=t1.b
+where t1.a < 5;
+a b c d e f
+2 10 10 300 300 5
+2 10 10 300 300 7
+2 10 10 300 300 2
+3 20 NULL NULL NULL NULL
+4 30 NULL NULL NULL NULL
+select *
+from t1 left join ( t2 left join t3 on t3.e=t2.d )
+on t2.c=t1.b and t3.f is not null
+where t1.a < 5;
+a b c d e f
+2 10 10 300 300 5
+2 10 10 300 300 7
+2 10 10 300 300 2
+3 20 NULL NULL NULL NULL
+4 30 NULL NULL NULL NULL
+explain extended
+select *
+from t1 left join v1 on v1.c=t1.b
+where t1.a < 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition
+1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where
+1 SIMPLE t3 ref f,e e 5 test.t2.d 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`e` = `test`.`t2`.`d`) and (`test`.`t3`.`f` is not null) and (`test`.`t1`.`b` is not null) and (`test`.`t2`.`d` is not null))) where (`test`.`t1`.`a` < 5)
+explain extended
+select *
+from t1 left join ( t2 left join t3 on t3.e=t2.d )
+on t2.c=t1.b and t3.f is not null
+where t1.a < 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition
+1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where
+1 SIMPLE t3 ref f,e e 5 test.t2.d 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`e` = `test`.`t2`.`d`) and (`test`.`t3`.`f` is not null) and (`test`.`t1`.`b` is not null) and (`test`.`t2`.`d` is not null))) where (`test`.`t1`.`a` < 5)
+explain extended
+select *
+from t1 left join v1 on v1.c=t1.b and v1.f=t1.a
+where t1.a < 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition
+1 SIMPLE t3 eq_ref f,e f 4 test.t1.a 1 100.00 Using where
+1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`f` = `test`.`t1`.`a`) and (`test`.`t2`.`d` = `test`.`t3`.`e`) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`b` is not null))) where (`test`.`t1`.`a` < 5)
+explain extended
+select *
+from t1 left join ( t2 left join t3 on t3.e=t2.d )
+on t2.c=t1.b and t3.f=t1.a and t3.f is not null
+where t1.a < 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition
+1 SIMPLE t3 eq_ref f,e f 4 test.t1.a 1 100.00 Using where
+1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`f` = `test`.`t1`.`a`) and (`test`.`t2`.`d` = `test`.`t3`.`e`) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`b` is not null))) where (`test`.`t1`.`a` < 5)
+drop view v1;
+drop table t1,t2,t3;
# -----------------------------------------------------------------
# -- End of 5.5 tests.
# -----------------------------------------------------------------
View Lorry Controller Status