MDEV-27201 Do not compute unused aggregate functions for derived tables and views

[Olernov]

trafficstars

• [x] The Jira issue number for this PR is: MDEV-27201

Description

Disable the calculation of aggregate functions whose results are not used, neither inside the derived table SQL nor outside, in the outer select. For example, in the following case: SELECT sum_b FROM (SELECT a, sum(b) AS sum_b, max(c) AS max_c, min(d) AS min_d FROM t1 GROUP BY a HAVING max_c > 10 ) T T.min_d is not used anywhere, thus there is no need to calculate the aggregate function min(d). This applies to both VIEWs and derived tables.

This commit discards setting all bits of read_set for temporary tables to 1, which was done by default. That allows to determine which fields of a view/derived table are not used in the outer query. If some of those fields are aggregate functions (descendants from Item_sum) there is no need to calculate them (given, of course, that those fields are neither used inside the derived table/view - in HAVING or ORDER BY clauses).

To disable the calculation of unused aggregate functions they are removed from JOIN::sum_funcs list and marked as eliminated. So the commit does not change the item tree (items are left in place) but only disables their calculation. Marking items as eliminated allows to later eliminate whole derived tables/views in the corresponding optimization.

Release Notes

TODO: What should the release notes say about this change?

How can this PR be tested?

Necessary tests have been added

Basing the PR against the correct MariaDB version

• [x] This is a new feature or a refactoring, and the PR is based against the latest MariaDB development branch.
• [ ] This is a bug fix, and the PR is based against the earliest maintained branch in which the bug can be reproduced.

PR quality check

• [x] I checked the CODING_STANDARDS.md file and my PR conforms to this where appropriate.
• [x] For any trivial modifications to the PR, I am ok with the reviewer making the changes themselves.