diff options
Diffstat (limited to 'mysql-test/suite/versioning')
30 files changed, 475 insertions, 62 deletions
diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index 5092adc2b11..b2dbbba7027 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -199,6 +199,8 @@ a 2 1 select row_start from t where a=3 into @tm; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead alter table t add column b int; select @tm=row_start from t where a=3; @tm=row_start @@ -448,11 +450,11 @@ alter table t1 engine=myisam; # MDEV-14692 crash in MDL_context::upgrade_shared_lock() create or replace temporary table t (a int); alter table t change column if exists b c bigint unsigned generated always as row start; -ERROR HY000: TEMPORARY tables do not support system versioning +ERROR HY000: System-versioned tables do not support CREATE TEMPORARY TABLE alter table t change column if exists b c bigint unsigned generated always as row end; -ERROR HY000: TEMPORARY tables do not support system versioning +ERROR HY000: System-versioned tables do not support CREATE TEMPORARY TABLE alter table t add system versioning; -ERROR HY000: TEMPORARY tables do not support system versioning +ERROR HY000: System-versioned tables do not support CREATE TEMPORARY TABLE drop table t; # MDEV-14744 trx_id-based and transaction-based mixup in assertion create or replace table t (c text) engine=innodb with system versioning; @@ -528,9 +530,9 @@ ERROR HY000: Table `t` is already system-versioned # use mysql; create or replace table t (x int) with system versioning; -ERROR HY000: System versioning tables in the `mysql` database are not supported -alter table user add system versioning; -ERROR HY000: System versioning tables in the `mysql` database are not supported +ERROR HY000: System-versioned tables in the `mysql` database are not supported +alter table db add system versioning; +ERROR HY000: System-versioned tables in the `mysql` database are not supported 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; @@ -546,6 +548,7 @@ alter table t add check (a > 0); insert into t values (0); ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t` insert into t values (2); +drop table t; # # MDEV-18869 Assertion `!((field)->vcol_info && (field)->stored_in_db())' failed in innodb_col_no upon altering table with system versioning # diff --git a/mysql-test/suite/versioning/r/commit_id.result b/mysql-test/suite/versioning/r/commit_id.result index abf2eaf91ba..8815613292e 100644 --- a/mysql-test/suite/versioning/r/commit_id.result +++ b/mysql-test/suite/versioning/r/commit_id.result @@ -10,6 +10,8 @@ insert into t1 values (); set @ts0= now(6); insert into t1 values (); select sys_trx_start from t1 where id = last_insert_id() into @tx0; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select transaction_id = @tx0 from mysql.transaction_registry order by transaction_id desc limit 1; transaction_id = @tx0 @@ -17,6 +19,8 @@ transaction_id = @tx0 set @ts1= now(6); insert into t1 values (); select sys_trx_start from t1 where id = last_insert_id() into @tx1; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select transaction_id = @tx1 from mysql.transaction_registry order by transaction_id desc limit 1; transaction_id = @tx1 @@ -24,6 +28,8 @@ transaction_id = @tx1 set @ts2= now(6); insert into t1 values (); select sys_trx_start from t1 where id = last_insert_id() into @tx2; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select transaction_id = @tx2 from mysql.transaction_registry order by transaction_id desc limit 1; transaction_id = @tx2 @@ -66,24 +72,32 @@ trt_trx_sees(0, @tx2) set transaction isolation level read uncommitted; insert into t1 values (); select sys_trx_start from t1 where id = last_insert_id() into @tx3; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select isolation_level = 'READ-UNCOMMITTED' from mysql.transaction_registry where transaction_id = @tx3; isolation_level = 'READ-UNCOMMITTED' 1 set transaction isolation level read committed; insert into t1 values (); select sys_trx_start from t1 where id = last_insert_id() into @tx4; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select isolation_level = 'READ-COMMITTED' from mysql.transaction_registry where transaction_id = @tx4; isolation_level = 'READ-COMMITTED' 1 set transaction isolation level serializable; insert into t1 values (); select sys_trx_start from t1 where id = last_insert_id() into @tx5; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select isolation_level = 'SERIALIZABLE' from mysql.transaction_registry where transaction_id = @tx5; isolation_level = 'SERIALIZABLE' 1 set transaction isolation level repeatable read; insert into t1 values (); select sys_trx_start from t1 where id = last_insert_id() into @tx6; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select isolation_level = 'REPEATABLE-READ' from mysql.transaction_registry where transaction_id = @tx6; isolation_level = 'REPEATABLE-READ' 1 diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result index fe0e6c807c3..75f7b560f30 100644 --- a/mysql-test/suite/versioning/r/create.result +++ b/mysql-test/suite/versioning/r/create.result @@ -272,8 +272,12 @@ t3 CREATE TABLE `t3` ( ## For versioned table insert into t1 values (1); select row_start from t1 into @row_start; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead insert into t0 (y) values (2); select st from t0 into @st; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead create or replace table t2 with system versioning as select * from t1; show create table t2; Table Create Table @@ -336,8 +340,12 @@ ERROR 42S21: Duplicate column name 'row_end' # Prepare checking for historical row delete from t1; select row_end from t1 for system_time all into @row_end; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead delete from t0; select en from t0 for system_time all into @en; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead ## Combinations of versioned + non-versioned create or replace table t2 (y int); insert into t2 values (3); @@ -358,10 +366,14 @@ insert into t2 (y) values (1), (2); delete from t2 where y = 2; create or replace table t3 select * from t2 for system_time all; select st, en from t3 where y = 1 into @st, @en; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select y from t2 for system_time all where st = @st and en = @en; y 1 select st, en from t3 where y = 2 into @st, @en; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select y from t2 for system_time all where st = @st and en = @en; y 2 @@ -414,7 +426,7 @@ t3 CREATE TABLE `t3` ( ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING ## Errors create or replace temporary table t (x28 int) with system versioning; -ERROR HY000: TEMPORARY tables do not support system versioning +ERROR HY000: System-versioned tables do not support CREATE TEMPORARY TABLE create or replace table t1 ( x29 int unsigned, Sys_start0 timestamp(6) as row start invisible, diff --git a/mysql-test/suite/versioning/r/cte.result b/mysql-test/suite/versioning/r/cte.result index 65cc90da2a7..5c54a23701e 100644 --- a/mysql-test/suite/versioning/r/cte.result +++ b/mysql-test/suite/versioning/r/cte.result @@ -137,7 +137,7 @@ where e.mgr = a.emp_id ) select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY emp ALL PRIMARY NULL NULL NULL 4 75.00 Using where +1 PRIMARY emp ALL PRIMARY NULL NULL NULL 4 100.00 Using where 1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 4 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 4 100.00 2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where diff --git a/mysql-test/suite/versioning/r/debug.result b/mysql-test/suite/versioning/r/debug.result index 72bd1f18a68..c1c134692a2 100644 --- a/mysql-test/suite/versioning/r/debug.result +++ b/mysql-test/suite/versioning/r/debug.result @@ -5,7 +5,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 create temporary table tt1 (a int) with system versioning; -ERROR HY000: TEMPORARY tables do not support system versioning +ERROR HY000: System-versioned tables do not support CREATE TEMPORARY TABLE set @old_dbug=@@global.debug_dbug; set global debug_dbug='+d,sysvers_force'; create table t2 (a int); diff --git a/mysql-test/suite/versioning/r/delete_history.result b/mysql-test/suite/versioning/r/delete_history.result index 95d7304c3dd..4fc4618d110 100644 --- a/mysql-test/suite/versioning/r/delete_history.result +++ b/mysql-test/suite/versioning/r/delete_history.result @@ -106,6 +106,31 @@ call pr; call pr; drop procedure pr; drop table t1; +# MDEV-15966 Behavior for TRUNCATE versioned table is not documented and not covered by tests +create or replace table t1 (id int); +create or replace table t2 (id int) with system versioning; +# force cleaning table shares +flush tables t1, t2; +truncate table t1; +truncate table t2; +ERROR HY000: System-versioned tables do not support TRUNCATE TABLE +# fetch table shares +describe t1; +Field Type Null Key Default Extra +id int(11) YES NULL +describe t2; +Field Type Null Key Default Extra +id int(11) YES NULL +truncate table t1; +truncate table t2; +ERROR HY000: System-versioned tables do not support TRUNCATE TABLE +# enter locked tables mode +lock tables t1 WRITE, t2 WRITE; +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/foreign.result b/mysql-test/suite/versioning/r/foreign.result index e54afdbc74e..288909bbc37 100644 --- a/mysql-test/suite/versioning/r/foreign.result +++ b/mysql-test/suite/versioning/r/foreign.result @@ -274,6 +274,8 @@ on update cascade ) engine=innodb; insert into parent (value) values (23); select id, value from parent into @id, @value; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead insert into child values (default, @id, @value); insert into subchild values (default, @id, @value); select parent_id from subchild; @@ -358,7 +360,7 @@ key(f) INSERT INTO t2 VALUES (1,'against'),(2,'q'); SET SQL_MODE= ''; SET timestamp = 2; -SELECT * FROM t1 INTO OUTFILE 't1.data'; +SELECT * INTO OUTFILE 't1.data' FROM t1; SET timestamp = 3; UPDATE t1 SET f13 = 'q'; SET timestamp = 4; @@ -367,7 +369,7 @@ Warnings: Warning 1265 Data truncated for column 'f12' at row 2 Warning 1265 Data truncated for column 'f12' at row 4 Warning 1265 Data truncated for column 'f12' at row 7 -SELECT * FROM t1 INTO OUTFILE 't1.data.2' ; +SELECT * INTO OUTFILE 't1.data.2' FROM t1; SET timestamp = 5; LOAD DATA INFILE 't1.data.2' REPLACE INTO TABLE t1; Warnings: @@ -376,7 +378,7 @@ Warning 1265 Data truncated for column 'f12' at row 2 Warning 1265 Data truncated for column 'f12' at row 4 Warning 1265 Data truncated for column 'f12' at row 7 Warning 1265 Data truncated for column 'f1' at row 10 -SELECT * FROM t2 INTO OUTFILE 't2.data'; +SELECT * INTO OUTFILE 't2.data' FROM t2; SET timestamp = 6; LOAD DATA INFILE 't2.data' REPLACE INTO TABLE t2; SET FOREIGN_KEY_CHECKS = OFF; diff --git a/mysql-test/suite/versioning/r/insert.result b/mysql-test/suite/versioning/r/insert.result index 01d829d3430..2645d0184e8 100644 --- a/mysql-test/suite/versioning/r/insert.result +++ b/mysql-test/suite/versioning/r/insert.result @@ -54,6 +54,8 @@ drop view vt1_1; create or replace table t1( id bigint primary key, a int, b int) with system versioning; insert into t1 values(1, 1, 1); select row_start, row_end from t1 into @sys_start, @sys_end; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select id, a, b from t1; id a b 1 1 1 diff --git a/mysql-test/suite/versioning/r/load_data.result b/mysql-test/suite/versioning/r/load_data.result index 5e7b36c9a6a..1fcde73e565 100644 --- a/mysql-test/suite/versioning/r/load_data.result +++ b/mysql-test/suite/versioning/r/load_data.result @@ -1,6 +1,8 @@ CREATE TABLE t1 (a INT, b INT, c INT, vc INT AS (c), UNIQUE(a), UNIQUE(b)) WITH SYSTEM VERSIONING; INSERT IGNORE INTO t1 (a,b,c) VALUES (1,2,3); SELECT a, b, c FROM t1 INTO OUTFILE '15330.data'; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead LOAD DATA INFILE '15330.data' IGNORE INTO TABLE t1 (a,b,c); Warnings: Warning 1062 Duplicate entry '1' for key 'a' diff --git a/mysql-test/suite/versioning/r/online.result b/mysql-test/suite/versioning/r/online.result index aabf2b98cbf..62d62913428 100644 --- a/mysql-test/suite/versioning/r/online.result +++ b/mysql-test/suite/versioning/r/online.result @@ -69,6 +69,7 @@ affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 1 Warnings: Warning 1280 Name 'pk' ignored for PRIMARY key. +# restart update t1 set a=2; select count(*) from t1 for system_time all; count(*) @@ -115,3 +116,44 @@ select count(*) from t for system_time all; count(*) 2 drop table t; +# Start of 10.4 tests +create or replace table t (a int, b int) engine=innodb; +alter table t +add s bigint unsigned as row start, +add e bigint unsigned as row end, +add period for system_time(s, e), +add system versioning; +alter table t drop column b, algorithm=instant; +alter table t add index idx(a), lock=none; +alter table t drop column s, drop column e; +alter table t drop system versioning, lock=none; +ERROR 0A000: LOCK=NONE is not supported. Reason: Not implemented for system-versioned operations. Try LOCK=SHARED +# +# MDEV-17697 Broken versioning info after instant drop column +# +set @@system_versioning_alter_history= keep; +create or replace table t1 (a int, b int) with system versioning; +insert into t1 values (1, 1); +alter table t1 drop column b, algorithm=instant; +alter table t1 drop system versioning; +create or replace table t1 (a int, b int) with system versioning; +insert into t1 values (1, 1); +alter table t1 drop system versioning; +# +# MDEV-18173 Assertion `o->ind == vers_end' or `o->ind == vers_start' failed in dict_table_t::instant_column +# +set @@system_versioning_alter_history= keep; +create or replace table t1 (pk integer primary key, a int, b int, v int as (a)) +with system versioning; +alter table t1 force; +alter table t1 drop column b; +# +# MDEV-18122 Assertion `table->versioned() == m_prebuilt->table->versioned()' failed in ha_innobase::open +# +create or replace table t1 ( +x int, +v int as (x) virtual, +y int +) with system versioning; +alter table t1 drop system versioning; +drop tables t, t1; diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index d2cf32e5bd0..9ccc900cd12 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -149,6 +149,8 @@ x C D 1 1 1 set @str= concat('select row_start from t1 partition (pn) into @ts0'); prepare stmt from @str; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead execute stmt; drop prepare stmt; set @now= now(6); @@ -160,6 +162,8 @@ execute select_pn; x C D set @str= concat('select row_start from t1 partition (p0) into @ts1'); prepare stmt from @str; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead execute stmt; drop prepare stmt; select @ts0 = @ts1; @@ -175,6 +179,8 @@ x C D 2 1 1 set @str= concat('select row_start from t1 partition (pn) into @ts0'); prepare stmt from @str; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead execute stmt; drop prepare stmt; set @now= now(6); @@ -190,14 +196,20 @@ drop prepare select_p0; drop prepare select_pn; set @str= concat('select row_start from t1 partition (p0) where x = 2 into @ts1'); prepare stmt from @str; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead execute stmt; drop prepare stmt; set @str= concat('select row_end from t1 partition (p0) where x = 2 into @ts2'); prepare stmt from @str; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead execute stmt; drop prepare stmt; set @str= concat('select row_start from t1 partition (pn) into @ts3'); prepare stmt from @str; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead execute stmt; drop prepare stmt; select @ts0 = @ts1; @@ -370,6 +382,8 @@ create or replace table t2 (f int); create or replace trigger tr before insert on t2 for each row select table_rows from information_schema.tables where table_name = 't1' into @a; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead insert into t2 values (1); # MDEV-14740 Locking assertion for system_time partitioning create or replace table t1 (i int) with system versioning @@ -379,15 +393,9 @@ partition pn current); create or replace table t2 (f int); create or replace trigger tr before insert on t2 for each row select count(*) from t1 into @a; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead insert into t2 values (1); -# MDEV-14741 Assertion `(trx)->start_file == 0' failed in row_truncate_table_for_mysql() -create or replace table t1 (i int) with system versioning -partition by system_time interval 1 hour ( -partition p1 history, -partition pn current); -set autocommit= off; -truncate table t1; -set autocommit= on; # MDEV-14747 ALTER PARTITION BY SYSTEM_TIME after LOCK TABLES create or replace table t1 (x int) with system versioning; lock table t1 write; @@ -440,7 +448,7 @@ partition_name partition_ordinal_position partition_method timediff(partition_de p0 1 SYSTEM_TIME 00:00:00.000000 pn 2 SYSTEM_TIME NULL Warnings: -Warning 1292 Truncated incorrect time value: 'CURRENT' +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='test' and table_name='t1'; partition_name partition_ordinal_position partition_method timediff(partition_description, @ts) @@ -449,7 +457,7 @@ p1 2 SYSTEM_TIME 01:00:00.000000 p2 3 SYSTEM_TIME 02:00:00.000000 pn 4 SYSTEM_TIME NULL Warnings: -Warning 1292 Truncated incorrect time value: 'CURRENT' +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='test' and table_name='t1'; partition_name partition_ordinal_position partition_method timediff(partition_description, @ts) @@ -457,7 +465,7 @@ p1 1 SYSTEM_TIME 01:00:00.000000 p2 2 SYSTEM_TIME 02:00:00.000000 pn 3 SYSTEM_TIME NULL Warnings: -Warning 1292 Truncated incorrect time value: 'CURRENT' +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='test' and table_name='t1'; @@ -466,7 +474,7 @@ p1 1 SYSTEM_TIME 01:00:00.000000 p2 2 SYSTEM_TIME 02:00:00.000000 pn 3 SYSTEM_TIME NULL Warnings: -Warning 1292 Truncated incorrect time value: 'CURRENT' +Warning 1292 Incorrect time value: 'CURRENT' # # MDEV-15103 Assertion in ha_partition::part_records() for updating VIEW # @@ -711,7 +719,7 @@ f varchar(45000) partition by system_time interval 1 year (partition p1 history, partition pn current); insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); -select * from t1 into outfile 'load.data'; +select * into outfile 'load.data' from t1; load data infile 'load.data' replace into table t1; load data infile 'load.data' replace into table t1; ERROR HY000: The table 't1' is full @@ -748,3 +756,24 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pn # NULL NULL NULL NULL # # drop table t1; # End of 10.3 tests +# +# MDEV-22283 Server crashes in key_copy or unexpected error 156: The table already existed in the storage engine +# +create table t1 (a int primary key) engine=aria page_checksum=0 +with system versioning +partition by system_time (partition p1 history, partition pn current); +alter table t1 add partition (partition p2 history); +Warnings: +Warning 4115 Maybe missing parameters: no rotation condition for multiple HISTORY partitions. +show table status; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary +t1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Got error 174 "Fatal error during initialization of handler" from storage engine Aria NULL NULL +Warnings: +Warning 1030 Got error 174 "Fatal error during initialization of handler" from storage engine Aria +drop table t1; +create table t1 (b int) engine=aria row_format=dynamic with system versioning +partition by system_time (partition p1 history, partition pn current); +insert into t1 values (1); +replace into t1 values (1); +drop table t1; +# End of 10.4 tests diff --git a/mysql-test/suite/versioning/r/partition_innodb.result b/mysql-test/suite/versioning/r/partition_innodb.result index b9870797cd2..1bc0549a735 100644 --- a/mysql-test/suite/versioning/r/partition_innodb.result +++ b/mysql-test/suite/versioning/r/partition_innodb.result @@ -20,7 +20,7 @@ alter table t1 partition by system_time ( partition p0 history, partition pn current ); -ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `#sql-temporary` +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, @@ -32,7 +32,7 @@ partition by key() ( partition p1, partition p2 ); -ERROR HY000: Transaction-precise system versioned tables do not support partitioning by ROW START or ROW END +ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END create or replace table t ( a int primary key, row_start bigint unsigned as row start invisible, @@ -43,7 +43,7 @@ partition by key(a, row_start) ( partition p1, partition p2 ); -ERROR HY000: Transaction-precise system versioned tables do not support partitioning by ROW START or ROW END +ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END create or replace table t ( a int primary key, row_start bigint unsigned as row start invisible, @@ -54,7 +54,7 @@ partition by hash(a + row_end * 2) ( partition p1, partition p2 ); -ERROR HY000: Transaction-precise system versioned tables do not support partitioning by ROW START or ROW END +ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END create or replace table t ( a int primary key, row_start bigint unsigned as row start invisible, @@ -64,7 +64,7 @@ period for system_time(row_start, row_end) partition by range columns (a, row_start) ( partition p1 values less than (100, 100) ); -ERROR HY000: Transaction-precise system versioned tables do not support partitioning by ROW START or ROW END +ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END # # MDEV-18794 Assertion `!m_innodb' failed in ha_partition::cmp_ref upon SELECT from partitioned table # diff --git a/mysql-test/suite/versioning/r/select,trx_id.rdiff b/mysql-test/suite/versioning/r/select,trx_id.rdiff new file mode 100644 index 00000000000..8906007a348 --- /dev/null +++ b/mysql-test/suite/versioning/r/select,trx_id.rdiff @@ -0,0 +1,11 @@ +--- select.result 2018-06-29 18:09:17.962447067 +0200 ++++ select.reject 2018-06-29 18:10:04.618808616 +0200 +@@ -17,6 +17,8 @@ + (8, 108), + (9, 109); + set @t0= now(6); ++Warnings: ++Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead + delete from t1 where x = 3; + delete from t1 where x > 7; + insert into t1(x, y) values(3, 33); diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result index 6e0b5784402..aa46d810ed1 100644 --- a/mysql-test/suite/versioning/r/select.result +++ b/mysql-test/suite/versioning/r/select.result @@ -21,6 +21,8 @@ delete from t1 where x = 3; delete from t1 where x > 7; insert into t1(x, y) values(3, 33); select sys_trx_start from t1 where x = 3 and y = 33 into @t1; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select x, y from t1; x y 0 100 @@ -192,6 +194,40 @@ NULL NULL 2 1 NULL NULL 3 1 drop table t1; drop table t2; +create or replace table t1(x int) with system versioning; +insert into t1 values (1); +delete from t1; +insert into t1 values (2); +delete from t1; +insert into t1 values (3); +delete from t1; +select row_start into @start1 from t1 for system_time all where x = 1; +select row_end into @end1 from t1 for system_time all where x = 1; +select row_start into @start2 from t1 for system_time all where x = 2; +select row_end into @end2 from t1 for system_time all where x = 2; +select row_start into @start3 from t1 for system_time all where x = 3; +select row_end into @end3 from t1 for system_time all where x = 3; +select x as ASOF_x from t1 for system_time as of @start2; +ASOF_x +2 +select x as ASOF_x from t1 for system_time as of @end2; +ASOF_x +select x as FROMTO_x from t1 for system_time from @start1 to @end3; +FROMTO_x +1 +2 +3 +select x as FROMTO_x from t1 for system_time from @end1 to @start2; +FROMTO_x +select x as BETWAND_x from t1 for system_time between @start1 and @end3; +BETWAND_x +1 +2 +3 +select x as BETWAND_x from t1 for system_time between @end1 and @start2; +BETWAND_x +2 +drop table t1; create table t1( A int ) with system versioning; @@ -344,6 +380,8 @@ insert into t1 values (1); set @ts= now(6); delete from t1; select sys_trx_start from t1 for system_time all into @trx_start; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead ## ensure @trx_start is much lower than unix timestamp select @trx_start < unix_timestamp(@ts) - 100 as trx_start_good; trx_start_good @@ -540,7 +578,11 @@ period for system_time (row_start, row_end) insert into t1 values (1); delete from t1; select row_start from t1 for system_time all into @t1; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select row_end from t1 for system_time all into @t2; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select * from t1 for system_time between @t1 and @t2; a 1 diff --git a/mysql-test/suite/versioning/r/select2,trx_id.rdiff b/mysql-test/suite/versioning/r/select2,trx_id.rdiff index d4b35c45568..4075b1b0f28 100644 --- a/mysql-test/suite/versioning/r/select2,trx_id.rdiff +++ b/mysql-test/suite/versioning/r/select2,trx_id.rdiff @@ -1,15 +1,15 @@ ---- select2.result -+++ select2,trx_id.result~ -@@ -22,6 +22,8 @@ - delete from t1 where x > 7; - insert into t1(x, y) values(3, 33); +--- select2.result 2018-06-29 17:51:17.142172085 +0200 ++++ select2,trx_id.reject 2018-06-29 18:03:49.034273090 +0200 +@@ -26,6 +26,8 @@ select sys_start from t1 where x = 3 and y = 33 into @t1; + Warnings: + Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +set @x1= @t1; +select trt_commit_ts(@x1) into @t1; select x, y from t1; x y 0 100 -@@ -82,7 +84,7 @@ +@@ -86,7 +88,7 @@ 8 108 9 109 3 33 @@ -18,7 +18,7 @@ ASOF2_x y 0 100 1 101 -@@ -94,7 +96,7 @@ +@@ -98,7 +100,7 @@ 7 107 8 108 9 109 @@ -27,7 +27,7 @@ FROMTO2_x y 0 100 1 101 -@@ -106,7 +108,7 @@ +@@ -110,7 +112,7 @@ 7 107 8 108 9 109 diff --git a/mysql-test/suite/versioning/r/select2.result b/mysql-test/suite/versioning/r/select2.result index 90607335fc1..8c9c519c349 100644 --- a/mysql-test/suite/versioning/r/select2.result +++ b/mysql-test/suite/versioning/r/select2.result @@ -18,10 +18,14 @@ insert into t1 (x, y) values (9, 109); set @t0= now(6); select sys_start from t1 limit 1 into @x0; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead delete from t1 where x = 3; delete from t1 where x > 7; insert into t1(x, y) values(3, 33); select sys_start from t1 where x = 3 and y = 33 into @t1; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select x, y from t1; x y 0 100 @@ -330,7 +334,7 @@ x y select * from (select * from t1 for system_time all, t2 for system_time all) for system_time all as t; ERROR HY000: Table `t` is not system-versioned select * from (t1 for system_time all join t2 for system_time all) for system_time all; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'system_time all' at line 1 # MDEV-16043 Assertion thd->Item_change_list::is_empty() failed in mysql_parse upon SELECT from a view reading from a versioned table create or replace table t1 (a int) with system versioning; create or replace view v1 as select * from t1; diff --git a/mysql-test/suite/versioning/r/sysvars.result b/mysql-test/suite/versioning/r/sysvars.result index ee9509a0039..a5a3f79990c 100644 --- a/mysql-test/suite/versioning/r/sysvars.result +++ b/mysql-test/suite/versioning/r/sysvars.result @@ -70,7 +70,7 @@ Variable_name Value system_versioning_asof 1990-01-01 00:00:00.000000 set global system_versioning_asof= timestamp'1991-11-11 11:11:11.1111119'; Warnings: -Note 1292 Truncated incorrect datetime value: '1991-11-11 11:11:11.1111119' +Note 1292 Truncated incorrect DATETIME value: '1991-11-11 11:11:11.1111119' show global variables like 'system_versioning_asof'; Variable_name Value system_versioning_asof 1991-11-11 11:11:11.111111 @@ -96,7 +96,7 @@ Variable_name Value system_versioning_asof 1990-01-01 00:00:00.000000 set system_versioning_asof= timestamp'1991-11-11 11:11:11.1111119'; Warnings: -Note 1292 Truncated incorrect datetime value: '1991-11-11 11:11:11.1111119' +Note 1292 Truncated incorrect DATETIME value: '1991-11-11 11:11:11.1111119' show variables like 'system_versioning_asof'; Variable_name Value system_versioning_asof 1991-11-11 11:11:11.111111 @@ -233,3 +233,24 @@ y 3 4 drop tables t1, t2; +# +# MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP +# +SET sql_mode=TIME_ROUND_FRACTIONAL; +SET @@global.system_versioning_asof= timestamp'2001-12-31 23:59:59.9999999'; +Warnings: +Note 1292 Truncated incorrect DATETIME value: '2001-12-31 23:59:59.9999999' +SELECT @@global.system_versioning_asof; +@@global.system_versioning_asof +2002-01-01 00:00:00.000000 +SET @@global.system_versioning_asof= DEFAULT; +# +# MDEV-23562 Assertion `time_type == MYSQL_TIMESTAMP_DATETIME' failed upon SELECT from versioned table +# +CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING; +SET system_versioning_asof= DATE(NOW()); +SELECT * FROM t1; +a +DROP TABLE t1; +SET system_versioning_asof= DEFAULT; +# End of 10.4 tests diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result index bad7272419e..1210262fcd0 100644 --- a/mysql-test/suite/versioning/r/trx_id.result +++ b/mysql-test/suite/versioning/r/trx_id.result @@ -25,11 +25,15 @@ add period for system_time(s, e), add system versioning, algorithm=inplace; select s from t1 into @trx_start; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select count(*) = 1 from mysql.transaction_registry where transaction_id = @trx_start; count(*) = 1 1 create or replace table t1 (x int); select count(*) from mysql.transaction_registry into @tmp; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead alter table t1 add column s bigint unsigned as row start, add column e bigint unsigned as row end, @@ -48,11 +52,15 @@ add period for system_time(s, e), add system versioning, algorithm=copy; select s from t1 into @trx_start; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select count(*) = 1 from mysql.transaction_registry where transaction_id = @trx_start; count(*) = 1 1 create or replace table t1 (x int); select count(*) from mysql.transaction_registry into @tmp; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead alter table t1 add column s bigint unsigned as row start, add column e bigint unsigned as row end, @@ -104,8 +112,14 @@ set @ts2= sysdate(6); commit; set @ts3= sysdate(6); select sys_start from t1 where x = 1 into @trx_id1; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select sys_start from t1 where x = 2 into @trx_id2; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select sys_start from t1 where x = 3 into @trx_id3; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select @trx_id1 < @trx_id2, @trx_id2 < @trx_id3; @trx_id1 < @trx_id2 @trx_id2 < @trx_id3 1 1 @@ -186,6 +200,8 @@ set @ts1= now(6); insert into t1 values (1); commit; select row_start from t1 into @trx_id; +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead select trt_begin_ts(@trx_id) <= @ts1 as BEGIN_TS_GOOD; BEGIN_TS_GOOD 1 diff --git a/mysql-test/suite/versioning/r/update.result b/mysql-test/suite/versioning/r/update.result index fbb9f541b06..da893432749 100644 --- a/mysql-test/suite/versioning/r/update.result +++ b/mysql-test/suite/versioning/r/update.result @@ -399,3 +399,19 @@ a check_row(row_start, row_end) 1 HISTORICAL ROW 1 CURRENT ROW drop tables t1, t2, t3; +# +# MDEV-24522 Assertion `inited==NONE' fails upon UPDATE on versioned table with unique blob + +create table t1 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning; +insert into t1 values (1, 1, 'foo'), (2, 11, 'bar'); +update t1 set a = 3 where b <= 9; +update t1 set a = 3 where b <= 10; +drop table t1; +create table t1 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning; +create table t2 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning; +insert into t1 values (1, 1, 'foo'), (2, 11, 'bar'); +insert into t2 values (1, 1, 'foo'), (2, 11, 'bar'); +update t1 set a = 3 where b <= 9; +update t2 set a = 3 where b <= 9; +update t1, t2 set t1.a = 3, t2.a = 3 where t1.b <= 10 and t2.b <= 10 and t1.b = t2.b; +drop tables t1, t2; diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test index 3ce87817e06..16f391b1454 100644 --- a/mysql-test/suite/versioning/t/alter.test +++ b/mysql-test/suite/versioning/t/alter.test @@ -375,11 +375,11 @@ alter table t1 engine=myisam; --echo # MDEV-14692 crash in MDL_context::upgrade_shared_lock() create or replace temporary table t (a int); ---error ER_VERS_TEMPORARY +--error ER_VERS_NOT_SUPPORTED alter table t change column if exists b c bigint unsigned generated always as row start; ---error ER_VERS_TEMPORARY +--error ER_VERS_NOT_SUPPORTED alter table t change column if exists b c bigint unsigned generated always as row end; ---error ER_VERS_TEMPORARY +--error ER_VERS_NOT_SUPPORTED alter table t add system versioning; drop table t; @@ -457,7 +457,7 @@ use mysql; --error ER_VERS_DB_NOT_SUPPORTED create or replace table t (x int) with system versioning; --error ER_VERS_DB_NOT_SUPPORTED -alter table user add system versioning; +alter table db add system versioning; use test; --echo # MDEV-15956 Strange ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN upon ALTER on versioning column @@ -475,6 +475,7 @@ alter table t add check (a > 0); --error ER_CONSTRAINT_FAILED insert into t values (0); insert into t values (2); +drop table t; --echo # --echo # MDEV-18869 Assertion `!((field)->vcol_info && (field)->stored_in_db())' failed in innodb_col_no upon altering table with system versioning diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test index 1d9f3a1b341..6f8cff3ca2e 100644 --- a/mysql-test/suite/versioning/t/create.test +++ b/mysql-test/suite/versioning/t/create.test @@ -310,7 +310,7 @@ show create table t3; --echo ## Errors ---error ER_VERS_TEMPORARY +--error ER_VERS_NOT_SUPPORTED create or replace temporary table t (x28 int) with system versioning; --error ER_VERS_DUPLICATE_ROW_START_END diff --git a/mysql-test/suite/versioning/t/debug.test b/mysql-test/suite/versioning/t/debug.test index c6d5bd60861..5069d7f6002 100644 --- a/mysql-test/suite/versioning/t/debug.test +++ b/mysql-test/suite/versioning/t/debug.test @@ -3,7 +3,7 @@ create table t1 (a int); show create table t1; ---error ER_VERS_TEMPORARY +--error ER_VERS_NOT_SUPPORTED create temporary table tt1 (a int) with system versioning; set @old_dbug=@@global.debug_dbug; diff --git a/mysql-test/suite/versioning/t/delete_history.test b/mysql-test/suite/versioning/t/delete_history.test index bd605c689ad..94e58590dd9 100644 --- a/mysql-test/suite/versioning/t/delete_history.test +++ b/mysql-test/suite/versioning/t/delete_history.test @@ -117,6 +117,35 @@ call pr; drop procedure pr; drop table t1; +--echo # MDEV-15966 Behavior for TRUNCATE versioned table is not documented and not covered by tests +create or replace table t1 (id int); +create or replace table t2 (id int) with system versioning; + +-- echo # force cleaning table shares +flush tables t1, t2; + +truncate table t1; +--error ER_VERS_NOT_SUPPORTED +truncate table t2; + +-- echo # fetch table shares +describe t1; +describe t2; + +truncate table t1; +--error ER_VERS_NOT_SUPPORTED +truncate table t2; + +--echo # enter locked tables mode +lock tables t1 WRITE, t2 WRITE; + +truncate t1; +--error ER_VERS_NOT_SUPPORTED +truncate t2; + +unlock tables; +drop table t2; + --echo # --echo # MDEV-19814 Assertion `update->n_fields < ulint(table->n_cols + table->n_v_cols)' on DELETE HISTORY --echo # diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test index 725f51f0660..4143cd59048 100644 --- a/mysql-test/suite/versioning/t/foreign.test +++ b/mysql-test/suite/versioning/t/foreign.test @@ -396,15 +396,15 @@ INSERT INTO t2 VALUES (1,'against'),(2,'q'); SET SQL_MODE= ''; SET timestamp = 2; -SELECT * FROM t1 INTO OUTFILE 't1.data'; +SELECT * INTO OUTFILE 't1.data' FROM t1; SET timestamp = 3; UPDATE t1 SET f13 = 'q'; SET timestamp = 4; LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1; -SELECT * FROM t1 INTO OUTFILE 't1.data.2' ; +SELECT * INTO OUTFILE 't1.data.2' FROM t1; SET timestamp = 5; LOAD DATA INFILE 't1.data.2' REPLACE INTO TABLE t1; -SELECT * FROM t2 INTO OUTFILE 't2.data'; +SELECT * INTO OUTFILE 't2.data' FROM t2; SET timestamp = 6; LOAD DATA INFILE 't2.data' REPLACE INTO TABLE t2; SET FOREIGN_KEY_CHECKS = OFF; diff --git a/mysql-test/suite/versioning/t/online.test b/mysql-test/suite/versioning/t/online.test index cff3193407b..5932c34687a 100644 --- a/mysql-test/suite/versioning/t/online.test +++ b/mysql-test/suite/versioning/t/online.test @@ -133,7 +133,66 @@ show create table t; select count(*) from t for system_time all; update t set b=11; select count(*) from t for system_time all; - drop table t; +--echo # Start of 10.4 tests + +create or replace table t (a int, b int) engine=innodb; +alter table t + add s bigint unsigned as row start, + add e bigint unsigned as row end, + add period for system_time(s, e), + add system versioning; +alter table t drop column b, algorithm=instant; +alter table t add index idx(a), lock=none; +alter table t drop column s, drop column e; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t drop system versioning, lock=none; + +--echo # +--echo # MDEV-17697 Broken versioning info after instant drop column +--echo # +set @@system_versioning_alter_history= keep; +create or replace table t1 (a int, b int) with system versioning; +insert into t1 values (1, 1); +alter table t1 drop column b, algorithm=instant; +alter table t1 drop system versioning; + +create or replace table t1 (a int, b int) with system versioning; +insert into t1 values (1, 1); + +if ($have_debug) { +--disable_query_log +--disable_result_log +set debug_dbug='+d,ib_commit_inplace_fail_1'; +--error ER_INTERNAL_ERROR +alter table t1 drop column b, algorithm=instant; +set debug_dbug= default; +--enable_query_log +--enable_result_log +} +alter table t1 drop system versioning; + +--echo # +--echo # MDEV-18173 Assertion `o->ind == vers_end' or `o->ind == vers_start' failed in dict_table_t::instant_column +--echo # +set @@system_versioning_alter_history= keep; +create or replace table t1 (pk integer primary key, a int, b int, v int as (a)) +with system versioning; + +alter table t1 force; +alter table t1 drop column b; + +--echo # +--echo # MDEV-18122 Assertion `table->versioned() == m_prebuilt->table->versioned()' failed in ha_innobase::open +--echo # +create or replace table t1 ( + x int, + v int as (x) virtual, + y int +) with system versioning; +alter table t1 drop system versioning; + +drop tables t, t1; + --source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index c5f6b242b53..c6279444a6a 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -341,15 +341,6 @@ create or replace trigger tr before insert on t2 for each row select count(*) from t1 into @a; insert into t2 values (1); ---echo # MDEV-14741 Assertion `(trx)->start_file == 0' failed in row_truncate_table_for_mysql() -create or replace table t1 (i int) with system versioning -partition by system_time interval 1 hour ( - partition p1 history, - partition pn current); -set autocommit= off; -truncate table t1; -set autocommit= on; - --echo # MDEV-14747 ALTER PARTITION BY SYSTEM_TIME after LOCK TABLES create or replace table t1 (x int) with system versioning; lock table t1 write; @@ -684,7 +675,7 @@ create or replace table t1 ( insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); -select * from t1 into outfile 'load.data'; +select * into outfile 'load.data' from t1; load data infile 'load.data' replace into table t1; --error ER_RECORD_FILE_FULL load data infile 'load.data' replace into table t1; @@ -729,4 +720,25 @@ drop table t1; --echo # End of 10.3 tests +--echo # +--echo # MDEV-22283 Server crashes in key_copy or unexpected error 156: The table already existed in the storage engine +--echo # +create table t1 (a int primary key) engine=aria page_checksum=0 +with system versioning +partition by system_time (partition p1 history, partition pn current); + +alter table t1 add partition (partition p2 history); +show table status; +drop table t1; + +create table t1 (b int) engine=aria row_format=dynamic with system versioning +partition by system_time (partition p1 history, partition pn current); +insert into t1 values (1); +replace into t1 values (1); + +# cleanup +drop table t1; + +--echo # End of 10.4 tests + --source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/partition_innodb.test b/mysql-test/suite/versioning/t/partition_innodb.test index 29ec58af13c..e09a684961e 100644 --- a/mysql-test/suite/versioning/t/partition_innodb.test +++ b/mysql-test/suite/versioning/t/partition_innodb.test @@ -22,7 +22,6 @@ create or replace table t1( period for system_time(row_start, row_end) ) engine=InnoDB with system versioning; ---replace_regex /#sql-[0-9a-f_]*/#sql-temporary/ --error ER_VERS_FIELD_WRONG_TYPE alter table t1 partition by system_time ( partition p0 history, diff --git a/mysql-test/suite/versioning/t/select.test b/mysql-test/suite/versioning/t/select.test index f1217c382cf..1d5d0f323be 100644 --- a/mysql-test/suite/versioning/t/select.test +++ b/mysql-test/suite/versioning/t/select.test @@ -108,6 +108,32 @@ for system_time as of timestamp @t0 as t; drop table t1; drop table t2; +# Query conditions check + +create or replace table t1(x int) with system versioning; +insert into t1 values (1); +delete from t1; +insert into t1 values (2); +delete from t1; +insert into t1 values (3); +delete from t1; + +select row_start into @start1 from t1 for system_time all where x = 1; +select row_end into @end1 from t1 for system_time all where x = 1; +select row_start into @start2 from t1 for system_time all where x = 2; +select row_end into @end2 from t1 for system_time all where x = 2; +select row_start into @start3 from t1 for system_time all where x = 3; +select row_end into @end3 from t1 for system_time all where x = 3; + +select x as ASOF_x from t1 for system_time as of @start2; +select x as ASOF_x from t1 for system_time as of @end2; +select x as FROMTO_x from t1 for system_time from @start1 to @end3; +select x as FROMTO_x from t1 for system_time from @end1 to @start2; +select x as BETWAND_x from t1 for system_time between @start1 and @end3; +select x as BETWAND_x from t1 for system_time between @end1 and @start2; + +drop table t1; + # Wildcard expansion on hidden fields create table t1( diff --git a/mysql-test/suite/versioning/t/sysvars.test b/mysql-test/suite/versioning/t/sysvars.test index eb76581121d..ab1f612edf3 100644 --- a/mysql-test/suite/versioning/t/sysvars.test +++ b/mysql-test/suite/versioning/t/sysvars.test @@ -176,3 +176,25 @@ insert t2 select x + 2 from t1; select * from t2; drop tables t1, t2; + + +--echo # +--echo # MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP +--echo # + +SET sql_mode=TIME_ROUND_FRACTIONAL; +SET @@global.system_versioning_asof= timestamp'2001-12-31 23:59:59.9999999'; +SELECT @@global.system_versioning_asof; +SET @@global.system_versioning_asof= DEFAULT; + +--echo # +--echo # MDEV-23562 Assertion `time_type == MYSQL_TIMESTAMP_DATETIME' failed upon SELECT from versioned table +--echo # + +CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING; +SET system_versioning_asof= DATE(NOW()); +SELECT * FROM t1; +DROP TABLE t1; +SET system_versioning_asof= DEFAULT; + +--echo # End of 10.4 tests diff --git a/mysql-test/suite/versioning/t/update.test b/mysql-test/suite/versioning/t/update.test index 7f99e307942..47a56a71bd3 100644 --- a/mysql-test/suite/versioning/t/update.test +++ b/mysql-test/suite/versioning/t/update.test @@ -326,4 +326,28 @@ select *, check_row(row_start, row_end) from t2 for system_time all order by row # cleanup drop tables t1, t2, t3; +--echo # +--echo # MDEV-24522 Assertion `inited==NONE' fails upon UPDATE on versioned table with unique blob +--echo +create table t1 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning; +insert into t1 values (1, 1, 'foo'), (2, 11, 'bar'); + +update t1 set a = 3 where b <= 9; +update t1 set a = 3 where b <= 10; + +# cleanup +drop table t1; + +create table t1 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning; +create table t2 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning; +insert into t1 values (1, 1, 'foo'), (2, 11, 'bar'); +insert into t2 values (1, 1, 'foo'), (2, 11, 'bar'); + +update t1 set a = 3 where b <= 9; +update t2 set a = 3 where b <= 9; +update t1, t2 set t1.a = 3, t2.a = 3 where t1.b <= 10 and t2.b <= 10 and t1.b = t2.b; + +# cleanup +drop tables t1, t2; + source suite/versioning/common_finish.inc; |