revision-id: 4c306c51be6fc6554e558783175917551705a1e6 (mariadb-10.3.10-36-g4c306c51be6) parent(s): f8148e77b0c956fa739b71e688340cd12ab5ebbd author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2018-10-23 11:23:34 +0200 message: MDEV-17493: Partition pruning doesn't work for nested outer joins Reuse the fix for MDEV-17518 here, too. --- mysql-test/main/partition_pruning.result | 33 ++++++++++ mysql-test/main/partition_pruning.test | 31 +++++++++ sql/sql_select.cc | 109 ++++++++++++++----------------- sql/sql_select.h | 8 ++- 4 files changed, 120 insertions(+), 61 deletions(-) diff --git a/mysql-test/main/partition_pruning.result b/mysql-test/main/partition_pruning.result index 422132dd1c3..00bd24101d6 100644 --- a/mysql-test/main/partition_pruning.result +++ b/mysql-test/main/partition_pruning.result @@ -3484,3 +3484,36 @@ select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) O a b c d 1 a b 1 drop table t1; +# +# MDEV-17493: Partition pruning doesn't work for nested outer joins +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int); +insert into t1 select a,a,a from t0; +create table t2 (a int, b int, c int); +insert into t2 select a,a,a from t0; +create table t3 ( +part_id int, +a int +) partition by list (part_id) ( +partition p0 values in (0), +partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3), +partition p4 values in (4) +); +insert into t3 select mod(a,5), a from t0; +explain partitions +select * from t1 left join t3 on (t1.a=t3.a and t3.part_id=3); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 +1 SIMPLE t3 p3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +# The following should have partitions="p3", NOT p0,p1,p2,p3,p4: +explain partitions +select * from t1 left join (t3 join t2) on (t1.a=t3.a and t3.a=t2.b and t3.part_id=3); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 10 +1 SIMPLE t3 p3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 10 Using where; Using join buffer (incremental, BNL join) +drop table t0,t1,t2,t3; diff --git a/mysql-test/main/partition_pruning.test b/mysql-test/main/partition_pruning.test index 9d72e9c0d01..2879b0eae6c 100644 --- a/mysql-test/main/partition_pruning.test +++ b/mysql-test/main/partition_pruning.test @@ -1535,4 +1535,35 @@ select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) O (a = 1 AND b >= 'a' AND (c = 'c' OR (c = 'd' AND d = 2)))); drop table t1; +--echo # +--echo # MDEV-17493: Partition pruning doesn't work for nested outer joins +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int); +insert into t1 select a,a,a from t0; +create table t2 (a int, b int, c int); +insert into t2 select a,a,a from t0; + +create table t3 ( + part_id int, + a int +) partition by list (part_id) ( + partition p0 values in (0), + partition p1 values in (1), + partition p2 values in (2), + partition p3 values in (3), + partition p4 values in (4) +); +insert into t3 select mod(a,5), a from t0; + +explain partitions +select * from t1 left join t3 on (t1.a=t3.a and t3.part_id=3); + +--echo # The following should have partitions="p3", NOT p0,p1,p2,p3,p4: +explain partitions +select * from t1 left join (t3 join t2) on (t1.a=t3.a and t3.a=t2.b and t3.part_id=3); + +drop table t0,t1,t2,t3; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 91d9326854b..08e9b8daf73 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -292,6 +292,9 @@ static bool find_order_in_list(THD *, Ref_ptr_array, TABLE_LIST *, ORDER *, static double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, table_map rem_tables); void set_postjoin_aggr_write_func(JOIN_TAB *tab); + +static Item **get_sargable_cond(JOIN *join, TABLE *table); + #ifndef DBUG_OFF /* @@ -1770,19 +1773,9 @@ JOIN::optimize_inner() List_iterator_fast<TABLE_LIST> li(select_lex->leaf_tables); while ((tbl= li++)) { - /* - If tbl->embedding!=NULL that means that this table is in the inner - part of the nested outer join, and we can't do partition pruning - (TODO: check if this limitation can be lifted) - */ - if (!tbl->embedding || - (tbl->embedding && tbl->embedding->sj_on_expr)) - { - Item *prune_cond= tbl->on_expr? tbl->on_expr : conds; - tbl->table->all_partitions_pruned_away= prune_partitions(thd, - tbl->table, - prune_cond); - } + Item **prune_cond= get_sargable_cond(this, tbl->table); + tbl->table->all_partitions_pruned_away= + prune_partitions(thd, tbl->table, *prune_cond); } } #endif @@ -4334,6 +4327,47 @@ void mark_join_nest_as_const(JOIN *join, } } + +/* + @brief Get the condition that can be used to do range analysis/partition + pruning/etc + + @detail + Figure out which condition we can use: + - For INNER JOIN, we use the WHERE, + - "t1 LEFT JOIN t2 ON ..." uses t2's ON expression + - "t1 LEFT JOIN (...) ON ..." uses the join nest's ON expression. +*/ + +static Item **get_sargable_cond(JOIN *join, TABLE *table) +{ + Item **retval; + if (table->pos_in_table_list->on_expr) + { + /* + This is an inner table from a single-table LEFT JOIN, "t1 LEFT JOIN + t2 ON cond". Use the condition cond. + */ + retval= &table->pos_in_table_list->on_expr; + } + else if (table->pos_in_table_list->embedding && + !table->pos_in_table_list->embedding->sj_on_expr) + { + /* + This is the inner side of a multi-table outer join. Use the + appropriate ON expression. + */ + retval= &(table->pos_in_table_list->embedding->on_expr); + } + else + { + /* The table is not inner wrt some LEFT JOIN. Use the WHERE clause */ + retval= &join->conds; + } + return retval; +} + + /** Calculate the best possible join and initialize the join structure. @@ -4919,42 +4953,11 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, SQL_SELECT *select= 0; if (!s->const_keys.is_clear_all()) { - Item *sargable_cond; - int cond_source; - /* - Figure out which condition we should use for range analysis. For - INNER JOIN, we use the WHERE, for inner side of LEFT JOIN we should - use the ON expression. - */ - if (*s->on_expr_ref) - { - /* - This is an inner table from a single-table LEFT JOIN, "t1 LEFT JOIN - t2 ON cond". Use the condition cond. - */ - cond_source= 0; - sargable_cond= *s->on_expr_ref; - } - else if (s->table->pos_in_table_list->embedding && - !s->table->pos_in_table_list->embedding->sj_on_expr) - { - /* - This is the inner side of a multi-table outer join. Use the - appropriate ON expression. - */ - cond_source= 1; - sargable_cond= s->table->pos_in_table_list->embedding->on_expr; - } - else - { - /* The table is not inner wrt some LEFT JOIN. Use the WHERE clause */ - cond_source= 2; - sargable_cond= join->conds; - } + Item **sargable_cond= get_sargable_cond(join, s->table); select= make_select(s->table, found_const_table_map, found_const_table_map, - sargable_cond, + *sargable_cond, (SORT_INFO*) 0, 1, &error); if (!select) @@ -4966,19 +4969,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, Range analyzer might have modified the condition. Put it the new condition to where we got it from. */ - switch (cond_source) { - case 0: - *s->on_expr_ref= select->cond; - break; - case 1: - s->table->pos_in_table_list->embedding->on_expr= select->cond; - break; - case 2: - join->conds= select->cond; - break; - default: - DBUG_ASSERT(0); - } + *sargable_cond= select->cond; s->quick=select->quick; s->needed_reg=select->needed_reg; diff --git a/sql/sql_select.h b/sql/sql_select.h index 2cc47f6ec3b..0e486c1fbec 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -263,8 +263,12 @@ typedef struct st_join_table { /* Pointer to the associated ON expression. on_expr_ref=!NULL except for degenerate joins. - *on_expr_ref!=NULL for tables that are first inner tables within an outer - join. + + Optimization phase: *on_expr_ref!=NULL for tables that are the single + tables on the inner side of the outer join (t1 LEFT JOIN t2 ON...) + + Execution phase: *on_expr_ref!=NULL for tables that are first inner tables + within an outer join (which may have multiple tables) */ Item **on_expr_ref; COND_EQUAL *cond_equal; /**< multiple equalities for the on expression */