[MDEV-30486] Table is not eliminated in bb-11.0 Created: 2023-01-27  Updated: 2023-02-10  Resolved: 2023-01-31

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 11.0.0
Fix Version/s: 11.0.0

Type: Bug Priority: Major
Reporter: Michael Widenius Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-26974 Improve selectivity and related costs... Closed

 Description   

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 lineite
m ON ( ps_suppkey = l_suppkey ) JOIN orders ON ( l_orderkey = o_orderkey ) JOIN
customer ON ( o_custkey = c_custkey ) HAVING c_custkey > 150;
In 11.0 shows the table 'part' which should have been eliminated.
This also causes a small degradation in performance.

 Comments   
Comment by Michael Widenius [ 2023-01-31 ]

Some tables where not eliminated when they could have been.
This was caused because HA_KEYREAD_ONLY is not set anymore for InnoDB
clustered index and the elimination code was depending on
field->part_of_key_not_clustered which was not set if HA_KEYREAD_ONLY
is not present.

Fixed by moving out field->part_of_key and
field->part_of_key_not_clustered from under HA_KEYREAD_ONLY (which
they should never have been part of).

The fix exposes issues in extended key code, which took a long time to understand and fix.
The exposed bugs where fixed in another commit

Generated at Thu Feb 08 10:16:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.