diff options
| author | Sergei Golubchik <sergii@pisem.net> | 2011-11-22 18:04:38 +0100 |
|---|---|---|
| committer | Sergei Golubchik <sergii@pisem.net> | 2011-11-22 18:04:38 +0100 |
| commit | d2755a2c9c109ddb4e2e0c9feda89431a6c4fd50 (patch) | |
| tree | c6e4678908c750d7f558e98cedc349aa1d350892 /mysql-test/suite | |
| parent | af32b02c06f32a89dc9f52e556bc5dd3bf49c19e (diff) | |
| parent | 42221abaed700f6dc5d280b462755851780e8487 (diff) | |
| download | mariadb-git-d2755a2c9c109ddb4e2e0c9feda89431a6c4fd50.tar.gz | |
5.3->5.5 merge
Diffstat (limited to 'mysql-test/suite')
68 files changed, 616 insertions, 421 deletions
diff --git a/mysql-test/suite/binlog/r/binlog_row_annotate.result b/mysql-test/suite/binlog/r/binlog_row_annotate.result index 9442dd63efb..0c008661784 100644 --- a/mysql-test/suite/binlog/r/binlog_row_annotate.result +++ b/mysql-test/suite/binlog/r/binlog_row_annotate.result @@ -427,7 +427,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; # ##################################################################################### -# mysqlbinlog --skip-annotate-rows-events +# mysqlbinlog --skip-annotate-row-events # No Annotates should appear in this output ##################################################################################### /*!40019 SET @@session.max_insert_delayed_threads=0*/; @@ -1013,7 +1013,7 @@ ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; # ##################################################################################### -# mysqlbinlog --read-from-remote-server --skip-annotate-rows-events +# mysqlbinlog --read-from-remote-server --skip-annotate-row-events # No Annotates should appear in this output ##################################################################################### /*!40019 SET @@session.max_insert_delayed_threads=0*/; diff --git a/mysql-test/suite/binlog/t/binlog_row_annotate.test b/mysql-test/suite/binlog/t/binlog_row_annotate.test index c5db9ef2148..a0f72c9222c 100644 --- a/mysql-test/suite/binlog/t/binlog_row_annotate.test +++ b/mysql-test/suite/binlog/t/binlog_row_annotate.test @@ -1,11 +1,11 @@ ############################################################################### # WL47: Store in binlog text of statements that caused RBR events # new event: ANNOTATE_ROWS_EVENT -# new master option: --binlog-annotate-rows-events -# new mysqlbinlog option: --skip-annotate-rows-events +# new master option: --binlog-annotate-row-events +# new mysqlbinlog option: --skip-annotate-row-events # # Intended to test that: -# *** If the --binlog-annotate-rows-events option is switched on on master +# *** If the --binlog-annotate-row-events option is switched on on master # then Annotate_rows events: # - are generated; # - are genrated only once for "multi-table-maps" rbr queries; @@ -13,9 +13,9 @@ # - are generated when the corresponding queries are filtered away partialy # (e.g. in case of multi-delete). # *** Annotate_rows events are printed by mysqlbinlog started without -# --skip-annotate-rows-events options both in remote and local cases. +# --skip-annotate-row-events options both in remote and local cases. # *** Annotate_rows events are not printed by mysqlbinlog started with -# --skip-annotate-rows-events options both in remote and local cases. +# --skip-annotate-row-events options both in remote and local cases. ############################################################################### --source include/have_log_bin.inc @@ -54,11 +54,11 @@ CREATE TABLE xtest1.xt1(a int); CREATE DATABASE xtest2; CREATE TABLE xtest2.xt2(a int); -# By default SESSION binlog_annotate_rows_events = OFF +# By default SESSION binlog_annotate_row_events = OFF INSERT INTO test1.t1 VALUES (1), (2), (3); -SET SESSION binlog_annotate_rows_events = ON; +SET SESSION binlog_annotate_row_events = ON; INSERT INTO test2.t2 VALUES (1), (2), (3); INSERT INTO test3.t3 VALUES (1), (2), (3); @@ -133,13 +133,13 @@ let $MYSQLD_DATADIR= `select @@datadir`; --echo # --echo ##################################################################################### ---echo # mysqlbinlog --skip-annotate-rows-events +--echo # mysqlbinlog --skip-annotate-row-events --echo # No Annotates should appear in this output --echo ##################################################################################### let $MYSQLD_DATADIR= `select @@datadir`; --replace_regex /server id [0-9]*/server id #/ /server v [^ ]*/server v #.##.##/ /exec_time=[0-9]*/exec_time=#/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ ---exec $MYSQL_BINLOG --base64-output=decode-rows --skip-annotate-rows-events -v -v $MYSQLD_DATADIR/master-bin.000001 +--exec $MYSQL_BINLOG --base64-output=decode-rows --skip-annotate-row-events -v -v $MYSQLD_DATADIR/master-bin.000001 --echo # --echo ##################################################################################### @@ -169,13 +169,13 @@ let $MYSQLD_DATADIR= `select @@datadir`; --echo # --echo ##################################################################################### ---echo # mysqlbinlog --read-from-remote-server --skip-annotate-rows-events +--echo # mysqlbinlog --read-from-remote-server --skip-annotate-row-events --echo # No Annotates should appear in this output --echo ##################################################################################### let $MYSQLD_DATADIR= `select @@datadir`; --replace_regex /server id [0-9]*/server id #/ /server v [^ ]*/server v #.##.##/ /exec_time=[0-9]*/exec_time=#/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ ---exec $MYSQL_BINLOG --base64-output=decode-rows --skip-annotate-rows-events -v -v --read-from-remote-server --user=root --host=localhost --port=$MASTER_MYPORT master-bin.000001 +--exec $MYSQL_BINLOG --base64-output=decode-rows --skip-annotate-row-events -v -v --read-from-remote-server --user=root --host=localhost --port=$MASTER_MYPORT master-bin.000001 # Clean-up diff --git a/mysql-test/suite/funcs_1/r/innodb_func_view.result b/mysql-test/suite/funcs_1/r/innodb_func_view.result index cea131523e8..29a2a2aa861 100644 --- a/mysql-test/suite/funcs_1/r/innodb_func_view.result +++ b/mysql-test/suite/funcs_1/r/innodb_func_view.result @@ -3716,8 +3716,8 @@ NULL 1.7976931348623e308 3 -00:00:01 -1 5 00:17:58 1758 25 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3 +Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1 DROP VIEW v1; @@ -4068,10 +4068,10 @@ NULL 1.7976931348623e308 3 NULL -1 5 NULL 200506271758 19 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3 -Warning 1292 Incorrect datetime value: '-1' for column 'my_double' at row 5 -Warning 1292 Incorrect datetime value: '200506271758' for column 'my_double' at row 19 +Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '-1' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '200506271758' for column 'my_double' at row 1 DROP VIEW v1; @@ -4429,9 +4429,9 @@ NULL 1.7976931348623e308 3 NULL -1 5 2005-06-27 20050627 13 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3 -Warning 1292 Incorrect datetime value: '-1' for column 'my_double' at row 5 +Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '-1' for column 'my_double' at row 1 DROP VIEW v1; diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result index 417228db52d..4eff12dab7b 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result @@ -205,7 +205,7 @@ def mysql user Lock_tables_priv 21 N NO enum 1 3 NULL NULL NULL utf8 utf8_genera def mysql user max_connections 39 0 NO int NULL NULL 10 0 NULL NULL NULL int(11) unsigned select,insert,update,references def mysql user max_questions 37 0 NO int NULL NULL 10 0 NULL NULL NULL int(11) unsigned select,insert,update,references def mysql user max_updates 38 0 NO int NULL NULL 10 0 NULL NULL NULL int(11) unsigned select,insert,update,references -def mysql user max_user_connections 40 0 NO int NULL NULL 10 0 NULL NULL NULL int(11) unsigned select,insert,update,references +def mysql user max_user_connections 40 0 NO int NULL NULL 10 0 NULL NULL NULL int(11) select,insert,update,references def mysql user Password 3 NO char 41 41 NULL NULL NULL latin1 latin1_bin char(41) select,insert,update,references def mysql user plugin 41 NO char 64 64 NULL NULL NULL latin1 latin1_swedish_ci char(64) select,insert,update,references def mysql user Process_priv 12 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references @@ -515,6 +515,6 @@ NULL mysql time_zone_transition_type Is_DST tinyint NULL NULL NULL NULL tinyint( NULL mysql user max_questions int NULL NULL NULL NULL int(11) unsigned NULL mysql user max_updates int NULL NULL NULL NULL int(11) unsigned NULL mysql user max_connections int NULL NULL NULL NULL int(11) unsigned -NULL mysql user max_user_connections int NULL NULL NULL NULL int(11) unsigned +NULL mysql user max_user_connections int NULL NULL NULL NULL int(11) 1.0000 mysql user plugin char 64 64 latin1 latin1_swedish_ci char(64) 1.0000 mysql user authentication_string text 65535 65535 utf8 utf8_bin text diff --git a/mysql-test/suite/funcs_1/r/memory_func_view.result b/mysql-test/suite/funcs_1/r/memory_func_view.result index 23aba87e7bf..d8f26c81e58 100644 --- a/mysql-test/suite/funcs_1/r/memory_func_view.result +++ b/mysql-test/suite/funcs_1/r/memory_func_view.result @@ -3717,8 +3717,8 @@ NULL 1.7976931348623e308 3 -00:00:01 -1 5 00:17:58 1758 25 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3 +Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1 DROP VIEW v1; @@ -4069,10 +4069,10 @@ NULL 1.7976931348623e308 3 NULL -1 5 NULL 200506271758 19 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3 -Warning 1292 Incorrect datetime value: '-1' for column 'my_double' at row 5 -Warning 1292 Incorrect datetime value: '200506271758' for column 'my_double' at row 6 +Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '-1' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '200506271758' for column 'my_double' at row 1 DROP VIEW v1; @@ -4430,9 +4430,9 @@ NULL 1.7976931348623e308 3 NULL -1 5 2005-06-27 20050627 13 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3 -Warning 1292 Incorrect datetime value: '-1' for column 'my_double' at row 5 +Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '-1' for column 'my_double' at row 1 DROP VIEW v1; diff --git a/mysql-test/suite/funcs_1/r/myisam_func_view.result b/mysql-test/suite/funcs_1/r/myisam_func_view.result index 23aba87e7bf..d8f26c81e58 100644 --- a/mysql-test/suite/funcs_1/r/myisam_func_view.result +++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result @@ -3717,8 +3717,8 @@ NULL 1.7976931348623e308 3 -00:00:01 -1 5 00:17:58 1758 25 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3 +Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1 DROP VIEW v1; @@ -4069,10 +4069,10 @@ NULL 1.7976931348623e308 3 NULL -1 5 NULL 200506271758 19 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3 -Warning 1292 Incorrect datetime value: '-1' for column 'my_double' at row 5 -Warning 1292 Incorrect datetime value: '200506271758' for column 'my_double' at row 6 +Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '-1' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '200506271758' for column 'my_double' at row 1 DROP VIEW v1; @@ -4430,9 +4430,9 @@ NULL 1.7976931348623e308 3 NULL -1 5 2005-06-27 20050627 13 Warnings: -Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2 -Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3 -Warning 1292 Incorrect datetime value: '-1' for column 'my_double' at row 5 +Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1 +Warning 1292 Incorrect datetime value: '-1' for column 'my_double' at row 1 DROP VIEW v1; diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index 5f37ae6e2c6..efb47f65e6d 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -785,7 +785,7 @@ create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h insert into t1 values (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,1,1,1,1); explain select * from t1 where a > 0 and a < 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using index condition drop table t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index b8b507112b7..44cd2bd9573 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -532,6 +532,7 @@ DROP TABLE t1; create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = innodb; insert into t1 values (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,1,1,1,1); --replace_column 9 # +--replace_result "Using where" "Using index condition" explain select * from t1 where a > 0 and a < 50; drop table t1; diff --git a/mysql-test/suite/maria/r/maria.result b/mysql-test/suite/maria/r/maria.result index 7baf561eea3..320c081382f 100644 --- a/mysql-test/suite/maria/r/maria.result +++ b/mysql-test/suite/maria/r/maria.result @@ -361,7 +361,8 @@ ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e); ERROR 42000: Specified key was too long; max key length is 1208 bytes DROP TABLE t1; CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a)); -INSERT into t1 values (0, null, 0), (0, null, 1), (0, null, 2), (0, null,3), (1,1,4); +INSERT into t1 values (0,null,0), (0,null,1), (0,null,2), (0,null,3), (1,1,4); +INSERT into t1 values (2,4,5), (7,8,4), (8,3,1), (9,7,2), (5,5,9); create table t2 (a int not null, b int, c int, key(b), key(c), key(a)); INSERT into t2 values (1,1,1), (2,2,2); optimize table t1; @@ -372,24 +373,24 @@ Table Op Msg_type Msg_text test.t1 check status OK show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 b 1 b A 5 NULL NULL YES BTREE -t1 1 c 1 c A 5 NULL NULL YES BTREE -t1 1 a 1 a A 1 NULL NULL BTREE -t1 1 a 2 b A 5 NULL NULL YES BTREE -t1 1 c_2 1 c A 5 NULL NULL YES BTREE -t1 1 c_2 2 a A 5 NULL NULL BTREE +t1 1 b 1 b A 10 NULL NULL YES BTREE +t1 1 c 1 c A 10 NULL NULL YES BTREE +t1 1 a 1 a A 10 NULL NULL BTREE +t1 1 a 2 b A 10 NULL NULL YES BTREE +t1 1 c_2 1 c A 10 NULL NULL YES BTREE +t1 1 c_2 2 a A 10 NULL NULL BTREE explain select * from t1,t2 where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a NULL NULL NULL 2 -1 SIMPLE t1 ref a a 4 test.t2.a 3 +1 SIMPLE t1 ref a a 4 test.t2.a 1 explain select * from t1,t2 force index(a) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a NULL NULL NULL 2 -1 SIMPLE t1 ref a a 4 test.t2.a 3 +1 SIMPLE t1 ref a a 4 test.t2.a 1 explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a NULL NULL NULL 2 -1 SIMPLE t1 ref a a 4 test.t2.a 3 +1 SIMPLE t1 ref a a 4 test.t2.a 1 explain select * from t1,t2 where t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL b NULL NULL NULL 2 Using where @@ -397,19 +398,19 @@ id select_type table type possible_keys key key_len ref rows Extra explain select * from t1,t2 force index(c) where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 -1 SIMPLE t1 ref a a 4 test.t2.a 3 +1 SIMPLE t1 ref a a 4 test.t2.a 1 explain select * from t1 where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL 4 Using where +1 SIMPLE t1 range a a 4 NULL 5 Using where explain select * from t1 force index (a) where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL 4 Using where +1 SIMPLE t1 range a a 4 NULL 5 Using where explain select * from t1 where c=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c,c_2 c 5 const 1 +1 SIMPLE t1 ref c,c_2 c 5 const 2 explain select * from t1 use index() where c=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where drop table t1,t2; create table t1 (a int not null auto_increment primary key, b varchar(255)); insert into t1 (b) values (repeat('a',100)),(repeat('b',100)),(repeat('c',100)); diff --git a/mysql-test/suite/maria/r/maria3.result b/mysql-test/suite/maria/r/maria3.result index fce5fa61034..21de234dad6 100644 --- a/mysql-test/suite/maria/r/maria3.result +++ b/mysql-test/suite/maria/r/maria3.result @@ -304,6 +304,7 @@ select lower(variable_name) as Variable_name, Variable_value as Value from infor Variable_name Value aria_block_size 8192 aria_checkpoint_interval 30 +aria_checkpoint_log_activity 1048576 aria_force_start_after_recovery_failures 0 aria_group_commit none aria_group_commit_interval 0 diff --git a/mysql-test/suite/maria/t/maria.test b/mysql-test/suite/maria/t/maria.test index 9d04e564e56..b371394b2b3 100644 --- a/mysql-test/suite/maria/t/maria.test +++ b/mysql-test/suite/maria/t/maria.test @@ -386,7 +386,8 @@ DROP TABLE t1; # CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a)); -INSERT into t1 values (0, null, 0), (0, null, 1), (0, null, 2), (0, null,3), (1,1,4); +INSERT into t1 values (0,null,0), (0,null,1), (0,null,2), (0,null,3), (1,1,4); +INSERT into t1 values (2,4,5), (7,8,4), (8,3,1), (9,7,2), (5,5,9); create table t2 (a int not null, b int, c int, key(b), key(c), key(a)); INSERT into t2 values (1,1,1), (2,2,2); optimize table t1; diff --git a/mysql-test/suite/optimizer_unfixed_bugs/r/bug45219.result b/mysql-test/suite/optimizer_unfixed_bugs/r/bug45219.result index e3cee8cd055..1335120b6b0 100644 --- a/mysql-test/suite/optimizer_unfixed_bugs/r/bug45219.result +++ b/mysql-test/suite/optimizer_unfixed_bugs/r/bug45219.result @@ -56,27 +56,6 @@ WHERE ( `int_nokey` , `pk` ) IN ( SELECT INNR .`pk` , INNR .`pk` FROM CC LEFT JOIN BB INNR ON INNR .`varchar_key` ) AND `pk` = 9 ; datetime_key -Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' -Warning 1292 Truncated incorrect DOUBLE value: 'i' DROP TABLE CC, C, BB; DROP TABLE IF EXISTS CC, C, BB; CREATE TABLE `CC` ( diff --git a/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result b/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result index 060955d84a4..657f1f57b2d 100644 --- a/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result +++ b/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result @@ -25,10 +25,10 @@ SELECT `int_key` FROM CC WHERE `date_nokey` < `datetime_nokey` XOR OUTR .`date_nokey` ) ; pk +9 2 5 6 -9 SELECT `pk` FROM C WHERE `pk` IN ( @@ -36,11 +36,11 @@ SELECT `int_key` FROM CC WHERE `date_nokey` < `datetime_nokey` XOR '2009-11-25' ) ; pk +9 2 -4 5 6 -9 +4 Warnings: Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' @@ -52,6 +52,12 @@ Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' +Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' +Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' +Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' +Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' +Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' +Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' DROP TABLE CC; DROP TABLE C; CREATE TABLE `CC` ( diff --git a/mysql-test/suite/parts/r/partition_repair_myisam.result b/mysql-test/suite/parts/r/partition_repair_myisam.result index 4b6d4dc6ec0..a9d7ce8aeb4 100644 --- a/mysql-test/suite/parts/r/partition_repair_myisam.result +++ b/mysql-test/suite/parts/r/partition_repair_myisam.result @@ -264,6 +264,7 @@ Table Op Msg_type Msg_text test.t1_will_crash analyze status OK OPTIMIZE TABLE t1_will_crash; Table Op Msg_type Msg_text +test.t1_will_crash optimize info Found row block followed by deleted block test.t1_will_crash optimize warning Number of rows changed from 8 to 7 test.t1_will_crash optimize status OK CHECK TABLE t1_will_crash; diff --git a/mysql-test/suite/pbxt/r/derived.result b/mysql-test/suite/pbxt/r/derived.result index 565a3bd1a58..9294b658595 100644 --- a/mysql-test/suite/pbxt/r/derived.result +++ b/mysql-test/suite/pbxt/r/derived.result @@ -190,13 +190,13 @@ pla_id test explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY <derived2> ALL NULL $hj 7 test.m2.matintnum 9 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 Using where 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY <derived2> ALL NULL $hj 7 test.m2.matintnum 9 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 Using where 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 drop table t1,t2; diff --git a/mysql-test/suite/pbxt/r/func_in.result b/mysql-test/suite/pbxt/r/func_in.result index b6b9e8be16e..167f75240a7 100644 --- a/mysql-test/suite/pbxt/r/func_in.result +++ b/mysql-test/suite/pbxt/r/func_in.result @@ -419,9 +419,15 @@ id select_type table type possible_keys key key_len ref rows Extra select f2 from t2 where f2 in ('a','b'); f2 0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' explain select f2 from t2 where f2 in ('a','b'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' select f2 from t2 where f2 in (1,'b'); f2 0 diff --git a/mysql-test/suite/pbxt/r/group_by.result b/mysql-test/suite/pbxt/r/group_by.result index c2f31b2165b..b33c9ac87c2 100644 --- a/mysql-test/suite/pbxt/r/group_by.result +++ b/mysql-test/suite/pbxt/r/group_by.result @@ -1171,8 +1171,8 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT 1 FROM t1 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL PRIMARY 4 NULL 144 Using where; Using index -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where +1 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t1) CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; @@ -1184,6 +1184,6 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT 1 FROM t2 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where +1 PRIMARY t2 index a a 5 NULL 4 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2) DROP TABLE t1, t2; diff --git a/mysql-test/suite/pbxt/r/join_nested.result b/mysql-test/suite/pbxt/r/join_nested.result index b4edf18f2f5..1a195cb13a8 100644 --- a/mysql-test/suite/pbxt/r/join_nested.result +++ b/mysql-test/suite/pbxt/r/join_nested.result @@ -1017,6 +1017,7 @@ id select_type table type possible_keys key key_len ref rows Extra ATTENTION: the above EXPLAIN has several competing QEPs with identical . costs. To combat the plan change it uses --sorted_result and . and --replace tricks +INSERT INTO t1 VALUES (-1,133,0), (-2,12,0), (-3,11,0), (-5,15,0); CREATE INDEX idx_b ON t1(b); CREATE INDEX idx_a ON t0(a); EXPLAIN @@ -1041,7 +1042,7 @@ ON t6.b >= 2 AND t5.b=t7.b ) ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND -(t1.a != 2), +(t1.a != 2) AND t1.a>0, t9 WHERE t0.a=1 AND t0.b=t1.b AND @@ -1089,7 +1090,7 @@ ON t6.b >= 2 AND t5.b=t7.b ) ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND -(t1.a != 2), +(t1.a != 2) AND t1.a>0, t9 WHERE t0.a=1 AND t0.b=t1.b AND diff --git a/mysql-test/suite/pbxt/r/key_diff.result b/mysql-test/suite/pbxt/r/key_diff.result index 33bedfcc39e..99bc7e7f8ad 100644 --- a/mysql-test/suite/pbxt/r/key_diff.result +++ b/mysql-test/suite/pbxt/r/key_diff.result @@ -6,7 +6,12 @@ KEY (a), KEY (b) ); INSERT INTO t1 VALUES ('A','B'),('b','A'),('C','c'),('D','E'),('a','a'); -select * from t1,t1 as t2; +INSERT INTO t1 VALUES +('AA','BB'),('bb','AA'),('CC','cc'),('DD','EE'),('aa','aa'); +INSERT INTO t1 VALUES +('AAA','BBB'),('bbb','AAA'),('CCC','ccc'),('DDD','EEE'),('aaa','aaa'); +select * from t1,t1 as t2 +where length(t1.A)=1 and length(t2.B)=1 ; a b a b A B A B b A A B @@ -33,11 +38,14 @@ b A a a C c a a D E a a a a a a -explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; +explain select t1.*,t2.* from t1,t1 as t2 +where t1.A=t2.B and length(t1.A)=1 and length(t2.B)=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL a NULL NULL NULL 5 +1 SIMPLE t1 ALL a NULL NULL NULL 15 Using where 1 SIMPLE t2 ref b b 4 test.t1.a 1 Using where -select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a; +select t1.*,t2.* from t1,t1 as t2 +where t1.A=t2.B and length(t1.A)=1 and length(t2.B)=1 +order by binary t1.a,t2.a; a b a b A B a a A B b A diff --git a/mysql-test/suite/pbxt/r/limit.result b/mysql-test/suite/pbxt/r/limit.result index 9bbf54fcfe9..dd53cb7deec 100644 --- a/mysql-test/suite/pbxt/r/limit.result +++ b/mysql-test/suite/pbxt/r/limit.result @@ -23,7 +23,7 @@ a b 2 2 3 2 4 4 -delete from t1 where b=2 limit 1; +delete from t1 where b=2 order by a limit 1; select * from t1 order by a; a b 0 0 diff --git a/mysql-test/suite/pbxt/r/mysqlshow.result b/mysql-test/suite/pbxt/r/mysqlshow.result index 96e45e84b77..a8bd5904600 100644 --- a/mysql-test/suite/pbxt/r/mysqlshow.result +++ b/mysql-test/suite/pbxt/r/mysqlshow.result @@ -132,11 +132,11 @@ Database: information_schema | INNODB_SYS_TABLES | | INNODB_SYS_FIELDS | | INNODB_SYS_COLUMNS | -| INNODB_TABLE_STATS | | INNODB_SYS_STATS | | INNODB_SYS_FOREIGN | | INNODB_SYS_INDEXES | | XTRADB_ADMIN_COMMAND | +| INNODB_TABLE_STATS | +---------------------------------------+ Database: INFORMATION_SCHEMA +---------------------------------------+ @@ -195,11 +195,11 @@ Database: INFORMATION_SCHEMA | INNODB_SYS_TABLES | | INNODB_SYS_FIELDS | | INNODB_SYS_COLUMNS | -| INNODB_TABLE_STATS | | INNODB_SYS_STATS | | INNODB_SYS_FOREIGN | | INNODB_SYS_INDEXES | | XTRADB_ADMIN_COMMAND | +| INNODB_TABLE_STATS | +---------------------------------------+ Wildcard: inf_rmation_schema +--------------------+ diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index f8f75a6f543..c012c2d1c29 100644 --- a/mysql-test/suite/pbxt/r/subselect.result +++ b/mysql-test/suite/pbxt/r/subselect.result @@ -50,7 +50,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select 1 AS `1` from dual having ((select 1) = 1) +Note 1003 select 1 AS `1` from dual having (<expr_cache><1>((select 1)) = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -224,7 +224,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4` +Note 1003 select `test`.`t4`.`b` AS `b`,<expr_cache><`test`.`t4`.`a`>((select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) from `test`.`t2`)) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4` select * from t3 where exists (select * from t2 where t2.b=t3.a); a 7 @@ -270,7 +270,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= `test`.`t3`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`)))) select * from t3 where a >= all (select b from t2); a 7 @@ -314,7 +314,7 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL # NULL Warnings: Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select (select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2` +Note 1003 select <expr_cache><`test`.`t2`.`a`>((select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`))) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2` select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; ERROR 21000: Subquery returns more than 1 row create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)); @@ -332,7 +332,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`)) +Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where <expr_cache><`test`.`t6`.`clinic_uq`>(exists(select 1 from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))) select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); ERROR 23000: Column 'a' in field list is ambiguous drop table t1,t2,t3; @@ -362,12 +362,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: -Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where ('joce' = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where ('joce' = (select 'joce' from `test`.`t8` where 1)) +Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -535,18 +535,24 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index +2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) +SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`) +Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1') EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index +1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -743,7 +749,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT 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 `test`.`t2`.`id` AS `id` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3)))) +Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where <expr_cache><`test`.`t2`.`id`>(<in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))) SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3); id SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); @@ -893,7 +899,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index 2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1` +Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`))))) AS `t1.a in (select t2.a from t2)` from `test`.`t1` CREATE TABLE t3 (a int(11) default '0'); INSERT INTO t3 VALUES (1),(2),(3); SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; @@ -908,7 +914,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` +Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); @@ -1295,31 +1301,31 @@ a 4 explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 3 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where -2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -1334,31 +1340,31 @@ a 4 explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using where; Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 3 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 ref a a 5 func 1 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t3 ref a a 5 test.t1.b 1 100.00 Using index +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index; Start temporary +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using index +1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a @@ -1371,10 +1377,10 @@ a 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using where; Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) drop table t1, t2, t3; create table t1 (a int, b int); create table t2 (a int, b int); @@ -1465,27 +1471,27 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; -create table t2 (a int, b int); +create table t2 (a int, b int not null); create table t3 (a int); insert into t3 values (6),(7),(3); select * from t3 where a >= all (select b from t2); @@ -1498,7 +1504,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(`test`.`t2`.`b`) from `test`.`t2`) > `test`.`t3`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(`test`.`t2`.`b`) from `test`.`t2`) > <cache>(`test`.`t3`.`a`)))) select * from t3 where a >= some (select b from t2); a explain extended select * from t3 where a >= some (select b from t2); @@ -1506,7 +1512,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= `test`.`t3`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`)))) select * from t3 where a >= all (select b from t2 group by 1); a 6 @@ -1517,7 +1523,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select `test`.`t2`.`b` from `test`.`t2` group by 1) > `test`.`t3`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select `test`.`t2`.`b` from `test`.`t2` group by 1) > <cache>(`test`.`t3`.`a`)))) select * from t3 where a >= some (select b from t2 group by 1); a explain extended select * from t3 where a >= some (select b from t2 group by 1); @@ -1525,7 +1531,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(<min>(select `test`.`t2`.`b` from `test`.`t2` group by 1) <= `test`.`t3`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(<min>(select `test`.`t2`.`b` from `test`.`t2` group by 1) <= <cache>(`test`.`t3`.`a`)))) select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); @@ -1568,7 +1574,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= `test`.`t3`.`a`))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select max(`test`.`t2`.`b`) from `test`.`t2` group by `test`.`t2`.`a`) >= <cache>(`test`.`t3`.`a`)))) drop table t2, t3; CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now()); @@ -1738,14 +1744,14 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where 2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where Warnings: -Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`))))))) +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`id`>(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))) explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index Warnings: Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))) +Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(<expr_cache><`test`.`tt`.`id`>(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))) insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001'); create table t2 (id int not null, text varchar(20) not null default '', primary key (id)); insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10'); @@ -2282,7 +2288,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`)) +Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where <expr_cache><`test`.`up`.`a`>(exists(select 1 from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))) drop table t1; CREATE TABLE t1 (t1_a int); INSERT INTO t1 VALUES (1); @@ -2825,19 +2831,19 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; FirstMatch(t1) Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = 'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N')) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; set @@optimizer_switch=@save_optimizer_switch; CREATE TABLE t1 (a char(5), b char(5)); @@ -4220,8 +4226,8 @@ CREATE INDEX I1 ON t1 (a); CREATE INDEX I2 ON t1 (b); EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where +1 PRIMARY t1 ref I1 I1 2 test.t1.b 1 Using where; Using index; FirstMatch(t1) SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); a b CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); @@ -4230,15 +4236,15 @@ CREATE INDEX I1 ON t2 (a); CREATE INDEX I2 ON t2 (b); EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 1 Using index; Using where +1 PRIMARY t2 ALL I2 NULL NULL NULL 2 Using where +1 PRIMARY t2 ref I1 I1 4 test.t2.b 1 Using where; Using index; FirstMatch(t2) SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); a b EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where +1 PRIMARY t1 ref I1 I1 2 test.t1.b 1 Using where; Using index; FirstMatch(t1) SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); a b DROP TABLE t1,t2; @@ -4282,7 +4288,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select 2 AS `2` from `test`.`t1` where exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) +Note 1003 select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))) EXPLAIN EXTENDED SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a)); @@ -4294,7 +4300,7 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select 2 AS `2` from `test`.`t1` where exists((select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))) +Note 1003 select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists((select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) union (select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)))) DROP TABLE t1,t2; create table t1(f11 int, f12 int); create table t2(f21 int unsigned not null, f22 int, f23 varchar(10)); diff --git a/mysql-test/suite/pbxt/t/join_nested.test b/mysql-test/suite/pbxt/t/join_nested.test index 3b72dc1e293..98ffcbc9a8a 100644 --- a/mysql-test/suite/pbxt/t/join_nested.test +++ b/mysql-test/suite/pbxt/t/join_nested.test @@ -587,6 +587,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, --echo . costs. To combat the plan change it uses --sorted_result and --echo . and --replace tricks +INSERT INTO t1 VALUES (-1,133,0), (-2,12,0), (-3,11,0), (-5,15,0); CREATE INDEX idx_b ON t1(b); CREATE INDEX idx_a ON t0(a); @@ -614,7 +615,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, ) ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND - (t1.a != 2), + (t1.a != 2) AND t1.a>0, t9 WHERE t0.a=1 AND t0.b=t1.b AND @@ -653,7 +654,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, ) ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND - (t1.a != 2), + (t1.a != 2) AND t1.a>0, t9 WHERE t0.a=1 AND t0.b=t1.b AND diff --git a/mysql-test/suite/pbxt/t/key_diff.test b/mysql-test/suite/pbxt/t/key_diff.test index 5e9d7bac9cc..fc93c7418ab 100644 --- a/mysql-test/suite/pbxt/t/key_diff.test +++ b/mysql-test/suite/pbxt/t/key_diff.test @@ -13,11 +13,19 @@ CREATE TABLE t1 ( ); INSERT INTO t1 VALUES ('A','B'),('b','A'),('C','c'),('D','E'),('a','a'); +INSERT INTO t1 VALUES + ('AA','BB'),('bb','AA'),('CC','cc'),('DD','EE'),('aa','aa'); +INSERT INTO t1 VALUES + ('AAA','BBB'),('bbb','AAA'),('CCC','ccc'),('DDD','EEE'),('aaa','aaa'); -select * from t1,t1 as t2; -explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; +select * from t1,t1 as t2 + where length(t1.A)=1 and length(t2.B)=1 ; +explain select t1.*,t2.* from t1,t1 as t2 + where t1.A=t2.B and length(t1.A)=1 and length(t2.B)=1; #select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; -select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a; +select t1.*,t2.* from t1,t1 as t2 + where t1.A=t2.B and length(t1.A)=1 and length(t2.B)=1 +order by binary t1.a,t2.a; select * from t1 where a='a'; drop table t1; diff --git a/mysql-test/suite/pbxt/t/limit.test b/mysql-test/suite/pbxt/t/limit.test index 0844af8705d..a65d5060e4d 100644 --- a/mysql-test/suite/pbxt/t/limit.test +++ b/mysql-test/suite/pbxt/t/limit.test @@ -15,7 +15,7 @@ update t1 set b=2 where b=1 limit 2; select * from t1 order by a; # PBXT: required for consistent result update t1 set b=4 where b=1; select * from t1 order by a; # PBXT: required for consistent result -delete from t1 where b=2 limit 1; +delete from t1 where b=2 order by a limit 1; select * from t1 order by a; # PBXT: required for consistent result delete from t1 limit 1; select * from t1 order by a; # PBXT: required for consistent result diff --git a/mysql-test/suite/pbxt/t/mysqlshow.test b/mysql-test/suite/pbxt/t/mysqlshow.test index 41505cc0b10..b54eeaeab34 100644 --- a/mysql-test/suite/pbxt/t/mysqlshow.test +++ b/mysql-test/suite/pbxt/t/mysqlshow.test @@ -30,7 +30,9 @@ DROP TABLE t1, t2; # # Bug #19147: mysqlshow INFORMATION_SCHEMA does not work # +--sorted_result --exec $MYSQL_SHOW information_schema +--sorted_result --exec $MYSQL_SHOW INFORMATION_SCHEMA --exec $MYSQL_SHOW inf_rmation_schema diff --git a/mysql-test/suite/pbxt/t/subselect.test b/mysql-test/suite/pbxt/t/subselect.test index 891e23d923e..651f47fcee1 100644 --- a/mysql-test/suite/pbxt/t/subselect.test +++ b/mysql-test/suite/pbxt/t/subselect.test @@ -278,8 +278,9 @@ SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a; SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); --- error 1242 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +-- error 1242 +SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); drop table t1; @@ -928,7 +929,7 @@ drop table t1,t2; # # correct ALL optimisation # -create table t2 (a int, b int); +create table t2 (a int, b int not null); create table t3 (a int); insert into t3 values (6),(7),(3); select * from t3 where a >= all (select b from t2); diff --git a/mysql-test/suite/perfschema/r/pfs_upgrade.result b/mysql-test/suite/perfschema/r/pfs_upgrade.result index b2e415637d9..a7c47d8d64a 100644 --- a/mysql-test/suite/perfschema/r/pfs_upgrade.result +++ b/mysql-test/suite/perfschema/r/pfs_upgrade.result @@ -25,7 +25,7 @@ ERROR 1050 (42S01) at line 427: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 444: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 460: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 477: Table 'threads' already exists -ERROR 1644 (HY000) at line 1122: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1125: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed show tables like "user_table"; Tables_in_performance_schema (user_table) @@ -55,7 +55,7 @@ ERROR 1050 (42S01) at line 427: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 444: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 460: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 477: Table 'threads' already exists -ERROR 1644 (HY000) at line 1122: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1125: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed show tables like "user_view"; Tables_in_performance_schema (user_view) @@ -83,7 +83,7 @@ ERROR 1050 (42S01) at line 427: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 444: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 460: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 477: Table 'threads' already exists -ERROR 1644 (HY000) at line 1122: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1125: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed select name from mysql.proc where db='performance_schema'; name @@ -111,7 +111,7 @@ ERROR 1050 (42S01) at line 427: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 444: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 460: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 477: Table 'threads' already exists -ERROR 1644 (HY000) at line 1122: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1125: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed select name from mysql.proc where db='performance_schema'; name @@ -139,7 +139,7 @@ ERROR 1050 (42S01) at line 427: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 444: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 460: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 477: Table 'threads' already exists -ERROR 1644 (HY000) at line 1122: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1125: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed select name from mysql.event where db='performance_schema'; name diff --git a/mysql-test/suite/rpl/r/rpl_row_annotate_do.result b/mysql-test/suite/rpl/r/rpl_row_annotate_do.result index 0ece93e7aa5..a7dc2a569a1 100644 --- a/mysql-test/suite/rpl/r/rpl_row_annotate_do.result +++ b/mysql-test/suite/rpl/r/rpl_row_annotate_do.result @@ -127,7 +127,7 @@ slave-bin.000001 # Rotate 2 # slave-bin.000002;pos=4 ######################################################################## # INSERTs DELAYED ON MASTERs ######################################################################## -SET SESSION binlog_annotate_rows_events = ON; +SET SESSION binlog_annotate_row_events = ON; INSERT DELAYED INTO test1.t4 VALUES (1,1); FLUSH TABLES; SELECT * FROM test1.t4 ORDER BY a; diff --git a/mysql-test/suite/rpl/r/rpl_row_annotate_dont.result b/mysql-test/suite/rpl/r/rpl_row_annotate_dont.result index 8463256d5db..2a3b5b1870e 100644 --- a/mysql-test/suite/rpl/r/rpl_row_annotate_dont.result +++ b/mysql-test/suite/rpl/r/rpl_row_annotate_dont.result @@ -109,7 +109,7 @@ slave-bin.000001 # Rotate 2 # slave-bin.000002;pos=4 ######################################################################## # INSERTs DELAYED ON MASTERs ######################################################################## -SET SESSION binlog_annotate_rows_events = ON; +SET SESSION binlog_annotate_row_events = ON; INSERT DELAYED INTO test1.t4 VALUES (1,1); FLUSH TABLES; SELECT * FROM test1.t4 ORDER BY a; diff --git a/mysql-test/suite/rpl/r/rpl_stm_000001.result b/mysql-test/suite/rpl/r/rpl_stm_000001.result index eb421d1adc4..80a3240021a 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_000001.result +++ b/mysql-test/suite/rpl/r/rpl_stm_000001.result @@ -49,7 +49,7 @@ select (@id := id) - id from t2; kill @id; drop table t2; Got one of the listed errors -include/wait_for_slave_sql_error_and_skip.inc [errno=1053] +include/wait_for_slave_sql_error_and_skip.inc [errno=1927] select count(*) from t1; count(*) 5000 diff --git a/mysql-test/suite/rpl/t/rpl_row_annotate_do-slave.opt b/mysql-test/suite/rpl/t/rpl_row_annotate_do-slave.opt index aa3af621897..18de9bd1e33 100644 --- a/mysql-test/suite/rpl/t/rpl_row_annotate_do-slave.opt +++ b/mysql-test/suite/rpl/t/rpl_row_annotate_do-slave.opt @@ -1 +1 @@ ---log-slave-updates --replicate-annotate-rows-events --replicate-ignore-table=test1.xt1 --replicate-ignore-table=test1.xt2
\ No newline at end of file +--log-slave-updates --replicate-annotate-row-events --replicate-ignore-table=test1.xt1 --replicate-ignore-table=test1.xt2 diff --git a/mysql-test/suite/rpl/t/rpl_row_annotate_do.test b/mysql-test/suite/rpl/t/rpl_row_annotate_do.test index b61ce0ab6d8..4114f90b90b 100644 --- a/mysql-test/suite/rpl/t/rpl_row_annotate_do.test +++ b/mysql-test/suite/rpl/t/rpl_row_annotate_do.test @@ -1,7 +1,7 @@ ############################################################################### # WL47: Store in binlog text of statements that caused RBR events # Wrapper for extra/rpl/rpl_row_annotate.test. -# Intended to test that if the --replicate-annotate-rows-events option +# Intended to test that if the --replicate-annotate-row-events option # is switched on on slave then Annotate_events: # - are reproduced on slave # - are reproduced only once for "multi-table-maps" rbr queries diff --git a/mysql-test/suite/rpl/t/rpl_row_annotate_dont.test b/mysql-test/suite/rpl/t/rpl_row_annotate_dont.test index 56765c591aa..ef746e76b4d 100644 --- a/mysql-test/suite/rpl/t/rpl_row_annotate_dont.test +++ b/mysql-test/suite/rpl/t/rpl_row_annotate_dont.test @@ -1,7 +1,7 @@ ############################################################################### # WL47: Store in binlog text of statements that caused RBR events # Wrapper for extra/rpl/rpl_row_annotate.test. -# Intended to test that if the --replicate-annotate-rows-events option +# Intended to test that if the --replicate-annotate-row-events option # is switched off on slave then Annotate_events are not reproduced. ############################################################################### diff --git a/mysql-test/suite/rpl/t/rpl_stm_000001.test b/mysql-test/suite/rpl/t/rpl_stm_000001.test index 268a10ad1fa..16f89123d75 100644 --- a/mysql-test/suite/rpl/t/rpl_stm_000001.test +++ b/mysql-test/suite/rpl/t/rpl_stm_000001.test @@ -96,14 +96,15 @@ drop table t2; connection master; # The get_lock function causes warning for unsafe statement. --disable_warnings ---error 1317,2013 +# 2013 = CR_SERVER_LOST +--error ER_QUERY_INTERRUPTED,ER_CONNECTION_KILLED,2013 reap; --enable_warnings connection slave; # The SQL slave thread should now have stopped because the query was killed on # the master (so it has a non-zero error code in the binlog). -# 1053 = ER_SERVER_SHUTDOWN ---let $slave_sql_errno= 1053 +# 1927 = ER_CONNECTION_KILLED +--let $slave_sql_errno= 1927 --source include/wait_for_slave_sql_error_and_skip.inc select count(*) from t1; diff --git a/mysql-test/suite/sys_vars/r/aria_checkpoint_log_activity_basic.result b/mysql-test/suite/sys_vars/r/aria_checkpoint_log_activity_basic.result new file mode 100644 index 00000000000..8a0247471ff --- /dev/null +++ b/mysql-test/suite/sys_vars/r/aria_checkpoint_log_activity_basic.result @@ -0,0 +1,43 @@ +SET @start_global_value = @@global.aria_checkpoint_log_activity; +select @@global.aria_checkpoint_log_activity; +@@global.aria_checkpoint_log_activity +1048576 +select @@session.aria_checkpoint_log_activity; +ERROR HY000: Variable 'aria_checkpoint_log_activity' is a GLOBAL variable +show global variables like 'aria_checkpoint_log_activity'; +Variable_name Value +aria_checkpoint_log_activity 1048576 +show session variables like 'aria_checkpoint_log_activity'; +Variable_name Value +aria_checkpoint_log_activity 1048576 +select * from information_schema.global_variables where variable_name='aria_checkpoint_log_activity'; +VARIABLE_NAME VARIABLE_VALUE +ARIA_CHECKPOINT_LOG_ACTIVITY 1048576 +select * from information_schema.session_variables where variable_name='aria_checkpoint_log_activity'; +VARIABLE_NAME VARIABLE_VALUE +ARIA_CHECKPOINT_LOG_ACTIVITY 1048576 +set global aria_checkpoint_log_activity=1; +select @@global.aria_checkpoint_log_activity; +@@global.aria_checkpoint_log_activity +1 +set session aria_checkpoint_log_activity=1; +ERROR HY000: Variable 'aria_checkpoint_log_activity' is a GLOBAL variable and should be set with SET GLOBAL +set global aria_checkpoint_log_activity=1.1; +ERROR 42000: Incorrect argument type to variable 'aria_checkpoint_log_activity' +set global aria_checkpoint_log_activity=1e1; +ERROR 42000: Incorrect argument type to variable 'aria_checkpoint_log_activity' +set global aria_checkpoint_log_activity="foo"; +ERROR 42000: Incorrect argument type to variable 'aria_checkpoint_log_activity' +set global aria_checkpoint_log_activity=0; +select @@global.aria_checkpoint_log_activity; +@@global.aria_checkpoint_log_activity +0 +set global aria_checkpoint_log_activity=cast(-1 as unsigned int); +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement +Warning 1105 Cast to unsigned converted negative integer to it's positive complement +Warning 1292 Truncated incorrect aria_checkpoint_log_activity value: '18446744073709551615' +select @@global.aria_checkpoint_log_activity; +@@global.aria_checkpoint_log_activity +4294967295 +SET @@global.aria_checkpoint_log_activity = @start_global_value; diff --git a/mysql-test/suite/sys_vars/r/binlog_annotate_row_events_basic.result b/mysql-test/suite/sys_vars/r/binlog_annotate_row_events_basic.result new file mode 100644 index 00000000000..6fef4fc1d32 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/binlog_annotate_row_events_basic.result @@ -0,0 +1,50 @@ +SET @start_global_value = @@global.binlog_annotate_row_events; +select @@global.binlog_annotate_row_events; +@@global.binlog_annotate_row_events +0 +select @@session.binlog_annotate_row_events; +@@session.binlog_annotate_row_events +0 +show global variables like 'binlog_annotate_row_events'; +Variable_name Value +binlog_annotate_row_events OFF +show session variables like 'binlog_annotate_row_events'; +Variable_name Value +binlog_annotate_row_events OFF +select * from information_schema.global_variables where variable_name='binlog_annotate_row_events'; +VARIABLE_NAME VARIABLE_VALUE +BINLOG_ANNOTATE_ROW_EVENTS OFF +select * from information_schema.session_variables where variable_name='binlog_annotate_row_events'; +VARIABLE_NAME VARIABLE_VALUE +BINLOG_ANNOTATE_ROW_EVENTS OFF +set global binlog_annotate_row_events=ON; +select @@global.binlog_annotate_row_events; +@@global.binlog_annotate_row_events +1 +set global binlog_annotate_row_events=OFF; +select @@global.binlog_annotate_row_events; +@@global.binlog_annotate_row_events +0 +set global binlog_annotate_row_events=1; +select @@global.binlog_annotate_row_events; +@@global.binlog_annotate_row_events +1 +set session binlog_annotate_row_events=ON; +select @@session.binlog_annotate_row_events; +@@session.binlog_annotate_row_events +1 +set session binlog_annotate_row_events=OFF; +select @@session.binlog_annotate_row_events; +@@session.binlog_annotate_row_events +0 +set session binlog_annotate_row_events=1; +select @@session.binlog_annotate_row_events; +@@session.binlog_annotate_row_events +1 +set global binlog_annotate_row_events=1.1; +ERROR 42000: Incorrect argument type to variable 'binlog_annotate_row_events' +set session binlog_annotate_row_events=1e1; +ERROR 42000: Incorrect argument type to variable 'binlog_annotate_row_events' +set session binlog_annotate_row_events="foo"; +ERROR 42000: Variable 'binlog_annotate_row_events' can't be set to the value of 'foo' +SET @@global.binlog_annotate_row_events = @start_global_value; diff --git a/mysql-test/suite/sys_vars/r/binlog_annotate_rows_events_basic.result b/mysql-test/suite/sys_vars/r/binlog_annotate_rows_events_basic.result deleted file mode 100644 index af6b27972e5..00000000000 --- a/mysql-test/suite/sys_vars/r/binlog_annotate_rows_events_basic.result +++ /dev/null @@ -1,50 +0,0 @@ -SET @start_global_value = @@global.binlog_annotate_rows_events; -select @@global.binlog_annotate_rows_events; -@@global.binlog_annotate_rows_events -0 -select @@session.binlog_annotate_rows_events; -@@session.binlog_annotate_rows_events -0 -show global variables like 'binlog_annotate_rows_events'; -Variable_name Value -binlog_annotate_rows_events OFF -show session variables like 'binlog_annotate_rows_events'; -Variable_name Value -binlog_annotate_rows_events OFF -select * from information_schema.global_variables where variable_name='binlog_annotate_rows_events'; -VARIABLE_NAME VARIABLE_VALUE -BINLOG_ANNOTATE_ROWS_EVENTS OFF -select * from information_schema.session_variables where variable_name='binlog_annotate_rows_events'; -VARIABLE_NAME VARIABLE_VALUE -BINLOG_ANNOTATE_ROWS_EVENTS OFF -set global binlog_annotate_rows_events=ON; -select @@global.binlog_annotate_rows_events; -@@global.binlog_annotate_rows_events -1 -set global binlog_annotate_rows_events=OFF; -select @@global.binlog_annotate_rows_events; -@@global.binlog_annotate_rows_events -0 -set global binlog_annotate_rows_events=1; -select @@global.binlog_annotate_rows_events; -@@global.binlog_annotate_rows_events -1 -set session binlog_annotate_rows_events=ON; -select @@session.binlog_annotate_rows_events; -@@session.binlog_annotate_rows_events -1 -set session binlog_annotate_rows_events=OFF; -select @@session.binlog_annotate_rows_events; -@@session.binlog_annotate_rows_events -0 -set session binlog_annotate_rows_events=1; -select @@session.binlog_annotate_rows_events; -@@session.binlog_annotate_rows_events -1 -set global binlog_annotate_rows_events=1.1; -ERROR 42000: Incorrect argument type to variable 'binlog_annotate_rows_events' -set session binlog_annotate_rows_events=1e1; -ERROR 42000: Incorrect argument type to variable 'binlog_annotate_rows_events' -set session binlog_annotate_rows_events="foo"; -ERROR 42000: Variable 'binlog_annotate_rows_events' can't be set to the value of 'foo' -SET @@global.binlog_annotate_rows_events = @start_global_value; diff --git a/mysql-test/suite/sys_vars/r/debug_binlog_fsync_sleep_basic.result b/mysql-test/suite/sys_vars/r/debug_binlog_fsync_sleep_basic.result new file mode 100644 index 00000000000..adf2b4c39b9 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/debug_binlog_fsync_sleep_basic.result @@ -0,0 +1,41 @@ +SET @start_global_value = @@global.debug_binlog_fsync_sleep; +select @@global.debug_binlog_fsync_sleep; +@@global.debug_binlog_fsync_sleep +0 +select @@session.debug_binlog_fsync_sleep; +ERROR HY000: Variable 'debug_binlog_fsync_sleep' is a GLOBAL variable +show global variables like 'debug_binlog_fsync_sleep'; +Variable_name Value +debug_binlog_fsync_sleep 0 +show session variables like 'debug_binlog_fsync_sleep'; +Variable_name Value +debug_binlog_fsync_sleep 0 +select * from information_schema.global_variables where variable_name='debug_binlog_fsync_sleep'; +VARIABLE_NAME VARIABLE_VALUE +DEBUG_BINLOG_FSYNC_SLEEP 0 +select * from information_schema.session_variables where variable_name='debug_binlog_fsync_sleep'; +VARIABLE_NAME VARIABLE_VALUE +DEBUG_BINLOG_FSYNC_SLEEP 0 +set global debug_binlog_fsync_sleep=20; +select @@global.debug_binlog_fsync_sleep; +@@global.debug_binlog_fsync_sleep +20 +set session debug_binlog_fsync_sleep=1; +ERROR HY000: Variable 'debug_binlog_fsync_sleep' is a GLOBAL variable and should be set with SET GLOBAL +set global debug_binlog_fsync_sleep=1.1; +ERROR 42000: Incorrect argument type to variable 'debug_binlog_fsync_sleep' +set global debug_binlog_fsync_sleep=1e1; +ERROR 42000: Incorrect argument type to variable 'debug_binlog_fsync_sleep' +set global debug_binlog_fsync_sleep="foo"; +ERROR 42000: Incorrect argument type to variable 'debug_binlog_fsync_sleep' +set global debug_binlog_fsync_sleep=0; +select @@global.debug_binlog_fsync_sleep; +@@global.debug_binlog_fsync_sleep +0 +set global debug_binlog_fsync_sleep=cast(-1 as unsigned int); +Warnings: +Warning 1105 Cast to unsigned converted negative integer to it's positive complement +select @@global.debug_binlog_fsync_sleep; +@@global.debug_binlog_fsync_sleep +18446744073709551615 +SET @@global.debug_binlog_fsync_sleep = @start_global_value; diff --git a/mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result b/mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result index 36dc9b6399e..0922113f8b6 100644 --- a/mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result +++ b/mysql-test/suite/sys_vars/r/engine_condition_pushdown_basic.result @@ -9,7 +9,7 @@ SELECT @global_start_value; select @old_session_opt_switch:=@@session.optimizer_switch, @old_global_opt_switch:=@@global.optimizer_switch; @old_session_opt_switch:=@@session.optimizer_switch @old_global_opt_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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on '#--------------------FN_DYNVARS_028_01------------------------#' SET @@session.engine_condition_pushdown = 0; Warnings: @@ -212,7 +212,7 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 0 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +0 0 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on set @@session.engine_condition_pushdown = TRUE; Warnings: Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead @@ -220,7 +220,7 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -1 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,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=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +1 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on set @@session.engine_condition_pushdown = FALSE; Warnings: Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead @@ -228,7 +228,7 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 0 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +0 0 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on set @@global.engine_condition_pushdown = TRUE; Warnings: Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead @@ -236,7 +236,7 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 1 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,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=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +0 1 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on set @@global.engine_condition_pushdown = FALSE; Warnings: Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead @@ -244,31 +244,31 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 0 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +0 0 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on set @@session.optimizer_switch = "engine_condition_pushdown=on"; select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -1 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,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=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +1 0 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on set @@session.optimizer_switch = "engine_condition_pushdown=off"; select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 0 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +0 0 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on set @@global.optimizer_switch = "engine_condition_pushdown=on"; select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 1 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,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=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +0 1 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on set @@global.optimizer_switch = "engine_condition_pushdown=off"; select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 0 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +0 0 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on SET @@session.engine_condition_pushdown = @session_start_value; Warnings: Warning 1287 The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MySQL 7.0. Please use '@@optimizer_switch' instead @@ -287,4 +287,4 @@ select @@session.engine_condition_pushdown, @@global.engine_condition_pushdown, @@session.optimizer_switch, @@global.optimizer_switch; @@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch -0 0 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +0 0 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on diff --git a/mysql-test/suite/sys_vars/r/max_user_connections-2.result b/mysql-test/suite/sys_vars/r/max_user_connections-2.result new file mode 100644 index 00000000000..d9daec5c089 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/max_user_connections-2.result @@ -0,0 +1,2 @@ +set global max_user_connections=100; +ERROR HY000: The MySQL server is running with the --max-user-connections=0 option so it cannot execute this statement diff --git a/mysql-test/suite/sys_vars/r/max_user_connections_basic.result b/mysql-test/suite/sys_vars/r/max_user_connections_basic.result index cc43b631085..26d82f3b215 100644 --- a/mysql-test/suite/sys_vars/r/max_user_connections_basic.result +++ b/mysql-test/suite/sys_vars/r/max_user_connections_basic.result @@ -1,11 +1,11 @@ SET @start_global_value = @@global.max_user_connections; SELECT @start_global_value; @start_global_value -0 +10 SET @start_session_value = @@session.max_user_connections; SELECT @start_session_value; @start_session_value -0 +10 '#--------------------FN_DYNVARS_087_01-------------------------#' SET @@global.max_user_connections = 1000; SET @@global.max_user_connections = DEFAULT; @@ -37,14 +37,14 @@ SET @@global.max_user_connections = 65536; SELECT @@global.max_user_connections; @@global.max_user_connections 65536 -SET @@global.max_user_connections = 4294967295; +SET @@global.max_user_connections = 2147483647; SELECT @@global.max_user_connections; @@global.max_user_connections -4294967295 -SET @@global.max_user_connections = 4294967294; +2147483647 +SET @@global.max_user_connections = 2147483646; SELECT @@global.max_user_connections; @@global.max_user_connections -4294967294 +2147483646 '#------------------FN_DYNVARS_087_05-----------------------#' SET @@global.max_user_connections = -1024; Warnings: @@ -57,7 +57,7 @@ Warnings: Warning 1292 Truncated incorrect max_user_connections value: '4294967296' SELECT @@global.max_user_connections; @@global.max_user_connections -4294967295 +2147483647 SET @@global.max_user_connections = -1; Warnings: Warning 1292 Truncated incorrect max_user_connections value: '-1' @@ -69,17 +69,17 @@ Warnings: Warning 1292 Truncated incorrect max_user_connections value: '429496729500' SELECT @@global.max_user_connections; @@global.max_user_connections -4294967295 +2147483647 SET @@global.max_user_connections = 65530.34; ERROR 42000: Incorrect argument type to variable 'max_user_connections' SELECT @@global.max_user_connections; @@global.max_user_connections -4294967295 +2147483647 SET @@global.max_user_connections = test; ERROR 42000: Incorrect argument type to variable 'max_user_connections' SELECT @@global.max_user_connections; @@global.max_user_connections -4294967295 +2147483647 '#------------------FN_DYNVARS_087_06-----------------------#' SELECT @@global.max_user_connections = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES @@ -114,4 +114,4 @@ ERROR 42S22: Unknown column 'max_user_connections' in 'field list' SET @@global.max_user_connections = @start_global_value; SELECT @@global.max_user_connections; @@global.max_user_connections -0 +10 diff --git a/mysql-test/suite/sys_vars/r/max_user_connections_func.result b/mysql-test/suite/sys_vars/r/max_user_connections_func.result index 2ff2ac82ce7..04999a0a43f 100644 --- a/mysql-test/suite/sys_vars/r/max_user_connections_func.result +++ b/mysql-test/suite/sys_vars/r/max_user_connections_func.result @@ -2,18 +2,20 @@ SET @default_max_user_connections = @@global.max_user_connections; Set Global max_user_connections=2; +GRANT USAGE on *.* TO test@localhost; '#--------------------FN_DYNVARS_114_01-------------------------#' -** Connecting conn1 using username 'root' ** -** Connecting conn2 using username 'root' ** -** Connecting conn3 using username 'root' ** -ERROR 42000: User root already has more than 'max_user_connections' active connections +** Connecting conn1 using username 'test' ** +** Connecting conn2 using username 'test' ** +** Connecting conn3 using username 'test' ** +ERROR 42000: User test already has more than 'max_user_connections' active connections Expected error "too many connections" ** Disconnecting conn1 ** ** Poll till disconnected conn1 disappears from processlist '#--------------------FN_DYNVARS_114_02-------------------------#' Set Global max_user_connections=3; -** Connecting conn5 using username 'root' ** -** Connecting conn6 using username 'root' ** +** Connecting conn5 using username 'test' ** +** Connecting conn6 using username 'test' ** ** Connection default ** ** Disconnecting conn5, conn6 ** +drop user test@localhost; SET @@global.max_user_connections = @default_max_user_connections; 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 a56bcc77886..1fa39374f9c 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result @@ -1,25 +1,25 @@ SET @start_global_value = @@global.optimizer_switch; SELECT @start_global_value; @start_global_value -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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on set global optimizer_switch=10; set session optimizer_switch=5; select @@global.optimizer_switch; @@ -63,4 +63,4 @@ ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'foobar' SET @@global.optimizer_switch = @start_global_value; 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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,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=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown1.result b/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown1.result new file mode 100644 index 00000000000..ad618c1727f --- /dev/null +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown1.result @@ -0,0 +1,5 @@ +select @@session.engine_condition_pushdown, +@@global.engine_condition_pushdown, +@@session.optimizer_switch, @@global.optimizer_switch; +@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch +1 1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown2.result b/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown2.result new file mode 100644 index 00000000000..daed5b2919c --- /dev/null +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_eng_cond_pushdown2.result @@ -0,0 +1,5 @@ +select @@session.engine_condition_pushdown, +@@global.engine_condition_pushdown, +@@session.optimizer_switch, @@global.optimizer_switch; +@@session.engine_condition_pushdown @@global.engine_condition_pushdown @@session.optimizer_switch @@global.optimizer_switch +0 0 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on 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=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on diff --git a/mysql-test/suite/sys_vars/r/replicate_annotate_row_events_basic.result b/mysql-test/suite/sys_vars/r/replicate_annotate_row_events_basic.result new file mode 100644 index 00000000000..802c92268d6 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/replicate_annotate_row_events_basic.result @@ -0,0 +1,21 @@ +select @@global.replicate_annotate_row_events; +@@global.replicate_annotate_row_events +0 +select @@session.replicate_annotate_row_events; +ERROR HY000: Variable 'replicate_annotate_row_events' is a GLOBAL variable +show global variables like 'replicate_annotate_row_events'; +Variable_name Value +replicate_annotate_row_events OFF +show session variables like 'replicate_annotate_row_events'; +Variable_name Value +replicate_annotate_row_events OFF +select * from information_schema.global_variables where variable_name='replicate_annotate_row_events'; +VARIABLE_NAME VARIABLE_VALUE +REPLICATE_ANNOTATE_ROW_EVENTS OFF +select * from information_schema.session_variables where variable_name='replicate_annotate_row_events'; +VARIABLE_NAME VARIABLE_VALUE +REPLICATE_ANNOTATE_ROW_EVENTS OFF +set global replicate_annotate_row_events=1; +ERROR HY000: Variable 'replicate_annotate_row_events' is a read only variable +set session replicate_annotate_row_events=1; +ERROR HY000: Variable 'replicate_annotate_row_events' is a read only variable diff --git a/mysql-test/suite/sys_vars/r/replicate_annotate_rows_events_basic.result b/mysql-test/suite/sys_vars/r/replicate_annotate_rows_events_basic.result deleted file mode 100644 index ef0d29fad07..00000000000 --- a/mysql-test/suite/sys_vars/r/replicate_annotate_rows_events_basic.result +++ /dev/null @@ -1,21 +0,0 @@ -select @@global.replicate_annotate_rows_events; -@@global.replicate_annotate_rows_events -0 -select @@session.replicate_annotate_rows_events; -ERROR HY000: Variable 'replicate_annotate_rows_events' is a GLOBAL variable -show global variables like 'replicate_annotate_rows_events'; -Variable_name Value -replicate_annotate_rows_events OFF -show session variables like 'replicate_annotate_rows_events'; -Variable_name Value -replicate_annotate_rows_events OFF -select * from information_schema.global_variables where variable_name='replicate_annotate_rows_events'; -VARIABLE_NAME VARIABLE_VALUE -REPLICATE_ANNOTATE_ROWS_EVENTS OFF -select * from information_schema.session_variables where variable_name='replicate_annotate_rows_events'; -VARIABLE_NAME VARIABLE_VALUE -REPLICATE_ANNOTATE_ROWS_EVENTS OFF -set global replicate_annotate_rows_events=1; -ERROR HY000: Variable 'replicate_annotate_rows_events' is a read only variable -set session replicate_annotate_rows_events=1; -ERROR HY000: Variable 'replicate_annotate_rows_events' is a read only variable diff --git a/mysql-test/suite/sys_vars/t/aria_checkpoint_log_activity_basic.test b/mysql-test/suite/sys_vars/t/aria_checkpoint_log_activity_basic.test new file mode 100644 index 00000000000..9fa5e5e2e2f --- /dev/null +++ b/mysql-test/suite/sys_vars/t/aria_checkpoint_log_activity_basic.test @@ -0,0 +1,43 @@ +# ulong global +--source include/have_maria.inc + +SET @start_global_value = @@global.aria_checkpoint_log_activity; + +# +# exists as global only +# +select @@global.aria_checkpoint_log_activity; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.aria_checkpoint_log_activity; +show global variables like 'aria_checkpoint_log_activity'; +show session variables like 'aria_checkpoint_log_activity'; +select * from information_schema.global_variables where variable_name='aria_checkpoint_log_activity'; +select * from information_schema.session_variables where variable_name='aria_checkpoint_log_activity'; + +# +# show that it's writable +# +set global aria_checkpoint_log_activity=1; +select @@global.aria_checkpoint_log_activity; +--error ER_GLOBAL_VARIABLE +set session aria_checkpoint_log_activity=1; + +# +# incorrect types +# +--error ER_WRONG_TYPE_FOR_VAR +set global aria_checkpoint_log_activity=1.1; +--error ER_WRONG_TYPE_FOR_VAR +set global aria_checkpoint_log_activity=1e1; +--error ER_WRONG_TYPE_FOR_VAR +set global aria_checkpoint_log_activity="foo"; + +# +# min/max values +# +set global aria_checkpoint_log_activity=0; +select @@global.aria_checkpoint_log_activity; +set global aria_checkpoint_log_activity=cast(-1 as unsigned int); +select @@global.aria_checkpoint_log_activity; + +SET @@global.aria_checkpoint_log_activity = @start_global_value; diff --git a/mysql-test/suite/sys_vars/t/binlog_annotate_row_events_basic.test b/mysql-test/suite/sys_vars/t/binlog_annotate_row_events_basic.test new file mode 100644 index 00000000000..9dd3545e1ec --- /dev/null +++ b/mysql-test/suite/sys_vars/t/binlog_annotate_row_events_basic.test @@ -0,0 +1,39 @@ +# bool session + +SET @start_global_value = @@global.binlog_annotate_row_events; + +select @@global.binlog_annotate_row_events; +select @@session.binlog_annotate_row_events; +show global variables like 'binlog_annotate_row_events'; +show session variables like 'binlog_annotate_row_events'; +select * from information_schema.global_variables where variable_name='binlog_annotate_row_events'; +select * from information_schema.session_variables where variable_name='binlog_annotate_row_events'; + +# +# show that it's writable +# +set global binlog_annotate_row_events=ON; +select @@global.binlog_annotate_row_events; +set global binlog_annotate_row_events=OFF; +select @@global.binlog_annotate_row_events; +set global binlog_annotate_row_events=1; +select @@global.binlog_annotate_row_events; + +set session binlog_annotate_row_events=ON; +select @@session.binlog_annotate_row_events; +set session binlog_annotate_row_events=OFF; +select @@session.binlog_annotate_row_events; +set session binlog_annotate_row_events=1; +select @@session.binlog_annotate_row_events; +# +# incorrect types +# +--error ER_WRONG_TYPE_FOR_VAR +set global binlog_annotate_row_events=1.1; +--error ER_WRONG_TYPE_FOR_VAR +set session binlog_annotate_row_events=1e1; +--error ER_WRONG_VALUE_FOR_VAR +set session binlog_annotate_row_events="foo"; + +SET @@global.binlog_annotate_row_events = @start_global_value; + diff --git a/mysql-test/suite/sys_vars/t/binlog_annotate_rows_events_basic.test b/mysql-test/suite/sys_vars/t/binlog_annotate_rows_events_basic.test deleted file mode 100644 index 89bdb0986e5..00000000000 --- a/mysql-test/suite/sys_vars/t/binlog_annotate_rows_events_basic.test +++ /dev/null @@ -1,39 +0,0 @@ -# bool session - -SET @start_global_value = @@global.binlog_annotate_rows_events; - -select @@global.binlog_annotate_rows_events; -select @@session.binlog_annotate_rows_events; -show global variables like 'binlog_annotate_rows_events'; -show session variables like 'binlog_annotate_rows_events'; -select * from information_schema.global_variables where variable_name='binlog_annotate_rows_events'; -select * from information_schema.session_variables where variable_name='binlog_annotate_rows_events'; - -# -# show that it's writable -# -set global binlog_annotate_rows_events=ON; -select @@global.binlog_annotate_rows_events; -set global binlog_annotate_rows_events=OFF; -select @@global.binlog_annotate_rows_events; -set global binlog_annotate_rows_events=1; -select @@global.binlog_annotate_rows_events; - -set session binlog_annotate_rows_events=ON; -select @@session.binlog_annotate_rows_events; -set session binlog_annotate_rows_events=OFF; -select @@session.binlog_annotate_rows_events; -set session binlog_annotate_rows_events=1; -select @@session.binlog_annotate_rows_events; -# -# incorrect types -# ---error ER_WRONG_TYPE_FOR_VAR -set global binlog_annotate_rows_events=1.1; ---error ER_WRONG_TYPE_FOR_VAR -set session binlog_annotate_rows_events=1e1; ---error ER_WRONG_VALUE_FOR_VAR -set session binlog_annotate_rows_events="foo"; - -SET @@global.binlog_annotate_rows_events = @start_global_value; - diff --git a/mysql-test/suite/sys_vars/t/binlog_dbug_fsync_sleep_basic.test b/mysql-test/suite/sys_vars/t/binlog_dbug_fsync_sleep_basic.test deleted file mode 100644 index 8e9f4651929..00000000000 --- a/mysql-test/suite/sys_vars/t/binlog_dbug_fsync_sleep_basic.test +++ /dev/null @@ -1,48 +0,0 @@ ---source include/have_debug.inc - -# ulong global - -SET @start_global_value = @@global.binlog_dbug_fsync_sleep; - -# -# exists as global only -# -select @@global.binlog_dbug_fsync_sleep; ---error ER_INCORRECT_GLOBAL_LOCAL_VAR -select @@session.binlog_dbug_fsync_sleep; -show global variables like 'binlog_dbug_fsync_sleep'; -show session variables like 'binlog_dbug_fsync_sleep'; -select * from information_schema.global_variables where variable_name='binlog_dbug_fsync_sleep'; -select * from information_schema.session_variables where variable_name='binlog_dbug_fsync_sleep'; - -# -# show that it's writable -# -set global binlog_dbug_fsync_sleep=200; -select @@global.binlog_dbug_fsync_sleep; ---error ER_GLOBAL_VARIABLE -set session binlog_dbug_fsync_sleep=1; - -# -# incorrect types -# ---error ER_WRONG_TYPE_FOR_VAR -set global binlog_dbug_fsync_sleep=1.1; ---error ER_WRONG_TYPE_FOR_VAR -set global binlog_dbug_fsync_sleep=1e1; ---error ER_WRONG_TYPE_FOR_VAR -set global binlog_dbug_fsync_sleep="foo"; - -# -# min/max values, block size -# -set global binlog_dbug_fsync_sleep=1; -select @@global.binlog_dbug_fsync_sleep; -set global binlog_dbug_fsync_sleep=@@global.binlog_dbug_fsync_sleep + 100 - 1; -select @@global.binlog_dbug_fsync_sleep; -set global binlog_dbug_fsync_sleep=@@global.binlog_dbug_fsync_sleep + 100; -select @@global.binlog_dbug_fsync_sleep; -set global binlog_dbug_fsync_sleep=cast(-1 as unsigned int); -select @@global.binlog_dbug_fsync_sleep; - -SET @@global.binlog_dbug_fsync_sleep = @start_global_value; diff --git a/mysql-test/suite/sys_vars/t/debug_binlog_fsync_sleep_basic.test b/mysql-test/suite/sys_vars/t/debug_binlog_fsync_sleep_basic.test new file mode 100644 index 00000000000..73f61c45623 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/debug_binlog_fsync_sleep_basic.test @@ -0,0 +1,42 @@ +# ulong global + +SET @start_global_value = @@global.debug_binlog_fsync_sleep; + +# +# exists as global only +# +select @@global.debug_binlog_fsync_sleep; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.debug_binlog_fsync_sleep; +show global variables like 'debug_binlog_fsync_sleep'; +show session variables like 'debug_binlog_fsync_sleep'; +select * from information_schema.global_variables where variable_name='debug_binlog_fsync_sleep'; +select * from information_schema.session_variables where variable_name='debug_binlog_fsync_sleep'; + +# +# show that it's writable +# +set global debug_binlog_fsync_sleep=20; +select @@global.debug_binlog_fsync_sleep; +--error ER_GLOBAL_VARIABLE +set session debug_binlog_fsync_sleep=1; + +# +# incorrect types +# +--error ER_WRONG_TYPE_FOR_VAR +set global debug_binlog_fsync_sleep=1.1; +--error ER_WRONG_TYPE_FOR_VAR +set global debug_binlog_fsync_sleep=1e1; +--error ER_WRONG_TYPE_FOR_VAR +set global debug_binlog_fsync_sleep="foo"; + +# +# min/max values, block size +# +set global debug_binlog_fsync_sleep=0; +select @@global.debug_binlog_fsync_sleep; +set global debug_binlog_fsync_sleep=cast(-1 as unsigned int); +select @@global.debug_binlog_fsync_sleep; + +SET @@global.debug_binlog_fsync_sleep = @start_global_value; diff --git a/mysql-test/suite/sys_vars/t/max_user_connections-2.test b/mysql-test/suite/sys_vars/t/max_user_connections-2.test new file mode 100644 index 00000000000..a376c78a09b --- /dev/null +++ b/mysql-test/suite/sys_vars/t/max_user_connections-2.test @@ -0,0 +1,11 @@ +# +# Test behavior of various per-account limits (aka quotas) +# +--source include/not_embedded.inc + +# +# We will get an error as it was set to 0 at startup +# +--error ER_OPTION_PREVENTS_STATEMENT +set global max_user_connections=100; + diff --git a/mysql-test/suite/sys_vars/t/max_user_connections_basic-master.opt b/mysql-test/suite/sys_vars/t/max_user_connections_basic-master.opt new file mode 100644 index 00000000000..f507cd0406c --- /dev/null +++ b/mysql-test/suite/sys_vars/t/max_user_connections_basic-master.opt @@ -0,0 +1 @@ +--max-user-connections=10 diff --git a/mysql-test/suite/sys_vars/t/max_user_connections_basic.test b/mysql-test/suite/sys_vars/t/max_user_connections_basic.test index 1adcc012bc4..8c9d031030f 100644 --- a/mysql-test/suite/sys_vars/t/max_user_connections_basic.test +++ b/mysql-test/suite/sys_vars/t/max_user_connections_basic.test @@ -77,9 +77,9 @@ SET @@global.max_user_connections = 2; SELECT @@global.max_user_connections; SET @@global.max_user_connections = 65536; SELECT @@global.max_user_connections; -SET @@global.max_user_connections = 4294967295; +SET @@global.max_user_connections = 2147483647; SELECT @@global.max_user_connections; -SET @@global.max_user_connections = 4294967294; +SET @@global.max_user_connections = 2147483646; SELECT @@global.max_user_connections; diff --git a/mysql-test/suite/sys_vars/t/max_user_connections_func-master.opt b/mysql-test/suite/sys_vars/t/max_user_connections_func-master.opt new file mode 100644 index 00000000000..f507cd0406c --- /dev/null +++ b/mysql-test/suite/sys_vars/t/max_user_connections_func-master.opt @@ -0,0 +1 @@ +--max-user-connections=10 diff --git a/mysql-test/suite/sys_vars/t/max_user_connections_func.test b/mysql-test/suite/sys_vars/t/max_user_connections_func.test index 841a630c5ce..74707c9b296 100644 --- a/mysql-test/suite/sys_vars/t/max_user_connections_func.test +++ b/mysql-test/suite/sys_vars/t/max_user_connections_func.test @@ -39,21 +39,23 @@ SET @default_max_user_connections = @@global.max_user_connections; Set Global max_user_connections=2; +GRANT USAGE on *.* TO test@localhost; + --echo '#--------------------FN_DYNVARS_114_01-------------------------#' ######################################## #Should not make more then 2 connection# ######################################## ---echo ** Connecting conn1 using username 'root' ** -CONNECT (conn1,localhost,root,,); +--echo ** Connecting conn1 using username 'test' ** +CONNECT (conn1,localhost,test,,); ---echo ** Connecting conn2 using username 'root' ** -CONNECT (conn2,localhost,root,,); +--echo ** Connecting conn2 using username 'test' ** +CONNECT (conn2,localhost,test,,); ---echo ** Connecting conn3 using username 'root' ** +--echo ** Connecting conn3 using username 'test' ** --disable_query_log --Error ER_TOO_MANY_USER_CONNECTIONS -CONNECT (conn3,localhost,root,,); +CONNECT (conn3,localhost,test,,); --enable_query_log --echo Expected error "too many connections" @@ -62,7 +64,7 @@ DISCONNECT conn1; --echo ** Poll till disconnected conn1 disappears from processlist let $wait_condition= SELECT count(id) <= 2 - FROM information_schema.processlist WHERE user = 'root'; + FROM information_schema.processlist WHERE user = 'test'; --source include/wait_condition.inc --echo '#--------------------FN_DYNVARS_114_02-------------------------#' @@ -70,11 +72,12 @@ let $wait_condition= SELECT count(id) <= 2 #Set value to 3 and see if 3 connections can be made# ##################################################### +connection default; Set Global max_user_connections=3; ---echo ** Connecting conn5 using username 'root' ** -CONNECT (conn5,localhost,root,,); ---echo ** Connecting conn6 using username 'root' ** -CONNECT (conn6,localhost,root,,); +--echo ** Connecting conn5 using username 'test' ** +CONNECT (conn5,localhost,test,,); +--echo ** Connecting conn6 using username 'test' ** +CONNECT (conn6,localhost,test,,); # # Cleanup @@ -88,5 +91,7 @@ DISCONNECT conn2; DISCONNECT conn5; DISCONNECT conn6; +drop user test@localhost; + SET @@global.max_user_connections = @default_max_user_connections; diff --git a/mysql-test/suite/sys_vars/t/optimizer_switch_eng_cond_pushdown1-master.opt b/mysql-test/suite/sys_vars/t/optimizer_switch_eng_cond_pushdown1-master.opt new file mode 100644 index 00000000000..89aa07976ac --- /dev/null +++ b/mysql-test/suite/sys_vars/t/optimizer_switch_eng_cond_pushdown1-master.opt @@ -0,0 +1 @@ +--optimizer-switch=engine_condition_pushdown=off --engine-condition-pushdown=1 diff --git a/mysql-test/suite/sys_vars/t/optimizer_switch_eng_cond_pushdown1.test b/mysql-test/suite/sys_vars/t/optimizer_switch_eng_cond_pushdown1.test new file mode 100644 index 00000000000..187aa145408 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/optimizer_switch_eng_cond_pushdown1.test @@ -0,0 +1,5 @@ +# check how --engine-condition-pushdown and --optimizer-switch +# influence each other when used together (last wins). +select @@session.engine_condition_pushdown, +@@global.engine_condition_pushdown, +@@session.optimizer_switch, @@global.optimizer_switch; diff --git a/mysql-test/suite/sys_vars/t/optimizer_switch_eng_cond_pushdown2-master.opt b/mysql-test/suite/sys_vars/t/optimizer_switch_eng_cond_pushdown2-master.opt new file mode 100644 index 00000000000..f48ab5b963b --- /dev/null +++ b/mysql-test/suite/sys_vars/t/optimizer_switch_eng_cond_pushdown2-master.opt @@ -0,0 +1 @@ +--engine-condition-pushdown=1 --optimizer-switch=engine_condition_pushdown=off diff --git a/mysql-test/suite/sys_vars/t/optimizer_switch_eng_cond_pushdown2.test b/mysql-test/suite/sys_vars/t/optimizer_switch_eng_cond_pushdown2.test new file mode 100644 index 00000000000..187aa145408 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/optimizer_switch_eng_cond_pushdown2.test @@ -0,0 +1,5 @@ +# check how --engine-condition-pushdown and --optimizer-switch +# influence each other when used together (last wins). +select @@session.engine_condition_pushdown, +@@global.engine_condition_pushdown, +@@session.optimizer_switch, @@global.optimizer_switch; diff --git a/mysql-test/suite/sys_vars/t/replicate_annotate_row_events_basic.test b/mysql-test/suite/sys_vars/t/replicate_annotate_row_events_basic.test new file mode 100644 index 00000000000..f4d7221f1b8 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/replicate_annotate_row_events_basic.test @@ -0,0 +1,23 @@ +# bool readonly + +--source include/not_embedded.inc + +# +# show values; +# +select @@global.replicate_annotate_row_events; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.replicate_annotate_row_events; +show global variables like 'replicate_annotate_row_events'; +show session variables like 'replicate_annotate_row_events'; +select * from information_schema.global_variables where variable_name='replicate_annotate_row_events'; +select * from information_schema.session_variables where variable_name='replicate_annotate_row_events'; + +# +# show that it's read-only +# +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set global replicate_annotate_row_events=1; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set session replicate_annotate_row_events=1; + diff --git a/mysql-test/suite/sys_vars/t/replicate_annotate_rows_events_basic.test b/mysql-test/suite/sys_vars/t/replicate_annotate_rows_events_basic.test deleted file mode 100644 index b519859e971..00000000000 --- a/mysql-test/suite/sys_vars/t/replicate_annotate_rows_events_basic.test +++ /dev/null @@ -1,23 +0,0 @@ -# bool readonly - ---source include/not_embedded.inc - -# -# show values; -# -select @@global.replicate_annotate_rows_events; ---error ER_INCORRECT_GLOBAL_LOCAL_VAR -select @@session.replicate_annotate_rows_events; -show global variables like 'replicate_annotate_rows_events'; -show session variables like 'replicate_annotate_rows_events'; -select * from information_schema.global_variables where variable_name='replicate_annotate_rows_events'; -select * from information_schema.session_variables where variable_name='replicate_annotate_rows_events'; - -# -# show that it's read-only -# ---error ER_INCORRECT_GLOBAL_LOCAL_VAR -set global replicate_annotate_rows_events=1; ---error ER_INCORRECT_GLOBAL_LOCAL_VAR -set session replicate_annotate_rows_events=1; - diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result index 58bd048ec85..693ea0d9174 100644 --- a/mysql-test/suite/vcol/r/vcol_misc.result +++ b/mysql-test/suite/vcol/r/vcol_misc.result @@ -139,6 +139,9 @@ create table t2 (c int, d int, v int as (d+1), index idx(c)); insert into t2(c,d) values (20, 100), (20, 300), (30, 100), (30, 200), (40, 500), (70, 100), (40, 300), (60, 100), (40, 100), (70, 100); +insert into t2(c,d) values +(120, 100), (150, 300), (130, 100), (130, 200), (140, 500), +(170, 100), (180, 300), (160, 100), (40, 100), (170, 100); set join_cache_level=6; explain select * from t1,t2 where t1.b=t2.c and d <= 100; diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result index dfae286e984..6dafda87647 100644 --- a/mysql-test/suite/vcol/r/vcol_select_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result @@ -63,8 +63,8 @@ a b c 3 -3 -3 explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where -2 DEPENDENT SUBQUERY t3 index_subquery c c 5 func 1 Using index +1 PRIMARY t3 index c c 5 NULL 3 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c @@ -73,8 +73,8 @@ a b c 1 -1 -1 explain select * from t1 where c in (select c from t3 where c between -2 and -1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where -2 DEPENDENT SUBQUERY t3 index_subquery c c 5 func 1 Using index; Using where +1 PRIMARY t3 index c c 5 NULL 3 Using where; Using index +1 PRIMARY t1 ALL c NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) # select_type=UNION, type=system # select_type=UNION RESULT, type=<union1,2> select * from t1 union select * from t2; diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result index 24af9cd4256..4d74d791841 100644 --- a/mysql-test/suite/vcol/r/vcol_select_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result @@ -63,8 +63,8 @@ a b c 3 -3 -3 explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where -2 DEPENDENT SUBQUERY t3 index_subquery c c 5 func 2 Using index +1 PRIMARY t3 index c c 5 NULL 3 Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c @@ -73,8 +73,8 @@ a b c 1 -1 -1 explain select * from t1 where c in (select c from t3 where c between -2 and -1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where -2 DEPENDENT SUBQUERY t3 index_subquery c c 5 func 2 Using index; Using where +1 PRIMARY t3 index c c 5 NULL 3 Using where; Using index +1 PRIMARY t1 ref c c 5 test.t3.c 2 # select_type=UNION, type=system # select_type=UNION RESULT, type=<union1,2> select * from t1 union select * from t2; diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test index a0616d187da..f87cb5fbec8 100644 --- a/mysql-test/suite/vcol/t/vcol_misc.test +++ b/mysql-test/suite/vcol/t/vcol_misc.test @@ -151,6 +151,9 @@ create table t2 (c int, d int, v int as (d+1), index idx(c)); insert into t2(c,d) values (20, 100), (20, 300), (30, 100), (30, 200), (40, 500), (70, 100), (40, 300), (60, 100), (40, 100), (70, 100); +insert into t2(c,d) values + (120, 100), (150, 300), (130, 100), (130, 200), (140, 500), + (170, 100), (180, 300), (160, 100), (40, 100), (170, 100); set join_cache_level=6; explain |