diff options
19 files changed, 138 insertions, 123 deletions
diff --git a/mysql-test/main/analyze_format_json.result b/mysql-test/main/analyze_format_json.result index b98500d4c34..c306c2fc1b3 100644 --- a/mysql-test/main/analyze_format_json.result +++ b/mysql-test/main/analyze_format_json.result @@ -143,7 +143,7 @@ ANALYZE "attached_condition": "tbl2.b < 60" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "1Kb", "join_type": "BNL", "r_filtered": 100 } @@ -181,7 +181,7 @@ ANALYZE "attached_condition": "tbl2.b < 60" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "1Kb", "join_type": "BNL", "attached_condition": "tbl1.c > tbl2.c", "r_filtered": 15.833 @@ -625,7 +625,7 @@ ANALYZE "attached_condition": "<in_optimizer>(t2.b,t2.b in (subquery#2))" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "65", "join_type": "BNL", "attached_condition": "<in_optimizer>(t2.b,t2.b in (subquery#2))", "r_filtered": null @@ -711,7 +711,7 @@ ANALYZE "attached_condition": "t3.f3 in (1,2)" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "1", "join_type": "BNL", "r_filtered": null }, @@ -742,7 +742,7 @@ ANALYZE "r_filtered": null }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "65", "join_type": "BNL", "attached_condition": "t2.f2 = t3.f3", "r_filtered": null diff --git a/mysql-test/main/analyze_stmt_orderby.result b/mysql-test/main/analyze_stmt_orderby.result index 3f6a4973d0a..23f4c39dd5b 100644 --- a/mysql-test/main/analyze_stmt_orderby.result +++ b/mysql-test/main/analyze_stmt_orderby.result @@ -426,7 +426,7 @@ ANALYZE "r_filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "65", "join_type": "BNL", "attached_condition": "t3.a = t0.a", "r_filtered": 10 @@ -497,7 +497,7 @@ ANALYZE "r_filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "119", "join_type": "BNL", "attached_condition": "t5.a = t6.a", "r_filtered": 21.429 @@ -537,7 +537,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "119", "join_type": "BNL", "attached_condition": "t5.a = t6.a" } diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index f2ae9929145..0b22da8f72a 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -1409,7 +1409,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "686", "join_type": "BNL", "attached_condition": "prev_gen.father = folks.`id` or prev_gen.mother = folks.`id`" } @@ -1636,16 +1636,16 @@ i div 4 - (i % 4) = ps.i div 4 - (ps.i % 4) ) SELECT regexp_replace(board,concat('(',REPEAT('.', 4),')'),'\\1\n') n_queens FROM solutions WHERE n_queens = 4; n_queens --*-- ----* -*--- ---*- - --*- *--- ---* -*-- +-*-- +---* +*--- +--*- + # # MDEV-10883: execution of prepared statement from SELECT # with recursive CTE that renames columns diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 52143f8e6c1..ea2ff69f792 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -136,7 +136,7 @@ EXPLAIN "attached_condition": "v1.max_c > 214" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "t2.a > v1.a", "materialized": { @@ -291,7 +291,7 @@ EXPLAIN "attached_condition": "v1.max_c > 400 or v1.max_c < 135" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.max_c > 400 and t2.a > v1.a or v1.max_c < 135 and t2.a < v1.a", "materialized": { @@ -359,7 +359,7 @@ EXPLAIN "attached_condition": "v1.max_c > 300 or v1.max_c < 135" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.b = t2.b and v1.max_c > 300 and v1.avg_c > t2.d or v1.a = t2.a and v1.max_c < 135 and v1.max_c < t2.c", "materialized": { @@ -416,7 +416,7 @@ EXPLAIN "attached_condition": "v1.a > 6" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "t2.b > v1.b", "materialized": { @@ -483,7 +483,7 @@ EXPLAIN "attached_condition": "v2.b > 25" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "t2.a < v2.a", "materialized": { @@ -571,7 +571,7 @@ EXPLAIN "attached_condition": "v1.a > 7 or v1.a < 2" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.a > 7 and t2.c < v1.max_c or v1.a < 2 and t2.b < v1.b", "materialized": { @@ -652,7 +652,7 @@ EXPLAIN "attached_condition": "v2.a > 7 or v2.a > 5" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v2.a > 7 and t2.c < v2.max_c or v2.a > 5 and t2.b < v2.b", "materialized": { @@ -719,7 +719,7 @@ EXPLAIN "attached_condition": "v1.a > 4 or v1.a < 2" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.a > 4 and v1.b > t2.b and v1.max_c = t2.d or v1.a < 2 and v1.max_c < t2.c and v1.max_c = t2.d", "materialized": { @@ -779,7 +779,7 @@ EXPLAIN "attached_condition": "v1.a < 2 and v1.max_c > 400" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "t2.b > v1.b", "materialized": { @@ -991,7 +991,7 @@ EXPLAIN "attached_condition": "v1.a > 7 and v1.max_c > 300 or v1.a < 4 and v1.max_c < 500" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.a > 7 and v1.max_c > 300 and t2.c < v1.max_c or v1.a < 4 and v1.max_c < 500 and t2.b < v1.b", "materialized": { @@ -1080,7 +1080,7 @@ EXPLAIN "attached_condition": "v1.a < 2 and v1.max_c > 120 or v1.a > 7" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.a < 2 and v1.max_c > 120 or v1.a > 7", "materialized": { @@ -1158,7 +1158,7 @@ EXPLAIN "attached_condition": "v1.a < 2 and v1.max_c > 120 or v1.a > 7" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.b = t2.b and v1.a < 2 and v1.max_c > 120 or v1.a > 7", "materialized": { @@ -1225,7 +1225,7 @@ EXPLAIN "attached_condition": "v1.a < 2 and v1.max_c < 200 or v1.a > 4" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.a < 2 and v1.max_c < 200 and t2.c > v1.max_c and v1.max_c = t2.d or v1.max_c = t2.c and v1.a > 4 and t2.c < 500 and t2.b < v1.b", "materialized": { @@ -1302,7 +1302,7 @@ EXPLAIN "attached_condition": "v1.max_c > 400 or v1.max_c < 135" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.max_c > 400 and t2.a > v1.a or v1.max_c < 135 and t2.a < v1.a", "materialized": { @@ -1345,7 +1345,7 @@ EXPLAIN "attached_condition": "v1.max_c > 400 or v1.max_c < 135" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.max_c > 400 and t2.a > v1.a or v1.max_c < 135 and t2.a < v1.a", "materialized": { @@ -1684,7 +1684,7 @@ EXPLAIN "attached_condition": "v1.a = 3 and v1.b = 3" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "materialized": { "query_block": { @@ -1734,7 +1734,7 @@ EXPLAIN "attached_condition": "v1.a = 1 and v1.b = 21" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "materialized": { "query_block": { @@ -1807,7 +1807,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "220", "join_type": "BNL", "attached_condition": "t.b = v.b or v.max_c > 20" } @@ -1931,7 +1931,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.a = t2.a and t2.a < 4 or v1.max_c = t2.c and t2.c > 150", "materialized": { @@ -2061,7 +2061,7 @@ EXPLAIN "attached_condition": "v1.a = 8 and v1.max_c = 404" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "materialized": { "query_block": { @@ -2359,7 +2359,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "162", "join_type": "BNL" } } @@ -2471,7 +2471,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "materialized": { "query_block": { @@ -2598,7 +2598,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.a = t2.a or v1.b = t2.b and (v1.a = 1 or v1.a = 6)", "materialized": { @@ -2693,7 +2693,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.a = 1 or v1.b = 21 or t2.a = 2", "materialized": { @@ -2761,7 +2761,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.a < t2.a or t2.a < 11", "materialized": { @@ -2943,7 +2943,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.b is not null", "materialized": { @@ -3038,7 +3038,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.a = t2.a or t2.c < 115", "materialized": { @@ -3067,7 +3067,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "incremental", - "buffer_size": "256Kb", + "buffer_size": "4Kb", "join_type": "BNL", "attached_condition": "v1.a = t2.a and v2.a = t2.a or v2.b > 13 and t2.c < 115", "materialized": { @@ -3138,7 +3138,7 @@ EXPLAIN "attached_condition": "v1.max_c < 300" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "materialized": { "query_block": { @@ -3167,7 +3167,7 @@ EXPLAIN "attached_condition": "v2.b < 50 or v2.b = 19" }, "buffer_type": "incremental", - "buffer_size": "256Kb", + "buffer_size": "4Kb", "join_type": "BNL", "attached_condition": "(v2.a = v1.a or v1.a = t2.a) and (v2.b < 50 or v2.b = 19)", "materialized": { @@ -3317,7 +3317,7 @@ EXPLAIN "attached_condition": "v1.a = 1 and v1.b > 10" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.b is not null", "materialized": { @@ -3435,7 +3435,7 @@ EXPLAIN "attached_condition": "t.a = 'b'" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "220", "join_type": "BNL" } } @@ -3544,7 +3544,7 @@ EXPLAIN "attached_condition": "v2.max_c > 300" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "715", "join_type": "BNL", "materialized": { "query_block": { @@ -3574,7 +3574,7 @@ EXPLAIN "attached_condition": "v1.max_c < 500" }, "buffer_type": "incremental", - "buffer_size": "256Kb", + "buffer_size": "9Kb", "join_type": "BNL", "attached_condition": "v1.a = v2.a or v1.a = t2.a", "materialized": { @@ -3790,7 +3790,7 @@ EXPLAIN "attached_condition": "v1.avg_c < 400 or v1.a > 1" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "(v1.avg_c < 400 or v1.a > 1) and v1.a is not null and v1.b is not null", "materialized": { @@ -3935,7 +3935,7 @@ EXPLAIN "attached_condition": "(v1.a = 1 or v1.max_c < 300) and v1.b > 25" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.a = 1 or v1.max_c < 300", "materialized": { @@ -4128,7 +4128,7 @@ EXPLAIN "attached_condition": "v1.max_c > 100 and v1.a > 7" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "materialized": { "query_block": { @@ -4229,7 +4229,7 @@ EXPLAIN "attached_condition": "v1.b = 19 and v1.a < 5" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "materialized": { "query_block": { @@ -4271,7 +4271,7 @@ EXPLAIN "attached_condition": "v1.max_c > 400 or v1.avg_c > 270" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "(v1.max_c > 400 or v1.avg_c > 270) and v1.a < t2.a", "materialized": { @@ -4383,7 +4383,7 @@ EXPLAIN "attached_condition": "v1.a = 1 or v1.a = 6" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "(v1.a = t2.a or v1.b = t2.b) and (v1.a = 1 or v1.a = 6)", "materialized": { @@ -4426,7 +4426,7 @@ EXPLAIN "attached_condition": "v1.a > 3 and v1.b > 27 or v1.max_c > 550" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.a > 3 and v1.b > 27 or v1.max_c > 550", "materialized": { @@ -4540,7 +4540,7 @@ EXPLAIN "attached_condition": "v1.a = 1 and (v1.max_c < 500 or v1.avg_c > 500)" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v1.max_c < 500 or v1.avg_c > 500", "materialized": { @@ -4584,7 +4584,7 @@ EXPLAIN "attached_condition": "v2.b > 10" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v2.a < t2.b or v2.max_c > 200", "materialized": { @@ -4706,7 +4706,7 @@ EXPLAIN "attached_condition": "v_union.a < 3 and v_union.c > 100" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "materialized": { "query_block": { @@ -4814,7 +4814,7 @@ EXPLAIN "attached_condition": "(v_union.a < 2 or v_union.c > 800) and v_union.b > 12" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "v_union.a < 2 or v_union.c > 800", "materialized": { @@ -4911,7 +4911,7 @@ EXPLAIN "attached_condition": "v_union.a = 1 and v_union.c < 200" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "materialized": { "query_block": { @@ -5078,7 +5078,7 @@ EXPLAIN "attached_condition": "v1.a = 1" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "materialized": { "query_block": { @@ -5108,7 +5108,7 @@ EXPLAIN "attached_condition": "v_union.a = 1" }, "buffer_type": "incremental", - "buffer_size": "256Kb", + "buffer_size": "4Kb", "join_type": "BNL", "attached_condition": "v_union.c > 800 or v1.max_c > 200", "materialized": { @@ -5923,7 +5923,7 @@ EXPLAIN "attached_condition": "v1.a > 5 and v1.b > 12" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "333", "join_type": "BNL", "materialized": { "query_block": { @@ -6306,7 +6306,7 @@ EXPLAIN "attached_condition": "v4.b > 10 and v4.a > 1 or v4.b < 20" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "238", "join_type": "BNL", "attached_condition": "(v4.b > 10 and v4.a > 1 or v4.b < 20) and v4.a is not null", "materialized": { @@ -6451,7 +6451,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "333", "join_type": "BNL", "materialized": { "query_block": { @@ -6971,7 +6971,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "715", "join_type": "BNL", "materialized": { "query_block": { @@ -7766,7 +7766,7 @@ EXPLAIN "attached_condition": "v2.pk > 2" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "119", "join_type": "BNL", "materialized": { "query_block": { @@ -8763,7 +8763,7 @@ EXPLAIN "attached_condition": "v2.i2 = 1" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "65", "join_type": "BNL", "materialized": { "query_block": { @@ -8879,7 +8879,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "163", "join_type": "BNL", "attached_condition": "t1.id2 = vc.id2" } @@ -8952,7 +8952,7 @@ EXPLAIN "attached_condition": "v1.a = 1 and v1.b = 1 and v1.max_c > 30" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "173", "join_type": "BNL", "materialized": { "query_block": { @@ -9027,7 +9027,7 @@ EXPLAIN "attached_condition": "v1.a = 1 and v1.b = 1 and v1.d = 1 and v1.max_c > 30" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "173", "join_type": "BNL", "materialized": { "query_block": { @@ -9758,7 +9758,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "65", "join_type": "BNL", "attached_condition": "t1.a = dt1.a" } @@ -9817,7 +9817,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "119", "join_type": "BNL", "attached_condition": "t1.a = dt.a" } @@ -10313,7 +10313,7 @@ EXPLAIN "attached_condition": "t1.f is not null" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "64", "join_type": "BNL" } } @@ -10488,7 +10488,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "65", "join_type": "BNL", "attached_condition": "t1.f2 = t.f2" } @@ -10647,7 +10647,7 @@ EXPLAIN "attached_condition": "v1.a = 8" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "173", "join_type": "BNL", "materialized": { "query_block": { @@ -10735,7 +10735,7 @@ EXPLAIN "attached_condition": "v1.a = 8" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "173", "join_type": "BNL", "materialized": { "query_block": { @@ -11225,7 +11225,7 @@ EXPLAIN "attached_condition": "v1.a > 3 or v1.a = 1 and v1.c < 110" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "173", "join_type": "BNL", "attached_condition": "v1.c = t2.c and v1.a > 3 and t2.c > 110 or v1.a = 1 and v1.c < 110", "materialized": { @@ -11566,7 +11566,7 @@ EXPLAIN "attached_condition": "v1.a = 6" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "173", "join_type": "BNL", "materialized": { "query_block": { @@ -11656,7 +11656,7 @@ EXPLAIN "attached_condition": "v1.a = 6" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "173", "join_type": "BNL", "materialized": { "query_block": { @@ -12150,7 +12150,7 @@ EXPLAIN "attached_condition": "v1.a > 1 or v1.a = 1 and v1.c > 500" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "173", "join_type": "BNL", "attached_condition": "v1.c = t2.c and v1.a > 1 and t2.c < 500 or v1.a = 1 and v1.c > 500", "materialized": { @@ -14010,7 +14010,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "173", "join_type": "BNL", "attached_condition": "t2.b = v1.b" } @@ -14106,7 +14106,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "173", "join_type": "BNL", "attached_condition": "t2.b = v1.b" } @@ -16413,7 +16413,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "65", "join_type": "BNL", "attached_condition": "t2.i = t1.i and t2.i = t1.i" } diff --git a/mysql-test/main/except.result b/mysql-test/main/except.result index b9959a3f520..9c5a3eaa93c 100644 --- a/mysql-test/main/except.result +++ b/mysql-test/main/except.result @@ -255,7 +255,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "119", "join_type": "BNL" } } @@ -278,7 +278,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "119", "join_type": "BNL" } } @@ -324,7 +324,7 @@ ANALYZE "r_filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "119", "join_type": "BNL", "r_filtered": 100 } @@ -358,7 +358,7 @@ ANALYZE "r_filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "119", "join_type": "BNL", "r_filtered": 100 } @@ -420,7 +420,7 @@ ANALYZE "r_filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "119", "join_type": "BNL", "r_filtered": 100 } @@ -454,7 +454,7 @@ ANALYZE "r_filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "119", "join_type": "BNL", "r_filtered": 100 } diff --git a/mysql-test/main/explain_json.result b/mysql-test/main/explain_json.result index 2f60a827cc0..b918e7614af 100644 --- a/mysql-test/main/explain_json.result +++ b/mysql-test/main/explain_json.result @@ -373,7 +373,7 @@ EXPLAIN "attached_condition": "tbl2.b < 5" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "1Kb", "join_type": "BNL", "attached_condition": "tbl2.a = tbl1.a" } @@ -640,7 +640,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "1Kb", "join_type": "BNL" } } @@ -674,7 +674,7 @@ EXPLAIN "first_match": "t2" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "141", "join_type": "BNL", "attached_condition": "t1.b = t2.b and t1.a = t2.a" } @@ -711,7 +711,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "206", "join_type": "BNL", "attached_condition": "t1.b = t2.b and t1.a = t2.a" } @@ -825,7 +825,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "65", "join_type": "BNL", "attached_condition": "t2.b <> outer_t1.a" } @@ -876,7 +876,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "1Kb", "join_type": "BNL", "attached_condition": "tbl2.b = tbl1.b" } @@ -1492,7 +1492,7 @@ EXPLAIN "index_condition_bka": "t4.b + 1 <= t3.b + 1" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "400", "join_type": "BKA", "mrr_type": "Rowid-ordered scan" } @@ -1535,7 +1535,7 @@ ANALYZE "index_condition_bka": "t4.b + 1 <= t3.b + 1" }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "400", "join_type": "BKA", "mrr_type": "Rowid-ordered scan", "r_filtered": 100 diff --git a/mysql-test/main/in_subq_cond_pushdown.result b/mysql-test/main/in_subq_cond_pushdown.result index 06248895bd3..522d97f5273 100644 --- a/mysql-test/main/in_subq_cond_pushdown.result +++ b/mysql-test/main/in_subq_cond_pushdown.result @@ -605,7 +605,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "119", "join_type": "BNL", "attached_condition": "t1.c = t3.y and t1.a is not null" }, @@ -1505,7 +1505,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "119", "join_type": "BNL", "attached_condition": "t1.a = t3.x and t1.c is not null" }, diff --git a/mysql-test/main/intersect.result b/mysql-test/main/intersect.result index 5c7e9dfc6f6..f2d36e1d0e9 100644 --- a/mysql-test/main/intersect.result +++ b/mysql-test/main/intersect.result @@ -316,7 +316,7 @@ EXPLAIN "filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "65", "join_type": "BNL" } } @@ -380,7 +380,7 @@ ANALYZE "r_filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "65", "join_type": "BNL", "r_filtered": 100 } @@ -459,7 +459,7 @@ ANALYZE "r_filtered": 100 }, "buffer_type": "flat", - "buffer_size": "256Kb", + "buffer_size": "65", "join_type": "BNL", "r_filtered": 100 } diff --git a/mysql-test/main/myisam_mrr,64bit.rdiff b/mysql-test/main/myisam_mrr,64bit.rdiff new file mode 100644 index 00000000000..82f6dfabb65 --- /dev/null +++ b/mysql-test/main/myisam_mrr,64bit.rdiff @@ -0,0 +1,13 @@ +--- main/myisam_mrr.result 2019-02-04 13:47:00.000000000 +0530 ++++ main/myisam_mrr,64bit.reject 2019-02-04 13:50:01.000000000 +0530 +@@ -617,8 +617,8 @@ + show status like 'handler_mrr%'; + Variable_name Value + Handler_mrr_init 1 +-Handler_mrr_key_refills 0 +-Handler_mrr_rowid_refills 0 ++Handler_mrr_key_refills 1 ++Handler_mrr_rowid_refills 1 + set join_buffer_size=10; + explain select sum(t1.b) from t0,t1 where t0.a=t1.a; + id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/main/myisam_mrr.test b/mysql-test/main/myisam_mrr.test index b15fdf60ce7..60d96c602ef 100644 --- a/mysql-test/main/myisam_mrr.test +++ b/mysql-test/main/myisam_mrr.test @@ -12,6 +12,7 @@ set @mrr_buffer_size_save= @@mrr_buffer_size; set mrr_buffer_size=79; -- source include/mrr_tests.inc +-- source include/word_size.inc set @@mrr_buffer_size= @mrr_buffer_size_save; diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index 5a6bb51b501..a547a978a01 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -1571,7 +1571,7 @@ old-style-user-limits FALSE optimizer-prune-level 1 optimizer-search-depth 62 optimizer-selectivity-sampling-limit 100 -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 +optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on optimizer-trace optimizer-trace-max-mem-size 1048576 optimizer-use-condition-selectivity 4 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 aa52edcc8fb..72438605d05 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result @@ -1,25 +1,25 @@ SET @start_global_value = @@global.optimizer_switch; SELECT @start_global_value; @start_global_value -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=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 +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on 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,rowid_filter=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=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 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,rowid_filter=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=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 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,rowid_filter=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=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 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,rowid_filter=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=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 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,rowid_filter=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=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 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,rowid_filter=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=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=10; set session optimizer_switch=5; select @@global.optimizer_switch; @@ -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,rowid_filter=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=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 diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index be5d9a9e670..483d38674ef 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -2743,10 +2743,10 @@ 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,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 +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=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 +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=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 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,rowid_filter=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=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 VARIABLE_SCOPE SESSION VARIABLE_TYPE FLAGSET VARIABLE_COMMENT Fine-tune the optimizer behavior 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 6cd6c674cbf..cd119f98438 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -2967,10 +2967,10 @@ 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,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 +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=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 +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=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 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,rowid_filter=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=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 VARIABLE_SCOPE SESSION VARIABLE_TYPE FLAGSET VARIABLE_COMMENT Fine-tune the optimizer behavior diff --git a/sql/sql_priv.h b/sql/sql_priv.h index dab7a3161be..13ecbe9b1f1 100644 --- a/sql/sql_priv.h +++ b/sql/sql_priv.h @@ -263,7 +263,8 @@ OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED | \ OPTIMIZER_SWITCH_SPLIT_MATERIALIZED | \ OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_SUBQUERY |\ - OPTIMIZER_SWITCH_USE_ROWID_FILTER) + OPTIMIZER_SWITCH_USE_ROWID_FILTER |\ + OPTIMIZER_SWITCH_OPTIMIZE_JOIN_BUFFER_SIZE) /* Replication uses 8 bytes to store SQL_MODE in the binary log. The day you diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result index 55a2cc4b34b..17270ac5115 100644 --- a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result +++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result @@ -1,7 +1,7 @@ drop table if exists t; 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=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 +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on create table t (id int not null, x int not null, y int not null, primary key(id), key(x)) engine=innodb; insert into t values (0,0,0),(1,1,1),(2,2,2),(3,2,3),(4,2,4); explain select x,id from t force index (x) where x=0 and id=0; diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result index 1b5998b1534..0ea5a45af20 100644 --- a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result +++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result @@ -1,7 +1,7 @@ drop table if exists t; 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=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 +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on create table t (id int not null, x int not null, y int not null, primary key(id), key(x)) engine=tokudb; insert into t values (0,0,0),(1,1,1),(2,2,2),(3,2,3),(4,2,4); explain select x,id from t force index (x) where x=0 and id=0; diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result index 0bba5b7c47f..58bc4bcb31a 100644 --- a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result +++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result @@ -1,7 +1,7 @@ drop table if exists t; 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=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 +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on create table t (a int not null, b int not null, c int not null, d int not null, primary key(a,b), key(c,a)) engine=innodb; insert into t values (0,0,0,0),(0,1,0,1); explain select c,a,b from t where c=0 and a=0 and b=1; diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result index 556c5ffd897..dbcbe39cb47 100644 --- a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result +++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result @@ -1,7 +1,7 @@ drop table if exists t; 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=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 +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on create table t (a int not null, b int not null, c int not null, d int not null, primary key(a,b), key(c,a)) engine=tokudb; insert into t values (0,0,0,0),(0,1,0,1); explain select c,a,b from t where c=0 and a=0 and b=1; |