diff options
Diffstat (limited to 'mysql-test/main/group_min_max_innodb.test')
-rw-r--r-- | mysql-test/main/group_min_max_innodb.test | 245 |
1 files changed, 245 insertions, 0 deletions
diff --git a/mysql-test/main/group_min_max_innodb.test b/mysql-test/main/group_min_max_innodb.test new file mode 100644 index 00000000000..91e0bd3279f --- /dev/null +++ b/mysql-test/main/group_min_max_innodb.test @@ -0,0 +1,245 @@ +# +# Test file for WL#1724 (Min/Max Optimization for Queries with Group By Clause). +# The queries in this file test query execution via QUICK_GROUP_MIN_MAX_SELECT +# that depends on InnoDB +# + +--source include/have_innodb.inc + +--disable_warnings +drop view if exists v1; +drop table if exists t1,t4; +--enable_warnings + +# +# Bug #12672: primary key implcitly included in every innodb index +# + +--disable_warnings +create table t4 ( + pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' +) engine=innodb; +--enable_warnings + +insert into t4 (a1, a2, b, c, d) values +('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), +('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), +('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), +('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), +('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), +('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), +('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), +('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), +('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), +('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), +('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), +('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), +('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), +('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), +('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), +('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), +('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), +('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), +('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), +('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), +('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), +('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), +('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), +('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), +('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), +('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), +('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), +('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), +('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), +('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), +('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), +('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); + +create index idx12672_0 on t4 (a1); +create index idx12672_1 on t4 (a1,a2,b,c); +create index idx12672_2 on t4 (a1,a2,b); +analyze table t4; + +select distinct a1 from t4 where pk_col not in (1,2,3,4); + +drop table t4; + + +# +# Bug #6142: a problem with the empty innodb table +# + +--disable_warnings +create table t1 ( + a varchar(30), b varchar(30), primary key(a), key(b) +) engine=innodb; +--enable_warnings +select distinct a from t1; +drop table t1; + +# +# Bug #9798: group by with rollup +# + +--disable_warnings +create table t1(a int, key(a)) engine=innodb; +--enable_warnings +insert into t1 values(1); +select a, count(a) from t1 group by a with rollup; +drop table t1; + + +# +# Bug #13293 Wrongly used index results in endless loop. +# +create table t1 (f1 int, f2 char(1), primary key(f1,f2)) engine=innodb; +insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d"); +alter table t1 drop primary key, add primary key (f2, f1); +explain select distinct f1 a, f1 b from t1; +explain select distinct f1, f2 from t1; +drop table t1; + + +# +# Bug #36632: Select distinct from a simple view on an InnoDB table +# returns incorrect results +# +create table t1(pk int primary key) engine=innodb; +create view v1 as select pk from t1 where pk < 20; + +insert into t1 values (1), (2), (3), (4); +select distinct pk from v1; + +insert into t1 values (5), (6), (7); +select distinct pk from v1; + +drop view v1; +drop table t1; + +--echo End of 5.1 tests + +--echo # +--echo # Bug#12540545 61101: ASSERTION FAILURE IN THREAD 1256741184 IN +--echo # FILE /BUILDDIR/BUILD/BUILD/MYSQ +--echo # + +CREATE TABLE t1 (a CHAR(1), b CHAR(1), PRIMARY KEY (a,b)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd'); +EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 WHERE b = 'b'; +SELECT COUNT(DISTINCT a) FROM t1 WHERE b = 'b'; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(1) NOT NULL, b CHAR(1) NOT NULL, UNIQUE KEY (a,b)) +ENGINE=InnoDB; +INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd'); +EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 WHERE b = 'b'; +SELECT COUNT(DISTINCT a) FROM t1 WHERE b = 'b'; +DROP TABLE t1; + +--echo End of 5.5 tests + +--echo # +--echo # Bug#17909656 - WRONG RESULTS FOR A SIMPLE QUERY WITH GROUP BY +--echo # + +CREATE TABLE t0 ( + i1 INTEGER NOT NULL +); + +INSERT INTO t0 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10), + (11),(12),(13),(14),(15),(16),(17),(18),(19),(20), + (21),(22),(23),(24),(25),(26),(27),(28),(29),(30); + +CREATE TABLE t1 ( + c1 CHAR(1) NOT NULL, + i1 INTEGER NOT NULL, + i2 INTEGER NOT NULL, + UNIQUE KEY k1 (c1,i2) +) ENGINE=InnoDB; + +INSERT INTO t1 SELECT 'A',i1,i1 FROM t0; +INSERT INTO t1 SELECT 'B',i1,i1 FROM t0; +INSERT INTO t1 SELECT 'C',i1,i1 FROM t0; +INSERT INTO t1 SELECT 'D',i1,i1 FROM t0; +INSERT INTO t1 SELECT 'E',i1,i1 FROM t0; +INSERT INTO t1 SELECT 'F',i1,i1 FROM t0; + +CREATE TABLE t2 ( + c1 CHAR(1) NOT NULL, + i1 INTEGER NOT NULL, + i2 INTEGER NOT NULL, + UNIQUE KEY k2 (c1,i1,i2) +) ENGINE=InnoDB; + +INSERT INTO t2 SELECT 'A',i1,i1 FROM t0; +INSERT INTO t2 SELECT 'B',i1,i1 FROM t0; +INSERT INTO t2 SELECT 'C',i1,i1 FROM t0; +INSERT INTO t2 SELECT 'D',i1,i1 FROM t0; +INSERT INTO t2 SELECT 'E',i1,i1 FROM t0; +INSERT INTO t2 SELECT 'F',i1,i1 FROM t0; + +-- disable_result_log +ANALYZE TABLE t1; +ANALYZE TABLE t2; +-- enable_result_log + +let query= +SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' AND i2 = 17) OR ( c1 = 'F') +GROUP BY c1; +eval EXPLAIN $query; +eval $query; + +let query= +SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' OR ( c1 = 'F' AND i2 = 17)) +GROUP BY c1; +eval EXPLAIN $query; +eval $query; + +let query= +SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' OR c1 = 'F' ) AND ( i2 = 17 ) +GROUP BY c1; +eval EXPLAIN $query; +eval $query; + +let query= +SELECT c1, max(i2) FROM t1 +WHERE ((c1 = 'C' AND (i2 = 40 OR i2 = 30)) OR ( c1 = 'F' AND (i2 = 40 ))) +GROUP BY c1; +eval EXPLAIN $query; +eval $query; + +let query= +SELECT c1, i1, max(i2) FROM t2 +WHERE (c1 = 'C' OR ( c1 = 'F' AND i1 < 35)) AND ( i2 = 17 ) +GROUP BY c1,i1; +eval EXPLAIN $query; +eval $query; + +let query= +SELECT c1, i1, max(i2) FROM t2 +WHERE (((c1 = 'C' AND i1 < 40) OR ( c1 = 'F' AND i1 < 35)) AND ( i2 = 17 )) +GROUP BY c1,i1; +eval EXPLAIN $query; +eval $query; + +let query= +SELECT c1, i1, max(i2) FROM t2 +WHERE ((c1 = 'C' AND i1 < 40) OR ( c1 = 'F' AND i1 < 35) OR ( i2 = 17 )) +GROUP BY c1,i1; +eval EXPLAIN $query; +eval $query; + +DROP TABLE t0,t1,t2; + +--echo # +--echo # MDEV-MariaDB daemon leaks memory with specific query +--echo # + +CREATE TABLE t1 (`voter_id` int(11) unsigned NOT NULL, + `language_id` int(11) unsigned NOT NULL DEFAULT '1' +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE t2 (`voter_id` int(10) unsigned NOT NULL DEFAULT '0', + `serialized_c` mediumblob) ENGINE=InnoDB DEFAULT CHARSET=utf8; +insert into t2 values (1,repeat("a",1000)),(2,repeat("a",1000)),(3,repeat("b",1000)),(4,repeat("c",1000)),(4,repeat("b",1000)); +SELECT GROUP_CONCAT(t1.language_id SEPARATOR ',') AS `translation_resources`, `d`.`serialized_c` FROM t2 AS `d` LEFT JOIN t1 ON `d`.`voter_id` = t1.`voter_id` GROUP BY `d`.`voter_id` ORDER BY 10-d.voter_id+RAND()*0; +drop table t1,t2; |