Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23645

Optimizer trace: print conditions after substitute_for_best_equal_field

Details

    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.

      Attachments

        Activity

          psergei Sergei Petrunia added a comment - - edited

          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`))))",
          

          psergei Sergei Petrunia added a comment - - edited 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`))))",
          psergei Sergei Petrunia added a comment - - edited

          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.

          psergei Sergei Petrunia added a comment - - edited 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.

          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

          psergei Sergei Petrunia added a comment - 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

          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.

          psergei Sergei Petrunia added a comment - 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 .

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.