Debian Codegraph @

mysql-test/suite/gcol_ndb/r/gcol_select_ndb.result

raw
1 SET @@session.default_storage_engine = 'ndbcluster';
2 SET ndb_index_stat_enable=OFF;
3 SET optimizer_switch='derived_merge=off';
4 create table t1 (a int,
5 b int generated always as (-a) virtual,
6 c int generated always as (-a) stored,
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 generated always as (-a) virtual,
13 c int generated always as (-a) stored unique);
14 insert into t3 (a) values (2),(1),(3);
15 analyze table t1,t2,t3;
16 Table Op Msg_type Msg_text
17 test.t1 analyze status OK
18 test.t2 analyze status OK
19 test.t3 analyze status OK
20 # select_type=SIMPLE, type=system
21 select * from t2;
22 a b c
23 1 -1 -1
24 explain select * from t2;
25 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
26 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 NULL
27 Warnings:
28 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2`
29 select * from t2 where c=-1;
30 a b c
31 1 -1 -1
32 explain select * from t2 where c=-1;
33 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
34 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref c c 5 const 2 100.00 NULL
35 Warnings:
36 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`c` = -(1))
37 # select_type=SIMPLE, type=ALL
38 select * from t1 where b=-1;
39 a b c
40 1 -1 -1
41 1 -1 -1
42 explain select * from t1 where b=-1;
43 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
44 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 20.00 Using where
45 Warnings:
46 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`b` = <cache>(-(1)))
47 # select_type=SIMPLE, type=const
48 select * from t3 where a=1;
49 a b c
50 1 -1 -1
51 explain select * from t3 where a=1;
52 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
53 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref PRIMARY PRIMARY 4 const 1 100.00 NULL
54 Warnings:
55 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`a` = 1)
56 # select_type=SIMPLE, type=range
57 select * from t3 where c>=-1;
58 a b c
59 1 -1 -1
60 explain select * from t3 where c>=-1;
61 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
62 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 range c c 5 NULL 3 100.00 Using pushed condition (`test`.`t3`.`c` >= <cache>(-(1))); Using MRR
63 Warnings:
64 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`c` >= <cache>(-(1)))
65 # select_type=SIMPLE, type=ref
66 select * from t1,t3 where t1.c=t3.c and t3.c=-1;
67 a b c a b c
68 1 -1 -1 1 -1 -1
69 1 -1 -1 1 -1 -1
70 explain select * from t1,t3 where t1.c=t3.c and t3.c=-1;
71 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
72 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref c c 5 const 1 100.00 NULL
73 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref c c 5 const 3 100.00 NULL
74 Warnings:
75 Note 1003 Push of table 't1' as scan-child with lookup-root 't3' not implemented
76 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`c` = -(1)) and (`test`.`t1`.`c` = -(1)))
77 # select_type=PRIMARY, type=index,ALL
78 select * from t1 where b in (select c from t3);
79 a b c
80 1 -1 -1
81 1 -1 -1
82 2 -2 -2
83 3 -3 -3
84 explain select * from t1 where b in (select c from t3);
85 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
86 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 Using where
87 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref c c 5 test.t1.b 1 100.00 NULL
88 Warnings:
89 Note 1003 Can't push condition on virtual generated column 't1.b'
90 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t3` join `test`.`t1` where (`test`.`t3`.`c` = `test`.`t1`.`b`)
91 # select_type=PRIMARY, type=range,ref
92 select * from t1 where c in (select c from t3 where c between -2 and -1);
93 a b c
94 1 -1 -1
95 1 -1 -1
96 2 -2 -2
97 explain select * from t1 where c in (select c from t3 where c between -2 and -1);
98 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
99 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 range c c 5 NULL 3 100.00 Parent of 2 pushed join@1; Using pushed condition ((`test`.`t1`.`c` between <cache>(-(2)) and <cache>(-(1))) and (`test`.`t1`.`c` is not null)); Using MRR
100 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 eq_ref c c 5 test.t1.c 1 100.00 Child of 't1' in pushed join@1
101 Warnings:
102 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t3` join `test`.`t1` where ((`test`.`t3`.`c` = `test`.`t1`.`c`) and (`test`.`t1`.`c` between <cache>(-(2)) and <cache>(-(1))))
103 # select_type=UNION, type=system
104 # select_type=UNION RESULT, type=<union1,2>
105 select * from t1 union select * from t2;
106 a b c
107 1 -1 -1
108 2 -2 -2
109 3 -3 -3
110 NULL NULL NULL
111 explain select * from t1 union select * from t2;
112 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
113 1 PRIMARY t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 NULL
114 2 UNION t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 NULL
115 3 UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
116 Warnings:
117 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2`
118 # select_type=DERIVED, type=system
119 select * from (select a,b,c from t1) as t11;
120 a b c
121 1 -1 -1
122 1 -1 -1
123 2 -2 -2
124 3 -3 -3
125 NULL NULL NULL
126 explain select * from (select a,b,c from t1) as t11;
127 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
128 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 5 100.00 NULL
129 2 DERIVED t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 NULL
130 Warnings:
131 Note 1003 /* select#1 */ select `t11`.`a` AS `a`,`t11`.`b` AS `b`,`t11`.`c` AS `c` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1`) `t11`
132 ###
133 ### Using aggregate functions with/without DISTINCT
134 ###
135 # SELECT COUNT(*) FROM tbl_name
136 select count(*) from t1;
137 count(*)
138 5
139 explain select count(*) from t1;
140 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
141 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
142 Warnings:
143 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1`
144 # SELECT COUNT(DISTINCT <non-gcol>) FROM tbl_name
145 select count(distinct a) from t1;
146 count(distinct a)
147 3
148 explain select count(distinct a) from t1;
149 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
150 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 NULL
151 Warnings:
152 Note 1003 /* select#1 */ select count(distinct `test`.`t1`.`a`) AS `count(distinct a)` from `test`.`t1`
153 # SELECT COUNT(DISTINCT <non-stored gcol>) FROM tbl_name
154 select count(distinct b) from t1;
155 count(distinct b)
156 3
157 explain select count(distinct b) from t1;
158 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
159 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 NULL
160 Warnings:
161 Note 1003 /* select#1 */ select count(distinct `test`.`t1`.`b`) AS `count(distinct b)` from `test`.`t1`
162 # SELECT COUNT(DISTINCT <stored gcol>) FROM tbl_name
163 select count(distinct c) from t1;
164 count(distinct c)
165 3
166 explain select count(distinct c) from t1;
167 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
168 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL c NULL NULL NULL 5 100.00 NULL
169 Warnings:
170 Note 1003 /* select#1 */ select count(distinct `test`.`t1`.`c`) AS `count(distinct c)` from `test`.`t1`
171 ###
172 ### filesort & range-based utils
173 ###
174 # SELECT * FROM tbl_name WHERE <gcol expr>
175 select * from t3 where c >= -2;
176 a b c
177 1 -1 -1
178 2 -2 -2
179 explain select * from t3 where c >= -2;
180 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
181 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 range c c 5 NULL 3 100.00 Using pushed condition (`test`.`t3`.`c` >= <cache>(-(2))); Using MRR
182 Warnings:
183 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`c` >= <cache>(-(2)))
184 # SELECT * FROM tbl_name WHERE <non-gcol expr>
185 select * from t3 where a between 1 and 2;
186 a b c
187 1 -1 -1
188 2 -2 -2
189 explain select * from t3 where a between 1 and 2;
190 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
191 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY PRIMARY 4 NULL 3 100.00 Using pushed condition (`test`.`t3`.`a` between 1 and 2); Using MRR
192 Warnings:
193 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`a` between 1 and 2)
194 # SELECT * FROM tbl_name WHERE <non-indexed gcol expr>
195 select * from t3 where b between -2 and -1;
196 a b c
197 1 -1 -1
198 2 -2 -2
199 explain select * from t3 where b between -2 and -1;
200 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
201 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 33.33 Using where
202 Warnings:
203 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`b` between <cache>(-(2)) and <cache>(-(1)))
204 # SELECT * FROM tbl_name WHERE <indexed gcol expr>
205 select * from t3 where c between -2 and -1;
206 a b c
207 1 -1 -1
208 2 -2 -2
209 explain select * from t3 where c between -2 and -1;
210 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
211 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 range c c 5 NULL 3 100.00 Using pushed condition (`test`.`t3`.`c` between <cache>(-(2)) and <cache>(-(1))); Using MRR
212 Warnings:
213 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`c` between <cache>(-(2)) and <cache>(-(1)))
214 # SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <non-indexed gcol>
215 select * from t3 where a between 1 and 2 order by b;
216 a b c
217 2 -2 -2
218 1 -1 -1
219 explain select * from t3 where a between 1 and 2 order by b;
220 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
221 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY PRIMARY 4 NULL 3 100.00 Using pushed condition (`test`.`t3`.`a` between 1 and 2); Using MRR; Using filesort
222 Warnings:
223 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`a` between 1 and 2) order by `test`.`t3`.`b`
224 # bug#20022189: WL411:DEBUG ASSERT AT FIELD_LONG::VAL_INT IN SQL/FIELD.CC
225 # SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <non-indexed stored gcol>
226 select * from t3 where a between 1 and 2 order by c;
227 a b c
228 2 -2 -2
229 1 -1 -1
230 explain select * from t3 where a between 1 and 2 order by c;
231 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
232 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY PRIMARY 4 NULL 3 100.00 Using pushed condition (`test`.`t3`.`a` between 1 and 2); Using MRR; Using filesort
233 Warnings:
234 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`a` between 1 and 2) order by `test`.`t3`.`c`
235 # bug#20022189: WL411:DEBUG ASSERT AT FIELD_LONG::VAL_INT IN SQL/FIELD.CC
236 CREATE TABLE t4 (
237 `pk` int(11) NOT NULL ,
238 `col_int_nokey` int(11) GENERATED ALWAYS AS (pk + col_int_key) STORED,
239 `col_int_key` int(11) DEFAULT NULL,
240 `col_date_nokey` date DEFAULT NULL,
241 `col_datetime_key` datetime DEFAULT NULL,
242 PRIMARY KEY (`pk`),
243 KEY `col_int_key` (`col_int_key`),
244 KEY `col_datetime_key` (`col_datetime_key`)
245 );
246 Warnings:
247 Warning 1681 Integer display width is deprecated and will be removed in a future release.
248 Warning 1681 Integer display width is deprecated and will be removed in a future release.
249 Warning 1681 Integer display width is deprecated and will be removed in a future release.
250 INSERT INTO t4 VALUES
251 (1,default,4,'2008-12-05','1900-01-01 00:00:00');
252 SELECT
253 SQL_BIG_RESULT
254 GRANDPARENT1 . `col_int_nokey` AS g1
255 FROM t4 AS GRANDPARENT1 LEFT JOIN t4 AS GRANDPARENT2 ON ( GRANDPARENT2 .
256 `col_datetime_key` <= GRANDPARENT1 . `col_date_nokey` )
257 GROUP BY GRANDPARENT1 . `pk`;
258 g1
259 5
260 DROP TABLE t4;
261 # SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <indexed gcol>
262 select * from t3 where a between 1 and 2 order by c;
263 a b c
264 2 -2 -2
265 1 -1 -1
266 explain select * from t3 where a between 1 and 2 order by c;
267 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
268 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 range PRIMARY PRIMARY 4 NULL 3 100.00 Using pushed condition (`test`.`t3`.`a` between 1 and 2); Using MRR; Using filesort
269 Warnings:
270 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`a` between 1 and 2) order by `test`.`t3`.`c`
271 # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-gcol>
272 select * from t3 where b between -2 and -1 order by a;
273 a b c
274 1 -1 -1
275 2 -2 -2
276 explain select * from t3 where b between -2 and -1 order by a;
277 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
278 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 index NULL PRIMARY 4 NULL 3 33.33 Using where
279 Warnings:
280 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`b` between <cache>(-(2)) and <cache>(-(1))) order by `test`.`t3`.`a`
281 # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-gcol>
282 select * from t3 where c between -2 and -1 order by a;
283 a b c
284 1 -1 -1
285 2 -2 -2
286 explain select * from t3 where c between -2 and -1 order by a;
287 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
288 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 index c PRIMARY 4 NULL 3 100.00 Using pushed condition (`test`.`t3`.`c` between <cache>(-(2)) and <cache>(-(1)))
289 Warnings:
290 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`c` between <cache>(-(2)) and <cache>(-(1))) order by `test`.`t3`.`a`
291 # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-indexed gcol>
292 select * from t3 where b between -2 and -1 order by b;
293 a b c
294 2 -2 -2
295 1 -1 -1
296 explain select * from t3 where b between -2 and -1 order by b;
297 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
298 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 33.33 Using where; Using filesort
299 Warnings:
300 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`b` between <cache>(-(2)) and <cache>(-(1))) order by `test`.`t3`.`b`
301 # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-indexed gcol>
302 select * from t3 where c between -2 and -1 order by b;
303 a b c
304 2 -2 -2
305 1 -1 -1
306 explain select * from t3 where c between -2 and -1 order by b;
307 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
308 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 range c c 5 NULL 3 100.00 Using pushed condition (`test`.`t3`.`c` between <cache>(-(2)) and <cache>(-(1))); Using MRR; Using filesort
309 Warnings:
310 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`c` between <cache>(-(2)) and <cache>(-(1))) order by `test`.`t3`.`b`
311 # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <indexed gcol>
312 select * from t3 where b between -2 and -1 order by c;
313 a b c
314 2 -2 -2
315 1 -1 -1
316 explain select * from t3 where b between -2 and -1 order by c;
317 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
318 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 33.33 Using where; Using filesort
319 Warnings:
320 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`b` between <cache>(-(2)) and <cache>(-(1))) order by `test`.`t3`.`c`
321 # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <indexed gcol>
322 select * from t3 where c between -2 and -1 order by c;
323 a b c
324 2 -2 -2
325 1 -1 -1
326 explain select * from t3 where c between -2 and -1 order by c;
327 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
328 1 SIMPLE t3 p0,p1,p2,p3,p4,p5,p6,p7 range c c 5 NULL 3 100.00 Using pushed condition (`test`.`t3`.`c` between <cache>(-(2)) and <cache>(-(1))); Using MRR
329 Warnings:
330 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t3` where (`test`.`t3`.`c` between <cache>(-(2)) and <cache>(-(1))) order by `test`.`t3`.`c`
331 # SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol>
332 select sum(b) from t1 group by b;
333 sum(b)
334 -2
335 -2
336 -3
337 NULL
338 explain select sum(b) from t1 group by b;
339 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
340 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 Using temporary
341 Warnings:
342 Note 1003 /* select#1 */ select sum(`test`.`t1`.`b`) AS `sum(b)` from `test`.`t1` group by `test`.`t1`.`b`
343 # SELECT sum(<indexed gcol>) FROM tbl_name GROUP BY <indexed gcol>
344 select sum(c) from t1 group by c;
345 sum(c)
346 NULL
347 -3
348 -2
349 -2
350 explain select sum(c) from t1 group by c;
351 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
352 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index c c 5 NULL 5 100.00 NULL
353 Warnings:
354 Note 1003 /* select#1 */ select sum(`test`.`t1`.`c`) AS `sum(c)` from `test`.`t1` group by `test`.`t1`.`c`
355 # SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <indexed gcol>
356 select sum(b) from t1 group by c;
357 sum(b)
358 NULL
359 -3
360 -2
361 -2
362 explain select sum(b) from t1 group by c;
363 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
364 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index c c 5 NULL 5 100.00 NULL
365 Warnings:
366 Note 1003 /* select#1 */ select sum(`test`.`t1`.`b`) AS `sum(b)` from `test`.`t1` group by `test`.`t1`.`c`
367 # SELECT sum(<indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol>
368 select sum(c) from t1 group by b;
369 sum(c)
370 -2
371 -2
372 -3
373 NULL
374 explain select sum(c) from t1 group by b;
375 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
376 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 Using temporary
377 Warnings:
378 Note 1003 /* select#1 */ select sum(`test`.`t1`.`c`) AS `sum(c)` from `test`.`t1` group by `test`.`t1`.`b`
379 drop table t1;
380 #
381 # Bug#20241655: WL411:FAILING ASSERTION ASSERTION
382 #
383 CREATE TABLE BB (
384 col_time_key time NOT NULL,
385 col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL,
386 col_datetime_key datetime NOT NULL);
387 INSERT INTO BB VALUES('23:28:02', default, '2005-03-15 22:48:25');
388 CREATE TABLE CC (
389 col_time_key time NOT NULL,
390 col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL,
391 col_datetime_key datetime NOT NULL
392 );
393 INSERT INTO CC VALUES('16:22:51', default, '1900-01-01 00:00:00');
394 SELECT 1 AS g1 FROM BB AS gp1 LEFT JOIN BB AS gp2 USING ( col_time_nokey);
395 g1
396 1
397 DROP TABLE BB, CC;
398 #
399 # Bug#20328786: WL411:VALGRIND WARNINGS OF CONDITIONAL
400 # JUMP WHILE SELECTING FROM VIEW
401 #
402 CREATE TABLE A (
403 pk INTEGER AUTO_INCREMENT,
404 col_int_nokey INTEGER,
405 col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED,
406 PRIMARY KEY (pk)
407 );
408 CREATE TABLE C (
409 pk INTEGER AUTO_INCREMENT,
410 col_int_nokey INTEGER,
411 col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED,
412 col_varchar_nokey VARCHAR(1),
413 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
414 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED,
415 PRIMARY KEY (pk),
416 KEY (col_int_key),
417 KEY (col_varchar_key, col_int_key)
418 );
419 INSERT INTO C (
420 col_int_nokey,
421 col_varchar_nokey
422 ) VALUES (4, 'v'),(62, 'v'),(7, 'c'),(1, NULL),(0, 'x'),(7, 'i'),(7, 'e'),(1, 'p'),(7, 's'),(1, 'j'),(5, 'z'),(2, 'c'),(0, 'a'),(1, 'q'),(8, 'y'),(1, NULL),(1, 'r'),(9, 'v'),(1, NULL),(5, 'r');
423 CREATE OR REPLACE ALGORITHM=MERGE VIEW V1 AS SELECT alias1.
424 col_varchar_key AS field1 , alias1.pk AS field2, alias2.
425 col_int_nokey AS field3 FROM C AS alias1 LEFT JOIN A AS alias2 ON
426 alias1.pk = alias2.col_int_key WHERE alias1.pk > 8 AND alias1
427 .pk < ( 9 + 2 ) AND alias1.col_int_key <> 1 OR alias1.col_int_key
428 > 0 AND alias1.col_int_key <= ( 3 + 2 ) ORDER BY field1, field2, field3
429 LIMIT 100 OFFSET 6;
430 Warnings:
431 Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm)
432 SELECT * FROM V1;
433 field1 field2 field3
434 qq 14 NULL
435 rr 17 NULL
436 ss 9 NULL
437 xx 5 NULL
438 DROP VIEW V1;
439 DROP TABLE A,C;
440 #
441 # Bug#20406510: WL411:VALGRIND WARNINGS WITH
442 # COUNT DISTINCT QUERY ON VIRTUAL GC VARCHAR COLUMN
443 #
444 CREATE TABLE A (
445 pk INTEGER AUTO_INCREMENT,
446 col_time_key TIME NOT NULL,
447 col_datetime_key DATETIME NOT NULL,
448 PRIMARY KEY (pk),
449 KEY (col_time_key),
450 KEY (col_datetime_key)
451 );
452 CREATE TABLE C (
453 pk INTEGER AUTO_INCREMENT,
454 col_int_key INTEGER NOT NULL,
455 col_varchar_key VARCHAR(1) NOT NULL,
456 col_varchar_nokey VARCHAR(2) GENERATED ALWAYS AS
457 (CONCAT(col_varchar_key, col_varchar_key)),
458 PRIMARY KEY (pk),
459 KEY (col_int_key),
460 KEY (col_varchar_key, col_int_key)
461 );
462 INSERT INTO C (col_int_key,col_varchar_key) VALUES (0, 'j'),(8, 'v'),(1, 'c'),(8, 'm'),(9, 'd');
463 SELECT MIN( alias2 . col_int_key ) AS field1,
464 COUNT( DISTINCT alias2 . col_varchar_nokey ) AS field2
465 FROM ( A AS alias1 , C AS alias2 )
466 ORDER BY alias1.col_time_key, alias1.col_datetime_key, alias1.pk ASC;
467 field1 field2
468 NULL 0
469 DROP TABLE A,C;
470 #
471 # Bug#20566325: WL8149: INNODB: FAILING ASSERTION:
472 # COL_NR < TABLE->N_DEF
473 #
474 CREATE TABLE A (
475 pk INTEGER AUTO_INCREMENT,
476 col_varchar_nokey VARCHAR(1) NOT NULL,
477 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
478 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
479 PRIMARY KEY (pk)
480 );
481 INSERT /*! IGNORE */ INTO A (col_varchar_nokey) VALUES ('k');
482 CREATE TABLE CC (
483 pk INTEGER AUTO_INCREMENT,
484 col_datetime_nokey DATETIME /*! NULL */,
485 col_time_nokey TIME /*! NULL */,
486 col_time_key TIME GENERATED ALWAYS AS
487 (ADDTIME(col_datetime_nokey, col_time_nokey)),
488 col_varchar_nokey VARCHAR(1) /*! NULL */,
489 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
490 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
491 PRIMARY KEY (pk));
492 INSERT INTO CC (col_time_nokey,col_datetime_nokey,col_varchar_nokey) VALUES
493 ('13:06:13.033877','1900-01-01 00:00:00', 'p'),
494 (NULL, '2007-05-25 11:58:54.015689', 'g');
495 SELECT
496 table1.col_time_key AS field1,
497 'z' AS field2
498 FROM
499 (CC AS table1 LEFT OUTER JOIN (A AS table2 STRAIGHT_JOIN CC AS table3 ON
500 (table3.col_varchar_key = table2.col_varchar_nokey)) ON
501 (table3.col_varchar_key = table2.col_varchar_nokey))
502 WHERE
503 table2.pk != 6
504 AND table1.col_varchar_key IN ('l', 's' , 'b' )
505 AND table3.col_varchar_key != table1.col_varchar_key
506 ORDER BY table1.col_varchar_key , field1 , field2;
507 field1 field2
508 DROP TABLE A,CC;
509 CREATE TABLE cc (
510 pk int(11) NOT NULL AUTO_INCREMENT,
511 col_int_nokey int(11) NOT NULL,
512 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) STORED,
513 col_date_nokey date NOT NULL,
514 col_date_key date GENERATED ALWAYS AS (col_date_nokey) STORED,
515 col_datetime_nokey datetime NOT NULL,
516 col_time_nokey time NOT NULL,
517 col_datetime_key datetime GENERATED ALWAYS AS (col_datetime_nokey)STORED,
518 col_time_key time GENERATED ALWAYS AS (col_time_nokey) STORED,
519 col_varchar_nokey varchar(1) NOT NULL,
520 col_varchar_key varchar(1) GENERATED ALWAYS AS (col_varchar_nokey)STORED,
521 PRIMARY KEY (pk),
522 KEY gc_idx1 (col_int_key),
523 KEY gc_idx2 (col_varchar_key),
524 KEY gc_idx3 (col_date_key),
525 KEY gc_idx4 (col_time_key),
526 KEY gc_idx5 (col_datetime_key),
527 KEY gc_idx6 (col_varchar_key,col_int_key),
528 KEY gc_idx7 (col_date_key,col_datetime_key,col_time_key),
529 KEY gc_idx8(col_int_key,col_varchar_key,col_date_key,col_time_key,
530 col_datetime_key)
531 );
532 Warnings:
533 Warning 1681 Integer display width is deprecated and will be removed in a future release.
534 Warning 1681 Integer display width is deprecated and will be removed in a future release.
535 Warning 1681 Integer display width is deprecated and will be removed in a future release.
536 INSERT INTO cc (
537 col_int_nokey,
538 col_date_nokey,
539 col_time_nokey,
540 col_datetime_nokey,
541 col_varchar_nokey
542 ) VALUES (1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'),
543 (8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'),
544 (9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'),
545 (24, '2000-10-08', '10:14:58.018534', '2006-10-12 04:32:53.031976', 'd'),
546 (6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'),
547 (1, '2008-01-23', '11:14:24.032949', '2004-10-02 20:31:15.022553', 't');
548 SET @save_old_sql_mode= @@sql_mode;
549 SET sql_mode="";
550 SELECT DISTINCT alias1.col_varchar_key AS field1
551 FROM ( cc AS alias1 STRAIGHT_JOIN
552 (( cc AS alias2 STRAIGHT_JOIN cc AS alias3 ON
553 (alias3.col_varchar_key > alias2.col_varchar_key ) ) ) ON
554 (( alias3 .pk >= alias2.col_int_nokey ) AND
555 (alias3 .pk >= alias2.col_int_nokey ) ))
556 WHERE alias1.col_varchar_key <= 'v'
557 GROUP BY field1 HAVING field1 = 91
558 ORDER BY field1, alias1.col_date_key, field1 ASC, field1 DESC,
559 alias1.col_time_key ASC, field1;
560 field1
561 DROP TABLE cc;
562 SET sql_mode=@save_old_sql_mode;
563 #
564 # Bug#20797941: WL8149:ASSERTION !TABLE ||
565 # (!TABLE->READ_SET || BITMAP_IS_SET(TABLE->READ_SET
566 #
567 CREATE TABLE t(a int, b int as(a+1));
568 INSERT INTO t(a) values(1),(2);
569 SELECT * FROM t ORDER BY b;
570 a b
571 1 2
572 2 3
573 DROP TABLE t;
574 DROP TABLE t2, t3;
575 #
576 # Bug#21317507:GC: STORED COLUMN REJECTED, BUT VIRTUAL IS ACCEPTED
577 #
578 CREATE TABLE t1(a INT);
579 INSERT INTO t1 VALUES(2147483647);
580 ALTER TABLE t1 ADD INDEX idx(a), WITH VALIDATION;
581 ERROR HY000: Incorrect usage of ALTER and WITH VALIDATION
582 DROP TABLE t1;
583 CREATE TABLE t1(a INT);
584 INSERT INTO t1 VALUES(2147483647);
585 ALTER TABLE t1 ADD COLUMN h INT AS (a) VIRTUAL, WITHOUT VALIDATION;
586 ALTER TABLE t1 CHANGE h i INT AS (a) VIRTUAL, WITH VALIDATION,
587 ALGORITHM=INPLACE;
588 Got one of the listed errors
589 ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, WITH VALIDATION,
590 ALGORITHM=INPLACE, LOCK=NONE;
591 Got one of the listed errors
592 ALTER TABLE t1 ADD COLUMN c SMALLINT AS (a) VIRTUAL, WITH VALIDATION,
593 ALGORITHM=INPLACE, LOCK=SHARED;
594 Got one of the listed errors
595 ALTER TABLE t1 ADD COLUMN d SMALLINT AS (a) VIRTUAL, WITH VALIDATION,
596 ALGORITHM=INPLACE, LOCK=EXCLUSIVE;
597 Got one of the listed errors
598 Shouldn't fail on Innodb
599 DROP TABLE t1;
600 #
601 # Bug#21980430 GCOLS: CRASHING
602 #
603 CREATE TABLE t (
604 a INT,
605 b BLOB,
606 c BLOB GENERATED ALWAYS AS (a+b) VIRTUAL,
607 UNIQUE KEY i0008 (a)
608 );
609 Warnings:
610 Warning 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
611 INSERT INTO t(a,b) VALUES(1,'cccc');
612 EXPLAIN SELECT /*+ bka() */ 1 AS c FROM t AS b RIGHT JOIN t AS c ON b.a > c.c
613 WHERE b.b>c.a;
614 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
615 1 SIMPLE b p0,p1,p2,p3,p4,p5,p6,p7 ALL i0008 NULL NULL NULL 2 100.00 NULL
616 1 SIMPLE c p0,p1,p2,p3,p4,p5,p6,p7 ALL i0008 NULL NULL NULL 2 50.00 Range checked for each record (index map: 0x1)
617 Warnings:
618 Note 1003 Table 'b' is not pushable: select list can't contain BLOB columns
619 Note 1003 /* select#1 */ select /*+ BKA(@`select#1`) */ 1 AS `c` from `test`.`t` `c` join `test`.`t` `b` where ((cast(`test`.`b`.`b` as double) > cast(`test`.`c`.`a` as double)) and (cast(`test`.`b`.`a` as double) > cast(`test`.`c`.`c` as double)))
620 SELECT /*+ bka() */ 1 AS c FROM t AS b RIGHT JOIN t AS c ON b.a > c.c
621 WHERE b.b>c.a;
622 c
623 DROP TABLE t;
624 set @optimizer_switch_save = @@optimizer_switch;
625 set optimizer_switch='mrr_cost_based=off';
626 set @read_rnd_buffer_size_save= @@read_rnd_buffer_size;
627 set read_rnd_buffer_size=32;
628 CREATE TABLE t0 (
629 i1 INTEGER NOT NULL
630 );
631 INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
632 CREATE TABLE t1 (
633 pk INTEGER NOT NULL,
634 i1 INTEGER NOT NULL,
635 i2 INTEGER NOT NULL,
636 v1 INTEGER GENERATED ALWAYS AS (i2 + 1) VIRTUAL,
637 v2 INTEGER GENERATED ALWAYS AS (i1 / (i1 - i2 + 57)) VIRTUAL,
638 PRIMARY KEY (pk),
639 INDEX idx(i1)
640 );
641 INSERT INTO t1 (pk, i1, i2)
642 SELECT a0.i1 + a1.i1*10 + a2.i1*100,
643 a0.i1 + a1.i1*10,
644 a0.i1 + a1.i1*10
645 FROM t0 AS a0, t0 AS a1, t0 AS a2;
646 EXPLAIN SELECT * FROM t1
647 WHERE i1 > 41 AND i1 <= 43;
648 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
649 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 range idx idx 4 NULL 50 100.00 Using pushed condition ((`test`.`t1`.`i1` > 41) and (`test`.`t1`.`i1` <= 43)); Using MRR
650 Warnings:
651 Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t1`.`v1` AS `v1`,`test`.`t1`.`v2` AS `v2` from `test`.`t1` where ((`test`.`t1`.`i1` > 41) and (`test`.`t1`.`i1` <= 43))
652 SELECT * FROM t1
653 WHERE i1 > 41 AND i1 <= 43;
654 pk i1 i2 v1 v2
655 142 42 42 43 1
656 143 43 43 44 1
657 242 42 42 43 1
658 243 43 43 44 1
659 342 42 42 43 1
660 343 43 43 44 1
661 42 42 42 43 1
662 43 43 43 44 1
663 442 42 42 43 1
664 443 43 43 44 1
665 542 42 42 43 1
666 543 43 43 44 1
667 642 42 42 43 1
668 643 43 43 44 1
669 742 42 42 43 1
670 743 43 43 44 1
671 842 42 42 43 1
672 843 43 43 44 1
673 942 42 42 43 1
674 943 43 43 44 1
675 EXPLAIN SELECT * FROM t1
676 WHERE v1 > 41 AND v1 <= 43;
677 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
678 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 1000 11.11 Using where
679 Warnings:
680 Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t1`.`v1` AS `v1`,`test`.`t1`.`v2` AS `v2` from `test`.`t1` where ((`test`.`t1`.`v1` > 41) and (`test`.`t1`.`v1` <= 43))
681 SELECT * FROM t1
682 WHERE v1 > 41 AND v1 <= 43;
683 pk i1 i2 v1 v2
684 141 41 41 42 1
685 142 42 42 43 1
686 241 41 41 42 1
687 242 42 42 43 1
688 341 41 41 42 1
689 342 42 42 43 1
690 41 41 41 42 1
691 42 42 42 43 1
692 441 41 41 42 1
693 442 42 42 43 1
694 541 41 41 42 1
695 542 42 42 43 1
696 641 41 41 42 1
697 642 42 42 43 1
698 741 41 41 42 1
699 742 42 42 43 1
700 841 41 41 42 1
701 842 42 42 43 1
702 941 41 41 42 1
703 942 42 42 43 1
704 DROP TABLE t0, t1;
705 set optimizer_switch= @optimizer_switch_save;
706 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
707 #
708 # Bug#21872184 CONDITIONAL JUMP AT JOIN_CACHE::WRITE_RECORD_DATA IN
709 # SQL_JOIN_BUFFER.CC
710 #
711 #
712 # Test 1: Dynamic range scan with one covering index
713 #
714 CREATE TABLE t1 (
715 i1 INTEGER NOT NULL,
716 c1 VARCHAR(1) NOT NULL
717 );
718 INSERT INTO t1
719 VALUES (10, 'c'), (10, 'i'), (2, 't'), (4, 'g');
720 CREATE TABLE t2 (
721 i1 INTEGER NOT NULL,
722 c1 VARCHAR(1) NOT NULL
723 );
724 INSERT INTO t2
725 VALUES (2, 'k'), (9, 'k'), (7, 'o'), (5, 'n'), (7, 'e');
726 CREATE TABLE t3 (
727 pk INTEGER NOT NULL,
728 i1 INTEGER,
729 i2_key INTEGER GENERATED ALWAYS AS (i1 + i1) VIRTUAL NOT NULL,
730 PRIMARY KEY (pk)
731 );
732 INSERT INTO t3 (pk, i1)
733 VALUES (1, 1), (2, 48), (3, 228), (4, 3), (5, 5),
734 (6, 39), (7, 6), (8, 8), (9, 3);
735 CREATE TABLE t4 (
736 i1 INTEGER NOT NULL,
737 c1 VARCHAR(1) NOT NULL
738 );
739 INSERT INTO t4
740 VALUES (1, 'j'), (2, 'c'), (0, 'a');
741 ANALYZE TABLE t1, t2, t3, t4;
742 Table Op Msg_type Msg_text
743 test.t1 analyze status OK
744 test.t2 analyze status OK
745 test.t3 analyze status OK
746 test.t4 analyze status OK
747 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
748 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
749 WHERE ( t3.pk IN
750 (
751 SELECT /*+ QB_NAME(subq1) */ t4.i1
752 FROM t4
753 WHERE t4.c1 < 'o'
754 )
755 )
756 AND t1.i1 <= t3.i2_key;
757 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
758 1 PRIMARY t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 100.00 NULL
759 1 PRIMARY t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 9 33.33 Using where; Using join buffer (hash join)
760 1 PRIMARY t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (hash join)
761 2 SUBQUERY t4 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 33.33 Using pushed condition (`test`.`t4`.`c1` < 'o')
762 Warnings:
763 Note 1003 Can't push table 't3' as child, 'type' must be a 'ref' access
764 Note 1003 Can't push table 't2' as child, 'type' must be a 'ref' access
765 Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`i1` AS `i1` from `test`.`t1` straight_join `test`.`t3` straight_join `test`.`t2` where (<in_optimizer>(`test`.`t3`.`pk`,`test`.`t3`.`pk` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t4`.`i1` from `test`.`t4` where (`test`.`t4`.`c1` < 'o') ), <primary_index_lookup>(`test`.`t3`.`pk` in <temporary table> on <auto_distinct_key> where ((`test`.`t3`.`pk` = `<materialized_subquery>`.`i1`))))) and (`test`.`t1`.`i1` <= `test`.`t3`.`i2_key`))
766 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
767 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
768 WHERE ( t3.pk IN
769 (
770 SELECT /*+ QB_NAME(subq1) */ t4.i1
771 FROM t4
772 WHERE t4.c1 < 'o'
773 )
774 )
775 AND t1.i1 <= t3.i2_key;
776 c1 i1
777 c 2
778 c 5
779 c 7
780 c 7
781 c 9
782 g 2
783 g 5
784 g 7
785 g 7
786 g 9
787 i 2
788 i 5
789 i 7
790 i 7
791 i 9
792 t 2
793 t 2
794 t 5
795 t 5
796 t 7
797 t 7
798 t 7
799 t 7
800 t 9
801 t 9
802 #
803 # Test 2: Two alternative covering indexes for the range scan
804 #
805 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
806 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
807 WHERE ( t3.pk IN
808 (
809 SELECT /*+ QB_NAME(subq1) */ t4.i1
810 FROM t4
811 WHERE t4.c1 < 'o'
812 )
813 )
814 AND t1.i1 <= t3.i2_key;
815 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
816 1 PRIMARY t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 100.00 NULL
817 1 PRIMARY t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 9 33.33 Using where; Using join buffer (hash join)
818 1 PRIMARY t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (hash join)
819 2 SUBQUERY t4 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 33.33 Using pushed condition (`test`.`t4`.`c1` < 'o')
820 Warnings:
821 Note 1003 Can't push table 't3' as child, 'type' must be a 'ref' access
822 Note 1003 Can't push table 't2' as child, 'type' must be a 'ref' access
823 Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`i1` AS `i1` from `test`.`t1` straight_join `test`.`t3` straight_join `test`.`t2` where (<in_optimizer>(`test`.`t3`.`pk`,`test`.`t3`.`pk` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t4`.`i1` from `test`.`t4` where (`test`.`t4`.`c1` < 'o') ), <primary_index_lookup>(`test`.`t3`.`pk` in <temporary table> on <auto_distinct_key> where ((`test`.`t3`.`pk` = `<materialized_subquery>`.`i1`))))) and (`test`.`t1`.`i1` <= `test`.`t3`.`i2_key`))
824 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
825 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
826 WHERE ( t3.pk IN
827 (
828 SELECT /*+ QB_NAME(subq1) */ t4.i1
829 FROM t4
830 WHERE t4.c1 < 'o'
831 )
832 )
833 AND t1.i1 <= t3.i2_key;
834 c1 i1
835 c 2
836 c 5
837 c 7
838 c 7
839 c 9
840 g 2
841 g 5
842 g 7
843 g 7
844 g 9
845 i 2
846 i 5
847 i 7
848 i 7
849 i 9
850 t 2
851 t 2
852 t 5
853 t 5
854 t 7
855 t 7
856 t 7
857 t 7
858 t 9
859 t 9
860 #
861 # Test 3: One covering index including the base column for the virtual
862 # column
863 #
864 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
865 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
866 WHERE ( t3.pk IN
867 (
868 SELECT /*+ QB_NAME(subq1) */ t4.i1
869 FROM t4
870 WHERE t4.c1 < 'o'
871 )
872 )
873 AND t1.i1 <= t3.i2_key;
874 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
875 1 PRIMARY t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 100.00 NULL
876 1 PRIMARY t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 9 33.33 Using where; Using join buffer (hash join)
877 1 PRIMARY t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (hash join)
878 2 SUBQUERY t4 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 33.33 Using pushed condition (`test`.`t4`.`c1` < 'o')
879 Warnings:
880 Note 1003 Can't push table 't3' as child, 'type' must be a 'ref' access
881 Note 1003 Can't push table 't2' as child, 'type' must be a 'ref' access
882 Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`i1` AS `i1` from `test`.`t1` straight_join `test`.`t3` straight_join `test`.`t2` where (<in_optimizer>(`test`.`t3`.`pk`,`test`.`t3`.`pk` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t4`.`i1` from `test`.`t4` where (`test`.`t4`.`c1` < 'o') ), <primary_index_lookup>(`test`.`t3`.`pk` in <temporary table> on <auto_distinct_key> where ((`test`.`t3`.`pk` = `<materialized_subquery>`.`i1`))))) and (`test`.`t1`.`i1` <= `test`.`t3`.`i2_key`))
883 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
884 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
885 WHERE ( t3.pk IN
886 (
887 SELECT /*+ QB_NAME(subq1) */ t4.i1
888 FROM t4
889 WHERE t4.c1 < 'o'
890 )
891 )
892 AND t1.i1 <= t3.i2_key;
893 c1 i1
894 c 2
895 c 5
896 c 7
897 c 7
898 c 9
899 g 2
900 g 5
901 g 7
902 g 7
903 g 9
904 i 2
905 i 5
906 i 7
907 i 7
908 i 9
909 t 2
910 t 2
911 t 5
912 t 5
913 t 7
914 t 7
915 t 7
916 t 7
917 t 9
918 t 9
919 #
920 # Test 4: One non-covering index
921 #
922 # Add more data to the table so that it will run the dynamic range scan
923 # as both table scan and range scan (the purpose of this is to make the
924 # table scan more expensive).
925 INSERT INTO t3 (pk, i1)
926 VALUES (10,1), (11,1), (12,1), (13,1), (14,1),(15,1), (16,1),(17,1), (18,1),
927 (19,1), (20,1), (21,1), (22,1), (23,1), (24,1),(25,1),(26,1),(27,1),
928 (28,1), (29,1);
929 # Change the query to read an extra column (t3.i1) making the index
930 # non-covering.
931 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1, t3.i1
932 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
933 WHERE ( t3.pk IN
934 (
935 SELECT /*+ QB_NAME(subq1) */ t4.i1
936 FROM t4
937 WHERE t4.c1 < 'o'
938 )
939 )
940 AND t1.i1 <= t3.i2_key;
941 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
942 1 PRIMARY t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 100.00 NULL
943 1 PRIMARY t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 29 33.33 Using where; Using join buffer (hash join)
944 1 PRIMARY t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (hash join)
945 2 SUBQUERY t4 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 33.33 Using pushed condition (`test`.`t4`.`c1` < 'o')
946 Warnings:
947 Note 1003 Can't push table 't3' as child, 'type' must be a 'ref' access
948 Note 1003 Can't push table 't2' as child, 'type' must be a 'ref' access
949 Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`i1` AS `i1`,`test`.`t3`.`i1` AS `i1` from `test`.`t1` straight_join `test`.`t3` straight_join `test`.`t2` where (<in_optimizer>(`test`.`t3`.`pk`,`test`.`t3`.`pk` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t4`.`i1` from `test`.`t4` where (`test`.`t4`.`c1` < 'o') ), <primary_index_lookup>(`test`.`t3`.`pk` in <temporary table> on <auto_distinct_key> where ((`test`.`t3`.`pk` = `<materialized_subquery>`.`i1`))))) and (`test`.`t1`.`i1` <= `test`.`t3`.`i2_key`))
950 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1, t3.i1
951 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
952 WHERE ( t3.pk IN
953 (
954 SELECT /*+ QB_NAME(subq1) */ t4.i1
955 FROM t4
956 WHERE t4.c1 < 'o'
957 )
958 )
959 AND t1.i1 <= t3.i2_key;
960 c1 i1 i1
961 c 2 48
962 c 5 48
963 c 7 48
964 c 7 48
965 c 9 48
966 g 2 48
967 g 5 48
968 g 7 48
969 g 7 48
970 g 9 48
971 i 2 48
972 i 5 48
973 i 7 48
974 i 7 48
975 i 9 48
976 t 2 1
977 t 2 48
978 t 5 1
979 t 5 48
980 t 7 1
981 t 7 1
982 t 7 48
983 t 7 48
984 t 9 1
985 t 9 48
986 #
987 # Test 5: Test where the added primary key to secondary indexes is
988 # used after it has been included in the join buffer
989 #
990 EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
991 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
992 WHERE ( t3.pk IN
993 (
994 SELECT /*+ QB_NAME(subq1) */ t4.i1
995 FROM t4
996 WHERE t4.c1 < 'o' and t4.i1 < (t2.i1 + 1)
997 )
998 )
999 AND t1.i1 <= t3.i2_key;
1000 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1001 1 PRIMARY t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 4 100.00 NULL
1002 1 PRIMARY t3 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 29 33.33 Using where; Using join buffer (hash join)
1003 1 PRIMARY t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (hash join)
1004 2 DEPENDENT SUBQUERY t4 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 3 33.33 Using pushed condition ((`test`.`t4`.`c1` < 'o') and (`test`.`t4`.`i1` < (`test`.`t2`.`i1` + 1)) and (<cache>(`test`.`t3`.`pk`) = `test`.`t4`.`i1`))
1005 Warnings:
1006 Note 1276 Field or reference 'test.t2.i1' of SELECT #2 was resolved in SELECT #1
1007 Note 1003 Can't push table 't3' as child, 'type' must be a 'ref' access
1008 Note 1003 Can't push table 't2' as child, 'type' must be a 'ref' access
1009 Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`i1` AS `i1` from `test`.`t1` straight_join `test`.`t3` straight_join `test`.`t2` where (<in_optimizer>(`test`.`t3`.`pk`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t4`.`i1` from `test`.`t4` where ((`test`.`t4`.`c1` < 'o') and (`test`.`t4`.`i1` < (`test`.`t2`.`i1` + 1)) and (<cache>(`test`.`t3`.`pk`) = `test`.`t4`.`i1`)))) and (`test`.`t1`.`i1` <= `test`.`t3`.`i2_key`))
1010 SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1
1011 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2
1012 WHERE ( t3.pk IN
1013 (
1014 SELECT /*+ QB_NAME(subq1) */ t4.i1
1015 FROM t4
1016 WHERE t4.c1 < 'o' and t4.i1 < (t2.i1 + 1)
1017 )
1018 )
1019 AND t1.i1 <= t3.i2_key;
1020 c1 i1
1021 c 2
1022 c 5
1023 c 7
1024 c 7
1025 c 9
1026 g 2
1027 g 5
1028 g 7
1029 g 7
1030 g 9
1031 i 2
1032 i 5
1033 i 7
1034 i 7
1035 i 9
1036 t 2
1037 t 2
1038 t 5
1039 t 5
1040 t 7
1041 t 7
1042 t 7
1043 t 7
1044 t 9
1045 t 9
1046 DROP TABLE t1, t2, t3, t4;
1047 SET optimizer_switch='derived_merge=default';
1048 TESTS FROM gcol_select_ndb_extra_tests.inc
1049 create table t1
1050 (t1_seq_pk int auto_increment not null primary key,
1051 t1_base_col int,
1052 t1_vir_gcol int generated always as (-t1_base_col) virtual,
1053 t1_sto_gcol int generated always as (-t1_base_col) stored,
1054 index (t1_sto_gcol));
1055 insert into t1 (t1_base_col) values (2), (1), (1), (3), (NULL);
1056 create table t2
1057 (t2_pk int primary key,
1058 t2_vir_gcol int generated always as (-t2_pk) virtual,
1059 t2_sto_gcol_unique int generated always as (-t2_pk) stored unique);
1060 insert into t2 (t2_pk) values (1),(2),(3),(4),(5),(6);
1061 create table t3
1062 (t3_base_col int,
1063 t3_sto_gcol int generated always as (-t3_base_col) stored,
1064 t3_vir_gcol int generated always as (-t3_base_col) virtual,
1065 index(t3_sto_gcol)
1066 );
1067 insert into t3 (t3_base_col) values (2), (1), (1), (3), (NULL);
1068 create table t4
1069 (t4_seq_pk int auto_increment not null primary key,
1070 t4_base_col int,
1071 t4_sto_gcol int generated always as (-t4_base_col) stored,
1072 t4_vir_gcol int generated always as (-t4_base_col) virtual,
1073 index (t4_sto_gcol));
1074 insert into t4 (t4_base_col) values (2), (1), (1), (3), (NULL);
1075 create table t5
1076 (t5_seq_pk int auto_increment not null primary key,
1077 t5_sto_gcol int generated always as (-t5_base_col) stored,
1078 t5_vir_gcol int generated always as (-t5_base_col) virtual,
1079 t5_base_col int,
1080 index (t5_sto_gcol));
1081 insert into t5 (t5_base_col) values (2), (1), (1), (3), (NULL);
1082 # TABLE SCAN
1083 select * from t2;
1084 t2_pk t2_vir_gcol t2_sto_gcol_unique
1085 1 -1 -1
1086 2 -2 -2
1087 3 -3 -3
1088 4 -4 -4
1089 5 -5 -5
1090 6 -6 -6
1091 # TABLE SCAN WITH MASK
1092 select t2_pk, t2_vir_gcol from t2;
1093 t2_pk t2_vir_gcol
1094 1 -1
1095 2 -2
1096 3 -3
1097 4 -4
1098 5 -5
1099 6 -6
1100 select t2_sto_gcol_unique from t2;
1101 t2_sto_gcol_unique
1102 -1
1103 -2
1104 -3
1105 -4
1106 -5
1107 -6
1108 # RANGE SCANS
1109 select * from t2 where t2_pk = 2 or t2_pk = 4;
1110 t2_pk t2_vir_gcol t2_sto_gcol_unique
1111 2 -2 -2
1112 4 -4 -4
1113 select t2_vir_gcol from t2 where t2_pk = 2 or t2_pk = 4;
1114 t2_vir_gcol
1115 -2
1116 -4
1117 select * from t2 where t2_sto_gcol_unique = -2 or t2_sto_gcol_unique = -4;
1118 t2_pk t2_vir_gcol t2_sto_gcol_unique
1119 2 -2 -2
1120 4 -4 -4
1121 select t2_vir_gcol from t2 where t2_sto_gcol_unique = -2 or t2_sto_gcol_unique = -4;
1122 t2_vir_gcol
1123 -2
1124 -4
1125 # Join t1 to t2
1126 select t1_base_col from t1
1127 join t2 on (t1_sto_gcol = t2_sto_gcol_unique)
1128 where t2_sto_gcol_unique between -2 and -1 ;
1129 t1_base_col
1130 1
1131 1
1132 2
1133 # As above but join t3 to t2
1134 select t3_base_col from t3
1135 join t2 on (t3_sto_gcol = t2_sto_gcol_unique)
1136 where t2_sto_gcol_unique between -2 and -1 ;
1137 t3_base_col
1138 1
1139 1
1140 2
1141 # Join t4 to t2
1142 select t4_base_col from t4
1143 join t2 on (t4_sto_gcol = t2_sto_gcol_unique)
1144 where t2_sto_gcol_unique between -2 and -1 ;
1145 t4_base_col
1146 1
1147 1
1148 2
1149 # Join t5 to t2
1150 select t5_base_col from t5
1151 join t2 on (t5_sto_gcol = t2_sto_gcol_unique)
1152 where t2_sto_gcol_unique between -2 and -1 ;
1153 t5_base_col
1154 1
1155 1
1156 2
1157 # Subquery transformed to semi-join
1158 select * from t1
1159 where t1_sto_gcol in
1160 (select t2_sto_gcol_unique from t2
1161 where t2_sto_gcol_unique between -2 and -1);
1162 t1_seq_pk t1_base_col t1_vir_gcol t1_sto_gcol
1163 1 2 -2 -2
1164 2 1 -1 -1
1165 3 1 -1 -1
1166 drop table t4;
1167 drop table t5;
1168 DROP VIEW IF EXISTS v1,v2;
1169 DROP TABLE IF EXISTS t1,t2,t3;
1170 DROP PROCEDURE IF EXISTS p1;
1171 DROP FUNCTION IF EXISTS f1;
1172 DROP TRIGGER IF EXISTS trg1;
1173 DROP TRIGGER IF EXISTS trg2;
1174 set sql_warnings = 0;