From 62d1e3bf67a12eb6f48ac615bda119e2ed65cf16 Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Mon, 24 Aug 2020 18:52:44 +0200
Subject: MDEV-23569 temporary tables can overwrite existing files

for internal temporary tables: don't use realpath(),
and let them overwrite whatever orphan temp files might've
left in the tmpdir (see main.error_simulation test).

for user created temporary tables: we have to use realpath(),
(see 3a726ab6e2e, remember DATA/INDEX DIRECTORY). don't allow
them to overwrite existing files.

This bug was reported by RACK911 LABS
---
 mysql-test/r/temp_table_symlink.result | 13 +++++++++++++
 mysql-test/t/temp_table_symlink.test   | 32 ++++++++++++++++++++++++++++++++
 storage/maria/ma_create.c              |  6 +++---
 storage/myisam/mi_create.c             |  6 +++---
 4 files changed, 51 insertions(+), 6 deletions(-)
 create mode 100644 mysql-test/r/temp_table_symlink.result
 create mode 100644 mysql-test/t/temp_table_symlink.test

diff --git a/mysql-test/r/temp_table_symlink.result b/mysql-test/r/temp_table_symlink.result
new file mode 100644
index 00000000000..1c5c68170ff
--- /dev/null
+++ b/mysql-test/r/temp_table_symlink.result
@@ -0,0 +1,13 @@
+create table d1 (a int);
+create temporary table t1 (a int);
+create temporary table t2 (a int);
+Got one of the listed errors
+create temporary table t3 (a int) engine=Aria;
+Got one of the listed errors
+select * from information_schema.columns where table_schema='test';
+Got one of the listed errors
+flush tables;
+select * from d1;
+a
+drop temporary table t1;
+drop table d1;
diff --git a/mysql-test/t/temp_table_symlink.test b/mysql-test/t/temp_table_symlink.test
new file mode 100644
index 00000000000..9297b472805
--- /dev/null
+++ b/mysql-test/t/temp_table_symlink.test
@@ -0,0 +1,32 @@
+source include/not_windows.inc;
+
+#
+# MDEV-23569 temporary tables can overwrite existing files
+#
+
+let datadir=`select @@datadir`;
+create table d1 (a int);
+create temporary table t1 (a int);
+perl;
+chdir "$ENV{MYSQL_TMP_DIR}/mysqld.1/";
+for (<#sql*.MYI>) {
+  /^#sql(.*)_([0-9a-f]+_)([0-9a-f]+)\.MYI$/ or die;
+  symlink "$ENV{datadir}/test/d1.MYI", sprintf "#sql$1_$2%x.MYI", hex($3)+1;
+  symlink "$ENV{datadir}/test/d1.MYI", sprintf "#sql$1_$2%x.MAI", hex($3)+1;
+  symlink "$ENV{datadir}/test/d1.MYI", sprintf "#sql$1_$2%x.MAI", hex($3)+2;
+  symlink "$ENV{datadir}/test/d1.MYI", "#sql_$1_0.MAI";
+}
+EOF
+
+error 1,1030;
+create temporary table t2 (a int);
+error 1,1030;
+create temporary table t3 (a int) engine=Aria;
+error 1,1030;
+select * from information_schema.columns where table_schema='test';
+
+flush tables;
+select * from d1;
+drop temporary table t1;
+remove_files_wildcard $MYSQL_TMP_DIR/mysqld.1 *sql*;
+drop table d1;
diff --git a/storage/maria/ma_create.c b/storage/maria/ma_create.c
index 263d8a307b1..e1d2d89020d 100644
--- a/storage/maria/ma_create.c
+++ b/storage/maria/ma_create.c
@@ -322,7 +322,7 @@ int maria_create(const char *name, enum data_file_type datafile_type,
   {
     options|= HA_OPTION_TMP_TABLE;
     tmp_table= TRUE;
-    create_mode|= O_NOFOLLOW;
+    create_mode|= O_NOFOLLOW | (internal_table ? 0 : O_EXCL);
     /* "CREATE TEMPORARY" tables are not crash-safe (dropped at restart) */
     ci->transactional= FALSE;
     flags&= ~HA_CREATE_PAGE_CHECKSUM;
@@ -887,8 +887,8 @@ int maria_create(const char *name, enum data_file_type datafile_type,
   {
     char *iext= strrchr(name, '.');
     int have_iext= iext && !strcmp(iext, MARIA_NAME_IEXT);
-    fn_format(kfilename, name, "", MARIA_NAME_IEXT,
-              MY_UNPACK_FILENAME | MY_RETURN_REAL_PATH |
+    fn_format(kfilename, name, "", MARIA_NAME_IEXT, MY_UNPACK_FILENAME |
+              (internal_table ? 0 : MY_RETURN_REAL_PATH) |
               (have_iext ? MY_REPLACE_EXT : MY_APPEND_EXT));
     klinkname_ptr= NullS;
     /*
diff --git a/storage/myisam/mi_create.c b/storage/myisam/mi_create.c
index 855ac8337b9..7d6095b8a09 100644
--- a/storage/myisam/mi_create.c
+++ b/storage/myisam/mi_create.c
@@ -184,7 +184,7 @@ int mi_create(const char *name,uint keys,MI_KEYDEF *keydefs,
   if (flags & HA_CREATE_TMP_TABLE)
   {
     options|= HA_OPTION_TMP_TABLE;
-    create_mode|= O_NOFOLLOW;
+    create_mode|= O_NOFOLLOW | (internal_table ? 0 : O_EXCL);
   }
   if (flags & HA_CREATE_CHECKSUM || (options & HA_OPTION_CHECKSUM))
   {
@@ -618,8 +618,8 @@ int mi_create(const char *name,uint keys,MI_KEYDEF *keydefs,
   {
     char *iext= strrchr(name, '.');
     int have_iext= iext && !strcmp(iext, MI_NAME_IEXT);
-    fn_format(kfilename, name, "", MI_NAME_IEXT,
-              MY_UNPACK_FILENAME | MY_RETURN_REAL_PATH |
+    fn_format(kfilename, name, "", MI_NAME_IEXT, MY_UNPACK_FILENAME |
+              (internal_table ? 0 : MY_RETURN_REAL_PATH) |
               (have_iext ? MY_REPLACE_EXT : MY_APPEND_EXT));
     klinkname_ptr= 0;
     /* Replace the current file */
-- 
cgit v1.2.1


From f69cc26757733724254ee37aec5a092f520d230f Mon Sep 17 00:00:00 2001
From: Varun Gupta <varun.gupta@mariadb.com>
Date: Thu, 27 Aug 2020 17:34:12 +0530
Subject: MDEV-23596: Assertion `tab->ref.use_count' failed in
 join_read_key_unlock_row

The issue here was that the query was using ORDER BY LIMIT optimzation where
the access method was changed from EQ_REF access to an index scan (index that would
resolve the ORDER BY clause).
But the parameter READ_RECORD::unlock_row was not reset to rr_unlock_row, which is
used when the access method is not EQ_REF access.
---
 mysql-test/r/order_by.result | 16 ++++++++++++++++
 mysql-test/t/order_by.test   | 15 +++++++++++++++
 sql/sql_select.cc            |  3 +++
 3 files changed, 34 insertions(+)

diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index ffb37c9309f..f7427bcd30e 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -3356,3 +3356,19 @@ SET max_sort_length= @save_max_sort_length;
 SET sort_buffer_size= @save_sort_buffer_size;
 SET max_length_for_sort_data= @save_max_length_for_sort_data;
 DROP TABLE t1;
+#
+# MDEV-23596: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY(b));
+INSERT INTO t1 VALUES (0, 1),(1, 2);
+CREATE TABLE t2 SELECT * FROM t1;
+EXPLAIN SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	DEPENDENT SUBQUERY	t1	index	PRIMARY	b	5	NULL	1	Using where
+SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2;
+c
+1
+NULL
+DROP TABLE t1,t2;
+# end of 10.1 tests
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 3a30e0b6c76..3e614ed3316 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -2196,3 +2196,18 @@ SET max_sort_length= @save_max_sort_length;
 SET sort_buffer_size= @save_sort_buffer_size;
 SET max_length_for_sort_data= @save_max_length_for_sort_data;
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-23596: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row
+--echo #
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY(b));
+INSERT INTO t1 VALUES (0, 1),(1, 2);
+CREATE TABLE t2 SELECT * FROM t1;
+
+EXPLAIN SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2;
+SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2;
+
+DROP TABLE t1,t2;
+
+--echo # end of 10.1 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 4c6e87e4f27..e2e87bb1a86 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -21601,6 +21601,9 @@ check_reverse_order:
     else if (select && select->quick)
       select->quick->need_sorted_output();
 
+    tab->read_record.unlock_row= (tab->type == JT_EQ_REF) ?
+                                 join_read_key_unlock_row : rr_unlock_row;
+
   } // QEP has been modified
 
   /*
-- 
cgit v1.2.1


From feac078f15c0be6c0592d74d20cb65b8f5ad2f85 Mon Sep 17 00:00:00 2001
From: Andrei Elkin <andrei.elkin@mariadb.com>
Date: Tue, 11 Aug 2020 21:45:09 +0300
Subject: MDEV-16372 ER_BASE64_DECODE_ERROR upon replaying binary log via
 mysqlbinlog --verbose

(This commit is exclusively for 10.1 branch, do not merge it to upper ones)

In case of a pattern of non-STMT_END-marked Rows-log-event (A) followed by
a STMT_END marked one (B) mysqlbinlog mixes up the base64 encoded rows events
with their pseudo sql representation produced by the verbose option:
      BINLOG '
        base64 encoded data for A
        ### verbose section for A
        base64 encoded data for B
        ### verbose section for B
      '/*!*/;
In effect the produced BINLOG '...' query is not valid and is rejected with the error.
Examples of this way malformed BINLOG could have been found in binlog_row_annotate.result
that gets corrected with the patch.

The issue is fixed with introduction an auxiliary IO_CACHE to hold on the verbose
comments until the terminal STMT_END event is found. The new cache is emptied
out after two pre-existing ones are done at that time.
The correctly produced output now for the above case is as the following:
      BINLOG '
        base64 encoded data for A
        base64 encoded data for B
      '/*!*/;
        ### verbose section for A
        ### verbose section for B

Thanks to Alexey Midenkov for the problem recognition and attempt to tackle,
Venkatesh Duggirala who produced a patch for the upstream whose
idea is exploited here, as well as to MDEV-23077 reporter LukeXwang who
also contributed a piece of a patch aiming at this issue.

Extra: mysqlbinlog_row_minimal refined to not produce mutable numeric values into the result file.
---
 client/mysqlbinlog.cc                              | 11 ++++--
 mysql-test/r/mysqlbinlog_row_minimal.result        | 40 +++++++++++++++-------
 .../suite/binlog/r/binlog_row_annotate.result      |  2 --
 .../binlog/t/binlog_mysqlbinlog_row_frag.test      |  2 +-
 .../binlog_encryption/binlog_row_annotate.result   |  1 -
 mysql-test/t/mysqlbinlog_row_minimal.test          | 34 ++++++++++++++++--
 sql/log_event.cc                                   | 11 ++++--
 sql/log_event.h                                    |  6 +++-
 sql/log_event_old.cc                               |  6 ++++
 9 files changed, 89 insertions(+), 24 deletions(-)

diff --git a/client/mysqlbinlog.cc b/client/mysqlbinlog.cc
index 979a8f29d12..449e4f0e8a9 100644
--- a/client/mysqlbinlog.cc
+++ b/client/mysqlbinlog.cc
@@ -860,7 +860,8 @@ static bool print_base64(PRINT_EVENT_INFO *print_event_info, Log_event *ev)
   ev->print(result_file, print_event_info);
   return
     print_event_info->head_cache.error == -1 ||
-    print_event_info->body_cache.error == -1;
+    print_event_info->body_cache.error == -1 ||
+    print_event_info->tail_cache.error == -1;
 }
 
 
@@ -912,8 +913,12 @@ static bool print_row_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev,
         my_b_printf(body_cache, "'%s\n", print_event_info->delimiter);
 
       // flush cache
-      if ((copy_event_cache_to_file_and_reinit(&print_event_info->head_cache, result_file) ||
-          copy_event_cache_to_file_and_reinit(&print_event_info->body_cache, result_file)))
+      if ((copy_event_cache_to_file_and_reinit(&print_event_info->head_cache,
+                                               result_file) ||
+           copy_event_cache_to_file_and_reinit(&print_event_info->body_cache,
+                                               result_file) ||
+           copy_event_cache_to_file_and_reinit(&print_event_info->tail_cache,
+                                               result_file)))
         return 1;
     }
   }
diff --git a/mysql-test/r/mysqlbinlog_row_minimal.result b/mysql-test/r/mysqlbinlog_row_minimal.result
index 2e34b2885ca..a7fecc7e4bc 100644
--- a/mysql-test/r/mysqlbinlog_row_minimal.result
+++ b/mysql-test/r/mysqlbinlog_row_minimal.result
@@ -295,6 +295,24 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
 DROP TABLE t1,t2;
+#
+# MDEV-16372 ER_BASE64_DECODE_ERROR upon replaying binary log with system table
+#
+FLUSH BINARY LOGS;
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (1), (2) ON DUPLICATE KEY UPDATE pk= pk + 10;
+FLUSH BINARY LOGS;
+Proof: two subsequent patterns must be found
+FOUND /### UPDATE `test`.`t1`/ in mysqlbinlog.sql
+FOUND /### INSERT INTO `test`.`t1`/ in mysqlbinlog.sql
+DROP TABLE t1;
+SELECT * FROM t1;
+pk
+2
+11
+# Cleanup
+DROP TABLE t1;
 CREATE TABLE `t1` (
 `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 `is_deleted` BIT(1) DEFAULT b'0',
@@ -319,28 +337,28 @@ FLUSH BINARY LOGS;
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
-# at 368
-#<date> server id 1  end_log_pos 406 	GTID 0-1-16
+# at POS
+#<date> server id 1  end_log_pos END_LOG_POS 	GTID D-S-N
 /*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
 /*!100001 SET @@session.gtid_domain_id=0*//*!*/;
 /*!100001 SET @@session.server_id=1*//*!*/;
-/*!100001 SET @@session.gtid_seq_no=16*//*!*/;
+/*!100001 SET @@session.gtid_seq_no=21*//*!*/;
 BEGIN
 /*!*/;
-# at 406
-#<date> server id 1  end_log_pos 453 	Table_map: `test`.`t1` mapped to number 34
-# at 453
-#<date> server id 1  end_log_pos 498 	Update_rows: table id 34 flags: STMT_END_F
+# at POS
+#<date> server id 1  end_log_pos END_LOG_POS 	Table_map: `test`.`t1` mapped to number TID
+# at POS
+#<date> server id 1  end_log_pos END_LOG_POS 	Update_rows: table id TID flags: STMT_END_F
 ### UPDATE `test`.`t1`
 ### WHERE
 ###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
 ### SET
 ###   @2=b'1' /* BIT(1) meta=1 nullable=1 is_null=0 */
 ###   @3=X /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
-# at 498
-#<date> server id 1  end_log_pos 576 	Query	thread_id=4	exec_time=x	error_code=0
+# at POS
+#<date> server id 1  end_log_pos END_LOG_POS 	Query	thread_id=TID	exec_time=x	error_code=0
 SET TIMESTAMP=X/*!*/;
-SET @@session.pseudo_thread_id=4/*!*/;
+SET @@session.pseudo_thread_id=TID/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
@@ -350,8 +368,6 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 COMMIT
 /*!*/;
-# at 576
-#<date> server id 1  end_log_pos 620 	Rotate to master-bin.000004  pos: 4
 DELIMITER ;
 # End of log file
 ROLLBACK /* added by mysqlbinlog */;
diff --git a/mysql-test/suite/binlog/r/binlog_row_annotate.result b/mysql-test/suite/binlog/r/binlog_row_annotate.result
index 6070f2da608..4e72ed8b032 100644
--- a/mysql-test/suite/binlog/r/binlog_row_annotate.result
+++ b/mysql-test/suite/binlog/r/binlog_row_annotate.result
@@ -449,7 +449,6 @@ BEGIN
 ### DELETE FROM `test1`.`t1`
 ### WHERE
 ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
-'/*!*/;
 # at #
 #010909  4:46:40 server id #  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
 SET TIMESTAMP=1000000000/*!*/;
@@ -1117,7 +1116,6 @@ BEGIN
 ### DELETE FROM `test1`.`t1`
 ### WHERE
 ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
-'/*!*/;
 # at #
 #010909  4:46:40 server id #  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
 SET TIMESTAMP=1000000000/*!*/;
diff --git a/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row_frag.test b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row_frag.test
index 40360421c6b..9f79deea3db 100644
--- a/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row_frag.test
+++ b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row_frag.test
@@ -11,7 +11,7 @@ SELECT a from t into @a;
 FLUSH LOGS;
 DELETE FROM t;
 
---exec $MYSQL_BINLOG --debug-binlog-row-event-max-encoded-size=256 $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
+--exec $MYSQL_BINLOG --verbose --debug-binlog-row-event-max-encoded-size=256 $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
 
 --let SEARCH_PATTERN= BINLOG @binlog_fragment_0, @binlog_fragment_1
 --let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
diff --git a/mysql-test/suite/binlog_encryption/binlog_row_annotate.result b/mysql-test/suite/binlog_encryption/binlog_row_annotate.result
index 41721441a72..6485b723975 100644
--- a/mysql-test/suite/binlog_encryption/binlog_row_annotate.result
+++ b/mysql-test/suite/binlog_encryption/binlog_row_annotate.result
@@ -449,7 +449,6 @@ BEGIN
 ### DELETE FROM `test1`.`t1`
 ### WHERE
 ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
-'/*!*/;
 # at #
 #010909  4:46:40 server id #  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
 SET TIMESTAMP=1000000000/*!*/;
diff --git a/mysql-test/t/mysqlbinlog_row_minimal.test b/mysql-test/t/mysqlbinlog_row_minimal.test
index 39cae408d4e..57bae4247e1 100644
--- a/mysql-test/t/mysqlbinlog_row_minimal.test
+++ b/mysql-test/t/mysqlbinlog_row_minimal.test
@@ -33,6 +33,35 @@ FLUSH BINARY LOGS;
 
 DROP TABLE t1,t2;
 
+
+--echo #
+--echo # MDEV-16372 ER_BASE64_DECODE_ERROR upon replaying binary log with system table
+--echo #
+
+FLUSH BINARY LOGS;
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (1), (2) ON DUPLICATE KEY UPDATE pk= pk + 10;
+
+--let $binlog = query_get_value(SHOW MASTER STATUS, File, 1)
+
+FLUSH BINARY LOGS;
+--exec $MYSQL_BINLOG --verbose $datadir/$binlog > $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
+--echo Proof: two subsequent patterns must be found
+--let SEARCH_PATTERN= ### UPDATE `test`.`t1`
+--let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
+--source include/search_pattern_in_file.inc
+--let SEARCH_PATTERN= ### INSERT INTO `test`.`t1`
+--let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
+--source include/search_pattern_in_file.inc
+
+DROP TABLE t1;
+--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
+SELECT * FROM t1;
+
+--echo # Cleanup
+DROP TABLE t1;
+--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
 #
 # MDEV-14605 ON UPDATE CURRENT_TIMESTAMP fields by multi-table UPDATE are not logged with binlog_row_image=MINIMAL
 #
@@ -64,11 +93,12 @@ UPDATE t1 t1 INNER JOIN t2 t2 ON t1.ref_id = t2.id
   WHERE t1.id = 1;
 
 --let $binlog = query_get_value(SHOW MASTER STATUS, File, 1)
+--let $binlog_end= query_get_value(SHOW MASTER STATUS, Position, 1)
 
 FLUSH BINARY LOGS;
 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
---replace_regex /\d{6} *\d*:\d\d:\d\d/<date>/ /SET TIMESTAMP=\d*/SET TIMESTAMP=X/ /exec_time=\d*/exec_time=x/ /@3=\d*/@3=X/ /CRC32 0x[0-9a-f]+/CRC32 XXX/
---exec $MYSQL_BINLOG --verbose --verbose --base64-output=DECODE-ROWS $datadir/$binlog --start-position=$binlog_pos
+--replace_regex /table id \d*/table id TID/ /mapped to number \d*/mapped to number TID/ /at \d*/at POS/ /end_log_pos \d*/end_log_pos END_LOG_POS/ /GTID \d*-\d*-\d*/GTID D-S-N/ /\d{6} *\d*:\d\d:\d\d/<date>/ /SET TIMESTAMP=\d*/SET TIMESTAMP=X/ /exec_time=\d*/exec_time=x/ /@3=\d*/@3=X/ /CRC32 0x[0-9a-f]+/CRC32 XXX/ /thread_id=\d*/thread_id=TID/
+--exec $MYSQL_BINLOG --verbose --verbose --base64-output=DECODE-ROWS $datadir/$binlog --start-position=$binlog_pos --stop-position=$binlog_end
 
 DROP TABLE t1,t2;
 
diff --git a/sql/log_event.cc b/sql/log_event.cc
index a1a442df43f..4d7bb1726ed 100644
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -2865,10 +2865,10 @@ void Log_event::print_base64(IO_CACHE* file,
     default:
       break;
     }
-    
+
     if (ev)
     {
-      ev->print_verbose(file, print_event_info);
+      ev->print_verbose(&print_event_info->tail_cache, print_event_info);
       delete ev;
     }
   }
@@ -10671,6 +10671,7 @@ void Rows_log_event::print_helper(FILE *file,
 {
   IO_CACHE *const head= &print_event_info->head_cache;
   IO_CACHE *const body= &print_event_info->body_cache;
+  IO_CACHE *const tail= &print_event_info->tail_cache;
   bool do_print_encoded=
     print_event_info->base64_output_mode != BASE64_OUTPUT_DECODE_ROWS &&
     !print_event_info->short_form;
@@ -10694,6 +10695,11 @@ void Rows_log_event::print_helper(FILE *file,
     }
     copy_cache_to_file_wrapped(file, body, do_print_encoded,
                                print_event_info->delimiter);
+    if (copy_event_cache_to_file_and_reinit(tail, file))
+    {
+      tail->error= -1;
+      return;
+    }
   }
 }
 #endif
@@ -13220,6 +13226,7 @@ st_print_event_info::st_print_event_info()
   myf const flags = MYF(MY_WME | MY_NABP);
   open_cached_file(&head_cache, NULL, NULL, 0, flags);
   open_cached_file(&body_cache, NULL, NULL, 0, flags);
+  open_cached_file(&tail_cache, NULL, NULL, 0, flags);
 }
 #endif
 
diff --git a/sql/log_event.h b/sql/log_event.h
index bdac9f2e581..58e1281c179 100644
--- a/sql/log_event.h
+++ b/sql/log_event.h
@@ -781,9 +781,12 @@ typedef struct st_print_event_info
   ~st_print_event_info() {
     close_cached_file(&head_cache);
     close_cached_file(&body_cache);
+    close_cached_file(&tail_cache);
   }
   bool init_ok() /* tells if construction was successful */
-    { return my_b_inited(&head_cache) && my_b_inited(&body_cache); }
+    { return my_b_inited(&head_cache) &&
+             my_b_inited(&body_cache) &&
+             my_b_inited(&tail_cache); }
 
 
   /* Settings on how to print the events */
@@ -811,6 +814,7 @@ typedef struct st_print_event_info
    */
   IO_CACHE head_cache;
   IO_CACHE body_cache;
+  IO_CACHE tail_cache;
 } PRINT_EVENT_INFO;
 #endif
 
diff --git a/sql/log_event_old.cc b/sql/log_event_old.cc
index 0a4d7227dfb..8f4f6c296c4 100644
--- a/sql/log_event_old.cc
+++ b/sql/log_event_old.cc
@@ -1856,6 +1856,7 @@ void Old_rows_log_event::print_helper(FILE *file,
 {
   IO_CACHE *const head= &print_event_info->head_cache;
   IO_CACHE *const body= &print_event_info->body_cache;
+  IO_CACHE *const tail= &print_event_info->tail_cache;
   bool do_print_encoded=
     print_event_info->base64_output_mode != BASE64_OUTPUT_DECODE_ROWS &&
     !print_event_info->short_form;
@@ -1879,6 +1880,11 @@ void Old_rows_log_event::print_helper(FILE *file,
     }
     copy_cache_to_file_wrapped(file, body, do_print_encoded,
                                print_event_info->delimiter);
+    if (copy_event_cache_to_file_and_reinit(tail, file))
+    {
+      tail->error= -1;
+      return;
+    }
   }
 }
 #endif
-- 
cgit v1.2.1


From 94e9dc95d4bebf711f001e16bbb53fd8d6a58b8c Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= <marko.makela@mariadb.com>
Date: Tue, 1 Sep 2020 15:52:36 +0300
Subject: MDEV-23600 Division by 0 in row_search_with_covering_prefix

The InnoDB index fields store bytes, not characters.
Remove some unnecessary conversions from characters to bytes.

This also fixes MDEV-20422 and the wrong-result bug MDEV-12486.
---
 ..._prefix_index_cluster_optimization.combinations |   5 +
 .../innodb_prefix_index_cluster_optimization.inc   |   1 +
 mysql-test/r/fast_prefix_index_fetch_innodb.result |  57 ++-
 mysql-test/r/innodb_ext_key,off.rdiff              | 295 ++++++++++++++
 mysql-test/r/innodb_ext_key.result                 | 422 ---------------------
 mysql-test/t/fast_prefix_index_fetch_innodb.test   |  52 ++-
 mysql-test/t/innodb_ext_key.combinations           |   5 +
 mysql-test/t/innodb_ext_key.test                   | 222 +----------
 storage/innobase/row/row0sel.cc                    |  31 +-
 storage/xtradb/row/row0sel.cc                      |  32 +-
 10 files changed, 439 insertions(+), 683 deletions(-)
 create mode 100644 mysql-test/include/innodb_prefix_index_cluster_optimization.combinations
 create mode 100644 mysql-test/include/innodb_prefix_index_cluster_optimization.inc
 create mode 100644 mysql-test/r/innodb_ext_key,off.rdiff
 create mode 100644 mysql-test/t/innodb_ext_key.combinations

diff --git a/mysql-test/include/innodb_prefix_index_cluster_optimization.combinations b/mysql-test/include/innodb_prefix_index_cluster_optimization.combinations
new file mode 100644
index 00000000000..ad82f82aa9d
--- /dev/null
+++ b/mysql-test/include/innodb_prefix_index_cluster_optimization.combinations
@@ -0,0 +1,5 @@
+[covering]
+innodb_prefix_index_cluster_optimization=on
+
+[unoptimized]
+innodb_prefix_index_cluster_optimization=off
diff --git a/mysql-test/include/innodb_prefix_index_cluster_optimization.inc b/mysql-test/include/innodb_prefix_index_cluster_optimization.inc
new file mode 100644
index 00000000000..c841fece702
--- /dev/null
+++ b/mysql-test/include/innodb_prefix_index_cluster_optimization.inc
@@ -0,0 +1 @@
+--source include/have_innodb.inc
diff --git a/mysql-test/r/fast_prefix_index_fetch_innodb.result b/mysql-test/r/fast_prefix_index_fetch_innodb.result
index c6d96389b08..885ee000c28 100644
--- a/mysql-test/r/fast_prefix_index_fetch_innodb.result
+++ b/mysql-test/r/fast_prefix_index_fetch_innodb.result
@@ -1,4 +1,4 @@
-drop table if exists prefixinno;
+SET @save_opt= @@GLOBAL.innodb_prefix_index_cluster_optimization;
 set global innodb_prefix_index_cluster_optimization = ON;
 show variables like 'innodb_prefix_index_cluster_optimization';
 Variable_name	Value
@@ -346,10 +346,10 @@ f1
 🐱🌑
 select @cluster_lookups;
 @cluster_lookups
-2
+1
 select @cluster_lookups_avoided;
 @cluster_lookups_avoided
-0
+1
 # Eligible - record length is shorter than prefix length
 SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%';
 f1
@@ -366,10 +366,10 @@ f1
 🌒
 select @cluster_lookups;
 @cluster_lookups
-1
+0
 select @cluster_lookups_avoided;
 @cluster_lookups_avoided
-1
+2
 DROP TABLE t1;
 CREATE TABLE t1(
 col1 INT,
@@ -398,4 +398,49 @@ select @cluster_lookups_avoided;
 @cluster_lookups_avoided
 0
 DROP TABLE t1;
-set global innodb_prefix_index_cluster_optimization = OFF;
+#
+# MDEV-23600 Division by 0 in row_search_with_covering_prefix()
+#
+CREATE TABLE t(c POINT UNIQUE) ENGINE=InnoDB;
+INSERT t SET c=POINT(1,1);
+SELECT * FROM t WHERE c > (SELECT MAX(c) FROM t);
+c
+DROP TABLE t;
+#
+# MDEV-12486 Wrong results with innodb_prefix_index_cluster_optimization
+#
+CREATE TABLE wp_blogs (
+blog_id bigint(20) NOT NULL auto_increment,
+site_id bigint(20) NOT NULL default '0',
+domain varchar(200) NOT NULL default '',
+path varchar(100) NOT NULL default '',
+registered datetime NOT NULL default '0000-00-00 00:00:00',
+last_updated datetime NOT NULL default '0000-00-00 00:00:00',
+public tinyint(2) NOT NULL default '1',
+archived tinyint(2) NOT NULL default '0',
+mature tinyint(2) NOT NULL default '0',
+spam tinyint(2) NOT NULL default '0',
+deleted tinyint(2) NOT NULL default '0',
+lang_id int(11) NOT NULL default '0',
+PRIMARY KEY  (blog_id),
+KEY domain (domain(50),path(5)),
+KEY lang_id (lang_id)
+) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
+INSERT INTO wp_blogs (domain, path) VALUES
+('domain.no', '/fondsinvesteringer/'), ('domain.no', '/'),
+('foo', 'bar'), ('bar', 'foo'), ('foo', 'foo'), ('bar', 'bar'),
+('foo', 'foobar'), ('bar', 'foobar'), ('foobar', 'foobar');
+SET GLOBAL innodb_prefix_index_cluster_optimization=off;
+SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no')
+AND path IN ( '/fondsinvesteringer/', '/' );
+blog_id
+2
+1
+SET GLOBAL innodb_prefix_index_cluster_optimization=on;
+SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no')
+AND path IN ( '/fondsinvesteringer/', '/' );
+blog_id
+2
+1
+DROP TABLE wp_blogs;
+SET GLOBAL innodb_prefix_index_cluster_optimization = @save_opt;
diff --git a/mysql-test/r/innodb_ext_key,off.rdiff b/mysql-test/r/innodb_ext_key,off.rdiff
new file mode 100644
index 00000000000..47ca1e6849a
--- /dev/null
+++ b/mysql-test/r/innodb_ext_key,off.rdiff
@@ -0,0 +1,295 @@
+@@ -4,7 +4,7 @@
+ explain
+ select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	8	const,const	1	Using index
++1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	const	5	Using where
+ flush status;
+ select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
+ count(*)
+@@ -14,7 +14,7 @@
+ Handler_read_first	0
+ Handler_read_key	1
+ Handler_read_last	0
+-Handler_read_next	1
++Handler_read_next	5
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -45,7 +45,7 @@
+ select count(*) from lineitem
+ where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	range	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	8	NULL	1	Using where; Using index
++1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	const	6	Using where; Using index
+ flush status;
+ select count(*) from lineitem
+ where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
+@@ -56,7 +56,7 @@
+ Handler_read_first	0
+ Handler_read_key	1
+ Handler_read_last	0
+-Handler_read_next	1
++Handler_read_next	6
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -66,7 +66,7 @@
+ select l_orderkey, l_linenumber from lineitem
+ where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	range	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	8	NULL	3	Using where; Using index
++1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	const	6	Using where; Using index
+ flush status;
+ select l_orderkey, l_linenumber from lineitem
+ where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
+@@ -79,7 +79,7 @@
+ Handler_read_first	0
+ Handler_read_key	1
+ Handler_read_last	0
+-Handler_read_next	3
++Handler_read_next	6
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -88,7 +88,7 @@
+ explain
+ select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
++1	SIMPLE	lineitem	ref	i_l_shipdate	i_l_shipdate	4	const	6	Using index
+ flush status;
+ select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
+ min(l_orderkey)
+@@ -98,7 +98,7 @@
+ Handler_read_first	0
+ Handler_read_key	1
+ Handler_read_last	0
+-Handler_read_next	0
++Handler_read_next	6
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -108,7 +108,7 @@
+ select min(l_orderkey) from lineitem 
+ where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
++1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	const	6	Using where; Using index
+ flush status;
+ select min(l_orderkey) from lineitem 
+ where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
+@@ -119,7 +119,7 @@
+ Handler_read_first	0
+ Handler_read_key	1
+ Handler_read_last	0
+-Handler_read_next	0
++Handler_read_next	6
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -129,7 +129,7 @@
+ select max(l_linenumber) from lineitem 
+ where l_shipdate='1992-07-01' and l_orderkey=130;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
++1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	const	5	Using where
+ flush status;
+ select max(l_linenumber) from lineitem 
+ where l_shipdate='1992-07-01' and l_orderkey=130;
+@@ -140,7 +140,7 @@
+ Handler_read_first	0
+ Handler_read_key	1
+ Handler_read_last	0
+-Handler_read_next	0
++Handler_read_next	5
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -152,7 +152,7 @@
+ where l_shipdate='1992-07-01' and l_orderkey=130 
+ or l_receiptdate='1992-07-01' and l_orderkey=5603;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	index_merge	i_l_shipdate,i_l_receiptdate	i_l_shipdate,i_l_receiptdate	8,8	NULL	2	Using union(i_l_shipdate,i_l_receiptdate); Using where
++1	SIMPLE	lineitem	index_merge	i_l_shipdate,i_l_receiptdate	i_l_shipdate,i_l_receiptdate	4,4	NULL	9	Using union(i_l_shipdate,i_l_receiptdate); Using where
+ flush status;
+ select l_orderkey, l_linenumber 
+ from lineitem use index (i_l_shipdate, i_l_receiptdate)
+@@ -166,10 +166,10 @@
+ Handler_read_first	0
+ Handler_read_key	2
+ Handler_read_last	0
+-Handler_read_next	2
++Handler_read_next	9
+ Handler_read_prev	0
+ Handler_read_retry	0
+-Handler_read_rnd	2
++Handler_read_rnd	9
+ Handler_read_rnd_deleted	0
+ Handler_read_rnd_next	0
+ explain
+@@ -178,7 +178,7 @@
+ where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
+ or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	index_merge	i_l_shipdate,i_l_receiptdate	i_l_shipdate,i_l_receiptdate	8,8	NULL	3	Using sort_union(i_l_shipdate,i_l_receiptdate); Using where
++1	SIMPLE	lineitem	index_merge	i_l_shipdate,i_l_receiptdate	i_l_shipdate,i_l_receiptdate	4,4	NULL	9	Using union(i_l_shipdate,i_l_receiptdate); Using where
+ flush status;
+ select l_orderkey, l_linenumber
+ from lineitem use index (i_l_shipdate, i_l_receiptdate)
+@@ -193,10 +193,10 @@
+ Handler_read_first	0
+ Handler_read_key	2
+ Handler_read_last	0
+-Handler_read_next	3
++Handler_read_next	9
+ Handler_read_prev	0
+ Handler_read_retry	0
+-Handler_read_rnd	3
++Handler_read_rnd	9
+ Handler_read_rnd_deleted	0
+ Handler_read_rnd_next	0
+ explain
+@@ -204,7 +204,7 @@
+ where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
+ or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	index_merge	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate,i_l_receiptdate	8,8	NULL	3	Using sort_union(i_l_shipdate,i_l_receiptdate); Using where
++1	SIMPLE	lineitem	index_merge	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate,PRIMARY,i_l_receiptdate,PRIMARY	4,4,4,4	NULL	2	Using union(intersect(i_l_shipdate,PRIMARY),intersect(i_l_receiptdate,PRIMARY)); Using where
+ flush status;
+ select l_orderkey, l_linenumber from lineitem 
+ where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
+@@ -218,7 +218,7 @@
+ Handler_read_first	0
+ Handler_read_key	2
+ Handler_read_last	0
+-Handler_read_next	3
++Handler_read_next	9
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	3
+@@ -228,7 +228,7 @@
+ select max(l_orderkey) from lineitem
+ where l_partkey between 1 and 10 group by l_partkey;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	range	i_l_suppkey_partkey,i_l_partkey	i_l_partkey	5	NULL	#	Using where; Using index for group-by
++1	SIMPLE	lineitem	range	i_l_suppkey_partkey,i_l_partkey	i_l_partkey	5	NULL	#	Using where; Using index
+ flush status;
+ select max(l_orderkey) from lineitem
+ where l_partkey between 1 and 10 group by l_partkey;
+@@ -246,9 +246,9 @@
+ show status like 'handler_read%';
+ Variable_name	Value
+ Handler_read_first	0
+-Handler_read_key	21
+-Handler_read_last	1
+-Handler_read_next	0
++Handler_read_key	1
++Handler_read_last	0
++Handler_read_next	294
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -258,7 +258,7 @@
+ select max(l_orderkey) from lineitem
+ where l_suppkey in (1,4) group by l_suppkey;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	range	i_l_suppkey	i_l_suppkey	5	NULL	#	Using where; Using index for group-by
++1	SIMPLE	lineitem	range	i_l_suppkey	i_l_suppkey	5	NULL	#	Using where; Using index
+ flush status;
+ select max(l_orderkey) from lineitem
+ where l_suppkey in (1,4) group by l_suppkey;
+@@ -268,9 +268,9 @@
+ show status like 'handler_read%';
+ Variable_name	Value
+ Handler_read_first	0
+-Handler_read_key	6
+-Handler_read_last	1
+-Handler_read_next	0
++Handler_read_key	2
++Handler_read_last	0
++Handler_read_next	1230
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -286,7 +286,7 @@
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+ 1	SIMPLE	part	range	i_p_retailprice	i_p_retailprice	9	NULL	#	Using where; Using index
+ 1	SIMPLE	orders	ref	PRIMARY,i_o_orderdate	i_o_orderdate	4	const	#	Using index
+-1	SIMPLE	lineitem	ref	i_l_partkey	i_l_partkey	9	dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey	#	Using index
++1	SIMPLE	lineitem	ref	i_l_partkey	i_l_partkey	5	dbt3_s001.part.p_partkey	#	Using where; Using index
+ flush status;
+ select o_orderkey, p_partkey
+ from part use index (i_p_retailprice),
+@@ -300,7 +300,7 @@
+ Handler_read_first	0
+ Handler_read_key	3
+ Handler_read_last	0
+-Handler_read_next	3
++Handler_read_next	26
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -317,8 +317,8 @@
+ select * from t0, part ignore index (primary)
+ where p_partkey=t0.a and p_size=1;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	5	Using where
+-1	SIMPLE	part	eq_ref	i_p_size	i_p_size	9	const,dbt3_s001.t0.a	1	
++1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	5	
++1	SIMPLE	part	ref	i_p_size	i_p_size	5	const	5	Using index condition
+ select * from t0,  part ignore index (primary)
+ where p_partkey=t0.a and p_size=1;
+ a	p_partkey	p_name	p_mfgr	p_brand	p_type	p_size	p_container	p_retailprice	p_comment
+@@ -495,7 +495,7 @@
+ select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a and t3.pk1=t1.a;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+ 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+-1	SIMPLE	t3	ref	PRIMARY,col1	col1	12	test.t1.a,test.t1.a,test.t1.a	#	Using index
++1	SIMPLE	t3	ref	PRIMARY,col1	col1	8	test.t1.a,test.t1.a	#	Using where; Using index
+ drop table t1,t2,t3;
+ #
+ # Bug mdev-4340: performance regression with extended_keys=on   
+@@ -705,13 +705,13 @@
+ select * from t1 force index(index_date_updated) 
+ where index_date_updated= 10 and index_id < 800;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	t1	range	index_date_updated	index_date_updated	13	NULL	#	Using index condition
++1	SIMPLE	t1	ref	index_date_updated	index_date_updated	5	const	#	Using index condition
+ # This used to work from the start:
+ explain 
+ select * from t2 force index(index_date_updated) 
+ where index_date_updated= 10 and index_id < 800;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	t2	range	index_date_updated	index_date_updated	13	NULL	#	Using index condition
++1	SIMPLE	t2	ref	index_date_updated	index_date_updated	5	const	#	Using index condition
+ drop table t0,t1,t2;
+ #
+ # MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff'
+@@ -746,11 +746,12 @@
+     "select_id": 1,
+     "table": {
+       "table_name": "t1",
+-      "access_type": "range",
++      "access_type": "ref",
+       "possible_keys": ["f2"],
+       "key": "f2",
+-      "key_length": "3070",
+-      "used_key_parts": ["f2", "pk1"],
++      "key_length": "3066",
++      "used_key_parts": ["f2"],
++      "ref": ["const"],
+       "rows": 1,
+       "filtered": 100,
+       "index_condition": "t1.pk1 <= 5 and t1.pk2 <= 5 and t1.f2 = 'abc'",
+@@ -779,8 +780,8 @@
+       "access_type": "range",
+       "possible_keys": ["k1"],
+       "key": "k1",
+-      "key_length": "3011",
+-      "used_key_parts": ["pk1", "f2", "pk2"],
++      "key_length": "3007",
++      "used_key_parts": ["pk1", "f2"],
+       "rows": 1,
+       "filtered": 100,
+       "index_condition": "t1.f2 <= 5 and t1.pk2 <= 5 and t1.pk1 = 'abc'",
diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result
index e02b78cad15..233b359157b 100644
--- a/mysql-test/r/innodb_ext_key.result
+++ b/mysql-test/r/innodb_ext_key.result
@@ -1,30 +1,6 @@
-DROP TABLE IF EXISTS t1,t2,t3,t4;
-DROP DATABASE IF EXISTS dbt3_s001;
 SET SESSION STORAGE_ENGINE='InnoDB';
 CREATE DATABASE dbt3_s001;
 use dbt3_s001;
-set @save_ext_key_optimizer_switch=@@optimizer_switch;
-set optimizer_switch='extended_keys=off';
-explain
-select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	const	5	Using where
-flush status;
-select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
-count(*)
-1
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	5
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -44,29 +20,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select count(*) from lineitem 
-where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	const	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	8	const,const	1	
-flush status;
-select count(*) from lineitem 
-where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
-count(*)
-1
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	0
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select count(*) from lineitem
 where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
@@ -88,29 +41,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain 
-select count(*) from lineitem
-where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	const	6	Using where; Using index
-flush status;
-select count(*) from lineitem
-where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
-count(*)
-1
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	6
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain 
 select count(*) from lineitem
 where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
@@ -132,31 +62,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select l_orderkey, l_linenumber from lineitem
-where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	const	6	Using where; Using index
-flush status;
-select l_orderkey, l_linenumber from lineitem
-where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-l_orderkey	l_linenumber
-1088	3
-1217	1
-1221	3
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	6
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select l_orderkey, l_linenumber from lineitem
 where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
@@ -180,27 +85,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	ref	i_l_shipdate	i_l_shipdate	4	const	6	Using index
-flush status;
-select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
-min(l_orderkey)
-130
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	6
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -220,29 +104,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select min(l_orderkey) from lineitem 
-where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	const	6	Using where; Using index
-flush status;
-select min(l_orderkey) from lineitem 
-where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-min(l_orderkey)
-1088
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	6
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select min(l_orderkey) from lineitem 
 where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
@@ -264,29 +125,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select max(l_linenumber) from lineitem 
-where l_shipdate='1992-07-01' and l_orderkey=130;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	const	5	Using where
-flush status;
-select max(l_linenumber) from lineitem 
-where l_shipdate='1992-07-01' and l_orderkey=130;
-max(l_linenumber)
-2
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	5
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select max(l_linenumber) from lineitem 
 where l_shipdate='1992-07-01' and l_orderkey=130;
@@ -308,34 +146,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select l_orderkey, l_linenumber 
-from lineitem use index (i_l_shipdate, i_l_receiptdate)
-where l_shipdate='1992-07-01' and l_orderkey=130 
-or l_receiptdate='1992-07-01' and l_orderkey=5603;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	index_merge	i_l_shipdate,i_l_receiptdate	i_l_shipdate,i_l_receiptdate	4,4	NULL	9	Using union(i_l_shipdate,i_l_receiptdate); Using where
-flush status;
-select l_orderkey, l_linenumber 
-from lineitem use index (i_l_shipdate, i_l_receiptdate)
-where l_shipdate='1992-07-01' and l_orderkey=130 
-or l_receiptdate='1992-07-01' and l_orderkey=5603;
-l_orderkey	l_linenumber
-130	2
-5603	2
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	2
-Handler_read_last	0
-Handler_read_next	9
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	9
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select l_orderkey, l_linenumber 
 from lineitem use index (i_l_shipdate, i_l_receiptdate)
@@ -362,35 +172,6 @@ Handler_read_retry	0
 Handler_read_rnd	2
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select l_orderkey, l_linenumber
-from lineitem use index (i_l_shipdate, i_l_receiptdate)
-where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	index_merge	i_l_shipdate,i_l_receiptdate	i_l_shipdate,i_l_receiptdate	4,4	NULL	9	Using union(i_l_shipdate,i_l_receiptdate); Using where
-flush status;
-select l_orderkey, l_linenumber
-from lineitem use index (i_l_shipdate, i_l_receiptdate)
-where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-l_orderkey	l_linenumber
-130	2
-5603	2
-5959	3
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	2
-Handler_read_last	0
-Handler_read_next	9
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	9
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select l_orderkey, l_linenumber
 from lineitem use index (i_l_shipdate, i_l_receiptdate)
@@ -418,33 +199,6 @@ Handler_read_retry	0
 Handler_read_rnd	3
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select l_orderkey, l_linenumber from lineitem 
-where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	index_merge	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate,PRIMARY,i_l_receiptdate,PRIMARY	4,4,4,4	NULL	2	Using union(intersect(i_l_shipdate,PRIMARY),intersect(i_l_receiptdate,PRIMARY)); Using where
-flush status;
-select l_orderkey, l_linenumber from lineitem 
-where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-l_orderkey	l_linenumber
-130	2
-5603	2
-5959	3
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	2
-Handler_read_last	0
-Handler_read_next	9
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	3
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select l_orderkey, l_linenumber from lineitem 
 where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
@@ -470,38 +224,6 @@ Handler_read_retry	0
 Handler_read_rnd	3
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain 
-select max(l_orderkey) from lineitem
-where l_partkey between 1 and 10 group by l_partkey;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	range	i_l_suppkey_partkey,i_l_partkey	i_l_partkey	5	NULL	#	Using where; Using index
-flush status;
-select max(l_orderkey) from lineitem
-where l_partkey between 1 and 10 group by l_partkey;
-max(l_orderkey)
-5984
-5957
-5892
-5856
-5959
-5957
-5794
-5894
-5859
-5632
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	294
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain 
 select max(l_orderkey) from lineitem
 where l_partkey between 1 and 10 group by l_partkey;
@@ -532,30 +254,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain 
-select max(l_orderkey) from lineitem
-where l_suppkey in (1,4) group by l_suppkey;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	range	i_l_suppkey	i_l_suppkey	5	NULL	#	Using where; Using index
-flush status;
-select max(l_orderkey) from lineitem
-where l_suppkey in (1,4) group by l_suppkey;
-max(l_orderkey)
-5988
-5984
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	2
-Handler_read_last	0
-Handler_read_next	1230
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain 
 select max(l_orderkey) from lineitem
 where l_suppkey in (1,4) group by l_suppkey;
@@ -579,37 +277,6 @@ Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 create index i_p_retailprice on part(p_retailprice);
-set optimizer_switch='extended_keys=off';
-explain
-select o_orderkey, p_partkey
-from part use index (i_p_retailprice),
-lineitem use index (i_l_partkey), orders
-where p_retailprice > 1100 and o_orderdate='1997-01-01' 
-and o_orderkey=l_orderkey and p_partkey=l_partkey;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	part	range	i_p_retailprice	i_p_retailprice	9	NULL	#	Using where; Using index
-1	SIMPLE	orders	ref	PRIMARY,i_o_orderdate	i_o_orderdate	4	const	#	Using index
-1	SIMPLE	lineitem	ref	i_l_partkey	i_l_partkey	5	dbt3_s001.part.p_partkey	#	Using where; Using index
-flush status;
-select o_orderkey, p_partkey
-from part use index (i_p_retailprice),
-lineitem use index (i_l_partkey), orders
-where p_retailprice > 1100 and o_orderdate='1997-01-01' 
-and o_orderkey=l_orderkey and p_partkey=l_partkey;
-o_orderkey	p_partkey
-5895	200
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	3
-Handler_read_last	0
-Handler_read_next	26
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select o_orderkey, p_partkey
 from part use index (i_p_retailprice),
@@ -646,7 +313,6 @@ Handler_read_rnd_next	0
 create table t0 (a int);
 insert into t0 values (1), (2), (3), (4), (5);
 create index i_p_size on part(p_size);
-set optimizer_switch='extended_keys=on';
 explain
 select * from t0, part ignore index (primary)
 where p_partkey=t0.a and p_size=1;
@@ -667,7 +333,6 @@ use test;
 #
 set @save_optimizer_switch=@@optimizer_switch;
 SET optimizer_switch='materialization=on,semijoin=on';
-SET optimizer_switch='extended_keys=on';
 CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (1,1), (2,2);
 SELECT * FROM t1 WHERE 2 IN (SELECT MAX(s1.a) FROM t1 AS s1, t1 AS s2);
@@ -688,9 +353,7 @@ set optimizer_switch=@save_optimizer_switch;
 #                 + extended_keys = on
 # (valgrinf complains fixed by the patch for bug #914560)
 #
-set @save_optimizer_switch=@@optimizer_switch;
 SET optimizer_switch = 'derived_with_keys=on';
-SET optimizer_switch = 'extended_keys=on';
 CREATE TABLE t1 (a varchar(1)) ENGINE=MyISAM;
 INSERT INTO t1 VALUES ('j'), ('v');
 CREATE TABLE t2 (b varchar(1)) ENGINE=MyISAM;
@@ -720,21 +383,6 @@ c int NOT NULL PRIMARY KEY
 INSERT INTO t2 VALUES
 (10), (11), (12), (13), (14), 
 (15), (16), (17), (18), (19), (24);
-set @save_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch = 'extended_keys=off';
-EXPLAIN
-SELECT a FROM t1 AS t, t2 
-WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t	index	a,b	b	7	NULL	10	Using index
-1	PRIMARY	t1	ref	b	b	3	test.t.b	2	Using index
-1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	11	Using index; FirstMatch(t)
-1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t.a	1	Using index
-SELECT a FROM t1 AS t, t2 
-WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
-a
-24
-SET optimizer_switch = 'extended_keys=on';
 EXPLAIN
 SELECT a FROM t1 AS t, t2 
 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
@@ -747,7 +395,6 @@ SELECT a FROM t1 AS t, t2
 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 a
 24
-set optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1,t2;
 #
 # LP Bug #923236: hash join + extended_keys = on 
@@ -756,12 +403,10 @@ CREATE TABLE t1 (a int) ENGINE=MyISAM;
 CREATE TABLE t2 (b int) ENGINE=MyISAM;
 INSERT INTO t1 (a) VALUES (4), (6);
 INSERT INTO t2 (b) VALUES (0), (8);
-set @save_optimizer_switch=@@optimizer_switch;
 set @save_join_cache_level=@@join_cache_level;
 SET join_cache_level=3;
 SET optimizer_switch='join_cache_hashed=on';
 SET optimizer_switch='join_cache_bka=on';
-SET optimizer_switch='extended_keys=on';
 EXPLAIN
 SELECT * FROM t1, t2 WHERE b=a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -786,26 +431,16 @@ UNIQUE KEY uq (c2,c3),
 KEY c3 (c3),
 KEY c4 (c4)
 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-set @save_optimizer_switch=@@optimizer_switch;
-set session optimizer_switch='extended_keys=off';
-INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
-ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
 INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
 ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
-DELETE FROM t1;
-set session optimizer_switch='extended_keys=on';
 INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
 ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
-INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
-ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
-set optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1;
 #
 # Bug mdev-4220: using ref instead of eq_ref
 #                with extended_keys=on   
 # (performance regression introduced in the patch for mdev-3851)
 #
-set @save_optimizer_switch=@@optimizer_switch;
 create table t1 (a int not null) engine=innodb;
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t2 (
@@ -815,29 +450,6 @@ insert into t2
 select 
 A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a 
 from t1 A, t1 B;
-set optimizer_switch='extended_keys=off';
-explain 
-select * from t1, t2 where t2.a=t1.a and t2.b < 2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
-1	SIMPLE	t2	eq_ref	a	a	4	test.t1.a	1	Using where
-flush status;
-select * from t1, t2 where t2.a=t1.a and t2.b < 2;
-a	pk	a	b
-0	0	0	0
-1	1	1	1
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	10
-Handler_read_last	0
-Handler_read_next	0
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	11
-set optimizer_switch='extended_keys=on';
 explain 
 select * from t1, t2 where t2.a=t1.a and t2.b < 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -874,18 +486,6 @@ analyze table t1,t3;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	OK
 test.t3	analyze	status	OK
-set optimizer_switch='extended_keys=off';
-explain 
-select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
-1	SIMPLE	t3	ref	col1	col1	8	test.t1.a,test.t1.a	#	Using index
-explain 
-select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a and t3.pk1=t1.a;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
-1	SIMPLE	t3	ref	PRIMARY,col1	col1	8	test.t1.a,test.t1.a	#	Using where; Using index
-set optimizer_switch='extended_keys=on';
 explain 
 select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -900,7 +500,6 @@ drop table t1,t2,t3;
 #
 # Bug mdev-4340: performance regression with extended_keys=on   
 #
-set @save_optimizer_switch=@@optimizer_switch;
 CREATE TABLE t1 (
 page_id int(8) unsigned NOT NULL AUTO_INCREMENT,
 page_namespace int(11) NOT NULL DEFAULT '0',
@@ -1000,16 +599,6 @@ INSERT INTO t3 VALUES
 (89,'text-8008',''),(90,'text-9008',''),(91,'text-9',''),(92,'text-1009',''),
 (93,'text-2009',''),(94,'text-3009',''),(95,'text-4009',''),(96,'text-5009',''),
 (97,'text-6009',''),(98,'text-7009',''),(99,'text-8009',''),(100,'text-9009','');
-set optimizer_switch='extended_keys=off';
-EXPLAIN 
-SELECT *  FROM t1, t2 IGNORE INDEX (PRIMARY), t3 
-WHERE page_id=rev_page AND  rev_text_id=old_id  AND page_namespace=4 AND page_title='Sandbox'
-ORDER BY rev_timestamp ASC LIMIT 10;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	const	PRIMARY,name_title	name_title	261	const,const	1	
-1	SIMPLE	t2	ref	page_timestamp	page_timestamp	4	const	10	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.rev_text_id	1	
-set optimizer_switch='extended_keys=on';
 EXPLAIN 
 SELECT *  FROM t1, t2 IGNORE INDEX (PRIMARY), t3 
 WHERE page_id=rev_page AND  rev_text_id=old_id  AND page_namespace=4 AND page_title='Sandbox'
@@ -1027,7 +616,6 @@ create table t1 (a bigint not null unique auto_increment, b varchar(10), primary
 create table t2 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = innodb default character set utf8;
 insert into t1 (b) values (null), (null), (null);
 insert into t2 (b) values (null), (null), (null);
-set optimizer_switch='extended_keys=on';
 explain select a from t1 where b is null order by a desc limit 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	b	PRIMARY	8	NULL	3	Using where
@@ -1042,14 +630,6 @@ select a from t2 where b is null order by a desc limit 2;
 a
 3
 2
-set optimizer_switch='extended_keys=off';
-explain select a from t2 where b is null order by a desc limit 2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	b	b	9	NULL	3	Using where; Using filesort
-select a from t2 where b is null order by a desc limit 2;
-a
-3
-2
 explain select a from t2 where b is null order by a desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	index	b	PRIMARY	8	NULL	3	Using where
@@ -1067,7 +647,6 @@ a
 2
 1
 drop table t1, t2;
-set optimizer_switch=@save_optimizer_switch;
 # 
 # MDEV-10325: Queries examines all rows of a tables when it should not
 # 
@@ -1213,7 +792,6 @@ EXPLAIN
   }
 }
 drop table t1;
-set optimizer_switch=@save_ext_key_optimizer_switch;
 set global innodb_file_format = @save_innodb_file_format;
 set global innodb_large_prefix = @save_innodb_large_prefix;
 SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/t/fast_prefix_index_fetch_innodb.test b/mysql-test/t/fast_prefix_index_fetch_innodb.test
index c3b3440d82d..7112f14bbd5 100644
--- a/mysql-test/t/fast_prefix_index_fetch_innodb.test
+++ b/mysql-test/t/fast_prefix_index_fetch_innodb.test
@@ -1,9 +1,6 @@
 -- source include/have_innodb.inc
 
---disable_warnings
-drop table if exists prefixinno;
---enable_warnings
-
+SET @save_opt= @@GLOBAL.innodb_prefix_index_cluster_optimization;
 set global innodb_prefix_index_cluster_optimization = ON;
 show variables like 'innodb_prefix_index_cluster_optimization';
 
@@ -665,4 +662,49 @@ select @cluster_lookups;
 select @cluster_lookups_avoided;
 
 DROP TABLE t1;
-set global innodb_prefix_index_cluster_optimization = OFF;
+
+--echo #
+--echo # MDEV-23600 Division by 0 in row_search_with_covering_prefix()
+--echo #
+CREATE TABLE t(c POINT UNIQUE) ENGINE=InnoDB;
+INSERT t SET c=POINT(1,1);
+SELECT * FROM t WHERE c > (SELECT MAX(c) FROM t);
+DROP TABLE t;
+
+--echo #
+--echo # MDEV-12486 Wrong results with innodb_prefix_index_cluster_optimization
+--echo #
+CREATE TABLE wp_blogs (
+  blog_id bigint(20) NOT NULL auto_increment,
+  site_id bigint(20) NOT NULL default '0',
+  domain varchar(200) NOT NULL default '',
+  path varchar(100) NOT NULL default '',
+  registered datetime NOT NULL default '0000-00-00 00:00:00',
+  last_updated datetime NOT NULL default '0000-00-00 00:00:00',
+  public tinyint(2) NOT NULL default '1',
+  archived tinyint(2) NOT NULL default '0',
+  mature tinyint(2) NOT NULL default '0',
+  spam tinyint(2) NOT NULL default '0',
+  deleted tinyint(2) NOT NULL default '0',
+  lang_id int(11) NOT NULL default '0',
+  PRIMARY KEY  (blog_id),
+  KEY domain (domain(50),path(5)),
+  KEY lang_id (lang_id)
+) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+INSERT INTO wp_blogs (domain, path) VALUES
+('domain.no', '/fondsinvesteringer/'), ('domain.no', '/'),
+('foo', 'bar'), ('bar', 'foo'), ('foo', 'foo'), ('bar', 'bar'),
+('foo', 'foobar'), ('bar', 'foobar'), ('foobar', 'foobar');
+
+SET GLOBAL innodb_prefix_index_cluster_optimization=off;
+SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no')
+AND path IN ( '/fondsinvesteringer/', '/' );
+
+SET GLOBAL innodb_prefix_index_cluster_optimization=on;
+SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no')
+AND path IN ( '/fondsinvesteringer/', '/' );
+
+DROP TABLE wp_blogs;
+
+SET GLOBAL innodb_prefix_index_cluster_optimization = @save_opt;
diff --git a/mysql-test/t/innodb_ext_key.combinations b/mysql-test/t/innodb_ext_key.combinations
new file mode 100644
index 00000000000..6ad97eaf75b
--- /dev/null
+++ b/mysql-test/t/innodb_ext_key.combinations
@@ -0,0 +1,5 @@
+[on]
+optimizer_switch=extended_keys=on
+
+[off]
+optimizer_switch=extended_keys=off
diff --git a/mysql-test/t/innodb_ext_key.test b/mysql-test/t/innodb_ext_key.test
index bec4462df08..c936e4b5170 100644
--- a/mysql-test/t/innodb_ext_key.test
+++ b/mysql-test/t/innodb_ext_key.test
@@ -1,9 +1,4 @@
---source include/have_innodb.inc
-
---disable_warnings
-DROP TABLE IF EXISTS t1,t2,t3,t4;
-DROP DATABASE IF EXISTS dbt3_s001;
---enable_warnings
+--source include/innodb_prefix_index_cluster_optimization.inc
 
 SET SESSION STORAGE_ENGINE='InnoDB';
 
@@ -19,32 +14,12 @@ use dbt3_s001;
 --enable_result_log
 --enable_query_log
 
-set @save_ext_key_optimizer_switch=@@optimizer_switch;
-
-set optimizer_switch='extended_keys=off';
 explain
 select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
 flush status;
 select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
-flush status;
-select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
-explain
-select count(*) from lineitem 
-  where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
-flush status;
-select count(*) from lineitem 
-  where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain
 select count(*) from lineitem
   where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
@@ -53,16 +28,6 @@ select count(*) from lineitem
   where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
-explain 
-select count(*) from lineitem
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
-flush status;
-select count(*) from lineitem
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain 
 select count(*) from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
@@ -71,16 +36,6 @@ select count(*) from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
-explain
-select l_orderkey, l_linenumber from lineitem
-  where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-flush status;
-select l_orderkey, l_linenumber from lineitem
-  where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain
 select l_orderkey, l_linenumber from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
@@ -89,21 +44,12 @@ select l_orderkey, l_linenumber from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
 explain
 select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
 flush status;
 select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
-flush status;
-select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
 explain
 select min(l_orderkey) from lineitem 
   where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
@@ -112,25 +58,6 @@ select min(l_orderkey) from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select min(l_orderkey) from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-flush status;
-select min(l_orderkey) from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
-explain
-select max(l_linenumber) from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey=130;
-flush status;
-select max(l_linenumber) from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey=130;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain
 select max(l_linenumber) from lineitem 
   where l_shipdate='1992-07-01' and l_orderkey=130;
@@ -139,7 +66,6 @@ select max(l_linenumber) from lineitem
   where l_shipdate='1992-07-01' and l_orderkey=130;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
 explain
 select l_orderkey, l_linenumber 
   from lineitem use index (i_l_shipdate, i_l_receiptdate)
@@ -152,20 +78,6 @@ select l_orderkey, l_linenumber
         or l_receiptdate='1992-07-01' and l_orderkey=5603;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select l_orderkey, l_linenumber 
-  from lineitem use index (i_l_shipdate, i_l_receiptdate)
-  where l_shipdate='1992-07-01' and l_orderkey=130 
-        or l_receiptdate='1992-07-01' and l_orderkey=5603;
-flush status;
-select l_orderkey, l_linenumber 
-  from lineitem use index (i_l_shipdate, i_l_receiptdate)
-  where l_shipdate='1992-07-01' and l_orderkey=130 
-        or l_receiptdate='1992-07-01' and l_orderkey=5603;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
 explain
 select l_orderkey, l_linenumber
   from lineitem use index (i_l_shipdate, i_l_receiptdate)
@@ -178,20 +90,6 @@ select l_orderkey, l_linenumber
          or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select l_orderkey, l_linenumber
-  from lineitem use index (i_l_shipdate, i_l_receiptdate)
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-         or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-flush status;
-select l_orderkey, l_linenumber
-  from lineitem use index (i_l_shipdate, i_l_receiptdate)
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-         or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
 explain
 select l_orderkey, l_linenumber from lineitem 
   where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
@@ -202,28 +100,6 @@ select l_orderkey, l_linenumber from lineitem
         or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select l_orderkey, l_linenumber from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-        or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-flush status;
-select l_orderkey, l_linenumber from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-        or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
---replace_column 9 #
-explain 
-select max(l_orderkey) from lineitem
-  where l_partkey between 1 and 10 group by l_partkey;
-flush status;
-select max(l_orderkey) from lineitem
-  where l_partkey between 1 and 10 group by l_partkey;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 --replace_column 9 #
 explain 
 select max(l_orderkey) from lineitem
@@ -233,17 +109,6 @@ select max(l_orderkey) from lineitem
   where l_partkey between 1 and 10 group by l_partkey;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
---replace_column 9 #
-explain 
-select max(l_orderkey) from lineitem
-  where l_suppkey in (1,4) group by l_suppkey;
-flush status;
-select max(l_orderkey) from lineitem
-  where l_suppkey in (1,4) group by l_suppkey;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 --replace_column 9 #
 explain 
 select max(l_orderkey) from lineitem
@@ -255,23 +120,6 @@ show status like 'handler_read%';
 
 create index i_p_retailprice on part(p_retailprice);
 
-set optimizer_switch='extended_keys=off';
---replace_column 9 #
-explain
-select o_orderkey, p_partkey
-  from part use index (i_p_retailprice),
-       lineitem use index (i_l_partkey), orders
-  where p_retailprice > 1100 and o_orderdate='1997-01-01' 
-        and o_orderkey=l_orderkey and p_partkey=l_partkey;
-flush status;
-select o_orderkey, p_partkey
-  from part use index (i_p_retailprice),
-       lineitem use index (i_l_partkey), orders
-  where p_retailprice > 1100 and o_orderdate='1997-01-01' 
-        and o_orderkey=l_orderkey and p_partkey=l_partkey;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 --replace_column 9 #
 explain
 select o_orderkey, p_partkey
@@ -296,8 +144,6 @@ create table t0 (a int);
 insert into t0 values (1), (2), (3), (4), (5);
 create index i_p_size on part(p_size);
 
-set optimizer_switch='extended_keys=on';
-
 explain
 select * from t0, part ignore index (primary)
   where p_partkey=t0.a and p_size=1;
@@ -320,7 +166,6 @@ use test;
 set @save_optimizer_switch=@@optimizer_switch;
 
 SET optimizer_switch='materialization=on,semijoin=on';
-SET optimizer_switch='extended_keys=on';
 
 CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (1,1), (2,2);
@@ -339,10 +184,7 @@ set optimizer_switch=@save_optimizer_switch;
 --echo # (valgrinf complains fixed by the patch for bug #914560)
 --echo #
 
-set @save_optimizer_switch=@@optimizer_switch;
-
 SET optimizer_switch = 'derived_with_keys=on';
-SET optimizer_switch = 'extended_keys=on';
 
 CREATE TABLE t1 (a varchar(1)) ENGINE=MyISAM;
 INSERT INTO t1 VALUES ('j'), ('v');
@@ -382,24 +224,12 @@ INSERT INTO t2 VALUES
   (10), (11), (12), (13), (14), 
   (15), (16), (17), (18), (19), (24);
 
-set @save_optimizer_switch=@@optimizer_switch;
-
-SET optimizer_switch = 'extended_keys=off';
-EXPLAIN
-SELECT a FROM t1 AS t, t2 
-  WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
-SELECT a FROM t1 AS t, t2 
-  WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
-
-SET optimizer_switch = 'extended_keys=on';
 EXPLAIN
 SELECT a FROM t1 AS t, t2 
   WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 SELECT a FROM t1 AS t, t2 
   WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 
-set optimizer_switch=@save_optimizer_switch;
-
 DROP TABLE t1,t2;
 
 --echo #
@@ -413,13 +243,11 @@ CREATE TABLE t2 (b int) ENGINE=MyISAM;
 INSERT INTO t1 (a) VALUES (4), (6);
 INSERT INTO t2 (b) VALUES (0), (8);
 
-set @save_optimizer_switch=@@optimizer_switch;
 set @save_join_cache_level=@@join_cache_level;
 
 SET join_cache_level=3;
 SET optimizer_switch='join_cache_hashed=on';
 SET optimizer_switch='join_cache_bka=on';
-SET optimizer_switch='extended_keys=on';
 
 EXPLAIN
 SELECT * FROM t1, t2 WHERE b=a;
@@ -448,24 +276,11 @@ KEY c4 (c4)
 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
 
 
-set @save_optimizer_switch=@@optimizer_switch;
-
-set session optimizer_switch='extended_keys=off';
-INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
-  ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
-INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
-  ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
-
-DELETE FROM t1;
-
-set session optimizer_switch='extended_keys=on';
 INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
   ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
 INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
   ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
 
-set optimizer_switch=@save_optimizer_switch;
-
 DROP TABLE t1;
 
 --echo #
@@ -474,8 +289,6 @@ DROP TABLE t1;
 --echo # (performance regression introduced in the patch for mdev-3851)
 --echo #
 
-set @save_optimizer_switch=@@optimizer_switch;
-
 create table t1 (a int not null) engine=innodb;
 
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -489,14 +302,6 @@ select
   A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a 
 from t1 A, t1 B;
 
-set optimizer_switch='extended_keys=off';
-explain 
-select * from t1, t2 where t2.a=t1.a and t2.b < 2;
-flush status;
-select * from t1, t2 where t2.a=t1.a and t2.b < 2;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain 
 select * from t1, t2 where t2.a=t1.a and t2.b < 2;
 flush status;
@@ -522,15 +327,6 @@ alter table t3 add primary key (pk1, pk2);
 alter table t3 add  key (col1, col2);
 analyze table t1,t3;
 
-set optimizer_switch='extended_keys=off';
---replace_column 9 #
-explain 
-select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a;
---replace_column 9 #
-explain 
-select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a and t3.pk1=t1.a;
-
-set optimizer_switch='extended_keys=on';
 --replace_column 9 #
 explain 
 select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a;
@@ -544,8 +340,6 @@ drop table t1,t2,t3;
 --echo # Bug mdev-4340: performance regression with extended_keys=on   
 --echo #
 
-set @save_optimizer_switch=@@optimizer_switch;
-
 CREATE TABLE t1 (
   page_id int(8) unsigned NOT NULL AUTO_INCREMENT,
   page_namespace int(11) NOT NULL DEFAULT '0',
@@ -649,13 +443,6 @@ INSERT INTO t3 VALUES
 (97,'text-6009',''),(98,'text-7009',''),(99,'text-8009',''),(100,'text-9009','');
 
 
-set optimizer_switch='extended_keys=off';
-EXPLAIN 
-SELECT *  FROM t1, t2 IGNORE INDEX (PRIMARY), t3 
-  WHERE page_id=rev_page AND  rev_text_id=old_id  AND page_namespace=4 AND page_title='Sandbox'
-ORDER BY rev_timestamp ASC LIMIT 10;
-
-set optimizer_switch='extended_keys=on';
 EXPLAIN 
 SELECT *  FROM t1, t2 IGNORE INDEX (PRIMARY), t3 
   WHERE page_id=rev_page AND  rev_text_id=old_id  AND page_namespace=4 AND page_title='Sandbox'
@@ -674,14 +461,10 @@ create table t2 (a bigint not null unique auto_increment, b varchar(10), primary
 insert into t1 (b) values (null), (null), (null);
 insert into t2 (b) values (null), (null), (null);
 
-set optimizer_switch='extended_keys=on';
 explain select a from t1 where b is null order by a desc limit 2;
 select a from t1 where b is null order by a desc limit 2;
 explain select a from t2 where b is null order by a desc limit 2;
 select a from t2 where b is null order by a desc limit 2;
-set optimizer_switch='extended_keys=off';
-explain select a from t2 where b is null order by a desc limit 2;
-select a from t2 where b is null order by a desc limit 2;
 
 explain select a from t2 where b is null order by a desc;
 select a from t2 where b is null order by a desc;
@@ -691,8 +474,6 @@ select a from t2 where b is null order by a desc,a,a;
 
 drop table t1, t2;
 
-set optimizer_switch=@save_optimizer_switch;
-
 --echo # 
 --echo # MDEV-10325: Queries examines all rows of a tables when it should not
 --echo # 
@@ -828,7 +609,6 @@ explain format= json
 select * from t1 force index(k1)  where f2 <= 5 and pk2 <=5 and pk1 = 'abc' and f1 <= '3';
 drop table t1;
 
-set optimizer_switch=@save_ext_key_optimizer_switch;
 set global innodb_file_format = @save_innodb_file_format;
 set global innodb_large_prefix = @save_innodb_large_prefix;
 SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc
index 936b0eccfb7..f45702157c8 100644
--- a/storage/innobase/row/row0sel.cc
+++ b/storage/innobase/row/row0sel.cc
@@ -2,7 +2,7 @@
 
 Copyright (c) 1997, 2017, Oracle and/or its affiliates. All Rights Reserved.
 Copyright (c) 2008, Google Inc.
-Copyright (c) 2015, 2018, MariaDB Corporation.
+Copyright (c) 2015, 2020, MariaDB Corporation.
 
 Portions of this file contain modifications contributed and copyrighted by
 Google, Inc. Those modifications are gratefully acknowledged and are described
@@ -3723,9 +3723,16 @@ bool row_search_with_covering_prefix(
 		return false;
 	}
 
+	/* We can avoid a clustered index lookup if
+	all of the following hold:
+	(1) all columns are in the secondary index
+	(2) all values for columns that are prefix-only
+	indexes are shorter than the prefix size
+	This optimization can avoid many IOs for certain schemas. */
 	for (ulint i = 0; i < prebuilt->n_template; i++) {
 		mysql_row_templ_t* templ = prebuilt->mysql_template + i;
 		ulint j = templ->rec_prefix_field_no;
+		ut_ad(!templ->mbminlen == !templ->mbmaxlen);
 
 		/** Condition (1) : is the field in the index. */
 		if (j == ULINT_UNDEFINED) {
@@ -3735,33 +3742,29 @@ bool row_search_with_covering_prefix(
 		/** Condition (2): If this is a prefix index then
 		row's value size shorter than prefix length. */
 
-		if (!templ->rec_field_is_prefix) {
+		if (!templ->rec_field_is_prefix
+		    || rec_offs_nth_sql_null(offsets, j)) {
 			continue;
 		}
 
-		ulint rec_size = rec_offs_nth_size(offsets, j);
 		const dict_field_t* field = dict_index_get_nth_field(index, j);
-		ulint max_chars = field->prefix_len / templ->mbmaxlen;
 
-		ut_a(field->prefix_len > 0);
-
-		if (rec_size < max_chars) {
-			/* Record in bytes shorter than the index
-			prefix length in char. */
+		if (!field->prefix_len) {
 			continue;
 		}
 
-		if (rec_size * templ->mbminlen >= field->prefix_len) {
+		const ulint rec_size = rec_offs_nth_size(offsets, j);
+
+		if (rec_size >= field->prefix_len) {
 			/* Shortest representation string by the
 			byte length of the record is longer than the
 			maximum possible index prefix. */
 			return false;
 		}
 
-		size_t num_chars = rec_field_len_in_chars(
-			field->col, j, rec, offsets);
-
-		if (num_chars >= max_chars) {
+		if (templ->mbminlen != templ->mbmaxlen
+		    && rec_field_len_in_chars(field->col, j, rec, offsets)
+		    >= field->prefix_len / templ->mbmaxlen) {
 			/* No of chars to store the record exceeds
 			the index prefix character length. */
 			return false;
diff --git a/storage/xtradb/row/row0sel.cc b/storage/xtradb/row/row0sel.cc
index c9c7f1dd9c1..a968fc83243 100644
--- a/storage/xtradb/row/row0sel.cc
+++ b/storage/xtradb/row/row0sel.cc
@@ -2,7 +2,7 @@
 
 Copyright (c) 1997, 2017, Oracle and/or its affiliates. All Rights Reserved.
 Copyright (c) 2008, Google Inc.
-Copyright (c) 2015, 2018, MariaDB Corporation.
+Copyright (c) 2015, 2020, MariaDB Corporation.
 
 Portions of this file contain modifications contributed and copyrighted by
 Google, Inc. Those modifications are gratefully acknowledged and are described
@@ -3740,9 +3740,16 @@ bool row_search_with_covering_prefix(
 		return false;
 	}
 
+	/* We can avoid a clustered index lookup if
+	all of the following hold:
+	(1) all columns are in the secondary index
+	(2) all values for columns that are prefix-only
+	indexes are shorter than the prefix size
+	This optimization can avoid many IOs for certain schemas. */
 	for (ulint i = 0; i < prebuilt->n_template; i++) {
 		mysql_row_templ_t* templ = prebuilt->mysql_template + i;
 		ulint j = templ->rec_prefix_field_no;
+		ut_ad(!templ->mbminlen == !templ->mbmaxlen);
 
 		/** Condition (1) : is the field in the index. */
 		if (j == ULINT_UNDEFINED) {
@@ -3752,34 +3759,29 @@ bool row_search_with_covering_prefix(
 		/** Condition (2): If this is a prefix index then
 		row's value size shorter than prefix length. */
 
-		if (!templ->rec_field_is_prefix) {
+		if (!templ->rec_field_is_prefix
+		    || rec_offs_nth_sql_null(offsets, j)) {
 			continue;
 		}
 
-		ulint rec_size = rec_offs_nth_size(offsets, j);
 		const dict_field_t* field = dict_index_get_nth_field(index, j);
-		ulint max_chars = field->prefix_len / templ->mbmaxlen;
 
-		ut_a(field->prefix_len > 0);
-
-		if (rec_size < max_chars) {
-			/* Record in bytes shorter than the index
-			prefix length in char. */
+		if (!field->prefix_len) {
 			continue;
 		}
 
-		if (rec_size * templ->mbminlen >= field->prefix_len) {
+		const ulint rec_size = rec_offs_nth_size(offsets, j);
+
+		if (rec_size >= field->prefix_len) {
 			/* Shortest representation string by the
 			byte length of the record is longer than the
 			maximum possible index prefix. */
 			return false;
 		}
 
-
-		size_t num_chars = rec_field_len_in_chars(
-			field->col, j, rec, offsets);
-
-		if (num_chars >= max_chars) {
+		if (templ->mbminlen != templ->mbmaxlen
+		    && rec_field_len_in_chars(field->col, j, rec, offsets)
+		    >= field->prefix_len / templ->mbmaxlen) {
 			/* No of chars to store the record exceeds
 			the index prefix character length. */
 			return false;
-- 
cgit v1.2.1