diff options
Diffstat (limited to 'mysql-test/suite/versioning/r/alter.result')
| -rw-r--r-- | mysql-test/suite/versioning/r/alter.result | 521 |
1 files changed, 521 insertions, 0 deletions
diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result new file mode 100644 index 00000000000..8693f24591c --- /dev/null +++ b/mysql-test/suite/versioning/r/alter.result @@ -0,0 +1,521 @@ +select @@system_versioning_alter_history; +@@system_versioning_alter_history +ERROR +create table t( +a int +); +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t drop system versioning; +ERROR HY000: Table `t` is not system-versioned +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 y int; +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 `test`.`t`. Change to/from native system versioning engine is prohibited. +alter table t drop system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +set system_versioning_alter_history= keep; +alter table t add system versioning; +alter table t drop system versioning, drop column row_start; +ERROR 42000: Can't DROP COLUMN `row_start`; check that it exists +alter table t drop system versioning; +alter table t +add column trx_start bigint(20) unsigned as row start invisible, +add column trx_end bigint(20) unsigned as row end invisible, +add period for system_time(trx_start, trx_end), +add system versioning; +ERROR HY000: `trx_start` must be of type TIMESTAMP(6) for system-versioned table `t` +alter table t +add column trx_start timestamp as row start invisible, +add column trx_end timestamp as row end invisible, +add period for system_time(trx_start, trx_end), +add system versioning; +ERROR HY000: `trx_start` must be of type TIMESTAMP(6) for system-versioned table `t` +alter table t +add column trx_start timestamp(6) not null as row start invisible, +add column trx_end timestamp(6) not null as row end invisible, +add period for system_time(trx_start, trx_end), +add system versioning; +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 'as row start invisible, +add column trx_end timestamp(6) not null as row end invi' at line 2 +alter table t +add column trx_start timestamp(6) as row start invisible, +add column trx_end timestamp(6) as row end invisible, +add period for system_time(trx_start, trx_end), +add system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `trx_start` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE, + `trx_end` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE, + PERIOD FOR SYSTEM_TIME (`trx_start`, `trx_end`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t drop system versioning; +ERROR HY000: Wrong parameters for `t`: missing 'DROP COLUMN `trx_start`' +alter table t drop column trx_start, drop column trx_end; +select row_start from t; +row_start +alter table t drop system versioning; +show create table t; +Table Create Table +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 +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 b int; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t add column c int; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t add column d int first; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `d` int(11) DEFAULT NULL, + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t add column e int after d; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `d` int(11) DEFAULT NULL, + `e` int(11) DEFAULT NULL, + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t drop column a; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `d` int(11) DEFAULT NULL, + `e` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +create or replace table t ( +a int, +row_start timestamp(6) as row start invisible, +row_end timestamp(6) as row end invisible, +period for system_time(row_start, row_end)) +with system versioning; +select * from t for system_time all; +a +alter table t drop column row_start; +ERROR HY000: Wrong parameters for `t`: missing 'DROP COLUMN `row_end`' +alter table t drop column row_end; +ERROR HY000: Wrong parameters for `t`: missing 'DROP COLUMN `row_start`' +alter table t drop column row_start, drop column row_end; +select * from t for system_time all; +a +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 drop column row_start; +ERROR 42000: Can't DROP COLUMN `row_start`; check that it exists +alter table t drop column row_end; +ERROR 42000: Can't DROP COLUMN `row_end`; check that it exists +create or replace table t ( +a int, +row_start timestamp(6) as row start invisible, +row_end timestamp(6) as row end invisible, +period for system_time(row_start, row_end)) +with system versioning; +select * from t for system_time all; +a +alter table t drop column row_start, drop column row_end; +select * from t for system_time all; +a +create or replace table t( +a int +); +insert into t values(1); +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 +insert into t values(2); +select * from t for system_time all; +a +1 +2 +select * from t; +a +1 +2 +update t set a=3 where a=1; +select * from t; +a +3 +2 +select * from t for system_time all; +a +3 +2 +1 +select row_start from t where a=3 into @tm; +alter table t add column b int; +select @tm=row_start from t where a=3; +@tm=row_start +1 +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +select * from t; +a b +3 NULL +2 NULL +select * from t for system_time all; +a b +3 NULL +2 NULL +1 NULL +alter table t drop system versioning; +select * from t; +a b +3 NULL +2 NULL +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t modify a int with system versioning; +ERROR HY000: Table `t` is not system-versioned +alter table t modify a int without system versioning; +ERROR HY000: Table `t` is not system-versioned +alter table t add system versioning; +alter table t modify a int without system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t modify a int with system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +create or replace table t( +a int +) engine=innodb; +alter table t +add column trx_start timestamp(6) as row start invisible, +add column trx_end timestamp(6) as row end invisible, +add period for system_time(trx_start, trx_end), +add system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `trx_start` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE, + `trx_end` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE, + PERIOD FOR SYSTEM_TIME (`trx_start`, `trx_end`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +# Issue #211: drop of system columns required before drop system versioning +alter table t drop column trx_start, drop column trx_end; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t drop system versioning; +insert into t values(1); +call verify_vtq; +No A B C D +alter table t +add column trx_start bigint(20) unsigned as row start invisible, +add column trx_end bigint(20) unsigned as row end invisible, +add period for system_time(trx_start, trx_end), +add system versioning; +call verify_vtq; +No A B C D +1 1 1 1 1 +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `trx_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START INVISIBLE, + `trx_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END INVISIBLE, + PERIOD FOR SYSTEM_TIME (`trx_start`, `trx_end`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t drop column trx_start, drop column trx_end; +call verify_vtq; +No A B C D +alter table t drop system versioning, algorithm=copy; +call verify_vtq; +No A B C D +alter table t add system versioning, algorithm=copy; +call verify_vtq; +No A B C D +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +update t set a= 2; +select * from t for system_time all; +a +2 +1 +alter table t add column b int, algorithm=copy; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +select * from t; +a b +2 NULL +call verify_vtq; +No A B C D +alter table t drop column b, algorithm=copy; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +select * from t for system_time all; +a +2 +1 +call verify_vtq; +No A B C D +alter table t drop system versioning, algorithm=copy; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +call verify_vtq; +No A B C D +create or replace table t (a int); +insert t values (1),(2),(3),(4); +alter table t add b int auto_increment null unique; +select * from t; +a b +1 1 +2 2 +3 3 +4 4 +drop table t; +create or replace table t (a int) with system versioning engine=innodb; +insert into t values (1), (2), (3); +delete from t where a<3; +alter table t add b int not null unique; +Got one of the listed errors +alter table t add b int auto_increment unique; +Got one of the listed errors +alter table t add b int auto_increment null unique; +select * from t; +a b +3 1 +select * from t for system_time all; +a b +1 NULL +2 NULL +3 1 +insert into t values (4, 0); +select * from t for system_time all; +a b +1 NULL +2 NULL +3 1 +4 4 +create or replace table t (a int) with system versioning; +insert into t values (1), (2), (3); +delete from t where a<3; +alter table t add b int not null unique; +Got one of the listed errors +alter table t add b int auto_increment unique; +Got one of the listed errors +alter table t add b int auto_increment null unique; +select * from t; +a b +3 1 +select * from t for system_time all; +a b +1 NULL +2 NULL +3 1 +insert into t values (4, 0); +select * from t for system_time all; +a b +1 NULL +2 NULL +3 1 +4 2 +create or replace table t (a int, b int primary key, c int unique) with system versioning; +insert t values (1,2,3),(1,3,4),(1,4,5); +alter table t drop system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `b` int(11) NOT NULL, + `c` int(11) DEFAULT NULL, + PRIMARY KEY (`b`), + UNIQUE KEY `c` (`c`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from t; +a b c +1 2 3 +1 3 4 +1 4 5 +create or replace table t ( +a int, +row_start timestamp(6) as row start invisible, +row_end timestamp(6) as row end invisible, +period for system_time(row_start, row_end) +) with system versioning; +alter table t change column row_start asdf timestamp(6); +ERROR HY000: Can not change system versioning field `row_start` +insert into t values (1); +alter table t modify column row_start bigint unsigned; +ERROR HY000: Can not change system versioning field `row_start` +set system_versioning_alter_history= SURVIVE; +ERROR 42000: Variable 'system_versioning_alter_history' can't be set to the value of 'SURVIVE' +set system_versioning_alter_history= 'DROP'; +ERROR 42000: Variable 'system_versioning_alter_history' can't be set to the value of 'DROP' +create or replace table t (a int) with system versioning; +alter table t add system versioning; +ERROR HY000: Table `t` is already system-versioned +alter table t add system versioning, drop system versioning; +ERROR HY000: Table `t` is already system-versioned +set @@system_versioning_alter_history=keep; +create or replace table t(x int, y int) with system versioning engine=innodb; +alter table t modify y int without system versioning; +insert into t values(1, 1); +update t set y=2; +# MDEV-14681 Bogus ER_UNSUPPORTED_EXTENSION +create or replace table t1 (pk int auto_increment unique) with system versioning; +insert into t1 values (1); +delete from t1; +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: System versioning prohibited for TEMPORARY tables +alter table t change column if exists b c bigint unsigned generated always as row end; +ERROR HY000: System versioning prohibited for TEMPORARY tables +alter table t add system versioning; +ERROR HY000: System versioning prohibited for TEMPORARY tables +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; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `c` text DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t add fulltext key (c); +Warnings: +Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +create or replace table t (a int) with system versioning; +alter table t drop column a; +ERROR HY000: Table `t` must have at least one versioned column +alter table t drop column a, drop column a; +ERROR 42000: Can't DROP COLUMN `a`; check that it exists +create or replace table t1 (row_start int); +alter table t1 with system versioning; +ERROR 42S21: Duplicate column name 'row_start' +create or replace table t1 (row_end int); +alter table t1 with system versioning; +ERROR 42S21: Duplicate column name 'row_end' +create or replace table t1 (a int, row_start int) with system versioning; +ERROR 42S21: Duplicate column name 'row_start' +create or replace table t1 (a int) with system versioning; +set statement system_versioning_alter_history=keep for +alter table t1 add column row_start int; +ERROR 42S21: Duplicate column name 'row_start' +set statement system_versioning_alter_history=keep for +alter table t1 add column row_start timestamp(6); +ERROR 42S21: Duplicate column name 'row_start' +# MDEV-14798 Add, drop system versioning semantic and syntax +create or replace table t ( +a int, +row_start timestamp(6) generated always as row start, +row_end timestamp(6) generated always as row end, +period for system_time(row_start, row_end) +) with system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `row_start` timestamp(6) GENERATED ALWAYS AS ROW START, + `row_end` timestamp(6) GENERATED ALWAYS AS ROW END, + PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t +drop column row_start, +drop column row_end, +drop period for system_time, +drop system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t drop period for system_time; +ERROR HY000: Table `t` is not system-versioned +create or replace table t ( +a int, +row_start timestamp(6) generated always as row start, +row_end timestamp(6) generated always as row end, +period for system_time(row_start, row_end) +) with system versioning; +alter table t drop period for system_time; +ERROR HY000: Wrong parameters for `t`: missing 'DROP COLUMN `row_start`, DROP COLUMN `row_end`' +alter table t drop column sys_trx_start, drop period for system_time; +ERROR HY000: Wrong parameters for `t`: missing 'DROP COLUMN `row_start`, DROP COLUMN `row_end`' +alter table t drop column sys_trx_end, drop period for system_time; +ERROR HY000: Wrong parameters for `t`: missing 'DROP COLUMN `row_start`, DROP COLUMN `row_end`' +alter table t add period for system_time(sys_trx_start, sys_trx_end); +ERROR HY000: Table `t` is already system-versioned +drop database test; +create database test; |