MDEV-12255 innodb_prefix_index_cluster_optimization hits debug build

		assert on UTF-8 columns

Problem:
=======
(1) Multi-byte character cases are not considered during prefix index
cluster optimization check. It leads to fetch of improper results during
read operation.
(2) Strict assert in row_sel_field_store_in_mysql_format_func and it asserts
for prefix index record to mysql conversion.

Solution:
========
(1) Consider the case of multi-byte character during prefix index
cluster optimization check.
(2) Relax the assert in row_sel_field_store_in_mysql_format_func to allow
prefix index record to mysql format conversion.

The patch is taken from
https://github.com/laurynas-biveinis/percona-server/commit/1eee538087ffcf121c37f844b447ba5480faf081

2 files changed, 921 insertions, 104 deletions

diff --git a/mysql-test/r/fast_prefix_index_fetch_innodb.result b/mysql-test/r/fast_prefix_index_fetch_innodb.result
index 92af85f7fdb..c6d96389b08 100644
--- a/mysql-test/r/fast_prefix_index_fetch_innodb.result
+++ b/mysql-test/r/fast_prefix_index_fetch_innodb.result
@@ -30,73 +30,372 @@ id	fake_id	bigfield
 33	1033	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
 128	1128	zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
 # Baseline sanity check: 0, 0.
+select "no-op query";
 no-op query
 no-op query
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
-1
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
 # Eligible for optimization.
+select id, bigfield from prefixinno where bigfield = repeat('d', 31);
 id	bigfield
 31	ddddddddddddddddddddddddddddddd
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
 1
 # Eligible for optimization, access via fake_id only.
+select id, bigfield from prefixinno where fake_id = 1031;
 id	bigfield
 31	ddddddddddddddddddddddddddddddd
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
 1
 # Not eligible for optimization, access via fake_id of big row.
+select id, bigfield from prefixinno where fake_id = 1033;
 id	bigfield
 33	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
 1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
 # Not eligible for optimization.
+select id, bigfield from prefixinno where bigfield = repeat('x', 32);
 id	bigfield
 32	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
 1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
 # Not eligible for optimization.
+select id, bigfield from prefixinno where bigfield = repeat('y', 33);
 id	bigfield
 33	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
 1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
 # Eligible, should not increment lookup counter.
+select id, bigfield from prefixinno where bigfield = repeat('b', 8);
 id	bigfield
 8	bbbbbbbb
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
 1
 # Eligible, should not increment lookup counter.
+select id, bigfield from prefixinno where bigfield = repeat('c', 24);
 id	bigfield
 24	cccccccccccccccccccccccc
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
 1
 # Should increment lookup counter.
+select id, bigfield from prefixinno where bigfield = repeat('z', 128);
 id	bigfield
 128	zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
 1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
 # Disable optimization, confirm we still increment counter.
+set global innodb_prefix_index_cluster_optimization = OFF;
+select id, bigfield from prefixinno where fake_id = 1033;
 id	bigfield
 33	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
