List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:September 8 2010 10:27am
Subject:bzr commit into mysql-next-mr-bugfixing branch (guilhem:3202)
View as plain text  
#At file:///home/mysql_src/bzrrepos_new/mysql-next-mr-opt-backporting-wl4800/ based on revid:guilhem@stripped

 3202 Guilhem Bichot	2010-09-08
      test of optimizer_trace vs {query with a stored function, or a stored procedure,
      or a trigger, or a view, or an INFORMATION_SCHEMA table}. Test that
      first and second EXECUTE of a prepared statement give the same trace.
     @ sql/sql_select.cc
        more disabling of range optimizer's tracing

    modified:
      WL4800_TODO.txt
      mysql-test/include/optimizer_trace.inc
      mysql-test/r/optimizer_trace_no_prot.result
      mysql-test/r/optimizer_trace_ps_prot.result
      sql/sql_select.cc
=== modified file 'WL4800_TODO.txt'
--- a/WL4800_TODO.txt	2010-09-05 16:08:07 +0000
+++ b/WL4800_TODO.txt	2010-09-08 10:27:44 +0000
@@ -22,10 +22,13 @@ rsync -avz /m/doxygen/mysql-wl4800 [URL]
 
 fix bugs recorded in optimizer_trace_bugs.test and delete that test.
 
-test with VIEWS (mergeable or not), INFORMATION_SCHEMA tables.
-
 Put in the trace some way to relate "select #N" to the corresponding
-SELECT.
+SELECT. Idea:
+top_query: "SELECT ... UNION SELECT ... WHERE x IN (SELECT ...)"
+select#  : "^1               ^2                     ^3"
+This is not a complete solution, because for "SELECT stored_func()",
+the stored func can contain SELECTs, each of them has a select#. Same
+for "CALL stored_proc()".
 
 Is String an acceptable way of memory management for the trace, for a
 first version?

=== modified file 'mysql-test/include/optimizer_trace.inc'
--- a/mysql-test/include/optimizer_trace.inc	2010-09-03 08:18:12 +0000
+++ b/mysql-test/include/optimizer_trace.inc	2010-09-08 10:27:44 +0000
@@ -251,9 +251,83 @@ select * from information_schema.OPTIMIZ
 insert into D select * from D where D>7;
 select * from information_schema.OPTIMIZER_TRACE;
 
-# TODO: what about CALL, stored functions, triggers?
+# Stored functions
 
-# TODO: PREPARE/EXECUTE/EXECUTE
+create table t1 (
+	id   char(16) not null default '',
+        data int not null
+);
+create table t2 (
+	s   char(16),
+        i   int,
+	d   double
+);
+delimiter |;
+insert into t1 values ("a", 1), ("b", 2) |
+insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
+create function f1() returns int
+  return (select sum(data) from t1)|
+select f1()|
+select * from information_schema.OPTIMIZER_TRACE|
+select s, f1() from t2 order by s desc|
+select * from information_schema.OPTIMIZER_TRACE|
+select * from D where d in (select f1() from t2 where s="c")|
+select * from information_schema.OPTIMIZER_TRACE|
 
+# Stored procedures
+
+create procedure p1(arg char(1))
+begin
+  declare res int;
+  select d into res from D where d in (select f1() from t2 where s=arg);
+  select d+1 into res from D where d=res+1;
+end|
+call p1("c")|
+select * from information_schema.OPTIMIZER_TRACE|
+
+# Triggers
+create trigger trg1 before insert on t2 for each row
+begin
+  set new.s=f1();  
+end|
+insert into t2 select d,100,200 from D where d is not null|
+select * from information_schema.OPTIMIZER_TRACE|
+select * from t2|
+
+delimiter ;|
+
+# PREPARE/EXECUTE/EXECUTE
+prepare stmt from 'call p1(?)';
+select * from information_schema.OPTIMIZER_TRACE;
+set @param="c";
+execute stmt using @param;
+select TRACE into @trace from information_schema.OPTIMIZER_TRACE;
+select @trace;
+# second EXECUTE should give same trace
+execute stmt using @param;
+select TRACE into @trace2 from information_schema.OPTIMIZER_TRACE;
+select @trace=@trace2;
+drop function f1;
+drop procedure p1;
+drop trigger trg1;
+
+# Views
+# merge-able
+create view v1 as select * from t1 where id < "c";
+explain select * from v1 where id="b";
+select * from information_schema.OPTIMIZER_TRACE;
+drop view v1;
+# not merge-able
+create view v1 as select * from t1 where id < "c" limit 2;
+explain select * from v1 where id="b";
+select * from information_schema.OPTIMIZER_TRACE;
+drop view v1;
+
+# I_S tables
+select * from information_schema.SESSION_VARIABLES where
+VARIABLE_NAME="optimizer_trace";
+select * from information_schema.OPTIMIZER_TRACE;
+
+drop table t1,t2;
 DROP TABLE C,D;
 set optimizer_trace=default;

=== modified file 'mysql-test/r/optimizer_trace_no_prot.result'
--- a/mysql-test/r/optimizer_trace_no_prot.result	2010-09-04 17:40:51 +0000
+++ b/mysql-test/r/optimizer_trace_no_prot.result	2010-09-08 10:27:44 +0000
@@ -3456,5 +3456,2247 @@ QUERY_ID	TRACE
     }
   ] /* steps */
 }
