122 lines
3.2 KiB
Plaintext
122 lines
3.2 KiB
Plaintext
set global innodb_rds_flashback_enabled = false;
|
|
|
|
create table tt (a int);
|
|
create table pp (a int);
|
|
create view vv as select * from pp;
|
|
|
|
# No reserved key words added
|
|
create table scn (a int, scn int);
|
|
drop table scn;
|
|
|
|
# Normal cases ===============
|
|
|
|
# in from list
|
|
select * from tt as of scn 1;
|
|
select * from tt as of scn 1 alias;
|
|
select * from tt as of scn 1 as alias;
|
|
select * from tt as of timestamp now() as alias;
|
|
select * from tt as of timestamp now() alias;
|
|
select * from tt as of timestamp now();
|
|
|
|
# in subquery
|
|
select * from (select * from tt as of scn 1 as alias union all select * from tt as of timestamp now()) ff;
|
|
|
|
# in with clause
|
|
with ff as (select * from tt as of scn 1 as alias union all select * from tt as of timestamp now()) select * from ff;
|
|
with ff as (select * from tt as of scn 1 as alias union all select * from tt as of timestamp now()) update pp set a=0 where a=(select max(a) from ff);
|
|
|
|
# in where or having cond
|
|
select * from pp where a=(select max(a) from tt as of scn 1 as alias);
|
|
select * from tt group by a having a< (select sum(a) from pp as of scn 1 as alias);
|
|
update tt set a=0 where a < (select sum(a) from pp as of scn 1 as alias);
|
|
|
|
# in join
|
|
select ll.* from tt as of timestamp now() ll join pp as of scn 1 as vv on vv.a=ll.a;
|
|
|
|
# constant expr
|
|
select * from tt as of scn (select 1+2);
|
|
select * from tt as of scn (1+2);
|
|
select * from tt as of timestamp (select date_add(current_timestamp, interval 1 day));
|
|
select * from tt as of timestamp date_add(current_timestamp, interval 1 day);
|
|
|
|
# execution const expr
|
|
insert into pp values(1);
|
|
select * from tt as of scn (select max(a) from pp);
|
|
delete from pp;
|
|
|
|
# timestamp
|
|
select * from tt as of timestamp now();
|
|
|
|
# cast type
|
|
select * from tt as of scn '123';
|
|
select * from tt as of timestamp '2020-01-01 01:01:01';
|
|
|
|
# sum, avg
|
|
select * from tt as of scn (select sum(a) from pp);
|
|
--error ER_AS_OF_BAD_SCN_TYPE
|
|
select * from tt as of scn (select avg(a) from pp);
|
|
|
|
# Bad parameter cases =======================
|
|
|
|
--error ER_AS_OF_BAD_SCN_TYPE
|
|
select * from tt as of scn 0.11;
|
|
|
|
--error ER_AS_OF_BAD_TIMESTAMP_TYPE
|
|
select * from tt as of timestamp (1<2);
|
|
|
|
--error 1054
|
|
select * from tt as of scn (a<b);
|
|
|
|
|
|
# Not surpported, bypass ==========================
|
|
|
|
create temporary table tmp(a int);
|
|
select * from tmp as of scn 1;
|
|
drop table tmp;
|
|
|
|
# now bypass, do not raise error for non-innnodb table
|
|
select * from information_schema.INNODB_TABLESPACES as of scn 1 limit 0;
|
|
|
|
--error ER_AS_OF_CONFLICT_LOCK_CLAUSE
|
|
select * from tt as of scn 1 as alias for update;
|
|
--error ER_AS_OF_CONFLICT_LOCK_CLAUSE
|
|
select * from tt as of scn 1 as alias LOCK IN SHARE MODE;
|
|
|
|
select * from vv as of scn 1;
|
|
|
|
lock tables vv read;
|
|
select * from vv as of scn 1;
|
|
unlock tables;
|
|
|
|
--error ER_AS_OF_NOT_SELECT
|
|
update tt as of scn 1 as alias set a=0;
|
|
|
|
--error 1064
|
|
delete from tt as of scn 1 as alias;
|
|
|
|
--error 1064
|
|
insert into tt as of scn 1 values (1);
|
|
|
|
--error 1064
|
|
create table no_ok as of scn 1 alias (a int);
|
|
|
|
--error 1064
|
|
alter table tt as of scn 1 add column b int;
|
|
|
|
--error 1064
|
|
lock table tt as of scn 1;
|
|
|
|
--error 1064
|
|
handler tt as of scn 1 open;
|
|
|
|
--error 1064
|
|
replace into tt as of scn 1 valuse (1);
|
|
|
|
# =====================
|
|
set global innodb_rds_flashback_enabled = default;
|
|
drop view vv;
|
|
drop table tt;
|
|
drop table pp;
|
|
|
|
|