diff options
44 files changed, 722 insertions, 216 deletions
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index c2b3b194c37..903bd81f2cb 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -2236,3 +2236,38 @@ a b drop table t1; set optimizer_switch=@save_optimizer_switch; SET SESSION STORAGE_ENGINE=DEFAULT; +# +# MDEV-19919: use of rowid filter for innodb table + ORDER BY +# +SET @stats.save= @@innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent= ON; +CREATE TABLE t1 ( +a INT, +b VARCHAR(10), +c VARCHAR(1024), +KEY (b), +KEY (c) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(1,'w','z'), (1,'X','o'), (1,'q','c'), (5,'w','c'), (2,'j','m'), +(2,'Q','s'), (9,'e','J'), (2,'p','W'), (9,'o','F'), (2,'g','S'), +(1,'Y','a'), (NULL,'Y','p'), (NULL,'s','x'), (NULL,'i','S'), +(1,'l','q'), (7,'r','e'), (4,'b','h'), (NULL,'E','c'), +(NULL,'M','a'), (3,'e','X'), (NULL,'p','r'), (9,'e','i'), +(3,'g','x'), (2,'h','y'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +EXPLAIN EXTENDED +SELECT a FROM t1 WHERE c < 'k' AND b > 't' ORDER BY a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range|filter b,c b|c 13|1027 NULL 5 (42%) 41.67 Using index condition; Using where; Using filesort; Using rowid filter +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`c` < 'k' and `test`.`t1`.`b` > 't' order by `test`.`t1`.`a` +SELECT a FROM t1 WHERE c < 'k' AND b > 't' ORDER BY a; +a +1 +5 +DROP TABLE t1; +SET GLOBAL innodb_stats_persistent= @stats.save; diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test index 240cd928d06..4a6c4316aa8 100644 --- a/mysql-test/main/rowid_filter_innodb.test +++ b/mysql-test/main/rowid_filter_innodb.test @@ -96,3 +96,36 @@ drop table t1; set optimizer_switch=@save_optimizer_switch; SET SESSION STORAGE_ENGINE=DEFAULT; + +--echo # +--echo # MDEV-19919: use of rowid filter for innodb table + ORDER BY +--echo # + +SET @stats.save= @@innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent= ON; + +CREATE TABLE t1 ( + a INT, + b VARCHAR(10), + c VARCHAR(1024), + KEY (b), + KEY (c) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES + (1,'w','z'), (1,'X','o'), (1,'q','c'), (5,'w','c'), (2,'j','m'), + (2,'Q','s'), (9,'e','J'), (2,'p','W'), (9,'o','F'), (2,'g','S'), + (1,'Y','a'), (NULL,'Y','p'), (NULL,'s','x'), (NULL,'i','S'), + (1,'l','q'), (7,'r','e'), (4,'b','h'), (NULL,'E','c'), + (NULL,'M','a'), (3,'e','X'), (NULL,'p','r'), (9,'e','i'), + (3,'g','x'), (2,'h','y'); + +ANALYZE TABLE t1; + +EXPLAIN EXTENDED +SELECT a FROM t1 WHERE c < 'k' AND b > 't' ORDER BY a; + +SELECT a FROM t1 WHERE c < 'k' AND b > 't' ORDER BY a; + +DROP TABLE t1; +SET GLOBAL innodb_stats_persistent= @stats.save; diff --git a/mysql-test/suite/federated/federatedx.result b/mysql-test/suite/federated/federatedx.result index 84dcb0d0a8c..8345f56dba9 100644 --- a/mysql-test/suite/federated/federatedx.result +++ b/mysql-test/suite/federated/federatedx.result @@ -2283,6 +2283,22 @@ connection default; connection master; CREATE TABLE t1 (a INT) ENGINE=FEDERATED CONNECTION='mysql://@127.0.0.1:SLAVE_PORT/federated/t1'; ERROR HY000: Can't create federated table. Foreign data src error: database: 'federated' username: '' hostname: '127.0.0.1' +# +# MDEV-21049 Segfault in create federatedx table with empty hostname +# +connection master; +CREATE TABLE federated.t1 (x int) ENGINE=FEDERATED +CONNECTION='mysql://root@:SLAVE_PORT/federated/t1'; +ERROR HY000: Can't create federated table. Foreign data src error: database: 'federated' username: 'root' hostname: 'localhost' +connection slave; +CREATE TABLE federated.t1(x int); +connection master; +CREATE TABLE federated.t1 (x int) ENGINE=FEDERATED +CONNECTION='mysql://root@:SLAVE_PORT/federated/t1'; +DROP TABLE federated.t1; +connection slave; +DROP TABLE federated.t1; +connection default; connection master; DROP TABLE IF EXISTS federated.t1; DROP DATABASE IF EXISTS federated; diff --git a/mysql-test/suite/federated/federatedx.test b/mysql-test/suite/federated/federatedx.test index 29d1eaddc26..fcc0178c024 100644 --- a/mysql-test/suite/federated/federatedx.test +++ b/mysql-test/suite/federated/federatedx.test @@ -2010,4 +2010,25 @@ connection master; --error ER_CANT_CREATE_FEDERATED_TABLE eval CREATE TABLE t1 (a INT) ENGINE=FEDERATED CONNECTION='mysql://@127.0.0.1:$SLAVE_MYPORT/federated/t1'; +--echo # +--echo # MDEV-21049 Segfault in create federatedx table with empty hostname +--echo # +connection master; +--replace_result $SLAVE_MYPORT SLAVE_PORT +--error ER_CANT_CREATE_FEDERATED_TABLE +eval CREATE TABLE federated.t1 (x int) ENGINE=FEDERATED + CONNECTION='mysql://root@:$SLAVE_MYPORT/federated/t1'; + +connection slave; +CREATE TABLE federated.t1(x int); +connection master; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval CREATE TABLE federated.t1 (x int) ENGINE=FEDERATED + CONNECTION='mysql://root@:$SLAVE_MYPORT/federated/t1'; + +DROP TABLE federated.t1; +connection slave; +DROP TABLE federated.t1; +connection default; + source include/federated_cleanup.inc; diff --git a/mysql-test/suite/galera/r/galera_as_slave_gtid_auto_engine.result b/mysql-test/suite/galera/r/galera_as_slave_gtid_auto_engine.result new file mode 100644 index 00000000000..6c84c1ecd31 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_as_slave_gtid_auto_engine.result @@ -0,0 +1,40 @@ +connection node_2; +connection node_1; +connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3; +connection node_2; +START SLAVE; +connection node_3; +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1); +SELECT LENGTH(@@global.gtid_binlog_state) > 1; +LENGTH(@@global.gtid_binlog_state) > 1 +1 +connection node_2; +gtid_binlog_state_equal +1 +connection node_1; +SELECT COUNT(*) = 1 FROM t1; +COUNT(*) = 1 +1 +gtid_binlog_state_equal +1 +connection node_3; +DROP TABLE t1; +connection node_1; +connection node_2; +STOP SLAVE; +RESET SLAVE ALL; +#cleanup +connection node_1; +set global wsrep_on=OFF; +reset master; +set global wsrep_on=ON; +connection node_2; +set global wsrep_on=OFF; +reset master; +set global wsrep_on=ON; +connection node_3; +reset master; +connection node_2; +DROP TABLE mysql.gtid_slave_pos_InnoDB; +CALL mtr.add_suppression("The automatically created table"); diff --git a/mysql-test/suite/galera/t/galera_as_slave_gtid.inc b/mysql-test/suite/galera/t/galera_as_slave_gtid.inc new file mode 100644 index 00000000000..f5222b4322b --- /dev/null +++ b/mysql-test/suite/galera/t/galera_as_slave_gtid.inc @@ -0,0 +1,86 @@ +# +# Test Galera as a slave to a MariaDB master using GTIDs +# +# suite/galera/galera_2nodes_as_slave.cnf describes the setup of the nodes +# suite/galera/t/galera_as_slave_gtid.cnf has the GTID options +# +# In addition to performing DDL and DML, we check that the gtid of the master is preserved inside the cluster +# + +--source include/have_innodb.inc +--source include/galera_cluster.inc + +# As node #3 is not a Galera node, and galera_cluster.inc does not open connetion to it +# we open the node_3 connection here +--connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3 + +--connection node_2 +--disable_query_log +--eval CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='root', MASTER_PORT=$NODE_MYPORT_3; +--enable_query_log +START SLAVE; + +--connection node_3 +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1); + +SELECT LENGTH(@@global.gtid_binlog_state) > 1; +--let $gtid_binlog_state_node1 = `SELECT @@global.gtid_binlog_state;` + +--connection node_2 +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'; +--source include/wait_condition.inc + +--let $wait_condition = SELECT COUNT(*) = 1 FROM t1; +--source include/wait_condition.inc + +--disable_query_log + +--eval SELECT '$gtid_binlog_state_node1' = @@global.gtid_binlog_state AS gtid_binlog_state_equal; +#--eval SELECT GTID_SUBSET('$gtid_executed_node1', @@global.gtid_executed) AS gtid_executed_equal; + +--enable_query_log + +--connection node_1 +SELECT COUNT(*) = 1 FROM t1; + +--disable_query_log +--eval SELECT '$gtid_binlog_state_node1' = @@global.gtid_binlog_state AS gtid_binlog_state_equal; +#--eval SELECT GTID_SUBSET('$gtid_executed_node1', @@global.gtid_executed) AS gtid_executed_equal; +--enable_query_log + +--connection node_3 +DROP TABLE t1; + +# +# Unfortunately without the sleep below the following statement fails with "query returned no rows", which +# is difficult to understand given that it is an aggregate query. A "query execution was interrupted" +# warning is also reported by MTR, which is also weird. +# + +--sleep 1 + +--connection node_1 +--let $wait_condition = SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'; +--source include/wait_condition.inc + +--connection node_2 +--let $wait_condition = SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'; +--source include/wait_condition.inc + +STOP SLAVE; +RESET SLAVE ALL; + +--echo #cleanup +--connection node_1 +set global wsrep_on=OFF; +reset master; +set global wsrep_on=ON; + +--connection node_2 +set global wsrep_on=OFF; +reset master; +set global wsrep_on=ON; + +--connection node_3 +reset master; diff --git a/mysql-test/suite/galera/t/galera_as_slave_gtid.test b/mysql-test/suite/galera/t/galera_as_slave_gtid.test index f5222b4322b..c5f45031050 100644 --- a/mysql-test/suite/galera/t/galera_as_slave_gtid.test +++ b/mysql-test/suite/galera/t/galera_as_slave_gtid.test @@ -7,80 +7,4 @@ # In addition to performing DDL and DML, we check that the gtid of the master is preserved inside the cluster # ---source include/have_innodb.inc ---source include/galera_cluster.inc - -# As node #3 is not a Galera node, and galera_cluster.inc does not open connetion to it -# we open the node_3 connection here ---connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3 - ---connection node_2 ---disable_query_log ---eval CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='root', MASTER_PORT=$NODE_MYPORT_3; ---enable_query_log -START SLAVE; - ---connection node_3 -CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; -INSERT INTO t1 VALUES(1); - -SELECT LENGTH(@@global.gtid_binlog_state) > 1; ---let $gtid_binlog_state_node1 = `SELECT @@global.gtid_binlog_state;` - ---connection node_2 ---let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'; ---source include/wait_condition.inc - ---let $wait_condition = SELECT COUNT(*) = 1 FROM t1; ---source include/wait_condition.inc - ---disable_query_log - ---eval SELECT '$gtid_binlog_state_node1' = @@global.gtid_binlog_state AS gtid_binlog_state_equal; -#--eval SELECT GTID_SUBSET('$gtid_executed_node1', @@global.gtid_executed) AS gtid_executed_equal; - ---enable_query_log - ---connection node_1 -SELECT COUNT(*) = 1 FROM t1; - ---disable_query_log ---eval SELECT '$gtid_binlog_state_node1' = @@global.gtid_binlog_state AS gtid_binlog_state_equal; -#--eval SELECT GTID_SUBSET('$gtid_executed_node1', @@global.gtid_executed) AS gtid_executed_equal; ---enable_query_log - ---connection node_3 -DROP TABLE t1; - -# -# Unfortunately without the sleep below the following statement fails with "query returned no rows", which -# is difficult to understand given that it is an aggregate query. A "query execution was interrupted" -# warning is also reported by MTR, which is also weird. -# - ---sleep 1 - ---connection node_1 ---let $wait_condition = SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'; ---source include/wait_condition.inc - ---connection node_2 ---let $wait_condition = SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'; ---source include/wait_condition.inc - -STOP SLAVE; -RESET SLAVE ALL; - ---echo #cleanup ---connection node_1 -set global wsrep_on=OFF; -reset master; -set global wsrep_on=ON; - ---connection node_2 -set global wsrep_on=OFF; -reset master; -set global wsrep_on=ON; - ---connection node_3 -reset master; +--source galera_as_slave_gtid.inc diff --git a/mysql-test/suite/galera/t/galera_as_slave_gtid_auto_engine.cnf b/mysql-test/suite/galera/t/galera_as_slave_gtid_auto_engine.cnf new file mode 100644 index 00000000000..adcba9b6069 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_as_slave_gtid_auto_engine.cnf @@ -0,0 +1,8 @@ +!include ../galera_2nodes_as_slave.cnf + +[mysqld] +log-bin=mysqld-bin +log-slave-updates +binlog-format=ROW + +gtid_pos_auto_engines=InnoDB
\ No newline at end of file diff --git a/mysql-test/suite/galera/t/galera_as_slave_gtid_auto_engine.test b/mysql-test/suite/galera/t/galera_as_slave_gtid_auto_engine.test new file mode 100644 index 00000000000..990dd35f40e --- /dev/null +++ b/mysql-test/suite/galera/t/galera_as_slave_gtid_auto_engine.test @@ -0,0 +1,14 @@ +# +# Test Galera as a slave to a MariaDB master using GTIDs +# +# suite/galera/galera_2nodes_as_slave.cnf describes the setup of the nodes +# suite/galera/t/galera_as_slave_gtid.cnf has the GTID options +# +# In addition to performing DDL and DML, we check that the gtid of the master is preserved inside the cluster +# + +--source galera_as_slave_gtid.inc + +--connection node_2 +DROP TABLE mysql.gtid_slave_pos_InnoDB; +CALL mtr.add_suppression("The automatically created table");
\ No newline at end of file diff --git a/mysql-test/suite/innodb/r/instant_alter_bugs.result b/mysql-test/suite/innodb/r/instant_alter_bugs.result index 19262246c9b..95efacf294b 100644 --- a/mysql-test/suite/innodb/r/instant_alter_bugs.result +++ b/mysql-test/suite/innodb/r/instant_alter_bugs.result @@ -324,4 +324,37 @@ InnoDB 0 transactions not purged SELECT * FROM t1; a DROP TABLE t1; +# +# MDEV-20190 Instant operation fails when add column and collation +# change on non-indexed column +# +CREATE TABLE t1 (a CHAR)ENGINE=INNODB; +ALTER TABLE t1 DEFAULT COLLATE= latin1_general_cs; +ALTER TABLE t1 ADD COLUMN b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(1) COLLATE latin1_general_cs DEFAULT NULL, + `b` int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs +DROP TABLE t1; +CREATE TABLE t1 (a CHAR NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DEFAULT COLLATE = latin1_general_cs; +ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(1) COLLATE latin1_general_cs DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs ROW_FORMAT=REDUNDANT +DROP TABLE t1; +CREATE TABLE t1 (a CHAR NOT NULL) CHARSET latin2 COLLATE latin2_bin +ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DEFAULT COLLATE = latin2_general_ci; +ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(1) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin2 ROW_FORMAT=REDUNDANT +DROP TABLE t1; SET GLOBAL innodb_purge_rseg_truncate_frequency=@save_frequency; diff --git a/mysql-test/suite/innodb/r/instant_alter_debug.result b/mysql-test/suite/innodb/r/instant_alter_debug.result index 7ab24a7671b..72c9a85e369 100644 --- a/mysql-test/suite/innodb/r/instant_alter_debug.result +++ b/mysql-test/suite/innodb/r/instant_alter_debug.result @@ -328,3 +328,21 @@ WHERE variable_name = 'innodb_instant_alter_column'; instants 22 SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; +# +# MDEV-21045 AddressSanitizer: use-after-poison in mem_heap_dup / row_log_table_get_pk_col +# +CREATE TABLE t1 (a TEXT) ENGINE = InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 (a) VALUES ('foo'); +ALTER TABLE t1 ADD COLUMN b INT DEFAULT 0,algorithm=instant; +connect con2,localhost,root,,test; +SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL onlinealter WAIT_FOR update'; +ALTER TABLE t1 ADD PRIMARY KEY (b); +connection default; +SET DEBUG_SYNC='now WAIT_FOR onlinealter'; +UPDATE t1 SET b = 1; +SET DEBUG_SYNC='now SIGNAL update'; +connection con2; +connection default; +SET DEBUG_SYNC='RESET'; +disconnect con2; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/instant_alter_bugs.test b/mysql-test/suite/innodb/t/instant_alter_bugs.test index 090a4aef787..ac93089e60e 100644 --- a/mysql-test/suite/innodb/t/instant_alter_bugs.test +++ b/mysql-test/suite/innodb/t/instant_alter_bugs.test @@ -348,4 +348,29 @@ ALTER TABLE t1 DROP b, DROP c, DROP d, DROP e; --source include/wait_all_purged.inc SELECT * FROM t1; DROP TABLE t1; + +--echo # +--echo # MDEV-20190 Instant operation fails when add column and collation +--echo # change on non-indexed column +--echo # + +CREATE TABLE t1 (a CHAR)ENGINE=INNODB; +ALTER TABLE t1 DEFAULT COLLATE= latin1_general_cs; +ALTER TABLE t1 ADD COLUMN b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DEFAULT COLLATE = latin1_general_cs; +ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR NOT NULL) CHARSET latin2 COLLATE latin2_bin +ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DEFAULT COLLATE = latin2_general_ci; +ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +DROP TABLE t1; + SET GLOBAL innodb_purge_rseg_truncate_frequency=@save_frequency; diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.test b/mysql-test/suite/innodb/t/instant_alter_debug.test index 267e2a2c22d..22452c78f4d 100644 --- a/mysql-test/suite/innodb/t/instant_alter_debug.test +++ b/mysql-test/suite/innodb/t/instant_alter_debug.test @@ -362,3 +362,29 @@ FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; + +--echo # +--echo # MDEV-21045 AddressSanitizer: use-after-poison in mem_heap_dup / row_log_table_get_pk_col +--echo # +CREATE TABLE t1 (a TEXT) ENGINE = InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 (a) VALUES ('foo'); + +ALTER TABLE t1 ADD COLUMN b INT DEFAULT 0,algorithm=instant; + +--connect (con2,localhost,root,,test) +SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL onlinealter WAIT_FOR update'; +--send +ALTER TABLE t1 ADD PRIMARY KEY (b); + +--connection default +SET DEBUG_SYNC='now WAIT_FOR onlinealter'; +UPDATE t1 SET b = 1; +SET DEBUG_SYNC='now SIGNAL update'; + +--connection con2 +--reap + +--connection default +SET DEBUG_SYNC='RESET'; +--disconnect con2 +DROP TABLE t1; diff --git a/mysql-test/suite/versioning/r/delete.result b/mysql-test/suite/versioning/r/delete.result index 77b7fc80286..26ade83acd7 100644 --- a/mysql-test/suite/versioning/r/delete.result +++ b/mysql-test/suite/versioning/r/delete.result @@ -1,3 +1,4 @@ +# Basic + delete from view create or replace table t1( XNo int unsigned, sys_start SYS_DATATYPE as row start invisible, @@ -44,6 +45,7 @@ XNo_vt1 5 drop view vt1; drop table t1; +# Check sys_start, sys_end create or replace table t1( x int, sys_start SYS_DATATYPE as row start invisible, @@ -59,6 +61,7 @@ select x = 1 as A, sys_start = @sys_start as B, sys_end > sys_start as C from t1 A B C 1 1 1 drop table t1; +# Multi-delete create or replace table t1( x int, y int, @@ -103,9 +106,6 @@ t2_x_all 14 drop table t1; drop table t2; -# Basic + delete from view -# Check sys_start, sys_end -# Multi-delete # Update + delete create or replace table t1 (x int) with system versioning; insert into t1 values (1); diff --git a/mysql-test/suite/versioning/r/truncate.result b/mysql-test/suite/versioning/r/delete_history.result index 46ef271db3d..46ef271db3d 100644 --- a/mysql-test/suite/versioning/r/truncate.result +++ b/mysql-test/suite/versioning/r/delete_history.result diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index c868a740927..a286cdf23be 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -566,3 +566,20 @@ pn SYSTEM_TIME CURRENT # Test cleanup drop view v1; drop tables t, t1, t2, t3, t4; +# +# MDEV-18957 UPDATE with LIMIT clause is wrong for versioned partitioned tables +# +create or replace table t1 ( +x int, +a varchar(255) +) with system versioning partition by system_time (partition p1 history, partition pn current); +insert into t1 (x) values (1), (2), (3), (4); +update t1 set a= 'foo' limit 3; +update t1 set a= 'bar' limit 4; +select * from t1; +x a +1 bar +2 bar +3 bar +4 bar +drop table t1; diff --git a/mysql-test/suite/versioning/r/view.result b/mysql-test/suite/versioning/r/view.result index 2c0df212ce8..b33602f7336 100644 --- a/mysql-test/suite/versioning/r/view.result +++ b/mysql-test/suite/versioning/r/view.result @@ -64,13 +64,13 @@ select * from vt1; x 1 2 -# VIEW with parameters [#151] +# VIEW with parameters [tempesta-tech/mariadb#151] create or replace table t1 (x int) with system versioning; create or replace view vt1(c) as select x from t1; show create view vt1; View Create View character_set_client collation_connection vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`x` AS `c` from `t1` latin1 latin1_swedish_ci -# VIEW over JOIN of versioned tables [#153] +# VIEW over JOIN of versioned tables [tempesta-tech/mariadb#153] create or replace table t1 (a int) with system versioning; create or replace table t2 (b int) with system versioning; insert into t1 values (1); @@ -82,7 +82,7 @@ a b create or replace view vt12 as select * from t1 for system_time as of timestamp ('0-0-0') cross join t2; select * from vt12; a b -# VIEW improvements [#183] +# VIEW improvements [tempesta-tech/mariadb#183] create or replace table t3 (x int); create or replace view vt1 as select * from t1, t2, t3; show create view vt1; @@ -96,12 +96,12 @@ create or replace view vt1 as select a, t2.row_end as endo from t3, t1, t2; show create view vt1; View Create View character_set_client collation_connection vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`row_end` AS `endo` from ((`t3` join `t1`) join `t2`) latin1 latin1_swedish_ci -# VIEW over UNION [#269] +# VIEW over UNION [tempesta-tech/mariadb#269] create or replace view vt1 as select * from t1 union select * from t1; select * from vt1; a 1 -# VIEW over UNION with non-versioned [#393] +# VIEW over UNION with non-versioned [tempesta-tech/mariadb#393] create or replace table t2 (a int); create or replace view vt1 as select * from t1 union select * from t2; select * from vt1; @@ -123,10 +123,10 @@ drop tables t1, t2; # # MDEV-15146 SQLError[4122]: View is not system versioned # -create table t1 (a int) with system versioning; +create or replace table t1 (a int) with system versioning; insert t1 values (1),(2); set @a=now(6); -create view v1 as select * from t1; +create or replace view v1 as select * from t1; delete from t1; select * from v1; a @@ -149,3 +149,67 @@ View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` FOR SYSTEM_TIME AS OF TIMESTAMP current_timestamp() - interval 6 second latin1 latin1_swedish_ci drop view v1, vt1, vt12; drop tables t1, t3; +# +# MDEV-18727 improve DML operation of System Versioning +# +create or replace table t1 ( +x int, +row_start SYS_DATATYPE as row start invisible, +row_end SYS_DATATYPE as row end invisible, +period for system_time (row_start, row_end) +) with system versioning; +insert into t1 values (1), (2); +create or replace view v1 as select * from t1 where x > 1; +update v1 set x= x + 1; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +x check_row(row_start, row_end) +1 CURRENT ROW +2 HISTORICAL ROW +3 CURRENT ROW +insert v1 values (4); +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +x check_row(row_start, row_end) +1 CURRENT ROW +2 HISTORICAL ROW +3 CURRENT ROW +4 CURRENT ROW +delete from v1 where x < 4; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +x check_row(row_start, row_end) +1 CURRENT ROW +2 HISTORICAL ROW +3 HISTORICAL ROW +4 CURRENT ROW +# multi-update +create or replace table t2 like t1; +insert into t2 values (1), (2); +create or replace view v2 as select * from t2 where x > 1; +update v1, v2 set v1.x= v1.x + 1, v2.x= v2.x + 1 where v1.x = v2.x + 2; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +x check_row(row_start, row_end) +1 CURRENT ROW +2 HISTORICAL ROW +3 HISTORICAL ROW +4 HISTORICAL ROW +5 CURRENT ROW +select *, check_row(row_start, row_end) from t2 for system_time all order by x; +x check_row(row_start, row_end) +1 CURRENT ROW +2 HISTORICAL ROW +3 CURRENT ROW +# multi-delete +delete v1, v2 from v1 join v2 where v1.x = v2.x + 2; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +x check_row(row_start, row_end) +1 CURRENT ROW +2 HISTORICAL ROW +3 HISTORICAL ROW +4 HISTORICAL ROW +5 HISTORICAL ROW +select *, check_row(row_start, row_end) from t2 for system_time all order by x; +x check_row(row_start, row_end) +1 CURRENT ROW +2 HISTORICAL ROW +3 HISTORICAL ROW +drop view v1, v2; +drop tables t1, t2; diff --git a/mysql-test/suite/versioning/t/delete.test b/mysql-test/suite/versioning/t/delete.test index b9045898bb0..4f1ba4b1d8e 100644 --- a/mysql-test/suite/versioning/t/delete.test +++ b/mysql-test/suite/versioning/t/delete.test @@ -1,6 +1,7 @@ source suite/versioning/engines.inc; source suite/versioning/common.inc; +--echo # Basic + delete from view replace_result $sys_datatype_expl SYS_DATATYPE; eval create or replace table t1( XNo int unsigned, @@ -31,7 +32,7 @@ select XNo as XNo_vt1 from vt1; drop view vt1; drop table t1; - +--echo # Check sys_start, sys_end replace_result $sys_datatype_expl SYS_DATATYPE; eval create or replace table t1( x int, @@ -47,6 +48,7 @@ select * from t1; select x = 1 as A, sys_start = @sys_start as B, sys_end > sys_start as C from t1 for system_time all; drop table t1; +--echo # Multi-delete replace_result $sys_datatype_expl SYS_DATATYPE; eval create or replace table t1( x int, @@ -69,12 +71,6 @@ select x as t2_x_all from t2 for system_time all; drop table t1; drop table t2; ---echo # Basic + delete from view - ---echo # Check sys_start, sys_end - ---echo # Multi-delete - --echo # Update + delete create or replace table t1 (x int) with system versioning; insert into t1 values (1); diff --git a/mysql-test/suite/versioning/t/truncate.test b/mysql-test/suite/versioning/t/delete_history.test index 07fb4eb187a..07fb4eb187a 100644 --- a/mysql-test/suite/versioning/t/truncate.test +++ b/mysql-test/suite/versioning/t/delete_history.test diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index c8c06b2858e..64c4bc247c0 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -487,4 +487,18 @@ select PARTITION_NAME, PARTITION_METHOD, PARTITION_DESCRIPTION from information_ drop view v1; drop tables t, t1, t2, t3, t4; +--echo # +--echo # MDEV-18957 UPDATE with LIMIT clause is wrong for versioned partitioned tables +--echo # +create or replace table t1 ( + x int, + a varchar(255) +) with system versioning partition by system_time (partition p1 history, partition pn current); + +insert into t1 (x) values (1), (2), (3), (4); +update t1 set a= 'foo' limit 3; +update t1 set a= 'bar' limit 4; +select * from t1; +drop table t1; + --source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/view.test b/mysql-test/suite/versioning/t/view.test index 5a03a50f1d3..c05fbfd3866 100644 --- a/mysql-test/suite/versioning/t/view.test +++ b/mysql-test/suite/versioning/t/view.test @@ -52,13 +52,13 @@ prepare stmt from @tmp; execute stmt; drop prepare stmt; select * from vt1; ---echo # VIEW with parameters [#151] +--echo # VIEW with parameters [tempesta-tech/mariadb#151] create or replace table t1 (x int) with system versioning; create or replace view vt1(c) as select x from t1; --replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT show create view vt1; ---echo # VIEW over JOIN of versioned tables [#153] +--echo # VIEW over JOIN of versioned tables [tempesta-tech/mariadb#153] create or replace table t1 (a int) with system versioning; create or replace table t2 (b int) with system versioning; insert into t1 values (1); @@ -68,7 +68,7 @@ select * from vt12; create or replace view vt12 as select * from t1 for system_time as of timestamp ('0-0-0') cross join t2; select * from vt12; ---echo # VIEW improvements [#183] +--echo # VIEW improvements [tempesta-tech/mariadb#183] create or replace table t3 (x int); create or replace view vt1 as select * from t1, t2, t3; --replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT @@ -80,11 +80,11 @@ create or replace view vt1 as select a, t2.row_end as endo from t3, t1, t2; --replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT show create view vt1; ---echo # VIEW over UNION [#269] +--echo # VIEW over UNION [tempesta-tech/mariadb#269] create or replace view vt1 as select * from t1 union select * from t1; select * from vt1; ---echo # VIEW over UNION with non-versioned [#393] +--echo # VIEW over UNION with non-versioned [tempesta-tech/mariadb#393] create or replace table t2 (a int); create or replace view vt1 as select * from t1 union select * from t2; select * from vt1; @@ -104,10 +104,10 @@ drop tables t1, t2; --echo # --echo # MDEV-15146 SQLError[4122]: View is not system versioned --echo # -create table t1 (a int) with system versioning; +create or replace table t1 (a int) with system versioning; insert t1 values (1),(2); set @a=now(6); -create view v1 as select * from t1; +create or replace view v1 as select * from t1; delete from t1; select * from v1; select * from v1 for system_time as of @a; @@ -124,4 +124,37 @@ show create view v1; drop view v1, vt1, vt12; drop tables t1, t3; +--echo # +--echo # MDEV-18727 improve DML operation of System Versioning +--echo # +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t1 ( + x int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end) +) with system versioning; +insert into t1 values (1), (2); +create or replace view v1 as select * from t1 where x > 1; +update v1 set x= x + 1; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +insert v1 values (4); +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +delete from v1 where x < 4; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +--echo # multi-update +create or replace table t2 like t1; +insert into t2 values (1), (2); +create or replace view v2 as select * from t2 where x > 1; +update v1, v2 set v1.x= v1.x + 1, v2.x= v2.x + 1 where v1.x = v2.x + 2; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +select *, check_row(row_start, row_end) from t2 for system_time all order by x; +--echo # multi-delete +delete v1, v2 from v1 join v2 where v1.x = v2.x + 2; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +select *, check_row(row_start, row_end) from t2 for system_time all order by x; + +drop view v1, v2; +drop tables t1, t2; + --source suite/versioning/common_finish.inc diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index e4ab4cb68f5..d40913c21d1 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -4539,7 +4539,7 @@ int ha_partition::delete_row(const uchar *buf) or last historical partition, but DELETE HISTORY can delete from any historical partition. So, skip the check in this case. */ - if (!thd->lex->vers_conditions.is_set()) // if not DELETE HISTORY + if (!thd->lex->vers_conditions.delete_history) { uint32 part_id; error= get_part_for_buf(buf, m_rec0, m_part_info, &part_id); diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index ea6a9910e1f..2d3ea388cc5 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -125,7 +125,7 @@ void Item_subselect::init(st_select_lex *select_lex, NO_MATTER : outer_select->parsing_place); if (unit->is_unit_op() && - (unit->first_select()->next_select() or unit->fake_select_lex)) + (unit->first_select()->next_select() || unit->fake_select_lex)) engine= new subselect_union_engine(unit, result, this); else engine= new subselect_single_select_engine(select_lex, result, this); diff --git a/sql/mysqld.h b/sql/mysqld.h index a518b6f34cd..eb92b5999ca 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -185,7 +185,8 @@ enum vers_system_time_t SYSTEM_TIME_AS_OF, SYSTEM_TIME_FROM_TO, SYSTEM_TIME_BETWEEN, - SYSTEM_TIME_BEFORE, + SYSTEM_TIME_BEFORE, // used for DELETE HISTORY ... BEFORE + SYSTEM_TIME_HISTORY, // used for DELETE HISTORY SYSTEM_TIME_ALL }; diff --git a/sql/service_wsrep.cc b/sql/service_wsrep.cc index 204d671d53b..b24f3cb511a 100644 --- a/sql/service_wsrep.cc +++ b/sql/service_wsrep.cc @@ -112,7 +112,17 @@ extern "C" my_bool wsrep_get_debug() extern "C" my_bool wsrep_thd_is_local(const THD *thd) { - return thd->wsrep_cs().mode() == wsrep::client_state::m_local; + /* + async replication IO and background threads have nothing to replicate in the cluster, + marking them as non-local here to prevent write set population and replication + + async replication SQL thread, applies client transactions from mariadb master + and will be replicated into cluster + */ + return ( + thd->system_thread != SYSTEM_THREAD_SLAVE_BACKGROUND && + thd->system_thread != SYSTEM_THREAD_SLAVE_IO && + thd->wsrep_cs().mode() == wsrep::client_state::m_local); } extern "C" my_bool wsrep_thd_is_applying(const THD *thd) diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index b12312041bd..29d6904f7f9 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -214,19 +214,11 @@ bool Update_plan::save_explain_data_intern(MEM_ROOT *mem_root, static bool record_should_be_deleted(THD *thd, TABLE *table, SQL_SELECT *sel, Explain_delete *explain, bool truncate_history) { - bool check_delete= true; - - if (table->versioned()) - { - bool historical= !table->vers_end_field()->is_max(); - check_delete= truncate_history ? historical : !historical; - } - explain->tracker.on_record_read(); thd->inc_examined_row_count(1); if (table->vfield) (void) table->update_virtual_fields(table->file, VCOL_UPDATE_FOR_DELETE); - if (check_delete && (!sel || sel->skip_record(thd) > 0)) + if (!sel || sel->skip_record(thd) > 0) { explain->tracker.on_record_after_where(); return true; @@ -340,30 +332,8 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, THD_STAGE_INFO(thd, stage_init_update); - bool delete_history= table_list->vers_conditions.is_set(); - if (delete_history) - { - DBUG_ASSERT(!table_list->period_conditions.is_set()); - - if (table_list->is_view_or_derived()) - { - my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); - DBUG_RETURN(true); - } - - DBUG_ASSERT(table_list->table); - DBUG_ASSERT(!conds || thd->stmt_arena->is_stmt_execute()); - - // conds could be cached from previous SP call - if (!conds) - { - if (select_lex->vers_setup_conds(thd, table_list)) - DBUG_RETURN(TRUE); - - conds= table_list->on_expr; - table_list->on_expr= NULL; - } - } + const bool delete_history= table_list->vers_conditions.delete_history; + DBUG_ASSERT(!(delete_history && table_list->period_conditions.is_set())); if (thd->lex->handle_list_of_derived(table_list, DT_MERGE_FOR_INSERT)) DBUG_RETURN(TRUE); @@ -1015,15 +985,11 @@ int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds, select_lex->leaf_tables, FALSE, DELETE_ACL, SELECT_ACL, TRUE)) DBUG_RETURN(TRUE); - if (table_list->vers_conditions.is_set()) + + if (table_list->vers_conditions.is_set() && table_list->is_view_or_derived()) { - if (table_list->is_view()) - { - my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); - DBUG_RETURN(true); - } - if (select_lex->vers_setup_conds(thd, table_list)) - DBUG_RETURN(true); + my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); + DBUG_RETURN(true); } if (table_list->has_period()) @@ -1034,11 +1000,19 @@ int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds, DBUG_RETURN(true); } - *conds= select_lex->period_setup_conds(thd, table_list, *conds); - if (!*conds) + if (select_lex->period_setup_conds(thd, table_list)) DBUG_RETURN(true); } + DBUG_ASSERT(table_list->table); + // conds could be cached from previous SP call + DBUG_ASSERT(!table_list->vers_conditions.is_set() || + !*conds || thd->stmt_arena->is_stmt_execute()); + if (select_lex->vers_setup_conds(thd, table_list)) + DBUG_RETURN(TRUE); + + *conds= select_lex->where; + if (setup_returning_fields(thd, table_list) || setup_conds(thd, table_list, select_lex->leaf_tables, conds) || setup_ftfuncs(select_lex)) @@ -1332,11 +1306,6 @@ int multi_delete::send_data(List<Item> &values) if (table->status & (STATUS_NULL_ROW | STATUS_DELETED)) continue; - if (table->versioned() && !table->vers_end_field()->is_max()) - { - continue; - } - table->file->position(table->record[0]); found++; diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index f4eaa43e8ab..09f8acc536c 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -723,7 +723,17 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) !(derived->is_multitable() && (thd->lex->sql_command == SQLCOM_UPDATE_MULTI || thd->lex->sql_command == SQLCOM_DELETE_MULTI)))) + { + /* + System versioned tables may still require to get versioning conditions + (when updating view). See vers_setup_conds(). + */ + if (!unit->prepared && + derived->table->versioned() && + (res= unit->prepare(derived, derived->derived_result, 0))) + goto exit; DBUG_RETURN(FALSE); + } /* prevent name resolving out of derived table */ for (SELECT_LEX *sl= first_select; sl; sl= sl->next_select()) diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 0c3108e8ebe..ad041f20275 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1316,7 +1316,7 @@ public: /* push new Item_field into item_list */ bool vers_push_field(THD *thd, TABLE_LIST *table, const LEX_CSTRING field_name); - Item* period_setup_conds(THD *thd, TABLE_LIST *table, Item *where); + int period_setup_conds(THD *thd, TABLE_LIST *table); void init_query(); void init_select(); st_select_lex_unit* master_unit() { return (st_select_lex_unit*) master; } diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 62cf4674241..7f33f288ce3 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -4789,8 +4789,10 @@ mysql_execute_command(THD *thd) { result= new (thd->mem_root) multi_delete(thd, aux_tables, lex->table_count); - if (unlikely(result)) + if (likely(result)) { + if (unlikely(select_lex->vers_setup_conds(thd, aux_tables))) + goto multi_delete_error; res= mysql_select(thd, select_lex->get_table_list(), select_lex->item_list, @@ -4811,6 +4813,7 @@ mysql_execute_command(THD *thd) if (lex->describe || lex->analyze_stmt) res= thd->lex->explain->send_explain(thd); } + multi_delete_error: delete result; } } @@ -9683,7 +9686,7 @@ bool update_precheck(THD *thd, TABLE_LIST *tables) bool delete_precheck(THD *thd, TABLE_LIST *tables) { DBUG_ENTER("delete_precheck"); - if (tables->vers_conditions.is_set()) + if (tables->vers_conditions.delete_history) { if (check_one_table_access(thd, DELETE_HISTORY_ACL, tables)) DBUG_RETURN(TRUE); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3b55179f0b3..e925715e20d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -723,6 +723,7 @@ bool vers_select_conds_t::init_from_sysvar(THD *thd) { vers_asof_timestamp_t &in= thd->variables.vers_asof_timestamp; type= (vers_system_time_t) in.type; + delete_history= false; start.unit= VERS_TIMESTAMP; if (type != SYSTEM_TIME_UNSPECIFIED && type != SYSTEM_TIME_ALL) { @@ -755,6 +756,7 @@ void vers_select_conds_t::print(String *str, enum_query_type query_type) const end.print(str, query_type, STRING_WITH_LEN(" AND ")); break; case SYSTEM_TIME_BEFORE: + case SYSTEM_TIME_HISTORY: DBUG_ASSERT(0); break; case SYSTEM_TIME_ALL: @@ -790,10 +792,15 @@ Item* period_get_condition(THD *thd, TABLE_LIST *table, SELECT_LEX *select, switch (conds->type) { case SYSTEM_TIME_UNSPECIFIED: + case SYSTEM_TIME_HISTORY: thd->variables.time_zone->gmt_sec_to_TIME(&max_time, TIMESTAMP_MAX_VALUE); max_time.second_part= TIME_MAX_SECOND_PART; curr= newx Item_datetime_literal(thd, &max_time, TIME_SECOND_PART_DIGITS); - cond1= newx Item_func_eq(thd, conds->field_end, curr); + if (conds->type == SYSTEM_TIME_UNSPECIFIED) + cond1= newx Item_func_eq(thd, conds->field_end, curr); + else + cond1= newx Item_func_lt(thd, conds->field_end, curr); + break; break; case SYSTEM_TIME_AS_OF: cond1= newx Item_func_le(thd, conds->field_start, conds->start.item); @@ -837,8 +844,13 @@ Item* period_get_condition(THD *thd, TABLE_LIST *table, SELECT_LEX *select, switch (conds->type) { case SYSTEM_TIME_UNSPECIFIED: + case SYSTEM_TIME_HISTORY: curr= newx Item_int(thd, ULONGLONG_MAX); - cond1= newx Item_func_eq(thd, conds->field_end, curr); + if (conds->type == SYSTEM_TIME_UNSPECIFIED) + cond1= newx Item_func_eq(thd, conds->field_end, curr); + else + cond1= newx Item_func_lt(thd, conds->field_end, curr); + break; DBUG_ASSERT(!conds->start.item); DBUG_ASSERT(!conds->end.item); break; @@ -881,12 +893,12 @@ bool skip_setup_conds(THD *thd) || thd->lex->is_view_context_analysis(); } -Item* SELECT_LEX::period_setup_conds(THD *thd, TABLE_LIST *tables, Item *where) +int SELECT_LEX::period_setup_conds(THD *thd, TABLE_LIST *tables) { DBUG_ENTER("SELECT_LEX::period_setup_conds"); if (skip_setup_conds(thd)) - DBUG_RETURN(where); + DBUG_RETURN(0); Query_arena backup; Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); @@ -904,19 +916,19 @@ Item* SELECT_LEX::period_setup_conds(THD *thd, TABLE_LIST *tables, Item *where) my_error(ER_PERIOD_NOT_FOUND, MYF(0), conds.name.str); if (arena) thd->restore_active_arena(arena, &backup); - DBUG_RETURN(NULL); + DBUG_RETURN(-1); } conds.period= &table->table->s->period; result= and_items(thd, result, period_get_condition(thd, table, this, &conds, true)); } - result= and_items(thd, where, result); + where= and_items(thd, where, result); if (arena) thd->restore_active_arena(arena, &backup); - DBUG_RETURN(result); + DBUG_RETURN(0); } int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables) @@ -968,9 +980,22 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables) } } + bool is_select= false; + switch (thd->lex->sql_command) + { + case SQLCOM_SELECT: + case SQLCOM_INSERT_SELECT: + case SQLCOM_REPLACE_SELECT: + case SQLCOM_DELETE_MULTI: + case SQLCOM_UPDATE_MULTI: + is_select= true; + default: + break; + } + for (TABLE_LIST *table= tables; table; table= table->next_local) { - if (!table->table || !table->table->versioned()) + if (!table->table || table->is_view() || !table->table->versioned()) continue; vers_select_conds_t &vers_conditions= table->vers_conditions; @@ -1000,7 +1025,7 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables) } // propagate system_time from sysvar - if (!vers_conditions.is_set()) + if (!vers_conditions.is_set() && is_select) { if (vers_conditions.init_from_sysvar(thd)) DBUG_RETURN(-1); @@ -1016,7 +1041,7 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables) bool timestamps_only= table->table->versioned(VERS_TIMESTAMP); - if (vers_conditions.is_set()) + if (vers_conditions.is_set() && vers_conditions.type != SYSTEM_TIME_HISTORY) { thd->where= "FOR SYSTEM_TIME"; /* TODO: do resolve fix_length_and_dec(), fix_fields(). This requires @@ -1036,10 +1061,21 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables) vers_conditions.period = &table->table->s->vers; Item *cond= period_get_condition(thd, table, this, &vers_conditions, timestamps_only); - if (cond) + if (is_select) table->on_expr= and_items(thd, table->on_expr, cond); - table->vers_conditions.type= SYSTEM_TIME_ALL; + else + { + if (join) + { + where= and_items(thd, join->conds, cond); + join->conds= where; + } + else + where= and_items(thd, where, cond); + table->where= and_items(thd, table->where, cond); + } + table->vers_conditions.type= SYSTEM_TIME_ALL; } // for (table= tables; ...) DBUG_RETURN(0); @@ -21209,11 +21245,12 @@ int join_init_read_record(JOIN_TAB *tab) */ if (tab->distinct && tab->remove_duplicates()) // Remove duplicates. return 1; - if (tab->filesort && tab->sort_table()) // Sort table. - return 1; tab->build_range_rowid_filter_if_needed(); + if (tab->filesort && tab->sort_table()) // Sort table. + return 1; + DBUG_EXECUTE_IF("kill_join_init_read_record", tab->join->thd->set_killed(KILL_QUERY);); if (tab->select && tab->select->quick && tab->select->quick->reset()) @@ -21273,6 +21310,9 @@ JOIN_TAB::sort_table() JOIN::ordered_index_order_by : JOIN::ordered_index_group_by)); rc= create_sort_index(join->thd, join, this, NULL); + /* Disactivate rowid filter if it was used when creating sort index */ + if (rowid_filter) + table->file->rowid_filter_is_active= false; return (rc != 0); } diff --git a/sql/sql_string.h b/sql/sql_string.h index 8ced35657e8..317adc13909 100644 --- a/sql/sql_string.h +++ b/sql/sql_string.h @@ -3,7 +3,7 @@ /* Copyright (c) 2000, 2013, Oracle and/or its affiliates. - Copyright (c) 2008, 2018, MariaDB Corporation. + Copyright (c) 2008, 2019, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -169,6 +169,10 @@ public: { swap_variables(CHARSET_INFO*, m_charset, other.m_charset); } + bool same_encoding(const Charset &other) const + { + return !strcmp(m_charset->csname, other.m_charset->csname); + } /* Collation name without the character set name. For example, in case of "latin1_swedish_ci", diff --git a/sql/sql_union.cc b/sql/sql_union.cc index ece5a84b1c3..d5d43e56875 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1459,9 +1459,21 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, if (sl->tvc->prepare(thd, sl, tmp_result, this)) goto err; } - else if (prepare_join(thd, first_sl, tmp_result, additional_options, + else + { + if (prepare_join(thd, first_sl, tmp_result, additional_options, is_union_select)) - goto err; + goto err; + + if (derived_arg && derived_arg->table && + derived_arg->derived_type == VIEW_ALGORITHM_MERGE && + derived_arg->table->versioned()) + { + /* Got versioning conditions (see vers_setup_conds()), need to update + derived_arg. */ + derived_arg->where= first_sl->where; + } + } types= first_sl->item_list; goto cont; } diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 26dfe5e40a2..739febf5e87 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -959,11 +959,6 @@ update_begin: THD_STAGE_INFO(thd, stage_updating); while (!(error=info.read_record()) && !thd->killed) { - if (table->versioned() && !table->vers_end_field()->is_max()) - { - continue; - } - explain->tracker.on_record_read(); thd->inc_examined_row_count(1); if (!select || select->skip_record(thd) > 0) @@ -1372,12 +1367,18 @@ bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list, thd->lex->allow_sum_func.clear_all(); - if (table_list->has_period()) - { - *conds= select_lex->period_setup_conds(thd, table_list, *conds); - if (!*conds) + if (table_list->has_period() && + select_lex->period_setup_conds(thd, table_list)) DBUG_RETURN(true); - } + + DBUG_ASSERT(table_list->table); + // conds could be cached from previous SP call + DBUG_ASSERT(!table_list->vers_conditions.is_set() || + !*conds || thd->stmt_arena->is_stmt_execute()); + if (select_lex->vers_setup_conds(thd, table_list)) + DBUG_RETURN(TRUE); + + *conds= select_lex->where; /* We do not call DT_MERGE_FOR_INSERT because it has no sense for simple @@ -1899,6 +1900,9 @@ bool mysql_multi_update(THD *thd, TABLE_LIST *table_list, List<Item> *fields, thd->abort_on_warning= !ignore && thd->is_strict_mode(); List<Item> total_list; + if (select_lex->vers_setup_conds(thd, table_list)) + DBUG_RETURN(1); + res= mysql_select(thd, table_list, total_list, conds, select_lex->order_list.elements, @@ -2458,11 +2462,6 @@ int multi_update::send_data(List<Item> ¬_used_values) if (table->status & (STATUS_NULL_ROW | STATUS_UPDATED)) continue; - if (table->versioned() && !table->vers_end_field()->is_max()) - { - continue; - } - if (table == table_to_update) { /* diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 2657586cc42..fee02f78792 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -13030,7 +13030,7 @@ delete: opt_delete_system_time: /* empty */ { - Lex->vers_conditions.init(SYSTEM_TIME_ALL); + Lex->vers_conditions.init(SYSTEM_TIME_HISTORY); } | BEFORE_SYM SYSTEM_TIME_SYM history_point { diff --git a/sql/table.cc b/sql/table.cc index 7ed5121a9c6..2b1bb16ef24 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -9647,6 +9647,8 @@ bool vers_select_conds_t::eq(const vers_select_conds_t &conds) const return true; case SYSTEM_TIME_BEFORE: break; + case SYSTEM_TIME_HISTORY: + break; case SYSTEM_TIME_AS_OF: return start.eq(conds.start); case SYSTEM_TIME_FROM_TO: diff --git a/sql/table.h b/sql/table.h index 043341db608..0c6917e7b62 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1859,6 +1859,7 @@ struct vers_select_conds_t { vers_system_time_t type; bool used:1; + bool delete_history:1; Vers_history_point start; Vers_history_point end; Lex_ident name; @@ -1872,6 +1873,7 @@ struct vers_select_conds_t { type= SYSTEM_TIME_UNSPECIFIED; used= false; + delete_history= false; start.empty(); end.empty(); } @@ -1883,6 +1885,8 @@ struct vers_select_conds_t { type= _type; used= false; + delete_history= (type == SYSTEM_TIME_HISTORY || + type == SYSTEM_TIME_BEFORE); start= _start; end= _end; name= _name; diff --git a/storage/federatedx/ha_federatedx.cc b/storage/federatedx/ha_federatedx.cc index 7db6fe9d541..261074fa98d 100644 --- a/storage/federatedx/ha_federatedx.cc +++ b/storage/federatedx/ha_federatedx.cc @@ -806,12 +806,12 @@ static int parse_url(MEM_ROOT *mem_root, FEDERATEDX_SHARE *share, goto error; if (share->hostname[0] == '\0') - share->hostname= NULL; + share->hostname= strdup_root(mem_root, my_localhost); } if (!share->port) { - if (!share->hostname || strcmp(share->hostname, my_localhost) == 0) + if (0 == strcmp(share->hostname, my_localhost)) share->socket= (char *) MYSQL_UNIX_ADDR; else share->port= MYSQL_PORT; @@ -3396,8 +3396,7 @@ int ha_federatedx::create(const char *name, TABLE *table_arg, goto error; /* loopback socket connections hang due to LOCK_open mutex */ - if ((!tmp_share.hostname || !strcmp(tmp_share.hostname,my_localhost)) && - !tmp_share.port) + if (0 == strcmp(tmp_share.hostname, my_localhost) && !tmp_share.port) goto error; /* diff --git a/storage/innobase/dict/dict0mem.cc b/storage/innobase/dict/dict0mem.cc index 9e67f19e8b3..7d3f33d1ae2 100644 --- a/storage/innobase/dict/dict0mem.cc +++ b/storage/innobase/dict/dict0mem.cc @@ -37,6 +37,7 @@ Created 1/8/1996 Heikki Tuuri #include "lock0lock.h" #include "sync0sync.h" #include "row0row.h" +#include "sql_string.h" #include <iostream> #define DICT_HEAP_SIZE 100 /*!< initial memory heap size when @@ -115,6 +116,14 @@ operator<<( return(s << ut_get_name(NULL, table_name.m_name)); } +bool dict_col_t::same_encoding(uint16_t a, uint16_t b) +{ + if (const CHARSET_INFO *acs= get_charset(a, MYF(MY_WME))) + if (const CHARSET_INFO *bcs= get_charset(b, MYF(MY_WME))) + return Charset(acs).same_encoding(bcs); + return false; +} + /**********************************************************************//** Creates a table memory object. @return own: table object */ diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 1f2b818bc12..2a0e8e0a805 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -7970,6 +7970,7 @@ report_error: if (!error_result && wsrep_on(m_user_thd) && wsrep_thd_is_local(m_user_thd) + && !wsrep_thd_ignore_table(m_user_thd) && !wsrep_consistency_check(m_user_thd) && (thd_sql_command(m_user_thd) != SQLCOM_CREATE_TABLE) && (thd_sql_command(m_user_thd) != SQLCOM_LOAD || @@ -8680,7 +8681,8 @@ func_exit: #ifdef WITH_WSREP if (error == DB_SUCCESS && wsrep_on(m_user_thd) - && wsrep_thd_is_local(m_user_thd)) { + && wsrep_thd_is_local(m_user_thd) + && !wsrep_thd_ignore_table(m_user_thd)) { DBUG_PRINT("wsrep", ("update row key")); diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index dbeb4ce9e60..5f5454deb93 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -539,8 +539,9 @@ inline bool dict_table_t::instant_column(const dict_table_t& table, c.def_val = o->def_val; DBUG_ASSERT(!((c.prtype ^ o->prtype) & ~(DATA_NOT_NULL | DATA_VERSIONED + | CHAR_COLL_MASK << 16 | DATA_LONG_TRUE_VARCHAR))); - DBUG_ASSERT(c.mtype == o->mtype); + DBUG_ASSERT(c.same_type(*o)); DBUG_ASSERT(c.len >= o->len); if (o->vers_sys_start()) { diff --git a/storage/innobase/include/data0type.h b/storage/innobase/include/data0type.h index 7168c104b1c..0e496085113 100644 --- a/storage/innobase/include/data0type.h +++ b/storage/innobase/include/data0type.h @@ -334,14 +334,15 @@ dtype_get_mblen( multi-byte character */ ulint* mbmaxlen); /*!< out: maximum length of a multi-byte character */ -/*********************************************************************//** -Gets the MySQL charset-collation code for MySQL string types. -@return MySQL charset-collation code */ -UNIV_INLINE -ulint -dtype_get_charset_coll( -/*===================*/ - ulint prtype);/*!< in: precise data type */ +/** +Get the charset-collation code for string types. +@param prtype InnoDB precise type +@return charset-collation code */ +inline uint16_t dtype_get_charset_coll(ulint prtype) +{ + return static_cast<uint16_t>(prtype >> 16) & CHAR_COLL_MASK; +} + /** Form a precise type from the < 4.1.2 format precise type plus the charset-collation code. @param[in] old_prtype MySQL type code and the flags diff --git a/storage/innobase/include/data0type.ic b/storage/innobase/include/data0type.ic index f2c499716ce..037a71a9345 100644 --- a/storage/innobase/include/data0type.ic +++ b/storage/innobase/include/data0type.ic @@ -28,18 +28,6 @@ Created 1/16/1996 Heikki Tuuri #include "ha_prototypes.h" /*********************************************************************//** -Gets the MySQL charset-collation code for MySQL string types. -@return MySQL charset-collation code */ -UNIV_INLINE -ulint -dtype_get_charset_coll( -/*===================*/ - ulint prtype) /*!< in: precise data type */ -{ - return((prtype >> 16) & CHAR_COLL_MASK); -} - -/*********************************************************************//** Determines if a MySQL string type is a subset of UTF-8. This function may return false negatives, in case further character-set collation codes are introduced in MySQL later. diff --git a/storage/innobase/include/dict0mem.h b/storage/innobase/include/dict0mem.h index e2a42519d99..1f419061b8a 100644 --- a/storage/innobase/include/dict0mem.h +++ b/storage/innobase/include/dict0mem.h @@ -674,18 +674,63 @@ public: def_val.data = NULL; } + /** @return whether two columns have compatible data type encoding */ + bool same_type(const dict_col_t &other) const + { + if (mtype != other.mtype) + { + /* For latin1_swedish_ci, DATA_CHAR and DATA_VARCHAR + will be used instead of DATA_MYSQL and DATA_VARMYSQL. + As long as mtype,prtype are being written to InnoDB + data dictionary tables, we cannot simplify this. */ + switch (mtype) { + default: + return false; + case DATA_VARCHAR: + if (other.mtype != DATA_VARMYSQL) + return false; + goto check_encoding; + case DATA_VARMYSQL: + if (other.mtype != DATA_VARCHAR) + return false; + goto check_encoding; + case DATA_CHAR: + if (other.mtype != DATA_MYSQL) + return false; + goto check_encoding; + case DATA_MYSQL: + if (other.mtype != DATA_CHAR) + return false; + goto check_encoding; + } + } + else if (dtype_is_string_type(mtype)) + { + check_encoding: + const uint16_t cset= dtype_get_charset_coll(prtype); + const uint16_t ocset= dtype_get_charset_coll(other.prtype); + return cset == ocset || dict_col_t::same_encoding(cset, ocset); + } + + return true; + } + + /** @return whether two collations codes have the same character encoding */ + static bool same_encoding(uint16_t a, uint16_t b); + /** Determine if the columns have the same format except for is_nullable() and is_versioned(). @param[in] other column to compare to @return whether the columns have the same format */ bool same_format(const dict_col_t& other) const { - return mtype == other.mtype + return same_type(other) && len >= other.len && mbminlen == other.mbminlen && mbmaxlen == other.mbmaxlen && !((prtype ^ other.prtype) & ~(DATA_NOT_NULL | DATA_VERSIONED + | CHAR_COLL_MASK << 16 | DATA_LONG_TRUE_VARCHAR)); } }; diff --git a/storage/innobase/row/row0log.cc b/storage/innobase/row/row0log.cc index caffeab6af0..5b55409a13a 100644 --- a/storage/innobase/row/row0log.cc +++ b/storage/innobase/row/row0log.cc @@ -1166,6 +1166,10 @@ row_log_table_get_pk_col( field = rec_get_nth_field(rec, offsets, i, &len); + if (len == UNIV_SQL_DEFAULT) { + field = log->instant_field_value(i, &len); + } + if (len == UNIV_SQL_NULL) { if (!log->allow_not_null) { return(DB_INVALID_NULL); |