[MDEV-23860] Move all optimization query re-writes from the prepare phase into the optimizer phase Created: 2020-10-01  Updated: 2023-11-09  Resolved: 2023-11-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Igor Babaev Assignee: Sergei Golubchik
Resolution: Duplicate Votes: 1
Labels: smart_engine

Issue Links:
Duplicate
duplicates MDEV-23450 Move subquery rewrites from JOIN::pre... Open
Relates
relates to MDEV-22366 Changes in optimizer to allow better ... Open
relates to MDEV-23791 Segregate some of optimizer rewrites ... Closed
relates to MDEV-23792 Add methods to disable some of optimi... Open

 Description   

Currently the function check_and_do_in_subquery_rewrite() is called at the prepare phase. Here's what the function does:

  /*
    If
      1) this join is inside a subquery (of any type except FROM-clause
         subquery) and
      2) we aren't just normalizing a VIEW
 
    Then perform early unconditional subquery transformations:
     - Convert subquery predicate into semi-join, or
     - Mark the subquery for execution using materialization, or
     - Perform IN->EXISTS transformation, or
     - Perform more/less ALL/ANY -> MIN/MAX rewriter
     - Substitute trivial scalar-context subquery with its value
 
*/

All the the rewrites above are actually an optimization re-writes. Most of them transform the query tree into a form that cannot be converted into a proper equivalent SQL query. This prevents usage of an external SQL parser after the prepare stage if any of such transformation has been performed.
From architectural point of view doing such re-writes in the middle of the prepare stage is also not a proper solution because some of the transformation may be rejected at the optimization stage.



 Comments   
Comment by Gregory Dorman (Inactive) [ 2020-10-02 ]

igor - 2 questions.
1. Is this affecting derived handler only or select handler as well? I am only asking 'just in case' because function name has "subquery" in it.

2. can this shave some meaningful amount of CPU from the roundtrip? I mean, is there a lot happening between the "correct place" and where it is now?

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