Debian Codegraph @

mysql-test/suite/vcol/r/vcol_select_innodb.result

raw
1 SET @@session.default_storage_engine = 'InnoDB';
2 SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent;
3 SET GLOBAL innodb_stats_persistent=0;
4 create table t1 (a int,
5 b int as (-a),
6 c int as (-a) persistent,
7 index (c));
8 insert into t1 (a) values (2), (1), (1), (3), (NULL);
9 create table t2 like t1;
10 insert into t2 (a) values (1);
11 create table t3 (a int primary key,
12 b int as (-a),
13 c int as (-a) persistent unique);
14 insert into t3 (a) values (2),(1),(3),(5),(4),(7);
15 # select_type=SIMPLE, type=system
16 select * from t2;
17 a b c
18 1 -1 -1
19 explain select * from t2;
20 id select_type table type possible_keys key key_len ref rows Extra
21 1 SIMPLE t2 ALL NULL NULL NULL NULL 1
22 select * from t2 where c=-1;
23 a b c
24 1 -1 -1
25 explain select * from t2 where c=-1;
26 id select_type table type possible_keys key key_len ref rows Extra
27 1 SIMPLE t2 ref c c 5 const 1
28 # select_type=SIMPLE, type=ALL
29 select * from t1 where b=-1;
30 a b c
31 1 -1 -1
32 1 -1 -1
33 explain select * from t1 where b=-1;
34 id select_type table type possible_keys key key_len ref rows Extra
35 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
36 # select_type=SIMPLE, type=const
37 select * from t3 where a=1;
38 a b c
39 1 -1 -1
40 explain select * from t3 where a=1;
41 id select_type table type possible_keys key key_len ref rows Extra
42 1 SIMPLE t3 const PRIMARY PRIMARY 4 const 1
43 # select_type=SIMPLE, type=range
44 select * from t3 where c>=-1;
45 a b c
46 1 -1 -1
47 explain select * from t3 where c>=-1;
48 id select_type table type possible_keys key key_len ref rows Extra
49 1 SIMPLE t3 range c c 5 NULL 1 Using index condition
50 # select_type=SIMPLE, type=ref
51 select * from t1,t3 where t1.c=t3.c and t3.c=-1;
52 a b c a b c
53 1 -1 -1 1 -1 -1
54 1 -1 -1 1 -1 -1
55 explain select * from t1,t3 where t1.c=t3.c and t3.c=-1;
56 id select_type table type possible_keys key key_len ref rows Extra
57 1 SIMPLE t3 const c c 5 const 1
58 1 SIMPLE t1 ref c c 5 const 2
59 # select_type=PRIMARY, type=index,ALL
60 select * from t1 where b in (select c from t3);
61 a b c
62 2 -2 -2
63 1 -1 -1
64 1 -1 -1
65 3 -3 -3
66 explain select * from t1 where b in (select c from t3);
67 id select_type table type possible_keys key key_len ref rows Extra
68 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where
69 1 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index
70 # select_type=PRIMARY, type=range,ref
71 select * from t1 where c in (select c from t3 where c between -2 and -1);
72 a b c
73 2 -2 -2
74 1 -1 -1
75 1 -1 -1
76 explain select * from t1 where c in (select c from t3 where c between -2 and -1);
77 id select_type table type possible_keys key key_len ref rows Extra
78 1 PRIMARY t3 range c c 5 NULL 2 Using where; Using index
79 1 PRIMARY t1 ref c c 5 test.t3.c 1
80 # select_type=UNION, type=system
81 # select_type=UNION RESULT, type=<union1,2>
82 select * from t1 union select * from t2;
83 a b c
84 2 -2 -2
85 1 -1 -1
86 3 -3 -3
87 NULL NULL NULL
88 explain select * from t1 union select * from t2;
89 id select_type table type possible_keys key key_len ref rows Extra
90 1 PRIMARY t1 ALL NULL NULL NULL NULL 5
91 2 UNION t2 ALL NULL NULL NULL NULL 1
92 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
93 # select_type=DERIVED, type=system
94 set @tmp_optimizer_switch=@@optimizer_switch;
95 set optimizer_switch='derived_merge=off,derived_with_keys=off';
96 select * from (select a,b,c from t1) as t11;
97 a b c
98 2 -2 -2
99 1 -1 -1
100 1 -1 -1
101 3 -3 -3
102 NULL NULL NULL
103 explain select * from (select a,b,c from t1) as t11;
104 id select_type table type possible_keys key key_len ref rows Extra
105 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5
106 2 DERIVED t1 ALL NULL NULL NULL NULL 5
107 set optimizer_switch=@tmp_optimizer_switch;
108 ###
109 ### Using aggregate functions with/without DISTINCT
110 ###
111 # SELECT COUNT(*) FROM tbl_name
112 select count(*) from t1;
113 count(*)
114 5
115 explain select count(*) from t1;
116 id select_type table type possible_keys key key_len ref rows Extra
117 1 SIMPLE t1 index NULL c 5 NULL 5 Using index
118 # SELECT COUNT(DISTINCT <non-vcol>) FROM tbl_name
119 select count(distinct a) from t1;
120 count(distinct a)
121 3
122 explain select count(distinct a) from t1;
123 id select_type table type possible_keys key key_len ref rows Extra
124 1 SIMPLE t1 ALL NULL NULL NULL NULL 5
125 # SELECT COUNT(DISTINCT <non-stored vcol>) FROM tbl_name
126 select count(distinct b) from t1;
127 count(distinct b)
128 3
129 explain select count(distinct b) from t1;
130 id select_type table type possible_keys key key_len ref rows Extra
131 1 SIMPLE t1 ALL NULL NULL NULL NULL 5
132 # SELECT COUNT(DISTINCT <stored vcol>) FROM tbl_name
133 select count(distinct c) from t1;
134 count(distinct c)
135 3
136 explain select count(distinct c) from t1;
137 id select_type table type possible_keys key key_len ref rows Extra
138 1 SIMPLE t1 range NULL c 5 NULL 5 Using index for group-by
139 ###
140 ### filesort & range-based utils
141 ###
142 # SELECT * FROM tbl_name WHERE <vcol expr>
143 select * from t3 where c >= -2;
144 a b c
145 2 -2 -2
146 1 -1 -1
147 explain select * from t3 where c >= -2;
148 id select_type table type possible_keys key key_len ref rows Extra
149 1 SIMPLE t3 range c c 5 NULL 2 Using index condition
150 # SELECT * FROM tbl_name WHERE <non-vcol expr>
151 select * from t3 where a between 1 and 2;
152 a b c
153 1 -1 -1
154 2 -2 -2
155 explain select * from t3 where a between 1 and 2;
156 id select_type table type possible_keys key key_len ref rows Extra
157 1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where
158 # SELECT * FROM tbl_name WHERE <non-indexed vcol expr>
159 select * from t3 where b between -2 and -1;
160 a b c
161 1 -1 -1
162 2 -2 -2
163 explain select * from t3 where b between -2 and -1;
164 id select_type table type possible_keys key key_len ref rows Extra
165 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where
166 # SELECT * FROM tbl_name WHERE <indexed vcol expr>
167 select * from t3 where c between -2 and -1;
168 a b c
169 2 -2 -2
170 1 -1 -1
171 explain select * from t3 where c between -2 and -1;
172 id select_type table type possible_keys key key_len ref rows Extra
173 1 SIMPLE t3 range c c 5 NULL 2 Using index condition
174 # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <non-indexed vcol>
175 select * from t3 where a between 1 and 2 order by b;
176 a b c
177 2 -2 -2
178 1 -1 -1
179 explain select * from t3 where a between 1 and 2 order by b;
180 id select_type table type possible_keys key key_len ref rows Extra
181 1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort
182 # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol>
183 select * from t3 where a between 1 and 2 order by c;
184 a b c
185 2 -2 -2
186 1 -1 -1
187 explain select * from t3 where a between 1 and 2 order by c;
188 id select_type table type possible_keys key key_len ref rows Extra
189 1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort
190 # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol>
191 select * from t3 where b between -2 and -1 order by a;
192 a b c
193 1 -1 -1
194 2 -2 -2
195 explain select * from t3 where b between -2 and -1 order by a;
196 id select_type table type possible_keys key key_len ref rows Extra
197 1 SIMPLE t3 index NULL PRIMARY 4 NULL 6 Using where
198 # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol>
199 select * from t3 where b between -2 and -1 order by b;
200 a b c
201 2 -2 -2
202 1 -1 -1
203 explain select * from t3 where b between -2 and -1 order by b;
204 id select_type table type possible_keys key key_len ref rows Extra
205 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort
206 # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-indexed vcol>
207 select * from t3 where c between -2 and -1 order by b;
208 a b c
209 2 -2 -2
210 1 -1 -1
211 explain select * from t3 where c between -2 and -1 order by b;
212 id select_type table type possible_keys key key_len ref rows Extra
213 1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort
214 # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol>
215 select * from t3 where b between -2 and -1 order by c;
216 a b c
217 2 -2 -2
218 1 -1 -1
219 explain select * from t3 where b between -2 and -1 order by c;
220 id select_type table type possible_keys key key_len ref rows Extra
221 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort
222 # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <indexed vcol>
223 select * from t3 where c between -2 and -1 order by c;
224 a b c
225 2 -2 -2
226 1 -1 -1
227 explain select * from t3 where c between -2 and -1 order by c;
228 id select_type table type possible_keys key key_len ref rows Extra
229 1 SIMPLE t3 range c c 5 NULL 2 Using index condition
230 # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
231 select sum(b) from t1 group by b;
232 sum(b)
233 NULL
234 -3
235 -2
236 -2
237 explain select sum(b) from t1 group by b;
238 id select_type table type possible_keys key key_len ref rows Extra
239 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort
240 # SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <indexed vcol>
241 select sum(c) from t1 group by c;
242 sum(c)
243 NULL
244 -3
245 -2
246 -2
247 explain select sum(c) from t1 group by c;
248 id select_type table type possible_keys key key_len ref rows Extra
249 1 SIMPLE t1 index NULL c 5 NULL 5 Using index
250 # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <indexed vcol>
251 select sum(b) from t1 group by c;
252 sum(b)
253 NULL
254 -3
255 -2
256 -2
257 explain select sum(b) from t1 group by c;
258 id select_type table type possible_keys key key_len ref rows Extra
259 1 SIMPLE t1 index NULL c 5 NULL 5
260 # SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol>
261 select sum(c) from t1 group by b;
262 sum(c)
263 NULL
264 -3
265 -2
266 -2
267 explain select sum(c) from t1 group by b;
268 id select_type table type possible_keys key key_len ref rows Extra
269 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort
270 SET GLOBAL innodb_stats_persistent=@save_stats_persistent;