List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:October 18 2011 2:28pm
Subject:bzr push into mysql-trunk branch (jorgen.loland:3508 to 3509) WL#5953
View as plain text  
 3509 Jorgen Loland	2011-10-18
      WL#5953: Optimize away useless subquery clauses
      
      Follow-up patch: Show the transformations in optimizer trace

    modified:
      mysql-test/suite/opt_trace/include/subquery.inc
      mysql-test/suite/opt_trace/r/subquery_no_prot.result
      mysql-test/suite/opt_trace/r/subquery_ps_prot.result
      sql/sql_select.cc
 3508 Tor Didriksen	2011-10-18
      Bug#12582849 ASSERTION FAILURE IN __CXA_PURE_VIRTUAL/ITEM_COND::FIX_FIELDS
      
      This is a variant of earlier bugs: 12603457 and 12603141:
      Sometimes Item_field objects are wrapped in Item_ref objects.
      These must be stripped off when saving conditions in in 'prep_where'.
     @ mysql-test/r/ps.result
        New test case.
     @ mysql-test/t/ps.test
        New test case.
     @ sql/sql_lex.cc
        Fix garbled comment.

    modified:
      mysql-test/r/ps.result
      mysql-test/t/ps.test
      sql/item_cmpfunc.cc
      sql/sql_lex.cc
=== modified file 'mysql-test/suite/opt_trace/include/subquery.inc'
--- a/mysql-test/suite/opt_trace/include/subquery.inc	2011-08-31 16:48:23 +0000
+++ b/mysql-test/suite/opt_trace/include/subquery.inc	2011-10-18 14:27:48 +0000
@@ -48,6 +48,14 @@ WHERE NOT EXISTS
 SELECT * FROM information_schema.OPTIMIZER_TRACE;
 --echo
 
+# Distinct, order and group is removed from subquery. Note: For PS,
+# removal happens during prepare so the only visible effect is that
+# the subquery does not contain those clauses.
+SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t1 GROUP BY a ORDER BY b);
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
 DROP TABLE t1,t2;
 
 --echo #

=== modified file 'mysql-test/suite/opt_trace/r/subquery_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_no_prot.result	2011-10-18 10:23:09 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_no_prot.result	2011-10-18 14:27:48 +0000
@@ -669,6 +669,131 @@ WHERE NOT EXISTS
   ] /* steps */
 }	0	0
 
+SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t1 GROUP BY a ORDER BY b);
+1
+1
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
+SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t1 GROUP BY a ORDER BY b)	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "join_preparation": {
+              "select#": 2,
+              "steps": [
+                {
+                  "transformations_to_subquery": [
+                    "removed_ordering",
+                    "removed_distinct",
+                    "removed_grouping"
+                  ] /* transformations_to_subquery */
+                },
+                {
+                  "expanded_query": "/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`"
+                }
+              ] /* steps */
+            } /* join_preparation */
+          },
+          {
+            "expanded_query": "/* select#1 */ select 1 AS `1` from DUAL  where (not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))",
+              "steps": [
+                {
+                  "transformation": "equality_propagation",
+                  "subselect_evaluation": [
+                  ] /* subselect_evaluation */,
+                  "resulting_condition": "(not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))"
+                },
+                {
+                  "transformation": "constant_propagation",
+                  "subselect_evaluation": [
+                  ] /* subselect_evaluation */,
+                  "resulting_condition": "(not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))"
+                },
+                {
+                  "transformation": "trivial_condition_removal",
+                  "subselect_evaluation": [
+                    {
+                      "subselect_execution": {
+                        "select#": 2,
+                        "steps": [
+                          {
+                            "join_optimization": {
+                              "select#": 2,
+                              "steps": [
+                                {
+                                  "table_dependencies": [
+                                    {
+                                      "database": "test",
+                                      "table": "t1",
+                                      "row_may_be_null": false,
+                                      "map_bit": 0,
+                                      "depends_on_map_bits": [
+                                      ] /* depends_on_map_bits */
+                                    }
+                                  ] /* table_dependencies */
+                                },
+                                {
+                                  "rows_estimation": [
+                                    {
+                                      "database": "test",
+                                      "table": "t1",
+                                      "rows": 1,
+                                      "cost": 1,
+                                      "table_type": "system"
+                                    }
+                                  ] /* rows_estimation */
+                                }
+                              ] /* steps */,
+                              "empty_result": {
+                                "cause": "no matching row in const table"
+                              } /* empty_result */
+                            } /* join_optimization */
+                          },
+                          {
+                            "join_execution": {
+                              "select#": 2,
+                              "steps": [
+                              ] /* steps */
+                            } /* join_execution */
+                          }
+                        ] /* steps */
+                      } /* subselect_execution */
+                    }
+                  ] /* subselect_evaluation */,
+                  "resulting_condition": null
+                }
+              ] /* steps */
+            } /* condition_processing */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+
 DROP TABLE t1,t2;
 #
 # BUG#12905521 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON SELECT
