revision-id: cfd2646c3152bbefe11e381c00b701a49cf5c4ef (mariadb-10.3.6-99-gcfd2646) parent(s): 37deed3f37561f264f65e162146bbc2ad35fb1a2 author: Igor Babaev committer: Igor Babaev timestamp: 2019-02-03 22:26:39 -0800 message: Fixed the results after the merge of 10.4 into bb-10.4-mdev16188. --- mysql-test/main/delete_use_source.result | 2 +- mysql-test/main/group_by.result | 1 + mysql-test/main/innodb_ext_key.result | 2 + mysql-test/main/join.result | 4 +- mysql-test/main/join_nested.result | 2 +- mysql-test/main/join_nested_jcl6.result | 4 +- mysql-test/main/join_outer_innodb.result | 32 +++++------ mysql-test/main/key.result | 3 +- mysql-test/main/myisam_icp.result | 8 ++- mysql-test/main/partition_range.result | 2 + mysql-test/main/range_vs_index_merge_innodb.result | 62 +++++++++++----------- mysql-test/main/select.result | 1 + mysql-test/main/select_jcl6.result | 1 + mysql-test/main/select_pkeycache.result | 1 + mysql-test/main/stat_tables.result | 4 +- mysql-test/main/subselect.result | 1 + mysql-test/main/subselect_mat.result | 21 +++++--- mysql-test/main/subselect_no_exists_to_in.result | 1 + mysql-test/main/subselect_no_mat.result | 1 + mysql-test/main/subselect_no_opts.result | 1 + mysql-test/main/subselect_no_scache.result | 1 + mysql-test/main/subselect_no_semijoin.result | 1 + mysql-test/main/subselect_sj.result | 4 +- mysql-test/main/subselect_sj2.result | 10 ++++ mysql-test/main/subselect_sj2_jcl6.result | 10 ++++ mysql-test/main/subselect_sj_jcl6.result | 4 +- mysql-test/main/subselect_sj_mat.result | 37 +++++++------ mysql-test/main/type_bit.result | 1 + mysql-test/main/union.result | 2 +- .../suite/sys_vars/r/optimizer_switch_basic.result | 36 ++++++------- .../sys_vars/r/sysvars_server_notembedded.result | 22 ++++++-- 31 files changed, 177 insertions(+), 105 deletions(-) diff --git a/mysql-test/main/delete_use_source.result b/mysql-test/main/delete_use_source.result index a9a6df8..0ce010e 100644 --- a/mysql-test/main/delete_use_source.result +++ b/mysql-test/main/delete_use_source.result @@ -48,7 +48,7 @@ rollback; start transaction; explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range c1 c1 4 NULL 600 Using where +1 PRIMARY t1 range c1 c1 4 NULL 502 Using where 2 DEPENDENT SUBQUERY b ref c1 c1 4 test.t1.c1 167 Using index delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1; affected rows: 1 diff --git a/mysql-test/main/group_by.result b/mysql-test/main/group_by.result index a6878e8..2eaec8a 100644 --- a/mysql-test/main/group_by.result +++ b/mysql-test/main/group_by.result @@ -2434,6 +2434,7 @@ INSERT INTO t1 VALUES (1,NULL),(0,'a'),(1,NULL),(0,'a'), (1,'a'),(0,'a'),(2,'a'),(1,'a'); ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result index b6fd9b0..daf1f53 100644 --- a/mysql-test/main/innodb_ext_key.result +++ b/mysql-test/main/innodb_ext_key.result @@ -1036,7 +1036,9 @@ insert into t1 (b) values (null), (null), (null); insert into t2 (b) values (null), (null), (null); analyze table t1,t2; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK set optimizer_switch='extended_keys=on'; explain select a from t1 where b is null order by a desc limit 2; diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index 57ed343..54da744 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -1270,7 +1270,9 @@ INSERT INTO t1 VALUES (3,'b'),(4,NULL),(5,'c'),(6,'cc'),(7,'d'), INSERT INTO t2 VALUES (2,NULL); ANALYZE TABLE t1,t2; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK FLUSH STATUS; SELECT * FROM t1 JOIN t2 ON t1.v = t2.v WHERE t2.v IS NULL ORDER BY 1; @@ -1278,7 +1280,7 @@ pk v pk v SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value Handler_read_first 0 -Handler_read_key 1 +Handler_read_key 11 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 diff --git a/mysql-test/main/join_nested.result b/mysql-test/main/join_nested.result index 603612e..4db32c3 100644 --- a/mysql-test/main/join_nested.result +++ b/mysql-test/main/join_nested.result @@ -1064,7 +1064,7 @@ t0.b=t1.b AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ref idx_a idx_a 5 const 2 100.00 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t1 ALL idx_b NULL NULL NULL 7 85.71 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result index bc65286..5791185 100644 --- a/mysql-test/main/join_nested_jcl6.result +++ b/mysql-test/main/join_nested_jcl6.result @@ -1075,7 +1075,7 @@ t0.b=t1.b AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ref idx_a idx_a 5 const 2 100.00 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t1 ALL idx_b NULL NULL NULL 7 85.71 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t1 ALL idx_b NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan @@ -2003,7 +2003,7 @@ ON t6.b >= 2 AND t5.b=t7.b AND id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 1 SIMPLE t7 ref|filter PRIMARY,b_i b_i|PRIMARY 5|4 test.t5.b 2 (29%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter -1 SIMPLE t6 range|filter PRIMARY,b_i PRIMARY|b_i 4|5 NULL 3 (86%) Using where; Rowid-ordered scan; Using join buffer (incremental, BNL join); Using filter +1 SIMPLE t6 range PRIMARY,b_i PRIMARY 4 NULL 3 Using where; Rowid-ordered scan; Using join buffer (incremental, BNL join) 1 SIMPLE t8 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b FROM t5 diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result index 9ab8bde..9026a32 100644 --- a/mysql-test/main/join_outer_innodb.result +++ b/mysql-test/main/join_outer_innodb.result @@ -433,47 +433,47 @@ left join t16 on t15.o1 = t16.p1 where t1.a10 = 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a4,a6,a5,a7 NULL NULL NULL 3 Using where -1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where -1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index 1 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where 1 SIMPLE t13 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index 1 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index 1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index -1 SIMPLE l3 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index +1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index +1 SIMPLE t5 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 SIMPLE l3 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 +1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index 1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index 1 SIMPLE t16 ref PRIMARY PRIMARY 2 test.t15.o1 1 Using where +1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a4,a6,a5,a7 NULL NULL NULL 3 Using where -1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where -1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index 1 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where 1 SIMPLE t13 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index 1 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index 1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index -1 SIMPLE l3 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index +1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index +1 SIMPLE t5 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 SIMPLE l3 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 +1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index 1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index 1 SIMPLE t16 ref PRIMARY PRIMARY 2 test.t15.o1 1 Using where +1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) drop view v1; drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16; # diff --git a/mysql-test/main/key.result b/mysql-test/main/key.result index 42eca8d..d1d751c 100644 --- a/mysql-test/main/key.result +++ b/mysql-test/main/key.result @@ -605,12 +605,13 @@ VALUES (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); ANALYZE table t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t1 range NULL a 5 NULL 4 Using index for group-by +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index SELECT 1 as RES FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; RES diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index 804a039..b76a4c2 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -407,7 +407,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,k1 k1 5 NULL 4 Using where +1 SIMPLE t1 range|filter PRIMARY,k1 PRIMARY|k1 4|5 NULL 3 (50%) Using index condition; Using where; Rowid-ordered scan; Using filesort; Using filter DROP TABLE t1; # # @@ -590,7 +590,9 @@ PRIMARY KEY (pk) INSERT INTO t2 VALUES (4,1); ANALYZE TABLE t1,t2; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK EXPLAIN SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 @@ -799,7 +801,9 @@ INSERT INTO t2 (g,h) VALUES (7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'); ANALYZE TABLE t1,t2; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status Table is already up to date SET @save_optimize_switch=@@optimizer_switch; SET optimizer_switch='materialization=on'; @@ -810,7 +814,7 @@ AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b) OR a = 0 AND h < 'z' ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL PRIMARY,c NULL NULL NULL 64 Using where -1 PRIMARY t2 ref g g 5 test.t.c 19 Using where +1 PRIMARY t2 ref g g 5 test.t.c 18 Using where 2 DEPENDENT SUBQUERY t1 index PRIMARY PRIMARY 4 NULL 64 Using where; Using index 2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where SELECT COUNT(*) FROM t1 AS t, t2 diff --git a/mysql-test/main/partition_range.result b/mysql-test/main/partition_range.result index 2a79e01..48b1ce8 100644 --- a/mysql-test/main/partition_range.result +++ b/mysql-test/main/partition_range.result @@ -960,7 +960,9 @@ INSERT INTO t1 SELECT a + 40, b + 40 FROM t1; INSERT INTO t2 SELECT * FROM t1; ANALYZE TABLE t1,t2; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status Table is already up to date # plans should be identical EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a; diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result index f085cf7..6b6b772 100644 --- a/mysql-test/main/range_vs_index_merge_innodb.result +++ b/mysql-test/main/range_vs_index_merge_innodb.result @@ -1085,7 +1085,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge Country,CountryPopulation,CountryName,CityName CountryName,CityName 38,35 NULL 27 Using sort_union(CountryName,CityName); Using where +1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CityName 35 NULL 27 Using index condition; Using where SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR (Name='Addis Abeba' AND Country='ETH') OR @@ -1116,32 +1116,32 @@ SELECT Name, Country, Population FROM City WHERE (Name='Seattle' AND Country='USA'); Name Country Population Addis Abeba ETH 2495000 -Manila PHL 1581082 -Jakarta IDN 9604900 -Delhi IND 7206704 +Ankara TUR 3038159 Bangalore IND 2660088 -Teheran IRN 6758845 -Roma ITA 2643581 -Venezia ITA 277305 -Tokyo JPN 7980230 -Toronto CAN 688275 -Vancouver CAN 514008 -Peking CHN 7472000 -Seoul KOR 9981619 +Basel CHE 166700 +Caracas VEN 1975294 +Dakar SEN 785071 +Delhi IND 7206704 +Dresden DEU 476668 +Jakarta IDN 9604900 Kaunas LTU 412639 -Rabat MAR 623457 -Tijuana MEX 1212232 Lagos NGA 1518000 +Lugansk UKR 469000 +Manila PHL 1581082 Paris FRA 2125246 -Dresden DEU 476668 -Dakar SEN 785071 -Basel CHE 166700 +Peking CHN 7472000 Praha CZE 1181126 -Ankara TUR 3038159 -Lugansk UKR 469000 -Caracas VEN 1975294 +Rabat MAR 623457 +Roma ITA 2643581 Samara RUS 1156100 Seattle USA 563374 +Seoul KOR 9981619 +Teheran IRN 6758845 +Tijuana MEX 1212232 +Tokyo JPN 7980230 +Toronto CAN 688275 +Vancouver CAN 514008 +Venezia ITA 277305 set optimizer_switch='index_merge=off'; EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR @@ -1172,7 +1172,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CityName 35 NULL 28 Using index condition; Using where +1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CityName 35 NULL 27 Using index condition; Using where SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR (Name='Addis Abeba' AND Country='ETH') OR @@ -1757,6 +1757,7 @@ INSERT INTO t1 VALUES (0,72,321,-7),(0,73,0,3),(0,74,5,25),(0,75,5,3); ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SET SESSION optimizer_switch='index_merge_sort_union=off'; EXPLAIN @@ -1837,6 +1838,7 @@ INSERT INTO t1 VALUES ; ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT * FROM t1 FORCE KEY (state,capital) @@ -1904,23 +1906,23 @@ Country='NOR' AND Name IN ('Oslo', 'Bergen') OR Country='ITA' AND Name IN ('Napoli', 'Venezia'); ID Name Country Population 175 Antwerpen BEL 446525 -176 Gent BEL 224180 +2808 Bergen NOR 230948 3068 Berlin DEU 3386667 3087 Bonn DEU 301048 +2918 Braga PRT 90535 +176 Gent BEL 224180 3242 Lahti FIN 96921 -2974 Paris FRA 2125246 +3580 Moscow RUS 8389200 1466 Napoli ITA 1002619 -1474 Venezia ITA 277305 -2808 Bergen NOR 230948 2807 Oslo NOR 508726 -2928 Warszawa POL 1615369 -2931 Wroclaw POL 636765 -2918 Braga PRT 90535 +2974 Paris FRA 2125246 2915 Porto PRT 273060 -3580 Moscow RUS 8389200 3581 St Petersburg RUS 4694000 3048 Stockholm SWE 750348 3051 Uppsala SWE 189569 +1474 Venezia ITA 277305 +2928 Warszawa POL 1615369 +2931 Wroclaw POL 636765 explain select * from City where Country='FIN' AND Name IN ('Lahti','Imatra') OR @@ -1934,7 +1936,7 @@ Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR Country='NOR' AND Name IN ('Oslo', 'Bergen') OR Country='ITA' AND Name IN ('Napoli', 'Venezia'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition; Using where +1 SIMPLE City range CountryName,Name Name 35 NULL 20 Using index condition; Using where DROP DATABASE world; set session optimizer_switch='index_merge_sort_intersection=default'; set global innodb_stats_persistent= @innodb_stats_persistent_save; diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index 230bef6..a73516f 100644 --- a/mysql-test/main/select.result +++ b/mysql-test/main/select.result @@ -3701,6 +3701,7 @@ INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; COUNT(*) diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index a27585c..3f9a9f4 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -3712,6 +3712,7 @@ INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; COUNT(*) diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index 230bef6..a73516f 100644 --- a/mysql-test/main/select_pkeycache.result +++ b/mysql-test/main/select_pkeycache.result @@ -3701,6 +3701,7 @@ INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; COUNT(*) diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index 56efb53..8cfa838 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -68,7 +68,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE nation ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3_s001.region.r_regionkey 5 1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 -1 SIMPLE orders ref PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 Using where +1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (12%) Using where; Using filter 1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region @@ -175,7 +175,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE nation ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3_s001.region.r_regionkey 5 1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 -1 SIMPLE orders ref PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 Using where +1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (12%) Using where; Using filter 1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 393e9be..3bd23a4 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -3106,6 +3106,7 @@ insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); insert into t2(a, c, b) values (4,10,'360'), (5,10,'35998'), (6,10,'35999'); analyze table t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 1850742..3cd45d1 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -41,11 +41,17 @@ insert into t2i select * from t2; insert into t3i select * from t3; analyze table t1,t2,t3,t1i,t2i,t3i; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK +test.t1i analyze status Engine-independent statistics collected test.t1i analyze status Table is already up to date +test.t2i analyze status Engine-independent statistics collected test.t2i analyze status Table is already up to date +test.t3i analyze status Engine-independent statistics collected test.t3i analyze status Table is already up to date set @@optimizer_switch='materialization=on,in_to_exists=off,firstmatch=off'; /****************************************************************************** @@ -133,7 +139,7 @@ explain extended select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1i index NULL # # # 3 100.00 # -2 MATERIALIZED t2i range it2i1,it2i3 # # # 3 100.00 # +2 MATERIALIZED t2i range it2i1,it2i3 # # # 5 100.00 # Warnings: Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` where `test`.`t2i`.`b1` > '0' group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where `test`.`t1i`.`a1` = `<subquery2>`.`b1` and `test`.`t1i`.`a2` = `<subquery2>`.`max(b2)`)))) select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); @@ -144,7 +150,7 @@ explain extended select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1i index NULL # # # 3 100.00 # -2 MATERIALIZED t2i range it2i1,it2i3 # # # 3 100.00 # +2 MATERIALIZED t2i range it2i1,it2i3 # # # 5 100.00 # Warnings: Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where `test`.`t2i`.`b1` > '0' group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where `test`.`t1i`.`a1` = `<subquery2>`.`b1` and `test`.`t1i`.`a2` = `<subquery2>`.`min(b2)`)))) select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); @@ -158,6 +164,7 @@ insert into t2i_c select * from t2i; insert into t2i_c select * from t2i; analyze table t2i_c; Table Op Msg_type Msg_text +test.t2i_c analyze status Engine-independent statistics collected test.t2i_c analyze status OK show create table t2i_c; Table Create Table @@ -172,7 +179,7 @@ explain extended select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -2 MATERIALIZED t2i_c range NULL it2i3 9 NULL 3 100.00 Using index for group-by +2 MATERIALIZED t2i_c range NULL it2i3 9 NULL 4 100.00 Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2i_c`.`b1`,max(`test`.`t2i_c`.`b2`) from `test`.`t2i_c` group by `test`.`t2i_c`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where `test`.`t1`.`a1` = `<subquery2>`.`b1` and `test`.`t1`.`a2` = `<subquery2>`.`max(b2)`)))) select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1); @@ -183,11 +190,11 @@ prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) execute st1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -2 MATERIALIZED t2i_c range NULL it2i3 9 NULL 3 Using index for group-by +2 MATERIALIZED t2i_c range NULL it2i3 9 NULL 4 Using index for group-by execute st1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -2 MATERIALIZED t2i_c range NULL it2i3 9 NULL 3 Using index for group-by +2 MATERIALIZED t2i_c range NULL it2i3 9 NULL 4 Using index for group-by prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1)"; execute st2; a1 a2 @@ -202,7 +209,7 @@ explain extended select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -2 MATERIALIZED t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by +2 MATERIALIZED t2i range it2i1,it2i3 it2i3 9 NULL 5 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where `test`.`t2i`.`b1` > '0' group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where `test`.`t1`.`a1` = `<subquery2>`.`b1` and `test`.`t1`.`a2` = `<subquery2>`.`min(b2)`)))) select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); @@ -511,7 +518,7 @@ where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 5 DEPENDENT SUBQUERY t3c ALL NULL NULL NULL NULL 4 100.00 Using where -6 DEPENDENT SUBQUERY t2i index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 2 100.00 Using index; Using where +6 DEPENDENT SUBQUERY t2i index_subquery it2i1,it2i2,it2i3 it2i3 18 func,func 1 100.00 Using index; Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where 4 MATERIALIZED t3b ALL NULL NULL NULL NULL 4 100.00 Using where 3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 46b6dd0..bacba84 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -3109,6 +3109,7 @@ insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); insert into t2(a, c, b) values (4,10,'360'), (5,10,'35998'), (6,10,'35999'); analyze table t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index d7b72eb..a5b1d95 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -3111,6 +3111,7 @@ insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); insert into t2(a, c, b) values (4,10,'360'), (5,10,'35998'), (6,10,'35999'); analyze table t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 0b528a7..0ea16d8 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -3107,6 +3107,7 @@ insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); insert into t2(a, c, b) values (4,10,'360'), (5,10,'35998'), (6,10,'35999'); analyze table t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 5062c04..196af2d 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -3112,6 +3112,7 @@ insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); insert into t2(a, c, b) values (4,10,'360'), (5,10,'35998'), (6,10,'35999'); analyze table t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index fa1312e..c590a5d 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -3107,6 +3107,7 @@ insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); insert into t2(a, c, b) values (4,10,'360'), (5,10,'35998'), (6,10,'35999'); analyze table t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index b16cdc2..85a1ee1 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -3105,13 +3105,13 @@ explain select a from t1, t2 where b between 1 and 2 and a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where -1 PRIMARY t2 ref idx idx 5 test.t1.a 1463 Using index; FirstMatch(t1) +1 PRIMARY t2 ref idx idx 5 test.t1.a 1462 Using index; FirstMatch(t1) 1 PRIMARY t2 range idx idx 5 NULL 5 Using where; Using index; Using join buffer (flat, BNL join) explain select a from t1 join t2 on b between 1 and 2 and a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where -1 PRIMARY t2 ref idx idx 5 test.t1.a 1463 Using index; FirstMatch(t1) +1 PRIMARY t2 ref idx idx 5 test.t1.a 1462 Using index; FirstMatch(t1) 1 PRIMARY t2 range idx idx 5 NULL 5 Using where; Using index; Using join buffer (flat, BNL join) drop table t1,t2; set optimizer_switch= @tmp_mdev12675; diff --git a/mysql-test/main/subselect_sj2.result b/mysql-test/main/subselect_sj2.result index e5ed30c..6b86f5a 100644 --- a/mysql-test/main/subselect_sj2.result +++ b/mysql-test/main/subselect_sj2.result @@ -74,8 +74,11 @@ insert into t3 select a,a, a,a,a from t0; insert into t3 select a,a, a+100,a+100,a+100 from t0; analyze table t1,t2,t3; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK explain select * from t3 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra @@ -933,7 +936,9 @@ INSERT INTO t2 VALUES (15,'g',6),(16,'x',7),(17,'f',8); analyze table t1,t2; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain SELECT * FROM t1 WHERE b IN ( @@ -968,6 +973,7 @@ INSERT INTO t1 VALUES ('q','q'),('w','w'),('d','d'),('e','e'); ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; # This query returned 6 rows instead of 19 @@ -1004,6 +1010,7 @@ INSERT INTO t2 SELECT * FROM t1; INSERT INTO t2 SELECT * FROM t1; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK EXPLAIN SELECT * FROM t2 @@ -1253,8 +1260,11 @@ INSERT IGNORE INTO t2 (t2id, t1idref) VALUES (200011, 200001),(200012, 200001),( INSERT IGNORE INTO t3 VALUES (1, 200011, 1), (1, 200012, 2), (1, 200013, 3); ANALYZE TABLE t1,t2,t3; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK set @tmp7474= @@optimizer_search_depth; SET SESSION optimizer_search_depth = 1; diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result index b930bdc..c523525 100644 --- a/mysql-test/main/subselect_sj2_jcl6.result +++ b/mysql-test/main/subselect_sj2_jcl6.result @@ -85,8 +85,11 @@ insert into t3 select a,a, a,a,a from t0; insert into t3 select a,a, a+100,a+100,a+100 from t0; analyze table t1,t2,t3; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK explain select * from t3 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra @@ -949,7 +952,9 @@ INSERT INTO t2 VALUES (15,'g',6),(16,'x',7),(17,'f',8); analyze table t1,t2; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain SELECT * FROM t1 WHERE b IN ( @@ -984,6 +989,7 @@ INSERT INTO t1 VALUES ('q','q'),('w','w'),('d','d'),('e','e'); ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; # This query returned 6 rows instead of 19 @@ -1020,6 +1026,7 @@ INSERT INTO t2 SELECT * FROM t1; INSERT INTO t2 SELECT * FROM t1; ANALYZE TABLE t2; Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK EXPLAIN SELECT * FROM t2 @@ -1269,8 +1276,11 @@ INSERT IGNORE INTO t2 (t2id, t1idref) VALUES (200011, 200001),(200012, 200001),( INSERT IGNORE INTO t3 VALUES (1, 200011, 1), (1, 200012, 2), (1, 200013, 3); ANALYZE TABLE t1,t2,t3; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK set @tmp7474= @@optimizer_search_depth; SET SESSION optimizer_search_depth = 1; diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index ca6eaeb..e5a6f28 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -3119,13 +3119,13 @@ explain select a from t1, t2 where b between 1 and 2 and a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where -1 PRIMARY t2 ref idx idx 5 test.t1.a 1463 Using index; FirstMatch(t1) +1 PRIMARY t2 ref idx idx 5 test.t1.a 1462 Using index; FirstMatch(t1) 1 PRIMARY t2 range idx idx 5 NULL 5 Using where; Using index; Using join buffer (flat, BNL join) explain select a from t1 join t2 on b between 1 and 2 and a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where -1 PRIMARY t2 ref idx idx 5 test.t1.a 1463 Using index; FirstMatch(t1) +1 PRIMARY t2 ref idx idx 5 test.t1.a 1462 Using index; FirstMatch(t1) 1 PRIMARY t2 range idx idx 5 NULL 5 Using where; Using index; Using join buffer (flat, BNL join) drop table t1,t2; set optimizer_switch= @tmp_mdev12675; diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index ee45627..00f5acc 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -40,11 +40,17 @@ insert into t2i select * from t2; insert into t3i select * from t3; analyze table t1,t2,t3,t1i,t2i,t3i; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK +test.t1i analyze status Engine-independent statistics collected test.t1i analyze status Table is already up to date +test.t2i analyze status Engine-independent statistics collected test.t2i analyze status Table is already up to date +test.t3i analyze status Engine-independent statistics collected test.t3i analyze status Table is already up to date set @@optimizer_switch='materialization=on,in_to_exists=off,firstmatch=off'; /****************************************************************************** @@ -138,7 +144,7 @@ select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1i index it1i1,it1i2,it1i3 # # # 3 100.00 # 1 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # -2 MATERIALIZED t2i range it2i1,it2i3 # # # 3 100.00 # +2 MATERIALIZED t2i range it2i1,it2i3 # # # 5 100.00 # Warnings: Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` where `test`.`t2i`.`b1` > '0' group by `test`.`t2i`.`b1`) join `test`.`t1i` where `<subquery2>`.`b1` = `test`.`t1i`.`a1` and `<subquery2>`.`max(b2)` = `test`.`t1i`.`a2` select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); @@ -150,7 +156,7 @@ select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1i index it1i1,it1i2,it1i3 # # # 3 100.00 # 1 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # -2 MATERIALIZED t2i range it2i1,it2i3 # # # 3 100.00 # +2 MATERIALIZED t2i range it2i1,it2i3 # # # 5 100.00 # Warnings: Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where `test`.`t2i`.`b1` > '0' group by `test`.`t2i`.`b1`) join `test`.`t1i` where `<subquery2>`.`b1` = `test`.`t1i`.`a1` and `<subquery2>`.`min(b2)` = `test`.`t1i`.`a2` select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); @@ -164,6 +170,7 @@ insert into t2i_c select * from t2i; insert into t2i_c select * from t2i; analyze table t2i_c; Table Op Msg_type Msg_text +test.t2i_c analyze status Engine-independent statistics collected test.t2i_c analyze status OK show create table t2i_c; Table Create Table @@ -179,7 +186,7 @@ select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00 -2 MATERIALIZED t2i_c range NULL it2i3 9 NULL 3 100.00 Using index for group-by +2 MATERIALIZED t2i_c range NULL it2i3 9 NULL 4 100.00 Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (/* select#2 */ select `test`.`t2i_c`.`b1`,max(`test`.`t2i_c`.`b2`) from `test`.`t2i_c` group by `test`.`t2i_c`.`b1`) join `test`.`t1` where `<subquery2>`.`b1` = `test`.`t1`.`a1` and `<subquery2>`.`max(b2)` = `test`.`t1`.`a2` select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1); @@ -191,12 +198,12 @@ execute st1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 -2 MATERIALIZED t2i_c range NULL it2i3 9 NULL 3 Using index for group-by +2 MATERIALIZED t2i_c range NULL it2i3 9 NULL 4 Using index for group-by execute st1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 -2 MATERIALIZED t2i_c range NULL it2i3 9 NULL 3 Using index for group-by +2 MATERIALIZED t2i_c range NULL it2i3 9 NULL 4 Using index for group-by prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i_c group by b1)"; execute st2; a1 a2 @@ -212,7 +219,7 @@ select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' g id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00 -2 MATERIALIZED t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by +2 MATERIALIZED t2i range it2i1,it2i3 it2i3 9 NULL 5 100.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where `test`.`t2i`.`b1` > '0' group by `test`.`t2i`.`b1`) join `test`.`t1` where `<subquery2>`.`b1` = `test`.`t1`.`a1` and `<subquery2>`.`min(b2)` = `test`.`t1`.`a2` select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); @@ -328,7 +335,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 16 func,func 1 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where -3 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 2 100.00 Using index +3 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and `test`.`t2`.`b1` > '0' and `test`.`t3`.`c2` > '0' @@ -348,7 +355,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2i index it2i1,it2i2,it2i3 # # # 5 50.00 # 1 PRIMARY t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # 1 PRIMARY t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 # -1 PRIMARY t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 # +1 PRIMARY t2i ref it2i1,it2i2,it2i3 # # # 1 100.00 # Warnings: Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where `test`.`t1i`.`a1` = `test`.`t2i`.`b1` and `test`.`t3i`.`c1` = `test`.`t2i`.`b1` and `test`.`t2i`.`b1` = `test`.`t2i`.`b1` and `test`.`t1i`.`a2` = `test`.`t2i`.`b2` and `test`.`t3i`.`c2` = `test`.`t2i`.`b2` and `test`.`t2i`.`b2` = `test`.`t2i`.`b2` and `test`.`t2i`.`b1` > '0' and `test`.`t2i`.`b2` > '0' select * from t1i @@ -370,7 +377,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 5 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where -5 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 2 100.00 Using index +5 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where 4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where @@ -396,7 +403,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 5 MATERIALIZED t3c ALL NULL NULL NULL NULL 4 100.00 Using where -5 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3c.c1,test.t3c.c2 2 100.00 Using index +5 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3c.c1,test.t3c.c2 1 100.00 Using index 4 MATERIALIZED t3b ALL NULL NULL NULL NULL 4 100.00 Using where 3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: @@ -429,14 +436,14 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery5> eq_ref distinct_key # # # 1 100.00 # 1 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # 5 MATERIALIZED t3 ALL NULL # # # 4 100.00 # -5 MATERIALIZED t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 # +5 MATERIALIZED t2i ref it2i1,it2i2,it2i3 # # # 1 100.00 # 2 MATERIALIZED t2 ALL NULL # # # 5 100.00 # 4 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 3 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 7 UNION t2i index it2i1,it2i2,it2i3 # # # 5 50.00 # 7 UNION t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # 7 UNION t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 # -7 UNION t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 # +7 UNION t2i ref it2i1,it2i2,it2i3 # # # 1 100.00 # NULL UNION RESULT <union1,7> ALL NULL # # # NULL NULL # Warnings: Note 1003 (/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#3 */ select `test`.`t3`.`c2` from `test`.`t3` where `test`.`t3`.`c2` like '%02' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery3>`.`c2`)))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3`.`c2` from `test`.`t3` where `test`.`t3`.`c2` like '%03' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery4>`.`c2`))))) and `test`.`t3`.`c2` > '0') union (/* select#7 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i `.`a2` A S `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where `test`.`t1i`.`a1` = `test`.`t2i`.`b1` and `test`.`t3i`.`c1` = `test`.`t2i`.`b1` and `test`.`t2i`.`b1` = `test`.`t2i`.`b1` and `test`.`t1i`.`a2` = `test`.`t2i`.`b2` and `test`.`t3i`.`c2` = `test`.`t2i`.`b2` and `test`.`t2i`.`b2` = `test`.`t2i`.`b2` and `test`.`t2i`.`b1` > '0' and `test`.`t2i`.`b2` > '0') @@ -464,7 +471,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 16 func,func 1 100.00 4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where -4 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 2 100.00 Using index +4 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL @@ -488,7 +495,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 4 100.00 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) 4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where -4 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 2 100.00 Using index +4 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL @@ -531,7 +538,7 @@ b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t1.a1,test.t1.a2 2 100.00 Using index; Start temporary +1 PRIMARY t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t1.a1,test.t1.a2 1 100.00 Using index; Start temporary 1 PRIMARY t3c ALL NULL NULL NULL NULL 4 100.00 Using where; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 4 MATERIALIZED t3b ALL NULL NULL NULL NULL 4 100.00 Using where diff --git a/mysql-test/main/type_bit.result b/mysql-test/main/type_bit.result index 42b31be..c2db7ee 100644 --- a/mysql-test/main/type_bit.result +++ b/mysql-test/main/type_bit.result @@ -679,6 +679,7 @@ INSERT IGNORE INTO t1(a) VALUES (65535),(65525),(65535),(65535),(65535),(65535),(65535),(65535),(65535),(65535); ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT 1 FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result index 7ad5881..4d82e53 100644 --- a/mysql-test/main/union.result +++ b/mysql-test/main/union.result @@ -2187,7 +2187,7 @@ select id from t5 where name = (select name from t3 where id = t1.product_id)) l id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 func 1 Using where -3 SUBQUERY t2 ref PRIMARY PRIMARY 4 const 3 Using index +3 SUBQUERY t2 ref PRIMARY PRIMARY 4 const 4 Using index 4 UNION t2 ref PRIMARY PRIMARY 4 func 1 Using where; Using index 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 6 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 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 bc22fe1..aa52edc 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result @@ -1,63 +1,63 @@ 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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on set global optimizer_switch=10; set session optimizer_switch=5; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off +index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off +index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off set global optimizer_switch="index_merge_sort_union=on"; set session optimizer_switch="index_merge=off"; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off +optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off +optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off set session optimizer_switch="default"; select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off set optimizer_switch = replace(@@optimizer_switch, '=off', '=on'); Warnings: Warning 1681 'engine_condition_pushdown=on' is deprecated and will be removed in a future release select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on set global optimizer_switch=1.1; ERROR 42000: Incorrect argument type to variable 'optimizer_switch' set global optimizer_switch=1e1; @@ -69,4 +69,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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 219b550..4f1b898 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -2476,6 +2476,20 @@ NUMERIC_BLOCK_SIZE 4096 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME MAX_ROWID_FILTER_SIZE +SESSION_VALUE 131072 +GLOBAL_VALUE 131072 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 131072 +VARIABLE_SCOPE SESSION +VARIABLE_TYPE BIGINT UNSIGNED +VARIABLE_COMMENT The maximum number of rows that fit in memory +NUMERIC_MIN_VALUE 1024 +NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME MAX_SEEKS_FOR_KEY SESSION_VALUE 4294967295 GLOBAL_VALUE 4294967295 @@ -2953,17 +2967,17 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SWITCH -SESSION_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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on -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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on +SESSION_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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on +DEFAULT_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=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on VARIABLE_SCOPE SESSION VARIABLE_TYPE FLAGSET VARIABLE_COMMENT Fine-tune the optimizer behavior NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,default +ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,default READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_USE_CONDITION_SELECTIVITY