[MDEV-23645] Optimizer trace: print conditions after substitute_for_best_equal_field Created: 2020-09-01  Updated: 2021-03-23  Resolved: 2021-03-19

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

Type: Task Priority: Critical
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: optimizer_trace


 Description   

Find this code in sql_select.cc:

  if (conds)
 
  {
 
    conds= substitute_for_best_equal_field(thd, NO_PARTICULAR_TAB, conds,


    having= substitute_for_best_equal_field(thd, NO_PARTICULAR_TAB, having,
 
                                            having_equal, map2table, false);

and subsequent calls for ON and select list.
Print the conditions after the substitution.

 Comments   
Comment by Sergei Petrunia [ 2020-10-10 ]

MySQL prints the substituted WHERE in attaching_conditions_to_tables.original_condition:

select * 
from t1, t2, t3 
where 
 t1.a=t2.a and t2.a=t3.a and 
 11+ t1.a  < (22 + t2.a) *(33 + t3.a) ;


    {
 
      "join_optimization": {
 
        "select#": 1,
 
        "steps": [
 
          {
 
            "condition_processing": {
 
              "condition": "WHERE",
 
              "original_condition": "((`t1`.`a` = `t2`.`a`) and (`t2`.`a` = `t3`.`a`) and ((11 + `t1`.`a`) < ((22 + `t2`.`a`) * (33 + `t3`.`a`))))",
 
              "steps": [
 
                {
 
                  "transformation": "equality_propagation",
 
                  "resulting_condition": "(((11 + `t1`.`a`) < ((22 + `t2`.`a`) * (33 + `t3`.`a`))) and multiple equal(`t1`.`a`, `t2`.`a`, `t3`.`a`))"
 
                },
 
                {
 
                  "transformation": "constant_propagation",
 
                  "resulting_condition": "(((11 + `t1`.`a`) < ((22 + `t2`.`a`) * (33 + `t3`.`a`))) and multiple equal(`t1`.`a`, `t2`.`a`, `t3`.`a`))"
 
                },
 
                {
 
                  "transformation": "trivial_condition_removal",
 
                  "resulting_condition": "(((11 + `t1`.`a`) < ((22 + `t2`.`a`) * (33 + `t3`.`a`))) and multiple equal(`t1`.`a`, `t2`.`a`, `t3`.`a`))"
 
                }
 
              ]
 
            }
 
          },

...

          {
 
            "attaching_conditions_to_tables": {
 
              "original_condition": "((`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`)
 
   and ((11 + `t1`.`a`) < ((22 + `t1`.`a`) * (33 + `t1`.`a`))))",

Comment by Sergei Petrunia [ 2020-10-10 ]

More input: don't print anything if there's no HAVING, ON, WHERE, etc.
Things like

+                  "having_clause_after_substitution": null,
 
+                  "on_clause_after_substitution": []

only add clutter.

Comment by Sergei Petrunia [ 2020-10-10 ]

Hmm MariaDB also has post-substitution WHERE clause hidden in attaching_conditions_to_tables.original_condition :

                {
 
                  "transformation": "trivial_condition_removal",
 
                  "resulting_condition": "11 + t1.a < (22 + t2.a) * (33 + t3.a) and multiple equal(t1.a, t2.a, t3.a)"
 
                }


          {
 
            "where_clause_after_substitution": "t2.a = t1.a and t3.a = t1.a and 11 + t1.a < (22 + t1.a) * (33 + t1.a)",
 
            "having_clause_after_substitution": null,
 
            "on_clause_after_substitution": []
 
          },
 
          {
 
            "attaching_conditions_to_tables": {
 
              "original_condition": "t2.a = t1.a and t3.a = t1.a and 11 + t1.a < (22 + t1.a) * (33 + t1.a)",

Note: the original one is "33+t3.a" , if we see "33+t1.a" it's post-substitution

Comment by Sergei Petrunia [ 2020-10-10 ]

I think, tracing for this should follow the pattern that other steps in "join_optimization" do.

{
 
  "substitute_for_best_equal_item": {
 
     "resulting_where": "<condition>",
 
     "resulting_having": "condition",
 
  }
 
}

Let the object be always printed.
Conditions that are NULL should not be.

Please also remove attaching_conditions_to_tables.original_condition.

Let's discuss ON expression print-out in MDEV-23646.

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