Debian Codegraph @

mysql-test/suite/versioning/r/select.result

raw
1 SET @saved_stats_persistent = @@GLOBAL.innodb_stats_persistent;
2 SET GLOBAL innodb_stats_persistent = OFF;
3 create or replace table t1 (
4 x int unsigned,
5 y int unsigned,
6 sys_trx_start SYS_DATATYPE as row start invisible,
7 sys_trx_end SYS_DATATYPE as row end invisible,
8 period for system_time (sys_trx_start, sys_trx_end)
9 ) with system versioning;
10 insert into t1 (x, y) values
11 (0, 100),
12 (1, 101),
13 (2, 102),
14 (3, 103),
15 (4, 104),
16 (5, 105),
17 (6, 106),
18 (7, 107),
19 (8, 108),
20 (9, 109);
21 set @t0= now(6);
22 delete from t1 where x = 3;
23 delete from t1 where x > 7;
24 insert into t1(x, y) values(3, 33);
25 select sys_trx_start from t1 where x = 3 and y = 33 into @t1;
26 select x, y from t1;
27 x y
28 0 100
29 1 101
30 2 102
31 4 104
32 5 105
33 6 106
34 7 107
35 3 33
36 select x as ASOF_x, y from t1 for system_time as of timestamp @t0;
37 ASOF_x y
38 0 100
39 1 101
40 2 102
41 3 103
42 4 104
43 5 105
44 6 106
45 7 107
46 8 108
47 9 109
48 select x as FROMTO_x, y from t1 for system_time from timestamp '1970-01-01 00:00:00' to timestamp @t1;
49 FROMTO_x y
50 0 100
51 1 101
52 2 102
53 3 103
54 4 104
55 5 105
56 6 106
57 7 107
58 8 108
59 9 109
60 select x as BETWAND_x, y from t1 for system_time between timestamp '1970-01-01 00:00:00' and timestamp @t1;
61 BETWAND_x y
62 0 100
63 1 101
64 2 102
65 3 103
66 4 104
67 5 105
68 6 106
69 7 107
70 8 108
71 9 109
72 3 33
73 select x as ALL_x, y from t1 for system_time all;
74 ALL_x y
75 0 100
76 1 101
77 2 102
78 3 103
79 4 104
80 5 105
81 6 106
82 7 107
83 8 108
84 9 109
85 3 33
86 ASOF2_x y
87 0 100
88 1 101
89 2 102
90 3 103
91 4 104
92 5 105
93 6 106
94 7 107
95 8 108
96 9 109
97 FROMTO2_x y
98 0 100
99 1 101
100 2 102
101 3 103
102 4 104
103 5 105
104 6 106
105 7 107
106 8 108
107 9 109
108 BETWAND2_x y
109 0 100
110 1 101
111 2 102
112 3 103
113 4 104
114 5 105
115 6 106
116 7 107
117 8 108
118 9 109
119 3 33
120 create or replace table t1 (
121 x int unsigned,
122 y int unsigned
123 ) with system versioning;
124 create or replace table t2 (
125 x int unsigned,
126 y int unsigned
127 ) with system versioning;
128 insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5);
129 insert into t2 values (1, 2), (2, 1), (3, 1);
130 set @t0= now(6);
131 select t1.x as IJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x;
132 IJ1_x1 y1 x2 y2
133 1 1 1 2
134 1 2 1 2
135 1 3 1 2
136 select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x;
137 LJ1_x1 y1 x2 y2
138 1 1 1 2
139 1 2 1 2
140 1 3 1 2
141 4 4 NULL NULL
142 5 5 NULL NULL
143 select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x;
144 RJ1_x1 y1 x2 y2
145 1 1 1 2
146 1 2 1 2
147 1 3 1 2
148 NULL NULL 2 1
149 NULL NULL 3 1
150 delete from t1;
151 delete from t2;
152 explain extended select * from (select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x)
153 for system_time as of timestamp @t0 as t;
154 id select_type table type possible_keys key key_len ref rows filtered Extra
155 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where
156 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
157 Warnings:
158 Note 1003 select `test`.`t1`.`x` AS `IJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME AS OF TIMESTAMP @`t0` join `test`.`t2` FOR SYSTEM_TIME AS OF TIMESTAMP @`t0` where `test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t2`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`t2`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`t1`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`t1`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu'
159 explain extended select * from (select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x)
160 for system_time as of timestamp @t0 as t;
161 id select_type table type possible_keys key key_len ref rows filtered Extra
162 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where
163 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
164 Warnings:
165 Note 1003 select `test`.`t1`.`x` AS `LJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME AS OF TIMESTAMP @`t0` left join `test`.`t2` FOR SYSTEM_TIME AS OF TIMESTAMP @`t0` on(`test`.`t2`.`x` = `test`.`t1`.`x` and `test`.`t2`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`t2`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu') where `test`.`t1`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`t1`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu'
166 explain extended select * from (select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x)
167 for system_time as of timestamp @t0 as t;
168 id select_type table type possible_keys key key_len ref rows filtered Extra
169 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where
170 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
171 Warnings:
172 Note 1003 select `test`.`t1`.`x` AS `RJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t2` FOR SYSTEM_TIME AS OF TIMESTAMP @`t0` left join `test`.`t1` FOR SYSTEM_TIME AS OF TIMESTAMP @`t0` on(`test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t1`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`t1`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu') where `test`.`t2`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`t2`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu'
173 select * from (select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x)
174 for system_time as of timestamp @t0 as t;
175 IJ2_x1 y1 x2 y2
176 1 1 1 2
177 1 2 1 2
178 1 3 1 2
179 select * from (select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x)
180 for system_time as of timestamp @t0 as t;
181 LJ2_x1 y1 x2 y2
182 1 1 1 2
183 1 2 1 2
184 1 3 1 2
185 4 4 NULL NULL
186 5 5 NULL NULL
187 select * from (select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x)
188 for system_time as of timestamp @t0 as t;
189 RJ2_x1 y1 x2 y2
190 1 1 1 2
191 1 2 1 2
192 1 3 1 2
193 NULL NULL 2 1
194 NULL NULL 3 1
195 drop table t1;
196 drop table t2;
197 create or replace table t1(x int) with system versioning;
198 insert into t1 values (1);
199 delete from t1;
200 insert into t1 values (2);
201 delete from t1;
202 insert into t1 values (3);
203 delete from t1;
204 select row_start into @start1 from t1 for system_time all where x = 1;
205 select row_end into @end1 from t1 for system_time all where x = 1;
206 select row_start into @start2 from t1 for system_time all where x = 2;
207 select row_end into @end2 from t1 for system_time all where x = 2;
208 select row_start into @start3 from t1 for system_time all where x = 3;
209 select row_end into @end3 from t1 for system_time all where x = 3;
210 select x as ASOF_x from t1 for system_time as of @start2;
211 ASOF_x
212 2
213 select x as ASOF_x from t1 for system_time as of @end2;
214 ASOF_x
215 select x as FROMTO_x from t1 for system_time from @start1 to @end3;
216 FROMTO_x
217 1
218 2
219 3
220 select x as FROMTO_x from t1 for system_time from @end1 to @start2;
221 FROMTO_x
222 select x as BETWAND_x from t1 for system_time between @start1 and @end3;
223 BETWAND_x
224 1
225 2
226 3
227 select x as BETWAND_x from t1 for system_time between @end1 and @start2;
228 BETWAND_x
229 2
230 drop table t1;
231 create table t1(
232 A int
233 ) with system versioning;
234 insert into t1 values(1);
235 select * from t1;
236 A
237 1
238 create or replace table t1 (x int);
239 insert into t1 values (1);
240 select * from t1 for system_time all;
241 ERROR HY000: Table `t1` is not system-versioned
242 create or replace table t1 (x int) with system versioning;
243 insert into t1 values (1);
244 select * from t1 for system_time all for update;
245 ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
246 create or replace table t1 (a int not null auto_increment primary key) with system versioning;
247 select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1;
248 a
249 create or replace table t1 (a int) with system versioning;
250 create or replace table t2 (a int) with system versioning;
251 insert into t1 values(1);
252 insert into t2 values(1);
253 create view v1 as select * from t2 inner join t1 using (a);
254 select * from v1;
255 a
256 1
257 drop view v1;
258 create or replace table t1 (a int) with system versioning;
259 insert into t1 values (1);
260 create view vt1 as select a from t1;
261 select * from t1 natural join vt1;
262 a
263 1
264 drop view vt1;
265 create or replace table t1(x int) with system versioning;
266 select * from (t1 as r left join t1 as u using (x)), t1;
267 x x
268 set @@time_zone="+00:00";
269 create or replace table t1 (a int) with system versioning;
270 insert into t1 values (1);
271 create trigger read_end after update on t1
272 for each row set @end = old.row_end;
273 update t1 set a=2;
274 select @end;
275 @end
276 MAX_RESULT
277 set @@time_zone=default;
278 create or replace table t1 (a int) with system versioning;
279 create or replace table t2 (b int) with system versioning;
280 insert into t1 values (1);
281 insert into t2 values (2);
282 select * from (select * from t1 cross join t2) as tmp;
283 a b
284 1 2
285 select * from (select * from (select * from t1 cross join t2) as tmp1) as tmp2;
286 a b
287 1 2
288 select * from (select * from t1 cross join t2 for system_time as of timestamp ('1970-01-01 00:00:00')) as tmp;
289 a b
290 create or replace table t1(a1 int) with system versioning;
291 create or replace table t2(a2 int) with system versioning;
292 insert into t1 values(1),(2);
293 insert into t2 values(1),(2);
294 select * from t1 for system_time all natural left join t2 for system_time all;
295 a1 a2
296 1 1
297 2 1
298 1 2
299 2 2
300 create or replace table t1(a1 int) with system versioning;
301 create or replace table t2(a2 int) with system versioning;
302 insert into t1 values(1),(2);
303 insert into t2 values(1),(2);
304 create or replace view v1 as select a1 from t1;
305 select * from v1 natural join t2;
306 a1 a2
307 1 1
308 2 1
309 1 2
310 2 2
311 select * from v1 natural left join t2;
312 a1 a2
313 1 1
314 2 1
315 1 2
316 2 2
317 select * from v1 natural right join t2;
318 a2 a1
319 1 1
320 2 1
321 1 2
322 2 2
323 create or replace table t1 (a int) with system versioning;
324 insert into t1 values (1);
325 insert into t1 values (2);
326 insert into t1 values (3);
327 explain extended
328 select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
329 id select_type table type possible_keys key key_len ref rows filtered Extra
330 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
331 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
332 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join)
333 Warnings:
334 Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a` from `test`.`t1` left join (`test`.`t1` `t2` left join `test`.`t1` `t3` on(`test`.`t3`.`a` = `test`.`t2`.`a` and `test`.`t3`.`row_end` = SYS_TIME_MAX)) on(`test`.`t2`.`row_end` = SYS_TIME_MAX and `test`.`t1`.`a` > 1) where `test`.`t1`.`row_end` = SYS_TIME_MAX
335 select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
336 a a
337 2 1
338 3 1
339 2 2
340 3 2
341 2 3
342 3 3
343 1 NULL
344 create or replace table t1 (x int) with system versioning;
345 create or replace table t2 (y int) with system versioning;
346 insert into t1 values (1), (2), (3);
347 delete from t1 where x = 3;
348 insert into t2 values (1);
349 select * from t1, t2 for system_time all;
350 x y
351 1 1
352 2 1
353 select * from (select * from t1 for system_time all, t2 for system_time all)
354 for system_time all as t;
355 ERROR HY000: Table `t` is not system-versioned
356 # TRANSACTION/TIMESTAMP specifier in SYSTEM_TIME [MDEV-14645, Issue #396]
357 create or replace table t1 (x int) with system versioning engine myisam;
358 select * from t1 for system_time as of transaction 1;
359 ERROR HY000: Transaction-precise system versioning for `t1` is not supported
360 # MDEV-18929 2nd execution of SP does not detect ER_VERS_NOT_VERSIONED
361 create or replace procedure sp()
362 select * from t1 for system_time as of transaction 1;
363 call sp;
364 ERROR HY000: Transaction-precise system versioning for `t1` is not supported
365 call sp;
366 ERROR HY000: Transaction-precise system versioning for `t1` is not supported
367 create or replace table t1 (a int);
368 create or replace procedure sp()
369 select * from t1 for system_time all;
370 call sp;
371 ERROR HY000: Table `t1` is not system-versioned
372 call sp;
373 ERROR HY000: Table `t1` is not system-versioned
374 drop procedure sp;
375 create or replace table t1 (
376 x int,
377 sys_trx_start bigint unsigned as row start invisible,
378 sys_trx_end bigint unsigned as row end invisible,
379 period for system_time (sys_trx_start, sys_trx_end)
380 ) with system versioning engine innodb;
381 insert into t1 values (1);
382 set @ts= now(6);
383 delete from t1;
384 select sys_trx_start from t1 for system_time all into @trx_start;
385 ## ensure @trx_start is much lower than unix timestamp
386 select @trx_start < unix_timestamp(@ts) - 100 as trx_start_good;
387 trx_start_good
388 1
389 ## TIMESTAMP specifier
390 select x from t1 for system_time as of timestamp @ts;
391 x
392 1
393 set @ts= timestamp'1-1-1 0:0:0';
394 select x from t1 for system_time as of timestamp @ts;
395 x
396 ## TRANSACTION specifier
397 select x from t1 for system_time as of transaction @trx_start;
398 x
399 1
400 ## no specifier (defaults to timestamp)
401 select x from t1 for system_time as of @ts;
402 x
403 ### Issue #365, bug 4 (related to #226, optimized fields)
404 create or replace table t1 (i int, b int) with system versioning;
405 insert into t1 values (0, 0), (0, 0);
406 select min(i) over (partition by b) as f
407 from (select i + 0 as i, b from t1) as tt
408 order by i;
409 f
410 0
411 0
412 ### Issue #365, bug 5 (dangling AND)
413 create or replace table t1 (a int);
414 create or replace table t2 (b int) with system versioning;
415 select * from t1
416 where exists (select 1 from t2 where t2.b = t1.a and t2.b = t1.a);
417 a
418 ### Issue #365, bug 9 (not a derived subquery)
419 create or replace table t1 (x int) with system versioning;
420 select t1.x in (select x from t1) a from t1, (select x from t1) b;
421 a
422 ### Issue #365, bug 10 (WHERE cond freed prematurely for PS)
423 create or replace table t1 (x int) with system versioning;
424 insert into t1 values (1);
425 create or replace view v1 as select x from t1 where x = 1;
426 prepare stmt from "
427 select x from t1 where x in (select x from v1);";
428 execute stmt;
429 x
430 1
431 execute stmt;
432 x
433 1
434 ### Issue #365, bug 11 (WHERE cond not top level item)
435 create or replace table t1 (a int, b int, key idx(a)) with system versioning;
436 insert into t1 values (1, 1), (2, 2);
437 select * from t1 where (a, 2) in ((1, 1), (2, 2)) and b = 1;
438 a b
439 ### Issue #398, NOW is now non-magic
440 create or replace table t1 (x int) with system versioning;
441 select * from t1 for system_time as of current_timestamp;
442 x
443 select * from t1 for system_time as of now;
444 ERROR HY000: Illegal parameter data type now for operation 'FOR SYSTEM_TIME'
445 ### Issue #405, NATURAL JOIN failure
446 create or replace table t1 (a int) with system versioning;
447 create or replace table t2 (b int);
448 create or replace view v1 as select a, row_start, row_end from t1 where a > round(rand()*1000);
449 select * from v1 natural join t2;
450 a row_start row_end b
451 #
452 # Issue #406, MDEV-14633 Assertion on TRT read
453 #
454 create or replace table t1 (pk int primary key, i int, t time, key (i)) with system versioning;
455 insert into t1 values (1, 10, '15:01:53'), (2, 20, '00:00:00');
456 delete from t1;
457 select * from t1 where t = '00:00:00' and i > 0 and row_end <> '2012-12-12 00:00:00';
458 pk i t
459 #
460 # MDEV-14816 Assertion `join->best_read < double(1.797...e+308L)' failed in bool greedy_search
461 #
462 create or replace table t1 (f1 int) with system versioning;
463 create or replace table t2 (f2 int) with system versioning;
464 create or replace table t3 (f3 int);
465 create or replace table t4 (f4 int);
466 insert into t1 values (1), (2), (3), (4);
467 insert into t2 values (1), (2), (3);
468 insert into t3 values (1), (2);
469 insert into t4 values (1);
470 select * from
471 t1 as t1a
472 left join t2 as t2a left join (t3 as t3a inner join t1) on t2a.f2 = t3a.f3 on t1a.f1 = t2a.f2
473 left join (t2 join t3 inner join t4) on t2a.f2 = t1a.f1;
474 f1 f2 f3 f1 f2 f3 f4
475 1 1 1 1 1 1 1
476 2 2 2 1 1 1 1
477 1 1 1 2 1 1 1
478 2 2 2 2 1 1 1
479 1 1 1 3 1 1 1
480 2 2 2 3 1 1 1
481 1 1 1 4 1 1 1
482 2 2 2 4 1 1 1
483 1 1 1 1 1 2 1
484 2 2 2 1 1 2 1
485 1 1 1 2 1 2 1
486 2 2 2 2 1 2 1
487 1 1 1 3 1 2 1
488 2 2 2 3 1 2 1
489 1 1 1 4 1 2 1
490 2 2 2 4 1 2 1
491 1 1 1 1 2 1 1
492 2 2 2 1 2 1 1
493 1 1 1 2 2 1 1
494 2 2 2 2 2 1 1
495 1 1 1 3 2 1 1
496 2 2 2 3 2 1 1
497 1 1 1 4 2 1 1
498 2 2 2 4 2 1 1
499 1 1 1 1 2 2 1
500 2 2 2 1 2 2 1
501 1 1 1 2 2 2 1
502 2 2 2 2 2 2 1
503 1 1 1 3 2 2 1
504 2 2 2 3 2 2 1
505 1 1 1 4 2 2 1
506 2 2 2 4 2 2 1
507 1 1 1 1 3 1 1
508 2 2 2 1 3 1 1
509 1 1 1 2 3 1 1
510 2 2 2 2 3 1 1
511 1 1 1 3 3 1 1
512 2 2 2 3 3 1 1
513 1 1 1 4 3 1 1
514 2 2 2 4 3 1 1
515 1 1 1 1 3 2 1
516 2 2 2 1 3 2 1
517 1 1 1 2 3 2 1
518 2 2 2 2 3 2 1
519 1 1 1 3 3 2 1
520 2 2 2 3 3 2 1
521 1 1 1 4 3 2 1
522 2 2 2 4 3 2 1
523 3 3 NULL NULL 1 1 1
524 3 3 NULL NULL 1 2 1
525 3 3 NULL NULL 2 1 1
526 3 3 NULL NULL 2 2 1
527 3 3 NULL NULL 3 1 1
528 3 3 NULL NULL 3 2 1
529 4 NULL NULL NULL NULL NULL NULL
530 #
531 # MDEV-15004 parser greedily parses AS OF TIMESTAMP
532 #
533 select timestamp'2016-02-30 08:07:06';
534 ERROR HY000: Incorrect DATETIME value: '2016-02-30 08:07:06'
535 select * from t1 for system_time as of timestamp'2016-02-30 08:07:06';
536 ERROR HY000: Incorrect DATETIME value: '2016-02-30 08:07:06'
537 select timestamp('2003-12-31 12:00:00','12:00:00');
538 timestamp('2003-12-31 12:00:00','12:00:00')
539 2004-01-01 00:00:00
540 select * from t1 for system_time as of timestamp('2003-12-31 12:00:00','12:00:00');
541 f1
542 #
543 # MDEV-15391 Server crashes in JOIN::fix_all_splittings_in_plan or Assertion `join->best_read < double(1.79...e+308L)' failed [tempesta-tech#475]
544 #
545 create or replace table t1 (f1 int) with system versioning;
546 insert t1 values (1),(2);
547 create or replace table t2 (f2 int);
548 create or replace table t3 (f3 int);
549 create or replace table t4 (f4 int) with system versioning;
550 select f1 from t1 join t2 left join t3 left join t4 on f3 = f4 on f3 = f2;
551 f1
552 insert t2 values (1),(2);
553 insert t3 values (1),(2);
554 insert t4 values (1),(2);
555 explain extended
556 select f1 from t1 join t2 left join t3 left join t4 on f3 = f4 on f3 = f2;
557 id select_type table type possible_keys key key_len ref rows filtered Extra
558 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
559 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
560 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)
561 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)
562 Warnings:
563 Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` left join `test`.`t4` on(`test`.`t4`.`f4` = `test`.`t2`.`f2` and `test`.`t4`.`row_end` = SYS_TIME_MAX)) on(`test`.`t3`.`f3` = `test`.`t2`.`f2`) where `test`.`t1`.`row_end` = SYS_TIME_MAX
564 drop view v1;
565 drop table t1, t2, t3, t4;
566 #
567 # MDEV-15980 FOR SYSTEM_TIME BETWEEN and FROM .. TO work with negative intervals
568 #
569 create or replace table t1 (
570 a int,
571 row_start SYS_DATATYPE as row start invisible,
572 row_end SYS_DATATYPE as row end invisible,
573 period for system_time (row_start, row_end)
574 ) with system versioning;
575 insert into t1 values (1);
576 delete from t1;
577 select row_start from t1 for system_time all into @t1;
578 select row_end from t1 for system_time all into @t2;
579 select * from t1 for system_time between @t1 and @t2;
580 a
581 1
582 select * from t1 for system_time between @t2 and @t1;
583 a
584 select * from t1 for system_time from @t1 to @t2;
585 a
586 1
587 select * from t1 for system_time from @t2 to @t1;
588 a
589 drop table t1;
590 #
591 # MDEV-15991 Server crashes in setup_on_expr upon calling SP or function executing DML on versioned tables
592 #
593 create or replace table t1 (i int);
594 insert into t1 values (1);
595 create or replace procedure p(n int)
596 begin
597 select * from t1;
598 end $
599 call p(1);
600 i
601 1
602 alter table t1 add system versioning;
603 call p(2);
604 i
605 1
606 call p(3);
607 i
608 1
609 #
610 # MDEV-15947 ASAN heap-use-after-free in Item_ident::print or in my_strcasecmp_utf8 or unexpected ER_BAD_FIELD_ERROR upon call of stored procedure reading from versioned table
611 #
612 create or replace table t1 (i int) with system versioning;
613 create or replace procedure p() select * from t1;
614 call p;
615 i
616 flush tables;
617 call p;
618 i
619 call p;
620 i
621 drop procedure p;
622 drop table t1;
623 #
624 # MDEV-21234 Server crashes in in setup_on_expr upon 3rd execution of SP
625 #
626 create table t1 (a varchar(8));
627 insert into t1 values ('foo'),('bar');
628 create table t2 (b date);
629 create procedure pr() insert into t2 select * from t1;
630 call pr;
631 ERROR 22007: Incorrect date value: 'foo' for column `test`.`t2`.`b` at row 1
632 prepare stmt from 'insert into t2 select * from t1';
633 execute stmt;
634 ERROR 22007: Incorrect date value: 'foo' for column `test`.`t2`.`b` at row 1
635 alter table t1 add system versioning;
636 call pr;
637 ERROR 22007: Incorrect date value: 'foo' for column `test`.`t2`.`b` at row 1
638 call pr;
639 ERROR 22007: Incorrect date value: 'foo' for column `test`.`t2`.`b` at row 1
640 execute stmt;
641 ERROR 22007: Incorrect date value: 'foo' for column `test`.`t2`.`b` at row 1
642 execute stmt;
643 ERROR 22007: Incorrect date value: 'foo' for column `test`.`t2`.`b` at row 1
644 drop prepare stmt;
645 drop procedure pr;
646 drop table t1, t2;
647 #
648 # MDEV-23799 CREATE .. SELECT wrong result on join versioned table
649 #
650 create or replace table x (id Int) with system versioning;
651 create or replace table x_p (elementId Int, pkey varchar(20), pvalue varchar(20)) with system versioning;
652 insert into x values (1), (2), (3);
653 insert into x_p values (1, 'gender', 'male');
654 insert into x_p values (2, 'gender', 'female');
655 insert into x_p values (3, 'gender', 'male');
656 create table tmp1
657 select xgender.pvalue as gender, xtitle.pvalue as title
658 from x
659 left join x_p as xgender on x.id = xgender.elementId and xgender.pkey = 'gender'
660 left join x_p as xtitle on x.id = xtitle.elementId and xtitle.pkey = 'title';
661 select * from tmp1;
662 gender title
663 male NULL
664 female NULL
665 male NULL
666 drop table tmp1;
667 drop tables x, x_p;
668 call verify_trt_dummy(34);
669 No A B C D
670 1 1 1 1 1
671 2 1 1 1 1
672 3 1 1 1 1
673 4 1 1 1 1
674 5 1 1 1 1
675 6 1 1 1 1
676 7 1 1 1 1
677 8 1 1 1 1
678 9 1 1 1 1
679 10 1 1 1 1
680 11 1 1 1 1
681 12 1 1 1 1
682 13 1 1 1 1
683 14 1 1 1 1
684 15 1 1 1 1
685 16 1 1 1 1
686 17 1 1 1 1
687 18 1 1 1 1
688 19 1 1 1 1
689 20 1 1 1 1
690 21 1 1 1 1
691 22 1 1 1 1
692 23 1 1 1 1
693 24 1 1 1 1
694 25 1 1 1 1
695 26 1 1 1 1
696 27 1 1 1 1
697 28 1 1 1 1
698 29 1 1 1 1
699 30 1 1 1 1
700 31 1 1 1 1
701 32 1 1 1 1
702 33 1 1 1 1
703 34 1 1 1 1
704 SET GLOBAL innodb_stats_persistent = @saved_stats_persistent;
705 #
706 # MDEV-32082 Server crash in find_field_in_table
707 #
708 create table t0 (c0 int) with system versioning;
709 select x0 from (
710 select c0 x0 from t0
711 ) for system_time as of nowasdf deriv;
712 ERROR HY000: Illegal parameter data type nowasdf for operation 'FOR SYSTEM_TIME'
713 drop table t0;