diff options
Diffstat (limited to '')
43 files changed, 1403 insertions, 204 deletions
diff --git a/mysql-test/include/analyze-format.inc b/mysql-test/include/analyze-format.inc index 49edd009..907f4e71 100644 --- a/mysql-test/include/analyze-format.inc +++ b/mysql-test/include/analyze-format.inc @@ -4,4 +4,4 @@ # - r_engine_stats depends on buffer pool state and whether old record versions # were purged. ---replace_regex /("(r_[a-z_]*_time(_in_progress)?_ms|r_buffer_size)": )[^, \n]*/\1"REPLACED"/ /("r_engine_stats":) {[^}]*}/\1 REPLACED/ +--replace_regex /("(r_[a-z_]*_time(_in_progress)?_ms|r_buffer_size|cost)": )[^, \n]*/\1"REPLACED"/ /("r_engine_stats":) {[^}]*}/\1 REPLACED/ diff --git a/mysql-test/include/analyze-no-filtered.inc b/mysql-test/include/analyze-no-filtered.inc new file mode 100644 index 00000000..eb166316 --- /dev/null +++ b/mysql-test/include/analyze-no-filtered.inc @@ -0,0 +1,2 @@ +--replace_regex /("(filtered|r_total_time_ms|r_table_time_ms|r_other_time_ms|r_buffer_size|r_filling_time_ms|r_query_time_in_progress_ms|r_unpack_time_ms|cost)": )[^, \n]*/\1"REPLACED"/ + diff --git a/mysql-test/include/binlog_combinations.combinations b/mysql-test/include/binlog_combinations.combinations index 07042c2c..5bd64366 100644 --- a/mysql-test/include/binlog_combinations.combinations +++ b/mysql-test/include/binlog_combinations.combinations @@ -1,8 +1,3 @@ -[row] -binlog-format=row - -[stmt] -binlog-format=statement - -[mix] -binlog-format=mixed +[nobinlog] +[binlog] +--log-bin=master-bin diff --git a/mysql-test/include/binlog_combinations.inc b/mysql-test/include/binlog_combinations.inc index 2ddd7876..b9fff341 100644 --- a/mysql-test/include/binlog_combinations.inc +++ b/mysql-test/include/binlog_combinations.inc @@ -1,5 +1,3 @@ -# The goal of including this file is to test with different -# binlog combinations: row, stmt or mix -# (see include/binlog_combinations.combinations) - ---source include/have_log_bin.inc +# +# Adds standalone and binlog combinations +# diff --git a/mysql-test/include/binlog_format_combinations.combinations b/mysql-test/include/binlog_format_combinations.combinations new file mode 100644 index 00000000..07042c2c --- /dev/null +++ b/mysql-test/include/binlog_format_combinations.combinations @@ -0,0 +1,8 @@ +[row] +binlog-format=row + +[stmt] +binlog-format=statement + +[mix] +binlog-format=mixed diff --git a/mysql-test/include/binlog_format_combinations.inc b/mysql-test/include/binlog_format_combinations.inc new file mode 100644 index 00000000..68ea4eee --- /dev/null +++ b/mysql-test/include/binlog_format_combinations.inc @@ -0,0 +1,5 @@ +# The goal of including this file is to test with different +# binlog format combinations: row, stmt or mix +# (see include/binlog_format_combinations.combinations) + +--source include/have_log_bin.inc diff --git a/mysql-test/include/check-testcase.test b/mysql-test/include/check-testcase.test index b43dffbf..25990c1a 100644 --- a/mysql-test/include/check-testcase.test +++ b/mysql-test/include/check-testcase.test @@ -46,7 +46,7 @@ if ($tmp) --echo Until_Condition # --echo Until_Log_File # --echo Until_Log_Pos # - --echo Master_SSL_Allowed No + --echo Master_SSL_Allowed Yes --echo Master_SSL_CA_File --echo Master_SSL_CA_Path --echo Master_SSL_Cert @@ -105,7 +105,7 @@ select count(*) from mysql.proc; call mtr.check_testcase(); let $datadir=`select @@datadir`; -list_files $datadir mysql_upgrade_info; +list_files $datadir mariadb_upgrade_info; list_files $datadir/test #sql*; list_files $datadir/mysql #sql*; diff --git a/mysql-test/include/check_digest.inc b/mysql-test/include/check_digest.inc new file mode 100644 index 00000000..9e5d6ccc --- /dev/null +++ b/mysql-test/include/check_digest.inc @@ -0,0 +1,34 @@ +# +# check_digest() checks digests validity from performance_schema.events_statements_summary_by_digest +# + +--disable_query_log +create table test._digests(d varchar(32) primary key); + +delimiter ~~; +create function test.check_digest(digest varchar(255)) +returns tinyint +not deterministic +begin + declare digest_exists tinyint; + if length(digest) != 32 or conv(digest, 16, 10) = 0 then + return 0; + end if; + select exists (select d from test._digests where d = digest) into digest_exists; + if digest_exists then + return 0; + end if; + #insert into test._digests values (digest); + return 1; +end~~ + +create procedure test.reset_digests() +begin + delete from test._digests; +end~~ +delimiter ;~~ + +--disable_result_log +call sys.ps_truncate_all_tables(false); +--enable_result_log +--enable_query_log diff --git a/mysql-test/include/check_digest_end.inc b/mysql-test/include/check_digest_end.inc new file mode 100644 index 00000000..7d4adf8a --- /dev/null +++ b/mysql-test/include/check_digest_end.inc @@ -0,0 +1,9 @@ +# +# Deinit for check_digest.inc +# + +--disable_query_log +drop table test._digests; +drop function test.check_digest; +drop procedure test.reset_digests; +--enable_query_log diff --git a/mysql-test/include/common-tests.inc b/mysql-test/include/common-tests.inc index 9c6b2985..9b54b049 100644 --- a/mysql-test/include/common-tests.inc +++ b/mysql-test/include/common-tests.inc @@ -13,6 +13,11 @@ drop table if exists t1,t2,t3,t4; --enable_warnings +# We have to use Aria instead of MyISAM as MyISAM has a very high row +# access cost which causes some tests to use use join_cache instead of eq_ref + +set @@default_storage_engine="aria"; + CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL @@ -1429,7 +1434,7 @@ set tmp_memory_table_size=default; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; # -# A big order by that should trigger a merge in filesort +# A big order by that should traigger a merge in filesort # select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2; @@ -1446,9 +1451,9 @@ select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr orde explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3; -# -# Some test with ORDER BY and limit -# +--echo # +--echo # Some test with ORDER BY and limit +--echo # explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; @@ -1501,7 +1506,7 @@ create table t4 ( companyname char(30) NOT NULL default '', PRIMARY KEY (companynr), UNIQUE KEY companyname(companyname) -) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames'; +) ENGINE=aria MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames'; --disable_query_log INSERT INTO t4 (companynr, companyname) VALUES (29,'company 1'); @@ -1555,8 +1560,9 @@ explain select companynr,companyname from t2 left join t4 using (companynr) wher explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null; delete from t2 where fld1=999999; -# -# Test left join optimization +--echo # +--echo # Test left join optimization +--echo # explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; diff --git a/mysql-test/include/concurrent.inc b/mysql-test/include/concurrent.inc index 1d65549f..f9058c06 100644 --- a/mysql-test/include/concurrent.inc +++ b/mysql-test/include/concurrent.inc @@ -30,7 +30,7 @@ SET SQL_MODE=""; # # Show prerequisites for this test. # -SELECT @@global.tx_isolation; +SELECT @@global.transaction_isolation; # # With the transaction isolation level REPEATABLE READ (the default) # or SERIALIZEBLE, InnoDB takes "next-key locks"/"gap locks". This means it @@ -43,7 +43,7 @@ SELECT @@global.tx_isolation; # We use the variable $keep_locks to set the expectations for # lock wait timeouts accordingly. # -let $keep_locks= `SELECT @@global.tx_isolation IN ('REPEATABLE-READ','SERIALIZABLE')`; +let $keep_locks= `SELECT @@global.transaction_isolation IN ('REPEATABLE-READ','SERIALIZABLE')`; --echo # keep_locks == $keep_locks # diff --git a/mysql-test/include/ctype_numconv.inc b/mysql-test/include/ctype_numconv.inc index a4eaf72c..6241d29e 100644 --- a/mysql-test/include/ctype_numconv.inc +++ b/mysql-test/include/ctype_numconv.inc @@ -1741,6 +1741,7 @@ CREATE TABLE t1 ( date_column DATE DEFAULT NULL, KEY(date_column)); INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01'); +INSERT INTO t1 VALUES (3,'2012-09-01'),(4,'2012-10-01'),(5,'2012-10-01'); EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01'; ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL; EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01'; diff --git a/mysql-test/include/default_client.cnf b/mysql-test/include/default_client.cnf index fc2fcf19..dea6eb91 100644 --- a/mysql-test/include/default_client.cnf +++ b/mysql-test/include/default_client.cnf @@ -2,6 +2,8 @@ # We use default-character-set=latin1 to avoid character set auto-detection # when running tests - not to depend on the current machine localization. # +[client] +loose-disable-ssl-verify-server-cert [mysql] default-character-set=latin1 diff --git a/mysql-test/include/default_my.cnf b/mysql-test/include/default_my.cnf index a1c427fe..c4c98900 100644 --- a/mysql-test/include/default_my.cnf +++ b/mysql-test/include/default_my.cnf @@ -27,9 +27,6 @@ # Run tests with the performance schema instrumentation loose-enable-performance-schema -[mysqlbinlog] -disable-force-if-open - [ENV] MASTER_MYPORT= @mysqld.1.port MASTER_MYSOCK= @mysqld.1.socket diff --git a/mysql-test/include/default_optimizer_switch.inc b/mysql-test/include/default_optimizer_switch.inc index 8634c2ee..5790cec4 100644 --- a/mysql-test/include/default_optimizer_switch.inc +++ b/mysql-test/include/default_optimizer_switch.inc @@ -8,7 +8,7 @@ set @save_optimizer_switch=@@optimizer_switch; set @save_join_cache_level=@@join_cache_level; -set optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on"; +set optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on"; set optimizer_use_condition_selectivity=4; set optimizer_search_depth=62; diff --git a/mysql-test/include/delete_use_source.inc b/mysql-test/include/delete_use_source.inc new file mode 100644 index 00000000..3f789767 --- /dev/null +++ b/mysql-test/include/delete_use_source.inc @@ -0,0 +1,94 @@ +create table t1 (c1 integer, c2 integer, c3 integer); + +insert into t1(c1,c2,c3) + values (1,1,1),(1,2,2),(1,3,3), + (2,1,4),(2,2,5),(2,3,6), + (2,4,7),(2,5,8); +insert into t1 select c1+10,c2,c3+10 from t1; +insert into t1 select c1+20,c2+1,c3+20 from t1; +analyze table t1 persistent for all; + +create view v1 as select * from t1 where c2=2; + +--echo ####################################### +--echo # Test without any index # +--echo ####################################### +--source include/delete_use_source_cases.inc +--source include/delete_use_source_cases_non_innodb.inc + +--echo ####################################### +--echo # Test with an index # +--echo ####################################### +create index t1_c2 on t1 (c2,c1); +--source include/delete_use_source_cases.inc +--source include/delete_use_source_cases_non_innodb.inc + +--echo ####################################### +--echo # Test with a primary key # +--echo ####################################### +drop index t1_c2 on t1; +alter table t1 add primary key (c3); +--source include/delete_use_source_cases.inc +--source include/delete_use_source_cases_non_innodb.inc + +drop view v1; +drop table t1; + +--echo # +--echo # Test on dynamic columns (blob) +--echo # +create table assets ( + item_name varchar(32) primary key, -- A common attribute for all items + dynamic_cols blob -- Dynamic columns will be stored here +); + +INSERT INTO assets + VALUES ('MariaDB T-shirt', + COLUMN_CREATE('color', 'blue', 'size', 'XL')); +INSERT INTO assets + VALUES ('Thinkpad Laptop', + COLUMN_CREATE('color', 'black', 'price', 500)); +INSERT INTO assets + VALUES ('Fridge', + COLUMN_CREATE('color', 'white', 'warranty', '5 years')); +INSERT INTO assets + VALUES ('Microwave', + COLUMN_CREATE('warranty', '3 years')); +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color + FROM assets ORDER BY item_name; +UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, 'color') + WHERE item_name='Fridge'; +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color + FROM assets ORDER BY item_name; +DELETE FROM assets + WHERE item_name in + (select b.item_name from assets b + where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black'); +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color + FROM assets ORDER BY item_name; +DELETE FROM assets WHERE item_name='Microwave'; +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color + FROM assets ORDER BY item_name; +drop table assets ; + + +--echo # +--echo # Test on fulltext columns +--echo # +CREATE TABLE ft2(copy TEXT,FULLTEXT(copy)); +INSERT INTO ft2(copy) VALUES + ('MySQL vs MariaDB database'), + ('Oracle vs MariaDB database'), + ('PostgreSQL vs MariaDB database'), + ('MariaDB overview'), + ('Foreign keys'), + ('Primary keys'), + ('Indexes'), + ('Transactions'), + ('Triggers'); + +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); +DELETE FROM ft2 WHERE MATCH(copy) AGAINST('database'); +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); +drop table ft2; + diff --git a/mysql-test/include/delete_use_source_cases.inc b/mysql-test/include/delete_use_source_cases.inc new file mode 100644 index 00000000..ce0507d5 --- /dev/null +++ b/mysql-test/include/delete_use_source_cases.inc @@ -0,0 +1,216 @@ +--echo # +--echo # Delete with value from subquery on the same table +--echo # + +analyze table t1 persistent for all; + +let $c = c1=(select a.c3 from t1 a where a.c3 = t1.c3); +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete with EXISTS subquery over the updated table +--echo # in WHERE + possibly sargable condition +--echo # + +analyze table t1 persistent for all; + +let $c = c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +eval analyze $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete with IN predicand over the updated table in WHERE +--echo # +let $c = c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete with a limit - can be deleted +--echo # +let $c = c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +eval analyze $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete with a limit and an order by +--echo # + +let $c = c1 in (select a.c2 from t1 a where a.c3 = t1.c3) + order by c3 desc limit 1; +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete: 2 execution of PS +--echo # + +prepare create_tmp_stmt from + "create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from + "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +--sorted_result +select * from t1; + +prepare insert_tmp_stmt from + "insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +--sorted_result +select * from t1; + +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +--sorted_result +select * from t1; + +drop table tmp; + +--echo # +--echo # Delete in stored procedure +--echo # + +delimiter //; +create procedure sp() +begin + delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) + order by c3 desc limit 1; +end +// +delimiter ;// + +create table tmp as select * from t1 + where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) + order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 + where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) + order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 + where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) + order by c3 desc limit 1; +CALL sp; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; + +--echo # +--echo # Delete in stored function +--echo # +delimiter //; +create function f1(IN a INT) returns int +begin + delete from t1 where c3 < a order by c3 limit 1; + return 1; +end;// +delimiter ;// + +set @a:=7; +create table tmp as select * from t1 where c3 < @a + order by c3 limit 1; +--disable_ps2_protocol # because SELECT with side effects +select f1(@a); +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a + order by c3 limit 1; +select f1(@a); +--enable_ps2_protocol +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; + +--echo # +--echo # Delete in trigger +--echo # + +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); + +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); + +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW + UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW + DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); + +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +--enable_info ONCE +DELETE FROM t1 WHERE c2>=3; + +--sorted_result +select * from t1; +--sorted_result +SELECT * FROM t2; +SELECT * FROM cnt; + +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; + +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; + +--echo # +--echo # Delete with a reference to view in subquery +--echo # +let $c = t1.c2 in ( select max(a.c2) from v1 a + where a.c1 = t1.c1); +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +eval analyze $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + diff --git a/mysql-test/include/delete_use_source_cases_memory.inc b/mysql-test/include/delete_use_source_cases_memory.inc new file mode 100644 index 00000000..e1b49f54 --- /dev/null +++ b/mysql-test/include/delete_use_source_cases_memory.inc @@ -0,0 +1,296 @@ +--echo # +--echo # Delete with value from subquery on the same table +--echo # + +analyze table t1 persistent for all; + +let $c = c1=(select a.c3 from t1 a where a.c3 = t1.c3); +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete with EXISTS subquery over the updated table +--echo # in WHERE + possibly sargable condition +--echo # + +analyze table t1 persistent for all; + +let $c = c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete with EXISTS subquery over the updated table +--echo # in WHERE + non-sargable condition +--echo # + +analyze table t1 persistent for all; + +let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete with order by +--echo # + +analyze table t1 persistent for all; + +let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) + and c2 >= 3 order by c2; +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete with IN predicand over the updated table in WHERE +--echo # +let $c = c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete with a limit - can be deleted +--echo # +let $c = c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete with a limit and an order by +--echo # + +let $c = c1 in (select a.c2 from t1 a where a.c3 = t1.c3) + order by c3 desc limit 1; +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete: 2 execution of PS +--echo # + +prepare create_tmp_stmt from + "create table tmp as select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +prepare delete_t1_stmt from + "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=5; +execute create_tmp_stmt using @a; +execute delete_t1_stmt using @a; +execute delete_t1_stmt using @a; +--sorted_result +select * from t1; + +prepare insert_tmp_stmt from + "insert into tmp(c1,c2,c3) select * from t1 + where c2=(select a.c3 from t1 a where a.c3 = ?)"; +set @a:=2; +execute insert_tmp_stmt using @a; +execute delete_t1_stmt using @a; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +--sorted_result +select * from t1; + +drop table tmp; + +--echo # +--echo # Delete in stored procedure +--echo # + +delimiter //; +create procedure sp() +begin + delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) + order by c3 desc limit 1; +end +// +delimiter ;// + +create table tmp as select * from t1 + where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) + order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 + where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) + order by c3 desc limit 1; +CALL sp; +insert into tmp(c1,c2,c3) select * from t1 + where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) + order by c3 desc limit 1; +CALL sp; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop procedure sp; +drop table tmp; + +--echo # +--echo # Delete in stored function +--echo # +delimiter //; +create function f1(IN a INT) returns int +begin + delete from t1 where c3 < a order by c3 limit 1; + return 1; +end;// +delimiter ;// + +set @a:=7; +create table tmp as select * from t1 where c3 < @a + order by c3 limit 1; +--disable_ps2_protocol # because SELECT with side effects +select f1(@a); +insert into tmp(c1,c2,c3) select * from t1 where c3 < @a + order by c3 limit 1; +select f1(@a); +--enable_ps2_protocol +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop function f1; +drop table tmp; + +--echo # +--echo # Delete in trigger +--echo # + +create table t2 (c1 integer); +insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); + +CREATE TABLE cnt(del integer); +INSERT INTO cnt VALUES(0); + +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW + UPDATE cnt SET del=del+1; +CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW + DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); + +CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; +--enable_info ONCE +DELETE FROM t1 WHERE c2>=3; + +--sorted_result +select * from t1; +--sorted_result +SELECT * FROM t2; +SELECT * FROM cnt; + +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; + +DROP TRIGGER tr1; +DROP TRIGGER tr2; +drop table t2, cnt, tmp; + +--echo # +--echo Delete with a reference to view in subquery +--echo # +let $c = t1.c2 in ( select max(a.c2) from v1 a + where a.c1 = t1.c1); +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete from view +--echo # + +analyze table t1 persistent for all; + +let $c = v1.c1 in + (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +eval create table tmp as select * from v1 where $c; +let $q = delete from v1 where $c; +eval explain select * from v1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete from view using reference +--echo # to the same view in subquery +--echo # + +analyze table t1 persistent for all; + +let $c = v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) + and c1 = 2 + and exists (select 'X' from v1 a where a.c1 = v1.c1); +eval create table tmp as select * from v1 where $c; +let $q = delete from v1 where $c; +eval explain select * from v1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + diff --git a/mysql-test/include/delete_use_source_cases_non_innodb.inc b/mysql-test/include/delete_use_source_cases_non_innodb.inc new file mode 100644 index 00000000..caa58171 --- /dev/null +++ b/mysql-test/include/delete_use_source_cases_non_innodb.inc @@ -0,0 +1,81 @@ +--echo # +--echo # Delete with EXISTS subquery over the updated table +--echo # in WHERE + non-sargable condition +--echo # + +analyze table t1 persistent for all; + +let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete with order by +--echo # + +analyze table t1 persistent for all; + +let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) + and c2 >= 3 order by c2; +eval create table tmp as select * from t1 where $c; +let $q = delete from t1 where $c; +--replace_column 9 # +eval explain select * from t1 where $c; +--replace_column 9 # +eval explain $q; +--replace_column 9 # +eval analyze $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete from view +--echo # + +analyze table t1 persistent for all; + +let $c = v1.c1 in + (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; +eval create table tmp as select * from v1 where $c; +let $q = delete from v1 where $c; +eval explain select * from v1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; + +--echo # +--echo # Delete from view using reference +--echo # to the same view in subquery +--echo # + +analyze table t1 persistent for all; + +let $c = v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) + and c1 = 2 + and exists (select 'X' from v1 a where a.c1 = v1.c1); +eval create table tmp as select * from v1 where $c; +let $q = delete from v1 where $c; +--replace_column 9 # +eval explain select * from v1 where $c; +--replace_column 9 # +eval explain $q; +--replace_column 9 # +eval analyze $q; +--sorted_result +select * from t1; +insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; +drop table tmp; diff --git a/mysql-test/include/delete_use_source_innodb.inc b/mysql-test/include/delete_use_source_innodb.inc new file mode 100644 index 00000000..83915d47 --- /dev/null +++ b/mysql-test/include/delete_use_source_innodb.inc @@ -0,0 +1,91 @@ +create table t1 (c1 integer, c2 integer, c3 integer); + +insert into t1(c1,c2,c3) + values (1,1,1),(1,2,2),(1,3,3), + (2,1,4),(2,2,5),(2,3,6), + (2,4,7),(2,5,8); +insert into t1 select c1+10,c2,c3+10 from t1; +insert into t1 select c1+20,c2+1,c3+20 from t1; +analyze table t1 persistent for all; + +create view v1 as select * from t1 where c2=2; + +--echo ####################################### +--echo # Test without any index # +--echo ####################################### +--source include/delete_use_source_cases.inc + +--echo ####################################### +--echo # Test with an index # +--echo ####################################### +create index t1_c2 on t1 (c2,c1); +--source include/delete_use_source_cases.inc + +--echo ####################################### +--echo # Test with a primary key # +--echo ####################################### +drop index t1_c2 on t1; +alter table t1 add primary key (c3); +--source include/delete_use_source_cases.inc + +drop view v1; +drop table t1; + +--echo # +--echo # Test on dynamic columns (blob) +--echo # +create table assets ( + item_name varchar(32) primary key, -- A common attribute for all items + dynamic_cols blob -- Dynamic columns will be stored here +); + +INSERT INTO assets + VALUES ('MariaDB T-shirt', + COLUMN_CREATE('color', 'blue', 'size', 'XL')); +INSERT INTO assets + VALUES ('Thinkpad Laptop', + COLUMN_CREATE('color', 'black', 'price', 500)); +INSERT INTO assets + VALUES ('Fridge', + COLUMN_CREATE('color', 'white', 'warranty', '5 years')); +INSERT INTO assets + VALUES ('Microwave', + COLUMN_CREATE('warranty', '3 years')); +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color + FROM assets ORDER BY item_name; +UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, 'color') + WHERE item_name='Fridge'; +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color + FROM assets ORDER BY item_name; +DELETE FROM assets + WHERE item_name in + (select b.item_name from assets b + where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black'); +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color + FROM assets ORDER BY item_name; +DELETE FROM assets WHERE item_name='Microwave'; +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color + FROM assets ORDER BY item_name; +drop table assets ; + + +--echo # +--echo # Test on fulltext columns +--echo # +CREATE TABLE ft2(copy TEXT,FULLTEXT(copy)); +INSERT INTO ft2(copy) VALUES + ('MySQL vs MariaDB database'), + ('Oracle vs MariaDB database'), + ('PostgreSQL vs MariaDB database'), + ('MariaDB overview'), + ('Foreign keys'), + ('Primary keys'), + ('Indexes'), + ('Transactions'), + ('Triggers'); + +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); +DELETE FROM ft2 WHERE MATCH(copy) AGAINST('database'); +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); +drop table ft2; + diff --git a/mysql-test/include/delete_use_source_memory.inc b/mysql-test/include/delete_use_source_memory.inc new file mode 100644 index 00000000..dbaae1a6 --- /dev/null +++ b/mysql-test/include/delete_use_source_memory.inc @@ -0,0 +1,33 @@ +create table t1 (c1 integer, c2 integer, c3 integer); + +insert into t1(c1,c2,c3) + values (1,1,1),(1,2,2),(1,3,3), + (2,1,4),(2,2,5),(2,3,6), + (2,4,7),(2,5,8); +insert into t1 select c1+10,c2,c3+10 from t1; +insert into t1 select c1+20,c2+1,c3+20 from t1; +analyze table t1 persistent for all; + +create view v1 as select * from t1 where c2=2; + +--echo ####################################### +--echo # Test without any index # +--echo ####################################### +--source include/delete_use_source_cases_memory.inc + +--echo ####################################### +--echo # Test with an index # +--echo ####################################### +create index t1_c2 on t1 (c2,c1); +--source include/delete_use_source_cases_memory.inc + +--echo ####################################### +--echo # Test with a primary key # +--echo ####################################### +drop index t1_c2 on t1; +alter table t1 add primary key (c3); +--source include/delete_use_source_cases_memory.inc + +drop view v1; +drop table t1; + diff --git a/mysql-test/include/explain-no-costs-filtered.inc b/mysql-test/include/explain-no-costs-filtered.inc new file mode 100644 index 00000000..585d8b3f --- /dev/null +++ b/mysql-test/include/explain-no-costs-filtered.inc @@ -0,0 +1 @@ +--replace_regex /("(cost|filtered)": )[^, \n]*/\1"REPLACED"/ diff --git a/mysql-test/include/explain-no-costs.inc b/mysql-test/include/explain-no-costs.inc new file mode 100644 index 00000000..f2f362b8 --- /dev/null +++ b/mysql-test/include/explain-no-costs.inc @@ -0,0 +1 @@ +--replace_regex /("(cost)": )[^, \n]*/\1"COST_REPLACED"/ diff --git a/mysql-test/include/explain_non_select.inc b/mysql-test/include/explain_non_select.inc index bab76ffb..c7bf68ff 100644 --- a/mysql-test/include/explain_non_select.inc +++ b/mysql-test/include/explain_non_select.inc @@ -1,6 +1,7 @@ # This file is a collection of regression and coverage tests # for WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE. +-- source include/have_sequence.inc -- disable_query_log -- disable_result_log # SET GLOBAL innodb_stats_persistent=0; @@ -73,15 +74,18 @@ INSERT INTO t2 VALUES (1), (2), (3); --source include/explain_utils.inc DROP TABLE t1, t2; ---echo #7 +--echo #7a CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (b INT); -INSERT INTO t2 VALUES (1), (2), (3); ---let $query = UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) ---let $select = SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) +INSERT INTO t2 VALUES (1), (2), (3), (1000); +CREATE TABLE t3 like t2; +insert into t3 select * from t2; +insert into t3 select seq from seq_1001_to_2000; +--let $query = UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t3 WHERE t3.b < 3) +--let $select = SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t3 WHERE t3.b < 3) --source include/explain_utils.inc -DROP TABLE t1, t2; +DROP TABLE t1, t2, t3; --echo #8 CREATE TABLE t1 (a INT); @@ -197,7 +201,7 @@ DROP TABLE t1, t2, t3; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (a INT); -INSERT INTO t2 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (1), (2), (3), (1000); --let $query = UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2) --let $select = SELECT * FROM t1 WHERE a IN (SELECT a FROM t2) --source include/explain_utils.inc diff --git a/mysql-test/include/galera_cluster.inc b/mysql-test/include/galera_cluster.inc index 48b5bc63..12708bfc 100644 --- a/mysql-test/include/galera_cluster.inc +++ b/mysql-test/include/galera_cluster.inc @@ -13,10 +13,8 @@ if (!$galera_cluster_size) } --source include/galera_init.inc ---source include/have_innodb.inc --source include/galera_wait_ready.inc - --let $_galera_node= $galera_cluster_size while ($_galera_node != 1) diff --git a/mysql-test/include/galera_variables_ok.inc b/mysql-test/include/galera_variables_ok.inc index c9a54724..e420b3af 100644 --- a/mysql-test/include/galera_variables_ok.inc +++ b/mysql-test/include/galera_variables_ok.inc @@ -1,6 +1,6 @@ --disable_query_log ---let $galera_variables_ok = `SELECT COUNT(*) = 50 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE 'wsrep%'` +--let $galera_variables_ok = `SELECT COUNT(*) = 51 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE 'wsrep%'` --if (!$galera_variables_ok) { --skip Galera number of variables has changed! diff --git a/mysql-test/include/have_wsrep_enabled.inc b/mysql-test/include/have_wsrep_enabled.inc index 7eb8b437..33ff58b9 100644 --- a/mysql-test/include/have_wsrep_enabled.inc +++ b/mysql-test/include/have_wsrep_enabled.inc @@ -1,8 +1,6 @@ # To be used in a test which requires wsrep plugin to be ACTIVE and enabled # (i.e. wsrep_on=ON). It includes have_wsrep.inc. ---source include/have_innodb.inc - if (`SELECT COUNT(*)=0 FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'wsrep_on' AND VARIABLE_VALUE='ON'`) { --skip Test requires wsrep_on=ON diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index d7fa6b5d..e42645fa 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -487,7 +487,7 @@ CREATE TABLE t1 ( ); INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); - +insert into t1 select seq,seq from seq_100_to_110; EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); SET SESSION optimizer_switch='index_condition_pushdown=off'; @@ -724,7 +724,6 @@ DROP TABLE t1; CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)); INSERT INTO t1 VALUES (1,4,'Ill'); -insert into t1 select seq+100,5,seq from seq_1_to_100; CREATE TABLE t2 (a varchar(1024), KEY (a(512))); INSERT INTO t2 VALUES @@ -858,6 +857,8 @@ ANALYZE TABLE t1,t2; SET @save_optimize_switch=@@optimizer_switch; SET optimizer_switch='materialization=on'; +set @save_optimizer_where_cost=@@optimizer_where_cost; +set @@optimizer_where_cost=1; EXPLAIN SELECT COUNT(*) FROM t1 AS t, t2 @@ -875,6 +876,7 @@ WHERE c = g OR a = 0 AND h < 'z' ); SET optimizer_switch=@save_optimizer_switch; +set @@optimizer_where_cost=@save_optimizer_where_cost; DROP TABLE t1,t2; diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc index 91609f62..199fc9d3 100644 --- a/mysql-test/include/index_merge1.inc +++ b/mysql-test/include/index_merge1.inc @@ -517,7 +517,7 @@ DROP TABLE t1; create table t1 (a int); insert into t1 values (1),(2); create table t2(a int, b int); -insert into t2 values (1,1), (2, 1000); +insert into t2 values (1,1), (2, 1000),(5000,5000); create table t3 (a int, b int, filler char(100), key(a), key(b)); insert into t3 select 1000, 1000,'filler' from seq_1_to_1000; diff --git a/mysql-test/include/last_query_cost.inc b/mysql-test/include/last_query_cost.inc new file mode 100644 index 00000000..a18fd9e4 --- /dev/null +++ b/mysql-test/include/last_query_cost.inc @@ -0,0 +1,5 @@ +--disable_query_log +--disable_column_names +show status like 'last_query_cost'; +--enable_column_names +--enable_query_log diff --git a/mysql-test/include/load_dump_and_upgrade.inc b/mysql-test/include/load_dump_and_upgrade.inc index 52351ea4..86ebc12c 100644 --- a/mysql-test/include/load_dump_and_upgrade.inc +++ b/mysql-test/include/load_dump_and_upgrade.inc @@ -46,4 +46,4 @@ SELECT COUNT(*) > 0 AS `mysql.user has data` FROM mysql.user; # It will fail if the file doesn't exist, which is good, # which is an extra check that it was written ---remove_file $ddir/mysql_upgrade_info +--remove_file $ddir/mariadb_upgrade_info diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index 2ec0868c..cbb79668 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -1183,14 +1183,14 @@ set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment; set global innodb_autoextend_increment=8; set global innodb_autoextend_increment=@my_innodb_autoextend_increment; -# -# Bug #37830: ORDER BY ASC/DESC - no difference -# +--echo # +--echo # Bug #37830: ORDER BY ASC/DESC - no difference +--echo # CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY (a), KEY t1_b (b)) ENGINE=InnoDB; -INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1); +INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1), (100,2,2); INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1; -- disable_query_log diff --git a/mysql-test/include/mix2.inc b/mysql-test/include/mix2.inc index 6a6fbcbc..5ea4b795 100644 --- a/mysql-test/include/mix2.inc +++ b/mysql-test/include/mix2.inc @@ -776,7 +776,7 @@ eval create table t1 (id int unsigned not null auto_increment, code tinyint unsi BEGIN; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -SELECT @@tx_isolation,@@global.tx_isolation; +SELECT @@transaction_isolation,@@global.transaction_isolation; insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); select id, code, name from t1 order by id; COMMIT; diff --git a/mysql-test/include/mtr_warnings.sql b/mysql-test/include/mtr_warnings.sql index d02f504a..88b3eb69 100644 --- a/mysql-test/include/mtr_warnings.sql +++ b/mysql-test/include/mtr_warnings.sql @@ -93,7 +93,6 @@ INSERT INTO global_suppressions VALUES ("Enabling keys got errno"), ("Error reading master configuration"), ("Error reading packet"), - ("Event Scheduler"), ("Failed to open log"), ("Failed to open the existing master info file"), ("Forcing shutdown of [0-9]* plugins"), diff --git a/mysql-test/include/percona_nonflushing_analyze_debug.inc b/mysql-test/include/percona_nonflushing_analyze_debug.inc index 95621c70..8cdf6218 100644 --- a/mysql-test/include/percona_nonflushing_analyze_debug.inc +++ b/mysql-test/include/percona_nonflushing_analyze_debug.inc @@ -8,7 +8,7 @@ --connect con1,localhost,root -SET DEBUG_SYNC="handler_ha_index_next_end SIGNAL idx_scan_in_progress WAIT_FOR finish_scan"; +SET DEBUG_SYNC="handler_rnd_next_end SIGNAL idx_scan_in_progress WAIT_FOR finish_scan"; send_eval SELECT * FROM $percona_nonflushing_analyze_table; diff --git a/mysql-test/include/rowid_filter_debug_kill.inc b/mysql-test/include/rowid_filter_debug_kill.inc index 513efed8..230bef6d 100644 --- a/mysql-test/include/rowid_filter_debug_kill.inc +++ b/mysql-test/include/rowid_filter_debug_kill.inc @@ -2,13 +2,15 @@ --source include/have_debug_sync.inc --source include/have_sequence.inc --source include/count_sessions.inc +--source include/have_sequence.inc +--source include/no_valgrind_without_big.inc --echo # --echo # MDEV-22761 KILL QUERY during rowid_filter, crashes --echo # create table t2(a int); -insert into t2 select * from seq_0_to_99; +insert into t2 select seq from seq_1_to_100; # 10K rows CREATE TABLE t3 ( @@ -18,30 +20,24 @@ CREATE TABLE t3 ( KEY (key1), KEY (key2) ); +insert into t3 select seq,seq, 'filler-data-filler-data' from seq_1_to_2000; + select engine from information_schema.tables where table_schema=database() and table_name='t3'; - -insert into t3 -select - A.seq, - B.seq, - 'filler-data-filler-data' -from seq_0_to_99 A, seq_0_to_99 B; - analyze table t2,t3; explain -select * from t2, t3 +select straight_join * from t2, t3 where - t3.key1=t2.a and t3.key2 in (2,3); + t3.key1=t2.a and t3.key2 between 2 and 10; let $target_id= `select connection_id()`; set debug_sync='handler_rowid_filter_check SIGNAL at_rowid_filter_check WAIT_FOR go'; send -select * from t2, t3 +select straight_join * from t2, t3 where - t3.key1=t2.a and t3.key2 in (2,3); + t3.key1=t2.a and t3.key2 between 2 and 10; connect (con1, localhost, root,,); set debug_sync='now WAIT_FOR at_rowid_filter_check'; diff --git a/mysql-test/include/rpl_change_topology.inc b/mysql-test/include/rpl_change_topology.inc index ddaf90a9..299277a4 100644 --- a/mysql-test/include/rpl_change_topology.inc +++ b/mysql-test/include/rpl_change_topology.inc @@ -236,11 +236,11 @@ if (!$rpl_skip_change_master) } if ($rpl_master_log_file) { - eval CHANGE MASTER TO MASTER_HOST = '127.0.0.1', MASTER_PORT = $_rpl_port, MASTER_USER = 'root', MASTER_LOG_FILE = '$_rpl_master_log_file'$_rpl_master_log_pos, MASTER_CONNECT_RETRY = 1$_timeout_adjustment, MASTER_USE_GTID=NO; + eval CHANGE MASTER TO MASTER_HOST = '127.0.0.1', MASTER_PORT = $_rpl_port, MASTER_USER = 'root', MASTER_LOG_FILE = '$_rpl_master_log_file'$_rpl_master_log_pos, MASTER_CONNECT_RETRY = 1$_timeout_adjustmen, MASTER_SSL_VERIFY_SERVER_CERT=0, MASTER_USE_GTID=NO; } if (!$rpl_master_log_file) { - eval CHANGE MASTER TO MASTER_HOST = '127.0.0.1', MASTER_PORT = $_rpl_port, MASTER_USER = 'root', MASTER_CONNECT_RETRY=1$_timeout_adjustment; + eval CHANGE MASTER TO MASTER_HOST = '127.0.0.1', MASTER_PORT = $_rpl_port, MASTER_USER = 'root', MASTER_CONNECT_RETRY=1$_timeout_adjustment, MASTER_SSL_VERIFY_SERVER_CERT=0; } } if ($_rpl_master == '') diff --git a/mysql-test/include/rpl_clone_slave_using_mariadb-backup.inc b/mysql-test/include/rpl_clone_slave_using_mariadb-backup.inc index 96fcfa21..5961e3cc 100644 --- a/mysql-test/include/rpl_clone_slave_using_mariadb-backup.inc +++ b/mysql-test/include/rpl_clone_slave_using_mariadb-backup.inc @@ -5,13 +5,14 @@ if ($cnf == "galera2_to_mariadb") --connect slave, 127.0.0.1, root, , test, $NODE_MYPORT_3 --disable_query_log --replace_result $MASTER_MYPORT ### - --eval CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='root', MASTER_PORT=$MASTER_MYPORT, MASTER_USE_GTID=NO; + --eval CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='root', MASTER_PORT=$MASTER_MYPORT, MASTER_USE_GTID=NO, MASTER_SSL_VERIFY_SERVER_CERT=0; --enable_query_log START SLAVE; --source include/wait_for_slave_to_start.inc - --let XTRABACKUP_BACKUP_OPTIONS=--no-defaults --user=root --host='127.0.0.1' --port=$NODE_MYPORT_3 - --let XTRABACKUP_COPY_BACK_OPTIONS= --no-defaults + --let XTRABACKUP_BACKUP_OPTIONS=--no-defaults --user=root --host='127.0.0.1' --port=$NODE_MYPORT_3 --loose-disable-ssl-verify-server-cert --loose-ssl-cert=$MYSQL_TEST_DIR/std_data/server-cert.pem --loose-ssl-key=$MYSQL_TEST_DIR/std_data/server-key.pem --loose-ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem + + --let XTRABACKUP_COPY_BACK_OPTIONS=--no-defaults } if ($cnf == "mariadb_to_mariadb") @@ -92,7 +93,7 @@ COMMIT; --echo ############################################################## --echo ### Running `mariadb-backup --backup,--prepare` and checking ---echo ### that xtrabackup_slave_info and xtrabackup_binlog_info are OK +--echo ### that mariadb_backup_slave_info and mariadb_backup_binlog_info are OK --echo ### Slave: Create a backup --let $backup_slave=$MYSQLTEST_VARDIR/tmp/backup-slave @@ -104,12 +105,12 @@ COMMIT; --exec $XTRABACKUP --prepare --target-dir=$backup_slave --echo ### Slave: xtrabackup files: ---echo ############################ xtrabackup_slave_info +--echo ############################ mariadb_backup_slave_info --replace_result $master_after_tr01_show_master_status_file master_after_tr01_show_master_status_file $master_after_tr01_show_master_status_position master_after_tr01_show_master_status_position ---cat_file $backup_slave/xtrabackup_slave_info ---echo ############################ xtrabackup_binlog_info +--cat_file $backup_slave/mariadb_backup_slave_info +--echo ############################ mariadb_backup_binlog_info --replace_result $slave_after_tr01_show_master_status_file slave_after_tr01_show_master_status_file $slave_after_tr01_show_master_status_position slave_after_tr01_show_master_status_position $slave_after_tr01_gtid_binlog_pos slave_after_tr01_gtid_binlog_pos ---cat_file $backup_slave/xtrabackup_binlog_info +--cat_file $backup_slave/mariadb_backup_binlog_info --echo ############################ @@ -229,11 +230,11 @@ SELECT * FROM t1 ORDER BY a; --echo ### Slave: Execute the CHANGE MASTER statement to set up the host and port --replace_result $MASTER_MYPORT ### ---eval CHANGE MASTER '' TO MASTER_USER='root', MASTER_HOST='127.0.0.1', MASTER_PORT=$MASTER_MYPORT, MASTER_CONNECT_RETRY=1 +--eval CHANGE MASTER '' TO MASTER_USER='root', MASTER_HOST='127.0.0.1', MASTER_PORT=$MASTER_MYPORT, MASTER_CONNECT_RETRY=1, MASTER_SSL_VERIFY_SERVER_CERT=0 ---echo ### Slave: Execute the CHANGE MASTER statement from xtrabackup_slave_info +--echo ### Slave: Execute the CHANGE MASTER statement from mariadb_backup_slave_info --replace_result $master_after_tr01_show_master_status_file master_after_tr01_show_master_status_file $master_after_tr01_show_master_status_position master_after_tr01_show_master_status_position ---source $backup_slave/xtrabackup_slave_info +--source $backup_slave/mariadb_backup_slave_info --echo ### Slave: Execute START SLAVE --source include/start_slave.inc diff --git a/mysql-test/include/sargable_casefold.inc b/mysql-test/include/sargable_casefold.inc new file mode 100644 index 00000000..c10e78e6 --- /dev/null +++ b/mysql-test/include/sargable_casefold.inc @@ -0,0 +1,44 @@ +# Check sargable_casefold rewrite for $collation + +eval create table t1 ( + col1 varchar(32), + col2 varchar(32), + col3 char(32), + col4 text, + key(col1), + key(col2), + key(col3), + key(col4(32)) +) collate $collation; + +insert into t1 +select + concat('A-', seq), + concat('A-', seq), + concat('A-', seq), + concat('A-', seq) +from seq_1_to_100; + +analyze table t1 persistent for all; + +--echo # Basic examples. All should use ref(col1): +explain +select * from t1 where upper(col1)='A-3'; +select * from t1 where upper(col1)='A-3'; + +explain +select * from t1 where ucase(col1)='a-3'; +select * from t1 where ucase(col1)='a-3'; + +explain select * from t1 where 'abc'=upper(col1); +explain select * from t1 where 'xyz'=ucase(col1); + +create view v1 as select * from t1; +explain select * from v1 where 'abc'=upper(col1); +drop view v1; + +explain select * from t1 where upper(col3)='a-3'; +explain select * from t1 where upper(col4)='a-3'; + +# DROP TABLE t1 is missing intentionally here. + diff --git a/mysql-test/include/update_use_source.inc b/mysql-test/include/update_use_source.inc index 3c487704..e1b66d2b 100644 --- a/mysql-test/include/update_use_source.inc +++ b/mysql-test/include/update_use_source.inc @@ -1,150 +1,188 @@ # Include to test update with same table as source and target ---echo # ---echo # Update a with value from subquery on the same table, no search clause. ALL access ---echo # - -#Enable view protocol after fix MDEV-29207 ---disable_view_protocol -start transaction; +create table t1 (old_c1 integer, + old_c2 integer, + c1 integer, + c2 integer, + c3 integer); + +create view v1 as select * from t1 where c2=2; +delimiter /; +create trigger trg_t1 before update on t1 for each row +begin + set new.old_c1=old.c1; + set new.old_c2=old.c2; +end; +/ +delimiter ;/ + +insert into t1(c1,c2,c3) + values (1,1,1), (1,2,2), (1,3,3), + (2,1,4), (2,2,5), (2,3,6), + (2,4,7), (2,5,8); +insert into t1 select NULL, NULL, c1+10,c2,c3+10 from t1; +insert into t1 select NULL, NULL, c1+20,c2+1,c3+20 from t1; +analyze table t1 persistent for all; + +create table tmp as select * from t1; + +--echo ####################################### +--echo # Test without any index # +--echo ####################################### +--source include/update_use_source_cases.inc + +--echo ####################################### +--echo # Test with an index # +--echo ####################################### +create index t1_c2 on t1 (c2,c1); +analyze table t1; +--source include/update_use_source_cases.inc + +--echo ####################################### +--echo # Test with a primary key # +--echo ####################################### +drop index t1_c2 on t1; +alter table t1 add primary key (c3); +analyze table t1; +--source include/update_use_source_cases.inc + +--echo # Update with error "Subquery returns more than 1 row" +--error ER_SUBQUERY_NO_1_ROW +update t1 set c2=(select c2 from t1); +--sorted_result +select c1,c2,c3 from t1; + +--echo # Update with error "Subquery returns more than 1 row" +--echo # and order by +--error ER_SUBQUERY_NO_1_ROW +update t1 set c2=(select c2 from t1) order by c3; +--sorted_result +select c1,c2,c3 from t1; + +-- echo # Duplicate value on update a primary key +--error ER_DUP_ENTRY + +update t1 set c3=0 + where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; +--sorted_result +select c1,c2,c3 from t1; +truncate table t1; +insert into t1 select * from tmp; + +-- echo # Duplicate value on update a primary key with ignore --enable_info ONCE -update t1 - set c1=(select a.c3 - from t1 a - where a.c3 = t1.c3); -select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; -rollback; - ---echo # ---echo # Update with search clause on the same table ---echo # - -start transaction; +update ignore t1 set c3=0 + where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; +--sorted_result +select c1,c2,c3 from t1; +truncate table t1; +insert into t1 select * from tmp; + +-- echo # Duplicate value on update a primary key and limit +--error ER_DUP_ENTRY +update t1 set c3=0 + where exists (select 'X' from t1 a where a.c2 = t1.c2) + and c2 >= 3 limit 2; +--sorted_result +select c1,c2,c3 from t1; +truncate table t1; +insert into t1 select * from tmp; + +-- echo # Duplicate value on update a primary key with ignore +-- echo # and limit --enable_info ONCE -update t1 - set c1=10 - where c1 <2 - and exists (select 'X' - from t1 a - where a.c1 = t1.c1); -select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; -rollback; - ---echo # ---echo # Update via RANGE or INDEX access if an index or a primary key exists ---echo # - -explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; -start transaction; +update ignore t1 set c3=0 + where exists (select 'X' from t1 a where a.c2 = t1.c2) + and c2 >= 3 limit 2; +--sorted_result +select c1,c2,c3 from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # Update no rows found --enable_info ONCE -update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; -select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; -rollback; - ---echo # ---echo # Update with order by ---echo # - -start transaction; -update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; -select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; -rollback; - ---echo # ---echo Update using a view in subquery ---echo # - -start transaction; +update t1 set c1=10 + where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1 + 10); +--sorted_result +select c1,c2,c3 from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # Update no rows changed +drop trigger trg_t1; --enable_info ONCE -update t1 - set c1=c1 +(select max(a.c2) - from v1 a - where a.c1 = t1.c1) ; -select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; -rollback; +update t1 set c1=c1 + where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1); +--sorted_result +select c1,c2,c3 from t1; +truncate table t1; +insert into t1 select * from tmp; --echo # ---echo # Update throw a view +--echo # Check call of after trigger --echo # -start transaction; ---enable_info ONCE -update v1 - set c1=c1 + (select max(a.c2) - from t1 a - where a.c1 = v1.c1) +10 -where c3 > 3; -select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; -rollback; - ---echo # ---echo # Update through a view and using the view in subquery ---echo # +delimiter /; +create or replace trigger trg_t2 after update on t1 for each row +begin + declare msg varchar(100); + if (new.c3 = 5) then + set msg=concat('in after update trigger on ',new.c3); + SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; + end if; +end; +/ +delimiter ;/ +--error 1644 -start transaction; ---enable_info ONCE -update v1 - set c1=c1 + 1 - where c1 <2 - and exists (select 'X' - from v1 a - where a.c1 = v1.c1); -select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; -rollback; +update t1 set c1=2 + where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1); +--sorted_result +select c1,c2,c3 from t1; +truncate table t1; +insert into t1 select * from tmp; --echo # ---echo # Update through a view and using the view in subquery +--echo # Check update with order by and after trigger --echo # -start transaction; ---enable_info ONCE -update v1 - set c1=(select max(a.c1)+10 - from v1 a - where a.c1 = v1.c1) - where c1 <10 - and exists (select 'X' - from v1 a - where a.c2 = v1.c2); -select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; -rollback; +--error 1644 +update t1 set c1=2 + where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1) + order by t1.c2, t1.c1; +--sorted_result +select c1,c2,c3 from t1; +truncate table t1; +insert into t1 select * from tmp; + +drop view v1; --echo # ---echo # Update of the index or primary key (c3) +--echo # Check update on view with check option --echo # -start transaction; -explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1); ---enable_info ONCE -update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1); -select c3 from t1; -rollback; +create view v1 as select * from t1 where c2=2 with check option; ---echo # ---echo # update with a limit ---echo # +-- error 1369 +update v1 set c2=3 where c1=1; +--sorted_result +select c1,c2,c3 from t1; +truncate table t1; +insert into t1 select * from tmp; -start transaction; ---enable_info ONCE -update t1 - set c1=(select a.c3 - from t1 a - where a.c3 = t1.c3) - limit 2; -select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; -rollback; +-- error 1369 +update v1 set c2=(select max(c3) from v1) where c1=1; +--sorted_result +select c1,c2,c3 from t1; +truncate table t1; +insert into t1 select * from tmp; ---echo # ---echo # update with a limit and an order by ---echo # +update v1 set c2=(select min(va.c3) from v1 va), c1=0 where c1=1; +--sorted_result +select c1,c2,c3 from t1; +truncate table t1; +insert into t1 select * from tmp; -start transaction; ---enable_info ONCE -update t1 - set c1=(select a.c3 - from t1 a - where a.c3 = t1.c3) - order by c3 desc limit 2; -select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ; -rollback; ---enable_view_protocol +drop table tmp; +drop view v1; +drop table t1; diff --git a/mysql-test/include/update_use_source_cases.inc b/mysql-test/include/update_use_source_cases.inc new file mode 100644 index 00000000..f1025f75 --- /dev/null +++ b/mysql-test/include/update_use_source_cases.inc @@ -0,0 +1,183 @@ +--echo # +--echo # Update with value from subquery on the same table +--echo # + +analyze table t1 persistent for all; +let $q = update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3); +eval explain select * from t1; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with EXISTS subquery over the updated table +--echo # in WHERE + possibly sargable condition +--echo # + +analyze table t1 persistent for all; +let $c = c1 <2 + and exists (select 'X' from t1 a where a.c1 = t1.c1); +let $q = update t1 set c1=10 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with EXISTS subquery over the updated table +--echo # in WHERE + non-sargable condition +--echo # + +analyze table t1 persistent for all; +let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; +let $q = update t1 set c1=c1+10 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with order by +--echo # + +analyze table t1 persistent for all; +let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) + and c2 >= 3 order by c2; +let $q = update t1 set c1=c1+10 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with a reference to view in subquery +--echo # in settable value +--echo # + +analyze table t1 persistent for all; +let $q = update t1 set c1=c1 +(select max(a.c2) from v1 a + where a.c1 = t1.c1); +eval explain select * from t1; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update view +--echo # + +analyze table t1 persistent for all; +let $q = update v1 set c1=c1 + (select max(a.c2) from t1 a + where a.c1 = v1.c1) +10 where c3 > 3; +eval explain select * from v1; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update view with reference to the same view in subquery +--echo # + +analyze table t1 persistent for all; +let $c = c1 <2 + and exists (select 'X' from v1 a where a.c1 = v1.c1); +let $q = update v1 set c1=c1 + 1 where $c; +eval explain select * from v1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update view with EXISTS and reference to the same view in subquery +--echo # + +analyze table t1 persistent for all; +let $c = c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2); +let $q = update v1 + set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1) + where $c; +eval explain select * from v1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with IN predicand over the updated table in WHERE +--echo # + +analyze table t1 persistent for all; +let $c = c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1); +let $q = update t1 set c3=c3+110 where $c; +eval explain select * from t1 where $c; +eval explain $q; +--enable_info ONCE +eval $q; +--sorted_result +select c3 from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with a limit +--echo # + +analyze table t1 persistent for all; +let $q = update t1 + set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2; +eval explain select * from t1 limit 2; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; + +--echo # +--echo # Update with a limit and an order by +--echo # + +analyze table t1 persistent for all; +let $q=update t1 + set c1=(select a.c3 from t1 a where a.c3 = t1.c3) + order by c3 desc limit 2; +eval explain select * from t1 order by c3 desc limit 2; +eval explain $q; +--enable_info ONCE +eval $q; +select concat(old_c1,'->',c1),c3, + case when c1 != old_c1 then '*' else ' ' end "Changed" from t1; +truncate table t1; +insert into t1 select * from tmp; diff --git a/mysql-test/include/update_use_source_ext.inc b/mysql-test/include/update_use_source_ext.inc new file mode 100644 index 00000000..004f7a96 --- /dev/null +++ b/mysql-test/include/update_use_source_ext.inc @@ -0,0 +1,59 @@ +--echo # +--echo # Test on dynamic columns (blob) +--echo # + +create table assets ( + item_name varchar(32) primary key, -- A common attribute for all items + dynamic_cols blob -- Dynamic columns will be stored here +); +INSERT INTO assets VALUES ('MariaDB T-shirt', + COLUMN_CREATE('color', 'blue', 'size', 'XL')); +INSERT INTO assets VALUES ('Thinkpad Laptop', + COLUMN_CREATE('color', 'black', 'price', 500)); +SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color + FROM assets; +UPDATE assets + SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years') + WHERE item_name='Thinkpad Laptop'; +SELECT item_name, + COLUMN_GET(dynamic_cols, 'warranty' as char) AS color + FROM assets; +UPDATE assets + SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '4 years') + WHERE item_name in + (select b.item_name from assets b + where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black'); +SELECT item_name, + COLUMN_GET(dynamic_cols, 'warranty' as char) AS color + FROM assets; + +UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', + (select COLUMN_GET(b.dynamic_cols, 'color' as char) + from assets b + where assets.item_name = item_name)); +SELECT item_name, + COLUMN_GET(dynamic_cols, 'warranty' as char) AS color + FROM assets; +drop table assets; + +--echo # +--echo # Test on fulltext columns +--echo # +CREATE TABLE ft2(copy TEXT,FULLTEXT(copy)); +INSERT INTO ft2(copy) VALUES + ('MySQL vs MariaDB database'), + ('Oracle vs MariaDB database'), + ('PostgreSQL vs MariaDB database'), + ('MariaDB overview'), + ('Foreign keys'), + ('Primary keys'), + ('Indexes'), + ('Transactions'), + ('Triggers'); +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); + +update ft2 set copy = (select max(concat('mykeyword ',substr(b.copy,1,5))) + from ft2 b WHERE MATCH(b.copy) AGAINST('database')) + where MATCH(copy) AGAINST('keys'); +SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('mykeyword'); +drop table ft2; diff --git a/mysql-test/include/world.inc b/mysql-test/include/world.inc index 1451a4ab..91ea66ef 100644 --- a/mysql-test/include/world.inc +++ b/mysql-test/include/world.inc @@ -4,6 +4,7 @@ # Table Country +BEGIN; INSERT IGNORE INTO Country VALUES ('AFG','Afghanistan',652090.00,22720000,1), ('NLD','Netherlands',41526.00,15864000,5), @@ -5339,5 +5340,6 @@ INSERT INTO CountryLanguage VALUES ('CHN','Dong',0.2), ('RUS','Belorussian',0.3), ('USA','Portuguese',0.2); +COMMIT; ANALYZE TABLE Country, City, CountryLanguage; |