From e0b4db5ba3d1fb14a1ec9e4ab0b346c6a8eebfd6 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Tue, 11 Oct 2022 12:56:01 +0200 Subject: MDEV-29750 triggers can modify history should be the same behavior as for virtual columns: * a warning on every inserted row * silently ignored in a trigger --- mysql-test/suite/versioning/r/misc.result | 27 +++++++++++++++++++++++++++ mysql-test/suite/versioning/t/misc.test | 20 ++++++++++++++++++++ 2 files changed, 47 insertions(+) create mode 100644 mysql-test/suite/versioning/r/misc.result create mode 100644 mysql-test/suite/versioning/t/misc.test (limited to 'mysql-test/suite/versioning') diff --git a/mysql-test/suite/versioning/r/misc.result b/mysql-test/suite/versioning/r/misc.result new file mode 100644 index 00000000000..398e3b8be70 --- /dev/null +++ b/mysql-test/suite/versioning/r/misc.result @@ -0,0 +1,27 @@ +set time_zone='+00:00'; +# +# MDEV-29750 triggers can modify history +# +set sql_mode='', timestamp=unix_timestamp('2010-10-10 10:10:10'); +create table t (a int, b int as (a+1), s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning; +insert into t values (1,1, '2022-01-01','2023-01-01'),(2,2, '2022-02-02','2023-02-02'); +Warnings: +Warning 1906 The value specified for generated column 'b' in table 't' has been ignored +Warning 1906 The value specified for generated column 's' in table 't' has been ignored +Warning 1906 The value specified for generated column 'e' in table 't' has been ignored +Warning 1906 The value specified for generated column 'b' in table 't' has been ignored +Warning 1906 The value specified for generated column 's' in table 't' has been ignored +Warning 1906 The value specified for generated column 'e' in table 't' has been ignored +create trigger tr before insert on t for each row set new.b=1, new.s = '2022-03-03', new.e = '2023-03-03'; +insert into t (a) values (3),(4); +select * from t for system_time all; +a b s e +1 2 2010-10-10 10:10:10.000000 2038-01-19 03:14:07.999999 +2 3 2010-10-10 10:10:10.000000 2038-01-19 03:14:07.999999 +3 4 2010-10-10 10:10:10.000000 2038-01-19 03:14:07.999999 +4 5 2010-10-10 10:10:10.000000 2038-01-19 03:14:07.999999 +drop table t; +set sql_mode=default, timestamp=default; +# +# End of 10.3 tests +# diff --git a/mysql-test/suite/versioning/t/misc.test b/mysql-test/suite/versioning/t/misc.test new file mode 100644 index 00000000000..dce1e0deced --- /dev/null +++ b/mysql-test/suite/versioning/t/misc.test @@ -0,0 +1,20 @@ +# +# simple tests that don't need to be run in multiple various combinations +# +set time_zone='+00:00'; + +--echo # +--echo # MDEV-29750 triggers can modify history +--echo # +set sql_mode='', timestamp=unix_timestamp('2010-10-10 10:10:10'); +create table t (a int, b int as (a+1), s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning; +insert into t values (1,1, '2022-01-01','2023-01-01'),(2,2, '2022-02-02','2023-02-02'); +create trigger tr before insert on t for each row set new.b=1, new.s = '2022-03-03', new.e = '2023-03-03'; +insert into t (a) values (3),(4); +select * from t for system_time all; +drop table t; +set sql_mode=default, timestamp=default; + +--echo # +--echo # End of 10.3 tests +--echo # -- cgit v1.2.1 From 9de37e07de860fdbaade1de482692a9221fbcc98 Mon Sep 17 00:00:00 2001 From: Alexey Botchkov Date: Tue, 18 Oct 2022 00:01:58 +0400 Subject: MDEV-19569 Assertion `table_list->table' failed in find_field_in_table_ref. Disallow subqueries in The PARTITIN BY INTERVAL syntax. Fix various interval types that now fail as they break syntax in the par file. --- mysql-test/suite/versioning/r/partition.result | 101 +++++++++++++++++++++++++ mysql-test/suite/versioning/t/partition.test | 74 ++++++++++++++++++ 2 files changed, 175 insertions(+) (limited to 'mysql-test/suite/versioning') diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index 9c94e76a68f..8adde14a875 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -1145,5 +1145,106 @@ insert into t values (1),(2); create trigger tr before insert on t for each row update tcount set c = c + 1; insert into t select * from t; drop table tcount, t; +# +# MDEV-19569 Assertion `table_list->table' failed in find_field_in_table_ref and Assertion `table_ref->table || table_ref->view' in Field_iterator_table_ref::set_field_iterator +# +create table t1 (i int); +create table t2 (i int); +alter table t1 partition by system_time +interval (select i from t2) day (partition p1 history, partition pn current); +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 'select i from t2) day (partition p1 history, partition pn current)' at line 2 +drop table t1; +create table t1 (id int) with system versioning +partition by system_time +interval (select i from t2) day (partition p1 history, partition pn current); +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 'select i from t2) day (partition p1 history, partition pn current)' at line 3 +create table t1 (id int) with system versioning +partition by system_time +interval "hello" day (partition p1 history, partition pn current); +ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL' +create table t1 (id int) with system versioning +partition by system_time +interval 3.893 day (partition p1 history, partition pn current); +drop table t1, t2; +create table t1 (id int) with system versioning +partition by system_time interval "3-11" year_month (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '3-11' YEAR_MONTH +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "3 11" day_hour (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '3 11' DAY_HOUR +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "3 11:12" day_minute (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '3 11:12' DAY_MINUTE +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "3 11:12:13" day_second (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '3 11:12:13' DAY_SECOND +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "11:12" hour_minute (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '11:12' HOUR_MINUTE +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "11:12:13" hour_second (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '11:12:13' HOUR_SECOND +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "12:13" minute_second (partition p1 history, partition pn current); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + PARTITION BY SYSTEM_TIME INTERVAL '12:13' MINUTE_SECOND +(PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, + PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) +drop table t1; +create table t1 (id int) with system versioning +partition by system_time interval "12:13.123" minute_microsecond (partition p1 history, partition pn current); +ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL' # End of 10.3 tests set global innodb_stats_persistent= @save_persistent; diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index b18493ae91c..9cd6a5d21e8 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -1098,6 +1098,80 @@ insert into t select * from t; # cleanup drop table tcount, t; +--echo # +--echo # MDEV-19569 Assertion `table_list->table' failed in find_field_in_table_ref and Assertion `table_ref->table || table_ref->view' in Field_iterator_table_ref::set_field_iterator +--echo # +create table t1 (i int); +create table t2 (i int); + +--error ER_PARSE_ERROR +alter table t1 partition by system_time + interval (select i from t2) day (partition p1 history, partition pn current); + +drop table t1; + +--error ER_PARSE_ERROR +create table t1 (id int) with system versioning + partition by system_time + interval (select i from t2) day (partition p1 history, partition pn current); + +--error ER_PART_WRONG_VALUE +create table t1 (id int) with system versioning + partition by system_time + interval "hello" day (partition p1 history, partition pn current); + +create table t1 (id int) with system versioning + partition by system_time + interval 3.893 day (partition p1 history, partition pn current); + +drop table t1, t2; + +create table t1 (id int) with system versioning + partition by system_time interval "3-11" year_month (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +create table t1 (id int) with system versioning + partition by system_time interval "3 11" day_hour (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +create table t1 (id int) with system versioning + partition by system_time interval "3 11:12" day_minute (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +create table t1 (id int) with system versioning + partition by system_time interval "3 11:12:13" day_second (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +create table t1 (id int) with system versioning + partition by system_time interval "11:12" hour_minute (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +create table t1 (id int) with system versioning + partition by system_time interval "11:12:13" hour_second (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +create table t1 (id int) with system versioning + partition by system_time interval "12:13" minute_second (partition p1 history, partition pn current); +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; + +--error ER_PART_WRONG_VALUE +create table t1 (id int) with system versioning + partition by system_time interval "12:13.123" minute_microsecond (partition p1 history, partition pn current); + --echo # End of 10.3 tests set global innodb_stats_persistent= @save_persistent; -- cgit v1.2.1 From 4b4c2b8cc0da949895292121ed5ef3e0c2dbaae1 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Tue, 25 Oct 2022 11:43:59 +0300 Subject: Merge 10.4 into 10.5 --- mysql-test/suite/versioning/r/partition.result | 15 ++++++++------- mysql-test/suite/versioning/t/partition.test | 1 + 2 files changed, 9 insertions(+), 7 deletions(-) (limited to 'mysql-test/suite/versioning') diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index e99afef738d..b8ad7693f51 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -1539,6 +1539,7 @@ drop table tcount, t; # # MDEV-19569 Assertion `table_list->table' failed in find_field_in_table_ref and Assertion `table_ref->table || table_ref->view' in Field_iterator_table_ref::set_field_iterator # +set timestamp=unix_timestamp('2000-01-01 00:00:00'); create table t1 (i int); create table t2 (i int); alter table t1 partition by system_time @@ -1564,7 +1565,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING - PARTITION BY SYSTEM_TIME INTERVAL '3-11' YEAR_MONTH + PARTITION BY SYSTEM_TIME INTERVAL '3-11' YEAR_MONTH STARTS TIMESTAMP'2000-01-01 00:00:00' (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) drop table t1; @@ -1575,7 +1576,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING - PARTITION BY SYSTEM_TIME INTERVAL '3 11' DAY_HOUR + PARTITION BY SYSTEM_TIME INTERVAL '3 11' DAY_HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) drop table t1; @@ -1586,7 +1587,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING - PARTITION BY SYSTEM_TIME INTERVAL '3 11:12' DAY_MINUTE + PARTITION BY SYSTEM_TIME INTERVAL '3 11:12' DAY_MINUTE STARTS TIMESTAMP'2000-01-01 00:00:00' (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) drop table t1; @@ -1597,7 +1598,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING - PARTITION BY SYSTEM_TIME INTERVAL '3 11:12:13' DAY_SECOND + PARTITION BY SYSTEM_TIME INTERVAL '3 11:12:13' DAY_SECOND STARTS TIMESTAMP'2000-01-01 00:00:00' (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) drop table t1; @@ -1608,7 +1609,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING - PARTITION BY SYSTEM_TIME INTERVAL '11:12' HOUR_MINUTE + PARTITION BY SYSTEM_TIME INTERVAL '11:12' HOUR_MINUTE STARTS TIMESTAMP'2000-01-01 00:00:00' (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) drop table t1; @@ -1619,7 +1620,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING - PARTITION BY SYSTEM_TIME INTERVAL '11:12:13' HOUR_SECOND + PARTITION BY SYSTEM_TIME INTERVAL '11:12:13' HOUR_SECOND STARTS TIMESTAMP'2000-01-01 00:00:00' (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) drop table t1; @@ -1630,7 +1631,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING - PARTITION BY SYSTEM_TIME INTERVAL '12:13' MINUTE_SECOND + PARTITION BY SYSTEM_TIME INTERVAL '12:13' MINUTE_SECOND STARTS TIMESTAMP'2000-01-01 00:00:00' (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE, PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE) drop table t1; diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index 3b8aa854625..1229d42a4e5 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -1388,6 +1388,7 @@ drop table tcount, t; --echo # --echo # MDEV-19569 Assertion `table_list->table' failed in find_field_in_table_ref and Assertion `table_ref->table || table_ref->view' in Field_iterator_table_ref::set_field_iterator --echo # +set timestamp=unix_timestamp('2000-01-01 00:00:00'); create table t1 (i int); create table t2 (i int); -- cgit v1.2.1 From 6449af6f2d52c7acb483fcfb186c838edaf0424a Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Mon, 31 Oct 2022 17:47:22 +0100 Subject: fix results after merge --- mysql-test/suite/versioning/t/data.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/suite/versioning') diff --git a/mysql-test/suite/versioning/t/data.test b/mysql-test/suite/versioning/t/data.test index 4046ec609dd..ddae6633337 100644 --- a/mysql-test/suite/versioning/t/data.test +++ b/mysql-test/suite/versioning/t/data.test @@ -23,7 +23,7 @@ set timestamp=default; --exec $MYSQL_DUMP --compact --as-of="1990-08-04 00:00" test t1 ## Forged query protection --echo #MYSQL_DUMP --compact --as-of="1990-08-04 00:00' where 'abc" test 2>&1 ---replace_result mysqldump.exe mysqldump +--replace_result mariadb-dump.exe mariadb-dump --error 1 --exec $MYSQL_DUMP --compact --as-of="1990-08-04 00:00' where 'abc" test 2>&1 -- cgit v1.2.1