revision-id: 3e30071e2b5405623a4f24f2910e9efb341c3c0d (mariadb-10.5.4-436-g3e30071e2b5) parent(s): 7c400434aac9e968696e6d6ad1b990d0ef70045e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-25 21:41:48 +0300 message: MDEV-24666, Step #2: fix calculation of derived table cardinality Use JOIN::join_record_count. Do not try to compute a product of POSITION::records_read as that ignores the semi-join duplicate removal, condition selectivities, etc. --- mysql-test/main/opt_tvc.result | 4 ++-- sql/sql_select.cc | 12 +++--------- 2 files changed, 5 insertions(+), 11 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index 9752aa71bfb..190f7d33c95 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -444,7 +444,7 @@ where b in (3,5) group by b ) as dr_table; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 2 DERIVED <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 @@ -464,7 +464,7 @@ as tvc_0 group by b ) as dr_table; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 2 DERIVED <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5422346884d..7cd399fc76b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5541,7 +5541,6 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, DBUG_RETURN(TRUE); /* purecov: inspected */ { - double records= 1; SELECT_LEX_UNIT *unit= join->select_lex->master_unit(); /* Find an optimal join order of the non-constant tables. */ @@ -5568,14 +5567,9 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, if (!(join->select_options & SELECT_DESCRIBE) && unit->derived && unit->derived->is_materialized_derived()) { - /* - Calculate estimated number of rows for materialized derived - table/view. - */ - for (i= 0; i < join->table_count ; i++) - if (double rr= join->best_positions[i].records_read) - records= COST_MULT(records, rr); - ha_rows rows= records > (double) HA_ROWS_MAX ? HA_ROWS_MAX : (ha_rows) records; + ha_rows rows= (join->join_record_count > (double) HA_ROWS_MAX)? + HA_ROWS_MAX : + (ha_rows) join->join_record_count; set_if_smaller(rows, unit->lim.get_select_limit()); join->select_lex->increase_derived_records(rows); }