mirror of
https://github.com/MariaDB/server.git
synced 2025-09-11 20:10:22 +02:00
b3e70bde38This is done by no longer merging inner table dep with outer nested_join ones. This expands the search space of outer joins and introduces suboptimal join order choices already existing in inner joins (e.g. MDEV-36331, MDEV-37346) to outer joins, caused by heuristic pruning. To mitigate this issue, we add a switch that turns on heuristic pruning always (existing behaviour), and make is so that when the switch is off (default) joins with limited number of tables or limited search space* do not perform heuristic pruning. This improves join order optimization for these joins, where otherwise heuristic pruning may remove good joins - see added test cases in the test main.greedy_optimizer. *: about the same as 10 tables with default search depth (7) when optimizer_search_depth=0. If we completely disable heuristic pruning, or use the upper bound 10!, the test main.greedy_optimizer will grind to almost hanging for optimizer_search_depth=0 when more than 20 tables are involved. If we only compare the number of tables with 10, then it will prune when search space is low due to a low search depth when we can afford not to prune.
35 lines
1.9 KiB
Text
35 lines
1.9 KiB
Text
set default_storage_engine=Aria;
|
|
CREATE DATABASE dbt3_s001;
|
|
use dbt3_s001;
|
|
#
|
|
# MDEV-30325 Wrong result upon range query using index condition
|
|
#
|
|
SELECT COUNT(*) FROM lineitem force index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 );
|
|
COUNT(*)
|
|
5658
|
|
SELECT COUNT(*) FROM lineitem ignore index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 );
|
|
COUNT(*)
|
|
5658
|
|
#
|
|
# MDEV-30373 Wrong result with range access
|
|
#
|
|
explain SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 );
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE lineitem range PRIMARY,i_l_orderkey,i_l_orderkey_quantity PRIMARY 8 NULL 506 Using where; Using index
|
|
SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 );
|
|
COUNT(*)
|
|
293
|
|
#
|
|
# MDEV-30486 Table is not eliminated in bb-11.0
|
|
#
|
|
explain SELECT c_custkey, c_name AS currency2 FROM partsupp LEFT JOIN part ON ( p_partkey = ps_partkey ) JOIN supplier ON (s_suppkey = ps_suppkey) JOIN lineitem ON ( ps_suppkey = l_suppkey ) JOIN orders ON ( l_orderkey = o_orderkey ) JOIN customer ON ( o_custkey = c_custkey ) HAVING c_custkey > 150;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE supplier index PRIMARY PRIMARY 4 NULL 10 Using index
|
|
1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 5 dbt3_s001.supplier.s_suppkey 100
|
|
1 SIMPLE orders eq_ref PRIMARY,i_o_custkey PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where
|
|
1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1
|
|
1 SIMPLE partsupp ref i_ps_suppkey i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 16
|
|
DROP DATABASE dbt3_s001;
|
|
#
|
|
# End of 10.5 tests
|
|
#
|