+create table t1 (
+id   char(16) not null default '',
+data int not null
+);
+create table t2 (
+s   char(16),
+i   int,
+d   double
+);
+insert into t1 values ("a", 1), ("b", 2) |
+insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
+create function f1() returns int
+return (select sum(data) from t1)|
+select f1()|
+f1()
+3
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "select f1()",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 2,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 2,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+select s, f1() from t2 order by s desc|
+s	f1()
+c	3
+b	3
+a	3
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "select s, f1() from t2 order by s desc",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t2",
+                "table_scan": {
+                  "records": 3,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t2",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0212,
+                      "records": 3,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0212,
+                "records_for_plan": 3,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t2",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 2,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 2,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 2,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 2,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 2,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 2,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+select * from D where d in (select f1() from t2 where s="c")|
+d
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "select * from D where d in (select f1() from t2 where s="c")",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+          {
+            "join_preparation": {
+              "select #": 2,
+              "steps": [
+                {
+                  "transformation": {
+                    "select #": 2,
+                    "from": "IN (SELECT)",
+                    "to": "semijoin",
+                    "chosen": true
+                  } /* transformation */
+                }
+              ] /* steps */
+            } /* join_preparation */
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "transformation": {
+              "select #": 2,
+              "from": "IN (SELECT)",
+              "to": "semijoin",
+              "chosen": true
+            } /* transformation */
+          },
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(1 and (`test`.`t2`.`s` = 'c') and (`test`.`D`.`d` = `f1`()))",
+              "after_equality_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+              "after_constant_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+              "after_trivial_conditions_removal": "(multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+              {
+                "condition": "D.d= `f1`()",
+                "null_rejecting": false
+              }
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "D",
+                "more_range_optimizer_trace": {
+                  "TODO?": "yes!"
+                } /* more_range_optimizer_trace */
+              },
+              {
+                "table": "t2",
+                "table_scan": {
+                  "records": 3,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "pulled_out_semijoin_tables": [
+            ] /* pulled_out_semijoin_tables */
+          },
+          {
+            "execution_plan_for_potential_materialization": {
+              "steps": [
+                {
+                  "considered_execution_plans": [
+                    {
+                      "table": "t2",
+                      "best_access_path": {
+                        "considered_access_paths": [
+                          {
+                            "access_type": "table scan",
+                            "cost": 2.0212,
+                            "records": 3,
+                            "chosen": true
+                          }
+                        ] /* considered_access_paths */
+                      } /* best_access_path */,
+                      "cost_for_plan": 2.0212,
+                      "records_for_plan": 3,
+                      "semijoin_strategy_choice": [
+                      ] /* semijoin_strategy_choice */,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                }
+              ] /* steps */
+            } /* execution_plan_for_potential_materialization */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t2",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0212,
+                      "records": 3,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0212,
+                "records_for_plan": 3,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "rest_of_plan": [
+                  {
+                    "table": "D",
+                    "best_access_path": {
+                      "considered_access_paths": [
+                        {
+                          "access_type": "index",
+                          "index": "d",
+                          "cost": 3,
+                          "records": 1,
+                          "chosen": true
+                        },
+                        {
+                          "access_type": "table scan",
+                          "cost": 2.6076,
+                          "records": 1,
+                          "chosen": true
+                        }
+                      ] /* considered_access_paths */
+                    } /* best_access_path */,
+                    "cost_for_plan": 4.6289,
+                    "records_for_plan": 3,
+                    "semijoin_strategy_choice": [
+                      {
+                        "strategy": "DuplicatesWeedout",
+                        "cost": 5.2289,
+                        "records": 1,
+                        "duplicate_tables_left": true,
+                        "chosen": true
+                      }
+                    ] /* semijoin_strategy_choice */,
+                    "chosen": true
+                  }
+                ] /* rest_of_plan */
+              },
+              {
+                "table": "D",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "index",
+                      "index": "d",
+                      "cost": 1,
+                      "records": 1,
+                      "chosen": true
+                    },
+                    {
+                      "access_type": "table scan",
+                      "cost": 2,
+                      "records": 4,
+                      "chosen": false
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 1,
+                "records_for_plan": 1,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "rest_of_plan": [
+                  {
+                    "table": "t2",
+                    "best_access_path": {
+                      "considered_access_paths": [
+                        {
+                          "access_type": "table scan",
+                          "cost": 2.0213,
+                          "records": 3,
+                          "chosen": true
+                        }
+                      ] /* considered_access_paths */
+                    } /* best_access_path */,
+                    "cost_for_plan": 3.0213,
+                    "records_for_plan": 3,
+                    "semijoin_strategy_choice": [
+                      {
+                        "strategy": "FirstMatch",
+                        "recompute_best_access_paths": {
+                          "cause": "join_buffering_not_possible",
+                          "tables": [
+                            {
+                              "table": "t2",
+                              "best_access_path": {
+                                "considered_access_paths": [
+                                  {
+                                    "access_type": "table scan",
+                                    "cost": 2.0213,
+                                    "records": 3,
+                                    "chosen": true
+                                  }
+                                ] /* considered_access_paths */
+                              } /* best_access_path */
+                            }
+                          ] /* tables */
+                        } /* recompute_best_access_paths */,
+                        "cost": 3.0213,
+                        "records": 1,
+                        "chosen": true
+                      },
+                      {
+                        "strategy": "MaterializationLookup",
+                        "cost": 3.2212,
+                        "records": 1,
+                        "duplicate_tables_left": false,
+                        "chosen": false
+                      },
+                      {
+                        "strategy": "DuplicatesWeedout",
+                        "cost": 3.6213,
+                        "records": 1,
+                        "duplicate_tables_left": false,
+                        "chosen": false
+                      }
+                    ] /* semijoin_strategy_choice */,
+                    "chosen": true
+                  }
+                ] /* rest_of_plan */
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "reconsidering_access_paths_for_semijoin": {
+              "strategy": "FirstMatch",
+              "table": "t2",
+              "best_access_path": {
+                "considered_access_paths": [
+                  {
+                    "access_type": "table scan",
+                    "cost": 2.0212,
+                    "records": 3,
+                    "chosen": true
+                  }
+                ] /* considered_access_paths */
+              } /* best_access_path */
+            } /* reconsidering_access_paths_for_semijoin */
+          },
+          {
+            "join_preparation": {
+              "select #": 2,
+              "steps": [
+              ] /* steps */
+            } /* join_preparation */
+          },
+          {
+            "join_optimization": {
+              "select #": 2,
+              "steps": [
+                {
+                  "constant_tables": [
+                  ] /* constant_tables */,
+                  "records_estimation": [
+                    {
+                      "table": "t1",
+                      "table_scan": {
+                        "records": 2,
+                        "cost": 2
+                      } /* table_scan */
+                    }
+                  ] /* records_estimation */
+                },
+                {
+                  "considered_execution_plans": [
+                    {
+                      "table": "t1",
+                      "best_access_path": {
+                        "considered_access_paths": [
+                          {
+                            "access_type": "table scan",
+                            "cost": 2.0103,
+                            "records": 2,
+                            "chosen": true
+                          }
+                        ] /* considered_access_paths */
+                      } /* best_access_path */,
+                      "cost_for_plan": 2.0103,
+                      "records_for_plan": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                },
+                {
+                  "attaching_conditions_to_tables": {
+                    "original_condition": "<null>",
+                    "attached_conditions": [
+                      {
+                        "table": "t1",
+                        "attached": "<null>"
+                      }
+                    ] /* attached_conditions */
+                  } /* attaching_conditions_to_tables */
+                }
+              ] /* steps */
+            } /* join_optimization */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
+              "attached_conditions": [
+                {
+                  "table": "D",
+                  "attached": "(`test`.`D`.`d` = `f1`())"
+                },
+                {
+                  "table": "t2",
+                  "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+create procedure p1(arg char(1))
+begin
+declare res int;
+select d into res from D where d in (select f1() from t2 where s=arg);
+select d+1 into res from D where d=res+1;
+end|
+call p1("c")|
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "call p1("c")",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+          {
+            "join_preparation": {
+              "select #": 2,
+              "steps": [
+                {
+                  "transformation": {
+                    "select #": 2,
+                    "from": "IN (SELECT)",
+                    "to": "semijoin",
+                    "chosen": true
+                  } /* transformation */
+                }
+              ] /* steps */
+            } /* join_preparation */
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "transformation": {
+              "select #": 2,
+              "from": "IN (SELECT)",
+              "to": "semijoin",
+              "chosen": true
+            } /* transformation */
+          },
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(1 and (`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
+              "after_equality_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+              "after_constant_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+              "after_trivial_conditions_removal": "(multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+              {
+                "condition": "D.d= `f1`()",
+                "null_rejecting": false
+              }
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "D",
+                "more_range_optimizer_trace": {
+                  "TODO?": "yes!"
+                } /* more_range_optimizer_trace */
+              },
+              {
+                "table": "t2",
+                "table_scan": {
+                  "records": 3,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "pulled_out_semijoin_tables": [
+            ] /* pulled_out_semijoin_tables */
+          },
+          {
+            "execution_plan_for_potential_materialization": {
+              "steps": [
+                {
+                  "considered_execution_plans": [
+                    {
+                      "table": "t2",
+                      "best_access_path": {
+                        "considered_access_paths": [
+                          {
+                            "access_type": "table scan",
+                            "cost": 2.0212,
+                            "records": 3,
+                            "chosen": true
+                          }
+                        ] /* considered_access_paths */
+                      } /* best_access_path */,
+                      "cost_for_plan": 2.0212,
+                      "records_for_plan": 3,
+                      "semijoin_strategy_choice": [
+                      ] /* semijoin_strategy_choice */,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                }
+              ] /* steps */
+            } /* execution_plan_for_potential_materialization */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t2",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0212,
+                      "records": 3,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0212,
+                "records_for_plan": 3,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "rest_of_plan": [
+                  {
+                    "table": "D",
+                    "best_access_path": {
+                      "considered_access_paths": [
+                        {
+                          "access_type": "index",
+                          "index": "d",
+                          "cost": 3,
+                          "records": 1,
+                          "chosen": true
+                        },
+                        {
+                          "access_type": "table scan",
+                          "cost": 2.6076,
+                          "records": 1,
+                          "chosen": true
+                        }
+                      ] /* considered_access_paths */
+                    } /* best_access_path */,
+                    "cost_for_plan": 4.6289,
+                    "records_for_plan": 3,
+                    "semijoin_strategy_choice": [
+                      {
+                        "strategy": "DuplicatesWeedout",
+                        "cost": 5.2289,
+                        "records": 1,
+                        "duplicate_tables_left": true,
+                        "chosen": true
+                      }
+                    ] /* semijoin_strategy_choice */,
+                    "chosen": true
+                  }
+                ] /* rest_of_plan */
+              },
+              {
+                "table": "D",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "index",
+                      "index": "d",
+                      "cost": 1,
+                      "records": 1,
+                      "chosen": true
+                    },
+                    {
+                      "access_type": "table scan",
+                      "cost": 2,
+                      "records": 4,
+                      "chosen": false
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 1,
+                "records_for_plan": 1,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "rest_of_plan": [
+                  {
+                    "table": "t2",
+                    "best_access_path": {
+                      "considered_access_paths": [
+                        {
+                          "access_type": "table scan",
+                          "cost": 2.0213,
+                          "records": 3,
+                          "chosen": true
+                        }
+                      ] /* considered_access_paths */
+                    } /* best_access_path */,
+                    "cost_for_plan": 3.0213,
+                    "records_for_plan": 3,
+                    "semijoin_strategy_choice": [
+                      {
+                        "strategy": "FirstMatch",
+                        "recompute_best_access_paths": {
+                          "cause": "join_buffering_not_possible",
+                          "tables": [
+                            {
+                              "table": "t2",
+                              "best_access_path": {
+                                "considered_access_paths": [
+                                  {
+                                    "access_type": "table scan",
+                                    "cost": 2.0213,
+                                    "records": 3,
+                                    "chosen": true
+                                  }
+                                ] /* considered_access_paths */
+                              } /* best_access_path */
+                            }
+                          ] /* tables */
+                        } /* recompute_best_access_paths */,
+                        "cost": 3.0213,
+                        "records": 1,
+                        "chosen": true
+                      },
+                      {
+                        "strategy": "MaterializationLookup",
+                        "cost": 3.2212,
+                        "records": 1,
+                        "duplicate_tables_left": false,
+                        "chosen": false
+                      },
+                      {
+                        "strategy": "DuplicatesWeedout",
+                        "cost": 3.6213,
+                        "records": 1,
+                        "duplicate_tables_left": false,
+                        "chosen": false
+                      }
+                    ] /* semijoin_strategy_choice */,
+                    "chosen": true
+                  }
+                ] /* rest_of_plan */
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "reconsidering_access_paths_for_semijoin": {
+              "strategy": "FirstMatch",
+              "table": "t2",
+              "best_access_path": {
+                "considered_access_paths": [
+                  {
+                    "access_type": "table scan",
+                    "cost": 2.0212,
+                    "records": 3,
+                    "chosen": true
+                  }
+                ] /* considered_access_paths */
+              } /* best_access_path */
+            } /* reconsidering_access_paths_for_semijoin */
+          },
+          {
+            "join_preparation": {
+              "select #": 3,
+              "steps": [
+              ] /* steps */
+            } /* join_preparation */
+          },
+          {
+            "join_optimization": {
+              "select #": 3,
+              "steps": [
+                {
+                  "constant_tables": [
+                  ] /* constant_tables */,
+                  "records_estimation": [
+                    {
+                      "table": "t1",
+                      "table_scan": {
+                        "records": 2,
+                        "cost": 2
+                      } /* table_scan */
+                    }
+                  ] /* records_estimation */
+                },
+                {
+                  "considered_execution_plans": [
+                    {
+                      "table": "t1",
+                      "best_access_path": {
+                        "considered_access_paths": [
+                          {
+                            "access_type": "table scan",
+                            "cost": 2.0103,
+                            "records": 2,
+                            "chosen": true
+                          }
+                        ] /* considered_access_paths */
+                      } /* best_access_path */,
+                      "cost_for_plan": 2.0103,
+                      "records_for_plan": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                },
+                {
+                  "attaching_conditions_to_tables": {
+                    "original_condition": "<null>",
+                    "attached_conditions": [
+                      {
+                        "table": "t1",
+                        "attached": "<null>"
+                      }
+                    ] /* attached_conditions */
+                  } /* attaching_conditions_to_tables */
+                }
+              ] /* steps */
+            } /* join_optimization */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))",
+              "attached_conditions": [
+                {
+                  "table": "D",
+                  "attached": "(`test`.`D`.`d` = `f1`())"
+                },
+                {
+                  "table": "t2",
+                  "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`test`.`D`.`d` = (res@1 + 1))",
+              "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+              "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+              "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`D`.`d`)"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+              {
+                "condition": "D.d= (res@1 + 1)",
+                "null_rejecting": false
+              }
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "D",
+                "more_range_optimizer_trace": {
+                  "TODO?": "yes!"
+                } /* more_range_optimizer_trace */
+              }
+            ] /* records_estimation */
+          }
+        ] /* steps */,
+        "empty_result": {
+          "cause": "no matching row in const table"
+        } /* empty_result */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+create trigger trg1 before insert on t2 for each row
+begin
+set new.s=f1();  
+end|
+insert into t2 select d,100,200 from D where d is not null|
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "insert into t2 select d,100,200 from D where d is not null",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`test`.`D`.`d` is not null)",
+              "after_equality_propagation": "(`test`.`D`.`d` is not null)",
+              "after_constant_propagation": "(`test`.`D`.`d` is not null)",
+              "after_trivial_conditions_removal": "(`test`.`D`.`d` is not null)"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "D",
+                "more_range_optimizer_trace": {
+                  "TODO?": "yes!"
+                } /* more_range_optimizer_trace */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "D",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0068,
+                      "records": 4,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0068,
+                "records_for_plan": 4,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(`test`.`D`.`d` is not null)",
+              "attached_conditions": [
+                {
+                  "table": "D",
+                  "attached": "(`test`.`D`.`d` is not null)"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+select * from t2|
+s	i	d
+a	1	1
+b	2	2
+c	3	3
+3	100	200
+3	100	200
+3	100	200
+3	100	200
+prepare stmt from 'call p1(?)';
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "prepare stmt from 'call p1(?)'",
+  "steps": [
+  ] /* steps */
+}
+set @param="c";
+execute stmt using @param;
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+select TRACE into @trace from information_schema.OPTIMIZER_TRACE;
+select @trace;
+@trace
+
+{
+  "top_query": "execute stmt using @param",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(1 and (`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
+              "after_equality_propagation": "(1 and (`test`.`D`.`d` = `f1`()) and multiple equal(arg@0, `test`.`t2`.`s`))",
+              "after_constant_propagation": "(1 and (`test`.`D`.`d` = `f1`()) and multiple equal(arg@0, `test`.`t2`.`s`))",
+              "after_trivial_conditions_removal": "((`test`.`D`.`d` = `f1`()) and multiple equal(arg@0, `test`.`t2`.`s`))"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "D",
+                "table_scan": {
+                  "records": 4,
+                  "cost": 2
+                } /* table_scan */
+              },
+              {
+                "table": "t2",
+                "table_scan": {
+                  "records": 7,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "pulled_out_semijoin_tables": [
+            ] /* pulled_out_semijoin_tables */
+          },
+          {
+            "execution_plan_for_potential_materialization": {
+              "steps": [
+                {
+                  "considered_execution_plans": [
+                    {
+                      "table": "t2",
+                      "best_access_path": {
+                        "considered_access_paths": [
+                          {
+                            "access_type": "table scan",
+                            "cost": 2.0496,
+                            "records": 7,
+                            "chosen": true
+                          }
+                        ] /* considered_access_paths */
+                      } /* best_access_path */,
+                      "cost_for_plan": 2.0496,
+                      "records_for_plan": 7,
+                      "semijoin_strategy_choice": [
+                      ] /* semijoin_strategy_choice */,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                }
+              ] /* steps */
+            } /* execution_plan_for_potential_materialization */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "D",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0068,
+                      "records": 4,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0068,
+                "records_for_plan": 4,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "rest_of_plan": [
+                  {
+                    "table": "t2",
+                    "best_access_path": {
+                      "considered_access_paths": [
+                        {
+                          "access_type": "table scan",
+                          "cost": 2.0499,
+                          "records": 7,
+                          "chosen": true
+                        }
+                      ] /* considered_access_paths */
+                    } /* best_access_path */,
+                    "cost_for_plan": 4.0567,
+                    "records_for_plan": 28,
+                    "semijoin_strategy_choice": [
+                      {
+                        "strategy": "FirstMatch",
+                        "recompute_best_access_paths": {
+                          "cause": "join_buffering_not_possible",
+                          "tables": [
+                            {
+                              "table": "t2",
+                              "best_access_path": {
+                                "considered_access_paths": [
+                                  {
+                                    "access_type": "table scan",
+                                    "cost": 2.0499,
+                                    "records": 7,
+                                    "chosen": true
+                                  }
+                                ] /* considered_access_paths */
+                              } /* best_access_path */
+                            }
+                          ] /* tables */
+                        } /* recompute_best_access_paths */,
+                        "cost": 4.0567,
+                        "records": 4,
+                        "chosen": true
+                      },
+                      {
+                        "strategy": "MaterializationLookup",
+                        "cost": 4.6064,
+                        "records": 4,
+                        "duplicate_tables_left": false,
+                        "chosen": false
+                      },
+                      {
+                        "strategy": "DuplicatesWeedout",
+                        "cost": 15.257,
+                        "records": 4,
+                        "duplicate_tables_left": false,
+                        "chosen": false
+                      }
+                    ] /* semijoin_strategy_choice */,
+                    "chosen": true
+                  }
+                ] /* rest_of_plan */
+              },
+              {
+                "table": "t2",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0496,
+                      "records": 7,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0496,
+                "records_for_plan": 7,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "pruned_by_heuristic": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "reconsidering_access_paths_for_semijoin": {
+              "strategy": "FirstMatch",
+              "table": "t2",
+              "best_access_path": {
+                "considered_access_paths": [
+                  {
+                    "access_type": "table scan",
+                    "cost": 8.1982,
+                    "records": 7,
+                    "chosen": true
+                  }
+                ] /* considered_access_paths */
+              } /* best_access_path */
+            } /* reconsidering_access_paths_for_semijoin */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "((`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
+              "attached_conditions": [
+                {
+                  "table": "D",
+                  "attached": "(`test`.`D`.`d` = `f1`())"
+                },
+                {
+                  "table": "t2",
+                  "attached": "((`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`test`.`D`.`d` = (res@1 + 1))",
+              "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+              "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+              "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`D`.`d`)"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+              {
+                "condition": "D.d= (res@1 + 1)",
+                "null_rejecting": false
+              }
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "D",
+                "more_range_optimizer_trace": {
+                  "TODO?": "yes!"
+                } /* more_range_optimizer_trace */
+              }
+            ] /* records_estimation */
+          }
+        ] /* steps */,
+        "empty_result": {
+          "cause": "no matching row in const table"
+        } /* empty_result */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+execute stmt using @param;
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+select TRACE into @trace2 from information_schema.OPTIMIZER_TRACE;
+select @trace=@trace2;
+@trace=@trace2
+1
+drop function f1;
+drop procedure p1;
+drop trigger trg1;
+create view v1 as select * from t1 where id < "c";
+explain select * from v1 where id="b";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "explain select * from v1 where id="b"",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "((`test`.`t1`.`id` = 'b') and (`test`.`t1`.`id` < 'c'))",
+              "after_equality_propagation": "(('b' < 'c') and multiple equal('b', `test`.`t1`.`id`))",
+              "after_constant_propagation": "(('b' < 'c') and multiple equal('b', `test`.`t1`.`id`))",
+              "after_trivial_conditions_removal": "multiple equal('b', `test`.`t1`.`id`)"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(`test`.`t1`.`id` = 'b')",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "(`test`.`t1`.`id` = 'b')"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+drop view v1;
+create view v1 as select * from t1 where id < "c" limit 2;
+explain select * from v1 where id="b";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "explain select * from v1 where id="b"",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 2,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 2,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`test`.`t1`.`id` < 'c')",
+              "after_equality_propagation": "(`test`.`t1`.`id` < 'c')",
+              "after_constant_propagation": "(`test`.`t1`.`id` < 'c')",
+              "after_trivial_conditions_removal": "(`test`.`t1`.`id` < 'c')"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(`test`.`t1`.`id` < 'c')",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "(`test`.`t1`.`id` < 'c')"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`v1`.`id` = 'b')",
+              "after_equality_propagation": "multiple equal('b', `v1`.`id`)",
+              "after_constant_propagation": "multiple equal('b', `v1`.`id`)",
+              "after_trivial_conditions_removal": "multiple equal('b', `v1`.`id`)"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "v1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 10
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "v1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 10.1,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 10.1,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(`v1`.`id` = 'b')",
+              "attached_conditions": [
+                {
+                  "table": "v1",
+                  "attached": "(`v1`.`id` = 'b')"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+drop view v1;
+select * from information_schema.SESSION_VARIABLES where
+VARIABLE_NAME="optimizer_trace";
+VARIABLE_NAME	VARIABLE_VALUE
+OPTIMIZER_TRACE	enabled=on,end_marker=on,one_line=off,skip_plan=off
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "select * from information_schema.SESSION_VARIABLES where
+VARIABLE_NAME="optimizer_trace"",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+              "after_equality_propagation": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+              "after_constant_propagation": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+              "after_trivial_conditions_removal": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "SESSION_VARIABLES",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 10
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "SESSION_VARIABLES",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 10.1,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 10.1,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+              "attached_conditions": [
+                {
+                  "table": "SESSION_VARIABLES",
+                  "attached": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+drop table t1,t2;
 DROP TABLE C,D;
 set optimizer_trace=default;

=== modified file 'mysql-test/r/optimizer_trace_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_ps_prot.result	2010-09-04 17:40:51 +0000
+++ b/mysql-test/r/optimizer_trace_ps_prot.result	2010-09-08 10:27:44 +0000
@@ -3432,5 +3432,2247 @@ QUERY_ID	TRACE
     }
   ] /* steps */
 }
+create table t1 (
+id   char(16) not null default '',
+data int not null
+);
+create table t2 (
+s   char(16),
+i   int,
+d   double
+);
+insert into t1 values ("a", 1), ("b", 2) |
+insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
+create function f1() returns int
+return (select sum(data) from t1)|
+select f1()|
+f1()
+3
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "select f1()",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 2,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 2,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+select s, f1() from t2 order by s desc|
+s	f1()
+c	3
+b	3
+a	3
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "select s, f1() from t2 order by s desc",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t2",
+                "table_scan": {
+                  "records": 3,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t2",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0212,
+                      "records": 3,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0212,
+                "records_for_plan": 3,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t2",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 2,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 2,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 2,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 2,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 2,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 2,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+select * from D where d in (select f1() from t2 where s="c")|
+d
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "select * from D where d in (select f1() from t2 where s="c")",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+          {
+            "join_preparation": {
+              "select #": 2,
+              "steps": [
+                {
+                  "transformation": {
+                    "select #": 2,
+                    "from": "IN (SELECT)",
+                    "to": "semijoin",
+                    "chosen": true
+                  } /* transformation */
+                }
+              ] /* steps */
+            } /* join_preparation */
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "transformation": {
+              "select #": 2,
+              "from": "IN (SELECT)",
+              "to": "semijoin",
+              "chosen": true
+            } /* transformation */
+          },
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(1 and (`test`.`t2`.`s` = 'c') and (`test`.`D`.`d` = `f1`()))",
+              "after_equality_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+              "after_constant_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+              "after_trivial_conditions_removal": "(multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+              {
+                "condition": "D.d= `f1`()",
+                "null_rejecting": false
+              }
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "D",
+                "more_range_optimizer_trace": {
+                  "TODO?": "yes!"
+                } /* more_range_optimizer_trace */
+              },
+              {
+                "table": "t2",
+                "table_scan": {
+                  "records": 3,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "pulled_out_semijoin_tables": [
+            ] /* pulled_out_semijoin_tables */
+          },
+          {
+            "execution_plan_for_potential_materialization": {
+              "steps": [
+                {
+                  "considered_execution_plans": [
+                    {
+                      "table": "t2",
+                      "best_access_path": {
+                        "considered_access_paths": [
+                          {
+                            "access_type": "table scan",
+                            "cost": 2.0212,
+                            "records": 3,
+                            "chosen": true
+                          }
+                        ] /* considered_access_paths */
+                      } /* best_access_path */,
+                      "cost_for_plan": 2.0212,
+                      "records_for_plan": 3,
+                      "semijoin_strategy_choice": [
+                      ] /* semijoin_strategy_choice */,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                }
+              ] /* steps */
+            } /* execution_plan_for_potential_materialization */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t2",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0212,
+                      "records": 3,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0212,
+                "records_for_plan": 3,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "rest_of_plan": [
+                  {
+                    "table": "D",
+                    "best_access_path": {
+                      "considered_access_paths": [
+                        {
+                          "access_type": "index",
+                          "index": "d",
+                          "cost": 3,
+                          "records": 1,
+                          "chosen": true
+                        },
+                        {
+                          "access_type": "table scan",
+                          "cost": 2.6076,
+                          "records": 1,
+                          "chosen": true
+                        }
+                      ] /* considered_access_paths */
+                    } /* best_access_path */,
+                    "cost_for_plan": 4.6289,
+                    "records_for_plan": 3,
+                    "semijoin_strategy_choice": [
+                      {
+                        "strategy": "DuplicatesWeedout",
+                        "cost": 5.2289,
+                        "records": 1,
+                        "duplicate_tables_left": true,
+                        "chosen": true
+                      }
+                    ] /* semijoin_strategy_choice */,
+                    "chosen": true
+                  }
+                ] /* rest_of_plan */
+              },
+              {
+                "table": "D",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "index",
+                      "index": "d",
+                      "cost": 1,
+                      "records": 1,
+                      "chosen": true
+                    },
+                    {
+                      "access_type": "table scan",
+                      "cost": 2,
+                      "records": 4,
+                      "chosen": false
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 1,
+                "records_for_plan": 1,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "rest_of_plan": [
+                  {
+                    "table": "t2",
+                    "best_access_path": {
+                      "considered_access_paths": [
+                        {
+                          "access_type": "table scan",
+                          "cost": 2.0213,
+                          "records": 3,
+                          "chosen": true
+                        }
+                      ] /* considered_access_paths */
+                    } /* best_access_path */,
+                    "cost_for_plan": 3.0213,
+                    "records_for_plan": 3,
+                    "semijoin_strategy_choice": [
+                      {
+                        "strategy": "FirstMatch",
+                        "recompute_best_access_paths": {
+                          "cause": "join_buffering_not_possible",
+                          "tables": [
+                            {
+                              "table": "t2",
+                              "best_access_path": {
+                                "considered_access_paths": [
+                                  {
+                                    "access_type": "table scan",
+                                    "cost": 2.0213,
+                                    "records": 3,
+                                    "chosen": true
+                                  }
+                                ] /* considered_access_paths */
+                              } /* best_access_path */
+                            }
+                          ] /* tables */
+                        } /* recompute_best_access_paths */,
+                        "cost": 3.0213,
+                        "records": 1,
+                        "chosen": true
+                      },
+                      {
+                        "strategy": "MaterializationLookup",
+                        "cost": 3.2212,
+                        "records": 1,
+                        "duplicate_tables_left": false,
+                        "chosen": false
+                      },
+                      {
+                        "strategy": "DuplicatesWeedout",
+                        "cost": 3.6213,
+                        "records": 1,
+                        "duplicate_tables_left": false,
+                        "chosen": false
+                      }
+                    ] /* semijoin_strategy_choice */,
+                    "chosen": true
+                  }
+                ] /* rest_of_plan */
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "reconsidering_access_paths_for_semijoin": {
+              "strategy": "FirstMatch",
+              "table": "t2",
+              "best_access_path": {
+                "considered_access_paths": [
+                  {
+                    "access_type": "table scan",
+                    "cost": 2.0212,
+                    "records": 3,
+                    "chosen": true
+                  }
+                ] /* considered_access_paths */
+              } /* best_access_path */
+            } /* reconsidering_access_paths_for_semijoin */
+          },
+          {
+            "join_preparation": {
+              "select #": 2,
+              "steps": [
+              ] /* steps */
+            } /* join_preparation */
+          },
+          {
+            "join_optimization": {
+              "select #": 2,
+              "steps": [
+                {
+                  "constant_tables": [
+                  ] /* constant_tables */,
+                  "records_estimation": [
+                    {
+                      "table": "t1",
+                      "table_scan": {
+                        "records": 2,
+                        "cost": 2
+                      } /* table_scan */
+                    }
+                  ] /* records_estimation */
+                },
+                {
+                  "considered_execution_plans": [
+                    {
+                      "table": "t1",
+                      "best_access_path": {
+                        "considered_access_paths": [
+                          {
+                            "access_type": "table scan",
+                            "cost": 2.0103,
+                            "records": 2,
+                            "chosen": true
+                          }
+                        ] /* considered_access_paths */
+                      } /* best_access_path */,
+                      "cost_for_plan": 2.0103,
+                      "records_for_plan": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                },
+                {
+                  "attaching_conditions_to_tables": {
+                    "original_condition": "<null>",
+                    "attached_conditions": [
+                      {
+                        "table": "t1",
+                        "attached": "<null>"
+                      }
+                    ] /* attached_conditions */
+                  } /* attaching_conditions_to_tables */
+                }
+              ] /* steps */
+            } /* join_optimization */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
+              "attached_conditions": [
+                {
+                  "table": "D",
+                  "attached": "(`test`.`D`.`d` = `f1`())"
+                },
+                {
+                  "table": "t2",
+                  "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+create procedure p1(arg char(1))
+begin
+declare res int;
+select d into res from D where d in (select f1() from t2 where s=arg);
+select d+1 into res from D where d=res+1;
+end|
+call p1("c")|
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "call p1("c")",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+          {
+            "join_preparation": {
+              "select #": 2,
+              "steps": [
+                {
+                  "transformation": {
+                    "select #": 2,
+                    "from": "IN (SELECT)",
+                    "to": "semijoin",
+                    "chosen": true
+                  } /* transformation */
+                }
+              ] /* steps */
+            } /* join_preparation */
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "transformation": {
+              "select #": 2,
+              "from": "IN (SELECT)",
+              "to": "semijoin",
+              "chosen": true
+            } /* transformation */
+          },
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(1 and (`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
+              "after_equality_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+              "after_constant_propagation": "(1 and multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))",
+              "after_trivial_conditions_removal": "(multiple equal(arg@0, `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`D`.`d`))"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+              {
+                "condition": "D.d= `f1`()",
+                "null_rejecting": false
+              }
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "D",
+                "more_range_optimizer_trace": {
+                  "TODO?": "yes!"
+                } /* more_range_optimizer_trace */
+              },
+              {
+                "table": "t2",
+                "table_scan": {
+                  "records": 3,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "pulled_out_semijoin_tables": [
+            ] /* pulled_out_semijoin_tables */
+          },
+          {
+            "execution_plan_for_potential_materialization": {
+              "steps": [
+                {
+                  "considered_execution_plans": [
+                    {
+                      "table": "t2",
+                      "best_access_path": {
+                        "considered_access_paths": [
+                          {
+                            "access_type": "table scan",
+                            "cost": 2.0212,
+                            "records": 3,
+                            "chosen": true
+                          }
+                        ] /* considered_access_paths */
+                      } /* best_access_path */,
+                      "cost_for_plan": 2.0212,
+                      "records_for_plan": 3,
+                      "semijoin_strategy_choice": [
+                      ] /* semijoin_strategy_choice */,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                }
+              ] /* steps */
+            } /* execution_plan_for_potential_materialization */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t2",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0212,
+                      "records": 3,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0212,
+                "records_for_plan": 3,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "rest_of_plan": [
+                  {
+                    "table": "D",
+                    "best_access_path": {
+                      "considered_access_paths": [
+                        {
+                          "access_type": "index",
+                          "index": "d",
+                          "cost": 3,
+                          "records": 1,
+                          "chosen": true
+                        },
+                        {
+                          "access_type": "table scan",
+                          "cost": 2.6076,
+                          "records": 1,
+                          "chosen": true
+                        }
+                      ] /* considered_access_paths */
+                    } /* best_access_path */,
+                    "cost_for_plan": 4.6289,
+                    "records_for_plan": 3,
+                    "semijoin_strategy_choice": [
+                      {
+                        "strategy": "DuplicatesWeedout",
+                        "cost": 5.2289,
+                        "records": 1,
+                        "duplicate_tables_left": true,
+                        "chosen": true
+                      }
+                    ] /* semijoin_strategy_choice */,
+                    "chosen": true
+                  }
+                ] /* rest_of_plan */
+              },
+              {
+                "table": "D",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "index",
+                      "index": "d",
+                      "cost": 1,
+                      "records": 1,
+                      "chosen": true
+                    },
+                    {
+                      "access_type": "table scan",
+                      "cost": 2,
+                      "records": 4,
+                      "chosen": false
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 1,
+                "records_for_plan": 1,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "rest_of_plan": [
+                  {
+                    "table": "t2",
+                    "best_access_path": {
+                      "considered_access_paths": [
+                        {
+                          "access_type": "table scan",
+                          "cost": 2.0213,
+                          "records": 3,
+                          "chosen": true
+                        }
+                      ] /* considered_access_paths */
+                    } /* best_access_path */,
+                    "cost_for_plan": 3.0213,
+                    "records_for_plan": 3,
+                    "semijoin_strategy_choice": [
+                      {
+                        "strategy": "FirstMatch",
+                        "recompute_best_access_paths": {
+                          "cause": "join_buffering_not_possible",
+                          "tables": [
+                            {
+                              "table": "t2",
+                              "best_access_path": {
+                                "considered_access_paths": [
+                                  {
+                                    "access_type": "table scan",
+                                    "cost": 2.0213,
+                                    "records": 3,
+                                    "chosen": true
+                                  }
+                                ] /* considered_access_paths */
+                              } /* best_access_path */
+                            }
+                          ] /* tables */
+                        } /* recompute_best_access_paths */,
+                        "cost": 3.0213,
+                        "records": 1,
+                        "chosen": true
+                      },
+                      {
+                        "strategy": "MaterializationLookup",
+                        "cost": 3.2212,
+                        "records": 1,
+                        "duplicate_tables_left": false,
+                        "chosen": false
+                      },
+                      {
+                        "strategy": "DuplicatesWeedout",
+                        "cost": 3.6213,
+                        "records": 1,
+                        "duplicate_tables_left": false,
+                        "chosen": false
+                      }
+                    ] /* semijoin_strategy_choice */,
+                    "chosen": true
+                  }
+                ] /* rest_of_plan */
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "reconsidering_access_paths_for_semijoin": {
+              "strategy": "FirstMatch",
+              "table": "t2",
+              "best_access_path": {
+                "considered_access_paths": [
+                  {
+                    "access_type": "table scan",
+                    "cost": 2.0212,
+                    "records": 3,
+                    "chosen": true
+                  }
+                ] /* considered_access_paths */
+              } /* best_access_path */
+            } /* reconsidering_access_paths_for_semijoin */
+          },
+          {
+            "join_preparation": {
+              "select #": 3,
+              "steps": [
+              ] /* steps */
+            } /* join_preparation */
+          },
+          {
+            "join_optimization": {
+              "select #": 3,
+              "steps": [
+                {
+                  "constant_tables": [
+                  ] /* constant_tables */,
+                  "records_estimation": [
+                    {
+                      "table": "t1",
+                      "table_scan": {
+                        "records": 2,
+                        "cost": 2
+                      } /* table_scan */
+                    }
+                  ] /* records_estimation */
+                },
+                {
+                  "considered_execution_plans": [
+                    {
+                      "table": "t1",
+                      "best_access_path": {
+                        "considered_access_paths": [
+                          {
+                            "access_type": "table scan",
+                            "cost": 2.0103,
+                            "records": 2,
+                            "chosen": true
+                          }
+                        ] /* considered_access_paths */
+                      } /* best_access_path */,
+                      "cost_for_plan": 2.0103,
+                      "records_for_plan": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                },
+                {
+                  "attaching_conditions_to_tables": {
+                    "original_condition": "<null>",
+                    "attached_conditions": [
+                      {
+                        "table": "t1",
+                        "attached": "<null>"
+                      }
+                    ] /* attached_conditions */
+                  } /* attaching_conditions_to_tables */
+                }
+              ] /* steps */
+            } /* join_optimization */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))",
+              "attached_conditions": [
+                {
+                  "table": "D",
+                  "attached": "(`test`.`D`.`d` = `f1`())"
+                },
+                {
+                  "table": "t2",
+                  "attached": "((`test`.`D`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`test`.`D`.`d` = (res@1 + 1))",
+              "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+              "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+              "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`D`.`d`)"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+              {
+                "condition": "D.d= (res@1 + 1)",
+                "null_rejecting": false
+              }
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "D",
+                "more_range_optimizer_trace": {
+                  "TODO?": "yes!"
+                } /* more_range_optimizer_trace */
+              }
+            ] /* records_estimation */
+          }
+        ] /* steps */,
+        "empty_result": {
+          "cause": "no matching row in const table"
+        } /* empty_result */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+create trigger trg1 before insert on t2 for each row
+begin
+set new.s=f1();  
+end|
+insert into t2 select d,100,200 from D where d is not null|
+select * from information_schema.OPTIMIZER_TRACE|
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "insert into t2 select d,100,200 from D where d is not null",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`test`.`D`.`d` is not null)",
+              "after_equality_propagation": "(`test`.`D`.`d` is not null)",
+              "after_constant_propagation": "(`test`.`D`.`d` is not null)",
+              "after_trivial_conditions_removal": "(`test`.`D`.`d` is not null)"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "D",
+                "more_range_optimizer_trace": {
+                  "TODO?": "yes!"
+                } /* more_range_optimizer_trace */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "D",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0068,
+                      "records": 4,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0068,
+                "records_for_plan": 4,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(`test`.`D`.`d` is not null)",
+              "attached_conditions": [
+                {
+                  "table": "D",
+                  "attached": "(`test`.`D`.`d` is not null)"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+select * from t2|
+s	i	d
+a	1	1
+b	2	2
+c	3	3
+3	100	200
+3	100	200
+3	100	200
+3	100	200
+prepare stmt from 'call p1(?)';
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "prepare stmt from 'call p1(?)'",
+  "steps": [
+  ] /* steps */
+}
+set @param="c";
+execute stmt using @param;
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+select TRACE into @trace from information_schema.OPTIMIZER_TRACE;
+select @trace;
+@trace
+
+{
+  "top_query": "execute stmt using @param",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(1 and (`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
+              "after_equality_propagation": "(1 and (`test`.`D`.`d` = `f1`()) and multiple equal(arg@0, `test`.`t2`.`s`))",
+              "after_constant_propagation": "(1 and (`test`.`D`.`d` = `f1`()) and multiple equal(arg@0, `test`.`t2`.`s`))",
+              "after_trivial_conditions_removal": "((`test`.`D`.`d` = `f1`()) and multiple equal(arg@0, `test`.`t2`.`s`))"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "D",
+                "table_scan": {
+                  "records": 4,
+                  "cost": 2
+                } /* table_scan */
+              },
+              {
+                "table": "t2",
+                "table_scan": {
+                  "records": 7,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "pulled_out_semijoin_tables": [
+            ] /* pulled_out_semijoin_tables */
+          },
+          {
+            "execution_plan_for_potential_materialization": {
+              "steps": [
+                {
+                  "considered_execution_plans": [
+                    {
+                      "table": "t2",
+                      "best_access_path": {
+                        "considered_access_paths": [
+                          {
+                            "access_type": "table scan",
+                            "cost": 2.0496,
+                            "records": 7,
+                            "chosen": true
+                          }
+                        ] /* considered_access_paths */
+                      } /* best_access_path */,
+                      "cost_for_plan": 2.0496,
+                      "records_for_plan": 7,
+                      "semijoin_strategy_choice": [
+                      ] /* semijoin_strategy_choice */,
+                      "chosen": true
+                    }
+                  ] /* considered_execution_plans */
+                }
+              ] /* steps */
+            } /* execution_plan_for_potential_materialization */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "D",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0068,
+                      "records": 4,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0068,
+                "records_for_plan": 4,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "rest_of_plan": [
+                  {
+                    "table": "t2",
+                    "best_access_path": {
+                      "considered_access_paths": [
+                        {
+                          "access_type": "table scan",
+                          "cost": 2.0499,
+                          "records": 7,
+                          "chosen": true
+                        }
+                      ] /* considered_access_paths */
+                    } /* best_access_path */,
+                    "cost_for_plan": 4.0567,
+                    "records_for_plan": 28,
+                    "semijoin_strategy_choice": [
+                      {
+                        "strategy": "FirstMatch",
+                        "recompute_best_access_paths": {
+                          "cause": "join_buffering_not_possible",
+                          "tables": [
+                            {
+                              "table": "t2",
+                              "best_access_path": {
+                                "considered_access_paths": [
+                                  {
+                                    "access_type": "table scan",
+                                    "cost": 2.0499,
+                                    "records": 7,
+                                    "chosen": true
+                                  }
+                                ] /* considered_access_paths */
+                              } /* best_access_path */
+                            }
+                          ] /* tables */
+                        } /* recompute_best_access_paths */,
+                        "cost": 4.0567,
+                        "records": 4,
+                        "chosen": true
+                      },
+                      {
+                        "strategy": "MaterializationLookup",
+                        "cost": 4.6064,
+                        "records": 4,
+                        "duplicate_tables_left": false,
+                        "chosen": false
+                      },
+                      {
+                        "strategy": "DuplicatesWeedout",
+                        "cost": 15.257,
+                        "records": 4,
+                        "duplicate_tables_left": false,
+                        "chosen": false
+                      }
+                    ] /* semijoin_strategy_choice */,
+                    "chosen": true
+                  }
+                ] /* rest_of_plan */
+              },
+              {
+                "table": "t2",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0496,
+                      "records": 7,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0496,
+                "records_for_plan": 7,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "pruned_by_heuristic": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "reconsidering_access_paths_for_semijoin": {
+              "strategy": "FirstMatch",
+              "table": "t2",
+              "best_access_path": {
+                "considered_access_paths": [
+                  {
+                    "access_type": "table scan",
+                    "cost": 8.1982,
+                    "records": 7,
+                    "chosen": true
+                  }
+                ] /* considered_access_paths */
+              } /* best_access_path */
+            } /* reconsidering_access_paths_for_semijoin */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "((`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))",
+              "attached_conditions": [
+                {
+                  "table": "D",
+                  "attached": "(`test`.`D`.`d` = `f1`())"
+                },
+                {
+                  "table": "t2",
+                  "attached": "((`test`.`t2`.`s` = arg@0) and (`test`.`D`.`d` = `f1`()))"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 3,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 3,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "<null>",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "<null>"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`test`.`D`.`d` = (res@1 + 1))",
+              "after_equality_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+              "after_constant_propagation": "multiple equal((res@1 + 1), `test`.`D`.`d`)",
+              "after_trivial_conditions_removal": "multiple equal((res@1 + 1), `test`.`D`.`d`)"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+              {
+                "condition": "D.d= (res@1 + 1)",
+                "null_rejecting": false
+              }
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "D",
+                "more_range_optimizer_trace": {
+                  "TODO?": "yes!"
+                } /* more_range_optimizer_trace */
+              }
+            ] /* records_estimation */
+          }
+        ] /* steps */,
+        "empty_result": {
+          "cause": "no matching row in const table"
+        } /* empty_result */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+execute stmt using @param;
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+select TRACE into @trace2 from information_schema.OPTIMIZER_TRACE;
+select @trace=@trace2;
+@trace=@trace2
+1
+drop function f1;
+drop procedure p1;
+drop trigger trg1;
+create view v1 as select * from t1 where id < "c";
+explain select * from v1 where id="b";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "explain select * from v1 where id="b"",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "((`test`.`t1`.`id` = 'b') and (`test`.`t1`.`id` < 'c'))",
+              "after_equality_propagation": "(('b' < 'c') and multiple equal('b', `test`.`t1`.`id`))",
+              "after_constant_propagation": "(('b' < 'c') and multiple equal('b', `test`.`t1`.`id`))",
+              "after_trivial_conditions_removal": "multiple equal('b', `test`.`t1`.`id`)"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(`test`.`t1`.`id` = 'b')",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "(`test`.`t1`.`id` = 'b')"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+drop view v1;
+create view v1 as select * from t1 where id < "c" limit 2;
+explain select * from v1 where id="b";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "explain select * from v1 where id="b"",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 2,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 2,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`test`.`t1`.`id` < 'c')",
+              "after_equality_propagation": "(`test`.`t1`.`id` < 'c')",
+              "after_constant_propagation": "(`test`.`t1`.`id` < 'c')",
+              "after_trivial_conditions_removal": "(`test`.`t1`.`id` < 'c')"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 2.0103,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0103,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(`test`.`t1`.`id` < 'c')",
+              "attached_conditions": [
+                {
+                  "table": "t1",
+                  "attached": "(`test`.`t1`.`id` < 'c')"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`v1`.`id` = 'b')",
+              "after_equality_propagation": "multiple equal('b', `v1`.`id`)",
+              "after_constant_propagation": "multiple equal('b', `v1`.`id`)",
+              "after_trivial_conditions_removal": "multiple equal('b', `v1`.`id`)"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "v1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 10
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "v1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 10.1,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 10.1,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(`v1`.`id` = 'b')",
+              "attached_conditions": [
+                {
+                  "table": "v1",
+                  "attached": "(`v1`.`id` = 'b')"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+drop view v1;
+select * from information_schema.SESSION_VARIABLES where
+VARIABLE_NAME="optimizer_trace";
+VARIABLE_NAME	VARIABLE_VALUE
+OPTIMIZER_TRACE	enabled=on,end_marker=on,one_line=off,skip_plan=off
+select * from information_schema.OPTIMIZER_TRACE;
+QUERY_ID	TRACE
+0	
+{
+  "top_query": "select * from information_schema.SESSION_VARIABLES where
+VARIABLE_NAME="optimizer_trace"",
+  "steps": [
+    {
+      "join_preparation": {
+        "select #": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select #": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+              "after_equality_propagation": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+              "after_constant_propagation": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+              "after_trivial_conditions_removal": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "table": "SESSION_VARIABLES",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 10
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "table": "SESSION_VARIABLES",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "table scan",
+                      "cost": 10.1,
+                      "records": 2,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 10.1,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+              "attached_conditions": [
+                {
+                  "table": "SESSION_VARIABLES",
+                  "attached": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}
+drop table t1,t2;
 DROP TABLE C,D;
 set optimizer_trace=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-09-05 16:08:07 +0000
+++ b/sql/sql_select.cc	2010-09-08 10:27:44 +0000
@@ -4970,6 +4970,20 @@ make_join_statistics(JOIN *join, TABLE_L
              (s->table->pos_in_table_list->embedding &&             // (3)
               s->table->pos_in_table_list->embedding->sj_on_expr))) // (3)
         {
+#ifdef NO_OPT_TRACE_FOR_RANGE_OPT
+          /*
+            1) Range optimizer tracing needs to be re-thought
+            2) if we have a condition like
+               field=stored_function_SELECTing_table(),
+            get_quick_record_count() below will evaluate the function and thus
+            this will add a join_preparation object which does not fit well
+            (has a no-key object whereas the current OOA is an object
+            ("records_estimation").
+          */
+          Opt_trace_object(join->thd->opt_trace, "more_range_optimizer_trace").
+            add("yes!", "TODO?");
+          Opt_trace_disable_I_S otd(join->thd->opt_trace, TRUE);
+#endif
           ha_rows records;
           SQL_SELECT *select;
           select= make_select(s->table, found_const_table_map,
@@ -4978,10 +4992,6 @@ make_join_statistics(JOIN *join, TABLE_L
                               1, &error);
           if (!select)
             goto error;
-#ifdef NO_OPT_TRACE_FOR_RANGE_OPT
-          Opt_trace_object(join->thd->opt_trace, "more_range_optimizer_trace").
-            add("yes!", "TODO?");
-#endif
           records= get_quick_record_count(join->thd, select, s->table,
                                           &s->const_keys, join->row_limit);
           s->quick= select->quick;


Attachment: [text/bzr-bundle] bzr/guilhem@mysql.com-20100908102744-onwirlk3x4z11ni9.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (guilhem:3202) Guilhem Bichot8 Sep