diff options
Diffstat (limited to 'mysql-test/suite/versioning/r/simple.result')
| -rw-r--r-- | mysql-test/suite/versioning/r/simple.result | 200 |
1 files changed, 200 insertions, 0 deletions
diff --git a/mysql-test/suite/versioning/r/simple.result b/mysql-test/suite/versioning/r/simple.result new file mode 100644 index 00000000..39ced386 --- /dev/null +++ b/mysql-test/suite/versioning/r/simple.result @@ -0,0 +1,200 @@ +set default_storage_engine=innodb; +create or replace table dept ( +dept_id int(10) primary key, +name varchar(100) +) +with system versioning; +create or replace table emp ( +emp_id int(10) primary key, +dept_id int(10), +name varchar(100), +salary int(10), +constraint `dept-emp-fk` + foreign key (dept_id) references dept (dept_id) +on delete restrict +on update restrict +) +with system versioning; +select now() into @ts_0; +insert into dept (dept_id, name) values (10, "accounting"); +commit; +select row_start into @ts_1 from dept where dept_id=10; +insert into emp (emp_id, name, salary, dept_id) values (1, "bill", 1000, 10); +commit; +select row_start into @ts_2 from emp where name="bill"; +select * from emp; +emp_id dept_id name salary +1 10 bill 1000 +update emp set salary=2000 where name="bill"; +commit; +select row_start into @ts_3 from emp where name="bill"; +select * from emp; +emp_id dept_id name salary +1 10 bill 2000 +select * from emp for system_time as of timestamp @ts_2; +emp_id dept_id name salary +1 10 bill 1000 +select * from emp for system_time as of timestamp @ts_3; +emp_id dept_id name salary +1 10 bill 2000 +select * from emp e, dept d +where d.dept_id = 10 +and d.dept_id = e.dept_id; +emp_id dept_id name salary dept_id name +1 10 bill 2000 10 accounting +select * from +emp for system_time from timestamp @ts_1 to timestamp @ts_2 e, +dept for system_time from timestamp @ts_1 to timestamp @ts_2 d +where d.dept_id = 10 +and d.dept_id = e.dept_id; +emp_id dept_id name salary dept_id name +set statement system_versioning_asof=@ts_0 for +select * from emp e, dept d +where d.dept_id = 10 +and d.dept_id = e.dept_id; +emp_id dept_id name salary dept_id name +set statement system_versioning_asof=@ts_1 for +select * from emp e, dept d +where d.dept_id = 10 +and d.dept_id = e.dept_id; +emp_id dept_id name salary dept_id name +set statement system_versioning_asof=@ts_2 for +select * from emp e, dept d +where d.dept_id = 10 +and d.dept_id = e.dept_id; +emp_id dept_id name salary dept_id name +1 10 bill 1000 10 accounting +set statement system_versioning_asof=@ts_3 for +select * from emp e, dept d +where d.dept_id = 10 +and d.dept_id = e.dept_id; +emp_id dept_id name salary dept_id name +1 10 bill 2000 10 accounting +drop table emp, dept; +create table t1 ( +a timestamp(6), +b timestamp(6) generated always as (a + interval 1 day), +c timestamp(6) generated always as (a + interval 1 month) stored, +d timestamp(6) generated always as row start, +e timestamp(6) generated always as row end, +period for system_time(d,e) +) with system versioning; +show columns from t1; +Field Type Null Key Default Extra +a timestamp(6) YES NULL +b timestamp(6) YES NULL VIRTUAL GENERATED +c timestamp(6) YES NULL STORED GENERATED +d timestamp(6) NO NULL STORED GENERATED +e timestamp(6) NO NULL STORED GENERATED +select table_catalog,table_schema,table_name,column_name,ordinal_position,column_default,is_nullable,data_type,character_maximum_length,character_octet_length,numeric_precision,numeric_scale,datetime_precision,character_set_name,collation_name,column_type,column_key,extra,column_comment,is_generated,generation_expression,'---' from information_schema.columns where table_name='t1'; +table_catalog def +table_schema test +table_name t1 +column_name a +ordinal_position 1 +column_default NULL +is_nullable YES +data_type timestamp +character_maximum_length NULL +character_octet_length NULL +numeric_precision NULL +numeric_scale NULL +datetime_precision 6 +character_set_name NULL +collation_name NULL +column_type timestamp(6) +column_key +extra +column_comment +is_generated NEVER +generation_expression NULL +--- --- +table_catalog def +table_schema test +table_name t1 +column_name b +ordinal_position 2 +column_default NULL +is_nullable YES +data_type timestamp +character_maximum_length NULL +character_octet_length NULL +numeric_precision NULL +numeric_scale NULL +datetime_precision 6 +character_set_name NULL +collation_name NULL +column_type timestamp(6) +column_key +extra VIRTUAL GENERATED +column_comment +is_generated ALWAYS +generation_expression `a` + interval 1 day +--- --- +table_catalog def +table_schema test +table_name t1 +column_name c +ordinal_position 3 +column_default NULL +is_nullable YES +data_type timestamp +character_maximum_length NULL +character_octet_length NULL +numeric_precision NULL +numeric_scale NULL +datetime_precision 6 +character_set_name NULL +collation_name NULL +column_type timestamp(6) +column_key +extra STORED GENERATED +column_comment +is_generated ALWAYS +generation_expression `a` + interval 1 month +--- --- +table_catalog def +table_schema test +table_name t1 +column_name d +ordinal_position 4 +column_default NULL +is_nullable NO +data_type timestamp +character_maximum_length NULL +character_octet_length NULL +numeric_precision NULL +numeric_scale NULL +datetime_precision 6 +character_set_name NULL +collation_name NULL +column_type timestamp(6) +column_key +extra STORED GENERATED +column_comment +is_generated ALWAYS +generation_expression ROW START +--- --- +table_catalog def +table_schema test +table_name t1 +column_name e +ordinal_position 5 +column_default NULL +is_nullable NO +data_type timestamp +character_maximum_length NULL +character_octet_length NULL +numeric_precision NULL +numeric_scale NULL +datetime_precision 6 +character_set_name NULL +collation_name NULL +column_type timestamp(6) +column_key +extra STORED GENERATED +column_comment +is_generated ALWAYS +generation_expression ROW END +--- --- +drop table t1; |