|
1
by brian
clean slate |
1 |
#
|
2 |
# Test file for WL#1724 (Min/Max Optimization for Queries with Group By Clause). |
|
3 |
# The queries in this file test query execution via QUICK_GROUP_MIN_MAX_SELECT |
|
4 |
# that depends on InnoDB |
|
5 |
#
|
|
6 |
||
7 |
--source include/have_innodb.inc
|
|
8 |
||
9 |
#
|
|
10 |
# Bug #12672: primary key implcitly included in every innodb index |
|
11 |
#
|
|
12 |
||
13 |
--disable_warnings
|
|
14 |
create table t4 ( |
|
15 |
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 ' ' |
|
16 |
) engine=innodb; |
|
17 |
--enable_warnings
|
|
18 |
||
19 |
insert into t4 (a1, a2, b, c, d) values |
|
20 |
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), |
|
21 |
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), |
|
22 |
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), |
|
23 |
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), |
|
24 |
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), |
|
25 |
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), |
|
26 |
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), |
|
27 |
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), |
|
28 |
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), |
|
29 |
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), |
|
30 |
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), |
|
31 |
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), |
|
32 |
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), |
|
33 |
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), |
|
34 |
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), |
|
35 |
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), |
|
36 |
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), |
|
37 |
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), |
|
38 |
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), |
|
39 |
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), |
|
40 |
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), |
|
41 |
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), |
|
42 |
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), |
|
43 |
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), |
|
44 |
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), |
|
45 |
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), |
|
46 |
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), |
|
47 |
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), |
|
48 |
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), |
|
49 |
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), |
|
50 |
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), |
|
51 |
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); |
|
52 |
||
53 |
create index idx12672_0 on t4 (a1); |
|
54 |
create index idx12672_1 on t4 (a1,a2,b,c); |
|
55 |
create index idx12672_2 on t4 (a1,a2,b); |
|
56 |
analyze table t4; |
|
57 |
||
58 |
select distinct a1 from t4 where pk_col not in (1,2,3,4); |
|
59 |
||
60 |
drop table t4; |
|
61 |
||
62 |
||
63 |
#
|
|
64 |
# Bug #6142: a problem with the empty innodb table |
|
65 |
#
|
|
66 |
||
67 |
--disable_warnings
|
|
68 |
create table t1 ( |
|
69 |
a varchar(30), b varchar(30), primary key(a), key(b) |
|
70 |
) engine=innodb; |
|
71 |
--enable_warnings
|
|
72 |
select distinct a from t1; |
|
73 |
drop table t1; |
|
74 |
||
75 |
#
|
|
76 |
# Bug #9798: group by with rollup |
|
77 |
#
|
|
78 |
||
79 |
--disable_warnings
|
|
80 |
create table t1(a int, key(a)) engine=innodb; |
|
81 |
--enable_warnings
|
|
82 |
insert into t1 values(1); |
|
83 |
select a, count(a) from t1 group by a with rollup; |
|
84 |
drop table t1; |
|
85 |
||
86 |
||
87 |
#
|
|
88 |
# Bug #13293 Wrongly used index results in endless loop. |
|
89 |
#
|
|
90 |
create table t1 (f1 int, f2 char(1), primary key(f1,f2)) engine=innodb; |
|
91 |
insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d"); |
|
92 |
alter table t1 drop primary key, add primary key (f2, f1); |
|
93 |
explain select distinct f1 a, f1 b from t1; |
|
94 |
explain select distinct f1, f2 from t1; |
|
95 |
drop table t1; |