diff options
Diffstat (limited to 'mysql-test')
73 files changed, 2866 insertions, 240 deletions
diff --git a/mysql-test/main/analyze_format_json_emb.result b/mysql-test/main/analyze_format_json_emb.result new file mode 100644 index 00000000000..d61e205f031 --- /dev/null +++ b/mysql-test/main/analyze_format_json_emb.result @@ -0,0 +1,11 @@ +# +# MDEV-31121: ANALYZE statement produces 0 for all timings in embedded serve +# +create table t1 (a int); +insert into t1 values (0),(0); +set @js='$out'; +set @out=(select json_extract(@js,'$**.query_block.r_total_time_ms')); +select cast(json_extract(@out,'$[0]') as DOUBLE) > 0; +cast(json_extract(@out,'$[0]') as DOUBLE) > 0 +1 +drop table t1; diff --git a/mysql-test/main/analyze_format_json_emb.test b/mysql-test/main/analyze_format_json_emb.test new file mode 100644 index 00000000000..dcf6f24dd8e --- /dev/null +++ b/mysql-test/main/analyze_format_json_emb.test @@ -0,0 +1,18 @@ +--source include/is_embedded.inc +--source include/big_test.inc + +--echo # +--echo # MDEV-31121: ANALYZE statement produces 0 for all timings in embedded serve +--echo # +create table t1 (a int); +insert into t1 values (0),(0); +let $out=` +analyze format=json select sleep(1+a) from t1 +`; + +evalp set @js='$out'; +set @out=(select json_extract(@js,'$**.query_block.r_total_time_ms')); +select cast(json_extract(@out,'$[0]') as DOUBLE) > 0; + +drop table t1; + diff --git a/mysql-test/main/cli_options_force_protocol_not_win.result b/mysql-test/main/cli_options_force_protocol_not_win.result index 20b12eb2cc0..46ee29dc509 100644 --- a/mysql-test/main/cli_options_force_protocol_not_win.result +++ b/mysql-test/main/cli_options_force_protocol_not_win.result @@ -4,21 +4,19 @@ # # The following group of tests should produce no warnings # -# exec MYSQL --host=localhost -e "status" 2>&1 | grep "Connection:\|WARNING:" +# exec MYSQL --host=localhost -e "status" 2>&1 | grep "Connection:" Connection: Localhost via UNIX socket -# exec MYSQL --port=MASTER_MYPORT --protocol=tcp -e "status" 2>&1 | grep "Connection:\|WARNING:" +# exec MYSQL --port=MASTER_MYPORT --protocol=tcp -e "status" 2>&1 | grep "Connection:" Connection: localhost via TCP/IP -# exec MYSQL --host=localhost --port=MASTER_MYPORT --protocol=socket -e "status" 2>&1 | grep "Connection:\|WARNING:" +# exec MYSQL --host=localhost --port=MASTER_MYPORT --protocol=socket -e "status" 2>&1 | grep "Connection:" Connection: Localhost via UNIX socket -# exec MYSQL --host=127.0.0.1 --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:" +# exec MYSQL --host=127.0.0.1 --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:" Connection: 127.0.0.1 via TCP/IP -# exec MYSQL --host=localhost --socket=MASTER_MYSOCK --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:" +# exec MYSQL --host=localhost --socket=MASTER_MYSOCK --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:" +Connection: localhost via TCP/IP +# exec MYSQL --host=localhost --port=MASTER_MYPORT --socket=MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:" Connection: Localhost via UNIX socket -# exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:\|WARNING:" +# exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:" Connection: Localhost via UNIX socket -# -# The remaining tests should produce warnings -# -# exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:" -WARNING: Forcing protocol to TCP due to option specification. Please explicitly state intended protocol. +# exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:" Connection: localhost via TCP/IP diff --git a/mysql-test/main/cli_options_force_protocol_not_win.test b/mysql-test/main/cli_options_force_protocol_not_win.test index a3c495f26ee..a92f6b93c4d 100644 --- a/mysql-test/main/cli_options_force_protocol_not_win.test +++ b/mysql-test/main/cli_options_force_protocol_not_win.test @@ -10,28 +10,26 @@ --echo # The following group of tests should produce no warnings --echo # ---echo # exec MYSQL --host=localhost -e "status" 2>&1 | grep "Connection:\|WARNING:" ---exec $MYSQL --host=localhost -e "status" 2>&1 | grep "Connection:\|WARNING:" +--echo # exec MYSQL --host=localhost -e "status" 2>&1 | grep "Connection:" +--exec $MYSQL --host=localhost -e "status" 2>&1 | grep "Connection:" ---echo # exec MYSQL --port=MASTER_MYPORT --protocol=tcp -e "status" 2>&1 | grep "Connection:\|WARNING:" ---exec $MYSQL --port=$MASTER_MYPORT --protocol=tcp -e "status" 2>&1 | grep "Connection:\|WARNING:" +--echo # exec MYSQL --port=MASTER_MYPORT --protocol=tcp -e "status" 2>&1 | grep "Connection:" +--exec $MYSQL --port=$MASTER_MYPORT --protocol=tcp -e "status" 2>&1 | grep "Connection:" ---echo # exec MYSQL --host=localhost --port=MASTER_MYPORT --protocol=socket -e "status" 2>&1 | grep "Connection:\|WARNING:" ---exec $MYSQL --host=localhost --port=$MASTER_MYPORT --protocol=socket -e "status" 2>&1 | grep "Connection:\|WARNING:" +--echo # exec MYSQL --host=localhost --port=MASTER_MYPORT --protocol=socket -e "status" 2>&1 | grep "Connection:" +--exec $MYSQL --host=localhost --port=$MASTER_MYPORT --protocol=socket -e "status" 2>&1 | grep "Connection:" ---echo # exec MYSQL --host=127.0.0.1 --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:" ---exec $MYSQL --host=127.0.0.1 --port=$MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:" +--echo # exec MYSQL --host=127.0.0.1 --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:" +--exec $MYSQL --host=127.0.0.1 --port=$MASTER_MYPORT -e "status" 2>&1 | grep "Connection:" ---echo # exec MYSQL --host=localhost --socket=MASTER_MYSOCK --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:" ---exec $MYSQL --host=localhost --socket=$MASTER_MYSOCK --port=$MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:" +--echo # exec MYSQL --host=localhost --socket=MASTER_MYSOCK --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:" +--exec $MYSQL --host=localhost --socket=$MASTER_MYSOCK --port=$MASTER_MYPORT -e "status" 2>&1 | grep "Connection:" ---echo # exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:\|WARNING:" ---exec $MYSQL --host=localhost --socket=$MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:\|WARNING:" +--echo # exec MYSQL --host=localhost --port=MASTER_MYPORT --socket=MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:" +--exec $MYSQL --host=localhost --port=MASTER_MYPORT --socket=$MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:" +--echo # exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:" +--exec $MYSQL --host=localhost --socket=$MASTER_MYSOCK -e "status" 2>&1 | grep "Connection:" ---echo # ---echo # The remaining tests should produce warnings ---echo # - ---echo # exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:" ---exec $MYSQL --host=localhost --port=$MASTER_MYPORT -e "status" 2>&1 | grep "Connection:\|WARNING:" +--echo # exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | grep "Connection:" +--exec $MYSQL --host=localhost --port=$MASTER_MYPORT -e "status" 2>&1 | grep "Connection:" diff --git a/mysql-test/main/cli_options_force_protocol_win.result b/mysql-test/main/cli_options_force_protocol_win.result index 4fffc2a4837..eedfde4f1de 100644 --- a/mysql-test/main/cli_options_force_protocol_win.result +++ b/mysql-test/main/cli_options_force_protocol_win.result @@ -1,24 +1,17 @@ # # MDEV-14974: --port ignored for --host=localhost # -# -# The following group of tests should produce no warnings -# -# exec MYSQL --host=localhost -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" +# exec MYSQL --host=localhost -e "status" 2>&1 | findstr /c:"Connection:" Connection: localhost via TCP/IP -# exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" +# exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | findstr /c:"Connection:" Connection: localhost via TCP/IP -# exec MYSQL --host=localhost --port=MASTER_MYPORT --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" +# exec MYSQL --host=localhost --port=MASTER_MYPORT --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" Connection: localhost via TCP/IP -# exec MYSQL --host=localhost --protocol=pipe -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" -Connection: localhost via named pipe -# exec MYSQL --host=localhost -W -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" -Connection: localhost via named pipe -# exec MYSQL --host=localhost -W --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" +# exec MYSQL --host=localhost --protocol=pipe -e "status" 2>&1 | findstr /c:"Connection:" Connection: localhost via named pipe -# -# The remaining tests should produce warnings -# -# exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" -WARNING: Forcing protocol to PIPE due to option specification. Please explicitly state intended protocol. +# exec MYSQL --host=localhost -W -e "status" 2>&1 | findstr /c:"Connection:" Connection: localhost via named pipe +# exec MYSQL --host=localhost -W --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" +Connection: localhost via TCP/IP +# exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" +Connection: localhost via TCP/IP diff --git a/mysql-test/main/cli_options_force_protocol_win.test b/mysql-test/main/cli_options_force_protocol_win.test index 54fbb78e5dc..63024f3b620 100644 --- a/mysql-test/main/cli_options_force_protocol_win.test +++ b/mysql-test/main/cli_options_force_protocol_win.test @@ -5,33 +5,23 @@ --source include/not_embedded.inc --source include/windows.inc +--echo # exec MYSQL --host=localhost -e "status" 2>&1 | findstr /c:"Connection:" +--exec $MYSQL --host=localhost -e "status" 2>&1 | findstr /c:"Connection:" ---echo # ---echo # The following group of tests should produce no warnings ---echo # - ---echo # exec MYSQL --host=localhost -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" ---exec $MYSQL --host=localhost -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" - ---echo # exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" ---exec $MYSQL --host=localhost --port=$MASTER_MYPORT -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" +--echo # exec MYSQL --host=localhost --port=MASTER_MYPORT -e "status" 2>&1 | findstr /c:"Connection:" +--exec $MYSQL --host=localhost --port=$MASTER_MYPORT -e "status" 2>&1 | findstr /c:"Connection:" ---echo # exec MYSQL --host=localhost --port=MASTER_MYPORT --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" ---exec $MYSQL --host=localhost --port=$MASTER_MYPORT --socket=$MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" +--echo # exec MYSQL --host=localhost --port=MASTER_MYPORT --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" +--exec $MYSQL --host=localhost --port=$MASTER_MYPORT --socket=$MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" ---echo # exec MYSQL --host=localhost --protocol=pipe -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" ---exec $MYSQL --host=localhost --protocol=pipe -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" +--echo # exec MYSQL --host=localhost --protocol=pipe -e "status" 2>&1 | findstr /c:"Connection:" +--exec $MYSQL --host=localhost --protocol=pipe -e "status" 2>&1 | findstr /c:"Connection:" ---echo # exec MYSQL --host=localhost -W -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" ---exec $MYSQL --host=localhost -W -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" +--echo # exec MYSQL --host=localhost -W -e "status" 2>&1 | findstr /c:"Connection:" +--exec $MYSQL --host=localhost -W -e "status" 2>&1 | findstr /c:"Connection:" ---echo # exec MYSQL --host=localhost -W --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" ---exec $MYSQL --host=localhost -W --socket=$MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" - - ---echo # ---echo # The remaining tests should produce warnings ---echo # +--echo # exec MYSQL --host=localhost -W --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" +--exec $MYSQL --host=localhost -W --socket=$MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" ---echo # exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" ---exec $MYSQL --host=localhost --socket=$MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" /c:"WARNING:" +--echo # exec MYSQL --host=localhost --socket=MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" +--exec $MYSQL --host=localhost --socket=$MASTER_MYSOCK -e "status" 2>&1 | findstr /c:"Connection:" diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index f47920c0e33..2cbdf9ae0b3 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -18248,4 +18248,87 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary drop view v1; drop table t1; +# +# MDEV-31102: execution of PS for query where pushdown of condition +# into view defined as union is applied +# +create table t1 ( +n int, +lv varchar(31) charset latin1, +mv varchar(31) charset utf8mb3 +) engine=myisam; +insert into t1 values (1,'aa','xxx'), ('2','bb','yyy'), (3,'cc','zzz'); +create view v1 as +select case when n=1 then lv when n=2 then mv else NULL end as r from t1 +union +select 'a'; +select * from v1 where r < 'x'; +r +aa +a +explain extended select * from v1 where r < 'x'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 Using where +2 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where +3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `v1`.`r` AS `r` from `test`.`v1` where `v1`.`r` < 'x' +explain format=json select * from v1 where r < 'x'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "v1.r < 'x'", + "materialized": { + "query_block": { + "union_result": { + "table_name": "<union2,3>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when t1.n = 1 then convert(t1.lv using utf8mb3) when t1.n = 2 then t1.mv else NULL end < 'x'" + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } + } + } + } +} +prepare stmt from "select * from v1 where r < 'x'"; +execute stmt; +r +aa +a +execute stmt; +r +aa +a +deallocate prepare stmt; +drop view v1; +drop table t1; # End of 10.4 tests diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 17115c143ac..08d0854df57 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -3943,4 +3943,34 @@ explain select * from v1; drop view v1; drop table t1; +--echo # +--echo # MDEV-31102: execution of PS for query where pushdown of condition +--echo # into view defined as union is applied +--echo # + +create table t1 ( + n int, + lv varchar(31) charset latin1, + mv varchar(31) charset utf8mb3 +) engine=myisam; +insert into t1 values (1,'aa','xxx'), ('2','bb','yyy'), (3,'cc','zzz'); +create view v1 as +select case when n=1 then lv when n=2 then mv else NULL end as r from t1 +union +select 'a'; + +let $q= +select * from v1 where r < 'x'; + +eval $q; +eval explain extended $q; +eval explain format=json $q; +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop view v1; +drop table t1; + --echo # End of 10.4 tests diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 74876836a53..188a7f334b1 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -287,4 +287,541 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DERIVED t4 ALL NULL NULL NULL NULL 40 Using filesort drop table t3, t4; # End of 10.3 tests +# +# MDEV-26301: Split optimization refills temporary table too many times +# +create table t1(a int, b int); +insert into t1 select seq,seq from seq_1_to_5; +create table t2(a int, b int, key(a)); +insert into t2 +select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B; +create table t3(a int, b int, key(a)); +insert into t3 +select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B; +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status Table is already up to date +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status Table is already up to date +explain +select * from +(t1 left join t2 on t2.a=t1.b) left join t3 on t3.a=t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +1 SIMPLE t2 ref a a 5 test.t1.b 2 Using where +1 SIMPLE t3 ref a a 5 test.t1.b 3 Using where +create table t10 ( +grp_id int, +col1 int, +key(grp_id) +); +insert into t10 +select +A.seq, +B.seq +from +seq_1_to_100 A, +seq_1_to_100 B; +create table t11 ( +col1 int, +col2 int +); +insert into t11 +select A.seq, A.seq from seq_1_to_10 A; +analyze table t10,t11 persistent for all; +Table Op Msg_type Msg_text +test.t10 analyze status Engine-independent statistics collected +test.t10 analyze status Table is already up to date +test.t11 analyze status Engine-independent statistics collected +test.t11 analyze status OK +explain select * from +( +(t1 left join t2 on t2.a=t1.b) +left join t3 on t3.a=t1.b +) left join (select grp_id, count(*) +from t10 left join t11 on t11.col1=t10.col1 +group by grp_id) T on T.grp_id=t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where +1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where +2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100 +2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +# The important part in the below output is: +# "lateral": 1, +# "query_block": { +# "select_id": 2, +# "r_loops": 5, <-- must be 5, not 30. +analyze format=json select * from +( +(t1 left join t2 on t2.a=t1.b) +left join t3 on t3.a=t1.b +) left join (select grp_id, count(*) +from t10 left join t11 on t11.col1=t10.col1 +group by grp_id) T on T.grp_id=t1.b; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "const_condition": "1", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 5, + "r_rows": 5, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "table": { + "table_name": "t2", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t1.b"], + "r_loops": 5, + "rows": 2, + "r_rows": 2, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "trigcond(trigcond(t1.b is not null))" + }, + "table": { + "table_name": "t3", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t1.b"], + "r_loops": 10, + "rows": 3, + "r_rows": 3, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "trigcond(trigcond(t1.b is not null))" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["grp_id"], + "ref": ["test.t1.b"], + "r_loops": 30, + "rows": 10, + "r_rows": 1, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "trigcond(trigcond(t1.b is not null))", + "materialized": { + "lateral": 1, + "query_block": { + "select_id": 2, + "r_loops": 5, + "r_total_time_ms": "REPLACED", + "outer_ref_condition": "t1.b is not null", + "table": { + "table_name": "t10", + "access_type": "ref", + "possible_keys": ["grp_id"], + "key": "grp_id", + "key_length": "5", + "used_key_parts": ["grp_id"], + "ref": ["test.t1.b"], + "r_loops": 5, + "rows": 100, + "r_rows": 100, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t11", + "access_type": "ALL", + "r_loops": 5, + "rows": 10, + "r_rows": 10, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "1Kb", + "join_type": "BNL", + "attached_condition": "trigcond(t11.col1 = t10.col1)", + "r_loops": 500, + "r_filtered": 10, + "r_unpack_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "r_effective_rows": 10 + } + } + } + } + } +} +create table t21 (pk int primary key); +insert into t21 values (1),(2),(3); +create table t22 (pk int primary key); +insert into t22 values (1),(2),(3); +explain +select * from +t21, t22, +( +(t1 left join t2 on t2.a=t1.b) +left join t3 on t3.a=t1.b +) left join (select grp_id, count(*) +from t10 left join t11 on t11.col1=t10.col1 +group by grp_id) T on T.grp_id=t1.b +where +t21.pk=1 and t22.pk=2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t21 const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY t22 const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where +1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where +2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100 +2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +explain +select * from +t21, +( +(t1 left join t2 on t2.a=t1.b) +left join t3 on t3.a=t1.b +) left join (select grp_id, count(*) +from +t22 join t10 left join t11 on t11.col1=t10.col1 +where +t22.pk=1 +group by grp_id) T on T.grp_id=t1.b +where +t21.pk=1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t21 const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where +1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where +2 LATERAL DERIVED t22 const PRIMARY PRIMARY 4 const 1 Using index +2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100 +2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +create table t5 ( +pk int primary key +); +insert into t5 select seq from seq_1_to_1000; +explain +select * from +t21, +( +(((t1 join t5 on t5.pk=t1.b)) left join t2 on t2.a=t1.b) +left join t3 on t3.a=t1.b +) left join (select grp_id, count(*) +from +t22 join t10 left join t11 on t11.col1=t10.col1 +where +t22.pk=1 +group by grp_id) T on T.grp_id=t1.b +where +t21.pk=1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t21 const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t5 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index +1 PRIMARY t2 ref a a 5 test.t1.b 2 +1 PRIMARY t3 ref a a 5 test.t1.b 3 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where +2 LATERAL DERIVED t22 const PRIMARY PRIMARY 4 const 1 Using index +2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t5.pk 100 Using index condition +2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +drop table t1,t2,t3,t5, t10, t11, t21, t22; +create table t1(a int, b int); +insert into t1 select seq,seq from seq_1_to_5; +create table t2(a int, b int, key(a)); +insert into t2 +select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B; +create table t3(a int, b int, key(a)); +insert into t3 +select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B; +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status Table is already up to date +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status Table is already up to date +create table t10 ( +grp_id int, +col1 int, +key(grp_id) +); +insert into t10 +select +A.seq, +B.seq +from +seq_1_to_100 A, +seq_1_to_100 B; +create table t11 ( +col1 int, +col2 int +); +insert into t11 +select A.seq, A.seq from seq_1_to_10 A; +analyze table t10,t11 persistent for all; +Table Op Msg_type Msg_text +test.t10 analyze status Engine-independent statistics collected +test.t10 analyze status Table is already up to date +test.t11 analyze status Engine-independent statistics collected +test.t11 analyze status OK +explain select * +from +( +(t1 left join t2 on t2.a=t1.b) +left join +t3 +on t3.a=t1.b +) +left join +( +select grp_id, count(*) +from t10 left join t11 on t11.col1=t10.col1 +group by grp_id +)dt +on dt.grp_id=t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where +1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where +2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100 +2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +select * +from +( +(t1 left join t2 on t2.a=t1.b) +left join +t3 +on t3.a=t1.b +) +left join +( +select grp_id, count(*) +from t10 left join t11 on t11.col1=t10.col1 +group by grp_id +)dt +on dt.grp_id=t1.b; +a b a b a b grp_id count(*) +1 1 1 1 1 1 1 100 +1 1 1 1 1 2 1 100 +1 1 1 1 1 3 1 100 +1 1 1 2 1 1 1 100 +1 1 1 2 1 2 1 100 +1 1 1 2 1 3 1 100 +2 2 2 1 2 1 2 100 +2 2 2 1 2 2 2 100 +2 2 2 1 2 3 2 100 +2 2 2 2 2 1 2 100 +2 2 2 2 2 2 2 100 +2 2 2 2 2 3 2 100 +3 3 3 1 3 1 3 100 +3 3 3 1 3 2 3 100 +3 3 3 1 3 3 3 100 +3 3 3 2 3 1 3 100 +3 3 3 2 3 2 3 100 +3 3 3 2 3 3 3 100 +4 4 4 1 4 1 4 100 +4 4 4 1 4 2 4 100 +4 4 4 1 4 3 4 100 +4 4 4 2 4 1 4 100 +4 4 4 2 4 2 4 100 +4 4 4 2 4 3 4 100 +5 5 5 1 5 1 5 100 +5 5 5 1 5 2 5 100 +5 5 5 1 5 3 5 100 +5 5 5 2 5 1 5 100 +5 5 5 2 5 2 5 100 +5 5 5 2 5 3 5 100 +set join_cache_level=4; +explain select * +from +( +(t1 left join t2 on t2.a=t1.b) +left join +t3 +on t3.a=t1.b +) +left join +( +select grp_id, count(*) +from t10 left join t11 on t11.col1=t10.col1 +group by grp_id +)dt +on dt.grp_id=t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where +1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where +2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100 +2 LATERAL DERIVED t11 hash_ALL NULL #hash#$hj 5 test.t10.col1 10 Using where; Using join buffer (flat, BNLH join) +select * +from +( +(t1 left join t2 on t2.a=t1.b) +left join +t3 +on t3.a=t1.b +) +left join +( +select grp_id, count(*) +from t10 left join t11 on t11.col1=t10.col1 +group by grp_id +)dt +on dt.grp_id=t1.b; +a b a b a b grp_id count(*) +1 1 1 1 1 1 1 100 +1 1 1 1 1 2 1 100 +1 1 1 1 1 3 1 100 +1 1 1 2 1 1 1 100 +1 1 1 2 1 2 1 100 +1 1 1 2 1 3 1 100 +2 2 2 1 2 1 2 100 +2 2 2 1 2 2 2 100 +2 2 2 1 2 3 2 100 +2 2 2 2 2 1 2 100 +2 2 2 2 2 2 2 100 +2 2 2 2 2 3 2 100 +3 3 3 1 3 1 3 100 +3 3 3 1 3 2 3 100 +3 3 3 1 3 3 3 100 +3 3 3 2 3 1 3 100 +3 3 3 2 3 2 3 100 +3 3 3 2 3 3 3 100 +4 4 4 1 4 1 4 100 +4 4 4 1 4 2 4 100 +4 4 4 1 4 3 4 100 +4 4 4 2 4 1 4 100 +4 4 4 2 4 2 4 100 +4 4 4 2 4 3 4 100 +5 5 5 1 5 1 5 100 +5 5 5 1 5 2 5 100 +5 5 5 1 5 3 5 100 +5 5 5 2 5 1 5 100 +5 5 5 2 5 2 5 100 +5 5 5 2 5 3 5 100 +set join_cache_level=default; +drop index a on t2; +drop index a on t3; +explain select * +from +( +(t1 left join t2 on t2.a=t1.b) +left join +t3 +on t3.a=t1.b +) +left join +( +select grp_id, count(*) +from t10 left join t11 on t11.col1=t10.col1 +group by grp_id +)dt +on dt.grp_id=t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 15 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 1000 Using where +2 DERIVED t10 ALL grp_id NULL NULL NULL 10000 Using temporary; Using filesort +2 DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +select * +from +( +(t1 left join t2 on t2.a=t1.b) +left join +t3 +on t3.a=t1.b +) +left join +( +select grp_id, count(*) +from t10 left join t11 on t11.col1=t10.col1 +group by grp_id +)dt +on dt.grp_id=t1.b; +a b a b a b grp_id count(*) +1 1 1 1 1 1 1 100 +1 1 1 2 1 1 1 100 +1 1 1 1 1 2 1 100 +1 1 1 2 1 2 1 100 +1 1 1 1 1 3 1 100 +1 1 1 2 1 3 1 100 +2 2 2 1 2 1 2 100 +2 2 2 2 2 1 2 100 +2 2 2 1 2 2 2 100 +2 2 2 2 2 2 2 100 +2 2 2 1 2 3 2 100 +2 2 2 2 2 3 2 100 +3 3 3 1 3 1 3 100 +3 3 3 2 3 1 3 100 +3 3 3 1 3 2 3 100 +3 3 3 2 3 2 3 100 +3 3 3 1 3 3 3 100 +3 3 3 2 3 3 3 100 +4 4 4 1 4 1 4 100 +4 4 4 2 4 1 4 100 +4 4 4 1 4 2 4 100 +4 4 4 2 4 2 4 100 +4 4 4 1 4 3 4 100 +4 4 4 2 4 3 4 100 +5 5 5 1 5 1 5 100 +5 5 5 2 5 1 5 100 +5 5 5 1 5 2 5 100 +5 5 5 2 5 2 5 100 +5 5 5 1 5 3 5 100 +5 5 5 2 5 3 5 100 +drop table t1,t2,t3; +drop table t10, t11; +# +# MDEV-31194: Server crash or assertion failure with join_cache_level=4 +# (a followup to the above bug, MDEV-26301) +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (3),(4); +CREATE TABLE t2 (id INT PRIMARY KEY) ENGINE=Aria; +INSERT INTO t2 VALUES (1),(2); +set @tmp1= @@optimizer_switch, @tmp2= @@join_cache_level; +set +optimizer_switch= 'derived_with_keys=off', +join_cache_level= 4; +SELECT t1.* FROM t1 JOIN (SELECT id, COUNT(*) FROM t2 GROUP BY id) sq ON sq.id= t1.a; +a +set optimizer_switch= @tmp1, join_cache_level= @tmp2; +DROP TABLE t1, t2; +# End of 10.4 tests SET GLOBAL innodb_stats_persistent=@save_innodb_stats_persistent; diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 1ebe27cd12c..6ccbc31d886 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -233,4 +233,239 @@ drop table t3, t4; --echo # End of 10.3 tests + +--echo # +--echo # MDEV-26301: Split optimization refills temporary table too many times +--echo # + +# 5 values +create table t1(a int, b int); +insert into t1 select seq,seq from seq_1_to_5; + +# 5 value groups of size 2 each +create table t2(a int, b int, key(a)); +insert into t2 +select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B; + +# 5 value groups of size 3 each +create table t3(a int, b int, key(a)); +insert into t3 +select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B; + +analyze table t1,t2,t3 persistent for all; + +explain +select * from + (t1 left join t2 on t2.a=t1.b) left join t3 on t3.a=t1.b; + +# Now, create tables for Groups. + +create table t10 ( + grp_id int, + col1 int, + key(grp_id) +); + +# 100 groups of 100 values each +insert into t10 +select + A.seq, + B.seq +from + seq_1_to_100 A, + seq_1_to_100 B; + +# and X10 multiplier + +create table t11 ( + col1 int, + col2 int +); +insert into t11 +select A.seq, A.seq from seq_1_to_10 A; + +analyze table t10,t11 persistent for all; + +let $q1= +select * from + ( + (t1 left join t2 on t2.a=t1.b) + left join t3 on t3.a=t1.b + ) left join (select grp_id, count(*) + from t10 left join t11 on t11.col1=t10.col1 + group by grp_id) T on T.grp_id=t1.b; + +eval +explain $q1; + +--echo # The important part in the below output is: +--echo # "lateral": 1, +--echo # "query_block": { +--echo # "select_id": 2, +--echo # "r_loops": 5, <-- must be 5, not 30. +--source include/analyze-format.inc + +eval +analyze format=json $q1; + +create table t21 (pk int primary key); +insert into t21 values (1),(2),(3); + +create table t22 (pk int primary key); +insert into t22 values (1),(2),(3); + +# Same as above but throw in a couple of const tables. +explain +select * from + t21, t22, + ( + (t1 left join t2 on t2.a=t1.b) + left join t3 on t3.a=t1.b + ) left join (select grp_id, count(*) + from t10 left join t11 on t11.col1=t10.col1 + group by grp_id) T on T.grp_id=t1.b +where + t21.pk=1 and t22.pk=2; + +explain +select * from + t21, + ( + (t1 left join t2 on t2.a=t1.b) + left join t3 on t3.a=t1.b + ) left join (select grp_id, count(*) + from + t22 join t10 left join t11 on t11.col1=t10.col1 + where + t22.pk=1 + group by grp_id) T on T.grp_id=t1.b +where + t21.pk=1; + +# And also add a non-const table + +create table t5 ( + pk int primary key + ); +insert into t5 select seq from seq_1_to_1000; + +explain +select * from + t21, + ( + (((t1 join t5 on t5.pk=t1.b)) left join t2 on t2.a=t1.b) + left join t3 on t3.a=t1.b + ) left join (select grp_id, count(*) + from + t22 join t10 left join t11 on t11.col1=t10.col1 + where + t22.pk=1 + group by grp_id) T on T.grp_id=t1.b +where + t21.pk=1; + +drop table t1,t2,t3,t5, t10, t11, t21, t22; + +# 5 values +create table t1(a int, b int); +insert into t1 select seq,seq from seq_1_to_5; + +# 5 value groups of size 2 each +create table t2(a int, b int, key(a)); +insert into t2 +select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B; + +# 5 value groups of size 3 each +create table t3(a int, b int, key(a)); +insert into t3 +select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B; + +analyze table t1,t2,t3 persistent for all; + +create table t10 ( + grp_id int, + col1 int, + key(grp_id) +); + +# 100 groups of 100 values each +insert into t10 +select + A.seq, + B.seq +from + seq_1_to_100 A, + seq_1_to_100 B; + +# and X10 multiplier + +create table t11 ( + col1 int, + col2 int +); +insert into t11 +select A.seq, A.seq from seq_1_to_10 A; + +analyze table t10,t11 persistent for all; + +let $q= +select * +from + ( + (t1 left join t2 on t2.a=t1.b) + left join + t3 + on t3.a=t1.b + ) + left join + ( + select grp_id, count(*) + from t10 left join t11 on t11.col1=t10.col1 + group by grp_id + )dt + on dt.grp_id=t1.b; + +eval explain $q; +eval $q; + +set join_cache_level=4; +eval explain $q; +eval $q; + +set join_cache_level=default; + +drop index a on t2; +drop index a on t3; + +eval explain $q; +eval $q; + +drop table t1,t2,t3; +drop table t10, t11; + + +--echo # +--echo # MDEV-31194: Server crash or assertion failure with join_cache_level=4 +--echo # (a followup to the above bug, MDEV-26301) +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (3),(4); + +CREATE TABLE t2 (id INT PRIMARY KEY) ENGINE=Aria; +INSERT INTO t2 VALUES (1),(2); + +set @tmp1= @@optimizer_switch, @tmp2= @@join_cache_level; +set + optimizer_switch= 'derived_with_keys=off', + join_cache_level= 4; + +SELECT t1.* FROM t1 JOIN (SELECT id, COUNT(*) FROM t2 GROUP BY id) sq ON sq.id= t1.a; + +set optimizer_switch= @tmp1, join_cache_level= @tmp2; + +# Cleanup +DROP TABLE t1, t2; + +--echo # End of 10.4 tests + SET GLOBAL innodb_stats_persistent=@save_innodb_stats_persistent; diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index c367b882e7f..4cb567ccab0 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -4171,3 +4171,71 @@ deallocate prepare stmt; drop view v; drop table t1,t2,t3; # End of 10.4 tests +# +# MDEV-31143: view with ORDER BY used in query with rownum() in WHERE +# +create table t1 (id int primary key); +insert into t1 values (3), (7), (1); +create table t2 (a int); +insert into t2 values (2), (4); +create view v as select a from t2 order by a; +set big_tables= 1; +Warnings: +Warning 1287 '@@big_tables' is deprecated and will be removed in a future release +select t1.id from v, t1 where rownum() = 1 group by t1.id; +id +1 +set big_tables=default; +Warnings: +Warning 1287 '@@big_tables' is deprecated and will be removed in a future release +drop view v; +drop table t1, t2; +# +# MDEV-31162: multi-table mergeable view with ORDER BY used +# in query with rownum() in WHERE +# +create table t1 (a INT) engine=MyISAM; +insert into t1 values (1),(2); +create table t2 (b INT) engine=MyISAM; +insert into t2 values (3),(4); +create view v1 AS select * from t1 join t2 order by b; +explain select * from v1 where rownum() <= 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +select * from v1 where rownum() <= 2; +a b +1 3 +2 3 +prepare stmt from "select * from v1 where rownum() <= 2"; +execute stmt; +a b +1 3 +2 3 +execute stmt; +a b +1 3 +2 3 +deallocate prepare stmt; +create view v2 AS select * from t1 join t2 order by b/a; +explain select * from v2 where rownum() <= 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +select * from v2 where rownum() <= 2; +a b +2 3 +1 3 +prepare stmt from "select * from v2 where rownum() <= 2"; +execute stmt; +a b +2 3 +1 3 +execute stmt; +a b +2 3 +1 3 +deallocate prepare stmt; +drop view v1,v2; +drop table t1,t2; +# End of 10.6 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index 5422fbcfd1d..777389a9844 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2752,3 +2752,65 @@ drop view v; drop table t1,t2,t3; --echo # End of 10.4 tests + +--echo # +--echo # MDEV-31143: view with ORDER BY used in query with rownum() in WHERE +--echo # + +create table t1 (id int primary key); +insert into t1 values (3), (7), (1); + +create table t2 (a int); +insert into t2 values (2), (4); + +create view v as select a from t2 order by a; + +set big_tables= 1; +select t1.id from v, t1 where rownum() = 1 group by t1.id; + +set big_tables=default; + +drop view v; +drop table t1, t2; + +--echo # +--echo # MDEV-31162: multi-table mergeable view with ORDER BY used +--echo # in query with rownum() in WHERE +--echo # + +create table t1 (a INT) engine=MyISAM; +insert into t1 values (1),(2); + +create table t2 (b INT) engine=MyISAM; +insert into t2 values (3),(4); + +create view v1 AS select * from t1 join t2 order by b; +let $q1= +select * from v1 where rownum() <= 2; + +eval explain $q1; +--sorted_result +eval $q1; + +eval prepare stmt from "$q1"; +--sorted_result +execute stmt; +--sorted_result +execute stmt; +deallocate prepare stmt; + +create view v2 AS select * from t1 join t2 order by b/a; +let $q2= +select * from v2 where rownum() <= 2; + +eval explain $q2; +eval $q2; +eval prepare stmt from "$q2"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop view v1,v2; +drop table t1,t2; + +--echo # End of 10.6 tests diff --git a/mysql-test/main/distinct.result b/mysql-test/main/distinct.result index 0c5eccad754..fa9f0259a0f 100644 --- a/mysql-test/main/distinct.result +++ b/mysql-test/main/distinct.result @@ -1093,6 +1093,7 @@ sum(distinct 1) sum(t1.d) > 5 c 1 1 0 1 0 5 1 1 6 +SET @sort_buffer_size_save= @@sort_buffer_size; set @@sort_buffer_size=1024; insert into t1 select -seq,-seq from seq_1_to_100; select distinct sum(distinct 1), sum(t1.d) > 2, length(group_concat(t1.d)) > 1000 from (t1 e join t1) group by t1.c having t1.c > -2 ; @@ -1106,6 +1107,19 @@ sum(distinct 1) sum(t1.d) > 2 length(group_concat(t1.d)) > 1000 c 1 1 0 5 1 1 0 6 drop table t1; +set @@sort_buffer_size=@sort_buffer_size_save; +# +# MDEV-31113 Server crashes in store_length / Type_handler_string_result::make_sort_key +# with DISTINCT and group function +# +CREATE TABLE t (f INT); +INSERT INTO t VALUES (1),(2); +SELECT DISTINCT CONVERT(STDDEV(f), CHAR(16)) AS f1, UUID() AS f2 FROM t GROUP BY f2 WITH ROLLUP; +f1 f2 +0.0000 # +0.0000 # +0.5000 # +DROP TABLE t; # End of 10.4 tests # # MDEV-27382: OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN diff --git a/mysql-test/main/distinct.test b/mysql-test/main/distinct.test index 9039edff20f..893e2dcc9a7 100644 --- a/mysql-test/main/distinct.test +++ b/mysql-test/main/distinct.test @@ -834,11 +834,24 @@ select distinct sum(distinct 1), sum(t1.d) > 5 from (t1 e join t1) group by t1.c select distinct sum(distinct 1), sum(t1.d) > 5, t1.c from (t1 e join t1) group by t1.c; # Force usage of remove_dup_with_compare() algorithm +SET @sort_buffer_size_save= @@sort_buffer_size; set @@sort_buffer_size=1024; insert into t1 select -seq,-seq from seq_1_to_100; select distinct sum(distinct 1), sum(t1.d) > 2, length(group_concat(t1.d)) > 1000 from (t1 e join t1) group by t1.c having t1.c > -2 ; select distinct sum(distinct 1), sum(t1.d) > 2, length(group_concat(t1.d)) > 1000,t1.c from (t1 e join t1) group by t1.c having t1.c > -2; drop table t1; +set @@sort_buffer_size=@sort_buffer_size_save; + +--echo # +--echo # MDEV-31113 Server crashes in store_length / Type_handler_string_result::make_sort_key +--echo # with DISTINCT and group function +--echo # + +CREATE TABLE t (f INT); +INSERT INTO t VALUES (1),(2); +--replace_column 2 # +SELECT DISTINCT CONVERT(STDDEV(f), CHAR(16)) AS f1, UUID() AS f2 FROM t GROUP BY f2 WITH ROLLUP; +DROP TABLE t; --echo # End of 10.4 tests diff --git a/mysql-test/main/explain_innodb.result b/mysql-test/main/explain_innodb.result index b46665c279c..c44d7baadea 100644 --- a/mysql-test/main/explain_innodb.result +++ b/mysql-test/main/explain_innodb.result @@ -18,3 +18,21 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DERIVED t1 range NULL id 53 NULL 2 Using index for group-by SET GLOBAL slow_query_log = @sql_tmp; drop table t1; +# +# MDEV-31181: Server crash in subselect_uniquesubquery_engine::print +# upon EXPLAIN EXTENDED DELETE +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (pk INT PRIMARY KEY); +INSERT INTO t2 VALUES (1),(2); +EXPLAIN EXTENDED DELETE FROM t1 WHERE a IN (SELECT pk FROM t2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index +Warnings: +Note 1003 /* select#1 */ delete from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a`)))) +drop table t1, t2; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/explain_innodb.test b/mysql-test/main/explain_innodb.test index 2c29a6e26da..3dcad4c2d49 100644 --- a/mysql-test/main/explain_innodb.test +++ b/mysql-test/main/explain_innodb.test @@ -18,3 +18,22 @@ SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; SET GLOBAL slow_query_log = @sql_tmp; drop table t1; + + +--echo # +--echo # MDEV-31181: Server crash in subselect_uniquesubquery_engine::print +--echo # upon EXPLAIN EXTENDED DELETE +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (pk INT PRIMARY KEY); +INSERT INTO t2 VALUES (1),(2); + +EXPLAIN EXTENDED DELETE FROM t1 WHERE a IN (SELECT pk FROM t2); + +drop table t1, t2; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/insert_innodb.result b/mysql-test/main/insert_innodb.result index 314412bcfdd..b8f3979d2ba 100644 --- a/mysql-test/main/insert_innodb.result +++ b/mysql-test/main/insert_innodb.result @@ -51,7 +51,7 @@ CREATE TEMPORARY TABLE v0 ( v1 TEXT ( 15 ) CHAR SET BINARY NOT NULL NOT NULL UNI ERROR HY000: Field 'v1' doesn't have a default value CREATE TEMPORARY TABLE t1 (i TEXT(15) NOT NULL DEFAULT '' UNIQUE CHECK (i)) engine=innodb REPLACE SELECT NULL AS a; -ERROR HY000: Field 'DB_ROW_HASH_1' doesn't have a default value +ERROR 23000: CONSTRAINT `t1.i` failed for `test`.`t1` # # End of 10.5 tests # diff --git a/mysql-test/main/insert_innodb.test b/mysql-test/main/insert_innodb.test index b5a9fc72c07..56ddb2eafbf 100644 --- a/mysql-test/main/insert_innodb.test +++ b/mysql-test/main/insert_innodb.test @@ -71,7 +71,7 @@ DROP TABLE t2, t1; --error ER_NO_DEFAULT_FOR_FIELD CREATE TEMPORARY TABLE v0 ( v1 TEXT ( 15 ) CHAR SET BINARY NOT NULL NOT NULL UNIQUE CHECK ( v1 ) ) REPLACE SELECT NULL AS v3 , 74 AS v2 ; ---error ER_NO_DEFAULT_FOR_FIELD +--error ER_CONSTRAINT_FAILED CREATE TEMPORARY TABLE t1 (i TEXT(15) NOT NULL DEFAULT '' UNIQUE CHECK (i)) engine=innodb REPLACE SELECT NULL AS a; diff --git a/mysql-test/main/insert_update.result b/mysql-test/main/insert_update.result index 68a1003ad85..83344971c59 100644 --- a/mysql-test/main/insert_update.result +++ b/mysql-test/main/insert_update.result @@ -412,3 +412,44 @@ select if( @stamp1 = @stamp2, "correct", "wrong"); if( @stamp1 = @stamp2, "correct", "wrong") correct drop table t1; +# +# MDEV-31164 default current_timestamp() not working when used INSERT ON DUPLICATE KEY in some cases +# +set timestamp=unix_timestamp('2000-10-20 0:0:0'); +create table t1 (pk integer primary key, val varchar(20) not null, ts timestamp); +insert t1 (pk, val) values(1, 'val1'); +select * from t1; +pk val ts +1 val1 2000-10-20 00:00:00 +set timestamp=unix_timestamp('2000-10-20 1:0:0'); +insert t1 (pk, val) select 2, 'val3' union select 3, 'val4' + on duplicate key update ts=now(); +select * from t1; +pk val ts +1 val1 2000-10-20 00:00:00 +2 val3 2000-10-20 01:00:00 +3 val4 2000-10-20 01:00:00 +set timestamp=unix_timestamp('2000-10-20 2:0:0'); +insert t1 (pk, val) select 1, 'val1' union select 4, 'val2' + on duplicate key update ts=now(); +select * from t1; +pk val ts +1 val1 2000-10-20 02:00:00 +2 val3 2000-10-20 01:00:00 +3 val4 2000-10-20 01:00:00 +4 val2 2000-10-20 02:00:00 +set timestamp=unix_timestamp('2000-10-20 3:0:0'); +insert t1 (pk, val) select 5, 'val1' union select 1, 'val2' + on duplicate key update ts=now(); +select * from t1; +pk val ts +1 val1 2000-10-20 03:00:00 +2 val3 2000-10-20 01:00:00 +3 val4 2000-10-20 01:00:00 +4 val2 2000-10-20 02:00:00 +5 val1 2000-10-20 03:00:00 +drop table t1; +set timestamp=default; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/insert_update.test b/mysql-test/main/insert_update.test index 06e16be84d7..25953938ad1 100644 --- a/mysql-test/main/insert_update.test +++ b/mysql-test/main/insert_update.test @@ -311,3 +311,29 @@ insert into t1(f1) values(1) on duplicate key update f1=1; select @stamp2:=f2 from t1; select if( @stamp1 = @stamp2, "correct", "wrong"); drop table t1; + +--echo # +--echo # MDEV-31164 default current_timestamp() not working when used INSERT ON DUPLICATE KEY in some cases +--echo # +set timestamp=unix_timestamp('2000-10-20 0:0:0'); +create table t1 (pk integer primary key, val varchar(20) not null, ts timestamp); +insert t1 (pk, val) values(1, 'val1'); +select * from t1; +set timestamp=unix_timestamp('2000-10-20 1:0:0'); +insert t1 (pk, val) select 2, 'val3' union select 3, 'val4' + on duplicate key update ts=now(); +select * from t1; +set timestamp=unix_timestamp('2000-10-20 2:0:0'); +insert t1 (pk, val) select 1, 'val1' union select 4, 'val2' + on duplicate key update ts=now(); +select * from t1; +set timestamp=unix_timestamp('2000-10-20 3:0:0'); +insert t1 (pk, val) select 5, 'val1' union select 1, 'val2' + on duplicate key update ts=now(); +select * from t1; +drop table t1; +set timestamp=default; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/join_cache_cardinality.result b/mysql-test/main/join_cache_cardinality.result new file mode 100644 index 00000000000..0a76080e601 --- /dev/null +++ b/mysql-test/main/join_cache_cardinality.result @@ -0,0 +1,105 @@ +create table t1 (a int, b int, c int); +insert into t1 select seq,seq/2, seq/4 from seq_1_to_100; +create table t2 (a int, b int, c int); +insert into t2 select seq, seq/2, seq/4 from seq_1_to_200; +analyze table t1,t2 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +set optimizer_trace=1; +set join_cache_level=6; +set optimizer_switch='hash_join_cardinality=on'; +explain select * +from t1, t2 +where t1.a=t2.a and t1.a=t2.b and t1.c=t2.c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE t2 hash_ALL NULL #hash#$hj 15 test.t1.a,test.t1.a,test.t1.c 200 Using where; Using join buffer (flat, BNLH join) +set @json= (select trace from information_schema.optimizer_trace); +select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS; +JS +[ + { + "hash_join_columns": + [ + { + "field": "a", + "avg_frequency": 1 + }, + { + "field": "b", + "avg_frequency": 2 + }, + { + "field": "c", + "avg_frequency": 3.9216 + } + ], + "rows": 1 + } +] +select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan')) +as ROWS_FOR_PLAN; +ROWS_FOR_PLAN +[100] +explain select * +from t1, t2 where t1.c=t2.c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.c 200 Using where; Using join buffer (flat, BNLH join) +set @json= (select trace from information_schema.optimizer_trace); +select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS; +JS +[ + { + "hash_join_columns": + [ + { + "field": "c", + "avg_frequency": 3.9216 + } + ], + "rows": 3.9216 + } +] +select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan')) +as ROWS_FOR_PLAN; +ROWS_FOR_PLAN +[392.16] +explain select * +from t1 straight_join t2 where t1.c=t2.c and t2.a<30; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.c 200 Using where; Using join buffer (flat, BNLH join) +set @json= (select trace from information_schema.optimizer_trace); +# Note that rows is the same: +select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS; +JS +[ + { + "hash_join_columns": + [ + { + "field": "c", + "avg_frequency": 3.9216 + } + ], + "rows": 3.9216 + } +] +# Despite available selectivity: +select json_detailed(json_extract(@json, '$**.selectivity_for_columns')) as JS; +JS +[ + [ + { + "column_name": "a", + "ranges": + ["NULL < a < 30"], + "selectivity_from_histogram": 0.1484375 + } + ] +] +drop table t1,t2; diff --git a/mysql-test/main/join_cache_cardinality.test b/mysql-test/main/join_cache_cardinality.test new file mode 100644 index 00000000000..b178810b807 --- /dev/null +++ b/mysql-test/main/join_cache_cardinality.test @@ -0,0 +1,41 @@ +--source include/have_sequence.inc + +# Embedded doesn't have optimizer trace: +--source include/not_embedded.inc + +create table t1 (a int, b int, c int); +insert into t1 select seq,seq/2, seq/4 from seq_1_to_100; + +create table t2 (a int, b int, c int); +insert into t2 select seq, seq/2, seq/4 from seq_1_to_200; + +analyze table t1,t2 persistent for all; + +set optimizer_trace=1; +set join_cache_level=6; +set optimizer_switch='hash_join_cardinality=on'; +explain select * +from t1, t2 +where t1.a=t2.a and t1.a=t2.b and t1.c=t2.c; + +set @json= (select trace from information_schema.optimizer_trace); +select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS; +select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan')) +as ROWS_FOR_PLAN; + +explain select * +from t1, t2 where t1.c=t2.c; +set @json= (select trace from information_schema.optimizer_trace); +select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS; +select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan')) +as ROWS_FOR_PLAN; + +explain select * +from t1 straight_join t2 where t1.c=t2.c and t2.a<30; +set @json= (select trace from information_schema.optimizer_trace); +--echo # Note that rows is the same: +select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS; + +--echo # Despite available selectivity: +select json_detailed(json_extract(@json, '$**.selectivity_for_columns')) as JS; +drop table t1,t2; diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 283ea870a5f..ed13c668ec3 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -451,6 +451,12 @@ a b 1 xxx drop table t1; # +# MDEV-22756 SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value +# +create table t1 (f text not null, unique (f)); +insert into t1 (f) select 'f'; +drop table t1; +# # End of 10.4 tests # # diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index 86003a6881c..3c1c101ffa0 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -444,6 +444,13 @@ select * from t1; drop table t1; --echo # +--echo # MDEV-22756 SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value +--echo # +create table t1 (f text not null, unique (f)); +insert into t1 (f) select 'f'; +drop table t1; + +--echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/main/mysqld--help,win.rdiff b/mysql-test/main/mysqld--help,win.rdiff index 337755252ba..bcfefbab1a8 100644 --- a/mysql-test/main/mysqld--help,win.rdiff +++ b/mysql-test/main/mysqld--help,win.rdiff @@ -1,6 +1,12 @@ ---- a/mysql-test/r/mysqld--help.result -+++ b/mysql-test/r/mysqld--help.result -@@ -647,6 +646,7 @@ +@@ -180,6 +180,7 @@ + --console Write error output on screen; don't remove the console + window on windows. + --core-file Write core on crashes ++ (Defaults to on; use --skip-core-file to disable.) + -h, --datadir=name Path to the database root directory + --date-format=name The DATE format (ignored) + --datetime-format=name +@@ -650,6 +651,7 @@ Use MySQL-5.6 (instead of MariaDB-5.3) format for TIME, DATETIME, TIMESTAMP columns. (Defaults to on; use --skip-mysql56-temporal-format to disable.) @@ -8,7 +14,7 @@ --net-buffer-length=# Buffer length for TCP/IP and socket communication --net-read-timeout=# -@@ -1236,6 +1236,10 @@ +@@ -1281,6 +1283,10 @@ Log slow queries to given log file. Defaults logging to 'hostname'-slow.log. Must be enabled to activate other slow log options @@ -19,7 +25,7 @@ --socket=name Socket file to use for connection --sort-buffer-size=# Each thread that needs to do a sort allocates a buffer of -@@ -1260,6 +1264,7 @@ +@@ -1305,6 +1311,7 @@ deleting or updating every row in a table. --stack-trace Print a symbolic stack trace on failure (Defaults to on; use --skip-stack-trace to disable.) @@ -27,7 +33,7 @@ --standard-compliant-cte Allow only CTEs compliant to SQL standard (Defaults to on; use --skip-standard-compliant-cte to disable.) -@@ -1330,6 +1335,11 @@ +@@ -1380,6 +1387,11 @@ --thread-pool-max-threads=# Maximum allowed number of worker threads in the thread pool @@ -39,7 +45,7 @@ --thread-pool-oversubscribe=# How many additional active worker threads in a group are allowed. -@@ -1370,8 +1380,8 @@ +@@ -1418,8 +1430,8 @@ automatically convert it to an on-disk MyISAM or Aria table. -t, --tmpdir=name Path for temporary files. Several paths may be specified, @@ -50,7 +56,7 @@ --transaction-alloc-block-size=# Allocation block size for transactions to be stored in binary log -@@ -1587,6 +1596,7 @@ +@@ -1634,6 +1646,7 @@ myisam-stats-method NULLS_UNEQUAL myisam-use-mmap FALSE mysql56-temporal-format TRUE @@ -58,7 +64,7 @@ net-buffer-length 16384 net-read-timeout 30 net-retry-count 10 -@@ -1726,6 +1736,7 @@ +@@ -1788,6 +1801,7 @@ slave-type-conversions slow-launch-time 2 slow-query-log FALSE @@ -66,8 +72,8 @@ sort-buffer-size 2097152 sql-mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION sql-safe-updates FALSE -@@ -1753,6 +1764,8 @@ - thread-cache-size 151 +@@ -1814,6 +1828,8 @@ + thread-pool-exact-stats FALSE thread-pool-idle-timeout 60 thread-pool-max-threads 65536 +thread-pool-min-threads 1 diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index a3dac7fcfcb..5c1a8840fa5 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -185,7 +185,7 @@ The following specify which files/extra groups are read (specified before remain ALWAYS --console Write error output on screen; don't remove the console window on windows. - --core-file Write core on errors. + --core-file Write core on crashes -h, --datadir=name Path to the database root directory --date-format=name The DATE format (ignored) --datetime-format=name @@ -734,7 +734,8 @@ The following specify which files/extra groups are read (specified before remain extended_keys, exists_to_in, orderby_uses_equalities, condition_pushdown_for_derived, split_materialized, condition_pushdown_for_subquery, rowid_filter, - condition_pushdown_from_having, not_null_range_scan + condition_pushdown_from_having, not_null_range_scan, + hash_join_cardinality --optimizer-trace=name Controls tracing of the Optimizer: optimizer_trace=option=val[,option=val...], where option @@ -1505,6 +1506,7 @@ column-compression-zlib-wrap FALSE completion-type NO_CHAIN concurrent-insert AUTO console TRUE +core-file TRUE date-format %Y-%m-%d datetime-format %Y-%m-%d %H:%i:%s deadlock-search-depth-long 15 diff --git a/mysql-test/main/mysqltest_tracking_info.result b/mysql-test/main/mysqltest_tracking_info.result index 61bb3f2d1e2..791f2a2b4a4 100644 --- a/mysql-test/main/mysqltest_tracking_info.result +++ b/mysql-test/main/mysqltest_tracking_info.result @@ -38,7 +38,7 @@ SET @@session.session_track_system_variables='optimizer_switch'; set optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off'; -- Tracker : SESSION_TRACK_SYSTEM_VARIABLES -- optimizer_switch --- index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off +-- index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off Warnings: Warning 1681 'engine_condition_pushdown=on' is deprecated and will be removed in a future release diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index c747423c99b..725b095c72e 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -457,6 +457,11 @@ select * from v2 { ] }, { + "check_split_materialized": { + "not_applicable": "no candidate field can be accessed through ref" + } + }, + { "best_join_order": ["t1"] }, { @@ -780,6 +785,11 @@ explain select * from v1 { ] }, { + "check_split_materialized": { + "not_applicable": "group list has no candidates" + } + }, + { "best_join_order": ["t1"] }, { @@ -8904,6 +8914,110 @@ SET optimizer_trace=DEFAULT; DROP VIEW v; DROP TABLE t; # +# MDEV-26301: Split optimization improvements: Optimizer Trace coverage +# +create table t1(a int, b int); +insert into t1 select seq,seq from seq_1_to_5; +create table t2(a int, b int, key(a)); +insert into t2 +select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B; +create table t3(a int, b int, key(a)); +insert into t3 +select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B; +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status Table is already up to date +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status Table is already up to date +create table t10 ( +grp_id int, +col1 int, +key(grp_id) +); +insert into t10 +select +A.seq, +B.seq +from +seq_1_to_100 A, +seq_1_to_100 B; +create table t11 ( +col1 int, +col2 int +); +insert into t11 +select A.seq, A.seq from seq_1_to_10 A; +analyze table t10,t11 persistent for all; +Table Op Msg_type Msg_text +test.t10 analyze status Engine-independent statistics collected +test.t10 analyze status Table is already up to date +test.t11 analyze status Engine-independent statistics collected +test.t11 analyze status OK +set optimizer_trace=1; +explain +select * from +( +(t1 left join t2 on t2.a=t1.b) +left join t3 on t3.a=t1.b +) left join (select grp_id, count(*) +from t10 left join t11 on t11.col1=t10.col1 +group by grp_id) T on T.grp_id=t1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where +1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where +2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100 +2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +select json_detailed(json_extract(trace, '$**.check_split_materialized')) as JS +from information_schema.optimizer_trace; +JS +[ + { + "split_candidates": + ["t10.grp_id"] + } +] +select +json_detailed( +json_remove( +json_extract(trace, '$**.choose_best_splitting') +, '$[0].split_plan_search[0]' + ) +) as JS +from information_schema.optimizer_trace; +JS +[ + { + "considered_keys": + [ + { + "table_name": "t10", + "index": "grp_id", + "rec_per_key": 100, + "param_tables": 1 + } + ], + "refills": 5, + "spl_pd_boundary": 2, + "split_plan_search": + [], + "lead_table": "t10", + "index": "grp_id", + "parts": 1, + "split_sel": 0.001, + "cost": 2535.968504, + "unsplit_cost": 253440.0075, + "records": 100, + "chosen": true + } +] +drop table t1,t2,t3,t10,t11; +set optimizer_trace=DEFAULT; +# # End of 10.4 tests # set optimizer_trace='enabled=on'; @@ -9326,69 +9440,73 @@ from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.choose_best_splitting')) [ - [ - { - "considered_execution_plans": - [ - { - "plan_prefix": - [], - "table": "t2", - "best_access_path": + { + "considered_keys": + [ + { + "table_name": "t2", + "index": "idx_a", + "rec_per_key": 1.8367, + "param_tables": 1 + } + ], + "refills": 4, + "spl_pd_boundary": 2, + "split_plan_search": + [ + { + "considered_execution_plans": + [ { - "considered_access_paths": - [ + "plan_prefix": + [], + "table": "t2", + "best_access_path": + { + "considered_access_paths": + [ + { + "access_type": "ref", + "index": "idx_a", + "used_range_estimates": false, + "cause": "not available", + "rows": 1.8367, + "cost": 2.000585794, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ], + "chosen_access_method": { - "access_type": "ref", - "index": "idx_a", - "used_range_estimates": false, - "cause": "not available", - "rows": 1.8367, + "type": "ref", + "records": 1.8367, "cost": 2.000585794, - "chosen": true - }, - { - "type": "scan", - "chosen": false, - "cause": "cost" + "uses_join_buffering": false } - ], - "chosen_access_method": - { - "type": "ref", - "records": 1.8367, - "cost": 2.000585794, - "uses_join_buffering": false - } - }, - "rows_for_plan": 1.8367, - "cost_for_plan": 2.367925794, - "cost_for_sorting": 1.8367 - } - ] - }, - { - "best_splitting": - { - "table": "t2", - "key": "idx_a", - "record_count": 4, - "cost": 2.488945919, - "unsplit_cost": 25.72361682 + }, + "rows_for_plan": 1.8367, + "cost_for_plan": 2.367925794, + "cost_for_sorting": 1.8367 + } + ] } - } - ] -] -select -json_detailed(json_extract(trace, '$**.lateral_derived')) -from -information_schema.optimizer_trace; -json_detailed(json_extract(trace, '$**.lateral_derived')) -[ + ], + "lead_table": "t2", + "index": "idx_a", + "parts": 1, + "split_sel": 0.020407778, + "cost": 2.488945919, + "unsplit_cost": 25.72361682, + "records": 1, + "chosen": true + }, { - "startup_cost": 9.955783677, - "splitting_cost": 2.488945919, - "records": 1 + "considered_keys": + [] } ] drop table t1,t2; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index a30a536125a..171f5a2627f 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -702,6 +702,76 @@ DROP VIEW v; DROP TABLE t; --echo # +--echo # MDEV-26301: Split optimization improvements: Optimizer Trace coverage +--echo # + +# 5 values +create table t1(a int, b int); +insert into t1 select seq,seq from seq_1_to_5; + +# 5 value groups of size 2 each +create table t2(a int, b int, key(a)); +insert into t2 +select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B; + +# 5 value groups of size 3 each +create table t3(a int, b int, key(a)); +insert into t3 +select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B; + +analyze table t1,t2,t3 persistent for all; + +create table t10 ( + grp_id int, + col1 int, + key(grp_id) +); + +# 100 groups of 100 values each +insert into t10 +select + A.seq, + B.seq +from + seq_1_to_100 A, + seq_1_to_100 B; + +# and X10 multiplier +create table t11 ( + col1 int, + col2 int +); +insert into t11 +select A.seq, A.seq from seq_1_to_10 A; + +analyze table t10,t11 persistent for all; + +set optimizer_trace=1; +explain +select * from + ( + (t1 left join t2 on t2.a=t1.b) + left join t3 on t3.a=t1.b + ) left join (select grp_id, count(*) + from t10 left join t11 on t11.col1=t10.col1 + group by grp_id) T on T.grp_id=t1.b; + +select json_detailed(json_extract(trace, '$**.check_split_materialized')) as JS +from information_schema.optimizer_trace; + +select + json_detailed( + json_remove( + json_extract(trace, '$**.choose_best_splitting') + , '$[0].split_plan_search[0]' + ) + ) as JS +from information_schema.optimizer_trace; + +drop table t1,t2,t3,t10,t11; +set optimizer_trace=DEFAULT; + +--echo # --echo # End of 10.4 tests --echo # @@ -909,13 +979,6 @@ from information_schema.optimizer_trace; --enable_view_protocol -# Same as above. just to show that splitting plan has some coverage in the -# trace. -select - json_detailed(json_extract(trace, '$**.lateral_derived')) -from - information_schema.optimizer_trace; - drop table t1,t2; --echo # diff --git a/mysql-test/main/rownum.result b/mysql-test/main/rownum.result index 3ad51c93260..b61269b1b47 100644 --- a/mysql-test/main/rownum.result +++ b/mysql-test/main/rownum.result @@ -139,6 +139,13 @@ select * from t1,t2 where t1.a=t2.a and rownum()<=2 order by t1.a,t2.a; a b a b 2 20 2 21 3 30 3 31 +create view v1 as +select * from (select * from t1 order by a desc) as t where rownum() <= 2; +select * from v1; +a b +3 30 +2 20 +drop view v1; # # Having # @@ -984,3 +991,29 @@ next row is 3 3 next row is 5 5 +# +# MDEV-31073: Server crash, assertion `table != 0 && +# view->field_translation != 0' failure with ROWNUM and view +# +CREATE TABLE t (f INT); +INSERT INTO t VALUES (1),(2); +CREATE VIEW v AS SELECT * FROM t; +UPDATE v SET f = 10 WHERE ROWNUM() > 42 LIMIT 1; +DROP VIEW v; +DROP TABLE t; +CREATE TABLE t (f INT); +INSERT INTO t VALUES (1),(2); +CREATE VIEW v AS SELECT f, 3 as e FROM t; +UPDATE v SET f = 10 WHERE e > 42 LIMIT 1; +DROP VIEW v; +DROP TABLE t; +CREATE TABLE t (f INT); +INSERT INTO t VALUES (1),(2); +CREATE VIEW v AS SELECT f, ROWNUM() as e FROM t; +UPDATE v SET f = 10 WHERE e > 42 LIMIT 1; +ERROR HY000: The target table v of the UPDATE is not updatable +DROP VIEW v; +DROP TABLE t; +# +# End of 10.6 tests +# diff --git a/mysql-test/main/rownum.test b/mysql-test/main/rownum.test index bdd0bfa4f41..291bd9bd993 100644 --- a/mysql-test/main/rownum.test +++ b/mysql-test/main/rownum.test @@ -58,6 +58,11 @@ select *,rownum() from t1,t2 order by t2.a desc, t1.a desc; select * from (select * from t1 order by a desc) as t where rownum() <= 2; select * from t1,t2 where t1.a=t2.a and rownum()<=2 order by t1.a,t2.a; +create view v1 as +select * from (select * from t1 order by a desc) as t where rownum() <= 2; +select * from v1; +drop view v1; + --echo # --echo # Having --echo # @@ -568,3 +573,40 @@ drop table t1; --echo # Table value constructors --echo # values ("first row"),("next row is 3"),(rownum()),("next row is 5"),(rownum()); + +--echo # +--echo # MDEV-31073: Server crash, assertion `table != 0 && +--echo # view->field_translation != 0' failure with ROWNUM and view +--echo # + +CREATE TABLE t (f INT); +INSERT INTO t VALUES (1),(2); +CREATE VIEW v AS SELECT * FROM t; +UPDATE v SET f = 10 WHERE ROWNUM() > 42 LIMIT 1; + +# Cleanup +DROP VIEW v; +DROP TABLE t; + +CREATE TABLE t (f INT); +INSERT INTO t VALUES (1),(2); +CREATE VIEW v AS SELECT f, 3 as e FROM t; +UPDATE v SET f = 10 WHERE e > 42 LIMIT 1; + +# Cleanup +DROP VIEW v; +DROP TABLE t; + +CREATE TABLE t (f INT); +INSERT INTO t VALUES (1),(2); +CREATE VIEW v AS SELECT f, ROWNUM() as e FROM t; +--error ER_NON_UPDATABLE_TABLE +UPDATE v SET f = 10 WHERE e > 42 LIMIT 1; + +# Cleanup +DROP VIEW v; +DROP TABLE t; + +--echo # +--echo # End of 10.6 tests +--echo # diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index b5a48d341ae..69e3c336c71 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -834,7 +834,7 @@ flush table t1; set optimizer_use_condition_selectivity=4; explain extended select * from t1 where a=0; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1025 0.39 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 1025 0.78 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 0 drop table t1; @@ -1649,7 +1649,7 @@ test.t1 analyze status Table is already up to date # Check what info the optimizer has about selectivities explain extended select * from t1 use index () where a in (17,51,5); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 3.90 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 3.91 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`a` in (17,51,5) explain extended select * from t1 use index () where b=2; @@ -1941,9 +1941,9 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +DROP TABLE t1; +# End of 10.2 tests set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set histogram_size=@save_histogram_size; set use_stat_tables= @save_use_stat_tables; -DROP TABLE t1; -# End of 10.2 tests set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test index 4e4513d09d6..df3850d74b7 100644 --- a/mysql-test/main/selectivity.test +++ b/mysql-test/main/selectivity.test @@ -1321,16 +1321,17 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; -set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; -set histogram_size=@save_histogram_size; -set use_stat_tables= @save_use_stat_tables; DROP TABLE t1; --echo # End of 10.2 tests + # # Clean up # +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; --source include/restore_charset.inc set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 3edf6d8e929..24dac99523e 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -845,7 +845,7 @@ flush table t1; set optimizer_use_condition_selectivity=4; explain extended select * from t1 where a=0; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1025 0.39 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 1025 0.78 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 0 drop table t1; @@ -1661,7 +1661,7 @@ test.t1 analyze status OK # Check what info the optimizer has about selectivities explain extended select * from t1 use index () where a in (17,51,5); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 3.90 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 3.91 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`a` in (17,51,5) explain extended select * from t1 use index () where b=2; @@ -1953,11 +1953,11 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +DROP TABLE t1; +# End of 10.2 tests set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set histogram_size=@save_histogram_size; set use_stat_tables= @save_use_stat_tables; -DROP TABLE t1; -# End of 10.2 tests set @@global.histogram_size=@save_histogram_size; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; diff --git a/mysql-test/main/selectivity_innodb_notembedded.result b/mysql-test/main/selectivity_innodb_notembedded.result new file mode 100644 index 00000000000..8b06fe7556b --- /dev/null +++ b/mysql-test/main/selectivity_innodb_notembedded.result @@ -0,0 +1,104 @@ +SET SESSION STORAGE_ENGINE='InnoDB'; +Warnings: +Warning 1287 '@@storage_engine' is deprecated and will be removed in a future release. Please use '@@default_storage_engine' instead +set @save_optimizer_switch_for_selectivity_test=@@optimizer_switch; +set optimizer_switch='extended_keys=on'; +drop table if exists t0,t1,t2,t3; +select @@global.use_stat_tables; +@@global.use_stat_tables +COMPLEMENTARY +select @@session.use_stat_tables; +@@session.use_stat_tables +COMPLEMENTARY +set @save_use_stat_tables=@@use_stat_tables; +set use_stat_tables='preferably'; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_histogram_size=@@histogram_size; +set @save_histogram_type=@@histogram_type; +set join_cache_level=2; +set @@global.histogram_size=0,@@local.histogram_size=0; +set histogram_type='single_prec_hb'; +set optimizer_use_condition_selectivity=3; +# +# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram +# +create table t0(a int); +insert into t0 select 1 from seq_1_to_78; +create table t1(a int); +insert into t1 select 1 from seq_1_to_26; +create table t10 (a int); +insert into t10 select 0 from t0, seq_1_to_4; +insert into t10 select 8693 from t1; +insert into t10 select 8694 from t1; +insert into t10 select 8695 from t1; +insert into t10 select 34783 from t1; +insert into t10 select 34784 from t1; +insert into t10 select 34785 from t1; +insert into t10 select 34785 from t0, seq_1_to_8; +insert into t10 select 65214 from t1; +insert into t10 select 65215 from t1; +insert into t10 select 65216 from t1; +insert into t10 select 65216 from t0, seq_1_to_52; +insert into t10 select 65217 from t1; +insert into t10 select 65218 from t1; +insert into t10 select 65219 from t1; +insert into t10 select 65219 from t0; +insert into t10 select 73913 from t1; +insert into t10 select 73914 from t1; +insert into t10 select 73915 from t1; +insert into t10 select 73915 from t0, seq_1_to_40; +insert into t10 select 78257 from t1; +insert into t10 select 78258 from t1; +insert into t10 select 78259 from t1; +insert into t10 select 91300 from t1; +insert into t10 select 91301 from t1; +insert into t10 select 91302 from t1; +insert into t10 select 91302 from t0, seq_1_to_6; +insert into t10 select 91303 from t1; +insert into t10 select 91304 from t1; +insert into t10 select 91305 from t1; +insert into t10 select 91305 from t0, seq_1_to_8; +insert into t10 select 99998 from t1; +insert into t10 select 99999 from t1; +insert into t10 select 100000 from t1; +set use_stat_tables=preferably; +analyze table t10 persistent for all; +Table Op Msg_type Msg_text +test.t10 analyze status Engine-independent statistics collected +test.t10 analyze status OK +flush tables; +set @tmp=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t10 where a in (91303); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where +# Must have selectivity_from_histogram <= 1.0: +select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) as sel +from information_schema.optimizer_trace; +sel +[ + [ + { + "column_name": "a", + "ranges": + ["91303 <= a <= 91303"], + "selectivity_from_histogram": 0.035714283 + } + ] +] +set optimizer_trace=@tmp; +drop table t0,t1,t10; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; +# +# End of 10.4 tests +# +# +# Clean up +# +set @@global.histogram_size=@save_histogram_size; +set optimizer_switch=@save_optimizer_switch_for_selectivity_test; +SET SESSION STORAGE_ENGINE=DEFAULT; +Warnings: +Warning 1287 '@@storage_engine' is deprecated and will be removed in a future release. Please use '@@default_storage_engine' instead diff --git a/mysql-test/main/selectivity_innodb_notembedded.test b/mysql-test/main/selectivity_innodb_notembedded.test new file mode 100644 index 00000000000..387f7dcb7de --- /dev/null +++ b/mysql-test/main/selectivity_innodb_notembedded.test @@ -0,0 +1,16 @@ +--source include/have_innodb.inc +# This test is slow on buildbot. +--source include/big_test.inc +--source include/default_optimizer_switch.inc +--source include/not_embedded.inc + +SET SESSION STORAGE_ENGINE='InnoDB'; + +set @save_optimizer_switch_for_selectivity_test=@@optimizer_switch; +set optimizer_switch='extended_keys=on'; + +--source selectivity_notembedded.test + +set optimizer_switch=@save_optimizer_switch_for_selectivity_test; + +SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/main/selectivity_no_engine.result b/mysql-test/main/selectivity_no_engine.result index 3811b12a1be..00e2b8fc397 100644 --- a/mysql-test/main/selectivity_no_engine.result +++ b/mysql-test/main/selectivity_no_engine.result @@ -36,12 +36,12 @@ test.t2 analyze status OK # The following two must have the same in 'Extra' column: explain extended select * from t2 where col1 IN (20, 180); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1100 1.35 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 1100 1.00 Using where Warnings: Note 1003 select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where `test`.`t2`.`col1` in (20,180) explain extended select * from t2 where col1 IN (180, 20); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1100 1.35 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 1100 1.00 Using where Warnings: Note 1003 select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where `test`.`t2`.`col1` in (180,20) drop table t1, t2; @@ -102,7 +102,7 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK explain extended select * from t1 where col1 in (1,2,3); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 3.37 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 2.97 Using where Warnings: Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where `test`.`t1`.`col1` in (1,2,3) # Must not cause fp division by zero, or produce nonsense numbers: @@ -314,6 +314,29 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE a ALL NULL NULL NULL NULL 5 Using where 1 SIMPLE b hash_ALL NULL #hash#$hj 1341 test.a.Host,test.a.User,test.a.Password,test.a.Select_priv,test.a.Insert_priv,test.a.Update_priv,test.a.Delete_priv,test.a.Create_priv,test.a.Drop_priv,test.a.Reload_priv,test.a.Shutdown_priv,test.a.Process_priv,test.a.File_priv,test.a.Grant_priv,test.a.References_priv,test.a.Index_priv,test.a.Alter_priv,test.a.Show_db_priv,test.a.Super_priv,test.a.Create_tmp_table_priv,test.a.Lock_tables_priv,test.a.Execute_priv,test.a.Repl_slave_priv,test.a.Repl_client_priv,test.a.Create_view_priv,test.a.Show_view_priv,test.a.Create_routine_priv,test.a.Alter_routine_priv,test.a.Create_user_priv,test.a.Event_priv,test.a.Trigger_priv,test.a.Create_tablespace_priv,test.a.Delete_history_priv,test.a.ssl_type,test.a.ssl_cipher,test.a.x509_issuer,test.a.x509_subject,test.a.max_questions,test.a.max_updates,test.a.max_connections,test.a.max_user_connections,test.a.plugin,test.a.authentication_string,test.a.password_expired,test.a.is_role,test.a.default_role,test.a.max_statement_time 5 Using where; Using join buffer (flat, BNLH join) DROP TABLE t1,t2,t3; +# +# MDEV-31199: Assertion `field->table->stats_is_read' fails with hash_join_cardinality=on +# +CREATE TABLE t1 (a VARCHAR(255)); +INSERT INTO t1 VALUES ('u'),('uu'); +CREATE TABLE t2 (b VARCHAR(255)) CHARACTER SET utf8mb4; +INSERT INTO t2 VALUES ('x'),('xx'); +CREATE TABLE t3 (c VARCHAR(255)); +INSERT INTO t3 VALUES ('z'),('zz'); +ANALYZE TABLE t1, t2, t3 PERSISTENT FOR ALL; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +set @tmp1=@@optimizer_switch, @tmp2=@@join_cache_level; +set optimizer_switch='hash_join_cardinality=on', join_cache_level=3; +SELECT t1.* FROM t1 JOIN (SELECT DISTINCT b FROM t2 JOIN t3) sq ON sq.b = t1.a; +a +set optimizer_switch=@tmp1, join_cache_level=@tmp2; +DROP TABLE t1, t2, t3; # # End of the test file # diff --git a/mysql-test/main/selectivity_no_engine.test b/mysql-test/main/selectivity_no_engine.test index 5bc78e03781..47bac21a2e1 100644 --- a/mysql-test/main/selectivity_no_engine.test +++ b/mysql-test/main/selectivity_no_engine.test @@ -250,6 +250,26 @@ SELECT * FROM t1 AS a NATURAL JOIN t1 AS b; DROP TABLE t1,t2,t3; +--echo # +--echo # MDEV-31199: Assertion `field->table->stats_is_read' fails with hash_join_cardinality=on +--echo # +CREATE TABLE t1 (a VARCHAR(255)); +INSERT INTO t1 VALUES ('u'),('uu'); + +CREATE TABLE t2 (b VARCHAR(255)) CHARACTER SET utf8mb4; +INSERT INTO t2 VALUES ('x'),('xx'); + +CREATE TABLE t3 (c VARCHAR(255)); +INSERT INTO t3 VALUES ('z'),('zz'); + +ANALYZE TABLE t1, t2, t3 PERSISTENT FOR ALL; # Optional, fails either way + +set @tmp1=@@optimizer_switch, @tmp2=@@join_cache_level; +set optimizer_switch='hash_join_cardinality=on', join_cache_level=3; +SELECT t1.* FROM t1 JOIN (SELECT DISTINCT b FROM t2 JOIN t3) sq ON sq.b = t1.a; +set optimizer_switch=@tmp1, join_cache_level=@tmp2; +DROP TABLE t1, t2, t3; + --echo # --echo # End of the test file --echo # diff --git a/mysql-test/main/selectivity_notembedded.result b/mysql-test/main/selectivity_notembedded.result new file mode 100644 index 00000000000..d2e90a19a68 --- /dev/null +++ b/mysql-test/main/selectivity_notembedded.result @@ -0,0 +1,95 @@ +drop table if exists t0,t1,t2,t3; +select @@global.use_stat_tables; +@@global.use_stat_tables +COMPLEMENTARY +select @@session.use_stat_tables; +@@session.use_stat_tables +COMPLEMENTARY +set @save_use_stat_tables=@@use_stat_tables; +set use_stat_tables='preferably'; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_histogram_size=@@histogram_size; +set @save_histogram_type=@@histogram_type; +set join_cache_level=2; +set @@global.histogram_size=0,@@local.histogram_size=0; +set histogram_type='single_prec_hb'; +set optimizer_use_condition_selectivity=3; +# +# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram +# +create table t0(a int); +insert into t0 select 1 from seq_1_to_78; +create table t1(a int); +insert into t1 select 1 from seq_1_to_26; +create table t10 (a int); +insert into t10 select 0 from t0, seq_1_to_4; +insert into t10 select 8693 from t1; +insert into t10 select 8694 from t1; +insert into t10 select 8695 from t1; +insert into t10 select 34783 from t1; +insert into t10 select 34784 from t1; +insert into t10 select 34785 from t1; +insert into t10 select 34785 from t0, seq_1_to_8; +insert into t10 select 65214 from t1; +insert into t10 select 65215 from t1; +insert into t10 select 65216 from t1; +insert into t10 select 65216 from t0, seq_1_to_52; +insert into t10 select 65217 from t1; +insert into t10 select 65218 from t1; +insert into t10 select 65219 from t1; +insert into t10 select 65219 from t0; +insert into t10 select 73913 from t1; +insert into t10 select 73914 from t1; +insert into t10 select 73915 from t1; +insert into t10 select 73915 from t0, seq_1_to_40; +insert into t10 select 78257 from t1; +insert into t10 select 78258 from t1; +insert into t10 select 78259 from t1; +insert into t10 select 91300 from t1; +insert into t10 select 91301 from t1; +insert into t10 select 91302 from t1; +insert into t10 select 91302 from t0, seq_1_to_6; +insert into t10 select 91303 from t1; +insert into t10 select 91304 from t1; +insert into t10 select 91305 from t1; +insert into t10 select 91305 from t0, seq_1_to_8; +insert into t10 select 99998 from t1; +insert into t10 select 99999 from t1; +insert into t10 select 100000 from t1; +set use_stat_tables=preferably; +analyze table t10 persistent for all; +Table Op Msg_type Msg_text +test.t10 analyze status Engine-independent statistics collected +test.t10 analyze status OK +flush tables; +set @tmp=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t10 where a in (91303); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where +# Must have selectivity_from_histogram <= 1.0: +select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) as sel +from information_schema.optimizer_trace; +sel +[ + [ + { + "column_name": "a", + "ranges": + ["91303 <= a <= 91303"], + "selectivity_from_histogram": 0.035714283 + } + ] +] +set optimizer_trace=@tmp; +drop table t0,t1,t10; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; +# +# End of 10.4 tests +# +# +# Clean up +# +set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/selectivity_notembedded.test b/mysql-test/main/selectivity_notembedded.test new file mode 100644 index 00000000000..6752bd3c7e1 --- /dev/null +++ b/mysql-test/main/selectivity_notembedded.test @@ -0,0 +1,121 @@ +--source include/no_valgrind_without_big.inc +--source include/have_stat_tables.inc +--source include/have_sequence.inc +--source include/default_charset.inc +--source include/not_embedded.inc + +--disable_warnings +drop table if exists t0,t1,t2,t3; +--enable_warnings + +select @@global.use_stat_tables; +select @@session.use_stat_tables; + +set @save_use_stat_tables=@@use_stat_tables; +set use_stat_tables='preferably'; + +--source include/default_optimizer_switch.inc +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_histogram_size=@@histogram_size; +set @save_histogram_type=@@histogram_type; +set join_cache_level=2; +set @@global.histogram_size=0,@@local.histogram_size=0; +set histogram_type='single_prec_hb'; + +# check that statistics on nulls is used + +set optimizer_use_condition_selectivity=3; + +--echo # +--echo # MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram +--echo # +create table t0(a int); # This holds how many rows we hold in a bucket. +insert into t0 select 1 from seq_1_to_78; + +create table t1(a int); # one-third of a bucket +insert into t1 select 1 from seq_1_to_26; + +create table t10 (a int); +insert into t10 select 0 from t0, seq_1_to_4; + +insert into t10 select 8693 from t1; +insert into t10 select 8694 from t1; +insert into t10 select 8695 from t1; + + +insert into t10 select 34783 from t1; +insert into t10 select 34784 from t1; +insert into t10 select 34785 from t1; + + +insert into t10 select 34785 from t0, seq_1_to_8; + +insert into t10 select 65214 from t1; +insert into t10 select 65215 from t1; +insert into t10 select 65216 from t1; + +insert into t10 select 65216 from t0, seq_1_to_52; + +insert into t10 select 65217 from t1; +insert into t10 select 65218 from t1; +insert into t10 select 65219 from t1; + +insert into t10 select 65219 from t0; + + +insert into t10 select 73913 from t1; +insert into t10 select 73914 from t1; +insert into t10 select 73915 from t1; + +insert into t10 select 73915 from t0, seq_1_to_40; + + +insert into t10 select 78257 from t1; +insert into t10 select 78258 from t1; +insert into t10 select 78259 from t1; + +insert into t10 select 91300 from t1; +insert into t10 select 91301 from t1; +insert into t10 select 91302 from t1; + +insert into t10 select 91302 from t0, seq_1_to_6; + +insert into t10 select 91303 from t1; # Only 1/3rd of bucket matches the search tuple +insert into t10 select 91304 from t1; +insert into t10 select 91305 from t1; + +insert into t10 select 91305 from t0, seq_1_to_8; + +insert into t10 select 99998 from t1; +insert into t10 select 99999 from t1; +insert into t10 select 100000 from t1; + +set use_stat_tables=preferably; +analyze table t10 persistent for all; +flush tables; + +set @tmp=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t10 where a in (91303); + +--echo # Must have selectivity_from_histogram <= 1.0: +select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) as sel +from information_schema.optimizer_trace; + +set optimizer_trace=@tmp; +drop table t0,t1,t10; + +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; + + +--echo # +--echo # End of 10.4 tests +--echo # + +--echo # +--echo # Clean up +--echo # +--source include/restore_charset.inc +set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index aec4a5d09f2..329f367844e 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6951,6 +6951,24 @@ create algorithm=merge view v as select * from t1 left join t2 on t1.a=t2.b and t1.a in (select d from t3); ERROR 42S22: Unknown column 'd' in 'field list' drop table t1,t2,t3; +# +# MDEV-31189: Server crash or assertion failure in upon 2nd +# execution of PS with views and HAVING +# +CREATE TABLE t (f INT); +INSERT INTO t VALUES (1),(2); +CREATE VIEW v1 AS SELECT 1 AS a; +CREATE VIEW v2 AS SELECT a FROM v1; +PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)"; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +DROP VIEW v1; +DROP VIEW v2; +DROP TABLE t; # End of 10.4 tests # # MDEV-13115: SELECT .. SKIP LOCKED - ensure SHOW CREATE VIEW is correct diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index c6cc9a69f93..fa3f0d312a7 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6695,6 +6695,25 @@ create algorithm=merge view v as drop table t1,t2,t3; +--echo # +--echo # MDEV-31189: Server crash or assertion failure in upon 2nd +--echo # execution of PS with views and HAVING +--echo # + +CREATE TABLE t (f INT); +INSERT INTO t VALUES (1),(2); # Optional, fails either way +CREATE VIEW v1 AS SELECT 1 AS a; +CREATE VIEW v2 AS SELECT a FROM v1; + +PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)"; +EXECUTE stmt; +EXECUTE stmt; + +# Cleanup +DROP VIEW v1; +DROP VIEW v2; +DROP TABLE t; + --echo # End of 10.4 tests --echo # diff --git a/mysql-test/std_data/rpl/master-bin-seq_10.3.36.000001 b/mysql-test/std_data/rpl/master-bin-seq_10.3.36.000001 Binary files differnew file mode 100644 index 00000000000..0fa163d0484 --- /dev/null +++ b/mysql-test/std_data/rpl/master-bin-seq_10.3.36.000001 diff --git a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted.cnf b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_coords.cnf index 73c9ad655bf..73c9ad655bf 100644 --- a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted.cnf +++ b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_coords.cnf diff --git a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted.result b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_coords.result index d632c565ad2..33c1f1413b4 100644 --- a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted.result +++ b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_coords.result @@ -4,6 +4,7 @@ include/rpl_init.inc [topology=1->2] connection server_2; include/stop_slave.inc +CHANGE MASTER TO MASTER_USE_GTID=NO; ##################################################### # Part 1: unencrypted master ##################################################### diff --git a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted.test b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_coords.test index 1e1b0cbd353..a34c9715239 100644 --- a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted.test +++ b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_coords.test @@ -1,14 +1,19 @@ # -# TODO: write here what the test checks after MDEV-11288 is fixed -# -# The test starts with unencrypted master. +# The test starts with unencrypted master. # It stops replication, generates a few statement and row events -# on the master, then restarts the server with encrypted binlog, -# generates some more events and restarts it back without encryption +# on the master, then restarts the server with encrypted binlog, +# generates some more events and restarts it back without encryption # (no encryption plugin). -# Then it resumes replication and checks what happens when the server -# tries to feed the binary logs (included the encrypted ones) -# to the slave. +# Then it resumes replication and should error with +# ER_MASTER_FATAL_ERROR_READING_BINLOG because the encrypted binlog is +# sent and unable to be decrypted. +# +# Note this variation of encrypted_master_switch_to_unencrypted tests +# using MASTER_USE_GTID=NO. In contrast to the GTID variant of this +# test, at part 3 (the error case), the master will scan binlogs +# starting from the first one (which is unencrypted initially, so +# replication is okay) and continue until the slave encounters the +# first encrypted event, which causes the slave to error. # --source include/have_binlog_format_mixed.inc @@ -27,6 +32,7 @@ --connection server_2 --disable_connect_log --source include/stop_slave.inc +CHANGE MASTER TO MASTER_USE_GTID=NO; --enable_connect_log --echo ##################################################### @@ -41,7 +47,7 @@ CREATE TABLE table1_no_encryption ( pk INT AUTO_INCREMENT PRIMARY KEY, ts TIMESTAMP NULL, b BLOB -) ENGINE=MyISAM; +) ENGINE=MyISAM; INSERT INTO table1_no_encryption VALUES (NULL,NOW(),'data_no_encryption'); INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption; @@ -74,7 +80,7 @@ CREATE TABLE table2_to_encrypt ( pk INT AUTO_INCREMENT PRIMARY KEY, ts TIMESTAMP NULL, b BLOB -) ENGINE=MyISAM; +) ENGINE=MyISAM; INSERT INTO table2_to_encrypt VALUES (NULL,NOW(),'data_to_encrypt'); INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt; @@ -101,7 +107,7 @@ CREATE TABLE table3_no_encryption ( pk INT AUTO_INCREMENT PRIMARY KEY, ts TIMESTAMP NULL, b BLOB -) ENGINE=MyISAM; +) ENGINE=MyISAM; INSERT INTO table3_no_encryption VALUES (NULL,NOW(),'data_no_encryption'); INSERT INTO table3_no_encryption SELECT NULL,NOW(),b FROM table3_no_encryption; @@ -113,7 +119,7 @@ INSERT INTO table3_no_encryption SELECT NULL,NOW(),b FROM table3_no_encryption; --connection server_2 start slave; -# The slave should be able to synchronize with master up to +# The slave should be able to synchronize with master up to # the previously saved position (when the log was still unencrypted) --sync_with_master diff --git a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.cnf b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.cnf new file mode 100644 index 00000000000..ad1b8b44c24 --- /dev/null +++ b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.cnf @@ -0,0 +1,8 @@ +!include my.cnf + +[mysqld.1] +encrypt-binlog=0 +skip-file-key-management + +[mysqld.2] +gtid-domain-id=1 diff --git a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.result b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.result new file mode 100644 index 00000000000..16ea30557e7 --- /dev/null +++ b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.result @@ -0,0 +1,84 @@ +################# +# Initialization +################# +include/rpl_init.inc [topology=1->2] +connection server_2; +include/stop_slave.inc +CHANGE MASTER TO MASTER_USE_GTID=SLAVE_POS; +call mtr.add_suppression(" Got fatal error 1236 from master when reading data from binary log: 'Could not set up decryption for binlog.'"); +##################################################### +# Part 1: unencrypted master +##################################################### +connection server_1; +CREATE TABLE table1_no_encryption ( +pk INT AUTO_INCREMENT PRIMARY KEY, +ts TIMESTAMP NULL, +b BLOB +) ENGINE=MyISAM; +INSERT INTO table1_no_encryption VALUES (NULL,NOW(),'data_no_encryption'); +INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption; +FLUSH BINARY LOGS; +SET binlog_format=ROW; +INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption; +INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption; +NOT FOUND /table1_no_encryption/ in master-bin.0* +##################################################### +# Part 2: restart master, now with binlog encryption +##################################################### +connection default; +connection server_1; +CREATE TABLE table2_to_encrypt ( +pk INT AUTO_INCREMENT PRIMARY KEY, +ts TIMESTAMP NULL, +b BLOB +) ENGINE=MyISAM; +INSERT INTO table2_to_encrypt VALUES (NULL,NOW(),'data_to_encrypt'); +INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt; +FLUSH BINARY LOGS; +SET binlog_format=ROW; +INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt; +INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt; +NOT FOUND /table2_to_encrypt/ in master-bin.0* +##################################################### +# Part 3: restart master again without encryption +##################################################### +connection default; +connection server_1; +CREATE TABLE table3_no_encryption ( +pk INT AUTO_INCREMENT PRIMARY KEY, +ts TIMESTAMP NULL, +b BLOB +) ENGINE=MyISAM; +INSERT INTO table3_no_encryption VALUES (NULL,NOW(),'data_no_encryption'); +INSERT INTO table3_no_encryption SELECT NULL,NOW(),b FROM table3_no_encryption; +INSERT INTO table3_no_encryption SELECT NULL,NOW(),b FROM table3_no_encryption; +##################################################### +# Check: resume replication and check how it goes +##################################################### +connection server_2; +start slave; +include/wait_for_slave_io_error.inc [errno=1236] +# Ensuring slave was unable to replicate any transactions.. +# ..success +SHOW TABLES; +Tables_in_test +include/stop_slave.inc +reset slave; +########## +# Cleanup +########## +connection server_1; +reset master; +SELECT COUNT(*) FROM table1_no_encryption; +COUNT(*) +8 +SELECT COUNT(*) FROM table2_to_encrypt; +COUNT(*) +8 +SELECT COUNT(*) FROM table3_no_encryption; +COUNT(*) +4 +DROP TABLE table1_no_encryption, table2_to_encrypt, table3_no_encryption; +connection server_2; +include/start_slave.inc +include/rpl_end.inc diff --git a/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.test b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.test new file mode 100644 index 00000000000..f882e8f3440 --- /dev/null +++ b/mysql-test/suite/binlog_encryption/encrypted_master_switch_to_unencrypted_gtid.test @@ -0,0 +1,154 @@ +# +# The test starts with unencrypted master. +# It stops replication, generates a few statement and row events +# on the master, then restarts the server with encrypted binlog, +# generates some more events and restarts it back without encryption +# (no encryption plugin). +# Then it resumes replication and should error with +# ER_MASTER_FATAL_ERROR_READING_BINLOG because the encrypted binlog is +# sent and unable to be decrypted. +# +# Note this variation of encrypted_master_switch_to_unencrypted tests +# using MASTER_USE_GTID=SLAVE_POS. encrypted_master_switch_to_unencrypted +# was the original test which only used binlog coordinates. When tested +# using MASTER_USE_GTID=Slave_Pos, the master optimizes the detection of +# an undecryptable binlog. I.e, the master will initially look for a +# Gtid_list_log_event, but fail to decrypt it and fail immediately in +# part 3. +# + +--source include/have_binlog_format_mixed.inc + +--echo ################# +--echo # Initialization +--echo ################# + +--let $rpl_topology= 1->2 +--source include/rpl_init.inc + +--enable_connect_log + +# We stop replication because we want it to happen after the switch + +--connection server_2 +--disable_connect_log +--source include/stop_slave.inc +CHANGE MASTER TO MASTER_USE_GTID=SLAVE_POS; +--enable_connect_log +call mtr.add_suppression(" Got fatal error 1236 from master when reading data from binary log: 'Could not set up decryption for binlog.'"); + +--echo ##################################################### +--echo # Part 1: unencrypted master +--echo ##################################################### + +--connection server_1 + +CREATE TABLE table1_no_encryption ( + pk INT AUTO_INCREMENT PRIMARY KEY, + ts TIMESTAMP NULL, + b BLOB +) ENGINE=MyISAM; + +INSERT INTO table1_no_encryption VALUES (NULL,NOW(),'data_no_encryption'); +INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption; +FLUSH BINARY LOGS; +SET binlog_format=ROW; +INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption; +INSERT INTO table1_no_encryption SELECT NULL,NOW(),b FROM table1_no_encryption; + +# Make sure that binary logs are not encrypted + +--let SEARCH_RANGE = 500000 +--let SEARCH_FILE= master-bin.0* +--let SEARCH_PATTERN= table1_no_encryption +--source include/search_pattern_in_file.inc + +--echo ##################################################### +--echo # Part 2: restart master, now with binlog encryption +--echo ##################################################### + +--let $rpl_server_parameters= --encrypt-binlog=1 --plugin-load-add=$FILE_KEY_MANAGEMENT_SO --file-key-management --loose-file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys.txt + +--let $rpl_server_number= 1 +--source restart_server.inc + +CREATE TABLE table2_to_encrypt ( + pk INT AUTO_INCREMENT PRIMARY KEY, + ts TIMESTAMP NULL, + b BLOB +) ENGINE=MyISAM; + +INSERT INTO table2_to_encrypt VALUES (NULL,NOW(),'data_to_encrypt'); +INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt; +FLUSH BINARY LOGS; +SET binlog_format=ROW; +INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt; +INSERT INTO table2_to_encrypt SELECT NULL,NOW(),b FROM table2_to_encrypt; + +# Make sure that binary logs are encrypted + +--let SEARCH_FILE= master-bin.0* +--let SEARCH_PATTERN= table2_to_encrypt +--source include/search_pattern_in_file.inc + +--echo ##################################################### +--echo # Part 3: restart master again without encryption +--echo ##################################################### + +--let $rpl_server_parameters= --encrypt-binlog=0 +--let $rpl_server_number= 1 +--source restart_server.inc + +CREATE TABLE table3_no_encryption ( + pk INT AUTO_INCREMENT PRIMARY KEY, + ts TIMESTAMP NULL, + b BLOB +) ENGINE=MyISAM; + +INSERT INTO table3_no_encryption VALUES (NULL,NOW(),'data_no_encryption'); +INSERT INTO table3_no_encryption SELECT NULL,NOW(),b FROM table3_no_encryption; +INSERT INTO table3_no_encryption SELECT NULL,NOW(),b FROM table3_no_encryption; + +--echo ##################################################### +--echo # Check: resume replication and check how it goes +--echo ##################################################### + +--connection server_2 +start slave; +# Make slave to try to synchronize. It shouldn't work, the slave IO thread is +# expected to abort with an error +--let $slave_io_errno= 1236 +--source include/wait_for_slave_io_error.inc + +--echo # Ensuring slave was unable to replicate any transactions.. +--let $gsp= `SELECT @@global.gtid_slave_pos` +if (`SELECT strcmp("$gsp","")`) +{ + die Slave without encryption configured should fail to read encrypted binlog; +} +--echo # ..success + +--sorted_result +SHOW TABLES; + +--disable_connect_log +--source include/stop_slave.inc +--enable_connect_log +reset slave; + +--echo ########## +--echo # Cleanup +--echo ########## + +--connection server_1 +reset master; + +SELECT COUNT(*) FROM table1_no_encryption; +SELECT COUNT(*) FROM table2_to_encrypt; +SELECT COUNT(*) FROM table3_no_encryption; +DROP TABLE table1_no_encryption, table2_to_encrypt, table3_no_encryption; + +--connection server_2 +--disable_connect_log +--source include/start_slave.inc +--source include/rpl_end.inc diff --git a/mysql-test/suite/galera/r/galera_var_retry_autocommit.result b/mysql-test/suite/galera/r/galera_var_retry_autocommit.result index 56c2c995402..50667b0a4fa 100644 --- a/mysql-test/suite/galera/r/galera_var_retry_autocommit.result +++ b/mysql-test/suite/galera/r/galera_var_retry_autocommit.result @@ -20,17 +20,25 @@ DROP TABLE t1; connection node_1; CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; SET SESSION wsrep_retry_autocommit = 1; +SET GLOBAL debug_dbug = '+d,sync.wsrep_retry_autocommit'; SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continue'; INSERT INTO t1 (f1) VALUES (3); connection node_1a; SET DEBUG_SYNC = 'now WAIT_FOR before_cert'; connection node_2; TRUNCATE TABLE t1; -connection node_1; +connection node_1a; +SET DEBUG_SYNC = 'now WAIT_FOR wsrep_retry_autocommit_reached'; SELECT COUNT(*) FROM t1; COUNT(*) 0 +SET DEBUG_SYNC = 'now SIGNAL wsrep_retry_autocommit_continue'; +connection node_1; +SELECT COUNT(*) FROM t1; +COUNT(*) +1 SET DEBUG_SYNC = 'RESET'; +SET GLOBAL debug_dbug = NULL; DROP TABLE t1; connection node_1; CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; @@ -64,6 +72,8 @@ SET SESSION wsrep_retry_autocommit = 64; SET GLOBAL debug_dbug = '+d,sync.wsrep_retry_autocommit'; SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continue EXECUTE 64'; INSERT INTO t1 VALUES (5); +connection node_2; +connection node_1; connection node_1; SELECT COUNT(*) FROM t1; COUNT(*) diff --git a/mysql-test/suite/galera/t/galera_var_retry_autocommit.test b/mysql-test/suite/galera/t/galera_var_retry_autocommit.test index bd10e448e06..c58eba1410e 100644 --- a/mysql-test/suite/galera/t/galera_var_retry_autocommit.test +++ b/mysql-test/suite/galera/t/galera_var_retry_autocommit.test @@ -25,6 +25,8 @@ SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continu SET DEBUG_SYNC = 'now WAIT_FOR before_cert'; --connection node_2 +--let $wait_condition = SELECT count(*)=1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1' +--source include/wait_condition.inc TRUNCATE TABLE t1; --connection node_1 @@ -44,6 +46,7 @@ DROP TABLE t1; CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; SET SESSION wsrep_retry_autocommit = 1; +SET GLOBAL debug_dbug = '+d,sync.wsrep_retry_autocommit'; SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continue'; --send INSERT INTO t1 (f1) VALUES (3) @@ -51,14 +54,21 @@ SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continu SET DEBUG_SYNC = 'now WAIT_FOR before_cert'; --connection node_2 +--let $wait_condition = SELECT count(*)=1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1' +--source include/wait_condition.inc TRUNCATE TABLE t1; +--connection node_1a +SET DEBUG_SYNC = 'now WAIT_FOR wsrep_retry_autocommit_reached'; +SELECT COUNT(*) FROM t1; +SET DEBUG_SYNC = 'now SIGNAL wsrep_retry_autocommit_continue'; + --connection node_1 ---error 0,ER_LOCK_DEADLOCK --reap SELECT COUNT(*) FROM t1; SET DEBUG_SYNC = 'RESET'; +SET GLOBAL debug_dbug = NULL; DROP TABLE t1; @@ -79,6 +89,8 @@ SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continu SET DEBUG_SYNC = 'now WAIT_FOR before_cert'; --connection node_2 +--let $wait_condition = SELECT count(*)=1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1' +--source include/wait_condition.inc TRUNCATE TABLE t1; --connection node_1a @@ -114,6 +126,11 @@ SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continu --send INSERT INTO t1 VALUES (5) +--connection node_2 +--let $wait_condition = SELECT count(*)=1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1' +--source include/wait_condition.inc + +--connection node_1 --disable_query_log --disable_result_log --let $count = 64 diff --git a/mysql-test/suite/galera_sr/r/MDEV-30838.result b/mysql-test/suite/galera_sr/r/MDEV-30838.result new file mode 100644 index 00000000000..6997b9c4d5d --- /dev/null +++ b/mysql-test/suite/galera_sr/r/MDEV-30838.result @@ -0,0 +1,15 @@ +connection node_2; +connection node_1; +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY); +SET SESSION wsrep_trx_fragment_size=1; +START TRANSACTION; +INSERT INTO t1 VALUES(1); +SET debug_dbug='+d,ib_create_table_fail_too_many_trx'; +INSERT INTO t1 VALUES(2); +ERROR HY000: Error while appending streaming replication fragment +COMMIT; +SELECT * FROM t1; +f1 +SET debug_dbug='-d,ib_create_table_fail_too_many_trx'; +DROP TABLE t1; +CALL mtr.add_suppression("Error writing into mysql.wsrep_streaming_log: 177"); diff --git a/mysql-test/suite/galera_sr/t/MDEV-30838.test b/mysql-test/suite/galera_sr/t/MDEV-30838.test new file mode 100644 index 00000000000..39ca7d2a375 --- /dev/null +++ b/mysql-test/suite/galera_sr/t/MDEV-30838.test @@ -0,0 +1,18 @@ +# +# MDEV-30838 - Assertion `m_thd == _current_thd()' failed in +# virtual int Wsrep_client_service::bf_rollback() +# +--source include/galera_cluster.inc +--source include/have_debug_sync.inc +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY); +SET SESSION wsrep_trx_fragment_size=1; +START TRANSACTION; +INSERT INTO t1 VALUES(1); +SET debug_dbug='+d,ib_create_table_fail_too_many_trx'; +--error ER_ERROR_DURING_COMMIT +INSERT INTO t1 VALUES(2); +COMMIT; +SELECT * FROM t1; +SET debug_dbug='-d,ib_create_table_fail_too_many_trx'; +DROP TABLE t1; +CALL mtr.add_suppression("Error writing into mysql.wsrep_streaming_log: 177"); diff --git a/mysql-test/suite/innodb_zip/r/page_size.result b/mysql-test/suite/innodb_zip/r/page_size.result index 47effe06884..48b954c945b 100644 --- a/mysql-test/suite/innodb_zip/r/page_size.result +++ b/mysql-test/suite/innodb_zip/r/page_size.result @@ -608,4 +608,25 @@ SET GLOBAL innodb_compression_level=0; INSERT INTO t1 VALUES (''); SET GLOBAL innodb_compression_level= @save_innodb_compression_level; DROP TABLE t1; +# +# MDEV-31158 Assertion ...MTR_MEMO_X_LOCKED in btr_attach_half_pages() +# +SET @save_compression_level=@@GLOBAL.innodb_compression_level; +SET GLOBAL innodb_compression_level=0; +CREATE TEMPORARY TABLE t(a SERIAL, prefix VARBINARY(4), pad INT); +INSERT INTO t(prefix, pad) VALUES +(_binary 0xff,160),('',19),(_binary 0x0001,253),(_binary 0x0b11,169), +(_binary 0x0b010001,23),(_binary 0x0b100001,251),(_binary 0x0d,163), +(_binary 0xb3,254),(_binary 0x96,254),(_binary 0xeb,61), +(_binary 0xf231,253),(_binary 0x1db0,253),(_binary 0x0005,101), +(_binary 0x6370,253),(_binary 0x0b12,112),(_binary 0x0b010002,23), +(_binary 0x0b100002,80),(_binary 0x181984,163),(_binary 0x181926,168), +(_binary 0xe1,176),(_binary 0xe2,187),(_binary 0xe6,254),(_binary 0xbb,51), +(_binary 0x1c,248),(_binary 0x8a,94),(_binary 0x14,254); +CREATE TABLE u(a SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, +b VARBINARY(255), KEY(b)) ENGINE=InnoDB +KEY_BLOCK_SIZE=1 ROW_FORMAT=COMPRESSED; +INSERT INTO u SELECT a,CONCAT(prefix,REPEAT(chr(0),pad)) FROM t; +DROP TABLE u, t; +SET GLOBAL innodb_compression_level=@save_compression_level; # End of 10.6 tests diff --git a/mysql-test/suite/innodb_zip/t/page_size.test b/mysql-test/suite/innodb_zip/t/page_size.test index 16d65a139cf..3455ef8ed94 100644 --- a/mysql-test/suite/innodb_zip/t/page_size.test +++ b/mysql-test/suite/innodb_zip/t/page_size.test @@ -888,4 +888,28 @@ INSERT INTO t1 VALUES (''); SET GLOBAL innodb_compression_level= @save_innodb_compression_level; DROP TABLE t1; +--echo # +--echo # MDEV-31158 Assertion ...MTR_MEMO_X_LOCKED in btr_attach_half_pages() +--echo # +--source include/have_innodb.inc + +SET @save_compression_level=@@GLOBAL.innodb_compression_level; +SET GLOBAL innodb_compression_level=0; +CREATE TEMPORARY TABLE t(a SERIAL, prefix VARBINARY(4), pad INT); +INSERT INTO t(prefix, pad) VALUES +(_binary 0xff,160),('',19),(_binary 0x0001,253),(_binary 0x0b11,169), +(_binary 0x0b010001,23),(_binary 0x0b100001,251),(_binary 0x0d,163), +(_binary 0xb3,254),(_binary 0x96,254),(_binary 0xeb,61), +(_binary 0xf231,253),(_binary 0x1db0,253),(_binary 0x0005,101), +(_binary 0x6370,253),(_binary 0x0b12,112),(_binary 0x0b010002,23), +(_binary 0x0b100002,80),(_binary 0x181984,163),(_binary 0x181926,168), +(_binary 0xe1,176),(_binary 0xe2,187),(_binary 0xe6,254),(_binary 0xbb,51), +(_binary 0x1c,248),(_binary 0x8a,94),(_binary 0x14,254); +CREATE TABLE u(a SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, + b VARBINARY(255), KEY(b)) ENGINE=InnoDB + KEY_BLOCK_SIZE=1 ROW_FORMAT=COMPRESSED; +INSERT INTO u SELECT a,CONCAT(prefix,REPEAT(chr(0),pad)) FROM t; +DROP TABLE u, t; +SET GLOBAL innodb_compression_level=@save_compression_level; + --echo # End of 10.6 tests diff --git a/mysql-test/suite/perfschema/r/digest_view.result b/mysql-test/suite/perfschema/r/digest_view.result index d3988f99727..2d5c7ca0f42 100644 --- a/mysql-test/suite/perfschema/r/digest_view.result +++ b/mysql-test/suite/perfschema/r/digest_view.result @@ -191,17 +191,17 @@ SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR FROM performance_schema.events_statements_summary_by_digest ORDER BY DIGEST_TEXT; SCHEMA_NAME DIGEST DIGEST_TEXT COUNT_STAR -test 3bcc75e0f4658692e16837ead0c455ca EXPLAIN SELECT * FROM `test` . `v1` 1 -test cc55af8221ef0bc8873e75cc704489ba EXPLAIN SELECT * FROM `test` . `v1` WHERE `a` = ? 1 -test bc0498551a492bd7c0ca92906bf86fb4 EXPLAIN SELECT * FROM `test` . `v1` WHERE `b` > ? 1 -test 76923336b6f4ffffd0d5854761ae1aec EXPLAIN SELECT `a` , `b` FROM `test` . `v1` 1 -test a65d479062ffbfe374b4cd823016a260 EXPLAIN SELECT `b` , `a` FROM `test` . `v1` 1 -test d44b1e50ebfd05accacef4167534ad65 SELECT * FROM `test` . `v1` 1 -test 0608cde507ef8fe35d1f68730e78f1e4 SELECT * FROM `test` . `v1` WHERE `a` = ? 1 -test 5e241fdb866faef138cc81b26203ddff SELECT * FROM `test` . `v1` WHERE `b` > ? 1 -test 74fa770ce44994b3fe7b534d504f2d6d SELECT `a` , `b` FROM `test` . `v1` 1 -test 45ca2a985d179d98395b238cf253bbe2 SELECT `b` , `a` FROM `test` . `v1` 1 -test 1cfa171590aa40ba23e14c92d6940ffc TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1 +test 8b1406618d34996cd11d1796438c78b5 EXPLAIN SELECT * FROM `test` . `v1` 1 +test 2c9e5d5b30d1690ba1a625afb4c42005 EXPLAIN SELECT * FROM `test` . `v1` WHERE `a` = ? 1 +test 2265269dbe1b17d1f309a63b8e56933f EXPLAIN SELECT * FROM `test` . `v1` WHERE `b` > ? 1 +test 2df0babfc3c8ad27b4e3f99ad59bc938 EXPLAIN SELECT `a` , `b` FROM `test` . `v1` 1 +test 8607297e7ffe77aa19a9d60812c5a8fd EXPLAIN SELECT `b` , `a` FROM `test` . `v1` 1 +test 9c94fee7865aa050201f6e67887fd0c8 SELECT * FROM `test` . `v1` 1 +test 7c856ddf7b57d65f8124f39e8b81882e SELECT * FROM `test` . `v1` WHERE `a` = ? 1 +test 994b14d068c24edd8fd61b2f03663be2 SELECT * FROM `test` . `v1` WHERE `b` > ? 1 +test b3102e1f51878e35936d7d3fe2901839 SELECT `a` , `b` FROM `test` . `v1` 1 +test cb300dd6358987c5afe1a2b0022fdea0 SELECT `b` , `a` FROM `test` . `v1` 1 +test 1b40b63f6a9dbffd146f0916fe4f5ed2 TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1 DROP TABLE test.v1; CREATE VIEW test.v1 AS SELECT * FROM test.t1; EXPLAIN SELECT * from test.v1; @@ -248,19 +248,19 @@ SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR FROM performance_schema.events_statements_summary_by_digest ORDER BY DIGEST_TEXT; SCHEMA_NAME DIGEST DIGEST_TEXT COUNT_STAR -test 630a0b16be33fcf549f4f467de557940 CREATE VIEW `test` . `v1` AS SELECT * FROM `test` . `t1` 1 -test efa438aa8b4ddbf4c19f9e4b345781b5 DROP TABLE `test` . `v1` 1 -test 3bcc75e0f4658692e16837ead0c455ca EXPLAIN SELECT * FROM `test` . `v1` 2 -test cc55af8221ef0bc8873e75cc704489ba EXPLAIN SELECT * FROM `test` . `v1` WHERE `a` = ? 2 -test bc0498551a492bd7c0ca92906bf86fb4 EXPLAIN SELECT * FROM `test` . `v1` WHERE `b` > ? 2 -test 76923336b6f4ffffd0d5854761ae1aec EXPLAIN SELECT `a` , `b` FROM `test` . `v1` 2 -test a65d479062ffbfe374b4cd823016a260 EXPLAIN SELECT `b` , `a` FROM `test` . `v1` 2 -test d44b1e50ebfd05accacef4167534ad65 SELECT * FROM `test` . `v1` 2 -test 0608cde507ef8fe35d1f68730e78f1e4 SELECT * FROM `test` . `v1` WHERE `a` = ? 2 -test 5e241fdb866faef138cc81b26203ddff SELECT * FROM `test` . `v1` WHERE `b` > ? 2 -test a3788cc4bc96d260cfb5c3ebdfc65c6d SELECT SCHEMA_NAME , `DIGEST` , `DIGEST_TEXT` , `COUNT_STAR` FROM `performance_schema` . `events_statements_summary_by_digest` ORDER BY `DIGEST_TEXT` 1 -test 74fa770ce44994b3fe7b534d504f2d6d SELECT `a` , `b` FROM `test` . `v1` 2 -test 45ca2a985d179d98395b238cf253bbe2 SELECT `b` , `a` FROM `test` . `v1` 2 -test 1cfa171590aa40ba23e14c92d6940ffc TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1 +test a68fd555281a14d2809c3105e9cb2c90 CREATE VIEW `test` . `v1` AS SELECT * FROM `test` . `t1` 1 +test 4baff8f96e4b6ec6cdbfef5b9c7a8b12 DROP TABLE `test` . `v1` 1 +test 8b1406618d34996cd11d1796438c78b5 EXPLAIN SELECT * FROM `test` . `v1` 2 +test 2c9e5d5b30d1690ba1a625afb4c42005 EXPLAIN SELECT * FROM `test` . `v1` WHERE `a` = ? 2 +test 2265269dbe1b17d1f309a63b8e56933f EXPLAIN SELECT * FROM `test` . `v1` WHERE `b` > ? 2 +test 2df0babfc3c8ad27b4e3f99ad59bc938 EXPLAIN SELECT `a` , `b` FROM `test` . `v1` 2 +test 8607297e7ffe77aa19a9d60812c5a8fd EXPLAIN SELECT `b` , `a` FROM `test` . `v1` 2 +test 9c94fee7865aa050201f6e67887fd0c8 SELECT * FROM `test` . `v1` 2 +test 7c856ddf7b57d65f8124f39e8b81882e SELECT * FROM `test` . `v1` WHERE `a` = ? 2 +test 994b14d068c24edd8fd61b2f03663be2 SELECT * FROM `test` . `v1` WHERE `b` > ? 2 +test df38ce7f6e35972efe5a4ec57e48bf4d SELECT SCHEMA_NAME , `DIGEST` , `DIGEST_TEXT` , `COUNT_STAR` FROM `performance_schema` . `events_statements_summary_by_digest` ORDER BY `DIGEST_TEXT` 1 +test b3102e1f51878e35936d7d3fe2901839 SELECT `a` , `b` FROM `test` . `v1` 2 +test cb300dd6358987c5afe1a2b0022fdea0 SELECT `b` , `a` FROM `test` . `v1` 2 +test 1b40b63f6a9dbffd146f0916fe4f5ed2 TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1 DROP VIEW test.v1; DROP TABLE test.t1; diff --git a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result index a2d84753331..f0ceee5e214 100644 --- a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result +++ b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result @@ -8,5 +8,5 @@ SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1 #################################### SELECT event_name, digest, digest_text, sql_text FROM events_statements_history_long; event_name digest digest_text sql_text -statement/sql/select 70a97f8223477a66e716cfcc317193e2 SELECT ? + ? + SELECT ... -statement/sql/truncate ac4c7fc23914b333e9f40ce317816b8e TRUNCATE TABLE truncat... +statement/sql/select ca9181d6d668396d467dd974f58a9402 SELECT ? + ? + SELECT ... +statement/sql/truncate 4d2423d405bbcea8fa7bf9519c08dd8e TRUNCATE TABLE truncat... diff --git a/mysql-test/suite/rpl/r/rpl_parallel_seq.result b/mysql-test/suite/rpl/r/rpl_parallel_seq.result new file mode 100644 index 00000000000..ae4041f470d --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_parallel_seq.result @@ -0,0 +1,85 @@ +include/master-slave.inc +[connection master] +connection slave; +include/stop_slave.inc +ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; +# MDEV-29621 the sequence engine binlog_row_image-full events +# MDL-deadlock on the parallel slave. +connection master; +CREATE SEQUENCE s1; +SET @@session.binlog_row_image=FULL; +SET @@session.debug_dbug="+d,binlog_force_commit_id"; +SET @commit_id=7; +SET @@gtid_seq_no=100; +SELECT NEXT VALUE FOR s1; +NEXT VALUE FOR s1 +1 +INSERT INTO s1 VALUES(2, 1, 10, 1, 2, 1, 1, 0); +SET @@session.debug_dbug=""; +connection slave; +SET @@global.slave_parallel_threads=2; +SET @@global.slave_parallel_mode=optimistic; +SET @@global.debug_dbug="+d,hold_worker_on_schedule"; +include/start_slave.inc +SET DEBUG_SYNC = 'now SIGNAL continue_worker'; +connection master; +DROP SEQUENCE s1; +connection slave; +include/stop_slave.inc +# Simulate buggy 10.3.36 master to prove the parallel applier +# does not deadlock now at replaying the above master load. +connection master; +include/rpl_stop_server.inc [server_number=1] +include/rpl_start_server.inc [server_number=1] +connection slave; +RESET MASTER; +SET @@global.gtid_slave_pos=""; +CHANGE MASTER TO master_host='127.0.0.1', master_port=SERVER_MYPORT_1, master_user='root', master_use_gtid=slave_pos; +START SLAVE UNTIL MASTER_GTID_POS='0-1-102'; +SET DEBUG_SYNC = 'now SIGNAL continue_worker'; +# Normal stop is expected +include/wait_for_slave_to_stop.inc +# MDEV-31077 ALTER SEQUENCE may end up in optimistic parallel slave binlog out-of-order +# The test proves ALTER-SEQUENCE binlogs first before the following transaction does so. +connection slave; +include/stop_slave.inc +Warnings: +Note 1255 Slave already has been stopped +RESET MASTER; +SET @@global.gtid_slave_pos=""; +SET @@global.gtid_strict_mode=1; +connection master; +RESET MASTER; +CREATE TABLE ti (a INT) ENGINE=innodb; +CREATE SEQUENCE s2 ENGINE=innodb; +SET @@gtid_seq_no=100; +ALTER SEQUENCE s2 restart with 1; +INSERT INTO ti SET a=1; +include/save_master_gtid.inc +SELECT @@global.gtid_binlog_state "Master gtid state"; +Master gtid state +0-1-101 +connection slave; +include/start_slave.inc +SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "no 100,101 yet in both"; +@@global.gtid_binlog_state no 100,101 yet in both +0-1-2 0-1-2 +SET DEBUG_SYNC = 'now SIGNAL continue_worker'; +# Normal sync with master proves the fixes correct +include/sync_with_master_gtid.inc +SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "all through 101 have been committed"; +@@global.gtid_binlog_state all through 101 have been committed +0-1-101 0-1-101 +connection slave; +include/stop_slave.inc +SET debug_sync = RESET; +SET @@global.slave_parallel_threads= 0; +SET @@global.slave_parallel_mode= optimistic; +SET @@global.debug_dbug = ""; +SET @@global.gtid_strict_mode=0; +include/start_slave.inc +connection master; +DROP SEQUENCE s2; +DROP TABLE ti; +connection slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_binlog_index.test b/mysql-test/suite/rpl/t/rpl_binlog_index.test index 95c49c3d574..6112affb3c4 100644 --- a/mysql-test/suite/rpl/t/rpl_binlog_index.test +++ b/mysql-test/suite/rpl/t/rpl_binlog_index.test @@ -17,10 +17,6 @@ # BUG#12133 master.index file keeps mysqld from starting if bin log has been moved # BUG#42576 Relay logs in relay-log.info&localhost-relay-bin.index not processed after move -source include/master-slave.inc; -# There is no need to run this test case on all binlog format -source include/have_binlog_format_row.inc; - # Since this test relies heavily on filesystem operations (like # moving files around, backslashes and so forth) we avoid messing # around with windows access violations for not cluttering the @@ -28,6 +24,10 @@ source include/have_binlog_format_row.inc; # it is not 100% compliant. --source include/not_windows.inc +source include/master-slave.inc; +# There is no need to run this test case on all binlog format +source include/have_binlog_format_row.inc; + connection master; --let $master_datadir= `select @@datadir` connection slave; diff --git a/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test b/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test index 2cf184a3401..38537810906 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test @@ -2,10 +2,10 @@ if (`SELECT $PS_PROTOCOL != 0`) { --skip Test temporarily disabled for ps-protocol } +--source include/no_valgrind_without_big.inc --let $rpl_topology=1->2 --source include/rpl_init.inc --source include/have_innodb.inc ---source include/no_valgrind_without_big.inc --echo *** Test normal shutdown/restart of slave server configured as a GTID slave. *** diff --git a/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test index 9c804d8206a..a27a50d0fc4 100644 --- a/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test +++ b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test @@ -15,8 +15,8 @@ # # (iii) master and slave tables do not differ # --- source include/master-slave.inc -- source include/not_windows.inc +-- source include/master-slave.inc SET SQL_LOG_BIN=0; CREATE DATABASE B37656; diff --git a/mysql-test/suite/rpl/t/rpl_mdev12179.test b/mysql-test/suite/rpl/t/rpl_mdev12179.test index c8b9e665747..aa0d05211ff 100644 --- a/mysql-test/suite/rpl/t/rpl_mdev12179.test +++ b/mysql-test/suite/rpl/t/rpl_mdev12179.test @@ -1,7 +1,7 @@ +--source include/no_valgrind_without_big.inc --source include/have_innodb.inc --let $rpl_topology=1->2 --source include/rpl_init.inc ---source include/no_valgrind_without_big.inc --connection server_2 call mtr.add_suppression("The automatically created table.*name may not be entirely in lowercase"); diff --git a/mysql-test/suite/rpl/t/rpl_mdev382.test b/mysql-test/suite/rpl/t/rpl_mdev382.test index 093b7b92413..84e3c84982d 100644 --- a/mysql-test/suite/rpl/t/rpl_mdev382.test +++ b/mysql-test/suite/rpl/t/rpl_mdev382.test @@ -1,7 +1,7 @@ +--source include/not_windows.inc #unix shell escaping used for mysqlbinlog --source include/have_innodb.inc --source include/have_binlog_format_statement.inc --source include/master-slave.inc ---source include/not_windows.inc #unix shell escaping used for mysqlbinlog # MDEV-382: multiple SQL injections in replication code. diff --git a/mysql-test/suite/rpl/t/rpl_parallel_seq.test b/mysql-test/suite/rpl/t/rpl_parallel_seq.test new file mode 100644 index 00000000000..2a4fd96ff34 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_parallel_seq.test @@ -0,0 +1,131 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +--connection slave +--source include/stop_slave.inc +ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; + +--echo # MDEV-29621 the sequence engine binlog_row_image-full events +--echo # MDL-deadlock on the parallel slave. +--connection master +CREATE SEQUENCE s1; +SET @@session.binlog_row_image=FULL; +SET @@session.debug_dbug="+d,binlog_force_commit_id"; +SET @commit_id=7; +SET @@gtid_seq_no=100; +SELECT NEXT VALUE FOR s1; +INSERT INTO s1 VALUES(2, 1, 10, 1, 2, 1, 1, 0); +SET @@session.debug_dbug=""; + +--connection slave +--let $slave_parallel_threads=`select @@global.slave_parallel_threads` +--let $slave_parallel_mode=`select @@global.slave_parallel_mode` +SET @@global.slave_parallel_threads=2; +SET @@global.slave_parallel_mode=optimistic; +SET @@global.debug_dbug="+d,hold_worker_on_schedule"; +--source include/start_slave.inc + +--let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE state LIKE "Waiting for prior transaction to start commit" +--source include/wait_condition.inc +SET DEBUG_SYNC = 'now SIGNAL continue_worker'; + +--connection master +DROP SEQUENCE s1; +--sync_slave_with_master +--source include/stop_slave.inc + +--echo # Simulate buggy 10.3.36 master to prove the parallel applier +--echo # does not deadlock now at replaying the above master load. +--connection master +--let $datadir= `SELECT @@datadir` + +--let $rpl_server_number= 1 +--source include/rpl_stop_server.inc + +--remove_file $datadir/master-bin.000001 +--copy_file $MYSQL_TEST_DIR/std_data/rpl/master-bin-seq_10.3.36.000001 $datadir/master-bin.000001 + +--let $rpl_server_number= 1 +--source include/rpl_start_server.inc + +--source include/wait_until_connected_again.inc +--save_master_pos + +--connection slave +RESET MASTER; +SET @@global.gtid_slave_pos=""; + +--replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1 +eval CHANGE MASTER TO master_host='127.0.0.1', master_port=$SERVER_MYPORT_1, master_user='root', master_use_gtid=slave_pos; + +START SLAVE UNTIL MASTER_GTID_POS='0-1-102'; + +--let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE state LIKE "Waiting for prior transaction to commit" +--source include/wait_condition.inc +SET DEBUG_SYNC = 'now SIGNAL continue_worker'; + +--echo # Normal stop is expected +--source include/wait_for_slave_to_stop.inc + +--echo # MDEV-31077 ALTER SEQUENCE may end up in optimistic parallel slave binlog out-of-order +--echo # The test proves ALTER-SEQUENCE binlogs first before the following transaction does so. + +--connection slave +--source include/stop_slave.inc +RESET MASTER; +SET @@global.gtid_slave_pos=""; +--let $slave_gtid_strict_mode=`select @@global.gtid_strict_mode` +SET @@global.gtid_strict_mode=1; +--connection master +RESET MASTER; + +# Load from master +CREATE TABLE ti (a INT) ENGINE=innodb; +CREATE SEQUENCE s2 ENGINE=innodb; + +SET @@gtid_seq_no=100; +ALTER SEQUENCE s2 restart with 1; +INSERT INTO ti SET a=1; +--source include/save_master_gtid.inc +SELECT @@global.gtid_binlog_state "Master gtid state"; + +--connection slave +--source include/start_slave.inc + +--let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE state LIKE "Waiting for prior transaction to commit" +--source include/wait_condition.inc + +SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "no 100,101 yet in both"; + +# DEBUG_DBUG extension point of hold_worker_on_schedule is reused +# (gets deployed) in Sql_cmd_alter_sequence::execute. +SET DEBUG_SYNC = 'now SIGNAL continue_worker'; + +--echo # Normal sync with master proves the fixes correct +--source include/sync_with_master_gtid.inc + +SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "all through 101 have been committed"; + +# +# MDEV-29621/MDEV-31077 clean up. +# +--connection slave +--source include/stop_slave.inc + +SET debug_sync = RESET; +--eval SET @@global.slave_parallel_threads= $slave_parallel_threads +--eval SET @@global.slave_parallel_mode= $slave_parallel_mode + SET @@global.debug_dbug = ""; +--eval SET @@global.gtid_strict_mode=$slave_gtid_strict_mode +--source include/start_slave.inc + +--connection master +DROP SEQUENCE s2; +DROP TABLE ti; + +--sync_slave_with_master + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test index 44c04dd62d3..7decd130a43 100644 --- a/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test +++ b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test @@ -3,10 +3,10 @@ # For details look into extra/rpl_tests/rpl_lower_case_table_names.test # --- source include/master-slave.inc --- source include/have_innodb.inc -- source include/not_windows.inc +-- source include/have_innodb.inc -- source include/have_binlog_format_row.inc +-- source include/master-slave.inc -- let $engine=InnoDB -- source include/rpl_lower_case_table_names.test diff --git a/mysql-test/suite/rpl/t/rpl_semi_sync_event.test b/mysql-test/suite/rpl/t/rpl_semi_sync_event.test index 7a7e1c1e074..d4df9b4041b 100644 --- a/mysql-test/suite/rpl/t/rpl_semi_sync_event.test +++ b/mysql-test/suite/rpl/t/rpl_semi_sync_event.test @@ -1,7 +1,7 @@ +source include/no_valgrind_without_big.inc; source include/not_embedded.inc; source include/have_innodb.inc; source include/master-slave.inc; -source include/no_valgrind_without_big.inc; let $engine_type= InnoDB; diff --git a/mysql-test/suite/rpl/t/rpl_ssl.test b/mysql-test/suite/rpl/t/rpl_ssl.test index 59a2af9f137..0420a6c8c2d 100644 --- a/mysql-test/suite/rpl/t/rpl_ssl.test +++ b/mysql-test/suite/rpl/t/rpl_ssl.test @@ -4,9 +4,9 @@ # Please check all dependent tests after modifying it # +source include/no_valgrind_without_big.inc; source include/have_ssl_communication.inc; source include/master-slave.inc; -source include/no_valgrind_without_big.inc; # create a user for replication that requires ssl encryption connection master; diff --git a/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test index 619b57994c2..3809cd89e4a 100644 --- a/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test +++ b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test @@ -3,10 +3,10 @@ # For details look into extra/rpl_tests/rpl_lower_case_table_names.test # +-- source include/not_windows.inc +-- source include/have_innodb.inc -- source include/have_binlog_format_mixed_or_statement.inc -- source include/master-slave.inc --- source include/have_innodb.inc --- source include/not_windows.inc -- let $engine=InnoDB -- source include/rpl_lower_case_table_names.test diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result index 80bd2d7af5f..7f2e1c6586c 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result @@ -1,60 +1,60 @@ set @@global.optimizer_switch=@@optimizer_switch; select @@global.optimizer_switch; @@global.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,not_null_range_scan=off +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,not_null_range_scan=off,hash_join_cardinality=off select @@session.optimizer_switch; @@session.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,not_null_range_scan=off +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,not_null_range_scan=off,hash_join_cardinality=off show global variables like 'optimizer_switch'; Variable_name Value -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,not_null_range_scan=off +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,not_null_range_scan=off,hash_join_cardinality=off show session variables like 'optimizer_switch'; Variable_name Value -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,not_null_range_scan=off +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,not_null_range_scan=off,hash_join_cardinality=off select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -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,not_null_range_scan=off +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,not_null_range_scan=off,hash_join_cardinality=off select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -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,not_null_range_scan=off +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,not_null_range_scan=off,hash_join_cardinality=off set global optimizer_switch=4101; set session optimizer_switch=2058; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off +index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off +index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off set global optimizer_switch="index_merge_sort_union=on"; set session optimizer_switch="index_merge=off"; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off +index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off +index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off +optimizer_switch index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off +optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off +OPTIMIZER_SWITCH index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off set session optimizer_switch="default"; select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off +index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off set optimizer_switch = replace(@@optimizer_switch, '=off', '=on'); Warnings: Warning 1681 'engine_condition_pushdown=on' is deprecated and will be removed in a future release select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,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=on,mrr_cost_based=on,mrr_sort_keys=on,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,not_null_range_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,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=on,mrr_cost_based=on,mrr_sort_keys=on,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,not_null_range_scan=on,hash_join_cardinality=on set global optimizer_switch=1.1; ERROR 42000: Incorrect argument type to variable 'optimizer_switch' set global optimizer_switch=1e1; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 7b2dc9ae316..40da0c920be 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -665,13 +665,13 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME CORE_FILE VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT write a core-file on crashes +VARIABLE_COMMENT Write core on crashes NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST OFF,ON READ_ONLY YES -COMMAND_LINE_ARGUMENT NULL +COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME DATADIR VARIABLE_SCOPE GLOBAL VARIABLE_TYPE VARCHAR @@ -2299,7 +2299,7 @@ VARIABLE_COMMENT Fine-tune the optimizer behavior NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,default +ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,hash_join_cardinality,default READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_TRACE diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index fc8169aba43..2e3173a2544 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -665,13 +665,13 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME CORE_FILE VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT write a core-file on crashes +VARIABLE_COMMENT Write core on crashes NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST OFF,ON READ_ONLY YES -COMMAND_LINE_ARGUMENT NULL +COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME DATADIR VARIABLE_SCOPE GLOBAL VARIABLE_TYPE VARCHAR @@ -2459,7 +2459,7 @@ VARIABLE_COMMENT Fine-tune the optimizer behavior NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,default +ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,hash_join_cardinality,default READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_TRACE diff --git a/mysql-test/suite/versioning/r/rpl.result b/mysql-test/suite/versioning/r/rpl.result index a6fa6b3fca7..17372c63e99 100644 --- a/mysql-test/suite/versioning/r/rpl.result +++ b/mysql-test/suite/versioning/r/rpl.result @@ -164,4 +164,28 @@ update t1 set i = 0; connection slave; connection master; drop table t1; +# check versioned -> versioned replication without any keys on duplicate records +connection master; +create table t1 (a INT) with system versioning; +insert into t1 values (1); +insert into t1 values (1); +delete from t1; +connection slave; +include/diff_tables.inc [master:test.t1,slave:test.t1] +connection master; +drop table t1; +connection slave; +# check unversioned -> versioned replication with non-unique keys on duplicate records +connection master; +set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b)); +connection slave; +set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b)) with system versioning; +connection master; +insert into t1 values (1,1); +insert into t1 values (1,1); +delete from t1; +connection slave; +include/diff_tables.inc [master:test.t1,slave:test.t1] +connection master; +drop table t1; include/rpl_end.inc diff --git a/mysql-test/suite/versioning/t/rpl.test b/mysql-test/suite/versioning/t/rpl.test index b5be68feece..7d78b60e6fa 100644 --- a/mysql-test/suite/versioning/t/rpl.test +++ b/mysql-test/suite/versioning/t/rpl.test @@ -133,4 +133,38 @@ sync_slave_with_master; connection master; drop table t1; + +# +# MDEV-30430: Enabling system versioning on tables without primary key breaks replication +# Note that bugs are only present with row binlog format +# +--echo # check versioned -> versioned replication without any keys on duplicate records +connection master; +create table t1 (a INT) with system versioning; +insert into t1 values (1); +insert into t1 values (1); +delete from t1; +sync_slave_with_master; +--let $diff_tables= master:test.t1,slave:test.t1 +--source include/diff_tables.inc +connection master; +drop table t1; +sync_slave_with_master; + +--echo # check unversioned -> versioned replication with non-unique keys on duplicate records +connection master; +set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b)); +connection slave; +set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b)) with system versioning; +connection master; +insert into t1 values (1,1); +insert into t1 values (1,1); +delete from t1; +sync_slave_with_master; +--let $diff_tables= master:test.t1,slave:test.t1 +--source include/diff_tables.inc + +connection master; +drop table t1; + --source include/rpl_end.inc |