diff options
Diffstat (limited to 'mysql-test/suite/versioning/r')
| -rw-r--r-- | mysql-test/suite/versioning/r/alter.result | 19 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/create.result | 13 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/delete.result | 1 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/foreign.result | 65 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/online.result | 7 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/partition.result | 21 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/partition_innodb.result | 1 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/replace.result | 10 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/select.result | 10 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/truncate.result | 3 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/trx_id.result | 4 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/update-big.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/update.result | 35 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/update2.result | 21 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/view.result | 9 |
15 files changed, 125 insertions, 96 deletions
diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index d7ebb4dd096..ed8a86c0053 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -18,13 +18,13 @@ t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING alter table t add column y int; -ERROR HY000: Not allowed for system-versioned `test2`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. +ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. alter table t add primary key (a); -ERROR HY000: Not allowed for system-versioned `test2`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. +ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. alter table t add unique key (a); -ERROR HY000: Not allowed for system-versioned `test2`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. +ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. alter table t engine innodb; -ERROR HY000: Not allowed for system-versioned `test2`.`t`. Change to/from native system versioning engine is not supported. +ERROR HY000: Not allowed for system-versioned `test`.`t`. Change to/from native system versioning engine is not supported. alter table t drop system versioning; show create table t; Table Create Table @@ -80,13 +80,17 @@ t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 alter table t add column trx_start timestamp(6) as row start; -ERROR HY000: Table `t` is not system-versioned +ERROR HY000: Duplicate ROW START column `trx_start` alter table t add system versioning; show create table t; Table Create Table t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t add column trx_start timestamp(6) as row start; +ERROR HY000: Duplicate ROW START column `trx_start` +alter table t modify a int as row start; +ERROR HY000: Duplicate ROW START column `a` alter table t add column b int; show create table t; Table Create Table @@ -533,7 +537,7 @@ use test; # MDEV-15956 Strange ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN upon ALTER on versioning column create or replace table t1 (i int, j int as (i), s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning; alter table t1 modify s timestamp(6) as row start; -ERROR HY000: Can not change system versioning field `s` +ERROR HY000: Duplicate ROW START column `s` # ignore CHECK for historical rows create or replace table t (a int) with system versioning; insert into t values (0), (1); @@ -682,5 +686,4 @@ alter table t add column c int without system versioning, change column c c int, change column b b int without system versioning; -drop database test; -create database test; +drop table t; diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result index 747da5483ec..ce33a3c6c51 100644 --- a/mysql-test/suite/versioning/r/create.result +++ b/mysql-test/suite/versioning/r/create.result @@ -15,7 +15,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING select table_catalog,table_schema,table_name,table_type,version,table_rows,data_free,auto_increment,check_time,table_collation,checksum,create_options,table_comment from information_schema.tables where table_name='t1'; table_catalog def -table_schema test2 +table_schema test table_name t1 table_type SYSTEM VERSIONED version 10 @@ -29,7 +29,7 @@ create_options table_comment select table_catalog,table_schema,table_name,column_name,ordinal_position,column_default,character_maximum_length,character_octet_length,character_set_name,collation_name,column_key,extra,column_comment,is_generated,generation_expression from information_schema.columns where table_name='t1'; table_catalog def -table_schema test2 +table_schema test table_name t1 column_name x1 ordinal_position 1 @@ -44,7 +44,7 @@ column_comment is_generated NEVER generation_expression NULL table_catalog def -table_schema test2 +table_schema test table_name t1 column_name Sys_start ordinal_position 2 @@ -59,7 +59,7 @@ column_comment start is_generated ALWAYS generation_expression ROW START table_catalog def -table_schema test2 +table_schema test table_name t1 column_name Sys_end ordinal_position 3 @@ -235,7 +235,7 @@ tt1 CREATE TABLE `tt1` ( drop table tt1; create temporary table tt1 like t1; Warnings: -Warning 1105 System versioning is stripped from temporary `test2.tt1` +Warning 1105 System versioning is stripped from temporary `test.tt1` # Temporary is stripped from versioning show create table tt1; Table Create Table @@ -535,5 +535,4 @@ create or replace table t1 (x int without system versioning) with system versioning select 1 as x; ERROR HY000: Table `t1` must have at least one versioned column -drop database test; -create database test; +drop tables t0, t1, t2, t3; diff --git a/mysql-test/suite/versioning/r/delete.result b/mysql-test/suite/versioning/r/delete.result index cfce8aa3069..77b7fc80286 100644 --- a/mysql-test/suite/versioning/r/delete.result +++ b/mysql-test/suite/versioning/r/delete.result @@ -115,3 +115,4 @@ select x from t1 for system_time all; x 2 1 +drop table t1; diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result index 91942510417..67d80584a90 100644 --- a/mysql-test/suite/versioning/r/foreign.result +++ b/mysql-test/suite/versioning/r/foreign.result @@ -2,7 +2,8 @@ # Test RESTRICT # ################# create table parent( -id int unique key +id int, +KEY_TYPE (id) ) engine innodb; create table child( parent_id int, @@ -16,13 +17,13 @@ on update restrict insert into parent values(1); insert into child values(1); delete from parent where id = 1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) delete from child where parent_id = 1; delete from parent where id = 1; insert into parent values(1); insert into child values(1); update parent set id=id+1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) delete from child; update parent set id=id+1; select * from child for system_time all; @@ -35,7 +36,8 @@ drop table parent; # Test when clustered index is a foreign key # ############################################## create table parent( -id int(10) unsigned unique key +id int(10) unsigned, +KEY_TYPE (id) ) engine innodb; create table child( parent_id int(10) unsigned primary key, @@ -47,14 +49,15 @@ foreign key(parent_id) references parent(id) insert into parent values(1); insert into child values(1); delete from parent where id = 1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) drop table child; drop table parent; ################ # Test CASCADE # ################ create table parent( -id int unique key +id int, +KEY_TYPE (id) ) engine innodb; create table child( parent_id int, @@ -87,7 +90,8 @@ parent_id drop table child; drop table parent; create or replace table parent ( -id int primary key, +id int, +KEY_TYPE(id), sys_start SYS_DATATYPE as row start invisible, sys_end SYS_DATATYPE as row end invisible, period for system_time(sys_start, sys_end) @@ -110,7 +114,8 @@ x parent_id drop table child; drop table parent; create or replace table parent ( -id int primary key +id int, +KEY_TYPE(id) ) engine innodb; create or replace table child ( @@ -138,8 +143,9 @@ drop table parent; ################# # Test SET NULL # ################# -create or replace table parent( -id int unique key +create table parent( +id int, +KEY_TYPE (id) ) engine innodb; create or replace table child( parent_id int, @@ -183,7 +189,8 @@ drop table parent; # Parent table is foreign # ########################### create or replace table parent( -id int unique key, +id int, +KEY_TYPE (id), sys_start SYS_DATATYPE as row start invisible, sys_end SYS_DATATYPE as row end invisible, period for system_time(sys_start, sys_end) @@ -195,26 +202,27 @@ foreign key(parent_id) references parent(id) insert into parent values(1); insert into child values(1); delete from parent; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) update parent set id=2; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) delete from child; delete from parent; insert into child values(1); -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) insert into parent values(1); insert into child values(1); delete from parent; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) update parent set id=2; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)) drop table child; drop table parent; ################### # crash on DELETE # ################### create or replace table a ( -cola int(10) primary key, +cola int(10), +KEY_TYPE (cola), v_cola int(10) as (cola mod 10) virtual, sys_start SYS_DATATYPE as row start invisible, sys_end SYS_DATATYPE as row end invisible, @@ -233,7 +241,7 @@ foreign key (v_cola) references a (v_cola); insert into a(cola) values (12); insert into b(cola, v_cola) values (10,2); delete from a; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`b`, CONSTRAINT `v_cola_fk` FOREIGN KEY (`v_cola`) REFERENCES `a` (`v_cola`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `v_cola_fk` FOREIGN KEY (`v_cola`) REFERENCES `a` (`v_cola`)) drop table b, a; ############################################### # CASCADE UPDATE foreign not system versioned # @@ -292,15 +300,18 @@ select count(*) from subchild; count(*) 0 drop table subchild, child, parent; -CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=InnoDB; -CREATE TABLE t2 (f2 INT, FOREIGN KEY (f2) REFERENCES t1 (f1)) ENGINE=InnoDB WITH SYSTEM VERSIONING; -SET FOREIGN_KEY_CHECKS= OFF; -INSERT IGNORE INTO t2 VALUES (1); -SET FOREIGN_KEY_CHECKS= ON; -UPDATE t2 SET f2= 2; -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test2`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)) -DELETE FROM t2; -DROP TABLE t2, t1; +# +# MDEV-18057 Assertion `(node->state == 5) || (node->state == 6)' failed in row_upd_sec_step upon DELETE after UPDATE failed due to FK violation +# +create or replace table t1 (f1 int, key(f1)) engine=innodb; +create or replace table t2 (f2 int, foreign key (f2) references t1 (f1)) engine=innodb with system versioning; +set foreign_key_checks= off; +insert ignore into t2 values (1); +set foreign_key_checks= on; +update t2 set f2= 2; +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)) +delete from t2; +drop table t2, t1; # # MDEV-18879 Corrupted record inserted by FOREIGN KEY operation # diff --git a/mysql-test/suite/versioning/r/online.result b/mysql-test/suite/versioning/r/online.result index 33ace87d440..62d62913428 100644 --- a/mysql-test/suite/versioning/r/online.result +++ b/mysql-test/suite/versioning/r/online.result @@ -90,13 +90,13 @@ period for system_time(row_start, row_end) insert into t values (1, 1); select c.prtype from information_schema.innodb_sys_columns as c join information_schema.innodb_sys_tables as t on c.table_id=t.table_id -where t.name='test2/t' and c.name='b'; +where t.name='test/t' and c.name='b'; prtype 50179 set @@system_versioning_alter_history=keep; select c.prtype from information_schema.innodb_sys_columns as c join information_schema.innodb_sys_tables as t on c.table_id=t.table_id -where t.name='test2/t' and c.name='b'; +where t.name='test/t' and c.name='b'; prtype 50179 show create table t; @@ -156,5 +156,4 @@ v int as (x) virtual, y int ) with system versioning; alter table t1 drop system versioning; -drop database test; -create database test; +drop tables t, t1; diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index a28b4377945..690617551de 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -46,7 +46,7 @@ period for system_time(row_start, row_end)) engine=DEFAULT_ENGINE with system versioning partition by hash(i); alter table t1 engine=NON_DEFAULT_ENGINE; -ERROR HY000: Not allowed for system-versioned `test2`.`t1`. Change to/from native system versioning engine is not supported. +ERROR HY000: Not allowed for system-versioned `test`.`t1`. Change to/from native system versioning engine is not supported. ## CREATE TABLE create or replace table t1 (x int) partition by system_time ( @@ -266,11 +266,11 @@ x 6 insert into t1 values (7), (8); Warnings: -Warning 4114 Versioned table `test2`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions ### warn about full partition delete from t1; Warnings: -Warning 4114 Versioned table `test2`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions select * from t1 partition (p1) order by x; x 4 @@ -441,16 +441,16 @@ create or replace table t1 (i int) with system versioning partition by system_time interval 1 hour ( partition p0 history, partition pn current); set @ts=(select partition_description from information_schema.partitions -where table_schema='test2' and table_name='t1' and partition_name='p0'); +where table_schema='test' and table_name='t1' and partition_name='p0'); alter table t1 add column b int; -select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test2' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; partition_name partition_ordinal_position partition_method timediff(partition_description, @ts) p0 1 SYSTEM_TIME 00:00:00.000000 pn 2 SYSTEM_TIME NULL Warnings: Warning 1292 Incorrect time value: 'CURRENT' alter table t1 add partition (partition p1 history, partition p2 history); -select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test2' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; partition_name partition_ordinal_position partition_method timediff(partition_description, @ts) p0 1 SYSTEM_TIME 00:00:00.000000 p1 2 SYSTEM_TIME 01:00:00.000000 @@ -459,7 +459,7 @@ pn 4 SYSTEM_TIME NULL Warnings: Warning 1292 Incorrect time value: 'CURRENT' alter table t1 drop partition p0; -select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test2' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; partition_name partition_ordinal_position partition_method timediff(partition_description, @ts) p1 1 SYSTEM_TIME 01:00:00.000000 p2 2 SYSTEM_TIME 02:00:00.000000 @@ -468,7 +468,7 @@ Warnings: Warning 1292 Incorrect time value: 'CURRENT' alter table t1 drop partition p2; ERROR HY000: Can only drop oldest partitions when rotating by INTERVAL -select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test2' and table_name='t1'; +select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1'; partition_name partition_ordinal_position partition_method timediff(partition_description, @ts) p1 1 SYSTEM_TIME 01:00:00.000000 p2 2 SYSTEM_TIME 02:00:00.000000 @@ -564,7 +564,7 @@ insert into t1 values (0), (1), (2), (3); delete from t1 where x < 3; delete from t1; Warnings: -Warning 4114 Versioned table `test2`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions unlock tables; # # MDEV-20336 Assertion bitmap_is_set(read_partitions) upon SELECT FOR UPDATE from versioned table @@ -572,4 +572,5 @@ unlock tables; create or replace table t1 (pk int primary key) with system versioning partition by system_time limit 100 (partition p1 history, partition pn current); execute immediate 'select * from t1 for update'; pk -drop table t1; +drop view v1; +drop tables t, t1, t2, t3, t4; diff --git a/mysql-test/suite/versioning/r/partition_innodb.result b/mysql-test/suite/versioning/r/partition_innodb.result index 554303bcee3..afed90ffe05 100644 --- a/mysql-test/suite/versioning/r/partition_innodb.result +++ b/mysql-test/suite/versioning/r/partition_innodb.result @@ -21,6 +21,7 @@ partition p0 history, partition pn current ); ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t1` +drop table t1; create or replace table t ( a int primary key, row_start bigint unsigned as row start invisible, diff --git a/mysql-test/suite/versioning/r/replace.result b/mysql-test/suite/versioning/r/replace.result index e5dcf61678e..bda61f118b0 100644 --- a/mysql-test/suite/versioning/r/replace.result +++ b/mysql-test/suite/versioning/r/replace.result @@ -1,9 +1,10 @@ -create table t ( -id int primary key, +create or replace table t( +id int, +KEY_TYPE(id), x int, row_start SYS_DATATYPE as row start invisible, row_end SYS_DATATYPE as row end invisible, -period for system_time (row_start, row_end) +period for system_time(row_start, row_end) ) with system versioning; insert t values (1, 2); replace t values (1, 3); @@ -32,7 +33,8 @@ insert into t1 values (1,1); create or replace table t2 (c int); create or replace view v as select t1.* from t1 join t2; replace into v (a, b) select a, b from t1; -drop table t1; +drop view v; +drop tables t1, t2; CREATE TABLE t1 ( pk INT AUTO_INCREMENT, f INT, diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result index f1ef954baa6..3569268ce1d 100644 --- a/mysql-test/suite/versioning/r/select.result +++ b/mysql-test/suite/versioning/r/select.result @@ -155,21 +155,21 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test2`.`t1`.`x` AS `IJ2_x1`,`test2`.`t1`.`y` AS `y1`,`test2`.`t2`.`x` AS `x2`,`test2`.`t2`.`y` AS `y2` from `test2`.`t1` FOR SYSTEM_TIME ALL join `test2`.`t2` FOR SYSTEM_TIME ALL where `test2`.`t1`.`x` = `test2`.`t2`.`x` and `test2`.`t2`.`row_end` > @`t0` and `test2`.`t2`.`row_start` <= @`t0` and `test2`.`t1`.`row_end` > @`t0` and `test2`.`t1`.`row_start` <= @`t0` +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 ALL join `test`.`t2` FOR SYSTEM_TIME ALL where `test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t2`.`row_end` > @`t0` and `test`.`t2`.`row_start` <= @`t0` and `test`.`t1`.`row_end` > @`t0` and `test`.`t1`.`row_start` <= @`t0` 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) for system_time as of timestamp @t0 as t; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test2`.`t1`.`x` AS `LJ2_x1`,`test2`.`t1`.`y` AS `y1`,`test2`.`t2`.`x` AS `x2`,`test2`.`t2`.`y` AS `y2` from `test2`.`t1` FOR SYSTEM_TIME ALL left join `test2`.`t2` FOR SYSTEM_TIME ALL on(`test2`.`t2`.`x` = `test2`.`t1`.`x` and `test2`.`t2`.`row_end` > @`t0` and `test2`.`t2`.`row_start` <= @`t0`) where `test2`.`t1`.`row_end` > @`t0` and `test2`.`t1`.`row_start` <= @`t0` +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 ALL left join `test`.`t2` FOR SYSTEM_TIME ALL on(`test`.`t2`.`x` = `test`.`t1`.`x` and `test`.`t2`.`row_end` > @`t0` and `test`.`t2`.`row_start` <= @`t0`) where `test`.`t1`.`row_end` > @`t0` and `test`.`t1`.`row_start` <= @`t0` 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) for system_time as of timestamp @t0 as t; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test2`.`t1`.`x` AS `RJ2_x1`,`test2`.`t1`.`y` AS `y1`,`test2`.`t2`.`x` AS `x2`,`test2`.`t2`.`y` AS `y2` from `test2`.`t2` FOR SYSTEM_TIME ALL left join `test2`.`t1` FOR SYSTEM_TIME ALL on(`test2`.`t1`.`x` = `test2`.`t2`.`x` and `test2`.`t1`.`row_end` > @`t0` and `test2`.`t1`.`row_start` <= @`t0`) where `test2`.`t2`.`row_end` > @`t0` and `test2`.`t2`.`row_start` <= @`t0` +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 ALL left join `test`.`t1` FOR SYSTEM_TIME ALL on(`test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t1`.`row_end` > @`t0` and `test`.`t1`.`row_start` <= @`t0`) where `test`.`t2`.`row_end` > @`t0` and `test`.`t2`.`row_start` <= @`t0` 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) for system_time as of timestamp @t0 as t; IJ2_x1 y1 x2 y2 @@ -330,7 +330,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: -Note 1003 select `test2`.`t1`.`a` AS `a`,`test2`.`t2`.`a` AS `a` from `test2`.`t1` FOR SYSTEM_TIME ALL left join (`test2`.`t1` FOR SYSTEM_TIME ALL `t2` left join `test2`.`t1` FOR SYSTEM_TIME ALL `t3` on(`test2`.`t3`.`a` = `test2`.`t2`.`a` and `test2`.`t3`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999')) on(`test2`.`t2`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' and `test2`.`t1`.`a` > 1) where `test2`.`t1`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a` from `test`.`t1` FOR SYSTEM_TIME ALL left join (`test`.`t1` FOR SYSTEM_TIME ALL `t2` left join `test`.`t1` FOR SYSTEM_TIME ALL `t3` on(`test`.`t3`.`a` = `test`.`t2`.`a` and `test`.`t3`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999')) on(`test`.`t2`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' and `test`.`t1`.`a` > 1) where `test`.`t1`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; a a 2 1 @@ -549,7 +549,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: -Note 1003 select `test2`.`t1`.`f1` AS `f1` from `test2`.`t1` FOR SYSTEM_TIME ALL join `test2`.`t2` left join (`test2`.`t3` left join `test2`.`t4` FOR SYSTEM_TIME ALL on(`test2`.`t4`.`f4` = `test2`.`t2`.`f2` and `test2`.`t4`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999')) on(`test2`.`t3`.`f3` = `test2`.`t2`.`f2`) where `test2`.`t1`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' +Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` FOR SYSTEM_TIME ALL join `test`.`t2` left join (`test`.`t3` left join `test`.`t4` FOR SYSTEM_TIME ALL on(`test`.`t4`.`f4` = `test`.`t2`.`f2` and `test`.`t4`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999')) on(`test`.`t3`.`f3` = `test`.`t2`.`f2`) where `test`.`t1`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' drop view v1; drop table t1, t2, t3, t4; # diff --git a/mysql-test/suite/versioning/r/truncate.result b/mysql-test/suite/versioning/r/truncate.result index ad682f5e041..46ef271db3d 100644 --- a/mysql-test/suite/versioning/r/truncate.result +++ b/mysql-test/suite/versioning/r/truncate.result @@ -93,7 +93,7 @@ drop view v; create or replace table t (i int); create or replace view v as select * from t; drop table v; -ERROR 42S02: 'test2.v' is a view +ERROR 42S02: 'test.v' is a view lock table v write; delete history from v before system_time now(6); ERROR 42S02: 'v' is a view @@ -130,6 +130,7 @@ truncate t1; truncate t2; ERROR HY000: System-versioned tables do not support TRUNCATE TABLE unlock tables; +drop table t2; # # MDEV-19814 Assertion `update->n_fields < ulint(table->n_cols + table->n_v_cols)' on DELETE HISTORY # diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result index 5d4669c46e2..ee8be01dc24 100644 --- a/mysql-test/suite/versioning/r/trx_id.result +++ b/mysql-test/suite/versioning/r/trx_id.result @@ -197,9 +197,7 @@ Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be select trt_begin_ts(@trx_id) <= @ts1 as BEGIN_TS_GOOD; BEGIN_TS_GOOD 1 -drop database test; -create database test; -use test; +drop table t1; # # MDEV-16100 FOR SYSTEM_TIME erroneously resolves string user variables as transaction IDs # diff --git a/mysql-test/suite/versioning/r/update-big.result b/mysql-test/suite/versioning/r/update-big.result index 5accf0a0b56..89297fe5d89 100644 --- a/mysql-test/suite/versioning/r/update-big.result +++ b/mysql-test/suite/versioning/r/update-big.result @@ -12,7 +12,7 @@ insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; insert into t1 select * from t1; -connect con1,localhost,root,,test2; +connect con1,localhost,root,,test; alter table t1 add system versioning; connection default; update t1 set a= 7 where a = 3; diff --git a/mysql-test/suite/versioning/r/update.result b/mysql-test/suite/versioning/r/update.result index 334d8d8899b..eaa8549b38a 100644 --- a/mysql-test/suite/versioning/r/update.result +++ b/mysql-test/suite/versioning/r/update.result @@ -241,3 +241,38 @@ B2 salary 1 2500 drop table t1; drop table t2; +### Issue tempesta-tech/mariadb#365, bug 7 (duplicate of historical row) +create or replace table t1 (a int primary key, b int) +with system versioning engine myisam; +insert into t1 (a) values (1); +replace t1 values (1,2),(1,3),(2,4); +# +# MDEV-14829 Assertion `0' failed in Protocol::end_statement upon concurrent UPDATE +# +create or replace table t1 (pk int, a char(3), b char(3), primary key(pk)) +engine=innodb with system versioning; +insert into t1 (pk) values (1); +connect con1,localhost,root,,test; +start transaction; +select * from t1 for update; +pk a b +1 NULL NULL +connection default; +update t1 set b = 'foo'; +connection con1; +update t1 set a = 'bar'; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +disconnect con1; +connection default; +drop table t1; +# +# MDEV-19406 Assertion on updating view of join with versioned table +# +create or replace table t1 (pk int primary key, a date, b int, index(b)) engine=innodb with system versioning; +create or replace table t2 (c int); +create or replace view v as select * from t1 join t2; +insert into t1 (pk) values (1); +update t1 set a= '2012-12-12'; +update v set a= '2000-01-01' order by b limit 1; +drop view v; +drop table t1, t2; diff --git a/mysql-test/suite/versioning/r/update2.result b/mysql-test/suite/versioning/r/update2.result deleted file mode 100644 index 73e6c5b393d..00000000000 --- a/mysql-test/suite/versioning/r/update2.result +++ /dev/null @@ -1,21 +0,0 @@ -### Issue #365, bug 7 (duplicate of historical row) -create or replace table t1 (a int primary key, b int) -with system versioning engine myisam; -insert into t1 (a) values (1); -replace t1 values (1,2),(1,3),(2,4); -create or replace table t1 (pk int, a char(3), b char(3), primary key(pk)) -engine=innodb with system versioning; -insert into t1 (pk) values (1); -connect con1,localhost,root,,test; -start transaction; -select * from t1 for update; -pk a b -1 NULL NULL -connection default; -update t1 set b = 'foo'; -connection con1; -update t1 set a = 'bar'; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -disconnect con1; -connection default; -drop table t1; diff --git a/mysql-test/suite/versioning/r/view.result b/mysql-test/suite/versioning/r/view.result index e897f0e17d9..850eba32c0d 100644 --- a/mysql-test/suite/versioning/r/view.result +++ b/mysql-test/suite/versioning/r/view.result @@ -118,9 +118,8 @@ execute stmt; a execute stmt; a -drop database test2; -create database test2; -use test2; +drop view v1; +drop tables t1, t2; # # MDEV-15146 SQLError[4122]: View is not system versioned # @@ -148,5 +147,5 @@ create or replace view v1 as select * from t1 for system_time as of date_sub(now show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` FOR SYSTEM_TIME AS OF current_timestamp() - interval 6 second latin1 latin1_swedish_ci -drop view v1; -drop table t1; +drop view v1, vt1, vt12; +drop tables t1, t3; |