summaryrefslogtreecommitdiff
path: root/mysql-test/main
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main')
-rw-r--r--mysql-test/main/alter_user.result2
-rw-r--r--mysql-test/main/alter_user.test2
-rw-r--r--mysql-test/main/grant.result2
-rw-r--r--mysql-test/main/lock_user.result2
-rw-r--r--mysql-test/main/lock_user.test2
-rw-r--r--mysql-test/main/mdev375.test2
-rw-r--r--mysql-test/main/mysqld--defaults-file.result10
-rw-r--r--mysql-test/main/mysqld--defaults-file.test16
-rw-r--r--mysql-test/main/partition_mrr_aria.result161
-rw-r--r--mysql-test/main/partition_mrr_innodb.result161
-rw-r--r--mysql-test/main/partition_mrr_myisam.result161
11 files changed, 516 insertions, 5 deletions
diff --git a/mysql-test/main/alter_user.result b/mysql-test/main/alter_user.result
index cae864fa437..6f88f0311bd 100644
--- a/mysql-test/main/alter_user.result
+++ b/mysql-test/main/alter_user.result
@@ -44,7 +44,7 @@ SET GLOBAL read_only = @start_read_only;
alter user boo;
ERROR HY000: Operation ALTER USER failed for 'boo'
#--warning ER_CANNOT_USER
-alter if exists user boo;
+alter user if exists boo;
Warnings:
Error 1133 Can't find any matching row in the user table
Note 1396 Operation ALTER USER failed for 'boo'
diff --git a/mysql-test/main/alter_user.test b/mysql-test/main/alter_user.test
index 9ea98615272..1e9a9b5625b 100644
--- a/mysql-test/main/alter_user.test
+++ b/mysql-test/main/alter_user.test
@@ -43,7 +43,7 @@ SET GLOBAL read_only = @start_read_only;
--error ER_CANNOT_USER
alter user boo;
--echo #--warning ER_CANNOT_USER
-alter if exists user boo;
+alter user if exists boo;
--echo # Test password related altering.
alter user foo identified by 'something';
diff --git a/mysql-test/main/grant.result b/mysql-test/main/grant.result
index e83083be4ed..e62d55c8c69 100644
--- a/mysql-test/main/grant.result
+++ b/mysql-test/main/grant.result
@@ -608,7 +608,7 @@ Create temporary tables Databases To use CREATE TEMPORARY TABLE
Create view Tables To create new views
Create user Server Admin To create new users
Delete Tables To delete existing rows
-Delete versioning rows Tables To delete versioning table historical rows
+Delete history Tables To delete versioning table historical rows
Drop Databases,Tables To drop databases, tables, and views
Event Server Admin To create, alter, drop and execute events
Execute Functions,Procedures To execute stored routines
diff --git a/mysql-test/main/lock_user.result b/mysql-test/main/lock_user.result
index bc32ee986b3..5048c5a9bee 100644
--- a/mysql-test/main/lock_user.result
+++ b/mysql-test/main/lock_user.result
@@ -31,7 +31,7 @@ alter user user1@localhost account unlock;
#
alter user inexistentUser@localhost account lock;
ERROR HY000: Operation ALTER USER failed for 'inexistentUser'@'localhost'
-alter if exists user inexistentUser@localhost account lock;
+alter user if exists inexistentUser@localhost account lock;
Warnings:
Error 1133 Can't find any matching row in the user table
Note 1396 Operation ALTER USER failed for 'inexistentUser'@'localhost'
diff --git a/mysql-test/main/lock_user.test b/mysql-test/main/lock_user.test
index 366c34ecea8..17ce1cc79da 100644
--- a/mysql-test/main/lock_user.test
+++ b/mysql-test/main/lock_user.test
@@ -44,7 +44,7 @@ alter user user1@localhost account unlock;
--error ER_CANNOT_USER
alter user inexistentUser@localhost account lock;
-alter if exists user inexistentUser@localhost account lock;
+alter user if exists inexistentUser@localhost account lock;
--echo #
--echo # Passing an existing user to CREATE should not be allowed
diff --git a/mysql-test/main/mdev375.test b/mysql-test/main/mdev375.test
index 9e35190b927..6fdb35016ca 100644
--- a/mysql-test/main/mdev375.test
+++ b/mysql-test/main/mdev375.test
@@ -27,6 +27,8 @@ SELECT 2;
--connection default
SELECT 0;
+let $count_sessions=11;
+--source include/wait_until_count_sessions.inc
show status like "Threads_connected";
SET GLOBAL log_warnings=@save_log_warnings;
diff --git a/mysql-test/main/mysqld--defaults-file.result b/mysql-test/main/mysqld--defaults-file.result
index 5fd5ca8d95a..ccad1d119fc 100644
--- a/mysql-test/main/mysqld--defaults-file.result
+++ b/mysql-test/main/mysqld--defaults-file.result
@@ -10,3 +10,13 @@ Could not open required defaults file: MYSQL_TEST_DIR/with.ext
Fatal error in defaults handling. Program aborted
Could not open required defaults file: MYSQL_TEST_DIR/no_extension
Fatal error in defaults handling. Program aborted
+#
+# MDEV-21374: When "--help --verbose" prints out configuration file paths,
+# the --defaults-file option is not considered
+#
+# Test on `defaults-file`
+Default options are read from the following files in the given order:
+MYSQLTEST_VARDIR/my.cnf
+# Test on `defaults-extra-file`
+Default options are read from the following files in the given order:
+<first-defaults> MYSQLTEST_VARDIR/my_test.cnf ~/.my.cnf
diff --git a/mysql-test/main/mysqld--defaults-file.test b/mysql-test/main/mysqld--defaults-file.test
index 3bfe0aa891f..91d30eb1617 100644
--- a/mysql-test/main/mysqld--defaults-file.test
+++ b/mysql-test/main/mysqld--defaults-file.test
@@ -29,3 +29,19 @@ exec $MYSQLD --defaults-file=with.ext --print-defaults 2>&1;
--error 1
exec $MYSQLD --defaults-file=no_extension --print-defaults 2>&1;
+--echo #
+--echo # MDEV-21374: When "--help --verbose" prints out configuration file paths,
+--echo # the --defaults-file option is not considered
+--echo #
+
+--echo # Test on `defaults-file`
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+exec $MYSQLD --defaults-file=$MYSQLTEST_VARDIR/my.cnf --help --verbose | grep -A 1 'Default options are read';
+
+--echo # Test on `defaults-extra-file`
+# <first-defaults> = `/etc/my.cnf /etc/mysql/my.cnf`
+# Using sysconfdir configuration, we don't always have `/etc/mysql/my.cnf`, so replace them with a regex as well.
+copy_file $MYSQLTEST_VARDIR/my.cnf $MYSQLTEST_VARDIR/my_test.cnf;
+--replace_regex /.*my_test.cnf/<first-defaults> MYSQLTEST_VARDIR\/my_test.cnf/
+exec $MYSQLD --defaults-extra-file=$MYSQLTEST_VARDIR/my_test.cnf --help --verbose | grep -A 1 'Default options are read';
+remove_file $MYSQLTEST_VARDIR/my_test.cnf;
diff --git a/mysql-test/main/partition_mrr_aria.result b/mysql-test/main/partition_mrr_aria.result
index 7a0c35a309e..e2b409fa4ae 100644
--- a/mysql-test/main/partition_mrr_aria.result
+++ b/mysql-test/main/partition_mrr_aria.result
@@ -39,6 +39,7 @@ A.a,
B.a,
123456
from t1 A, t1 B;
+set @save_optimizer_switch=@@optimizer_switch;
set optimizer_switch='mrr=on';
explain
select * from t3 force index (key_col) where key_col < 3;
@@ -76,4 +77,164 @@ ID part_id key_col col2
27 7 2 123456
28 8 2 123456
29 9 2 123456
+set optimizer_switch=@save_optimizer_switch;
drop table t1,t3;
+#
+# MDEV-21544: partitioned table is joined with BKA+MRR
+#
+set @save_join_cache_level=@@join_cache_level;
+set @save_optimizer_switch=@@optimizer_switch;
+create table t0 (
+tp int, a int, b int not null, c varchar(12), index idx (a,b)
+);
+insert into t0 values
+(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'),
+(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'),
+(1,3,30,'yzzy'), (1,93,30,'zzzy'),
+(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'),
+(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'),
+(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'),
+(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'),
+(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'),
+(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'),
+(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'),
+(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'),
+(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'),
+(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'),
+(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'),
+(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'),
+(3,4,30,'zzzyy'), (3,94,30,'yyz');
+create table t1 (
+tp int, a int, b int not null, c varchar(12), index idx (a,b)
+)
+partition by list (tp)
+( partition p1 values in (1),
+partition p2 values in (2),
+partition p3 values in (3));
+insert into t1 select * from t0;
+create table t2 (a int, index idx(a));
+insert into t2 values
+(1), (2), (3), (4), (5);
+insert into t2 select a+10 from t2;
+insert into t2 select a+20 from t2;
+analyze table t0,t1,t2;
+Table Op Msg_type Msg_text
+test.t0 analyze status Engine-independent statistics collected
+test.t0 analyze status OK
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status OK
+set join_cache_level=6;
+set optimizer_switch='mrr=on';
+explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index
+1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1
+select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1;
+tp a b c a
+3 4 30 yx 4
+3 4 30 yyxxx 4
+3 4 30 zzyy 4
+3 4 30 zxyy 4
+3 4 30 xxyy 4
+3 4 30 yyzx 4
+3 4 30 zyyy 4
+3 4 30 yzy 4
+3 4 30 zzzyy 4
+explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index
+1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1
+select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1;
+tp a b c a
+3 4 30 yx 4
+3 4 30 yyxxx 4
+3 4 30 zzyy 4
+3 4 30 zxyy 4
+3 4 30 xxyy 4
+3 4 30 yyzx 4
+3 4 30 zyyy 4
+3 4 30 yzy 4
+3 4 30 zzzyy 4
+explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index
+1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4
+select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
+tp a b c a
+2 3 40 yxx 3
+2 4 40 xx 4
+2 3 40 yyx 3
+2 4 40 xxx 4
+2 3 40 xzzzz 3
+2 4 40 yyyxx 4
+2 3 40 xxx 3
+2 4 40 xx 4
+2 3 40 yyxzx 3
+2 4 40 xyx 4
+2 3 40 xxxzz 3
+2 4 40 xxz 4
+2 3 40 zzxxx 3
+2 4 40 zxx 4
+2 3 40 xyyxx 3
+2 4 40 xzzzx 4
+2 3 40 yzxxx 3
+2 4 40 xxzy 4
+explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index
+1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4
+select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
+tp a b c a
+2 3 40 yxx 3
+2 4 40 xx 4
+2 3 40 yyx 3
+2 4 40 xxx 4
+2 3 40 xzzzz 3
+2 4 40 yyyxx 4
+2 3 40 xxx 3
+2 4 40 xx 4
+2 3 40 yyxzx 3
+2 4 40 xyx 4
+2 3 40 xxxzz 3
+2 4 40 xxz 4
+2 3 40 zzxxx 3
+2 4 40 zxx 4
+2 3 40 xyyxx 3
+2 4 40 xzzzx 4
+2 3 40 yzxxx 3
+2 4 40 xxzy 4
+insert into t2 values
+(3), (4), (5);
+analyze table t2;
+Table Op Msg_type Msg_text
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status OK
+explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index
+1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null
+select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null;
+a tp a b c
+explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index
+1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null
+select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null;
+a tp a b c
+set join_cache_level=@save_join_cache_level;
+set optimizer_switch=@save_optimizer_switch;
+drop table t0,t1,t2;
diff --git a/mysql-test/main/partition_mrr_innodb.result b/mysql-test/main/partition_mrr_innodb.result
index c188f7e9929..2efb10de694 100644
--- a/mysql-test/main/partition_mrr_innodb.result
+++ b/mysql-test/main/partition_mrr_innodb.result
@@ -39,6 +39,7 @@ A.a,
B.a,
123456
from t1 A, t1 B;
+set @save_optimizer_switch=@@optimizer_switch;
set optimizer_switch='mrr=on';
explain
select * from t3 force index (key_col) where key_col < 3;
@@ -76,4 +77,164 @@ ID part_id key_col col2
27 7 2 123456
28 8 2 123456
29 9 2 123456
+set optimizer_switch=@save_optimizer_switch;
drop table t1,t3;
+#
+# MDEV-21544: partitioned table is joined with BKA+MRR
+#
+set @save_join_cache_level=@@join_cache_level;
+set @save_optimizer_switch=@@optimizer_switch;
+create table t0 (
+tp int, a int, b int not null, c varchar(12), index idx (a,b)
+);
+insert into t0 values
+(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'),
+(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'),
+(1,3,30,'yzzy'), (1,93,30,'zzzy'),
+(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'),
+(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'),
+(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'),
+(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'),
+(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'),
+(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'),
+(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'),
+(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'),
+(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'),
+(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'),
+(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'),
+(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'),
+(3,4,30,'zzzyy'), (3,94,30,'yyz');
+create table t1 (
+tp int, a int, b int not null, c varchar(12), index idx (a,b)
+)
+partition by list (tp)
+( partition p1 values in (1),
+partition p2 values in (2),
+partition p3 values in (3));
+insert into t1 select * from t0;
+create table t2 (a int, index idx(a));
+insert into t2 values
+(1), (2), (3), (4), (5);
+insert into t2 select a+10 from t2;
+insert into t2 select a+20 from t2;
+analyze table t0,t1,t2;
+Table Op Msg_type Msg_text
+test.t0 analyze status Engine-independent statistics collected
+test.t0 analyze status OK
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status OK
+set join_cache_level=6;
+set optimizer_switch='mrr=on';
+explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index
+1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1
+select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1;
+tp a b c a
+3 4 30 yx 4
+3 4 30 yyxxx 4
+3 4 30 zzyy 4
+3 4 30 zxyy 4
+3 4 30 xxyy 4
+3 4 30 yyzx 4
+3 4 30 zyyy 4
+3 4 30 yzy 4
+3 4 30 zzzyy 4
+explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index
+1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1
+select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1;
+tp a b c a
+3 4 30 yx 4
+3 4 30 yyxxx 4
+3 4 30 zzyy 4
+3 4 30 zxyy 4
+3 4 30 xxyy 4
+3 4 30 yyzx 4
+3 4 30 zyyy 4
+3 4 30 yzy 4
+3 4 30 zzzyy 4
+explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index
+1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4
+select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
+tp a b c a
+2 3 40 yxx 3
+2 4 40 xx 4
+2 3 40 yyx 3
+2 4 40 xxx 4
+2 3 40 xzzzz 3
+2 4 40 yyyxx 4
+2 3 40 xxx 3
+2 4 40 xx 4
+2 3 40 yyxzx 3
+2 4 40 xyx 4
+2 3 40 xxxzz 3
+2 4 40 xxz 4
+2 3 40 zzxxx 3
+2 4 40 zxx 4
+2 3 40 xyyxx 3
+2 4 40 xzzzx 4
+2 3 40 yzxxx 3
+2 4 40 xxzy 4
+explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index
+1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4
+select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
+tp a b c a
+2 3 40 yxx 3
+2 4 40 xx 4
+2 3 40 yyx 3
+2 4 40 xxx 4
+2 3 40 xzzzz 3
+2 4 40 yyyxx 4
+2 3 40 xxx 3
+2 4 40 xx 4
+2 3 40 yyxzx 3
+2 4 40 xyx 4
+2 3 40 xxxzz 3
+2 4 40 xxz 4
+2 3 40 zzxxx 3
+2 4 40 zxx 4
+2 3 40 xyyxx 3
+2 4 40 xzzzx 4
+2 3 40 yzxxx 3
+2 4 40 xxzy 4
+insert into t2 values
+(3), (4), (5);
+analyze table t2;
+Table Op Msg_type Msg_text
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status OK
+explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index
+1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null
+select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null;
+a tp a b c
+explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index
+1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null
+select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null;
+a tp a b c
+set join_cache_level=@save_join_cache_level;
+set optimizer_switch=@save_optimizer_switch;
+drop table t0,t1,t2;
diff --git a/mysql-test/main/partition_mrr_myisam.result b/mysql-test/main/partition_mrr_myisam.result
index d989536324c..4123ae575e0 100644
--- a/mysql-test/main/partition_mrr_myisam.result
+++ b/mysql-test/main/partition_mrr_myisam.result
@@ -39,6 +39,7 @@ A.a,
B.a,
123456
from t1 A, t1 B;
+set @save_optimizer_switch=@@optimizer_switch;
set optimizer_switch='mrr=on';
explain
select * from t3 force index (key_col) where key_col < 3;
@@ -76,8 +77,168 @@ ID part_id key_col col2
27 7 2 123456
28 8 2 123456
29 9 2 123456
+set optimizer_switch=@save_optimizer_switch;
drop table t1,t3;
#
+# MDEV-21544: partitioned table is joined with BKA+MRR
+#
+set @save_join_cache_level=@@join_cache_level;
+set @save_optimizer_switch=@@optimizer_switch;
+create table t0 (
+tp int, a int, b int not null, c varchar(12), index idx (a,b)
+);
+insert into t0 values
+(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'),
+(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'),
+(1,3,30,'yzzy'), (1,93,30,'zzzy'),
+(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'),
+(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'),
+(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'),
+(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'),
+(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'),
+(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'),
+(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'),
+(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'),
+(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'),
+(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'),
+(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'),
+(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'),
+(3,4,30,'zzzyy'), (3,94,30,'yyz');
+create table t1 (
+tp int, a int, b int not null, c varchar(12), index idx (a,b)
+)
+partition by list (tp)
+( partition p1 values in (1),
+partition p2 values in (2),
+partition p3 values in (3));
+insert into t1 select * from t0;
+create table t2 (a int, index idx(a));
+insert into t2 values
+(1), (2), (3), (4), (5);
+insert into t2 select a+10 from t2;
+insert into t2 select a+20 from t2;
+analyze table t0,t1,t2;
+Table Op Msg_type Msg_text
+test.t0 analyze status Engine-independent statistics collected
+test.t0 analyze status OK
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status OK
+set join_cache_level=6;
+set optimizer_switch='mrr=on';
+explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index
+1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1
+select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1;
+tp a b c a
+3 4 30 yx 4
+3 4 30 yyxxx 4
+3 4 30 zzyy 4
+3 4 30 zxyy 4
+3 4 30 xxyy 4
+3 4 30 yyzx 4
+3 4 30 zyyy 4
+3 4 30 yzy 4
+3 4 30 zzzyy 4
+explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index
+1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1
+select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1;
+tp a b c a
+3 4 30 yx 4
+3 4 30 yyxxx 4
+3 4 30 zzyy 4
+3 4 30 zxyy 4
+3 4 30 xxyy 4
+3 4 30 yyzx 4
+3 4 30 zyyy 4
+3 4 30 yzy 4
+3 4 30 zzzyy 4
+explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index
+1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4
+select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
+tp a b c a
+2 3 40 yxx 3
+2 4 40 xx 4
+2 3 40 yyx 3
+2 4 40 xxx 4
+2 3 40 xzzzz 3
+2 4 40 yyyxx 4
+2 3 40 xxx 3
+2 4 40 xx 4
+2 3 40 yyxzx 3
+2 4 40 xyx 4
+2 3 40 xxxzz 3
+2 4 40 xxz 4
+2 3 40 zzxxx 3
+2 4 40 zxx 4
+2 3 40 xyyxx 3
+2 4 40 xzzzx 4
+2 3 40 yzxxx 3
+2 4 40 xxzy 4
+explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index
+1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4
+select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
+tp a b c a
+2 3 40 yxx 3
+2 4 40 xx 4
+2 3 40 yyx 3
+2 4 40 xxx 4
+2 3 40 xzzzz 3
+2 4 40 yyyxx 4
+2 3 40 xxx 3
+2 4 40 xx 4
+2 3 40 yyxzx 3
+2 4 40 xyx 4
+2 3 40 xxxzz 3
+2 4 40 xxz 4
+2 3 40 zzxxx 3
+2 4 40 zxx 4
+2 3 40 xyyxx 3
+2 4 40 xzzzx 4
+2 3 40 yzxxx 3
+2 4 40 xxzy 4
+insert into t2 values
+(3), (4), (5);
+analyze table t2;
+Table Op Msg_type Msg_text
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status OK
+explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index
+1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null
+select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null;
+a tp a b c
+explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index
+1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null
+select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null;
+a tp a b c
+set join_cache_level=@save_join_cache_level;
+set optimizer_switch=@save_optimizer_switch;
+drop table t0,t1,t2;
+#
# MDEV-21628: Index condition pushdown for a simple condition over
# index fields is not used for ref access of partitioned tables when employing BKA
#
View Lorry Controller Status