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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
|
#
# Bug#13464493 "19 X HIGHER THAN EXPECTED EXEC. TIME FOR
# MYSQL_BENCH/COUNT_DISTINCT_KEY_PREFIX"
#
CREATE TABLE t1 (
c1 CHAR(1) NOT NULL,
i1 INTEGER NOT NULL,
i2 INTEGER NOT NULL,
PRIMARY KEY (c1,i1),
UNIQUE KEY k1 (c1,i2)
) ENGINE=InnoDB, CHARSET utf8mb4;
INSERT INTO t1 VALUES ('A',0,999),('A',6,993),('A',12,987),
('A',18,981),('A',24,975),('A',30,969),('A',36,963),('A',42,957),
('A',48,951),('A',54,945),('A',60,939),('A',66,933),('A',72,927),
('A',78,921),('A',84,915),('A',90,909),('A',96,903),('A',102,897),
('A',108,891),('A',114,885),('A',120,879),('A',126,873),('A',132,867),
('A',138,861),('A',144,855),('A',150,849),('A',156,843),('A',162,837),
('A',168,831),('A',174,825),('A',180,819),('A',186,813),('A',192,807),
('A',198,801),('A',204,795),('B',210,789),('B',216,783),('B',222,777),
('B',228,771),('B',234,765),('B',240,759),('B',246,753),('B',252,747),
('B',258,741),('B',264,735),('B',270,729),('B',276,723),('B',282,717),
('B',288,711),('B',294,705),('B',300,699),('B',306,693),('B',312,687),
('B',318,681),('B',324,675),('B',330,669),('B',336,663),('B',342,657),
('B',348,651),('B',354,645),('B',360,639),('B',366,633),('B',372,627),
('B',378,621),('C',384,615),('C',390,609),('C',396,603),('C',402,597),
('C',408,591),('C',414,585),('C',420,579),('C',426,573),('C',432,567),
('C',438,561),('C',444,555),('C',450,549),('C',456,543),('C',462,537),
('C',468,531),('C',474,525),('C',480,519),('C',486,513),('C',492,507),
('C',498,501),('C',504,495),('C',510,489),('C',516,483),('C',522,477),
('C',528,471),('C',534,465),('C',540,459),('C',546,453),('C',552,447),
('C',558,441),('C',564,435),('C',570,429),('C',576,423),('C',582,417),
('C',588,411),('C',594,405);
ANALYZE TABLE t1;
EXPLAIN SELECT COUNT(DISTINCT c1) FROM t1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL range PRIMARY,k1 k1 4 NULL 4 100.00 Using index for group-by
Warnings:
Note 1003 /* select#1 */ select count(distinct `test`.`t1`.`c1`) AS `COUNT(DISTINCT c1)` from `test`.`t1`
SELECT COUNT(DISTINCT c1) FROM t1;
COUNT(DISTINCT c1)
3
DROP TABLE t1;
#
# Bug#11757108 "CHANGE IN EXECUTION PLAN FOR COUNT_DISTINCT_GROUP_ON_KEY
# CAUSES PEFORMANCE REGRESSIONS"
#
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,
PRIMARY KEY (c1,i1),
UNIQUE KEY k1 (c1,i2)
) ENGINE=InnoDB, CHARSET utf8mb4;
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;
ANALYZE TABLE t1;
EXPLAIN select c1,count(distinct i2) from t1 group by c1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL range PRIMARY,k1 k1 8 NULL 181 100.00 Using index for group-by (scanning)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,count(distinct `test`.`t1`.`i2`) AS `count(distinct i2)` from `test`.`t1` group by `test`.`t1`.`c1`
FLUSH STATUS;
select c1,count(distinct i2) from t1 group by c1;
c1 count(distinct i2)
A 30
B 30
C 30
D 30
E 30
F 30
SHOW STATUS LIKE 'Handler_read_key';
Variable_name Value
Handler_read_key 2
SHOW STATUS LIKE 'Handler_read_next';
Variable_name Value
Handler_read_next 180
DROP TABLE t0, t1;
#
# Bug#17222452 SELECT COUNT(DISTINCT A,B) INCORRECTLY COUNTS
# ROWS CONTAINING NULL
#
CREATE TABLE t (a INT, b INT,KEY k(a,b));
INSERT INTO t VALUES (1,2),
(NULL,3),(3,3),(1,NULL),
(NULL,2), (NULL,NULL);
EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL range k k 10 NULL # # Using index for group-by (scanning)
Warnings:
Note 1003 /* select#1 */ select count(distinct `test`.`t`.`a`,`test`.`t`.`b`) AS `COUNT(DISTINCT a,b)` from `test`.`t`
SELECT COUNT(DISTINCT a,b) FROM t;
COUNT(DISTINCT a,b)
2
EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t IGNORE INDEX (k);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t NULL ALL NULL NULL NULL NULL # 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select count(distinct `test`.`t`.`a`,`test`.`t`.`b`) AS `COUNT(DISTINCT a,b)` from `test`.`t` IGNORE INDEX (`k`)
SELECT COUNT(DISTINCT a,b) FROM t IGNORE INDEX (k);
COUNT(DISTINCT a,b)
2
DROP TABLE t;
#
# Bug#16982071: ASSERT `TAB->KEYS.IS_SET(KEYNO)' FAILED IN
# SETUP_SEMIJOIN_DUPS_ELIMINATION
#
CREATE TABLE t1 (
pk INTEGER,
col_int INTEGER,
PRIMARY KEY (pk)
);
CREATE TABLE t2 (
pk INTEGER,
col_varchar_key VARCHAR(1),
PRIMARY KEY (pk),
KEY (col_varchar_key)
) CHARSET utf8mb4;
INSERT INTO t2 VALUES (1, 'g');
CREATE TABLE t3 (
pk INTEGER,
col_varchar_key VARCHAR(1),
PRIMARY KEY (pk),
KEY (col_varchar_key)
) CHARSET utf8mb4;
INSERT INTO t3 VALUES (1, 'v'),(2, NULL);
EXPLAIN SELECT t1.col_int
FROM t1, t3
WHERE t3.col_varchar_key IN (
SELECT t2.col_varchar_key FROM t2 WHERE t2.pk > t1.col_int
);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL
1 SIMPLE t2 NULL index PRIMARY,col_varchar_key col_varchar_key 7 NULL # 100.00 Using where; Using index; LooseScan
1 SIMPLE t3 NULL ref col_varchar_key col_varchar_key 7 test.t2.col_varchar_key # 100.00 Using index
Warnings:
Note 1276 Field or reference 'test.t1.col_int' of SELECT #2 was resolved in SELECT #1
Note 1003 /* select#1 */ select `test`.`t1`.`col_int` AS `col_int` from `test`.`t1` join `test`.`t3` semi join (`test`.`t2`) where ((`test`.`t3`.`col_varchar_key` = `test`.`t2`.`col_varchar_key`) and (`test`.`t2`.`pk` > `test`.`t1`.`col_int`))
SELECT t1.col_int
FROM t1, t3
WHERE t3.col_varchar_key IN (
SELECT t2.col_varchar_key FROM t2 WHERE t2.pk > t1.col_int
);
col_int
DROP TABLE t1, t2, t3;
#
# Bug #30659810: INCORRECT QUERY RESULTS
#
CREATE TABLE t1 (
a INTEGER NOT NULL
);
INSERT INTO t1 VALUES (2),(2);
CREATE TABLE t2 (
b INTEGER
);
INSERT INTO t2 VALUES (2),(11),(11);
CREATE TABLE t3 (
b INTEGER,
pk INTEGER,
KEY b_key (b)
);
INSERT INTO t3 VALUES (2,5);
CREATE TABLE t4 (
pk INTEGER NOT NULL
);
INSERT INTO t4 VALUES (5),(7);
ANALYZE TABLE t1,t2,t3,t4;
Table Op Msg_type Msg_text
test.t1 analyze status OK
test.t2 analyze status OK
test.t3 analyze status OK
test.t4 analyze status OK
EXPLAIN FORMAT=TREE SELECT *
FROM t1
JOIN t2 ON t1.a = t2.b
WHERE t2.b IN (
SELECT t3.b
FROM t3 JOIN t4 ON t3.pk = t4.pk
);
EXPLAIN
-> Inner hash join (t2.b = t1.a) (cost=2 rows=2)
-> Table scan on t2 (cost=0.175 rows=3)
-> Hash
-> Nested loop inner join (cost=1.15 rows=2)
-> Table scan on t1 (cost=0.45 rows=2)
-> Nested loop semijoin with duplicate removal on b_key (cost=1 rows=1)
-> Index lookup on t3 using b_key (b=t1.a) (cost=0.3 rows=1)
-> Filter: (t4.pk = t3.pk) (cost=0.3 rows=1)
-> Table scan on t4 (cost=0.3 rows=2)
SELECT *
FROM t1
JOIN t2 ON t1.a = t2.b
WHERE t2.b IN (
SELECT t3.b
FROM t3 JOIN t4 ON t3.pk = t4.pk
);
a b
2 2
2 2
DROP TABLE t1, t2, t3, t4;
#
# Bug #32597719: WL#14325: ASSERTION `IS_NULL_ROW == FALSE' FAILED|SQL/SORTING_ITERATOR.H
#
CREATE TABLE t1 (
col_int INTEGER,
col_varchar_key VARCHAR(1)
);
CREATE TABLE t2 (
pk INTEGER,
j JSON
);
INSERT INTO t2 VALUES (1,'true'),(2,'true'),(3,'true'),(4,'true'),(5,'true');
ANALYZE TABLE t1, t2;
Table Op Msg_type Msg_text
test.t1 analyze status OK
test.t2 analyze status OK
SELECT SUM(t1.col_int)
FROM t1, t2
WHERE t2.j IN (
SELECT t3.j FROM t2 JOIN t2 AS t3 ON t2.pk <> t3.pk
) AND t1.col_varchar_key='';
SUM(t1.col_int)
NULL
DROP TABLE t1, t2;
|
