WIP: MDEV-34041 Display additional information for materialized subqueries…

[Olernov]

… in EXPLAIN/ANALYZE FORMAT=JSON

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

Description

This is a WIP (work-in-progress) PR, that's why the test suite is not updated. The ANALYZE output format is open for discussion, so it makes sense to update existing tests after the output format is approved

Release Notes

TODO: What should the release notes say about this change? Include any changed system variables, status variables or behaviour. Optionally list any https://mariadb.com/kb/ pages that need changing.

How can this PR be tested?

TODO: modify the automated test suite to verify that the PR causes MariaDB to behave as intended. Consult the documentation on "Writing good test cases".

If the changes are not amenable to automated testing, please explain why not and carefully describe how to test manually.

Basing the PR against the correct MariaDB version

• [ ] This is a new feature and the PR is based against the latest MariaDB development branch.
• [x] 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.

[Olernov]

I think it also makes sense to introduce r_loops in subquery_materialization (to be materialization)

I don't understand what should be counted/tracked under this counter. Every lookup in the materialized subquery?

[spetrunia]

I think it also makes sense to introduce r_loops in subquery_materialization (to be materialization)

I don't understand what should be counted/tracked under this counter. Every lookup in the materialized subquery?

Yes. Sometimes, it is possible to infer this number, but in general case it is not available anywhere else. So it makes a lot of sense to print it.

[spetrunia]

Also, note that for non-NULL-aware materialization, it reports r_loops==r_index_lookup_loops.

    "materialization": {
      "r_strategy": "index_lookup",
      "r_loops": 10,
      "r_index_lookup_loops": 10,
      "query_block": {

[spetrunia]

@Olernov , ok I think there are enough comments to get the next version of the patch.

[Olernov]

Also, note that for non-NULL-aware materialization, it reports r_loops==r_index_lookup_loops.

    "materialization": {
      "r_strategy": "index_lookup",
      "r_loops": 10,
      "r_index_lookup_loops": 10,
      "query_block": {

Do you mean for the index_lookup strategy? Yes, and it seems reasonable. But numbers differ for partial match strategies. Or you suggest removing redundant output for index_lookup strategy?

[spetrunia]

Do you mean for the index_lookup strategy? Yes, and it seems reasonable. But numbers differ for partial match strategies. Or you suggest removing redundant output for index_lookup strategy?

Let's keep this for now and look at this in the next step.

[Olernov]

Also, I do not see any testcase with r_table_scan_loops. Please add one. (or remove r_table_scan_loops if it's dead code, that's what I suspect).

I agree, removed the counter

[Olernov]

Do you mean for the index_lookup strategy? Yes, and it seems reasonable. But numbers differ for partial match strategies. Or you suggest removing redundant output for index_lookup strategy?

Let's keep this for now and look at this in the next step.

I decided to remove r_index_lookup_loops from index_lookup strategy because it is, indeed, redundant and equals to r_loops.

[spetrunia]

The result looks good. Please apply this code cleanup patch: https://gist.github.com/spetrunia/a09ad18f970d4ebe146e8cea5ed28bbd and update the test results.

[Olernov]

@spetrunia, OK. I applied the patch and updated the tests.