-cluster_lookups_matched
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+drop table prefixinno;
+# Multi-byte handling case
+set global innodb_prefix_index_cluster_optimization = ON;
+SET NAMES utf8mb4;
+CREATE TABLE t1(
+f1 varchar(10) CHARACTER SET UTF8MB4 COLLATE UTF8MB4_BIN,
+INDEX (f1(3)))ENGINE=INNODB;
+INSERT INTO t1 VALUES('a'), ('cccc'), ('až'), ('cčc'), ('ggᵷg'), ('¢¢');
+INSERT INTO t1 VALUES('தமிழ்'), ('🐱🌑'), ('🌒'), ('🌑');
+INSERT INTO t1 VALUES('😊me'), ('eu€'), ('ls¢');
+# Eligible - record length is shorter than prefix
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'a';
+f1
+a
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%';
+f1
+cccc
+cčc
+select @cluster_lookups;
+@cluster_lookups
+3
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až';
+f1
+až
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்';
+f1
+தமிழ்
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%';
+f1
+ggᵷg
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '😊%';
+f1
+😊me
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢';
+f1
+ls¢
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '¢¢%';
+f1
+¢¢
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🐱🌑%';
+f1
+🐱🌑
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%';
+f1
+🌑
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+2
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌒%';
+f1
+🌒
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+2
+DROP TABLE t1;
+# Multi-byte with minimum character length > 1 bytes
+CREATE TABLE t1(
+f1 varchar(10) CHARACTER SET UTF16 COLLATE UTF16_BIN,
+INDEX (f1(3)))ENGINE=INNODB;
+INSERT INTO t1 VALUES('a'), ('cccc'), ('až'), ('cčc'), ('ggᵷg'), ('¢¢');
+INSERT INTO t1 VALUES('தமிழ்'), ('🐱🌑'), ('🌒'), ('🌑');
+INSERT INTO t1 VALUES('😊me'), ('eu€'), ('ls¢');
+# Eligible - record length is shorter than prefix
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'a';
+f1
+a
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%';
+f1
+cccc
+cčc
+select @cluster_lookups;
+@cluster_lookups
+3
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až';
+f1
+až
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்';
+f1
+தமிழ்
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%';
+f1
+ggᵷg
+select @cluster_lookups;
+@cluster_lookups
+2
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '😊%';
+f1
+😊me
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢';
+f1
+ls¢
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX(`f1`) WHERE f1 like '¢¢%';
+f1
+¢¢
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🐱🌑%';
+f1
+🐱🌑
+select @cluster_lookups;
+@cluster_lookups
+2
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Eligible - record length is shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%';
+f1
+🌑
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+2
+# Eligible - record length is shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌒%';
+f1
+🌒
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+DROP TABLE t1;
+CREATE TABLE t1(
+col1 INT,
+col2 BLOB DEFAULT NULL,
+INDEX `idx1`(col2(4), col1))ENGINE=INNODB;
+INSERT INTO t1 VALUES (2, 'test'), (3, repeat('test1', 2000));
+INSERT INTO t1(col1) VALUES(1);
+# Eligible - record length is shorter than prefix length
+SELECT col1 FROM t1 FORCE INDEX (`idx1`) WHERE col2 is NULL;
+col1
 1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
 1
-# make test suite happy by cleaning up our mess
+# Not eligible - record length longer than prefix index
+SELECT col1 FROM t1 FORCE INDEX (`idx1`) WHERE col2 like 'test1%';
+col1
+3
+select @cluster_lookups;
+@cluster_lookups
+2
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+DROP TABLE t1;
+set global innodb_prefix_index_cluster_optimization = OFF;
diff --git a/mysql-test/t/fast_prefix_index_fetch_innodb.test b/mysql-test/t/fast_prefix_index_fetch_innodb.test
index e563e65ec2a..c3b3440d82d 100644
--- a/mysql-test/t/fast_prefix_index_fetch_innodb.test
+++ b/mysql-test/t/fast_prefix_index_fetch_innodb.test
@@ -31,120 +31,638 @@ select * from prefixinno;
 let $show_count_statement = show status like 'innodb_secondary_index_triggered_cluster_reads';
 let $show_opt_statement = show status like 'innodb_secondary_index_triggered_cluster_reads_avoided';
 
---disable_query_log
-
 --echo # Baseline sanity check: 0, 0.
---let $base_count = query_get_value($show_count_statement, Value, 1)
---let $base_opt = query_get_value($show_opt_statement, Value, 1)
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
 select "no-op query";
---let $count = query_get_value($show_count_statement, Value, 1)
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
 eval select $count - $base_count into @cluster_lookups;
-select @cluster_lookups = 0 as cluster_lookups_matched;
---let $opt = query_get_value($show_opt_statement, Value, 1)
-eval select $opt - $base_opt into @cluster_lookups;
-select @cluster_lookups = 0 as cluster_lookups_avoided_matched;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
 
 --echo # Eligible for optimization.
---let $base_count = query_get_value($show_count_statement, Value, 1)
---let $base_opt = query_get_value($show_opt_statement, Value, 1)
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
 select id, bigfield from prefixinno where bigfield = repeat('d', 31);
---let $count = query_get_value($show_count_statement, Value, 1)
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
 eval select $count - $base_count into @cluster_lookups;
-select @cluster_lookups = 0 as cluster_lookups_matched;
---let $opt = query_get_value($show_opt_statement, Value, 1)
-eval select $opt - $base_opt into @cluster_lookups;
-select @cluster_lookups = 1 as cluster_lookups_avoided_matched;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
 
 --echo # Eligible for optimization, access via fake_id only.
