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