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
|
show create table seq_1_to_15_step_2;
Table Create Table
seq_1_to_15_step_2 CREATE TABLE `seq_1_to_15_step_2` (
`seq` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`seq`)
) ENGINE=SEQUENCE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
select count(seq),sum(seq),1 from seq_1_to_15_step_2;
count(seq) sum(seq) 1
8 64 1
#
# The engine should be able to optimize the following requests
#
select count(*) from seq_1_to_15_step_2;
count(*)
8
explain select count(*) from seq_1_to_15_step_2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select count(seq) from seq_1_to_15_step_2;
count(seq)
8
explain select count(seq) from seq_1_to_15_step_2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select sum(seq) from seq_1_to_15_step_2;
sum(seq)
64
explain select sum(seq) from seq_1_to_15_step_2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select count(seq),sum(seq) from seq_1_to_15_step_2;
count(seq) sum(seq)
8 64
explain select count(seq),sum(seq) from seq_1_to_15_step_2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select count(seq) as c from seq_1_to_15_step_2 having c > 5;
c
8
explain select count(seq) as c from seq_1_to_15_step_2 having c > 5;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select count(seq) as c from seq_1_to_15_step_2 having c > 1000;
c
explain select count(seq) as c from seq_1_to_15_step_2 having c > 1000;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select distinct count(*) from seq_1_to_15_step_2;
count(*)
8
explain select distinct count(*) from seq_1_to_15_step_2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
select * from seq_1_to_15_step_2, (select count(*) from seq_1_to_15_step_2) as t1;
seq count(*)
1 8
3 8
5 8
7 8
9 8
11 8
13 8
15 8
explain select * from seq_1_to_15_step_2, (select count(*) from seq_1_to_15_step_2) as t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY seq_1_to_15_step_2 index NULL PRIMARY 8 NULL # Using index
1 PRIMARY <derived2> ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join)
2 DERIVED NULL NULL NULL NULL NULL NULL # Storage engine handles GROUP BY
create view v1 as select count(*) from seq_1_to_15_step_2;
select * from v1;
count(*)
8
drop view v1;
#
# The engine can't optimize the following queries
#
select count(seq),sum(seq),1 from seq_1_to_15_step_2;
count(seq) sum(seq) 1
8 64 1
explain select count(seq),sum(seq),1 from seq_1_to_15_step_2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index
explain select count(*) from seq_1_to_15_step_2, seq_1_to_15_step_2 as t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index
1 SIMPLE t2 index NULL PRIMARY 8 NULL 8 Using index; Using join buffer (flat, BNL join)
explain select count(*) from seq_1_to_15_step_2 where seq > 0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE seq_1_to_15_step_2 range PRIMARY PRIMARY 8 NULL 8 Using where; Using index
explain select count(*) from seq_1_to_15_step_2 group by mod(seq,2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index; Using temporary; Using filesort
create temporary table t1 select * from seq_1_to_3;
select count(NULL) from t1;
count(NULL)
0
select count(NULL) from seq_1_to_3;
count(NULL)
0
#
# MDEV-20753: Sequence with limit 0 crashes server
#
select count(NULL) from seq_1_to_3 limit 0;
count(NULL)
# End of 10.3 tests
#
# MDEV-16327: Server doesn't account for engines that supports
# OFFSET on their own.
#
select count(NULL) from seq_1_to_3 limit 1;
count(NULL)
0
explain format=json select count(NULL) from seq_1_to_3 limit 1;
EXPLAIN
{
"query_block": {
"select_id": 1,
"table": {
"message": "Storage engine handles GROUP BY"
}
}
}
select count(NULL) from seq_1_to_3 limit 1 offset 1;
count(NULL)
explain format=json select count(NULL) from seq_1_to_3 limit 1 offset 1;
EXPLAIN
{
"query_block": {
"select_id": 1,
"table": {
"message": "Storage engine handles GROUP BY"
}
}
}
# End of 10.5 tests
|
