diff options
Diffstat (limited to 'mysql-test/main/fast_prefix_index_fetch_innodb.result')
-rw-r--r-- | mysql-test/main/fast_prefix_index_fetch_innodb.result | 68 |
1 files changed, 62 insertions, 6 deletions
diff --git a/mysql-test/main/fast_prefix_index_fetch_innodb.result b/mysql-test/main/fast_prefix_index_fetch_innodb.result index c6d96389b08..ef297e5c6b5 100644 --- a/mysql-test/main/fast_prefix_index_fetch_innodb.result +++ b/mysql-test/main/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,60 @@ select @cluster_lookups_avoided; @cluster_lookups_avoided 0 DROP TABLE t1; -set global innodb_prefix_index_cluster_optimization = OFF; +# +# 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'); +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; |