revision-id: cff60be7fe159fdcb2517ce8441610ad512aa7d0 (mariadb-10.3.6-15-gcff60be) parent(s): bb5f4967f54d3f458bec86fb1845405a0a88bc0f author: Igor Babaev committer: Igor Babaev timestamp: 2018-04-17 23:39:40 -0700 message: MDEV-15899 Server crashes in st_join_table::is_inner_table_of_outer_join The crash happened because JOIN::check_for_splittable_materialized() called by mistake the function JOIN_TAB::is_inner_table_of_outer_join() instead of the function TABLE_LIST::is_inner_table_of_outer_join(). The former cannot be called before the call of make_outerjoin_info(). --- mysql-test/main/derived_cond_pushdown.result | 46 ++++++++++++++++++++++++++++ mysql-test/main/derived_cond_pushdown.test | 19 ++++++++++++ sql/opt_split.cc | 5 +-- 3 files changed, 68 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 6887ad1..1b7aeda 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -15119,3 +15119,49 @@ Warnings: Note 1003 /* select#1 */ select NULL AS `f`,`v2`.`f` AS `f` from `test`.`t1` `a` straight_join `test`.`t1` `b` join `test`.`v2` where 0 DROP VIEW v1,v2; DROP TABLE t1; +# +# MDEV-15899: derived with WF without any key access +# +create table t1 (f1 int, f2 int, f4 int); +insert into t1 values +(3,1,1), (3,0,9), (0,1,8), (9,0,0), (3,0,9); +with +cte as (select median(f2) over (partition by f1) as k1 from t1 order by f1), +cte1 as (select median(f4) over (partition by f1) as k2 from t1) +select k1,k2 from cte1, cte; +k1 k2 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +1.0000000000 8.0000000000 +1.0000000000 8.0000000000 +1.0000000000 8.0000000000 +1.0000000000 8.0000000000 +1.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +0.0000000000 8.0000000000 +explain with +cte as (select median(f2) over (partition by f1) as k1 from t1 order by f1), +cte1 as (select median(f4) over (partition by f1) as k2 from t1) +select k1,k2 from cte1, cte; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) +3 DERIVED t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +2 DERIVED t1 ALL NULL NULL NULL NULL 5 Using temporary +drop table t1; diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 234f889..2e2ec69 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2671,3 +2671,22 @@ SELECT * FROM v1 JOIN v2 ON v1.f = v2.f; DROP VIEW v1,v2; DROP TABLE t1; + +--echo # +--echo # MDEV-15899: derived with WF without any key access +--echo # + +create table t1 (f1 int, f2 int, f4 int); +insert into t1 values + (3,1,1), (3,0,9), (0,1,8), (9,0,0), (3,0,9); + +let $q= +with +cte as (select median(f2) over (partition by f1) as k1 from t1 order by f1), +cte1 as (select median(f4) over (partition by f1) as k2 from t1) +select k1,k2 from cte1, cte; + +eval $q; +eval explain $q; + +drop table t1; diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 063ca9f..b81556e 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -352,8 +352,9 @@ bool JOIN::check_for_splittable_materialized() Field *ord_field= ((Item_field *) (ord_item->real_item()))->field; - JOIN_TAB *tab= ord_field->table->reginfo.join_tab; - if (tab->is_inner_table_of_outer_join()) + /* Ignore fields from of inner tables of outer joins */ + TABLE_LIST *tbl= ord_field->table->pos_in_table_list; + if (tbl->is_inner_table_of_outer_join()) continue; List_iterator<Item> li(fields_list);