Debian Codegraph @

mysql-test/suite/versioning/t/select.test

raw
1 if (`SELECT $PS_PROTOCOL + $CURSOR_PROTOCOL > 0`)
2 {
3 --skip Need regular protocol but ps-protocol and cursor-protocol were specified
4 }
5 --source suite/versioning/engines.inc
6 --source suite/versioning/common.inc
7 --source include/default_optimizer_switch.inc
8
9 --let $replace_regex_tsltz6= /TIMESTAMP..WITH LOCAL TIME ZONE..'....-..-.. ..:..:..[.]......'/TIMESTAMP\/*WITH LOCAL TIME ZONE*\/'YYYY-MM-DD hh:ss:mm:.uuuuuu'/
10
11 SET @saved_stats_persistent = @@GLOBAL.innodb_stats_persistent;
12 SET GLOBAL innodb_stats_persistent = OFF;
13
14 --enable_prepare_warnings
15 # test_01
16
17 --replace_result $sys_datatype_expl SYS_DATATYPE
18 eval create or replace table t1 (
19 x int unsigned,
20 y int unsigned,
21 sys_trx_start $sys_datatype_expl as row start invisible,
22 sys_trx_end $sys_datatype_expl as row end invisible,
23 period for system_time (sys_trx_start, sys_trx_end)
24 ) with system versioning;
25
26 insert into t1 (x, y) values
27 (0, 100),
28 (1, 101),
29 (2, 102),
30 (3, 103),
31 (4, 104),
32 (5, 105),
33 (6, 106),
34 (7, 107),
35 (8, 108),
36 (9, 109);
37
38 set @t0= now(6);
39 if ($MTR_COMBINATION_TRX_ID)
40 {
41 --disable_query_log
42 --disable_cursor_protocol
43 select sys_trx_start from t1 limit 1 into @x0;
44 --enable_cursor_protocol
45 --enable_query_log
46 }
47
48 delete from t1 where x = 3;
49 delete from t1 where x > 7;
50
51 insert into t1(x, y) values(3, 33);
52 --disable_cursor_protocol
53 select sys_trx_start from t1 where x = 3 and y = 33 into @t1;
54 --enable_cursor_protocol
55 if ($MTR_COMBINATION_TRX_ID)
56 {
57 --disable_query_log
58 set @x1= @t1;
59 --disable_cursor_protocol
60 select trt_commit_ts(@x1) into @t1;
61 --enable_cursor_protocol
62 --enable_query_log
63 }
64
65 select x, y from t1;
66 select x as ASOF_x, y from t1 for system_time as of timestamp @t0;
67 select x as FROMTO_x, y from t1 for system_time from timestamp '1970-01-01 00:00:00' to timestamp @t1;
68 select x as BETWAND_x, y from t1 for system_time between timestamp '1970-01-01 00:00:00' and timestamp @t1;
69 select x as ALL_x, y from t1 for system_time all;
70
71 --disable_query_log
72 if ($MTR_COMBINATION_TRX_ID)
73 {
74 select x as ASOF2_x, y from t1 for system_time as of transaction @x0;
75 select x as FROMTO2_x, y from t1 for system_time from transaction @x0 to transaction @x1;
76 select x as BETWAND2_x, y from t1 for system_time between transaction @x0 and transaction @x1;
77 }
78 if ($MTR_COMBINATION_TIMESTAMP)
79 {
80 select x as ASOF2_x, y from t1 for system_time as of @t0;
81 select x as FROMTO2_x, y from t1 for system_time from timestamp '1970-01-01 00:00:00' to timestamp @t1;
82 select x as BETWAND2_x, y from t1 for system_time between timestamp '1970-01-01 00:00:00' and timestamp @t1;
83 }
84 --enable_query_log
85
86 # test_02
87
88 create or replace table t1 (
89 x int unsigned,
90 y int unsigned
91 ) with system versioning;
92 create or replace table t2 (
93 x int unsigned,
94 y int unsigned
95 ) with system versioning;
96
97 insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5);
98 insert into t2 values (1, 2), (2, 1), (3, 1);
99 set @t0= now(6);
100
101 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;
102 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;
103 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;
104
105 delete from t1;
106 delete from t2;
107
108 #384
109 --replace_regex $replace_regex_tsltz6
110 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)
111 for system_time as of timestamp @t0 as t;
112 --replace_regex $replace_regex_tsltz6
113 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)
114 for system_time as of timestamp @t0 as t;
115 #383
116 --replace_regex $replace_regex_tsltz6
117 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)
118 for system_time as of timestamp @t0 as t;
119
120 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)
121 for system_time as of timestamp @t0 as t;
122 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)
123 for system_time as of timestamp @t0 as t;
124 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)
125 for system_time as of timestamp @t0 as t;
126
127 drop table t1;
128 drop table t2;
129
130 # Query conditions check
131
132 create or replace table t1(x int) with system versioning;
133 insert into t1 values (1);
134 delete from t1;
135 insert into t1 values (2);
136 delete from t1;
137 insert into t1 values (3);
138 delete from t1;
139
140 --disable_cursor_protocol
141 select row_start into @start1 from t1 for system_time all where x = 1;
142 select row_end into @end1 from t1 for system_time all where x = 1;
143 select row_start into @start2 from t1 for system_time all where x = 2;
144 select row_end into @end2 from t1 for system_time all where x = 2;
145 select row_start into @start3 from t1 for system_time all where x = 3;
146 select row_end into @end3 from t1 for system_time all where x = 3;
147 --enable_cursor_protocol
148
149 select x as ASOF_x from t1 for system_time as of @start2;
150 select x as ASOF_x from t1 for system_time as of @end2;
151 select x as FROMTO_x from t1 for system_time from @start1 to @end3;
152 select x as FROMTO_x from t1 for system_time from @end1 to @start2;
153 select x as BETWAND_x from t1 for system_time between @start1 and @end3;
154 select x as BETWAND_x from t1 for system_time between @end1 and @start2;
155
156 drop table t1;
157
158 # Wildcard expansion on hidden fields
159
160 create table t1(
161 A int
162 ) with system versioning;
163 insert into t1 values(1);
164 select * from t1;
165
166 create or replace table t1 (x int);
167 insert into t1 values (1);
168 --error ER_VERS_NOT_VERSIONED
169 select * from t1 for system_time all;
170
171 create or replace table t1 (x int) with system versioning;
172 insert into t1 values (1);
173 --error ER_TABLE_NOT_LOCKED_FOR_WRITE
174 select * from t1 for system_time all for update;
175
176 create or replace table t1 (a int not null auto_increment primary key) with system versioning;
177 select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1;
178
179 create or replace table t1 (a int) with system versioning;
180 create or replace table t2 (a int) with system versioning;
181 insert into t1 values(1);
182 insert into t2 values(1);
183 create view v1 as select * from t2 inner join t1 using (a);
184 select * from v1;
185 drop view v1;
186
187 create or replace table t1 (a int) with system versioning;
188 insert into t1 values (1);
189 create view vt1 as select a from t1;
190 select * from t1 natural join vt1;
191 drop view vt1;
192
193 create or replace table t1(x int) with system versioning;
194 select * from (t1 as r left join t1 as u using (x)), t1;
195
196 # @end should be max
197 set @@time_zone="+00:00";
198 create or replace table t1 (a int) with system versioning;
199 insert into t1 values (1);
200 create trigger read_end after update on t1
201 for each row set @end = old.row_end;
202 update t1 set a=2;
203 --replace_result 18446744073709551615 MAX_RESULT $sys_time_max_replace MAX_RESULT
204 select @end;
205 set @@time_zone=default;
206
207 create or replace table t1 (a int) with system versioning;
208 create or replace table t2 (b int) with system versioning;
209 insert into t1 values (1);
210 insert into t2 values (2);
211 select * from (select * from t1 cross join t2) as tmp;
212 select * from (select * from (select * from t1 cross join t2) as tmp1) as tmp2;
213 select * from (select * from t1 cross join t2 for system_time as of timestamp ('1970-01-01 00:00:00')) as tmp;
214
215 create or replace table t1(a1 int) with system versioning;
216 create or replace table t2(a2 int) with system versioning;
217 insert into t1 values(1),(2);
218 insert into t2 values(1),(2);
219 select * from t1 for system_time all natural left join t2 for system_time all;
220
221 # natural join of a view and table
222 create or replace table t1(a1 int) with system versioning;
223 create or replace table t2(a2 int) with system versioning;
224 insert into t1 values(1),(2);
225 insert into t2 values(1),(2);
226 create or replace view v1 as select a1 from t1;
227
228 select * from v1 natural join t2;
229 select * from v1 natural left join t2;
230 select * from v1 natural right join t2;
231
232 create or replace table t1 (a int) with system versioning;
233 insert into t1 values (1);
234 insert into t1 values (2);
235 insert into t1 values (3);
236 --replace_result $sys_time_max SYS_TIME_MAX
237 explain extended
238 select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
239 select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
240
241 create or replace table t1 (x int) with system versioning;
242 create or replace table t2 (y int) with system versioning;
243 insert into t1 values (1), (2), (3);
244 delete from t1 where x = 3;
245 insert into t2 values (1);
246 select * from t1, t2 for system_time all;
247
248 --error ER_VERS_NOT_VERSIONED
249 select * from (select * from t1 for system_time all, t2 for system_time all)
250 for system_time all as t;
251
252 --echo # TRANSACTION/TIMESTAMP specifier in SYSTEM_TIME [MDEV-14645, Issue #396]
253 create or replace table t1 (x int) with system versioning engine myisam;
254 --error ER_VERS_ENGINE_UNSUPPORTED
255 select * from t1 for system_time as of transaction 1;
256 --echo # MDEV-18929 2nd execution of SP does not detect ER_VERS_NOT_VERSIONED
257 create or replace procedure sp()
258 select * from t1 for system_time as of transaction 1;
259 --error ER_VERS_ENGINE_UNSUPPORTED
260 call sp;
261 --error ER_VERS_ENGINE_UNSUPPORTED
262 call sp;
263 create or replace table t1 (a int);
264 create or replace procedure sp()
265 select * from t1 for system_time all;
266 --error ER_VERS_NOT_VERSIONED
267 call sp;
268 --error ER_VERS_NOT_VERSIONED
269 call sp;
270 drop procedure sp;
271
272 create or replace table t1 (
273 x int,
274 sys_trx_start bigint unsigned as row start invisible,
275 sys_trx_end bigint unsigned as row end invisible,
276 period for system_time (sys_trx_start, sys_trx_end)
277 ) with system versioning engine innodb;
278 insert into t1 values (1);
279 set @ts= now(6);
280 delete from t1;
281 --disable_cursor_protocol
282 select sys_trx_start from t1 for system_time all into @trx_start;
283 --enable_cursor_protocol
284
285 --echo ## ensure @trx_start is much lower than unix timestamp
286 select @trx_start < unix_timestamp(@ts) - 100 as trx_start_good;
287
288 --echo ## TIMESTAMP specifier
289 select x from t1 for system_time as of timestamp @ts;
290
291 set @ts= timestamp'1-1-1 0:0:0';
292
293 select x from t1 for system_time as of timestamp @ts;
294
295 --echo ## TRANSACTION specifier
296 select x from t1 for system_time as of transaction @trx_start;
297
298 --echo ## no specifier (defaults to timestamp)
299 select x from t1 for system_time as of @ts;
300
301 --echo ### Issue #365, bug 4 (related to #226, optimized fields)
302 create or replace table t1 (i int, b int) with system versioning;
303 insert into t1 values (0, 0), (0, 0);
304 select min(i) over (partition by b) as f
305 from (select i + 0 as i, b from t1) as tt
306 order by i;
307
308 --echo ### Issue #365, bug 5 (dangling AND)
309 create or replace table t1 (a int);
310 create or replace table t2 (b int) with system versioning;
311 select * from t1
312 where exists (select 1 from t2 where t2.b = t1.a and t2.b = t1.a);
313
314 --echo ### Issue #365, bug 9 (not a derived subquery)
315 create or replace table t1 (x int) with system versioning;
316 select t1.x in (select x from t1) a from t1, (select x from t1) b;
317
318 --echo ### Issue #365, bug 10 (WHERE cond freed prematurely for PS)
319 create or replace table t1 (x int) with system versioning;
320 insert into t1 values (1);
321 create or replace view v1 as select x from t1 where x = 1;
322 prepare stmt from "
323 select x from t1 where x in (select x from v1);";
324 execute stmt;
325 execute stmt;
326
327 --echo ### Issue #365, bug 11 (WHERE cond not top level item)
328 create or replace table t1 (a int, b int, key idx(a)) with system versioning;
329 insert into t1 values (1, 1), (2, 2);
330 select * from t1 where (a, 2) in ((1, 1), (2, 2)) and b = 1;
331
332 --echo ### Issue #398, NOW is now non-magic
333 create or replace table t1 (x int) with system versioning;
334 select * from t1 for system_time as of current_timestamp;
335 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
336 select * from t1 for system_time as of now;
337
338 --echo ### Issue #405, NATURAL JOIN failure
339 create or replace table t1 (a int) with system versioning;
340 create or replace table t2 (b int);
341 create or replace view v1 as select a, row_start, row_end from t1 where a > round(rand()*1000);
342 select * from v1 natural join t2;
343
344 --echo #
345 --echo # Issue #406, MDEV-14633 Assertion on TRT read
346 --echo #
347 create or replace table t1 (pk int primary key, i int, t time, key (i)) with system versioning;
348 insert into t1 values (1, 10, '15:01:53'), (2, 20, '00:00:00');
349 delete from t1;
350 --disable_warnings
351 select * from t1 where t = '00:00:00' and i > 0 and row_end <> '2012-12-12 00:00:00';
352 --enable_warnings
353
354 --echo #
355 --echo # MDEV-14816 Assertion `join->best_read < double(1.797...e+308L)' failed in bool greedy_search
356 --echo #
357 create or replace table t1 (f1 int) with system versioning;
358 create or replace table t2 (f2 int) with system versioning;
359 create or replace table t3 (f3 int);
360 create or replace table t4 (f4 int);
361 insert into t1 values (1), (2), (3), (4);
362 insert into t2 values (1), (2), (3);
363 insert into t3 values (1), (2);
364 insert into t4 values (1);
365 select * from
366 t1 as t1a
367 left join t2 as t2a left join (t3 as t3a inner join t1) on t2a.f2 = t3a.f3 on t1a.f1 = t2a.f2
368 left join (t2 join t3 inner join t4) on t2a.f2 = t1a.f1;
369
370 --echo #
371 --echo # MDEV-15004 parser greedily parses AS OF TIMESTAMP
372 --echo #
373 --error ER_WRONG_VALUE
374 select timestamp'2016-02-30 08:07:06';
375 --error ER_WRONG_VALUE
376 select * from t1 for system_time as of timestamp'2016-02-30 08:07:06';
377 select timestamp('2003-12-31 12:00:00','12:00:00');
378 select * from t1 for system_time as of timestamp('2003-12-31 12:00:00','12:00:00');
379
380
381 --echo #
382 --echo # 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]
383 --echo #
384 create or replace table t1 (f1 int) with system versioning;
385 insert t1 values (1),(2);
386 create or replace table t2 (f2 int);
387 create or replace table t3 (f3 int);
388 create or replace table t4 (f4 int) with system versioning;
389 select f1 from t1 join t2 left join t3 left join t4 on f3 = f4 on f3 = f2;
390 insert t2 values (1),(2);
391 insert t3 values (1),(2);
392 insert t4 values (1),(2);
393 --replace_result $sys_time_max SYS_TIME_MAX
394 explain extended
395 select f1 from t1 join t2 left join t3 left join t4 on f3 = f4 on f3 = f2;
396
397 drop view v1;
398 drop table t1, t2, t3, t4;
399
400 --echo #
401 --echo # MDEV-15980 FOR SYSTEM_TIME BETWEEN and FROM .. TO work with negative intervals
402 --echo #
403 --replace_result $sys_datatype_expl SYS_DATATYPE
404 eval create or replace table t1 (
405 a int,
406 row_start $sys_datatype_expl as row start invisible,
407 row_end $sys_datatype_expl as row end invisible,
408 period for system_time (row_start, row_end)
409 ) with system versioning;
410 insert into t1 values (1);
411 delete from t1;
412 --disable_cursor_protocol
413 select row_start from t1 for system_time all into @t1;
414 select row_end from t1 for system_time all into @t2;
415 --enable_cursor_protocol
416 --disable_query_log
417 if($MTR_COMBINATION_TRX_ID) {
418 set @t1= trt_begin_ts(@t1);
419 set @t2= trt_commit_ts(@t2);
420 }
421 --enable_query_log
422 select * from t1 for system_time between @t1 and @t2;
423 select * from t1 for system_time between @t2 and @t1;
424 select * from t1 for system_time from @t1 to @t2;
425 select * from t1 for system_time from @t2 to @t1;
426 drop table t1;
427
428 --echo #
429 --echo # MDEV-15991 Server crashes in setup_on_expr upon calling SP or function executing DML on versioned tables
430 --echo #
431 create or replace table t1 (i int);
432 insert into t1 values (1);
433 --delimiter $
434 create or replace procedure p(n int)
435 begin
436 select * from t1;
437 end $
438 --delimiter ;
439 call p(1);
440 alter table t1 add system versioning;
441 call p(2);
442 call p(3);
443
444 --echo #
445 --echo # 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
446 --echo #
447 create or replace table t1 (i int) with system versioning;
448 create or replace procedure p() select * from t1;
449 call p;
450 flush tables;
451 call p;
452 call p;
453 drop procedure p;
454 drop table t1;
455
456 --echo #
457 --echo # MDEV-21234 Server crashes in in setup_on_expr upon 3rd execution of SP
458 --echo #
459 create table t1 (a varchar(8));
460 insert into t1 values ('foo'),('bar');
461 create table t2 (b date);
462
463 create procedure pr() insert into t2 select * from t1;
464 --error ER_TRUNCATED_WRONG_VALUE
465 call pr;
466 prepare stmt from 'insert into t2 select * from t1';
467 --error ER_TRUNCATED_WRONG_VALUE
468 execute stmt;
469 alter table t1 add system versioning;
470 --error ER_TRUNCATED_WRONG_VALUE
471 call pr;
472 --error ER_TRUNCATED_WRONG_VALUE
473 call pr;
474 --error ER_TRUNCATED_WRONG_VALUE
475 execute stmt;
476 --error ER_TRUNCATED_WRONG_VALUE
477 execute stmt;
478 drop prepare stmt;
479
480 # cleanup
481 drop procedure pr;
482 drop table t1, t2;
483
484 --echo #
485 --echo # MDEV-23799 CREATE .. SELECT wrong result on join versioned table
486 --echo #
487 create or replace table x (id Int) with system versioning;
488 create or replace table x_p (elementId Int, pkey varchar(20), pvalue varchar(20)) with system versioning;
489
490 insert into x values (1), (2), (3);
491 insert into x_p values (1, 'gender', 'male');
492 insert into x_p values (2, 'gender', 'female');
493 insert into x_p values (3, 'gender', 'male');
494
495 create table tmp1
496 select xgender.pvalue as gender, xtitle.pvalue as title
497 from x
498 left join x_p as xgender on x.id = xgender.elementId and xgender.pkey = 'gender'
499 left join x_p as xtitle on x.id = xtitle.elementId and xtitle.pkey = 'title';
500
501 select * from tmp1;
502
503 drop table tmp1;
504 drop tables x, x_p;
505
506 call verify_trt_dummy(34);
507 --disable_prepare_warnings
508
509 SET GLOBAL innodb_stats_persistent = @saved_stats_persistent;
510
511 --echo #
512 --echo # MDEV-32082 Server crash in find_field_in_table
513 --echo #
514 create table t0 (c0 int) with system versioning;
515 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
516 select x0 from (
517 select c0 x0 from t0
518 ) for system_time as of nowasdf deriv;
519 drop table t0;
520
521 -- source suite/versioning/common_finish.inc