List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:May 21 2012 9:17am
Subject:bzr push into mysql-trunk branch (jorgen.loland:3890 to 3891) Bug#13423851
View as plain text  
 3891 Jorgen Loland	2012-05-21
      BUG#13423851: CRASH IN ITEM_FIELD::ITEM_FIELD, WITH QUERY
                    USING A VIEW
      
      Original committer forgot to record ps-protocol
      optimizer trace result files.

    modified:
      mysql-test/suite/opt_trace/r/general_ps_prot_all.result
      mysql-test/suite/opt_trace/r/general_ps_prot_none.result
 3890 Jorgen Loland	2012-05-21
      Bug#11754168 - PARTS OF INDEX_MERGE_INNODB.TEST ARE DISABLED 
                     DUE TO EXPLAIN DIFFS
      
      Trying to stabilize index_merge_innodb test

    modified:
      mysql-test/include/index_merge1.inc
      mysql-test/r/index_merge_innodb.result
      mysql-test/r/index_merge_myisam.result
=== modified file 'mysql-test/suite/opt_trace/r/general_ps_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/general_ps_prot_all.result	2012-03-31 18:30:05 +0000
+++ b/mysql-test/suite/opt_trace/r/general_ps_prot_all.result	2012-05-21 09:17:07 +0000
@@ -7450,7 +7450,7 @@ freturn 3 ret@0	{
   "steps": [
   ] /* steps */
 }	0	0
-set optimizer_trace_offset=-20, optimizer_trace_limit=20|
+set optimizer_trace_offset=-60, optimizer_trace_limit=60|
 select * from t6 where d in (select f1() from t2 where s="c")|
 d
 select * from information_schema.OPTIMIZER_TRACE|
