diff options
Diffstat (limited to 'mysql-test')
22 files changed, 302 insertions, 65 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 4ce4da31c13..bbbb2be9e01 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 +-- 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 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 ee0afdd4c33..0b28f15b968 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -3301,6 +3301,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 2e32f097c3f..ee61564e6c1 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -2179,6 +2179,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 2ed1032f6cf..002993eb128 100644 --- a/mysql-test/main/parser.result +++ b/mysql-test/main/parser.result @@ -936,11 +936,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); @@ -1003,14 +1001,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 040678a51df..4ee335dbbb4 100644 --- a/mysql-test/main/parser.test +++ b/mysql-test/main/parser.test @@ -1081,7 +1081,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; @@ -1122,8 +1124,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 dcca8434cf1..47d601eb4fe 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 61a3974a1df..082b7b8698f 100644 --- a/mysql-test/main/sp-error.test +++ b/mysql-test/main/sp-error.test @@ -3851,3 +3851,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 a13c0e525bd..431d60b8c29 100644 --- a/mysql-test/main/subselect_exists2in.result +++ b/mysql-test/main/subselect_exists2in.result @@ -437,8 +437,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, @@ -593,8 +593,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 3c8ef81537e..ea16e621e17 100644 --- a/mysql-test/main/union.result +++ b/mysql-test/main/union.result @@ -1779,8 +1779,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 @@ -1790,12 +1789,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 @@ -1805,8 +1799,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 @@ -1816,13 +1809,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 @@ -2321,6 +2308,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 1 group by 1 having 0 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 8c0540375f2..714b6abe131 100644 --- a/mysql-test/main/union.test +++ b/mysql-test/main/union.test @@ -1200,11 +1200,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 @@ -1217,13 +1217,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 @@ -1653,6 +1651,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 2b3b7eb8500..ca7d5c234cb 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 0b7778965ef..64fda5e7793 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/galera/r/galera_toi_truncate.result b/mysql-test/suite/galera/r/galera_toi_truncate.result index f299eacda00..c92da869a02 100644 --- a/mysql-test/suite/galera/r/galera_toi_truncate.result +++ b/mysql-test/suite/galera/r/galera_toi_truncate.result @@ -5,12 +5,12 @@ CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; connection node_2; SET SESSION wsrep_retry_autocommit = 0; INSERT INTO t1 (f1) SELECT 1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5, ten AS a6, ten AS a7, ten AS a8; +connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2; +connection node_2a; connection node_1; -TRUNCATE TABLE t1;; +TRUNCATE TABLE t1; connection node_2; ERROR 40001: Deadlock: wsrep aborted transaction -connection node_1; -connection node_2; SELECT COUNT(*) AS EXPECT_0 FROM t1; EXPECT_0 0 diff --git a/mysql-test/suite/galera/t/galera_toi_truncate.test b/mysql-test/suite/galera/t/galera_toi_truncate.test index 30e0e802816..5b23a8c3f3e 100644 --- a/mysql-test/suite/galera/t/galera_toi_truncate.test +++ b/mysql-test/suite/galera/t/galera_toi_truncate.test @@ -25,17 +25,18 @@ CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; SET SESSION wsrep_retry_autocommit = 0; --send INSERT INTO t1 (f1) SELECT 1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5, ten AS a6, ten AS a7, ten AS a8 +--connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2 +--connection node_2a +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE LIKE 'Sending data%' AND INFO LIKE 'INSERT INTO t1 (f1)%'; +--source include/wait_condition.inc + --connection node_1 ---send TRUNCATE TABLE t1; +TRUNCATE TABLE t1; --connection node_2 --error ER_LOCK_DEADLOCK --reap ---connection node_1 ---reap - ---connection node_2 SELECT COUNT(*) AS EXPECT_0 FROM t1; --connection node_1 diff --git a/mysql-test/suite/mariabackup/huge_lsn.result b/mysql-test/suite/mariabackup/huge_lsn.result index e7c4cc9471d..61ca3400721 100644 --- a/mysql-test/suite/mariabackup/huge_lsn.result +++ b/mysql-test/suite/mariabackup/huge_lsn.result @@ -16,3 +16,7 @@ SELECT * FROM t; i 1 DROP TABLE t; +# shutdown server +# remove datadir +# xtrabackup move back +# restart server diff --git a/mysql-test/suite/mariabackup/huge_lsn.test b/mysql-test/suite/mariabackup/huge_lsn.test index dcdc118df6f..3349ef40df8 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; @@ -54,3 +58,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 a09b569a72e..fe752e28d56 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=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=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 +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 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 +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 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 +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 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 +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 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 +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 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 +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 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 +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 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 +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 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 +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 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"; |