---let $base_count = query_get_value($show_count_statement, Value, 1)
---let $base_opt = query_get_value($show_opt_statement, Value, 1)
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
 select id, bigfield from prefixinno where fake_id = 1031;
---let $count = query_get_value($show_count_statement, Value, 1)
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
 eval select $count - $base_count into @cluster_lookups;
-select @cluster_lookups = 0 as cluster_lookups_matched;
---let $opt = query_get_value($show_opt_statement, Value, 1)
-eval select $opt - $base_opt into @cluster_lookups;
-select @cluster_lookups = 1 as cluster_lookups_avoided_matched;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
 
 --echo # Not eligible for optimization, access via fake_id of big row.
---let $base_count = query_get_value($show_count_statement, Value, 1)
---let $base_opt = query_get_value($show_opt_statement, Value, 1)
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
 select id, bigfield from prefixinno where fake_id = 1033;
---let $count = query_get_value($show_count_statement, Value, 1)
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
 eval select $count - $base_count into @cluster_lookups;
-select @cluster_lookups = 1 as cluster_lookups_matched;
---let $opt = query_get_value($show_opt_statement, Value, 1)
-eval select $opt - $base_opt into @cluster_lookups;
-select @cluster_lookups = 0 as cluster_lookups_avoided_matched;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
 
 --echo # Not eligible for optimization.
---let $base_count = query_get_value($show_count_statement, Value, 1)
---let $base_opt = query_get_value($show_opt_statement, Value, 1)
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
 select id, bigfield from prefixinno where bigfield = repeat('x', 32);
---let $count = query_get_value($show_count_statement, Value, 1)
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
 eval select $count - $base_count into @cluster_lookups;
-select @cluster_lookups = 1 as cluster_lookups_matched;
---let $opt = query_get_value($show_opt_statement, Value, 1)
-eval select $opt - $base_opt into @cluster_lookups;
-select @cluster_lookups = 0 as cluster_lookups_avoided_matched;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
 
 --echo # Not eligible for optimization.
---let $base_count = query_get_value($show_count_statement, Value, 1)
---let $base_opt = query_get_value($show_opt_statement, Value, 1)
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
 select id, bigfield from prefixinno where bigfield = repeat('y', 33);
---let $count = query_get_value($show_count_statement, Value, 1)
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
 eval select $count - $base_count into @cluster_lookups;
-select @cluster_lookups = 1 as cluster_lookups_matched;
---let $opt = query_get_value($show_opt_statement, Value, 1)
-eval select $opt - $base_opt into @cluster_lookups;
-select @cluster_lookups = 0 as cluster_lookups_avoided_matched;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
 
 --echo # Eligible, should not increment lookup counter.
---let $base_count = query_get_value($show_count_statement, Value, 1)
---let $base_opt = query_get_value($show_opt_statement, Value, 1)
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
 select id, bigfield from prefixinno where bigfield = repeat('b', 8);
---let $count = query_get_value($show_count_statement, Value, 1)
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
 eval select $count - $base_count into @cluster_lookups;
-select @cluster_lookups = 0 as cluster_lookups_matched;
---let $opt = query_get_value($show_opt_statement, Value, 1)
-eval select $opt - $base_opt into @cluster_lookups;
-select @cluster_lookups = 1 as cluster_lookups_avoided_matched;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
 
 --echo # Eligible, should not increment lookup counter.
---let $base_count = query_get_value($show_count_statement, Value, 1)
---let $base_opt = query_get_value($show_opt_statement, Value, 1)
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
 select id, bigfield from prefixinno where bigfield = repeat('c', 24);
---let $count = query_get_value($show_count_statement, Value, 1)
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
 eval select $count - $base_count into @cluster_lookups;
-select @cluster_lookups = 0 as cluster_lookups_matched;
---let $opt = query_get_value($show_opt_statement, Value, 1)
-eval select $opt - $base_opt into @cluster_lookups;
-select @cluster_lookups = 1 as cluster_lookups_avoided_matched;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
 
 --echo # Should increment lookup counter.
---let $base_count = query_get_value($show_count_statement, Value, 1)
---let $base_opt = query_get_value($show_opt_statement, Value, 1)
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
 select id, bigfield from prefixinno where bigfield = repeat('z', 128);
---let $count = query_get_value($show_count_statement, Value, 1)
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
 eval select $count - $base_count into @cluster_lookups;