@@ -7579,12 +7579,6 @@ select * from t6 where d in (select f1()
           },
           {
             "ref_optimizer_key_uses": [
-              {
-                "table": "`t6`",
-                "field": "d",
-                "equals": "`f1`()",
-                "null_rejecting": false
-              }
             ] /* ref_optimizer_key_uses */
           },
           {
@@ -7595,53 +7589,10 @@ select * from t6 where d in (select f1()
             "rows_estimation": [
               {
                 "table": "`t6`",
-                "range_analysis": {
-                  "table_scan": {
-                    "rows": 4,
-                    "cost": 4.9068
-                  } /* table_scan */,
-                  "potential_range_indices": [
-                    {
-                      "index": "d",
-                      "usable": true,
-                      "key_parts": [
-                        "d"
-                      ] /* key_parts */
-                    }
-                  ] /* potential_range_indices */,
-                  "best_covering_index_scan": {
-                    "index": "d",
-                    "cost": 1.8698,
-                    "chosen": true
-                  } /* best_covering_index_scan */,
-                  "setup_range_conditions": [
-                  ] /* setup_range_conditions */,
-                  "group_index_range": {
-                    "chosen": false,
-                    "cause": "not_single_table"
-                  } /* group_index_range */,
-                  "analyzing_range_alternatives": {
-                    "range_scan_alternatives": [
-                      {
-                        "index": "d",
-                        "index_dives_for_eq_ranges": true,
-                        "ranges": [
-                          "3 <= d <= 3"
-                        ] /* ranges */,
-                        "index_only": true,
-                        "rows": 1,
-                        "cost": 2.21,
-                        "rowid_ordered": true,
-                        "chosen": false,
-                        "cause": "cost"
-                      }
-                    ] /* range_scan_alternatives */,
-                    "analyzing_roworder_intersect": {
-                      "usable": false,
-                      "cause": "too_few_roworder_scans"
-                    } /* analyzing_roworder_intersect */
-                  } /* analyzing_range_alternatives */
-                } /* range_analysis */
+                "table_scan": {
+                  "rows": 4,
+                  "cost": 2
+                } /* table_scan */
               },
               {
                 "table": "`t2`",
@@ -7709,28 +7660,21 @@ select * from t6 where d in (select f1()
                     "best_access_path": {
                       "considered_access_paths": [
                         {
-                          "access_type": "ref",
-                          "index": "d",
-                          "rows": 1,
-                          "cost": 3.2,
-                          "chosen": true
-                        },
-                        {
                           "access_type": "scan",
                           "using_join_cache": true,
-                          "rows": 1,
-                          "cost": 2.6076,
+                          "rows": 4,
+                          "cost": 2.0076,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 5.8289,
-                    "rows_for_plan": 3,
+                    "cost_for_plan": 7.0289,
+                    "rows_for_plan": 12,
                     "semijoin_strategy_choice": [
                       {
                         "strategy": "DuplicatesWeedout",
-                        "cost": 8.6289,
-                        "rows": 1,
+                        "cost": 12.229,
+                        "rows": 4,
                         "duplicate_tables_left": true,
                         "chosen": true
                       }
@@ -7746,108 +7690,21 @@ select * from t6 where d in (select f1()
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "ref",
-                      "index": "d",
-                      "rows": 1,
-                      "cost": 1.2,
-                      "chosen": true
-                    },
-                    {
                       "access_type": "scan",
-                      "cost": 2,
                       "rows": 4,
-                      "cause": "cost",
-                      "chosen": false
+                      "cost": 2.0068,
+                      "chosen": true
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
-                "cost_for_plan": 1.2,
-                "rows_for_plan": 1,
+                "cost_for_plan": 2.8068,
+                "rows_for_plan": 4,
                 "semijoin_strategy_choice": [
                 ] /* semijoin_strategy_choice */,
-                "rest_of_plan": [
-                  {
-                    "plan_prefix": [
-                      "`t6`"
-                    ] /* plan_prefix */,
-                    "table": "`t2`",
-                    "best_access_path": {
-                      "considered_access_paths": [
-                        {
-                          "access_type": "scan",
-                          "using_join_cache": true,
-                          "rows": 3,
-                          "cost": 2.0213,
-                          "chosen": true
-                        }
-                      ] /* considered_access_paths */
-                    } /* best_access_path */,
-                    "cost_for_plan": 3.8213,
-                    "rows_for_plan": 3,
-                    "semijoin_strategy_choice": [
-                      {
-                        "strategy": "FirstMatch",
-                        "recalculate_access_paths_and_cost": {
-                          "tables": [
-                            {
-                              "table": "`t2`",
-                              "best_access_path": {
-                                "considered_access_paths": [
-                                  {
-                                    "access_type": "scan",
-                                    "using_join_cache": true,
-                                    "rows": 3,
-                                    "cost": 2.0213,
-                                    "chosen": true
-                                  }
-                                ] /* considered_access_paths */
-                              } /* best_access_path */
-                            }
-                          ] /* tables */
-                        } /* recalculate_access_paths_and_cost */,
-                        "cost": 3.8213,
-                        "rows": 1,
-                        "chosen": true
-                      },
-                      {
-                        "strategy": "MaterializeLookup",
-                        "cost": 6.6212,
-                        "rows": 1,
-                        "duplicate_tables_left": false,
-                        "chosen": false
-                      },
-                      {
-                        "strategy": "DuplicatesWeedout",
-                        "cost": 6.6213,
-                        "rows": 1,
-                        "duplicate_tables_left": false,
-                        "chosen": false
-                      }
-                    ] /* semijoin_strategy_choice */,
-                    "chosen": true
-                  }
-                ] /* rest_of_plan */
+                "pruned_by_heuristic": true
               },
               {
-                "final_semijoin_strategy": "FirstMatch",
-                "recalculate_access_paths_and_cost": {
-                  "tables": [
-                    {
-                      "table": "`t2`",
-                      "best_access_path": {
-                        "considered_access_paths": [
-                          {
-                            "access_type": "scan",
-                            "using_join_cache": true,
-                            "rows": 3,
-                            "cost": 2.0215,
-                            "chosen": true
-                          }
-                        ] /* considered_access_paths */
-                      } /* best_access_path */
-                    }
-                  ] /* tables */
-                } /* recalculate_access_paths_and_cost */
+                "final_semijoin_strategy": "DuplicateWeedout"
               }
             ] /* considered_execution_plans */
           },
@@ -7858,12 +7715,12 @@ select * from t6 where d in (select f1()
               ] /* attached_conditions_computation */,
               "attached_conditions_summary": [
                 {
-                  "table": "`t6`",
-                  "attached": null
+                  "table": "`t2`",
+                  "attached": "(`t2`.`s` = 'c')"
                 },
                 {
-                  "table": "`t2`",
-                  "attached": "((`t6`.`d` = `f1`()) and (`t2`.`s` = 'c'))"
+                  "table": "`t6`",
+                  "attached": "(`t6`.`d` = `f1`())"
                 }
               ] /* attached_conditions_summary */
             } /* attaching_conditions_to_tables */
@@ -7871,11 +7728,23 @@ select * from t6 where d in (select f1()
           {
             "refine_plan": [
               {
-                "table": "`t6`"
+                "creating_tmp_table": {
+                  "tmp_table_info": {
+                    "row_length": 9,
+                    "key_length": 7,
+                    "unique_constraint": false,
+                    "location": "memory (heap)",
+                    "row_limit_estimate": 116508
+                  } /* tmp_table_info */
+                } /* creating_tmp_table */
               },
               {
                 "table": "`t2`",
                 "access_type": "table_scan"
+              },
+              {
+                "table": "`t6`",
+                "access_type": "index_scan"
               }
             ] /* refine_plan */
           }
@@ -8127,64 +7996,35 @@ freturn 3 ret@0	{
   "steps": [
   ] /* steps */
 }	0	0
-select count(*) from information_schema.OPTIMIZER_TRACE|
-count(*)
-12
-set optimizer_trace_offset=3, optimizer_trace_limit=1|
-select * from t6 where d in (select f1() from t2 where s="c")|
-d
-select * from information_schema.OPTIMIZER_TRACE|
-QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
-insert into t1 values("z",0)	{
+set ret@0 NULL	{
   "steps": [
   ] /* steps */
 }	0	0
-create procedure p1(arg char(1))
-begin
-declare res int;
-select d into res from t6 where d in (select f1() from t2 where s=arg);
-select d+1 into res from t6 where d=res+1;
-end|
-set optimizer_trace_offset=0, optimizer_trace_limit=100;
-call p1("c")|
-Warnings:
-Warning	1329	No data - zero rows fetched, selected, or processed
-select * from information_schema.OPTIMIZER_TRACE|
-QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
-call p1("c")	{
+insert into t1 values("z",0)	{
   "steps": [
   ] /* steps */
 }	0	0
-set res@1 NULL	{
+delete from t1 where id="z"	{
   "steps": [
+    {
+      "table": "`t1`",
+      "range_analysis": {
+        "table_scan": {
+          "rows": 3,
+          "cost": 4.7154
+        } /* table_scan */
+      } /* range_analysis */
+    }
   ] /* steps */
 }	0	0
-select d into res from t6 where d in (select f1() from t2 where s=arg)	{
+select sum(data) into ret from t1	{
   "steps": [
     {
       "join_preparation": {
         "select#": 1,
         "steps": [
           {
-            "join_preparation": {
-              "select#": 2,
-              "steps": [
-                {
-                  "expanded_query": "/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = arg@0)"
-                },
-                {
-                  "transformation": {
-                    "select#": 2,
-                    "from": "IN (SELECT)",
-                    "to": "semijoin",
-                    "chosen": true
-                  } /* transformation */
-                }
-              ] /* steps */
-            } /* join_preparation */
-          },
-          {
-            "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` where `t6`.`d` in (/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = arg@0))"
+            "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`"
           }
         ] /* steps */
       } /* join_preparation */
@@ -8194,348 +8034,717 @@ select d into res from t6 where d in (se
         "select#": 1,
         "steps": [
           {
-            "transformation": {
-              "select#": 2,
-              "from": "IN (SELECT)",
-              "to": "semijoin",
-              "chosen": true,
-              "evaluating_constant_semijoin_conditions": [
-              ] /* evaluating_constant_semijoin_conditions */
-            } /* transformation */
-          },
-          {
-            "transformations_to_nested_joins": {
-              "transformations": [
-                "semijoin"
-              ] /* transformations */,
-              "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` semi join (`t2`) where (1 and (`t2`.`s` = arg@0) and (`t6`.`d` = `f1`()))"
-            } /* transformations_to_nested_joins */
-          },
-          {
-            "condition_processing": {
-              "condition": "WHERE",
-              "original_condition": "(1 and (`t2`.`s` = arg@0) and (`t6`.`d` = `f1`()))",
-              "steps": [
-                {
-                  "transformation": "equality_propagation",
-                  "resulting_condition": "(1 and multiple equal(arg@0, `t2`.`s`) and multiple equal(`f1`(), `t6`.`d`))"
-                },
-                {
-                  "transformation": "constant_propagation",
-                  "resulting_condition": "(1 and multiple equal(arg@0, `t2`.`s`) and multiple equal(`f1`(), `t6`.`d`))"
-                },
-                {
-                  "transformation": "trivial_condition_removal",
-                  "resulting_condition": "(multiple equal(arg@0, `t2`.`s`) and multiple equal(`f1`(), `t6`.`d`))"
-                }
-              ] /* steps */
-            } /* condition_processing */
-          },
-          {
             "table_dependencies": [
               {
-                "table": "`t6`",
+                "table": "`t1`",
                 "row_may_be_null": false,
                 "map_bit": 0,
                 "depends_on_map_bits": [
                 ] /* depends_on_map_bits */
-              },
-              {
-                "table": "`t2`",
-                "row_may_be_null": false,
-                "map_bit": 1,
-                "depends_on_map_bits": [
-                ] /* depends_on_map_bits */
               }
             ] /* table_dependencies */
           },
           {
-            "ref_optimizer_key_uses": [
+            "rows_estimation": [
               {
-                "table": "`t6`",
-                "field": "d",
-                "equals": "`f1`()",
-                "null_rejecting": false
+                "table": "`t1`",
+                "table_scan": {
+                  "rows": 2,
+                  "cost": 2
+                } /* table_scan */
               }
-            ] /* ref_optimizer_key_uses */
-          },
-          {
-            "pulled_out_semijoin_tables": [
-            ] /* pulled_out_semijoin_tables */
+            ] /* rows_estimation */
           },
           {
-            "rows_estimation": [
+            "considered_execution_plans": [
               {
-                "table": "`t6`",
-                "range_analysis": {
-                  "table_scan": {
-                    "rows": 4,
-                    "cost": 4.9068
-                  } /* table_scan */,
-                  "potential_range_indices": [
+                "plan_prefix": [
+                ] /* plan_prefix */,
+                "table": "`t1`",
+                "best_access_path": {
+                  "considered_access_paths": [
                     {
-                      "index": "d",
-                      "usable": true,
-                      "key_parts": [
-                        "d"
-                      ] /* key_parts */
+                      "access_type": "scan",
+                      "rows": 2,
+                      "cost": 2.0154,
+                      "chosen": true
                     }
-                  ] /* potential_range_indices */,
-                  "best_covering_index_scan": {
-                    "index": "d",
-                    "cost": 1.8698,
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.4154,
+                "rows_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": null,
+              "attached_conditions_computation": [
+              ] /* attached_conditions_computation */,
+              "attached_conditions_summary": [
+                {
+                  "table": "`t1`",
+                  "attached": null
+                }
+              ] /* attached_conditions_summary */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "table": "`t1`",
+                "access_type": "table_scan"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+freturn 3 ret@0	{
+  "steps": [
+  ] /* steps */
+}	0	0
+set ret@0 NULL	{
+  "steps": [
+  ] /* steps */
+}	0	0
+insert into t1 values("z",0)	{
+  "steps": [
+  ] /* steps */
+}	0	0
+delete from t1 where id="z"	{
+  "steps": [
+    {
+      "table": "`t1`",
+      "range_analysis": {
+        "table_scan": {
+          "rows": 3,
+          "cost": 4.7154
+        } /* table_scan */
+      } /* range_analysis */
+    }
+  ] /* steps */
+}	0	0
+select sum(data) into ret from t1	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "table_dependencies": [
+              {
+                "table": "`t1`",
+                "row_may_be_null": false,
+                "map_bit": 0,
+                "depends_on_map_bits": [
+                ] /* depends_on_map_bits */
+              }
+            ] /* table_dependencies */
+          },
+          {
+            "rows_estimation": [
+              {
+                "table": "`t1`",
+                "table_scan": {
+                  "rows": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* rows_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "plan_prefix": [
+                ] /* plan_prefix */,
+                "table": "`t1`",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "scan",
+                      "rows": 2,
+                      "cost": 2.0154,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.4154,
+                "rows_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": null,
+              "attached_conditions_computation": [
+              ] /* attached_conditions_computation */,
+              "attached_conditions_summary": [
+                {
+                  "table": "`t1`",
+                  "attached": null
+                }
+              ] /* attached_conditions_summary */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "table": "`t1`",
+                "access_type": "table_scan"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+freturn 3 ret@0	{
+  "steps": [
+  ] /* steps */
+}	0	0
+select count(*) from information_schema.OPTIMIZER_TRACE|
+count(*)
+22
+set optimizer_trace_offset=3, optimizer_trace_limit=1|
+select * from t6 where d in (select f1() from t2 where s="c")|
+d
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
+insert into t1 values("z",0)	{
+  "steps": [
+  ] /* steps */
+}	0	0
+create procedure p1(arg char(1))
+begin
+declare res int;
+select d into res from t6 where d in (select f1() from t2 where s=arg);
+select d+1 into res from t6 where d=res+1;
+end|
+set optimizer_trace_offset=0, optimizer_trace_limit=100;
+call p1("c")|
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
+call p1("c")	{
+  "steps": [
+  ] /* steps */
+}	0	0
+set res@1 NULL	{
+  "steps": [
+  ] /* steps */
+}	0	0
+select d into res from t6 where d in (select f1() from t2 where s=arg)	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "join_preparation": {
+              "select#": 2,
+              "steps": [
+                {
+                  "expanded_query": "/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = arg@0)"
+                },
+                {
+                  "transformation": {
+                    "select#": 2,
+                    "from": "IN (SELECT)",
+                    "to": "semijoin",
                     "chosen": true
-                  } /* best_covering_index_scan */,
-                  "setup_range_conditions": [
-                  ] /* setup_range_conditions */,
-                  "group_index_range": {
-                    "chosen": false,
-                    "cause": "not_single_table"
-                  } /* group_index_range */,
-                  "analyzing_range_alternatives": {
-                    "range_scan_alternatives": [
+                  } /* transformation */
+                }
+              ] /* steps */
+            } /* join_preparation */
+          },
+          {
+            "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` where `t6`.`d` in (/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = arg@0))"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "transformation": {
+              "select#": 2,
+              "from": "IN (SELECT)",
+              "to": "semijoin",
+              "chosen": true,
+              "evaluating_constant_semijoin_conditions": [
+              ] /* evaluating_constant_semijoin_conditions */
+            } /* transformation */
+          },
+          {
+            "transformations_to_nested_joins": {
+              "transformations": [
+                "semijoin"
+              ] /* transformations */,
+              "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` semi join (`t2`) where (1 and (`t2`.`s` = arg@0) and (`t6`.`d` = `f1`()))"
+            } /* transformations_to_nested_joins */
+          },
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(1 and (`t2`.`s` = arg@0) and (`t6`.`d` = `f1`()))",
+              "steps": [
+                {
+                  "transformation": "equality_propagation",
+                  "resulting_condition": "(1 and multiple equal(arg@0, `t2`.`s`) and multiple equal(`f1`(), `t6`.`d`))"
+                },
+                {
+                  "transformation": "constant_propagation",
+                  "resulting_condition": "(1 and multiple equal(arg@0, `t2`.`s`) and multiple equal(`f1`(), `t6`.`d`))"
+                },
+                {
+                  "transformation": "trivial_condition_removal",
+                  "resulting_condition": "(multiple equal(arg@0, `t2`.`s`) and multiple equal(`f1`(), `t6`.`d`))"
+                }
+              ] /* steps */
+            } /* condition_processing */
+          },
+          {
+            "table_dependencies": [
+              {
+                "table": "`t6`",
+                "row_may_be_null": false,
+                "map_bit": 0,
+                "depends_on_map_bits": [
+                ] /* depends_on_map_bits */
+              },
+              {
+                "table": "`t2`",
+                "row_may_be_null": false,
+                "map_bit": 1,
+                "depends_on_map_bits": [
+                ] /* depends_on_map_bits */
+              }
+            ] /* table_dependencies */
+          },
+          {
+            "ref_optimizer_key_uses": [
+            ] /* ref_optimizer_key_uses */
+          },
+          {
+            "pulled_out_semijoin_tables": [
+            ] /* pulled_out_semijoin_tables */
+          },
+          {
+            "rows_estimation": [
+              {
+                "table": "`t6`",
+                "table_scan": {
+                  "rows": 4,
+                  "cost": 2
+                } /* table_scan */
+              },
+              {
+                "table": "`t2`",
+                "table_scan": {
+                  "rows": 3,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* rows_estimation */
+          },
+          {
+            "execution_plan_for_potential_materialization": {
+              "steps": [
+                {
+                  "considered_execution_plans": [
+                    {
+                      "plan_prefix": [
+                      ] /* plan_prefix */,
+                      "table": "`t2`",
+                      "best_access_path": {
+                        "considered_access_paths": [
+                          {
+                            "access_type": "scan",
+                            "rows": 3,
+                            "cost": 2.0212,
+                            "chosen": true
+                          }
+                        ] /* considered_access_paths */
+                      } /* best_access_path */,
+                      "cost_for_plan": 2.6212,
+                      "rows_for_plan": 3,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                }
+              ] /* steps */
+            } /* execution_plan_for_potential_materialization */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "plan_prefix": [
+                ] /* plan_prefix */,
+                "table": "`t2`",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "scan",
+                      "rows": 3,
+                      "cost": 2.0212,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.6212,
+                "rows_for_plan": 3,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "rest_of_plan": [
+                  {
+                    "plan_prefix": [
+                      "`t2`"
+                    ] /* plan_prefix */,
+                    "table": "`t6`",
+                    "best_access_path": {
+                      "considered_access_paths": [
+                        {
+                          "access_type": "scan",
+                          "using_join_cache": true,
+                          "rows": 4,
+                          "cost": 2.0076,
+                          "chosen": true
+                        }
+                      ] /* considered_access_paths */
+                    } /* best_access_path */,
+                    "cost_for_plan": 7.0289,
+                    "rows_for_plan": 12,
+                    "semijoin_strategy_choice": [
                       {
-                        "index": "d",
-                        "index_dives_for_eq_ranges": true,
-                        "ranges": [
-                          "3 <= d <= 3"
-                        ] /* ranges */,
-                        "index_only": true,
-                        "rows": 1,
-                        "cost": 2.21,
-                        "rowid_ordered": true,
-                        "chosen": false,
-                        "cause": "cost"
+                        "strategy": "DuplicatesWeedout",
+                        "cost": 12.229,
+                        "rows": 4,
+                        "duplicate_tables_left": true,
+                        "chosen": true
                       }
-                    ] /* range_scan_alternatives */,
-                    "analyzing_roworder_intersect": {
-                      "usable": false,
-                      "cause": "too_few_roworder_scans"
-                    } /* analyzing_roworder_intersect */
-                  } /* analyzing_range_alternatives */
-                } /* range_analysis */
+                    ] /* semijoin_strategy_choice */,
+                    "chosen": true
+                  }
+                ] /* rest_of_plan */
+              },
+              {
+                "plan_prefix": [
+                ] /* plan_prefix */,
+                "table": "`t6`",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "scan",
+                      "rows": 4,
+                      "cost": 2.0068,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.8068,
+                "rows_for_plan": 4,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "pruned_by_heuristic": true
+              },
+              {
+                "final_semijoin_strategy": "DuplicateWeedout"
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "((`t6`.`d` = `f1`()) and (`t2`.`s` = arg@0))",
+              "attached_conditions_computation": [
+              ] /* attached_conditions_computation */,
+              "attached_conditions_summary": [
+                {
+                  "table": "`t2`",
+                  "attached": "(`t2`.`s` = arg@0)"
+                },
+                {
+                  "table": "`t6`",
+                  "attached": "(`t6`.`d` = `f1`())"
+                }
+              ] /* attached_conditions_summary */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "creating_tmp_table": {
+                  "tmp_table_info": {
+                    "row_length": 9,
+                    "key_length": 7,
+                    "unique_constraint": false,
+                    "location": "memory (heap)",
+                    "row_limit_estimate": 116508
+                  } /* tmp_table_info */
+                } /* creating_tmp_table */
+              },
+              {
+                "table": "`t2`",
+                "access_type": "table_scan"
               },
               {
-                "table": "`t2`",
+                "table": "`t6`",
+                "access_type": "index_scan"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+set ret@0 NULL	{
+  "steps": [
+  ] /* steps */
+}	0	0
+insert into t1 values("z",0)	{
+  "steps": [
+  ] /* steps */
+}	0	0
+delete from t1 where id="z"	{
+  "steps": [
+    {
+      "table": "`t1`",
+      "range_analysis": {
+        "table_scan": {
+          "rows": 3,
+          "cost": 4.7154
+        } /* table_scan */
+      } /* range_analysis */
+    }
+  ] /* steps */
+}	0	0
+select sum(data) into ret from t1	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "table_dependencies": [
+              {
+                "table": "`t1`",
+                "row_may_be_null": false,
+                "map_bit": 0,
+                "depends_on_map_bits": [
+                ] /* depends_on_map_bits */
+              }
+            ] /* table_dependencies */
+          },
+          {
+            "rows_estimation": [
+              {
+                "table": "`t1`",
+                "table_scan": {
+                  "rows": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* rows_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "plan_prefix": [
+                ] /* plan_prefix */,
+                "table": "`t1`",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "scan",
+                      "rows": 2,
+                      "cost": 2.0154,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.4154,
+                "rows_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": null,
+              "attached_conditions_computation": [
+              ] /* attached_conditions_computation */,
+              "attached_conditions_summary": [
+                {
+                  "table": "`t1`",
+                  "attached": null
+                }
+              ] /* attached_conditions_summary */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "table": "`t1`",
+                "access_type": "table_scan"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+freturn 3 ret@0	{
+  "steps": [
+  ] /* steps */
+}	0	0
+set ret@0 NULL	{
+  "steps": [
+  ] /* steps */
+}	0	0
+insert into t1 values("z",0)	{
+  "steps": [
+  ] /* steps */
+}	0	0
+delete from t1 where id="z"	{
+  "steps": [
+    {
+      "table": "`t1`",
+      "range_analysis": {
+        "table_scan": {
+          "rows": 3,
+          "cost": 4.7154
+        } /* table_scan */
+      } /* range_analysis */
+    }
+  ] /* steps */
+}	0	0
+select sum(data) into ret from t1	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "expanded_query": "/* select#1 */ select sum(`t1`.`data`) AS `sum(data)` from `t1`"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "table_dependencies": [
+              {
+                "table": "`t1`",
+                "row_may_be_null": false,
+                "map_bit": 0,
+                "depends_on_map_bits": [
+                ] /* depends_on_map_bits */
+              }
+            ] /* table_dependencies */
+          },
+          {
+            "rows_estimation": [
+              {
+                "table": "`t1`",
                 "table_scan": {
-                  "rows": 3,
+                  "rows": 2,
                   "cost": 2
                 } /* table_scan */
               }
             ] /* rows_estimation */
           },
           {
-            "execution_plan_for_potential_materialization": {
-              "steps": [
-                {
-                  "considered_execution_plans": [
-                    {
-                      "plan_prefix": [
-                      ] /* plan_prefix */,
-                      "table": "`t2`",
-                      "best_access_path": {
-                        "considered_access_paths": [
-                          {
-                            "access_type": "scan",
-                            "rows": 3,
-                            "cost": 2.0212,
-                            "chosen": true
-                          }
-                        ] /* considered_access_paths */
-                      } /* best_access_path */,
-                      "cost_for_plan": 2.6212,
-                      "rows_for_plan": 3,
-                      "chosen": true
-                    }
-                  ] /* considered_execution_plans */
-                }
-              ] /* steps */
-            } /* execution_plan_for_potential_materialization */
-          },
-          {
             "considered_execution_plans": [
               {
                 "plan_prefix": [
                 ] /* plan_prefix */,
-                "table": "`t2`",
+                "table": "`t1`",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
                       "access_type": "scan",
-                      "rows": 3,
-                      "cost": 2.0212,
-                      "chosen": true
-                    }
-                  ] /* considered_access_paths */
-                } /* best_access_path */,
-                "cost_for_plan": 2.6212,
-                "rows_for_plan": 3,
-                "semijoin_strategy_choice": [
-                ] /* semijoin_strategy_choice */,
-                "rest_of_plan": [
-                  {
-                    "plan_prefix": [
-                      "`t2`"
-                    ] /* plan_prefix */,
-                    "table": "`t6`",
-                    "best_access_path": {
-                      "considered_access_paths": [
-                        {
-                          "access_type": "ref",
-                          "index": "d",
-                          "rows": 1,
-                          "cost": 3.2,
-                          "chosen": true
-                        },
-                        {
-                          "access_type": "scan",
-                          "using_join_cache": true,
-                          "rows": 1,
-                          "cost": 2.6076,
-                          "chosen": true
-                        }
-                      ] /* considered_access_paths */
-                    } /* best_access_path */,
-                    "cost_for_plan": 5.8289,
-                    "rows_for_plan": 3,
-                    "semijoin_strategy_choice": [
-                      {
-                        "strategy": "DuplicatesWeedout",
-                        "cost": 8.6289,
-                        "rows": 1,
-                        "duplicate_tables_left": true,
-                        "chosen": true
-                      }
-                    ] /* semijoin_strategy_choice */,
-                    "chosen": true
-                  }
-                ] /* rest_of_plan */
-              },
-              {
-                "plan_prefix": [
-                ] /* plan_prefix */,
-                "table": "`t6`",
-                "best_access_path": {
-                  "considered_access_paths": [
-                    {
-                      "access_type": "ref",
-                      "index": "d",
-                      "rows": 1,
-                      "cost": 1.2,
+                      "rows": 2,
+                      "cost": 2.0154,
                       "chosen": true
-                    },
-                    {
-                      "access_type": "scan",
-                      "cost": 2,
-                      "rows": 4,
-                      "cause": "cost",
-                      "chosen": false
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
-                "cost_for_plan": 1.2,
-                "rows_for_plan": 1,
-                "semijoin_strategy_choice": [
-                ] /* semijoin_strategy_choice */,
-                "rest_of_plan": [
-                  {
-                    "plan_prefix": [
-                      "`t6`"
-                    ] /* plan_prefix */,
-                    "table": "`t2`",
-                    "best_access_path": {
-                      "considered_access_paths": [
-                        {
-                          "access_type": "scan",
-                          "using_join_cache": true,
-                          "rows": 3,
-                          "cost": 2.0213,
-                          "chosen": true
-                        }
-                      ] /* considered_access_paths */
-                    } /* best_access_path */,
-                    "cost_for_plan": 3.8213,
-                    "rows_for_plan": 3,
-                    "semijoin_strategy_choice": [
-                      {
-                        "strategy": "FirstMatch",
-                        "recalculate_access_paths_and_cost": {
-                          "tables": [
-                            {
-                              "table": "`t2`",
-                              "best_access_path": {
-                                "considered_access_paths": [
-                                  {
-                                    "access_type": "scan",
-                                    "using_join_cache": true,
-                                    "rows": 3,
-                                    "cost": 2.0213,
-                                    "chosen": true
-                                  }
-                                ] /* considered_access_paths */
-                              } /* best_access_path */
-                            }
-                          ] /* tables */
-                        } /* recalculate_access_paths_and_cost */,
-                        "cost": 3.8213,
-                        "rows": 1,
-                        "chosen": true
-                      },
-                      {
-                        "strategy": "MaterializeLookup",
-                        "cost": 6.6212,
-                        "rows": 1,
-                        "duplicate_tables_left": false,
-                        "chosen": false
-                      },
-                      {
-                        "strategy": "DuplicatesWeedout",
-                        "cost": 6.6213,
-                        "rows": 1,
-                        "duplicate_tables_left": false,
-                        "chosen": false
-                      }
-                    ] /* semijoin_strategy_choice */,
-                    "chosen": true
-                  }
-                ] /* rest_of_plan */
-              },
-              {
-                "final_semijoin_strategy": "FirstMatch",
-                "recalculate_access_paths_and_cost": {
-                  "tables": [
-                    {
-                      "table": "`t2`",
-                      "best_access_path": {
-                        "considered_access_paths": [
-                          {
-                            "access_type": "scan",
-                            "using_join_cache": true,
-                            "rows": 3,
-                            "cost": 2.0215,
-                            "chosen": true
-                          }
-                        ] /* considered_access_paths */
-                      } /* best_access_path */
-                    }
-                  ] /* tables */
-                } /* recalculate_access_paths_and_cost */
+                "cost_for_plan": 2.4154,
+                "rows_for_plan": 2,
+                "chosen": true
               }
             ] /* considered_execution_plans */
           },
           {
             "attaching_conditions_to_tables": {
-              "original_condition": "((`t6`.`d` = `f1`()) and (`t2`.`s` = arg@0))",
+              "original_condition": null,
               "attached_conditions_computation": [
               ] /* attached_conditions_computation */,
               "attached_conditions_summary": [
                 {
-                  "table": "`t6`",
+                  "table": "`t1`",
                   "attached": null
-                },
-                {
-                  "table": "`t2`",
-                  "attached": "((`t6`.`d` = `f1`()) and (`t2`.`s` = arg@0))"
                 }
               ] /* attached_conditions_summary */
             } /* attaching_conditions_to_tables */
@@ -8543,10 +8752,7 @@ select d into res from t6 where d in (se
           {
             "refine_plan": [
               {
-                "table": "`t6`"
-              },
-              {
-                "table": "`t2`",
+                "table": "`t1`",
                 "access_type": "table_scan"
               }
             ] /* refine_plan */
@@ -8563,6 +8769,10 @@ select d into res from t6 where d in (se
     }
   ] /* steps */
 }	0	0
+freturn 3 ret@0	{
+  "steps": [
+  ] /* steps */
+}	0	0
 set ret@0 NULL	{
   "steps": [
   ] /* steps */
@@ -9839,12 +10049,6 @@ select d into res from t6 where d in (se
           },
           {
             "ref_optimizer_key_uses": [
-              {
-                "table": "`t6`",
-                "field": "d",
-                "equals": "`f1`()",
-                "null_rejecting": false
-              }
             ] /* ref_optimizer_key_uses */
           },
           {
@@ -9855,53 +10059,10 @@ select d into res from t6 where d in (se
             "rows_estimation": [
               {
                 "table": "`t6`",
-                "range_analysis": {
-                  "table_scan": {
-                    "rows": 4,
-                    "cost": 4.9068
-                  } /* table_scan */,
-                  "potential_range_indices": [
-                    {
-                      "index": "d",
-                      "usable": true,
-                      "key_parts": [
-                        "d"
-                      ] /* key_parts */
-                    }
-                  ] /* potential_range_indices */,
-                  "best_covering_index_scan": {
-                    "index": "d",
-                    "cost": 1.8698,
-                    "chosen": true
-                  } /* best_covering_index_scan */,
-                  "setup_range_conditions": [
-                  ] /* setup_range_conditions */,
-                  "group_index_range": {
-                    "chosen": false,
-                    "cause": "not_single_table"
-                  } /* group_index_range */,
-                  "analyzing_range_alternatives": {
-                    "range_scan_alternatives": [
-                      {
-                        "index": "d",
-                        "index_dives_for_eq_ranges": true,
-                        "ranges": [
-                          "3 <= d <= 3"
-                        ] /* ranges */,
-                        "index_only": true,
-                        "rows": 1,
-                        "cost": 2.21,
-                        "rowid_ordered": true,
-                        "chosen": false,
-                        "cause": "cost"
-                      }
-                    ] /* range_scan_alternatives */,
-                    "analyzing_roworder_intersect": {
-                      "usable": false,
-                      "cause": "too_few_roworder_scans"
-                    } /* analyzing_roworder_intersect */
-                  } /* analyzing_range_alternatives */
-                } /* range_analysis */
+                "table_scan": {
+                  "rows": 4,
+                  "cost": 2
+                } /* table_scan */
               },
               {
                 "table": "`t2`",
@@ -9949,23 +10110,15 @@ select d into res from t6 where d in (se
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "ref",
-                      "index": "d",
-                      "rows": 1,
-                      "cost": 1.2,
-                      "chosen": true
-                    },
-                    {
                       "access_type": "scan",
-                      "cost": 2,
                       "rows": 4,
-                      "cause": "cost",
-                      "chosen": false
+                      "cost": 2.0068,
+                      "chosen": true
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
-                "cost_for_plan": 1.2,
-                "rows_for_plan": 1,
+                "cost_for_plan": 2.8068,
+                "rows_for_plan": 4,
                 "semijoin_strategy_choice": [
                 ] /* semijoin_strategy_choice */,
                 "rest_of_plan": [
@@ -9980,13 +10133,13 @@ select d into res from t6 where d in (se
                           "access_type": "scan",
                           "using_join_cache": true,
                           "rows": 7,
-                          "cost": 2.0496,
+                          "cost": 2.0499,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
                     } /* best_access_path */,
-                    "cost_for_plan": 4.6496,
-                    "rows_for_plan": 7,
+                    "cost_for_plan": 10.457,
+                    "rows_for_plan": 28,
                     "semijoin_strategy_choice": [
                       {
                         "strategy": "FirstMatch",
@@ -10000,7 +10153,7 @@ select d into res from t6 where d in (se
                                     "access_type": "scan",
                                     "using_join_cache": true,
                                     "rows": 7,
-                                    "cost": 2.0496,
+                                    "cost": 2.0499,
                                     "chosen": true
                                   }
                                 ] /* considered_access_paths */
@@ -10008,21 +10161,21 @@ select d into res from t6 where d in (se
                             }
                           ] /* tables */
                         } /* recalculate_access_paths_and_cost */,
-                        "cost": 4.6496,
-                        "rows": 1,
+                        "cost": 10.457,
+                        "rows": 4,
                         "chosen": true
                       },
                       {
                         "strategy": "MaterializeLookup",
-                        "cost": 8.2496,
-                        "rows": 1,
+                        "cost": 10.456,
+                        "rows": 4,
                         "duplicate_tables_left": false,
-                        "chosen": false
+                        "chosen": true
                       },
                       {
                         "strategy": "DuplicatesWeedout",
-                        "cost": 8.2496,
-                        "rows": 1,
+                        "cost": 18.857,
+                        "rows": 4,
                         "duplicate_tables_left": false,
                         "chosen": false
                       }
@@ -10052,25 +10205,7 @@ select d into res from t6 where d in (se
                 "pruned_by_heuristic": true
               },
               {
-                "final_semijoin_strategy": "FirstMatch",
-                "recalculate_access_paths_and_cost": {
-                  "tables": [
-                    {
-                      "table": "`t2`",
-                      "best_access_path": {
-                        "considered_access_paths": [
-                          {
-                            "access_type": "scan",
-                            "using_join_cache": true,
-                            "rows": 7,
-                            "cost": 2.0496,
-                            "chosen": true
-                          }
-                        ] /* considered_access_paths */
-                      } /* best_access_path */
-                    }
-                  ] /* tables */
-                } /* recalculate_access_paths_and_cost */
+                "final_semijoin_strategy": "MaterializeLookup"
               }
             ] /* considered_execution_plans */
           },
@@ -10086,7 +10221,7 @@ select d into res from t6 where d in (se
                 },
                 {
                   "table": "`t2`",
-                  "attached": "((`t6`.`d` = `f1`()) and (`t2`.`s` = arg@0))"
+                  "attached": "(`t2`.`s` = arg@0)"
                 }
               ] /* attached_conditions_summary */
             } /* attaching_conditions_to_tables */
@@ -10094,10 +10229,27 @@ select d into res from t6 where d in (se
           {
             "refine_plan": [
               {
-                "table": "`t6`"
+                "table": "`t6`",
+                "access_type": "index_scan"
               },
               {
                 "table": "`t2`",
+                "unknown_key_1": {
+                  "creating_tmp_table": {
+                    "tmp_table_info": {
+                      "row_length": 5,
+                      "key_length": 5,
+                      "unique_constraint": false,
+                      "location": "memory (heap)",
+                      "row_limit_estimate": 209715
+                    } /* tmp_table_info */
+                  } /* creating_tmp_table */
+                },
+                "creating_semijoin_materialization_conditions": {
+                  "evaluating_constant_semijoin_conditions": [
+                  ] /* evaluating_constant_semijoin_conditions */,
+                  "resulting_condition": "(`t6`.`d` = `sj-materialize`.`f1()`)"
+                } /* creating_semijoin_materialization_conditions */,
                 "access_type": "table_scan"
               }
             ] /* refine_plan */

=== modified file 'mysql-test/suite/opt_trace/r/general_ps_prot_none.result'
--- a/mysql-test/suite/opt_trace/r/general_ps_prot_none.result	2012-03-31 18:30:05 +0000
+++ b/mysql-test/suite/opt_trace/r/general_ps_prot_none.result	2012-05-21 09:17:07 +0000
@@ -6557,11 +6557,346 @@ freturn 3 ret@0	{
   "steps": [
   ] /* steps */
 }	0	0
-set optimizer_trace_offset=-20, optimizer_trace_limit=20|
+set optimizer_trace_offset=-60, optimizer_trace_limit=60|
 select * from t6 where d in (select f1() from t2 where s="c")|
 d
 select * from information_schema.OPTIMIZER_TRACE|
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
+select * from t6 where d in (select f1() from t2 where s="c")	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "join_preparation": {
+              "select#": 2,
+              "steps": [
+                {
+                  "expanded_query": "/* select#2 */ select `f1`() from `t2` where (`t2`.`s` = 'c')"
+                },
+                {
+                  "transformation": {
+                    "select#": 2,
+                    "from": "IN (SELECT)",
+                    "to": "semijoin",
+                    "chosen": false
+                  } /* transformation */
+                },
+                {
+                  "transformation": {
+                    "select#": 2,
+                    "from": "IN (SELECT)",
+                    "to": "EXISTS (CORRELATED SELECT)",
+                    "chosen": true,
+                    "evaluating_constant_where_conditions": [
+                    ] /* evaluating_constant_where_conditions */
+                  } /* transformation */
+                }
+              ] /* steps */
+            } /* join_preparation */
+          },
+          {
+            "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` where <in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    }
+  ] /* steps */
+}	0	0
+select * from t6 where d in (select f1() from t2 where s="c")	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "join_preparation": {
+              "select#": 2,
+              "steps": [
+                {
+                  "expanded_query": "/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))"
+                },
+                {
+                  "transformation": {
+                    "select#": 2,
+                    "from": "IN (SELECT)",
+                    "to": "semijoin",
+                    "chosen": false
+                  } /* transformation */
+                }
+              ] /* steps */
+            } /* join_preparation */
+          },
+          {
+            "expanded_query": "/* select#1 */ select `t6`.`d` AS `d` from `t6` where <in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "<in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))",
+              "steps": [
+                {
+                  "transformation": "equality_propagation",
+                  "subselect_evaluation": [
+                  ] /* subselect_evaluation */,
+                  "resulting_condition": "<in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))"
+                },
+                {
+                  "transformation": "constant_propagation",
+                  "subselect_evaluation": [
+                  ] /* subselect_evaluation */,
+                  "resulting_condition": "<in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))"
+                },
+                {
+                  "transformation": "trivial_condition_removal",
+                  "subselect_evaluation": [
+                  ] /* subselect_evaluation */,
+                  "resulting_condition": "<in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))"
+                }
+              ] /* steps */
+            } /* condition_processing */
+          },
+          {
+            "table_dependencies": [
+              {
+                "table": "`t6`",
+                "row_may_be_null": false,
+                "map_bit": 0,
+                "depends_on_map_bits": [
+                ] /* depends_on_map_bits */
+              }
+            ] /* table_dependencies */
+          },
+          {
+            "ref_optimizer_key_uses": [
+            ] /* ref_optimizer_key_uses */
+          },
+          {
+            "rows_estimation": [
+              {
+                "table": "`t6`",
+                "table_scan": {
+                  "rows": 4,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* rows_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "plan_prefix": [
+                ] /* plan_prefix */,
+                "table": "`t6`",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "scan",
+                      "rows": 4,
+                      "cost": 2.0068,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.8068,
+                "rows_for_plan": 4,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))",
+              "attached_conditions_computation": [
+              ] /* attached_conditions_computation */,
+              "attached_conditions_summary": [
+                {
+                  "table": "`t6`",
+                  "attached": "<in_optimizer>(`t6`.`d`,<exists>(/* select#2 */ select 1 from `t2` where ((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))))"
+                }
+              ] /* attached_conditions_summary */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "table": "`t6`",
+                "access_type": "index_scan"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+          {
+            "subselect_execution": {
+              "select#": 2,
+              "steps": [
+                {
+                  "join_optimization": {
+                    "select#": 2,
+                    "steps": [
+                      {
+                        "condition_processing": {
+                          "condition": "WHERE",
+                          "original_condition": "((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))",
+                          "steps": [
+                            {
+                              "transformation": "equality_propagation",
+                              "resulting_condition": "((<cache>(`t6`.`d`) = `f1`()) and multiple equal('c', `t2`.`s`))"
+                            },
+                            {
+                              "transformation": "constant_propagation",
+                              "resulting_condition": "((<cache>(`t6`.`d`) = `f1`()) and multiple equal('c', `t2`.`s`))"
+                            },
+                            {
+                              "transformation": "trivial_condition_removal",
+                              "resulting_condition": "((<cache>(`t6`.`d`) = `f1`()) and multiple equal('c', `t2`.`s`))"
+                            }
+                          ] /* steps */
+                        } /* condition_processing */
+                      },
+                      {
+                        "table_dependencies": [
+                          {
+                            "table": "`t2`",
+                            "row_may_be_null": false,
+                            "map_bit": 0,
+                            "depends_on_map_bits": [
+                            ] /* depends_on_map_bits */
+                          }
+                        ] /* table_dependencies */
+                      },
+                      {
+                        "ref_optimizer_key_uses": [
+                        ] /* ref_optimizer_key_uses */
+                      },
+                      {
+                        "rows_estimation": [
+                          {
+                            "table": "`t2`",
+                            "table_scan": {
+                              "rows": 3,
+                              "cost": 2
+                            } /* table_scan */
+                          }
+                        ] /* rows_estimation */
+                      },
+                      {
+                        "considered_execution_plans": [
+                          {
+                            "plan_prefix": [
+                            ] /* plan_prefix */,
+                            "table": "`t2`",
+                            "best_access_path": {
+                              "considered_access_paths": [
+                                {
+                                  "access_type": "scan",
+                                  "rows": 3,
+                                  "cost": 2.0212,
+                                  "chosen": true
+                                }
+                              ] /* considered_access_paths */
+                            } /* best_access_path */,
+                            "cost_for_plan": 2.6212,
+                            "rows_for_plan": 3,
+                            "chosen": true
+                          }
+                        ] /* considered_execution_plans */
+                      },
+                      {
+                        "attaching_conditions_to_tables": {
+                          "original_condition": "((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))",
+                          "attached_conditions_computation": [
+                          ] /* attached_conditions_computation */,
+                          "attached_conditions_summary": [
+                            {
+                              "table": "`t2`",
+                              "attached": "((`t2`.`s` = 'c') and (<cache>(`t6`.`d`) = `f1`()))"
+                            }
+                          ] /* attached_conditions_summary */
+                        } /* attaching_conditions_to_tables */
+                      },
+                      {
+                        "refine_plan": [
+                          {
+                            "table": "`t2`",
+                            "access_type": "table_scan"
+                          }
+                        ] /* refine_plan */
+                      }
+                    ] /* steps */
+                  } /* join_optimization */
+                },
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
+                  } /* join_execution */
+                }
+              ] /* steps */
+            } /* subselect_execution */
+          },
+          {
+            "subselect_execution": {
+              "select#": 2,
+              "steps": [
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
+                  } /* join_execution */
+                }
+              ] /* steps */
+            } /* subselect_execution */
+          },
+          {
+            "subselect_execution": {
+              "select#": 2,
+              "steps": [
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
+                  } /* join_execution */
+                }
+              ] /* steps */
+            } /* subselect_execution */
+          },
+          {
+            "subselect_execution": {
+              "select#": 2,
+              "steps": [
+                {
+                  "join_execution": {
+                    "select#": 2,
+                    "steps": [
+                    ] /* steps */
+                  } /* join_execution */
+                }
+              ] /* steps */
+            } /* subselect_execution */
+          }
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
 set ret@0 NULL	{
   "steps": [
   ] /* steps */
@@ -7036,7 +7371,7 @@ freturn 3 ret@0	{
 }	0	0
 select count(*) from information_schema.OPTIMIZER_TRACE|
 count(*)
-20
+22
 set optimizer_trace_offset=3, optimizer_trace_limit=1|
 select * from t6 where d in (select f1() from t2 where s="c")|
 d

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (jorgen.loland:3890 to 3891) Bug#13423851Jorgen Loland21 May