diff options
47 files changed, 528 insertions, 541 deletions
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 24d3e897bb0..173c2de3483 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -4897,6 +4897,76 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME SYSTEM_VERSIONING_ALTER_HISTORY +SESSION_VALUE ERROR +GLOBAL_VALUE ERROR +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE ERROR +VARIABLE_SCOPE SESSION +VARIABLE_TYPE ENUM +VARIABLE_COMMENT Versioning ALTER TABLE mode. ERROR: Fail ALTER with error; KEEP: Keep historical system rows and subject them to ALTER; +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST ERROR,KEEP +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME SYSTEM_VERSIONING_ASOF +SESSION_VALUE DEFAULT +GLOBAL_VALUE DEFAULT +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE DEFAULT +VARIABLE_SCOPE SESSION +VARIABLE_TYPE VARCHAR +VARIABLE_COMMENT Default value for the FOR SYSTEM_TIME AS OF clause +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST DEFAULT +READ_ONLY NO +COMMAND_LINE_ARGUMENT NULL +VARIABLE_NAME SYSTEM_VERSIONING_FORCE +SESSION_VALUE OFF +GLOBAL_VALUE OFF +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE OFF +VARIABLE_SCOPE SESSION +VARIABLE_TYPE BOOLEAN +VARIABLE_COMMENT Force system versioning for all created tables +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST OFF,ON +READ_ONLY NO +COMMAND_LINE_ARGUMENT OPTIONAL +VARIABLE_NAME SYSTEM_VERSIONING_HIDE +SESSION_VALUE AUTO +GLOBAL_VALUE AUTO +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE AUTO +VARIABLE_SCOPE SESSION +VARIABLE_TYPE ENUM +VARIABLE_COMMENT Hide system versioning from being displayed in table info. AUTO: hide implicit system fields only in non-versioned and AS OF queries; IMPLICIT: hide implicit system fields in all queries; FULL: hide any system fields in all queries and hide versioning info in SHOW commands; NEVER: don't hide system fields +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST AUTO,IMPLICIT,FULL,NEVER +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME SYSTEM_VERSIONING_INNODB_ALGORITHM_SIMPLE +SESSION_VALUE ON +GLOBAL_VALUE ON +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE ON +VARIABLE_SCOPE SESSION +VARIABLE_TYPE BOOLEAN +VARIABLE_COMMENT Use simple algorithm of timestamp handling in InnoDB instead of TRX_SEES +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST OFF,ON +READ_ONLY NO +COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME TABLE_DEFINITION_CACHE SESSION_VALUE NULL GLOBAL_VALUE 400 @@ -5373,76 +5443,6 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST NEVER,COMPLEMENTARY,PREFERABLY READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED -VARIABLE_NAME VERSIONING_ALTER_HISTORY -SESSION_VALUE ERROR -GLOBAL_VALUE ERROR -GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE ERROR -VARIABLE_SCOPE SESSION -VARIABLE_TYPE ENUM -VARIABLE_COMMENT Versioning ALTER TABLE mode. ERROR: Fail ALTER with error; KEEP: Keep historical system rows and subject them to ALTER; -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST ERROR,KEEP -READ_ONLY NO -COMMAND_LINE_ARGUMENT REQUIRED -VARIABLE_NAME VERSIONING_ASOF_TIMESTAMP -SESSION_VALUE CURRENT -GLOBAL_VALUE CURRENT -GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE CURRENT -VARIABLE_SCOPE SESSION -VARIABLE_TYPE VARCHAR -VARIABLE_COMMENT Default AS OF value for versioned queries -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST CURRENT,ALL -READ_ONLY NO -COMMAND_LINE_ARGUMENT NULL -VARIABLE_NAME VERSIONING_FORCE -SESSION_VALUE OFF -GLOBAL_VALUE OFF -GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE OFF -VARIABLE_SCOPE SESSION -VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Force system versioning for all created tables -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST OFF,ON -READ_ONLY NO -COMMAND_LINE_ARGUMENT OPTIONAL -VARIABLE_NAME VERSIONING_HIDE -SESSION_VALUE AUTO -GLOBAL_VALUE AUTO -GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE AUTO -VARIABLE_SCOPE SESSION -VARIABLE_TYPE ENUM -VARIABLE_COMMENT Hide system versioning from being displayed in table info. AUTO: hide implicit system fields only in non-versioned and AS OF queries; IMPLICIT: hide implicit system fields in all queries; FULL: hide any system fields in all queries and hide versioning info in SHOW commands; NEVER: don't hide system fields -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST AUTO,IMPLICIT,FULL,NEVER -READ_ONLY NO -COMMAND_LINE_ARGUMENT REQUIRED -VARIABLE_NAME VERSIONING_INNODB_ALGORITHM_SIMPLE -SESSION_VALUE ON -GLOBAL_VALUE ON -GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE ON -VARIABLE_SCOPE SESSION -VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Use simple algorithm of timestamp handling in InnoDB instead of TRX_SEES -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST OFF,ON -READ_ONLY NO -COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME WAIT_TIMEOUT SESSION_VALUE 28800 GLOBAL_VALUE 28800 diff --git a/mysql-test/suite/versioning/common.opt b/mysql-test/suite/versioning/common.opt index 9baff80804d..86f2c53c753 100644 --- a/mysql-test/suite/versioning/common.opt +++ b/mysql-test/suite/versioning/common.opt @@ -1,2 +1,2 @@ ---versioning-hide=implicit +--system-versioning-hide=implicit --plugin-load=versioning diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index 7e1d68c0cbd..eba447040d2 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -1,5 +1,5 @@ -select @@versioning_alter_history; -@@versioning_alter_history +select @@system_versioning_alter_history; +@@system_versioning_alter_history ERROR create table t( a int @@ -21,7 +21,7 @@ t CREATE TABLE `t` ( PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING alter table t add column y int; -ERROR HY000: Not allowed for versioned `test`.`t`. Change `versioning_alter_history` to proceed with ALTER. +ERROR HY000: Not allowed for versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER. alter table t engine innodb; ERROR HY000: Not allowed for versioned `test`.`t`. Change to/from native versioning engine is prohibited. alter table t drop system versioning; @@ -30,7 +30,7 @@ Table Create Table t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -set versioning_alter_history= keep; +set system_versioning_alter_history= keep; alter table t add column trx_start bigint(20) unsigned generated always as row start, add column trx_end bigint(20) unsigned generated always as row end, @@ -398,6 +398,16 @@ t CREATE TABLE `t` ( ) 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; @@ -493,10 +503,10 @@ Table Create Table t CREATE TABLE `t` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -set versioning_alter_history= SURVIVE; -ERROR 42000: Variable 'versioning_alter_history' can't be set to the value of 'SURVIVE' -set versioning_alter_history= DROP; -ERROR 42000: Variable 'versioning_alter_history' can't be set to the value of 'DROP' +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 table diff --git a/mysql-test/suite/versioning/r/cte.result b/mysql-test/suite/versioning/r/cte.result index 371c2257a94..c8a3ea14ff8 100644 --- a/mysql-test/suite/versioning/r/cte.result +++ b/mysql-test/suite/versioning/r/cte.result @@ -119,13 +119,13 @@ emp_id name mgr address 2 bill 1 New York 3 kate 1 London 4 john 1 Paris -with ancestors as (select * from emp natural join addr where 1 for system_time all) select * from ancestors; +with ancestors as (select * from (select * from emp natural join addr) for system_time all as t) select * from ancestors; emp_id name mgr address 1 bill 0 Moscow 2 bill 1 New York 3 kate 1 London 4 john 1 Paris -select * from emp natural join addr where 1 for system_time all; +select * from (select * from emp natural join addr) for system_time all as t; emp_id name mgr address 1 bill 0 Moscow 2 bill 1 New York diff --git a/mysql-test/suite/versioning/r/derived.result b/mysql-test/suite/versioning/r/derived.result index 6b71f120cc2..53a68273941 100644 --- a/mysql-test/suite/versioning/r/derived.result +++ b/mysql-test/suite/versioning/r/derived.result @@ -91,13 +91,11 @@ ancestors as ( select e.emp_id, e.name, e.mgr -from emp as e +from emp for system_time as of timestamp @ts as e where name = 'bill' - for system_time as of timestamp @ts -union + union select ee.emp_id, ee.name, ee.mgr -from emp -for system_time as of timestamp @ts as ee, +from emp for system_time as of timestamp @ts as ee, ancestors as a where ee.mgr = a.emp_id ) @@ -112,9 +110,8 @@ ancestors as ( select e.emp_id, e.name, e.mgr - from emp as e + from emp for system_time as of timestamp @ts as e where name = 'bill' - for system_time as of timestamp @ts union select ee.emp_id, ee.name, ee.mgr from emp for system_time as of timestamp @ts as ee, @@ -170,7 +167,7 @@ select * from (select * from vt1, t2) as s0; x y 1 10 ### SYSTEM_TIME clash -select * from (select * from t1 for system_time all) dt0 for system_time all; +select * from (select * from t1 for system_time all) for system_time all as dt0; ERROR HY000: SYSTEM_TIME is not allowed outside historical `dt0` select * from vt1 for system_time all; ERROR HY000: SYSTEM_TIME is not allowed outside historical `vt1` diff --git a/mysql-test/suite/versioning/r/optimized.result b/mysql-test/suite/versioning/r/optimized.result index 4c86b6abbc2..8be67793e12 100644 --- a/mysql-test/suite/versioning/r/optimized.result +++ b/mysql-test/suite/versioning/r/optimized.result @@ -25,7 +25,7 @@ a b 3 NULL Warnings: Warning 4112 Attempt to read unversioned field `b` in historical query -select count(*) from t group by b for system_time as of timestamp now(6); +select count(*) from t for system_time as of timestamp now(6) group by b; count(*) 2 Warnings: @@ -44,41 +44,10 @@ a b Warnings: Warning 4112 Attempt to read unversioned field `b` in historical query Warning 4112 Attempt to read unversioned field `b` in historical query -select * from t group by a having a=2 for system_time as of timestamp now(6); -a b -Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query -select * from t group by b having b=2 for system_time as of timestamp now(6); +select * from t for system_time as of timestamp now(6) group by a having a=2; a b Warnings: Warning 4112 Attempt to read unversioned field `b` in historical query -select a from t where b=2 for system_time as of timestamp now(6); -a -Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query -select a from t where b=NULL for system_time as of timestamp now(6); -a -Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query -select a from t where b is NULL for system_time as of timestamp now(6); -a -1 -3 -Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query -select count(*), b from t group by b having b=NULL for system_time as of timestamp now(6); -count(*) b -Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query -select a, b from t; -a b -1 2 -3 4 -select count(*) from t for system_time as of timestamp now(6) group by b; -count(*) -2 -Warnings: -Warning 4112 Attempt to read unversioned field `b` in historical query select * from t for system_time as of timestamp now(6) group by b having b=2; a b Warnings: @@ -101,6 +70,10 @@ select count(*), b from t for system_time as of timestamp now(6) group by b havi count(*) b Warnings: Warning 4112 Attempt to read unversioned field `b` in historical query +select a, b from t; +a b +1 2 +3 4 create or replace table t ( a int, b int not null without system versioning diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index 9c9e08a17c1..b1092a5ee9b 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -22,10 +22,10 @@ select * from t1 for system_time all; x 3 300 -select * from t1 partition (p0) for system_time all; +select * from t1 partition (p0); x 3 -select * from t1 partition (p1) for system_time all; +select * from t1 partition (p1); x 300 ### Engine change versioned/non-versioned prohibited @@ -35,50 +35,50 @@ ERROR HY000: Not allowed for versioned `test`.`t1`. Change to/from native versio ### check server-level partitioning create or replace table t1 (x int) partition by system_time ( -partition p0 versioning, -partition pn as of current_timestamp); +partition p0 history, +partition pn current); ERROR HY000: Engine does not support System Versioning for `t1` create or replace table t1 (x int); alter table t1 partition by system_time ( -partition p0 versioning, -partition pn as of current_timestamp); +partition p0 history, +partition pn current); Got one of the listed errors create or replace table t1 (x int) with system versioning partition by system_time ( -partition p0 as of current_timestamp); -ERROR HY000: Wrong partitions consistency for `t1`: must have at least one 'VERSIONING' and exactly one last 'AS OF NOW' +partition p0 current); +ERROR HY000: Wrong partitions consistency for `t1`: must have at least one HISTORY and exactly one last CURRENT create or replace table t1 (x int) with system versioning partition by system_time ( -partition p0 as of current_timestamp, -partition p1 as of current_timestamp); -ERROR HY000: Wrong partitions consistency for `t1`: must have at least one 'VERSIONING' and exactly one last 'AS OF NOW' +partition p0 current, +partition p1 current); +ERROR HY000: Wrong partitions consistency for `t1`: must have at least one HISTORY and exactly one last CURRENT create or replace table t1 (x int) with system versioning partition by system_time ( -partition p0 versioning, -partition p1 versioning); -ERROR HY000: Wrong partitions consistency for `t1`: must have at least one 'VERSIONING' and exactly one last 'AS OF NOW' +partition p0 history, +partition p1 history); +ERROR HY000: Wrong partitions consistency for `t1`: must have at least one HISTORY and exactly one last CURRENT create or replace table t1 (x int) with system versioning partition by system_time ( -partition pn as of current_timestamp, -partition p0 versioning); -ERROR HY000: Wrong partitions consistency for `t1`: must have at least one 'VERSIONING' and exactly one last 'AS OF NOW' +partition pn current, +partition p0 history); +ERROR HY000: Wrong partitions consistency for `t1`: must have at least one HISTORY and exactly one last CURRENT create or replace table t1 (x int) with system versioning partition by system_time ( -partition p0 versioning, -partition pn as of current_timestamp); +partition p0 history, +partition pn current); alter table t1 add partition ( -partition p1 as of current_timestamp); -ERROR HY000: Wrong partitions consistency for `t1`: must have at least one 'VERSIONING' and exactly one last 'AS OF NOW' +partition p1 current); +ERROR HY000: Wrong partitions consistency for `t1`: must have at least one HISTORY and exactly one last CURRENT alter table t1 add partition ( -partition p1 versioning); +partition p1 history); Warnings: -Warning 4115 Maybe missing parameters: no rotation condition for multiple `VERSIONING` partitions. +Warning 4115 Maybe missing parameters: no rotation condition for multiple HISTORY partitions. show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -88,15 +88,15 @@ t1 CREATE TABLE `t1` ( PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=${INNODB_OR_MYISAM} DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME -(PARTITION `p0` VERSIONING ENGINE = ${INNODB_OR_MYISAM}, - PARTITION `p1` VERSIONING ENGINE = ${INNODB_OR_MYISAM}, - PARTITION `pn` AS OF CURRENT_TIMESTAMP ENGINE = ${INNODB_OR_MYISAM}) +(PARTITION `p0` HISTORY ENGINE = ${INNODB_OR_MYISAM}, + PARTITION `p1` HISTORY ENGINE = ${INNODB_OR_MYISAM}, + PARTITION `pn` CURRENT ENGINE = ${INNODB_OR_MYISAM}) insert into t1 values (1), (2); alter table t1 drop partition pn; -ERROR HY000: Wrong partitions consistency for `t1`: must have at least one 'VERSIONING' and exactly one last 'AS OF NOW' +ERROR HY000: Wrong partitions consistency for `t1`: must have at least one HISTORY and exactly one last CURRENT alter table t1 drop partition p1; alter table t1 drop partition p0; -ERROR HY000: Wrong partitions consistency for `t1`: must have at least one 'VERSIONING' and exactly one last 'AS OF NOW' +ERROR HY000: Wrong partitions consistency for `t1`: must have at least one HISTORY and exactly one last CURRENT select x from t1; x 1 @@ -104,15 +104,15 @@ x create or replace table t1 (x int) with system versioning partition by system_time ( -partition p0 versioning, -partition pn as of current_timestamp); +partition p0 history, +partition pn current); set @now= now(6); insert into t1 values (1); set @ts_start= sys_commit_ts('sys_trx_start'); set @ts_end= sys_commit_ts('sys_trx_end'); -set @str= concat('select x, ', @ts_start, ' < @now as A, ', @ts_end, ' > @now as B from t1 partition (p0) for system_time all'); +set @str= concat('select x, ', @ts_start, ' < @now as A, ', @ts_end, ' > @now as B from t1 partition (p0)'); prepare select_p0 from @str; -set @str= concat('select x, ', @ts_start, ' > @now as C, ', @ts_end, ' = timestamp\'2038-01-19 03:14:07.999999\' as D from t1 partition (pn) for system_time all'); +set @str= concat('select x, ', @ts_start, ' > @now as C, ', @ts_end, ' = timestamp\'2038-01-19 03:14:07.999999\' as D from t1 partition (pn)'); prepare select_pn from @str; execute select_p0; x A B @@ -133,7 +133,7 @@ x A B 1 1 1 execute select_pn; x C D -set @str= concat('select ', @ts_start, ' from t1 partition (p0) for system_time all into @ts1'); +set @str= concat('select ', @ts_start, ' from t1 partition (p0) into @ts1'); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -163,11 +163,11 @@ x C D 3 1 1 drop prepare select_p0; drop prepare select_pn; -set @str= concat('select ', @ts_start, ' from t1 partition (p0) for system_time all where x = 2 into @ts1'); +set @str= concat('select ', @ts_start, ' from t1 partition (p0) where x = 2 into @ts1'); prepare stmt from @str; execute stmt; drop prepare stmt; -set @str= concat('select ', @ts_end, ' from t1 partition (p0) for system_time all where x = 2 into @ts2'); +set @str= concat('select ', @ts_end, ' from t1 partition (p0) where x = 2 into @ts2'); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -184,16 +184,16 @@ select @ts2 = @ts3; create or replace table t1 (x int) with system versioning partition by system_time limit 0 ( -partition p0 versioning, -partition p1 versioning, -partition pn as of current_timestamp); +partition p0 history, +partition p1 history, +partition pn current); ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'LIMIT' create or replace table t1 (x int) with system versioning partition by system_time limit 1 ( -partition p0 versioning, -partition p1 versioning, -partition pn as of current_timestamp); +partition p0 history, +partition p1 history, +partition pn current); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -203,9 +203,9 @@ t1 CREATE TABLE `t1` ( PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) ) ENGINE=${INNODB_OR_MYISAM} DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME LIMIT 1 -(PARTITION `p0` VERSIONING ENGINE = ${INNODB_OR_MYISAM}, - PARTITION `p1` VERSIONING ENGINE = ${INNODB_OR_MYISAM}, - PARTITION `pn` AS OF CURRENT_TIMESTAMP ENGINE = ${INNODB_OR_MYISAM}) +(PARTITION `p0` HISTORY ENGINE = ${INNODB_OR_MYISAM}, + PARTITION `p1` HISTORY ENGINE = ${INNODB_OR_MYISAM}, + PARTITION `pn` CURRENT ENGINE = ${INNODB_OR_MYISAM}) alter table t1 drop partition non_existent; ERROR HY000: Error in list of partitions to DROP insert into t1 values (1), (2); @@ -215,34 +215,34 @@ x 2 delete from t1; Warnings: -Note 4116 Switching from partition `p0` to `p1` -select * from t1 partition (p0) for system_time all; +Note 4116 Versioned table `test`.`t1`: switching from partition `p0` to `p1` +select * from t1 partition (p0); x 1 -select * from t1 partition (p1) for system_time all; +select * from t1 partition (p1); x 2 insert into t1 values (3); delete from t1; Warnings: -Warning 4114 Using full partition `p1`, need more VERSIONING partitions! -select * from t1 partition (p1) for system_time all; +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions +select * from t1 partition (p1); x 2 3 create or replace table t1 (x int) with system versioning partition by system_time interval 0 second ( -partition p0 versioning, -partition p1 versioning, -partition pn as of current_timestamp); +partition p0 history, +partition p1 history, +partition pn current); ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL' create or replace table t1 (x int) with system versioning partition by system_time interval 1 second ( -partition p0 versioning, -partition p1 versioning, -partition pn as of current_timestamp); +partition p0 history, +partition p1 history, +partition pn current); insert into t1 values (1), (2), (3); select * from t1 partition (pn); x @@ -250,7 +250,7 @@ x 2 3 delete from t1; -select * from t1 partition (p0) for system_time all; +select * from t1 partition (p0); x 1 2 @@ -258,8 +258,8 @@ x insert into t1 values (4); delete from t1; Warnings: -Note 4116 Switching from partition `p0` to `p1` -select * from t1 partition (p1) for system_time all; +Note 4116 Versioned table `test`.`t1`: switching from partition `p0` to `p1` +select * from t1 partition (p1); x 4 create or replace table t1 (x int) @@ -267,9 +267,9 @@ with system versioning partition by system_time limit 1 subpartition by key (x) subpartitions 2 ( -partition p0 versioning, -partition p1 versioning, -partition pn as of current_timestamp); +partition p0 history, +partition p1 history, +partition pn current); insert into t1 (x) values (1), (2), (3); select * from t1 partition (pnsp0); x @@ -280,17 +280,17 @@ x 2 delete from t1; Warnings: -Note 4116 Switching from partition `p0` to `p1` -Warning 4114 Using full partition `p1`, need more VERSIONING partitions! -select * from t1 partition (p0sp0) for system_time all; +Note 4116 Versioned table `test`.`t1`: switching from partition `p0` to `p1` +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions +select * from t1 partition (p0sp0); x 1 -select * from t1 partition (p0sp1) for system_time all; +select * from t1 partition (p0sp1); x -select * from t1 partition (p1sp0) for system_time all; +select * from t1 partition (p1sp0); x 3 -select * from t1 partition (p1sp1) for system_time all; +select * from t1 partition (p1sp1); x 2 create or replace table t1 (a bigint) diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result index ea7757b8160..16f3d82d243 100644 --- a/mysql-test/suite/versioning/r/select.result +++ b/mysql-test/suite/versioning/r/select.result @@ -144,22 +144,43 @@ NULL NULL 2 1 NULL NULL 3 1 delete from t1; delete from t2; -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; +explain extended 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; +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 `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`.`t1`.`sys_trx_end` > <cache>(cast(@`t0` as datetime(6))) and `test`.`t1`.`sys_trx_start` <= <cache>(cast(@`t0` as datetime(6))) and `test`.`t2`.`sys_trx_end` > <cache>(cast(@`t0` as datetime(6))) and `test`.`t2`.`sys_trx_start` <= <cache>(cast(@`t0` as datetime(6))) +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 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +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`.`t1`.`sys_trx_end` > <cache>(cast(@`t0` as datetime(6))) and `test`.`t1`.`sys_trx_start` <= <cache>(cast(@`t0` as datetime(6))) and `test`.`t2`.`sys_trx_end` > <cache>(cast(@`t0` as datetime(6))) and `test`.`t2`.`sys_trx_start` <= <cache>(cast(@`t0` as datetime(6)))) where 1 +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 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +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`.`sys_trx_end` > <cache>(cast(@`t0` as datetime(6))) and `test`.`t1`.`sys_trx_start` <= <cache>(cast(@`t0` as datetime(6))) and `test`.`t2`.`sys_trx_end` > <cache>(cast(@`t0` as datetime(6))) and `test`.`t2`.`sys_trx_start` <= <cache>(cast(@`t0` as datetime(6)))) where 1 +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 1 1 1 2 1 2 1 2 1 3 1 2 -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; +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; LJ2_x1 y1 x2 y2 1 1 1 2 1 2 1 2 1 3 1 2 4 4 NULL NULL 5 5 NULL NULL -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; +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; RJ2_x1 y1 x2 y2 1 1 1 2 1 2 1 2 @@ -280,8 +301,9 @@ select * from t1, t2 for system_time all; x y 1 1 2 1 -select * from t1 for system_time all, t2 for system_time all for system_time all; -ERROR HY000: Unused clause: 'SYSTEM_TIME' +select * from (select * from t1 for system_time all, t2 for system_time all) +for system_time all as t; +ERROR HY000: SYSTEM_TIME is not allowed outside historical `t` # TRANSACTION/TIMESTAMP specifier in SYSTEM_TIME [MDEV-14645, Issue #396] create or replace table t1 (x int) with system versioning engine myisam; select * from t1 for system_time as of transaction 1; diff --git a/mysql-test/suite/versioning/r/select_sp.result b/mysql-test/suite/versioning/r/select_sp.result index 4ac9009df7a..a7b9aa9735c 100644 --- a/mysql-test/suite/versioning/r/select_sp.result +++ b/mysql-test/suite/versioning/r/select_sp.result @@ -77,12 +77,12 @@ select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 o select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x; delete from t1; delete from t2; -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; -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; -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; +select IJ2_x1,y1,x2,y2 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; +select LJ2_x1,y1,x2,y2 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; +select RJ2_x1,y1,x2,y2 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; drop table t1; drop table t2; end~~ @@ -321,8 +321,10 @@ select * from t1, t2 for system_time all; x y 1 1 2 1 -select * from t1 for system_time all, t2 for system_time all for system_time all; -ERROR HY000: Unused clause: 'SYSTEM_TIME' +select * from (select * from t1 for system_time all, t2 for system_time all) for system_time all as t; +ERROR HY000: SYSTEM_TIME is not allowed outside historical `t` +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 drop view v1; drop table t1, t2; call innodb_verify_vtq(27); diff --git a/mysql-test/suite/versioning/r/simple.result b/mysql-test/suite/versioning/r/simple.result index 8f5fd57ea4b..8de6d24802c 100644 --- a/mysql-test/suite/versioning/r/simple.result +++ b/mysql-test/suite/versioning/r/simple.result @@ -42,31 +42,32 @@ where d.dept_id = 10 and d.dept_id = e.dept_id; emp_id dept_id name salary dept_id name 1 10 bill 2000 10 accounting -select * from emp e, dept d +select * from +emp for system_time from timestamp @ts_1 to timestamp @ts_2 e, +dept for system_time from timestamp @ts_1 to timestamp @ts_2 d where d.dept_id = 10 -and d.dept_id = e.dept_id -for system_time from timestamp @ts_1 to timestamp @ts_2; +and d.dept_id = e.dept_id; emp_id dept_id name salary sys_trx_start sys_trx_end dept_id name sys_trx_start sys_trx_end +set statement system_versioning_asof=@ts_0 for select * from emp e, dept d where d.dept_id = 10 -and d.dept_id = e.dept_id -for system_time as of timestamp @ts_0; +and d.dept_id = e.dept_id; emp_id dept_id name salary dept_id name +set statement system_versioning_asof=@ts_1 for select * from emp e, dept d where d.dept_id = 10 -and d.dept_id = e.dept_id -for system_time as of timestamp @ts_1; +and d.dept_id = e.dept_id; emp_id dept_id name salary dept_id name +set statement system_versioning_asof=@ts_2 for select * from emp e, dept d where d.dept_id = 10 -and d.dept_id = e.dept_id -for system_time as of timestamp @ts_2; +and d.dept_id = e.dept_id; emp_id dept_id name salary dept_id name 1 10 bill 1000 10 accounting +set statement system_versioning_asof=@ts_3 for select * from emp e, dept d where d.dept_id = 10 -and d.dept_id = e.dept_id -for system_time as of timestamp @ts_3; +and d.dept_id = e.dept_id; emp_id dept_id name salary dept_id name 1 10 bill 2000 10 accounting drop table emp, dept; diff --git a/mysql-test/suite/versioning/r/sysvars.result b/mysql-test/suite/versioning/r/sysvars.result index 8cb84755438..c9f2ff05111 100644 --- a/mysql-test/suite/versioning/r/sysvars.result +++ b/mysql-test/suite/versioning/r/sysvars.result @@ -1,116 +1,98 @@ create table t (a int) with system versioning; insert into t values (1); update t set a= 2; -show global variables like 'versioning_asof_timestamp'; +show global variables like 'system_versioning_asof'; Variable_name Value -versioning_asof_timestamp CURRENT -show variables like 'versioning_asof_timestamp'; +system_versioning_asof DEFAULT +show variables like 'system_versioning_asof'; Variable_name Value -versioning_asof_timestamp CURRENT +system_versioning_asof DEFAULT select * from t; a 2 -set versioning_asof_timestamp= '2031-1-1 0:0:0'; -show variables like 'versioning_asof_timestamp'; +set system_versioning_asof= '2031-1-1 0:0:0'; +show variables like 'system_versioning_asof'; Variable_name Value -versioning_asof_timestamp 2031-01-01 00:00:00.000000 +system_versioning_asof 2031-01-01 00:00:00.000000 select * from t; a 2 -set versioning_asof_timestamp= '2011-1-1 0:0:0'; -show variables like 'versioning_asof_timestamp'; +set system_versioning_asof= '2011-1-1 0:0:0'; +show variables like 'system_versioning_asof'; Variable_name Value -versioning_asof_timestamp 2011-01-01 00:00:00.000000 +system_versioning_asof 2011-01-01 00:00:00.000000 select * from t; a -set versioning_asof_timestamp= ALL; -show variables like 'versioning_asof_timestamp'; -Variable_name Value -versioning_asof_timestamp ALL -select * from t; -a -2 -1 -create view vt as select * from t; -select * from t; -a -2 -1 -drop view vt; -select * from (select * from t) as tt; -a -2 -1 -set global versioning_asof_timestamp= 'alley'; -ERROR 42000: Variable 'versioning_asof_timestamp' can't be set to the value of 'alley' -set global versioning_asof_timestamp= null; -ERROR 42000: Variable 'versioning_asof_timestamp' can't be set to the value of 'NULL' -set global versioning_asof_timestamp= 1; -ERROR 42000: Incorrect argument type to variable 'versioning_asof_timestamp' -set global versioning_asof_timestamp= 1.1; -ERROR 42000: Incorrect argument type to variable 'versioning_asof_timestamp' -set versioning_asof_timestamp= 'alley'; -ERROR 42000: Variable 'versioning_asof_timestamp' can't be set to the value of 'alley' -set versioning_asof_timestamp= null; -ERROR 42000: Variable 'versioning_asof_timestamp' can't be set to the value of 'NULL' -set versioning_asof_timestamp= 1; -ERROR 42000: Incorrect argument type to variable 'versioning_asof_timestamp' -set versioning_asof_timestamp= 1.1; -ERROR 42000: Incorrect argument type to variable 'versioning_asof_timestamp' -set global versioning_asof_timestamp= '1911-11-11 11:11:11.1111119'; +set global system_versioning_asof= 'alley'; +ERROR 42000: Variable 'system_versioning_asof' can't be set to the value of 'alley' +set global system_versioning_asof= null; +ERROR 42000: Variable 'system_versioning_asof' can't be set to the value of 'NULL' +set global system_versioning_asof= 1; +ERROR 42000: Incorrect argument type to variable 'system_versioning_asof' +set global system_versioning_asof= 1.1; +ERROR 42000: Incorrect argument type to variable 'system_versioning_asof' +set system_versioning_asof= 'alley'; +ERROR 42000: Variable 'system_versioning_asof' can't be set to the value of 'alley' +set system_versioning_asof= null; +ERROR 42000: Variable 'system_versioning_asof' can't be set to the value of 'NULL' +set system_versioning_asof= 1; +ERROR 42000: Incorrect argument type to variable 'system_versioning_asof' +set system_versioning_asof= 1.1; +ERROR 42000: Incorrect argument type to variable 'system_versioning_asof' +set global system_versioning_asof= '1911-11-11 11:11:11.1111119'; Warnings: Note 1292 Truncated incorrect datetime value: '1911-11-11 11:11:11.1111119' Note 1292 Truncated incorrect datetime value: '1911-11-11 11:11:11.1111119' -show global variables like 'versioning_asof_timestamp'; +show global variables like 'system_versioning_asof'; Variable_name Value -versioning_asof_timestamp 1911-11-11 11:11:11.111111 -set global versioning_asof_timestamp= '1900-01-01 00:00:00'; -show global variables like 'versioning_asof_timestamp'; +system_versioning_asof 1911-11-11 11:11:11.111111 +set global system_versioning_asof= '1900-01-01 00:00:00'; +show global variables like 'system_versioning_asof'; Variable_name Value -versioning_asof_timestamp 1900-01-01 00:00:00.000000 -set global versioning_asof_timestamp= timestamp'1911-11-11 11:11:11.1111119'; +system_versioning_asof 1900-01-01 00:00:00.000000 +set global system_versioning_asof= timestamp'1911-11-11 11:11:11.1111119'; Warnings: Note 1292 Truncated incorrect datetime value: '1911-11-11 11:11:11.1111119' -show global variables like 'versioning_asof_timestamp'; +show global variables like 'system_versioning_asof'; Variable_name Value -versioning_asof_timestamp 1911-11-11 11:11:11.111111 +system_versioning_asof 1911-11-11 11:11:11.111111 set @ts= timestamp'1900-01-01 00:00:00'; -set global versioning_asof_timestamp= @ts; -show global variables like 'versioning_asof_timestamp'; +set global system_versioning_asof= @ts; +show global variables like 'system_versioning_asof'; Variable_name Value -versioning_asof_timestamp 1900-01-01 00:00:00.000000 -set versioning_asof_timestamp= '1911-11-11 11:11:11.1111119'; +system_versioning_asof 1900-01-01 00:00:00.000000 +set system_versioning_asof= '1911-11-11 11:11:11.1111119'; Warnings: Note 1292 Truncated incorrect datetime value: '1911-11-11 11:11:11.1111119' Note 1292 Truncated incorrect datetime value: '1911-11-11 11:11:11.1111119' -show variables like 'versioning_asof_timestamp'; +show variables like 'system_versioning_asof'; Variable_name Value -versioning_asof_timestamp 1911-11-11 11:11:11.111111 -set versioning_asof_timestamp= '1900-01-01 00:00:00'; -show variables like 'versioning_asof_timestamp'; +system_versioning_asof 1911-11-11 11:11:11.111111 +set system_versioning_asof= '1900-01-01 00:00:00'; +show variables like 'system_versioning_asof'; Variable_name Value -versioning_asof_timestamp 1900-01-01 00:00:00.000000 -set versioning_asof_timestamp= timestamp'1911-11-11 11:11:11.1111119'; +system_versioning_asof 1900-01-01 00:00:00.000000 +set system_versioning_asof= timestamp'1911-11-11 11:11:11.1111119'; Warnings: Note 1292 Truncated incorrect datetime value: '1911-11-11 11:11:11.1111119' -show variables like 'versioning_asof_timestamp'; +show variables like 'system_versioning_asof'; Variable_name Value -versioning_asof_timestamp 1911-11-11 11:11:11.111111 +system_versioning_asof 1911-11-11 11:11:11.111111 set @ts= timestamp'1900-01-01 00:00:00'; -set versioning_asof_timestamp= @ts; -show variables like 'versioning_asof_timestamp'; +set system_versioning_asof= @ts; +show variables like 'system_versioning_asof'; Variable_name Value -versioning_asof_timestamp 1900-01-01 00:00:00.000000 -set global versioning_asof_timestamp= CURRENT; -set versioning_asof_timestamp= CURRENT; -show variables where variable_name = "versioning_hide"; +system_versioning_asof 1900-01-01 00:00:00.000000 +set global system_versioning_asof= DEFAULT; +set system_versioning_asof= DEFAULT; +show variables where variable_name = "system_versioning_hide"; Variable_name Value -versioning_hide IMPLICIT +system_versioning_hide IMPLICIT select * from t for system_time all; a 2 1 -set versioning_hide= AUTO; +set system_versioning_hide= AUTO; select * from t; a 2 @@ -129,11 +111,11 @@ select * from t for system_time between '0-0-0' and current_timestamp(6); a sys_trx_start sys_trx_end 2 TIMESTAMP TIMESTAMP 1 TIMESTAMP TIMESTAMP -set versioning_hide= NEVER; +set system_versioning_hide= NEVER; select * from t; a sys_trx_start sys_trx_end 2 TIMESTAMP TIMESTAMP -set versioning_hide= FULL; +set system_versioning_hide= FULL; create or replace table t ( x int, st timestamp(6) generated always as row start, @@ -153,4 +135,4 @@ select * from t for system_time all; x 2 drop table t; -set versioning_hide= IMPLICIT; +set system_versioning_hide= IMPLICIT; diff --git a/mysql-test/suite/versioning/r/truncate.result b/mysql-test/suite/versioning/r/truncate.result index 0390ba8b796..23577a83b91 100644 --- a/mysql-test/suite/versioning/r/truncate.result +++ b/mysql-test/suite/versioning/r/truncate.result @@ -39,8 +39,8 @@ create or replace table t (a int) with system versioning engine myisam partition by system_time ( -partition p0 versioning, -partition pn as of current_timestamp); +partition p0 history, +partition pn current); truncate table t to system_time current_timestamp; ERROR 42000: The used command is not allowed with this MariaDB version drop table t; diff --git a/mysql-test/suite/versioning/r/truncate_privilege.result b/mysql-test/suite/versioning/r/truncate_privilege.result index 69a43ba8830..9590d93809a 100644 --- a/mysql-test/suite/versioning/r/truncate_privilege.result +++ b/mysql-test/suite/versioning/r/truncate_privilege.result @@ -13,8 +13,8 @@ GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' truncate mysqltest.t to system_time now(); ERROR 42000: DELETE VERSIONING ROWS command denied to user 'mysqltest_1'@'localhost' for table 't' connection root; -grant delete versioning rows on mysqltest.* to mysqltest_1@localhost; -grant delete versioning rows on mysqltest.t to mysqltest_1@localhost; +grant delete history on mysqltest.* to mysqltest_1@localhost; +grant delete history on mysqltest.t to mysqltest_1@localhost; connection user1; show grants; Grants for mysqltest_1@localhost diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test index 70d54d07a8d..a75fc926a1d 100644 --- a/mysql-test/suite/versioning/t/alter.test +++ b/mysql-test/suite/versioning/t/alter.test @@ -1,4 +1,4 @@ -select @@versioning_alter_history; +select @@system_versioning_alter_history; create table t( a int @@ -18,7 +18,7 @@ alter table t engine innodb; alter table t drop system versioning; show create table t; -set versioning_alter_history= keep; +set system_versioning_alter_history= keep; --error ER_VERS_FIELD_WRONG_TYPE alter table t @@ -205,6 +205,13 @@ alter table t drop system versioning, algorithm=copy; show create table t; call verify_vtq; +# nullable autoinc test w/o versioning +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; +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; @@ -280,10 +287,10 @@ show create table t; ## These experimental options are now disabled --error ER_WRONG_VALUE_FOR_VAR -set versioning_alter_history= SURVIVE; +set system_versioning_alter_history= SURVIVE; --error ER_WRONG_VALUE_FOR_VAR -set versioning_alter_history= DROP; +set system_versioning_alter_history= DROP; if (0) { diff --git a/mysql-test/suite/versioning/t/cte.opt b/mysql-test/suite/versioning/t/cte.opt index e5abd94afeb..2757b0f472a 100644 --- a/mysql-test/suite/versioning/t/cte.opt +++ b/mysql-test/suite/versioning/t/cte.opt @@ -1,2 +1,2 @@ --plugin-load=versioning ---versioning-hide=implicit +--system-versioning-hide=implicit diff --git a/mysql-test/suite/versioning/t/cte.test b/mysql-test/suite/versioning/t/cte.test index 482c793a724..992ecbc6e05 100644 --- a/mysql-test/suite/versioning/t/cte.test +++ b/mysql-test/suite/versioning/t/cte.test @@ -109,8 +109,8 @@ insert emp values (4, 'john', 1); insert addr values (4, 'Paris'); with ancestors as (select * from emp natural join addr) select * from ancestors; with ancestors as (select * from emp natural join addr) select * from ancestors for system_time all; -with ancestors as (select * from emp natural join addr where 1 for system_time all) select * from ancestors; -select * from emp natural join addr where 1 for system_time all; +with ancestors as (select * from (select * from emp natural join addr) for system_time all as t) select * from ancestors; +select * from (select * from emp natural join addr) for system_time all as t; drop table emp; drop table dept; diff --git a/mysql-test/suite/versioning/t/derived.test b/mysql-test/suite/versioning/t/derived.test index 20bec5a7bdf..22c27ecbcb6 100644 --- a/mysql-test/suite/versioning/t/derived.test +++ b/mysql-test/suite/versioning/t/derived.test @@ -57,18 +57,17 @@ as select * from ancestors"; prepare stmt from @tmp; execute stmt; drop prepare stmt; +#385 with recursive ancestors as ( select e.emp_id, e.name, e.mgr - from emp as e + from emp for system_time as of timestamp @ts as e where name = 'bill' - for system_time as of timestamp @ts union select ee.emp_id, ee.name, ee.mgr - from emp - for system_time as of timestamp @ts as ee, + from emp for system_time as of timestamp @ts as ee, ancestors as a where ee.mgr = a.emp_id ) @@ -79,9 +78,8 @@ ancestors as ( select e.emp_id, e.name, e.mgr - from emp as e + from emp for system_time as of timestamp @ts as e where name = 'bill' - for system_time as of timestamp @ts union select ee.emp_id, ee.name, ee.mgr from emp for system_time as of timestamp @ts as ee, @@ -125,7 +123,7 @@ select * from (select * from vt1, t2) as s0; --echo ### SYSTEM_TIME clash --error ER_VERS_SYSTEM_TIME_CLASH -select * from (select * from t1 for system_time all) dt0 for system_time all; +select * from (select * from t1 for system_time all) for system_time all as dt0; --error ER_VERS_SYSTEM_TIME_CLASH select * from vt1 for system_time all; --error ER_VERS_SYSTEM_TIME_CLASH diff --git a/mysql-test/suite/versioning/t/foreign.opt b/mysql-test/suite/versioning/t/foreign.opt index c1a585b67eb..51c33158c7a 100644 --- a/mysql-test/suite/versioning/t/foreign.opt +++ b/mysql-test/suite/versioning/t/foreign.opt @@ -1 +1 @@ ---versioning-hide=implicit +--system-versioning-hide=implicit diff --git a/mysql-test/suite/versioning/t/optimized.test b/mysql-test/suite/versioning/t/optimized.test index 311b0aeeb31..93dd6ed6fc6 100644 --- a/mysql-test/suite/versioning/t/optimized.test +++ b/mysql-test/suite/versioning/t/optimized.test @@ -9,23 +9,16 @@ select * from t; select a from t for system_time as of timestamp now(6); select a, b, b+0 from t for system_time as of timestamp now(6); select * from t for system_time as of timestamp now(6); -select count(*) from t group by b for system_time as of timestamp now(6); +select count(*) from t for system_time as of timestamp now(6) group by b; select * from t for system_time as of timestamp now(6) order by b asc; select * from t for system_time as of timestamp now(6) order by b desc; -select * from t group by a having a=2 for system_time as of timestamp now(6); -select * from t group by b having b=2 for system_time as of timestamp now(6); -select a from t where b=2 for system_time as of timestamp now(6); -select a from t where b=NULL for system_time as of timestamp now(6); -select a from t where b is NULL for system_time as of timestamp now(6); -select count(*), b from t group by b having b=NULL for system_time as of timestamp now(6); -select a, b from t; - -select count(*) from t for system_time as of timestamp now(6) group by b; +select * from t for system_time as of timestamp now(6) group by a having a=2; select * from t for system_time as of timestamp now(6) group by b having b=2; select a from t for system_time as of timestamp now(6) where b=2; select a from t for system_time as of timestamp now(6) where b=NULL; select a from t for system_time as of timestamp now(6) where b is NULL; select count(*), b from t for system_time as of timestamp now(6) group by b having b=NULL; +select a, b from t; create or replace table t ( a int, diff --git a/mysql-test/suite/versioning/t/partition.opt b/mysql-test/suite/versioning/t/partition.opt index 8608eed372b..51c33158c7a 100644 --- a/mysql-test/suite/versioning/t/partition.opt +++ b/mysql-test/suite/versioning/t/partition.opt @@ -1,2 +1 @@ ---partition ---versioning-hide=implicit +--system-versioning-hide=implicit diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index 55f79a8bb77..8e319243c9a 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -1,3 +1,4 @@ +-- source include/have_partition.inc -- source suite/versioning/common.inc --echo ### check System Versioning and conventional partitioning @@ -16,8 +17,8 @@ select * from t1 partition (p1); delete from t1; select * from t1; select * from t1 for system_time all; -select * from t1 partition (p0) for system_time all; -select * from t1 partition (p1) for system_time all; +select * from t1 partition (p0); +select * from t1 partition (p1); --echo ### Engine change versioned/non-versioned prohibited eval create or replace table t1 (i int) engine=$default_engine with system versioning partition by hash(i); @@ -30,56 +31,56 @@ eval alter table t1 engine=$non_default_engine; --error ER_VERS_ENGINE_UNSUPPORTED create or replace table t1 (x int) partition by system_time ( - partition p0 versioning, - partition pn as of current_timestamp); + partition p0 history, + partition pn current); create or replace table t1 (x int); --error ER_VERS_ENGINE_UNSUPPORTED,ER_VERS_ENGINE_UNSUPPORTED alter table t1 partition by system_time ( - partition p0 versioning, - partition pn as of current_timestamp); + partition p0 history, + partition pn current); --error ER_VERS_WRONG_PARTS create or replace table t1 (x int) with system versioning partition by system_time ( - partition p0 as of current_timestamp); + partition p0 current); --error ER_VERS_WRONG_PARTS create or replace table t1 (x int) with system versioning partition by system_time ( - partition p0 as of current_timestamp, - partition p1 as of current_timestamp); + partition p0 current, + partition p1 current); --error ER_VERS_WRONG_PARTS create or replace table t1 (x int) with system versioning partition by system_time ( - partition p0 versioning, - partition p1 versioning); + partition p0 history, + partition p1 history); --error ER_VERS_WRONG_PARTS create or replace table t1 (x int) with system versioning partition by system_time ( - partition pn as of current_timestamp, - partition p0 versioning); + partition pn current, + partition p0 history); create or replace table t1 (x int) with system versioning partition by system_time ( - partition p0 versioning, - partition pn as of current_timestamp); + partition p0 history, + partition pn current); # alter table --error ER_VERS_WRONG_PARTS alter table t1 add partition ( - partition p1 as of current_timestamp); + partition p1 current); alter table t1 add partition ( - partition p1 versioning); + partition p1 history); --replace_result InnoDB ${INNODB_OR_MYISAM} MyISAM ${INNODB_OR_MYISAM} "bigint(20) unsigned" ${SYS_TRX_TYPE} timestamp(6) ${SYS_TRX_TYPE} show create table t1; @@ -98,16 +99,16 @@ select x from t1; create or replace table t1 (x int) with system versioning partition by system_time ( - partition p0 versioning, - partition pn as of current_timestamp); + partition p0 history, + partition pn current); set @now= now(6); insert into t1 values (1); set @ts_start= sys_commit_ts('sys_trx_start'); set @ts_end= sys_commit_ts('sys_trx_end'); -set @str= concat('select x, ', @ts_start, ' < @now as A, ', @ts_end, ' > @now as B from t1 partition (p0) for system_time all'); +set @str= concat('select x, ', @ts_start, ' < @now as A, ', @ts_end, ' > @now as B from t1 partition (p0)'); prepare select_p0 from @str; -set @str= concat('select x, ', @ts_start, ' > @now as C, ', @ts_end, ' = timestamp\'2038-01-19 03:14:07.999999\' as D from t1 partition (pn) for system_time all'); +set @str= concat('select x, ', @ts_start, ' > @now as C, ', @ts_end, ' = timestamp\'2038-01-19 03:14:07.999999\' as D from t1 partition (pn)'); prepare select_pn from @str; execute select_p0; @@ -125,7 +126,7 @@ delete from t1; execute select_p0; execute select_pn; -set @str= concat('select ', @ts_start, ' from t1 partition (p0) for system_time all into @ts1'); +set @str= concat('select ', @ts_start, ' from t1 partition (p0) into @ts1'); prepare stmt from @str; execute stmt; drop prepare stmt; select @ts0 = @ts1; @@ -146,9 +147,9 @@ execute select_pn; drop prepare select_p0; drop prepare select_pn; -set @str= concat('select ', @ts_start, ' from t1 partition (p0) for system_time all where x = 2 into @ts1'); +set @str= concat('select ', @ts_start, ' from t1 partition (p0) where x = 2 into @ts1'); prepare stmt from @str; execute stmt; drop prepare stmt; -set @str= concat('select ', @ts_end, ' from t1 partition (p0) for system_time all where x = 2 into @ts2'); +set @str= concat('select ', @ts_end, ' from t1 partition (p0) where x = 2 into @ts2'); prepare stmt from @str; execute stmt; drop prepare stmt; set @str= concat('select ', @ts_start, ' from t1 partition (pn) into @ts3'); prepare stmt from @str; execute stmt; drop prepare stmt; @@ -161,16 +162,16 @@ select @ts2 = @ts3; create or replace table t1 (x int) with system versioning partition by system_time limit 0 ( - partition p0 versioning, - partition p1 versioning, - partition pn as of current_timestamp); + partition p0 history, + partition p1 history, + partition pn current); create or replace table t1 (x int) with system versioning partition by system_time limit 1 ( - partition p0 versioning, - partition p1 versioning, - partition pn as of current_timestamp); + partition p0 history, + partition p1 history, + partition pn current); --replace_result InnoDB ${INNODB_OR_MYISAM} MyISAM ${INNODB_OR_MYISAM} "bigint(20) unsigned" ${SYS_TRX_TYPE} timestamp(6) ${SYS_TRX_TYPE} show create table t1; @@ -181,38 +182,38 @@ alter table t1 drop partition non_existent; insert into t1 values (1), (2); select * from t1 partition (pn); delete from t1; -select * from t1 partition (p0) for system_time all; -select * from t1 partition (p1) for system_time all; +select * from t1 partition (p0); +select * from t1 partition (p1); insert into t1 values (3); delete from t1; -select * from t1 partition (p1) for system_time all; +select * from t1 partition (p1); # rotation by INTERVAL --error ER_PART_WRONG_VALUE create or replace table t1 (x int) with system versioning partition by system_time interval 0 second ( - partition p0 versioning, - partition p1 versioning, - partition pn as of current_timestamp); + partition p0 history, + partition p1 history, + partition pn current); create or replace table t1 (x int) with system versioning partition by system_time interval 1 second ( - partition p0 versioning, - partition p1 versioning, - partition pn as of current_timestamp); + partition p0 history, + partition p1 history, + partition pn current); insert into t1 values (1), (2), (3); select * from t1 partition (pn); delete from t1; -select * from t1 partition (p0) for system_time all; +select * from t1 partition (p0); --sleep 2 insert into t1 values (4); delete from t1; -select * from t1 partition (p1) for system_time all; +select * from t1 partition (p1); # Subpartitions create or replace table t1 (x int) @@ -220,19 +221,19 @@ with system versioning partition by system_time limit 1 subpartition by key (x) subpartitions 2 ( - partition p0 versioning, - partition p1 versioning, - partition pn as of current_timestamp); + partition p0 history, + partition p1 history, + partition pn current); insert into t1 (x) values (1), (2), (3); select * from t1 partition (pnsp0); select * from t1 partition (pnsp1); delete from t1; -select * from t1 partition (p0sp0) for system_time all; -select * from t1 partition (p0sp1) for system_time all; -select * from t1 partition (p1sp0) for system_time all; -select * from t1 partition (p1sp1) for system_time all; +select * from t1 partition (p0sp0); +select * from t1 partition (p0sp1); +select * from t1 partition (p1sp0); +select * from t1 partition (p1sp1); create or replace table t1 (a bigint) with system versioning diff --git a/mysql-test/suite/versioning/t/rpl.opt b/mysql-test/suite/versioning/t/rpl.opt index c1a585b67eb..51c33158c7a 100644 --- a/mysql-test/suite/versioning/t/rpl.opt +++ b/mysql-test/suite/versioning/t/rpl.opt @@ -1 +1 @@ ---versioning-hide=implicit +--system-versioning-hide=implicit diff --git a/mysql-test/suite/versioning/t/select.test b/mysql-test/suite/versioning/t/select.test index 3ec0451e214..2bba0ab472f 100644 --- a/mysql-test/suite/versioning/t/select.test +++ b/mysql-test/suite/versioning/t/select.test @@ -84,12 +84,21 @@ select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 delete from t1; delete from t2; -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; -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; -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; +#384 +explain extended 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; +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; +#383 +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; + +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; +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; +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; drop table t1; drop table t2; @@ -179,8 +188,9 @@ delete from t1 where x = 3; insert into t2 values (1); select * from t1, t2 for system_time all; ---error ER_VERS_UNUSED_CLAUSE -select * from t1 for system_time all, t2 for system_time all for system_time all; +--error ER_VERS_SYSTEM_TIME_CLASH +select * from (select * from t1 for system_time all, t2 for system_time all) +for system_time all as t; --echo # TRANSACTION/TIMESTAMP specifier in SYSTEM_TIME [MDEV-14645, Issue #396] create or replace table t1 (x int) with system versioning engine myisam; diff --git a/mysql-test/suite/versioning/t/select_sp.test b/mysql-test/suite/versioning/t/select_sp.test index eb1d15663bc..ac19473f716 100644 --- a/mysql-test/suite/versioning/t/select_sp.test +++ b/mysql-test/suite/versioning/t/select_sp.test @@ -93,12 +93,12 @@ begin delete from t1; delete from t2; - 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; - 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; - 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; + select IJ2_x1,y1,x2,y2 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; + select LJ2_x1,y1,x2,y2 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; + select RJ2_x1,y1,x2,y2 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; drop table t1; drop table t2; @@ -192,8 +192,10 @@ delete from t1 where x = 3; insert into t2 values (1); select * from t1, t2 for system_time all; ---error ER_VERS_UNUSED_CLAUSE -select * from t1 for system_time all, t2 for system_time all for system_time all; +--error ER_VERS_SYSTEM_TIME_CLASH +select * from (select * from t1 for system_time all, t2 for system_time all) for system_time all as t; +--error ER_PARSE_ERROR +select * from (t1 for system_time all join t2 for system_time all) for system_time all; drop view v1; drop table t1, t2; diff --git a/mysql-test/suite/versioning/t/simple.test b/mysql-test/suite/versioning/t/simple.test index 5cda9380153..4a288b780ea 100644 --- a/mysql-test/suite/versioning/t/simple.test +++ b/mysql-test/suite/versioning/t/simple.test @@ -44,29 +44,30 @@ select * from emp e, dept d where d.dept_id = 10 and d.dept_id = e.dept_id; -select * from emp e, dept d +select * from + emp for system_time from timestamp @ts_1 to timestamp @ts_2 e, + dept for system_time from timestamp @ts_1 to timestamp @ts_2 d where d.dept_id = 10 - and d.dept_id = e.dept_id -for system_time from timestamp @ts_1 to timestamp @ts_2; + and d.dept_id = e.dept_id; +set statement system_versioning_asof=@ts_0 for select * from emp e, dept d where d.dept_id = 10 - and d.dept_id = e.dept_id -for system_time as of timestamp @ts_0; + and d.dept_id = e.dept_id; +set statement system_versioning_asof=@ts_1 for select * from emp e, dept d where d.dept_id = 10 - and d.dept_id = e.dept_id -for system_time as of timestamp @ts_1; + and d.dept_id = e.dept_id; +set statement system_versioning_asof=@ts_2 for select * from emp e, dept d where d.dept_id = 10 - and d.dept_id = e.dept_id -for system_time as of timestamp @ts_2; + and d.dept_id = e.dept_id; +set statement system_versioning_asof=@ts_3 for select * from emp e, dept d where d.dept_id = 10 - and d.dept_id = e.dept_id -for system_time as of timestamp @ts_3; + and d.dept_id = e.dept_id; drop table emp, dept; diff --git a/mysql-test/suite/versioning/t/sysvars.opt b/mysql-test/suite/versioning/t/sysvars.opt index c1a585b67eb..51c33158c7a 100644 --- a/mysql-test/suite/versioning/t/sysvars.opt +++ b/mysql-test/suite/versioning/t/sysvars.opt @@ -1 +1 @@ ---versioning-hide=implicit +--system-versioning-hide=implicit diff --git a/mysql-test/suite/versioning/t/sysvars.test b/mysql-test/suite/versioning/t/sysvars.test index 87a237d1d42..ef8133fef6f 100644 --- a/mysql-test/suite/versioning/t/sysvars.test +++ b/mysql-test/suite/versioning/t/sysvars.test @@ -2,83 +2,73 @@ create table t (a int) with system versioning; insert into t values (1); update t set a= 2; -show global variables like 'versioning_asof_timestamp'; -show variables like 'versioning_asof_timestamp'; +show global variables like 'system_versioning_asof'; +show variables like 'system_versioning_asof'; select * from t; -set versioning_asof_timestamp= '2031-1-1 0:0:0'; -show variables like 'versioning_asof_timestamp'; +set system_versioning_asof= '2031-1-1 0:0:0'; +show variables like 'system_versioning_asof'; select * from t; -set versioning_asof_timestamp= '2011-1-1 0:0:0'; -show variables like 'versioning_asof_timestamp'; +set system_versioning_asof= '2011-1-1 0:0:0'; +show variables like 'system_versioning_asof'; select * from t; -set versioning_asof_timestamp= ALL; -show variables like 'versioning_asof_timestamp'; -select * from t; - -create view vt as select * from t; -select * from t; -drop view vt; - -select * from (select * from t) as tt; - # global --error ER_WRONG_VALUE_FOR_VAR -set global versioning_asof_timestamp= 'alley'; +set global system_versioning_asof= 'alley'; --error ER_WRONG_VALUE_FOR_VAR -set global versioning_asof_timestamp= null; +set global system_versioning_asof= null; --error ER_WRONG_TYPE_FOR_VAR -set global versioning_asof_timestamp= 1; +set global system_versioning_asof= 1; --error ER_WRONG_TYPE_FOR_VAR -set global versioning_asof_timestamp= 1.1; +set global system_versioning_asof= 1.1; # session --error ER_WRONG_VALUE_FOR_VAR -set versioning_asof_timestamp= 'alley'; +set system_versioning_asof= 'alley'; --error ER_WRONG_VALUE_FOR_VAR -set versioning_asof_timestamp= null; +set system_versioning_asof= null; --error ER_WRONG_TYPE_FOR_VAR -set versioning_asof_timestamp= 1; +set system_versioning_asof= 1; --error ER_WRONG_TYPE_FOR_VAR -set versioning_asof_timestamp= 1.1; +set system_versioning_asof= 1.1; # global -set global versioning_asof_timestamp= '1911-11-11 11:11:11.1111119'; -show global variables like 'versioning_asof_timestamp'; +set global system_versioning_asof= '1911-11-11 11:11:11.1111119'; +show global variables like 'system_versioning_asof'; -set global versioning_asof_timestamp= '1900-01-01 00:00:00'; -show global variables like 'versioning_asof_timestamp'; +set global system_versioning_asof= '1900-01-01 00:00:00'; +show global variables like 'system_versioning_asof'; -set global versioning_asof_timestamp= timestamp'1911-11-11 11:11:11.1111119'; -show global variables like 'versioning_asof_timestamp'; +set global system_versioning_asof= timestamp'1911-11-11 11:11:11.1111119'; +show global variables like 'system_versioning_asof'; set @ts= timestamp'1900-01-01 00:00:00'; -set global versioning_asof_timestamp= @ts; -show global variables like 'versioning_asof_timestamp'; +set global system_versioning_asof= @ts; +show global variables like 'system_versioning_asof'; # session -set versioning_asof_timestamp= '1911-11-11 11:11:11.1111119'; -show variables like 'versioning_asof_timestamp'; +set system_versioning_asof= '1911-11-11 11:11:11.1111119'; +show variables like 'system_versioning_asof'; -set versioning_asof_timestamp= '1900-01-01 00:00:00'; -show variables like 'versioning_asof_timestamp'; +set system_versioning_asof= '1900-01-01 00:00:00'; +show variables like 'system_versioning_asof'; -set versioning_asof_timestamp= timestamp'1911-11-11 11:11:11.1111119'; -show variables like 'versioning_asof_timestamp'; +set system_versioning_asof= timestamp'1911-11-11 11:11:11.1111119'; +show variables like 'system_versioning_asof'; set @ts= timestamp'1900-01-01 00:00:00'; -set versioning_asof_timestamp= @ts; -show variables like 'versioning_asof_timestamp'; +set system_versioning_asof= @ts; +show variables like 'system_versioning_asof'; -set global versioning_asof_timestamp= CURRENT; -set versioning_asof_timestamp= CURRENT; +set global system_versioning_asof= DEFAULT; +set system_versioning_asof= DEFAULT; -show variables where variable_name = "versioning_hide"; +show variables where variable_name = "system_versioning_hide"; select * from t for system_time all; -set versioning_hide= AUTO; +set system_versioning_hide= AUTO; select * from t; select * from t for system_time as of timestamp current_timestamp(6); --replace_regex /\d{4}-\d\d-\d\d \d\d:\d\d:\d\d\.\d{6}/TIMESTAMP/ @@ -88,11 +78,11 @@ select * from t for system_time from '0-0-0' to current_timestamp(6); --replace_regex /\d{4}-\d\d-\d\d \d\d:\d\d:\d\d\.\d{6}/TIMESTAMP/ select * from t for system_time between '0-0-0' and current_timestamp(6); -set versioning_hide= NEVER; +set system_versioning_hide= NEVER; --replace_regex /\d{4}-\d\d-\d\d \d\d:\d\d:\d\d\.\d{6}/TIMESTAMP/ select * from t; -set versioning_hide= FULL; +set system_versioning_hide= FULL; create or replace table t ( x int, st timestamp(6) generated always as row start, @@ -108,4 +98,4 @@ select * from t; select * from t for system_time all; drop table t; -set versioning_hide= IMPLICIT; +set system_versioning_hide= IMPLICIT; diff --git a/mysql-test/suite/versioning/t/truncate.opt b/mysql-test/suite/versioning/t/truncate.opt index 7e681a629a8..c880e02e10c 100644 --- a/mysql-test/suite/versioning/t/truncate.opt +++ b/mysql-test/suite/versioning/t/truncate.opt @@ -1,2 +1,2 @@ ---versioning-hide=implicit +--system-versioning-hide=implicit --partition diff --git a/mysql-test/suite/versioning/t/truncate.test b/mysql-test/suite/versioning/t/truncate.test index 86880c52fd9..a33d9af83d9 100644 --- a/mysql-test/suite/versioning/t/truncate.test +++ b/mysql-test/suite/versioning/t/truncate.test @@ -34,8 +34,8 @@ create or replace table t (a int) with system versioning engine myisam partition by system_time ( - partition p0 versioning, - partition pn as of current_timestamp); + partition p0 history, + partition pn current); --error ER_NOT_ALLOWED_COMMAND truncate table t to system_time current_timestamp; diff --git a/mysql-test/suite/versioning/t/truncate_privilege.test b/mysql-test/suite/versioning/t/truncate_privilege.test index 7042f359e13..c2212570555 100644 --- a/mysql-test/suite/versioning/t/truncate_privilege.test +++ b/mysql-test/suite/versioning/t/truncate_privilege.test @@ -26,8 +26,8 @@ show grants; truncate mysqltest.t to system_time now(); connection root; -grant delete versioning rows on mysqltest.* to mysqltest_1@localhost; -grant delete versioning rows on mysqltest.t to mysqltest_1@localhost; +grant delete history on mysqltest.* to mysqltest_1@localhost; +grant delete history on mysqltest.t to mysqltest_1@localhost; connection user1; show grants; diff --git a/mysql-test/suite/versioning/t/view.opt b/mysql-test/suite/versioning/t/view.opt index c1a585b67eb..51c33158c7a 100644 --- a/mysql-test/suite/versioning/t/view.opt +++ b/mysql-test/suite/versioning/t/view.opt @@ -1 +1 @@ ---versioning-hide=implicit +--system-versioning-hide=implicit diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql index 07d07d3f6d2..c010fffbf67 100644 --- a/scripts/mysql_system_tables.sql +++ b/scripts/mysql_system_tables.sql @@ -23,7 +23,7 @@ set sql_mode=''; set @orig_storage_engine=@@storage_engine; set storage_engine=myisam; -set versioning_alter_history=keep; +set system_versioning_alter_history=keep; set @have_innodb= (select count(engine) from information_schema.engines where engine='INNODB' and support != 'NO'); SET @innodb_or_myisam=IF(@have_innodb <> 0, 'InnoDB', 'MyISAM'); diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index cf0041d4819..faf61c26c66 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -4395,7 +4395,7 @@ int ha_partition::update_row(const uchar *old_data, const uchar *new_data) uint sub_factor= m_part_info->num_subparts ? m_part_info->num_subparts : 1; DBUG_ASSERT(m_tot_parts == m_part_info->num_parts * sub_factor); uint lpart_id= new_part_id / sub_factor; - // lpart_id is VERSIONING partition because new_part_id != old_part_id + // lpart_id is HISTORY partition because new_part_id != old_part_id m_part_info->vers_update_stats(thd, lpart_id); } diff --git a/sql/handler.cc b/sql/handler.cc index 5a216d72cab..1c237e22015 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -3071,8 +3071,8 @@ int handler::update_auto_increment() table->next_number_field->set_null(); DBUG_RETURN(0); } - table->next_number_field->set_notnull(); } + table->next_number_field->set_notnull(); } /* diff --git a/sql/lex.h b/sql/lex.h index 88d20b82710..8a3fe552b95 100644 --- a/sql/lex.h +++ b/sql/lex.h @@ -274,6 +274,7 @@ static SYMBOL symbols[] = { { "HELP", SYM(HELP_SYM)}, { "HIGH_PRIORITY", SYM(HIGH_PRIORITY)}, { "INVISIBLE", SYM(INVISIBLE_SYM)}, + { "HISTORY", SYM(HISTORY_SYM)}, { "HOST", SYM(HOST_SYM)}, { "HOSTS", SYM(HOSTS_SYM)}, { "HOUR", SYM(HOUR_SYM)}, diff --git a/sql/partition_element.h b/sql/partition_element.h index 9785789d3eb..d381ca6dfc8 100644 --- a/sql/partition_element.h +++ b/sql/partition_element.h @@ -156,8 +156,8 @@ public: enum elem_type { CONVENTIONAL= 0, - AS_OF_NOW, - VERSIONING + CURRENT, + HISTORY }; List<partition_element> subpartitions; diff --git a/sql/partition_info.cc b/sql/partition_info.cc index 5487a66fac6..1f7ba06f875 100644 --- a/sql/partition_info.cc +++ b/sql/partition_info.cc @@ -898,6 +898,7 @@ partition_info::vers_part_rotate(THD * thd) Sql_condition::WARN_LEVEL_WARN, WARN_VERS_PART_FULL, ER_THD(thd, WARN_VERS_PART_FULL), + table->s->db.str, table->s->table_name.str, vers_info->hist_part->partition_name); return vers_info->hist_part; } @@ -910,6 +911,7 @@ partition_info::vers_part_rotate(THD * thd) Sql_condition::WARN_LEVEL_NOTE, WARN_VERS_PART_ROTATION, ER_THD(thd, WARN_VERS_PART_ROTATION), + table->s->db.str, table->s->table_name.str, old_part_name, vers_info->hist_part->partition_name); @@ -925,7 +927,7 @@ bool partition_info::vers_set_expression(THD *thd, partition_element *el, MYSQL_ for (uint i= 0; i < num_columns; ++i) { part_column_list_val *col_val= add_column_value(thd); - if (el->type() == partition_element::AS_OF_NOW) + if (el->type() == partition_element::CURRENT) { col_val->max_value= true; col_val->item_expression= NULL; @@ -989,20 +991,20 @@ bool partition_info::vers_setup_expression(THD * thd, uint32 alter_add) if (alter_add) { /* Non-empty historical partitions are left as is. */ - if (el->type() == partition_element::VERSIONING && !el->empty) + if (el->type() == partition_element::HISTORY && !el->empty) { ++id; continue; } /* Newly added element is inserted before AS_OF_NOW. */ - if (el->id == UINT32_MAX || el->type() == partition_element::AS_OF_NOW) + if (el->id == UINT32_MAX || el->type() == partition_element::CURRENT) { DBUG_ASSERT(table && table->s); Vers_min_max_stats *stat_trx_end= new (&table->s->mem_root) Vers_min_max_stats(&table->s->vers_end_field()->field_name, table->s); table->s->stat_trx[id * num_columns + STAT_TRX_END]= stat_trx_end; el->id= id++; - if (el->type() == partition_element::AS_OF_NOW) + if (el->type() == partition_element::CURRENT) break; goto set_expression; } @@ -1035,7 +1037,7 @@ bool partition_info::vers_scan_min_max(THD *thd, partition_element *part) uint32 part_id= part->id * sub_factor; uint32 part_id_end= part_id + sub_factor; DBUG_ASSERT(part->empty); - DBUG_ASSERT(part->type() == partition_element::VERSIONING); + DBUG_ASSERT(part->type() == partition_element::HISTORY); DBUG_ASSERT(table->s->stat_trx); for (; part_id < part_id_end; ++part_id) { @@ -1196,7 +1198,7 @@ bool partition_info::vers_setup_stats(THD * thd, bool is_create_table_ind) partition_element *el= NULL, *prev; while ((prev= el, el= it++)) { - if (el->type() == partition_element::VERSIONING && dont_stat) + if (el->type() == partition_element::HISTORY && dont_stat) { if (el->id == table->s->hist_part_id) { @@ -1214,7 +1216,7 @@ bool partition_info::vers_setup_stats(THD * thd, bool is_create_table_ind) if (!is_create_table_ind) { - if (el->type() == partition_element::AS_OF_NOW) + if (el->type() == partition_element::CURRENT) { uchar buf[8]; Field_timestampf fld(buf, NULL, 0, Field::NONE, &table->vers_end_field()->field_name, NULL, 6); @@ -1235,10 +1237,10 @@ bool partition_info::vers_setup_stats(THD * thd, bool is_create_table_ind) } } - if (el->type() == partition_element::AS_OF_NOW) + if (el->type() == partition_element::CURRENT) break; - DBUG_ASSERT(el->type() == partition_element::VERSIONING); + DBUG_ASSERT(el->type() == partition_element::HISTORY); if (vers_info->hist_part) { @@ -2083,13 +2085,13 @@ bool partition_info::check_partition_info(THD *thd, handlerton **eng_type, } if (part_type == VERSIONING_PARTITION) { - if (part_elem->type() == partition_element::VERSIONING) + if (part_elem->type() == partition_element::HISTORY) { hist_parts++; } else { - DBUG_ASSERT(part_elem->type() == partition_element::AS_OF_NOW); + DBUG_ASSERT(part_elem->type() == partition_element::CURRENT); now_parts++; } } @@ -2139,7 +2141,7 @@ bool partition_info::check_partition_info(THD *thd, handlerton **eng_type, Sql_condition::WARN_LEVEL_WARN, WARN_VERS_PARAMETERS, ER_THD(thd, WARN_VERS_PARAMETERS), - "no rotation condition for multiple `VERSIONING` partitions."); + "no rotation condition for multiple HISTORY partitions."); } } if (now_parts > 1) diff --git a/sql/partition_info.h b/sql/partition_info.h index 6b79fa82fb8..359eae6e8cb 100644 --- a/sql/partition_info.h +++ b/sql/partition_info.h @@ -58,11 +58,11 @@ struct Vers_part_info : public Sql_alloc if (now_part) { DBUG_ASSERT(now_part->id != UINT32_MAX); - DBUG_ASSERT(now_part->type() == partition_element::AS_OF_NOW); + DBUG_ASSERT(now_part->type() == partition_element::CURRENT); DBUG_ASSERT(!fully || (bool) hist_part); DBUG_ASSERT(!hist_part || ( hist_part->id != UINT32_MAX && - hist_part->type() == partition_element::VERSIONING)); + hist_part->type() == partition_element::HISTORY)); return true; } return false; @@ -436,7 +436,7 @@ public: while ((el= it++)) { DBUG_ASSERT(el->type() != partition_element::CONVENTIONAL); - if (el->type() == partition_element::VERSIONING && + if (el->type() == partition_element::HISTORY && el->id == table->s->hist_part_id) { vers_info->hist_part= el; @@ -508,7 +508,7 @@ public: { DBUG_ASSERT(vers_info && vers_info->initialized()); DBUG_ASSERT(table && table->s); - DBUG_ASSERT(el && el->type() == partition_element::VERSIONING); + DBUG_ASSERT(el && el->type() == partition_element::HISTORY); bool updated; mysql_rwlock_wrlock(&table->s->LOCK_stat_serial); el->empty= false; diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index c7483b47ad0..3050b56a725 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7837,13 +7837,13 @@ ER_PARTITION_WRONG_TYPE eng "Wrong partition type, expected type: %`s" WARN_VERS_PART_FULL - eng "Using full partition %`s, need more VERSIONING partitions!" + eng "Versioned table %`s.%`s: partition %`s is full, add more HISTORY partitions" WARN_VERS_PARAMETERS eng "Maybe missing parameters: %s" WARN_VERS_PART_ROTATION - eng "Switching from partition %`s to %`s" + eng "Versioned table %`s.%`s: switching from partition %`s to %`s" WARN_VERS_TRX_MISSING eng "VTQ missing transaction ID %lu" @@ -7852,7 +7852,7 @@ WARN_VERS_PART_NON_HISTORICAL eng "Partition %`s contains non-historical data" ER_VERS_ALTER_NOT_ALLOWED - eng "Not allowed for versioned %`s.%`s. Change `versioning_alter_history` to proceed with ALTER." + eng "Not allowed for versioned %`s.%`s. Change @@system_versioning_alter_history to proceed with ALTER." ER_VERS_ALTER_ENGINE_PROHIBITED eng "Not allowed for versioned %`s.%`s. Change to/from native versioning engine is prohibited." @@ -7897,7 +7897,7 @@ ER_PART_WRONG_VALUE eng "Wrong parameters for partitioned %`s: wrong value for '%s'" ER_VERS_WRONG_PARTS - eng "Wrong partitions consistency for %`s: must have at least one 'VERSIONING' and exactly one last 'AS OF NOW'" + eng "Wrong partitions consistency for %`s: must have at least one HISTORY and exactly one last CURRENT" ER_VERS_HISTORY_LOCK eng "Versioned SELECT write-locking of history rows" diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 307a6badd70..12ed22f6f32 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -2019,7 +2019,7 @@ static bool acl_load(THD *thd, const Grant_tables& tables) user.access|= TRIGGER_ACL; if (user_table.num_fields() <= 46 && (user.access & DELETE_ACL)) - user.access|= DELETE_VERSIONING_ROWS_ACL; + user.access|= DELETE_HISTORY_ACL; user.sort= get_sort(2, user.host.hostname, user.user.str); user.hostname_length= safe_strlen(user.host.hostname); diff --git a/sql/sql_acl.h b/sql/sql_acl.h index 49a7844108e..cff59036867 100644 --- a/sql/sql_acl.h +++ b/sql/sql_acl.h @@ -49,7 +49,7 @@ #define EVENT_ACL (1UL << 26) #define TRIGGER_ACL (1UL << 27) #define CREATE_TABLESPACE_ACL (1UL << 28) -#define DELETE_VERSIONING_ROWS_ACL (1UL << 29) +#define DELETE_HISTORY_ACL (1UL << 29) /* don't forget to update 1. static struct show_privileges_st sys_privileges[] @@ -64,12 +64,12 @@ GRANT_ACL | REFERENCES_ACL | INDEX_ACL | ALTER_ACL | CREATE_TMP_ACL | \ LOCK_TABLES_ACL | EXECUTE_ACL | CREATE_VIEW_ACL | SHOW_VIEW_ACL | \ CREATE_PROC_ACL | ALTER_PROC_ACL | EVENT_ACL | TRIGGER_ACL | \ - DELETE_VERSIONING_ROWS_ACL) + DELETE_HISTORY_ACL) #define TABLE_ACLS \ (SELECT_ACL | INSERT_ACL | UPDATE_ACL | DELETE_ACL | CREATE_ACL | DROP_ACL | \ GRANT_ACL | REFERENCES_ACL | INDEX_ACL | ALTER_ACL | CREATE_VIEW_ACL | \ - SHOW_VIEW_ACL | TRIGGER_ACL | DELETE_VERSIONING_ROWS_ACL) + SHOW_VIEW_ACL | TRIGGER_ACL | DELETE_HISTORY_ACL) #define COL_ACLS \ (SELECT_ACL | INSERT_ACL | UPDATE_ACL | REFERENCES_ACL) @@ -87,7 +87,7 @@ CREATE_TMP_ACL | LOCK_TABLES_ACL | REPL_SLAVE_ACL | REPL_CLIENT_ACL | \ EXECUTE_ACL | CREATE_VIEW_ACL | SHOW_VIEW_ACL | CREATE_PROC_ACL | \ ALTER_PROC_ACL | CREATE_USER_ACL | EVENT_ACL | TRIGGER_ACL | \ - CREATE_TABLESPACE_ACL | DELETE_VERSIONING_ROWS_ACL) + CREATE_TABLESPACE_ACL | DELETE_HISTORY_ACL) #define DEFAULT_CREATE_PROC_ACLS \ (ALTER_PROC_ACL | EXECUTE_ACL) @@ -119,7 +119,7 @@ CREATE_PROC_ACL | ALTER_PROC_ACL ) #define DB_CHUNK4 (EXECUTE_ACL) #define DB_CHUNK5 (EVENT_ACL | TRIGGER_ACL) -#define DB_CHUNK6 (DELETE_VERSIONING_ROWS_ACL) +#define DB_CHUNK6 (DELETE_HISTORY_ACL) #define fix_rights_for_db(A) (((A) & DB_CHUNK0) | \ (((A) << 4) & DB_CHUNK1) | \ @@ -139,7 +139,7 @@ #define TBL_CHUNK1 DB_CHUNK1 #define TBL_CHUNK2 (CREATE_VIEW_ACL | SHOW_VIEW_ACL) #define TBL_CHUNK3 TRIGGER_ACL -#define TBL_CHUNK4 (DELETE_VERSIONING_ROWS_ACL) +#define TBL_CHUNK4 (DELETE_HISTORY_ACL) #define fix_rights_for_table(A) (((A) & TBL_CHUNK0) | \ (((A) << 4) & TBL_CHUNK1) | \ (((A) << 11) & TBL_CHUNK2) | \ diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc index 55d906bb3b4..e4662f2d52e 100644 --- a/sql/sql_partition.cc +++ b/sql/sql_partition.cc @@ -2220,11 +2220,11 @@ static int add_partition_values(String *str, partition_info *part_info, { switch (p_elem->type()) { - case partition_element::AS_OF_NOW: - err+= str->append(STRING_WITH_LEN(" AS OF CURRENT_TIMESTAMP")); + case partition_element::CURRENT: + err+= str->append(STRING_WITH_LEN(" CURRENT")); break; - case partition_element::VERSIONING: - err+= str->append(STRING_WITH_LEN(" VERSIONING")); + case partition_element::HISTORY: + err+= str->append(STRING_WITH_LEN(" HISTORY")); break; default: DBUG_ASSERT(0 && "wrong p_elem->type"); @@ -5000,7 +5000,7 @@ that are reorganised. partition_element *el; while ((el= it++)) { - if (el->type() == partition_element::AS_OF_NOW) + if (el->type() == partition_element::CURRENT) { DBUG_ASSERT(tab_part_info->vers_info && el == tab_part_info->vers_info->now_part); it.remove(); @@ -5094,7 +5094,7 @@ that are reorganised. alter_info->partition_names)) { if (tab_part_info->part_type == VERSIONING_PARTITION && - part_elem->type() == partition_element::AS_OF_NOW) + part_elem->type() == partition_element::CURRENT) { DBUG_ASSERT(table && table->s && table->s->table_name.str); my_error(ER_VERS_WRONG_PARTS, MYF(0), table->s->table_name.str); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index af3783f8f64..95cae826015 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -827,6 +827,27 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables, COND **where_expr vers_select_conds_t &vers_conditions= table->vers_conditions; +#ifdef WITH_PARTITION_STORAGE_ENGINE + /* + if the history is stored in partitions, then partitions + themselves are not versioned + */ + if (table->partition_names && table->table->part_info->vers_info) + { + if (vers_conditions) + { +#define PART_VERS_ERR_MSG "%s PARTITION (%s)" + char buf[NAME_LEN*2 + sizeof(PART_VERS_ERR_MSG)]; + my_snprintf(buf, sizeof(buf), PART_VERS_ERR_MSG, table->alias, + table->partition_names->head()->c_ptr()); + my_error(ER_VERSIONING_REQUIRED, MYF(0), buf); + DBUG_RETURN(-1); + } + else + vers_conditions.init(FOR_SYSTEM_TIME_ALL); + } +#endif + if (outer_table && table != outer_table) // inner table may be outer in recursive CTE { if (vers_conditions) diff --git a/sql/sql_truncate.cc b/sql/sql_truncate.cc index 27e405cd6b9..9286143e259 100644 --- a/sql/sql_truncate.cc +++ b/sql/sql_truncate.cc @@ -498,7 +498,7 @@ bool Sql_cmd_truncate_table::execute(THD *thd) if (table->vers_conditions) { - if (check_one_table_access(thd, DELETE_VERSIONING_ROWS_ACL, table)) + if (check_one_table_access(thd, DELETE_HISTORY_ACL, table)) DBUG_RETURN(res); DBUG_RETURN(mysql_delete(thd, table, NULL, NULL, -1, 0, NULL)); } diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 7c27936b0aa..2c3150b1a38 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -740,12 +740,8 @@ bool LEX::set_bincmp(CHARSET_INFO *cs, bool bin) } while(0) -inline void vers_select_conds_t::init( - vers_range_type_t t, - vers_range_unit_t u_start= UNIT_AUTO, - Item * s= NULL, - vers_range_unit_t u_end= UNIT_AUTO, - Item * e= NULL) +void vers_select_conds_t::init(vers_range_type_t t, vers_range_unit_t u_start, + Item *s, vers_range_unit_t u_end, Item *e) { type= t; unit_start= u_start; @@ -755,7 +751,7 @@ inline void vers_select_conds_t::init( from_query= false; } -inline Item *vers_select_conds_t::fix_dec(Item *item) +Item *vers_select_conds_t::fix_dec(Item *item) { if (item && item->decimals == 0 && item->type() == Item::FUNC_ITEM && ((Item_func*)item)->functype() == Item_func::NOW_FUNC) @@ -896,10 +892,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %parse-param { THD *thd } %lex-param { THD *thd } /* - Currently there are 125 shift/reduce conflicts. + Currently there are 122 shift/reduce conflicts. We should not introduce new conflicts any more. */ -%expect 125 +%expect 122 /* Comments for TOKENS. @@ -1161,6 +1157,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %token HEX_STRING %token HIGH_PRIORITY %token INVISIBLE_SYM +%token HISTORY_SYM /* MYSQL */ %token HOST_SYM %token HOSTS_SYM %token HOUR_MICROSECOND_SYM @@ -5465,7 +5462,7 @@ opt_part_values: part_info->part_type= LIST_PARTITION; } part_values_in {} - | AS OF_SYM NOW_SYM + | CURRENT_SYM { LEX *lex= Lex; partition_info *part_info= lex->part_info; @@ -5484,7 +5481,7 @@ opt_part_values: my_yyabort_error((ER_VERS_WRONG_PARTS, MYF(0), Lex->create_last_non_select_table->table_name)); } - elem->type(partition_element::AS_OF_NOW); + elem->type(partition_element::CURRENT); DBUG_ASSERT(part_info->vers_info); part_info->vers_info->now_part= elem; if (part_info->init_column_part(thd)) @@ -5492,7 +5489,7 @@ opt_part_values: MYSQL_YYABORT; } } - | VERSIONING_SYM + | HISTORY_SYM { LEX *lex= Lex; partition_info *part_info= lex->part_info; @@ -5515,7 +5512,7 @@ opt_part_values: DBUG_ASSERT(Lex->create_last_non_select_table->table_name); my_yyabort_error((ER_VERS_WRONG_PARTS, MYF(0), Lex->create_last_non_select_table->table_name)); } - elem->type(partition_element::VERSIONING); + elem->type(partition_element::HISTORY); if (part_info->init_column_part(thd)) { MYSQL_YYABORT; @@ -9135,7 +9132,6 @@ table_expression: opt_group_clause opt_having_clause opt_window_clause - opt_system_time_clause ; opt_table_expression: @@ -9185,32 +9181,6 @@ opt_trans_or_timestamp: } ; -opt_system_time_clause: - /* empty */ - {} - | FOR_SYSTEM_TIME_SYM system_time_expr - { - DBUG_ASSERT(Select); - int used= 0; - if (Lex->vers_conditions) - { - for (TABLE_LIST *table= Select->table_list.first; table; table= table->next_local) - { - if (!table->vers_conditions) - { - table->vers_conditions= Lex->vers_conditions; - table->vers_conditions.from_query= true; - used++; - } - } - if (!used) - { - my_yyabort_error((ER_VERS_UNUSED_CLAUSE, MYF(0), "SYSTEM_TIME")); - } - } - } - ; - opt_for_system_time_clause: /* empty */ { @@ -11860,7 +11830,10 @@ table_primary_derived: !$$->derived->first_select()->next_select()) $$->select_lex->add_where_field($$->derived->first_select()); if ($5) + { + MYSQL_YYABORT_UNLESS(!$3); $$->vers_conditions= Lex->vers_conditions; + } } /* Represents derived table with WITH clause */ | '(' get_select_lex subselect_start @@ -15528,6 +15501,7 @@ keyword_sp_not_data_type: | HASH_SYM {} | HARD_SYM {} | INVISIBLE_SYM {} + | HISTORY_SYM {} | HOSTS_SYM {} | HOUR_SYM {} | ID_SYM {} @@ -16558,7 +16532,7 @@ object_privilege: | EVENT_SYM { Lex->grant |= EVENT_ACL;} | TRIGGER_SYM { Lex->grant |= TRIGGER_ACL; } | CREATE TABLESPACE { Lex->grant |= CREATE_TABLESPACE_ACL; } - | DELETE_SYM VERSIONING_SYM ROWS_SYM { Lex->grant |= DELETE_VERSIONING_ROWS_ACL; } + | DELETE_SYM HISTORY_SYM { Lex->grant |= DELETE_HISTORY_ACL; } ; opt_and: diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index ed16d663835..d4971c0d8d5 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -387,19 +387,19 @@ static Sys_var_charptr Sys_my_bind_addr( READ_ONLY GLOBAL_VAR(my_bind_addr_str), CMD_LINE(REQUIRED_ARG), IN_FS_CHARSET, DEFAULT(0)); -const char *Sys_var_vers_asof::asof_keywords[]= {"CURRENT", "ALL", NULL}; +const char *Sys_var_vers_asof::asof_keywords[]= {"DEFAULT", NULL}; static Sys_var_vers_asof Sys_vers_asof_timestamp( - "versioning_asof_timestamp", "Default AS OF value for versioned queries", + "system_versioning_asof", "Default value for the FOR SYSTEM_TIME AS OF clause", SESSION_VAR(vers_asof_timestamp.type), NO_CMD_LINE, Sys_var_vers_asof::asof_keywords, DEFAULT(FOR_SYSTEM_TIME_UNSPECIFIED)); static Sys_var_mybool Sys_vers_force( - "versioning_force", "Force system versioning for all created tables", + "system_versioning_force", "Force system versioning for all created tables", SESSION_VAR(vers_force), CMD_LINE(OPT_ARG), DEFAULT(FALSE)); static const char *vers_hide_keywords[]= {"AUTO", "IMPLICIT", "FULL", "NEVER", NULL}; static Sys_var_enum Sys_vers_hide( - "versioning_hide", "Hide system versioning from being displayed in table info. " + "system_versioning_hide", "Hide system versioning from being displayed in table info. " "AUTO: hide implicit system fields only in non-versioned and AS OF queries; " "IMPLICIT: hide implicit system fields in all queries; " "FULL: hide any system fields in all queries and hide versioning info in SHOW commands; " @@ -408,14 +408,14 @@ static Sys_var_enum Sys_vers_hide( vers_hide_keywords, DEFAULT(VERS_HIDE_AUTO)); static Sys_var_mybool Sys_vers_innodb_algorithm_simple( - "versioning_innodb_algorithm_simple", + "system_versioning_innodb_algorithm_simple", "Use simple algorithm of timestamp handling in InnoDB instead of TRX_SEES", SESSION_VAR(vers_innodb_algorithm_simple), CMD_LINE(OPT_ARG), DEFAULT(TRUE)); static const char *vers_alter_history_keywords[]= {"ERROR", "KEEP",/* "SURVIVE", "DROP",*/ NULL}; static Sys_var_enum Sys_vers_alter_history( - "versioning_alter_history", "Versioning ALTER TABLE mode. " + "system_versioning_alter_history", "Versioning ALTER TABLE mode. " "ERROR: Fail ALTER with error; " /* TODO: fail only when history non-empty */ "KEEP: Keep historical system rows and subject them to ALTER; " /*"SURVIVE: Keep historical system rows intact; " diff --git a/sql/table.h b/sql/table.h index 76e37da7cd0..4fffae7e763 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1886,8 +1886,9 @@ struct vers_select_conds_t Item *fix_dec(Item *item); - void init( vers_range_type_t t, vers_range_unit_t u_start, - Item * s, vers_range_unit_t u_end, Item * e); + void init(vers_range_type_t t, vers_range_unit_t u_start= UNIT_AUTO, + Item * s= NULL, vers_range_unit_t u_end= UNIT_AUTO, + Item * e= NULL); bool init_from_sysvar(THD *thd); |