@@ -825,6 +950,11 @@ field4,field5,field6	{
                     "select#": 3,
                     "steps": [
                       {
+                        "transformations_to_subquery": [
+                          "removed_distinct"
+                        ] /* transformations_to_subquery */
+                      },
+                      {
                         "expanded_query": "/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)"
                       }
                     ] /* steps */

=== modified file 'mysql-test/suite/opt_trace/r/subquery_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_ps_prot.result	2011-10-18 10:23:09 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_ps_prot.result	2011-10-18 14:27:48 +0000
@@ -669,6 +669,124 @@ WHERE NOT EXISTS
   ] /* steps */
 }	0	0
 
+SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t1 GROUP BY a ORDER BY b);
+1
+1
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
+SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t1 GROUP BY a ORDER BY b)	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "join_preparation": {
+              "select#": 2,
+              "steps": [
+                {
+                  "expanded_query": "/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`"
+                }
+              ] /* steps */
+            } /* join_preparation */
+          },
+          {
+            "expanded_query": "/* select#1 */ select 1 AS `1` from DUAL  where (not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))",
+              "steps": [
+                {
+                  "transformation": "equality_propagation",
+                  "subselect_evaluation": [
+                  ] /* subselect_evaluation */,
+                  "resulting_condition": "(not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))"
+                },
+                {
+                  "transformation": "constant_propagation",
+                  "subselect_evaluation": [
+                  ] /* subselect_evaluation */,
+                  "resulting_condition": "(not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))"
+                },
+                {
+                  "transformation": "trivial_condition_removal",
+                  "subselect_evaluation": [
+                    {
+                      "subselect_execution": {
+                        "select#": 2,
+                        "steps": [
+                          {
+                            "join_optimization": {
+                              "select#": 2,
+                              "steps": [
+                                {
+                                  "table_dependencies": [
+                                    {
+                                      "database": "test",
+                                      "table": "t1",
+                                      "row_may_be_null": false,
+                                      "map_bit": 0,
+                                      "depends_on_map_bits": [
+                                      ] /* depends_on_map_bits */
+                                    }
+                                  ] /* table_dependencies */
+                                },
+                                {
+                                  "rows_estimation": [
+                                    {
+                                      "database": "test",
+                                      "table": "t1",
+                                      "rows": 1,
+                                      "cost": 1,
+                                      "table_type": "system"
+                                    }
+                                  ] /* rows_estimation */
+                                }
+                              ] /* steps */,
+                              "empty_result": {
+                                "cause": "no matching row in const table"
+                              } /* empty_result */
+                            } /* join_optimization */
+                          },
+                          {
+                            "join_execution": {
+                              "select#": 2,
+                              "steps": [
+                              ] /* steps */
+                            } /* join_execution */
+                          }
+                        ] /* steps */
+                      } /* subselect_execution */
+                    }
+                  ] /* subselect_evaluation */,
+                  "resulting_condition": null
+                }
+              ] /* steps */
+            } /* condition_processing */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+
 DROP TABLE t1,t2;
 #
 # BUG#12905521 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON SELECT

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-10-18 10:23:09 +0000
+++ b/sql/sql_select.cc	2011-10-18 14:27:48 +0000
@@ -598,14 +598,27 @@ void remove_redundant_subquery_clauses(s
                subq_predicate->substype() == Item_subselect::IN_SUBS     ||
                subq_predicate->substype() == Item_subselect::ALL_SUBS    ||
                subq_predicate->substype() == Item_subselect::ANY_SUBS);
+
+  enum change
+  {
+    REMOVE_NONE=0,
+    REMOVE_ORDER= 1 << 0,
+    REMOVE_DISTINCT= 1 << 1,
+    REMOVE_GROUP= 1 << 2
+  };
+
+  uint changelog= 0;
+
   if (subq_select_lex->order_list.elements)
   {
+    changelog|= REMOVE_ORDER;
     subq_select_lex->join->order= NULL;
     subq_select_lex->order_list.empty();
   }
 
   if (subq_select_lex->options & SELECT_DISTINCT)
   {
+    changelog|= REMOVE_DISTINCT;
     subq_select_lex->join->select_distinct= false;
     subq_select_lex->options&= ~SELECT_DISTINCT;
   }
@@ -617,9 +630,26 @@ void remove_redundant_subquery_clauses(s
   if (subq_select_lex->group_list.elements &&
       !subq_select_lex->with_sum_func && !subq_select_lex->join->having)
   {
+    changelog|= REMOVE_GROUP;
     subq_select_lex->join->group_list= NULL;
     subq_select_lex->group_list.empty();
   }
+
+  if (changelog)
+  {
+    Opt_trace_context * trace= &subq_select_lex->join->thd->opt_trace;
+    if (unlikely(trace->is_started()))
+    {
+      Opt_trace_object trace_wrapper(trace);
+      Opt_trace_array trace_changes(trace, "transformations_to_subquery");
+      if (changelog & REMOVE_ORDER)
+        trace_changes.add_alnum("removed_ordering");
+      if (changelog & REMOVE_DISTINCT)
+        trace_changes.add_alnum("removed_distinct");
+      if (changelog & REMOVE_GROUP)
+        trace_changes.add_alnum("removed_grouping");
+    }
+  }
 }
 
 /**

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (jorgen.loland:3508 to 3509) WL#5953Jorgen Loland19 Oct