summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
authorMarko Mäkelä <marko.makela@mariadb.com>2020-07-15 10:17:15 +0300
committerMarko Mäkelä <marko.makela@mariadb.com>2020-07-15 10:17:15 +0300
commit9936cfd531a49b0ba124c4eb38a6099ec420c7fd (patch)
tree52e3c6dd19846b7ff0d895c31e78bc1e781ca780 /mysql-test
parenta7634281aa64a549d19cd20ead7e80d0267271ee (diff)
parent8a0944080c950349aaf7c81bc8f8c63b262f98eb (diff)
downloadmariadb-git-9936cfd531a49b0ba124c4eb38a6099ec420c7fd.tar.gz
Merge 10.3 into 10.4
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/main/create_drop_function.result17
-rw-r--r--mysql-test/main/create_drop_function.test24
-rw-r--r--mysql-test/main/mysqltest_tracking_info.result4
-rw-r--r--mysql-test/main/mysqltest_tracking_info.test2
-rw-r--r--mysql-test/main/order_by.result55
-rw-r--r--mysql-test/main/order_by.test22
-rw-r--r--mysql-test/main/parser.result15
-rw-r--r--mysql-test/main/parser.test5
-rw-r--r--mysql-test/main/sp-error.result13
-rw-r--r--mysql-test/main/sp-error.test19
-rw-r--r--mysql-test/main/subselect_exists2in.result8
-rw-r--r--mysql-test/main/subselect_exists2in.test8
-rw-r--r--mysql-test/main/union.result29
-rw-r--r--mysql-test/main/union.test21
-rw-r--r--mysql-test/main/win.result45
-rw-r--r--mysql-test/main/win.test25
-rw-r--r--mysql-test/suite/mariabackup/huge_lsn.result4
-rw-r--r--mysql-test/suite/mariabackup/huge_lsn.test8
-rw-r--r--mysql-test/suite/sys_vars/r/optimizer_switch_basic.result22
-rw-r--r--mysql-test/suite/sys_vars/t/optimizer_switch_basic.test4
20 files changed, 293 insertions, 57 deletions
diff --git a/mysql-test/main/create_drop_function.result b/mysql-test/main/create_drop_function.result
index 3ba6581d61b..7f23c954d7f 100644
--- a/mysql-test/main/create_drop_function.result
+++ b/mysql-test/main/create_drop_function.result
@@ -52,3 +52,20 @@ body
DROP FUNCTION IF EXISTS f1;
Warnings:
Note 1305 FUNCTION test.f1 does not exist
+#
+# 10.1 Test
+#
+# MDEV-22654: Assertion `!is_set() || (m_status == DA_OK_BULK &&
+# is_bulk_op())' failed in Diagnostics_area::set_ok_status on FUNCTION replace
+#
+SET GLOBAL log_bin_trust_function_creators=0;
+CREATE FUNCTION f(c INT) RETURNS NUMERIC NO SQL RETURN 0;
+CREATE OR REPLACE FUNCTION f(c INT) RETURNS INT RETURN 0;
+ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
+CREATE OR REPLACE FUNCTION sp1_thisisaveryverylongnamelongnameverylongname_thisisaveryverylongname234872934(a INT) RETURNS INT RETURN 0;
+ERROR 42000: Identifier name 'sp1_thisisaveryverylongnamelongnameverylongname_thisisaveryverylongname234872934' is too long
+DROP FUNCTION IF EXISTS f;
+Warnings:
+Note 1305 FUNCTION test.f does not exist
+SET GLOBAL log_bin_trust_function_creators=1;
+# End of 10.1 Test
diff --git a/mysql-test/main/create_drop_function.test b/mysql-test/main/create_drop_function.test
index e4d3d684cd5..3c4770e25e3 100644
--- a/mysql-test/main/create_drop_function.test
+++ b/mysql-test/main/create_drop_function.test
@@ -1,3 +1,5 @@
+source include/have_log_bin.inc;
+
SET timestamp=UNIX_TIMESTAMP('2014-09-30 08:00:00');
CREATE FUNCTION f1(str char(20))
@@ -39,3 +41,25 @@ DROP FUNCTION IF EXISTS f1;
SELECT body FROM mysql.proc WHERE name like 'f1';
DROP FUNCTION IF EXISTS f1;
+--echo #
+--echo # 10.1 Test
+--echo #
+--echo # MDEV-22654: Assertion `!is_set() || (m_status == DA_OK_BULK &&
+--echo # is_bulk_op())' failed in Diagnostics_area::set_ok_status on FUNCTION replace
+--echo #
+
+SET GLOBAL log_bin_trust_function_creators=0;
+
+CREATE FUNCTION f(c INT) RETURNS NUMERIC NO SQL RETURN 0;
+
+--error ER_BINLOG_UNSAFE_ROUTINE
+CREATE OR REPLACE FUNCTION f(c INT) RETURNS INT RETURN 0;
+
+--error ER_TOO_LONG_IDENT
+CREATE OR REPLACE FUNCTION sp1_thisisaveryverylongnamelongnameverylongname_thisisaveryverylongname234872934(a INT) RETURNS INT RETURN 0;
+
+DROP FUNCTION IF EXISTS f;
+
+SET GLOBAL log_bin_trust_function_creators=1;
+
+--echo # End of 10.1 Test
diff --git a/mysql-test/main/mysqltest_tracking_info.result b/mysql-test/main/mysqltest_tracking_info.result
index 1b1db09d61d..9fa63436286 100644
--- a/mysql-test/main/mysqltest_tracking_info.result
+++ b/mysql-test/main/mysqltest_tracking_info.result
@@ -35,10 +35,10 @@ SET @@session.session_track_system_variables= @save_session_track_system_variabl
#
set @save_optimizer_switch=@@optimizer_switch;
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=off,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';
+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=off,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
+-- 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
Warnings:
Warning 1681 'engine_condition_pushdown=on' is deprecated and will be removed in a future release
diff --git a/mysql-test/main/mysqltest_tracking_info.test b/mysql-test/main/mysqltest_tracking_info.test
index da7732b5011..dc51167137a 100644
--- a/mysql-test/main/mysqltest_tracking_info.test
+++ b/mysql-test/main/mysqltest_tracking_info.test
@@ -36,7 +36,7 @@ set @save_optimizer_switch=@@optimizer_switch;
SET @@session.session_track_system_variables='optimizer_switch';
--enable_session_track_info
-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=off,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';
+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';
--disable_session_track_info
set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index 6fdbfbd530e..826abe97502 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -3377,6 +3377,61 @@ SET @@sort_buffer_size= @save_sort_buffer_size;
SET @@max_sort_length= @save_max_sort_length;
DROP TABLE t1;
#
+# MDEV-22390: Assertion `m_next_rec_ptr >= m_rawmem' failed in Filesort_buffer::spaceleft |
+# SIGSEGV in __memmove_avx_unaligned_erms from my_b_write (on optimized)
+#
+SET @save_max_sort_length= @@max_sort_length;
+SET @save_sort_buffer_size= @@sort_buffer_size;
+SET @save_max_length_for_sort_data= @@max_length_for_sort_data;
+SET max_sort_length=8;
+SET sort_buffer_size=1024;
+SET max_length_for_sort_data=7000;
+CREATE TABLE t1(a VARCHAR(64), b VARCHAR(2048))DEFAULT CHARSET=utf8;
+INSERT INTO t1 SELECT seq,seq from seq_1_to_100;
+ANALYZE FORMAT=JSON SELECT * FROM t1 ORDER BY a LIMIT 5;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "read_sorted_file": {
+ "r_rows": 5,
+ "filesort": {
+ "sort_key": "t1.a",
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "r_limit": 5,
+ "r_used_priority_queue": false,
+ "r_output_rows": 35,
+ "r_sort_passes": 1,
+ "r_buffer_size": "REPLACED",
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 100,
+ "r_rows": 100,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ }
+ }
+ }
+ }
+}
+SELECT * FROM t1 ORDER BY a LIMIT 5;
+a b
+1 1
+10 10
+100 100
+11 11
+12 12
+SET max_sort_length= @save_max_sort_length;
+SET sort_buffer_size= @save_sort_buffer_size;
+SET max_length_for_sort_data= @save_max_length_for_sort_data;
+DROP TABLE t1;
+#
# MDEV-13994: Bad join results with orderby_uses_equalities=on
#
CREATE TABLE books (
diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test
index 60fb2d05ab4..021390f5c2b 100644
--- a/mysql-test/main/order_by.test
+++ b/mysql-test/main/order_by.test
@@ -2180,6 +2180,28 @@ SET @@max_sort_length= @save_max_sort_length;
DROP TABLE t1;
--echo #
+--echo # MDEV-22390: Assertion `m_next_rec_ptr >= m_rawmem' failed in Filesort_buffer::spaceleft |
+--echo # SIGSEGV in __memmove_avx_unaligned_erms from my_b_write (on optimized)
+--echo #
+
+SET @save_max_sort_length= @@max_sort_length;
+SET @save_sort_buffer_size= @@sort_buffer_size;
+SET @save_max_length_for_sort_data= @@max_length_for_sort_data;
+SET max_sort_length=8;
+SET sort_buffer_size=1024;
+# needed to make sure we use addon fields
+SET max_length_for_sort_data=7000;
+CREATE TABLE t1(a VARCHAR(64), b VARCHAR(2048))DEFAULT CHARSET=utf8;
+INSERT INTO t1 SELECT seq,seq from seq_1_to_100;
+--source include/analyze-format.inc
+ANALYZE FORMAT=JSON SELECT * FROM t1 ORDER BY a LIMIT 5;
+SELECT * FROM t1 ORDER BY a LIMIT 5;
+SET max_sort_length= @save_max_sort_length;
+SET sort_buffer_size= @save_sort_buffer_size;
+SET max_length_for_sort_data= @save_max_length_for_sort_data;
+DROP TABLE t1;
+
+--echo #
--echo # MDEV-13994: Bad join results with orderby_uses_equalities=on
--echo #
diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result
index d9f98ac3611..42fdc01617b 100644
--- a/mysql-test/main/parser.result
+++ b/mysql-test/main/parser.result
@@ -932,11 +932,9 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20),(30);
SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a);
-a
-1
+ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION
SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a ORDER BY a);
-a
-1
+ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION
DROP TABLE t1;
# UNION with a parenthesed term
CREATE TABLE t1 (a INT);
@@ -999,14 +997,11 @@ DROP TABLE t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20),(30);
SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a);
-a
-1
+ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION
SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a);
-a
-1
+ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION
SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a) LIMIT 1;
-a
-1
+ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION
DROP TABLE t1;
# Derived table with ROLLUP
CREATE TABLE t1 (a INT);
diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test
index dba155f1b7c..8aa2fb528ea 100644
--- a/mysql-test/main/parser.test
+++ b/mysql-test/main/parser.test
@@ -1084,7 +1084,9 @@ CREATE TABLE t1 AS SELECT 1 LIMIT 1 UNION SELECT 2;
--echo # For now, we're testing the parser.
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20),(30);
+--error ER_AGGREGATE_ORDER_FOR_UNION
SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a);
+--error ER_AGGREGATE_ORDER_FOR_UNION
SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a ORDER BY a);
DROP TABLE t1;
@@ -1125,8 +1127,11 @@ DROP TABLE t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20),(30);
+--error ER_AGGREGATE_ORDER_FOR_UNION
SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a);
+--error ER_AGGREGATE_ORDER_FOR_UNION
SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a);
+--error ER_AGGREGATE_ORDER_FOR_UNION
SELECT 1 AS a UNION SELECT a FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY a) LIMIT 1;
DROP TABLE t1;
diff --git a/mysql-test/main/sp-error.result b/mysql-test/main/sp-error.result
index b7d9a3a5cc8..d77928fcf5e 100644
--- a/mysql-test/main/sp-error.result
+++ b/mysql-test/main/sp-error.result
@@ -2874,3 +2874,16 @@ SELECT @msg;
DROP FUNCTION f1;
DROP FUNCTION f2;
DROP TABLE t1;
+#
+# 10.2 Test
+#
+# MDEV-MDEV-22058: Assertion `!is_set() || (m_status == DA_OK_BULK &&
+# is_bulk_op())' failed in Diagnostics_area::set_ok_status
+#
+SET @old_optimizer_switch = @@SESSION.OPTIMIZER_SWITCH;
+SET @cmd:="SET @@SESSION.SQL_MODE=(SELECT 'a')";
+SET @@SESSION.OPTIMIZER_SWITCH="in_to_exists=OFF,materialization=OFF";
+ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on'
+PREPARE stmt FROM @cmd;
+set @@SESSION.OPTIMIZER_SWITCH=@old_optimizer_switch;
+# End of 10.2 Test
diff --git a/mysql-test/main/sp-error.test b/mysql-test/main/sp-error.test
index 95a7372a6f2..4b2169aad94 100644
--- a/mysql-test/main/sp-error.test
+++ b/mysql-test/main/sp-error.test
@@ -3846,3 +3846,22 @@ SELECT @msg;
DROP FUNCTION f1;
DROP FUNCTION f2;
DROP TABLE t1;
+
+--echo #
+--echo # 10.2 Test
+--echo #
+--echo # MDEV-MDEV-22058: Assertion `!is_set() || (m_status == DA_OK_BULK &&
+--echo # is_bulk_op())' failed in Diagnostics_area::set_ok_status
+--echo #
+
+SET @old_optimizer_switch = @@SESSION.OPTIMIZER_SWITCH;
+
+SET @cmd:="SET @@SESSION.SQL_MODE=(SELECT 'a')";
+
+--error ER_ILLEGAL_SUBQUERY_OPTIMIZER_SWITCHES
+SET @@SESSION.OPTIMIZER_SWITCH="in_to_exists=OFF,materialization=OFF";
+PREPARE stmt FROM @cmd;
+
+set @@SESSION.OPTIMIZER_SWITCH=@old_optimizer_switch;
+
+--echo # End of 10.2 Test
diff --git a/mysql-test/main/subselect_exists2in.result b/mysql-test/main/subselect_exists2in.result
index 950538ad114..576559c2e86 100644
--- a/mysql-test/main/subselect_exists2in.result
+++ b/mysql-test/main/subselect_exists2in.result
@@ -440,8 +440,8 @@ drop table t1,t3;
# MDEV-159 Assertion about not marked for read failed in
# String* Field_varstring::val_str(String*, String*)
#
-SET optimizer_switch = REPLACE( @@optimizer_switch, '=on', '=off' );
-SET optimizer_switch='in_to_exists=on,exists_to_in=on';
+SET optimizer_switch = REPLACE(REPLACE(@@optimizer_switch, '=on', '=off'), 'in_to_exists=off', 'in_to_exists=on');
+SET optimizer_switch='exists_to_in=on';
CREATE TABLE t1 ( a VARCHAR(1) );
INSERT INTO t1 VALUES ('k'),('m');
CREATE TABLE t2 ( b INT,
@@ -596,8 +596,8 @@ Warnings:
Note 1276 Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` semi join (`test`.`t1`) where `test`.`t1`.`a` = `test`.`alias`.`b` and `test`.`alias`.`b` > `test`.`alias`.`a`
-SET optimizer_switch = REPLACE( @@optimizer_switch, '=on', '=off' );
-SET optimizer_switch = 'exists_to_in=on,materialization=on,semijoin=off';
+SET optimizer_switch = REPLACE(REPLACE(@@optimizer_switch, '=on', '=off'), 'materialization=off', 'materialization=on');
+SET optimizer_switch = 'exists_to_in=on,semijoin=off';
SELECT * FROM t1 AS alias
WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b );
a b
diff --git a/mysql-test/main/subselect_exists2in.test b/mysql-test/main/subselect_exists2in.test
index 1354953e972..2a9947123d4 100644
--- a/mysql-test/main/subselect_exists2in.test
+++ b/mysql-test/main/subselect_exists2in.test
@@ -330,8 +330,8 @@ drop table t1,t3;
--echo # String* Field_varstring::val_str(String*, String*)
--echo #
-SET optimizer_switch = REPLACE( @@optimizer_switch, '=on', '=off' );
-SET optimizer_switch='in_to_exists=on,exists_to_in=on';
+SET optimizer_switch = REPLACE(REPLACE(@@optimizer_switch, '=on', '=off'), 'in_to_exists=off', 'in_to_exists=on');
+SET optimizer_switch='exists_to_in=on';
CREATE TABLE t1 ( a VARCHAR(1) );
INSERT INTO t1 VALUES ('k'),('m');
@@ -466,8 +466,8 @@ explain extended
SELECT * FROM t1 AS alias
WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b );
-SET optimizer_switch = REPLACE( @@optimizer_switch, '=on', '=off' );
-SET optimizer_switch = 'exists_to_in=on,materialization=on,semijoin=off';
+SET optimizer_switch = REPLACE(REPLACE(@@optimizer_switch, '=on', '=off'), 'materialization=off', 'materialization=on');
+SET optimizer_switch = 'exists_to_in=on,semijoin=off';
SELECT * FROM t1 AS alias
WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b );
explain extended
diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result
index e3a69f54949..ef3aed397ba 100644
--- a/mysql-test/main/union.result
+++ b/mysql-test/main/union.result
@@ -1794,8 +1794,7 @@ union
select 4
order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1)
;
-foo
-1
+ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION
prepare stmt1 from 'select 1 as foo
union
select 2
@@ -1805,12 +1804,7 @@ union
select 4
order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1)
';
-execute stmt1;
-foo
-1
-execute stmt1;
-foo
-1
+ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION
select 1 as foo
union
select 2
@@ -1820,8 +1814,7 @@ union
(select 4)
order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1)
;
-foo
-1
+ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION
prepare stmt1 from 'select 1 as foo
union
select 2
@@ -1831,13 +1824,7 @@ union
(select 4)
order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1)
';
-execute stmt1;
-foo
-1
-execute stmt1;
-foo
-1
-deallocate prepare stmt1;
+ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION
End of 5.1 tests
#
# mdev-5091: Asseirtion failure for UNION with ORDER BY
@@ -2336,6 +2323,14 @@ Warnings:
Note 1003 /* select#1 */ select 1 AS `1`,2 AS `2` union all /* select#2 */ select 1 AS `i`,count(0) AS `COUNT(*)` from `test`.`t2` where 0 group by 1 having 1
DROP TABLE t1,t2;
#
+# MDEV-10120: Wrong result of UNION .. ORDER BY GROUP_CONCAT()
+#
+CREATE TABLE t1 (a INT);
+INSERT t1 VALUES (1),(2),(3);
+(SELECT 1 AS a) UNION (SELECT a FROM t1 GROUP BY a) ORDER BY GROUP_CONCAT(a);
+ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION
+DROP TABLE t1;
+#
# Start of 10.3 tests
#
#
diff --git a/mysql-test/main/union.test b/mysql-test/main/union.test
index a90d27e161d..33adbb4603d 100644
--- a/mysql-test/main/union.test
+++ b/mysql-test/main/union.test
@@ -1210,11 +1210,11 @@ select 4
order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1)
;
+--error ER_AGGREGATE_ORDER_FOR_UNION
eval $my_stmt;
+--error ER_AGGREGATE_ORDER_FOR_UNION
eval prepare stmt1 from '$my_stmt';
-execute stmt1;
-execute stmt1;
let $my_stmt=
select 1 as foo
@@ -1227,13 +1227,11 @@ union
order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1)
;
+--error ER_AGGREGATE_ORDER_FOR_UNION
eval $my_stmt;
+--error ER_AGGREGATE_ORDER_FOR_UNION
eval prepare stmt1 from '$my_stmt';
-execute stmt1;
-execute stmt1;
-
-deallocate prepare stmt1;
--echo End of 5.1 tests
@@ -1663,6 +1661,17 @@ eval EXPLAIN EXTENDED $q;
DROP TABLE t1,t2;
+--echo #
+--echo # MDEV-10120: Wrong result of UNION .. ORDER BY GROUP_CONCAT()
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT t1 VALUES (1),(2),(3);
+
+--error ER_AGGREGATE_ORDER_FOR_UNION
+(SELECT 1 AS a) UNION (SELECT a FROM t1 GROUP BY a) ORDER BY GROUP_CONCAT(a);
+DROP TABLE t1;
+
--echo #
--echo # Start of 10.3 tests
diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result
index c522c8b9e82..d5565bca7d0 100644
--- a/mysql-test/main/win.result
+++ b/mysql-test/main/win.result
@@ -3798,6 +3798,51 @@ SELECT FIRST_VALUE(MAX(a) OVER (PARTITION BY a)) OVER (ORDER BY a) AS x FROM t1
ERROR HY000: Window functions can not be used as arguments to group functions.
DROP TABLE t1;
#
+# MDEV-12059: Assertion `precision > 0' failed with a window function or window aggregate function
+#
+CREATE TABLE t1 (d DECIMAL(1,0) UNSIGNED);
+INSERT INTO t1 VALUES (1),(2);
+SELECT MIN(d) OVER () FROM t1;
+MIN(d) OVER ()
+1
+1
+DROP TABLE t1;
+#
+# MDEV-22463: Element_type &Bounds_checked_array<Item *>::operator[](size_t) [Element_type = Item *]:
+# Assertion `n < m_size' failed
+#
+CREATE TABLE t1 (a INT, b INT, c INT, d INT, e INT, f INT, g int, h INT, i INT);
+INSERT INTO t1 SELECT seq,seq,seq,seq, seq,seq,seq,seq,seq FROM seq_1_to_5;
+SELECT ROW_NUMBER() OVER w2 FROM t1 WINDOW w2 AS (PARTITION BY -1,0,1,2,3,4,5,6);
+ROW_NUMBER() OVER w2
+1
+2
+3
+4
+5
+SELECT a FROM t1 ORDER BY ROW_NUMBER() OVER (PARTITION BY -1,1,0,2,3,4,5,6,7,8);
+a
+1
+2
+3
+4
+5
+SELECT a,b FROM t1 WINDOW w2 AS (PARTITION BY -1,1,0,2,3,4);
+a b
+1 1
+2 2
+3 3
+4 4
+5 5
+SELECT ROW_NUMBER() OVER w2 FROM t1 WINDOW w2 AS (PARTITION BY -1,0,1,2,3,4,5,6);
+ROW_NUMBER() OVER w2
+1
+2
+3
+4
+5
+DROP TABLE t1;
+#
# End of 10.2 tests
#
#
diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test
index a4b816e7136..7ef505fc3da 100644
--- a/mysql-test/main/win.test
+++ b/mysql-test/main/win.test
@@ -1,6 +1,7 @@
#
# Window Functions Tests
#
+--source include/have_sequence.inc
--disable_warnings
drop table if exists t1,t2;
@@ -2473,6 +2474,30 @@ SELECT FIRST_VALUE(MAX(a) OVER (PARTITION BY a)) OVER (ORDER BY a) AS x FROM t1
DROP TABLE t1;
--echo #
+--echo # MDEV-12059: Assertion `precision > 0' failed with a window function or window aggregate function
+--echo #
+
+CREATE TABLE t1 (d DECIMAL(1,0) UNSIGNED);
+INSERT INTO t1 VALUES (1),(2);
+SELECT MIN(d) OVER () FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-22463: Element_type &Bounds_checked_array<Item *>::operator[](size_t) [Element_type = Item *]:
+--echo # Assertion `n < m_size' failed
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, c INT, d INT, e INT, f INT, g int, h INT, i INT);
+INSERT INTO t1 SELECT seq,seq,seq,seq, seq,seq,seq,seq,seq FROM seq_1_to_5;
+
+SELECT ROW_NUMBER() OVER w2 FROM t1 WINDOW w2 AS (PARTITION BY -1,0,1,2,3,4,5,6);
+--sorted_result
+SELECT a FROM t1 ORDER BY ROW_NUMBER() OVER (PARTITION BY -1,1,0,2,3,4,5,6,7,8);
+SELECT a,b FROM t1 WINDOW w2 AS (PARTITION BY -1,1,0,2,3,4);
+SELECT ROW_NUMBER() OVER w2 FROM t1 WINDOW w2 AS (PARTITION BY -1,0,1,2,3,4,5,6);
+DROP TABLE t1;
+
+--echo #
--echo # End of 10.2 tests
--echo #
diff --git a/mysql-test/suite/mariabackup/huge_lsn.result b/mysql-test/suite/mariabackup/huge_lsn.result
index 82d743bbad4..1af19ef3cf8 100644
--- a/mysql-test/suite/mariabackup/huge_lsn.result
+++ b/mysql-test/suite/mariabackup/huge_lsn.result
@@ -17,3 +17,7 @@ SELECT * FROM t;
i
1
DROP TABLE t;
+# shutdown server
+# remove datadir
+# xtrabackup move back
+# restart
diff --git a/mysql-test/suite/mariabackup/huge_lsn.test b/mysql-test/suite/mariabackup/huge_lsn.test
index d41bb61a096..055c92f9a55 100644
--- a/mysql-test/suite/mariabackup/huge_lsn.test
+++ b/mysql-test/suite/mariabackup/huge_lsn.test
@@ -8,6 +8,10 @@
let INNODB_PAGE_SIZE=`select @@innodb_page_size`;
let MYSQLD_DATADIR=`select @@datadir`;
+let $targetdir_old=$MYSQLTEST_VARDIR/tmp/backup_1;
+--disable_result_log
+exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$targetdir_old;
+--enable_result_log
--source include/shutdown_mysqld.inc
perl;
@@ -56,3 +60,7 @@ exec $XTRABACKUP --prepare --target-dir=$targetdir;
SELECT * FROM t;
DROP TABLE t;
rmdir $targetdir;
+let $targetdir= $targetdir_old;
+exec $XTRABACKUP --prepare --target-dir=$targetdir;
+--source include/restart_and_restore.inc
+rmdir $targetdir_old;
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 91932c966f6..5c76684306a 100644
--- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
+++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
@@ -17,38 +17,38 @@ OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,i
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
-set global optimizer_switch=10;
-set session optimizer_switch=5;
+set global optimizer_switch=4101;
+set session optimizer_switch=2058;
select @@global.optimizer_switch;
@@global.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=off,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
+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
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=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
+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
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=off,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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,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
+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
select @@session.optimizer_switch;
@@session.optimizer_switch
-index_merge=off,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=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
+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
show global variables like 'optimizer_switch';
Variable_name Value
-optimizer_switch index_merge=off,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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,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
+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
show session variables like 'optimizer_switch';
Variable_name Value
-optimizer_switch index_merge=off,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=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
+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
select * from information_schema.global_variables where variable_name='optimizer_switch';
VARIABLE_NAME VARIABLE_VALUE
-OPTIMIZER_SWITCH index_merge=off,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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,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
+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
select * from information_schema.session_variables where variable_name='optimizer_switch';
VARIABLE_NAME VARIABLE_VALUE
-OPTIMIZER_SWITCH index_merge=off,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=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
+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
set session optimizer_switch="default";
select @@session.optimizer_switch;
@@session.optimizer_switch
-index_merge=off,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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,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
+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
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
diff --git a/mysql-test/suite/sys_vars/t/optimizer_switch_basic.test b/mysql-test/suite/sys_vars/t/optimizer_switch_basic.test
index ef9d8f17832..ada22845f3e 100644
--- a/mysql-test/suite/sys_vars/t/optimizer_switch_basic.test
+++ b/mysql-test/suite/sys_vars/t/optimizer_switch_basic.test
@@ -19,8 +19,8 @@ select * from information_schema.session_variables where variable_name='optimize
#
# show that it's writable
#
-set global optimizer_switch=10;
-set session optimizer_switch=5;
+set global optimizer_switch=4101;
+set session optimizer_switch=2058;
select @@global.optimizer_switch;
select @@session.optimizer_switch;
set global optimizer_switch="index_merge_sort_union=on";