-select @cluster_lookups = 1 as cluster_lookups_matched;
---let $opt = query_get_value($show_opt_statement, Value, 1)
-eval select $opt - $base_opt into @cluster_lookups;
-select @cluster_lookups = 0 as cluster_lookups_avoided_matched;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
 
 --echo # Disable optimization, confirm we still increment counter.
---let $base_count = query_get_value($show_count_statement, Value, 1)
---let $base_opt = query_get_value($show_opt_statement, Value, 1)
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
 set global innodb_prefix_index_cluster_optimization = OFF;
 select id, bigfield from prefixinno where fake_id = 1033;
---let $count = query_get_value($show_count_statement, Value, 1)
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
 eval select $count - $base_count into @cluster_lookups;
-select @cluster_lookups = 1 as cluster_lookups_matched;
---let $opt = query_get_value($show_opt_statement, Value, 1)
-eval select $opt - $base_opt into @cluster_lookups;
-select @cluster_lookups = 0 as cluster_lookups_avoided_matched;
+eval select $opt - $base_opt into @cluster_lookups_avoided;
+--enable_query_log
 
+select @cluster_lookups;
+select @cluster_lookups_avoided;
 
---echo # make test suite happy by cleaning up our mess
 drop table prefixinno;
+
+--echo # Multi-byte handling case
+
+set global innodb_prefix_index_cluster_optimization = ON;
+SET NAMES utf8mb4;
+CREATE TABLE t1(
+	f1 varchar(10) CHARACTER SET UTF8MB4 COLLATE UTF8MB4_BIN,
+	INDEX (f1(3)))ENGINE=INNODB;
+
+INSERT INTO t1 VALUES('a'), ('cccc'), ('až'), ('cčc'), ('ggᵷg'), ('¢¢');
+INSERT INTO t1 VALUES('தமிழ்'), ('🐱🌑'), ('🌒'), ('🌑');
+INSERT INTO t1 VALUES('😊me'), ('eu€'), ('ls¢');
+
+--echo # Eligible - record length is shorter than prefix
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'a';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length shorter than prefix length
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '😊%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '¢¢%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🐱🌑%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌒%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+DROP TABLE t1;
+
+--echo # Multi-byte with minimum character length > 1 bytes
+
+CREATE TABLE t1(
+	f1 varchar(10) CHARACTER SET UTF16 COLLATE UTF16_BIN,
+	INDEX (f1(3)))ENGINE=INNODB;
+
+INSERT INTO t1 VALUES('a'), ('cccc'), ('až'), ('cčc'), ('ggᵷg'), ('¢¢');
+INSERT INTO t1 VALUES('தமிழ்'), ('🐱🌑'), ('🌒'), ('🌑');
+INSERT INTO t1 VALUES('😊me'), ('eu€'), ('ls¢');
+
+--echo # Eligible - record length is shorter than prefix
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'a';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '😊%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX(`f1`) WHERE f1 like '¢¢%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🐱🌑%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length is shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Eligible - record length is shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌒%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+DROP TABLE t1;
+
+CREATE TABLE t1(
+	col1 INT,
+	col2 BLOB DEFAULT NULL,
+	INDEX `idx1`(col2(4), col1))ENGINE=INNODB;
+INSERT INTO t1 VALUES (2, 'test'), (3, repeat('test1', 2000));
+INSERT INTO t1(col1) VALUES(1);
+
+--echo # Eligible - record length is shorter than prefix length
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT col1 FROM t1 FORCE INDEX (`idx1`) WHERE col2 is NULL;
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+--echo # Not eligible - record length longer than prefix index
+
+let $base_count = query_get_value($show_count_statement, Value, 1);
+let $base_opt = query_get_value($show_opt_statement, Value, 1);
+
+SELECT col1 FROM t1 FORCE INDEX (`idx1`) WHERE col2 like 'test1%';
+
+let $count = query_get_value($show_count_statement, Value, 1);
+let $opt = query_get_value($show_opt_statement, Value, 1);
+
+--disable_query_log
+eval set @cluster_lookups = $count - $base_count;
+eval set @cluster_lookups_avoided = $opt - $base_opt;
+--enable_query_log
+
+select @cluster_lookups;
+select @cluster_lookups_avoided;
+
+DROP TABLE t1;
 set global innodb_prefix_index_cluster_optimization = OFF;