Adding upstream version 1:10.11.6.upstream/1%10.11.6

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 232 insertions, 0 deletions

diff --git a/mysql-test/main/fast_prefix_index_fetch_innodb.result b/mysql-test/main/fast_prefix_index_fetch_innodb.result
new file mode 100644
index 00000000..1ce29100
--- /dev/null
+++ b/mysql-test/main/fast_prefix_index_fetch_innodb.result
@@ -0,0 +1,232 @@
+SET @save_innodb_stats_persistent=@@GLOBAL.innodb_stats_persistent;
+SET GLOBAL innodb_stats_persistent = OFF;
+# Create a table with a large varchar field that we index the prefix
+# of and ensure we only trigger cluster lookups when we expect it.
+create table prefixinno (
+id int not null,
+fake_id int not null,
+bigfield varchar(4096),
+primary key(id),
+index bigfield_idx (bigfield(32)),
+index fake_id_bigfield_prefix (fake_id, bigfield(32))
+) engine=innodb;
+insert into prefixinno values (1, 1001, repeat('a', 1)),
+(8, 1008, repeat('b', 8)),
+(24, 1024, repeat('c', 24)),
+(31, 1031, repeat('d', 31)),
+(32, 1032, repeat('x', 32)),
+(33, 1033, repeat('y', 33)),
+(128, 1128, repeat('z', 128));
+select * from prefixinno;
+id	fake_id	bigfield
+1	1001	a
+8	1008	bbbbbbbb
+24	1024	cccccccccccccccccccccccc
+31	1031	ddddddddddddddddddddddddddddddd
+32	1032	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+33	1033	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
+128	1128	zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
+# Eligible for optimization.
+select id, bigfield from prefixinno where bigfield = repeat('d', 31);
+id	bigfield
+31	ddddddddddddddddddddddddddddddd
+# Eligible for optimization, access via fake_id only.
+select id, bigfield from prefixinno where fake_id = 1031;
+id	bigfield
+31	ddddddddddddddddddddddddddddddd
+# Not eligible for optimization, access via fake_id of big row.
+select id, bigfield from prefixinno where fake_id = 1033;
+id	bigfield
+33	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
+# Not eligible for optimization.
+select id, bigfield from prefixinno where bigfield = repeat('x', 32);
+id	bigfield
+32	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+select id, bigfield from prefixinno where bigfield = repeat('y', 33);
+id	bigfield
+33	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
+select id, bigfield from prefixinno where bigfield = repeat('b', 8);
+id	bigfield
+8	bbbbbbbb
+select id, bigfield from prefixinno where bigfield = repeat('c', 24);
+id	bigfield
+24	cccccccccccccccccccccccc
+select id, bigfield from prefixinno where bigfield = repeat('z', 128);
+id	bigfield
+128	zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
+select id, bigfield from prefixinno where fake_id = 1033;
+id	bigfield
+33	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
+drop table prefixinno;
+# Multi-byte handling case
+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
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%';
+f1
+cccc
+cčc
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až';
+f1
+až
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்';
+f1
+தமிழ்
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%';
+f1
+ggᵷg
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '😊%';
+f1
+😊me
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢';
+f1
+ls¢
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '¢¢%';
+f1
+¢¢
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🐱🌑%';
+f1
+🐱🌑
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%';
+f1
+🌑
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌒%';
+f1
+🌒
+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
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%';
+f1
+cccc
+cčc
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až';
+f1
+až
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்';
+f1
+தமிழ்
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%';
+f1
+ggᵷg
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '😊%';
+f1
+😊me
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢';
+f1
+ls¢
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX(`f1`) WHERE f1 like '¢¢%';
+f1
+¢¢
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🐱🌑%';
+f1
+🐱🌑
+# Eligible - record length is shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%';
+f1
+🌑
+# Eligible - record length is shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌒%';
+f1
+🌒
+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
+# Not eligible - record length longer than prefix index
+SELECT col1 FROM t1 FORCE INDEX (`idx1`) WHERE col2 like 'test1%';
+col1
+3
+DROP TABLE t1;
+#
+# MDEV-20464 Division by 0 in row_search_with_covering_prefix()
+#
+CREATE TABLE t1 (f1 INT, f2 INT AS (f1), f3 INT AS (f1), f4 INT AS (f1),
+KEY (f1,f2,f3)) ENGINE=InnoDB;
+INSERT INTO t1 (f1) VALUES (NULL),(0);
+SELECT f1, MAX(f3), COUNT(f4) FROM t1 GROUP BY f1;
+f1	MAX(f3)	COUNT(f4)
+NULL	NULL	0
+0	0	1
+DROP TABLE t1;
+#
+# 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');
+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_stats_persistent = @save_innodb_stats_persistent;