List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:November 17 2010 12:27pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3233) WL#5594
View as plain text  
#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-patchcleanup/ based on revid:jorgen.loland@stripped

 3233 Jorgen Loland	2010-11-17
      WL#5594 - Add optimizer traces to the range optimizer
      
      Minor changes as requested by reviewer.

    modified:
      WL4800_TODO.txt
      mysql-test/r/mysqld--help-notwin.result
      mysql-test/r/optimizer_trace_charset.result
      mysql-test/r/optimizer_trace_no_prot.result
      mysql-test/r/optimizer_trace_range.result
      mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result
      mysql-test/t/optimizer_trace_range.test
      sql/opt_range.cc
      sql/opt_range.h
      sql/opt_trace.h
      sql/sql_delete.cc
      sql/sql_help.cc
      sql/sql_select.cc
      sql/sql_update.cc
      sql/sys_vars.cc
=== modified file 'WL4800_TODO.txt'
--- a/WL4800_TODO.txt	2010-11-05 10:20:12 +0000
+++ b/WL4800_TODO.txt	2010-11-17 12:27:39 +0000
@@ -40,4 +40,7 @@ Review comments left:
 - in changelog, mention /*select#*/; in doc, mention use of this (to
 decrypt what "id" is about in EXPLAIN).
 - more checks for non-unique keys?
-
+- investigate/test whether or not we need special case for
+  MYSQL_TYPE_BIT in print_key2
+- revert print_quick() and dbug_dump() to how they are in
+  opt_backporting

=== modified file 'mysql-test/r/mysqld--help-notwin.result'
--- a/mysql-test/r/mysqld--help-notwin.result	2010-11-05 10:20:12 +0000
+++ b/mysql-test/r/mysqld--help-notwin.result	2010-11-17 12:27:39 +0000
@@ -418,8 +418,8 @@ The following options may be given as th
  Enables/disables tracing of selected features of the
  Optimizer:
  optimizer_trace_features=option=val[,option=val...],
- where option is one of {misc, greedy_search} and val is
- one of {on, off, default}
+ where option is one of {misc, greedy_search,
+ range_optimizer} and val is one of {on, off, default}
  --optimizer-trace-limit=# 
  Maximum number of shown optimizer traces
  --optimizer-trace-max-mem-size=# 
@@ -871,7 +871,7 @@ optimizer-prune-level 1
 optimizer-search-depth 62
 optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
 optimizer-trace 
-optimizer-trace-features misc=on,greedy_search=on
+optimizer-trace-features misc=on,greedy_search=on,range_optimizer=on
 optimizer-trace-limit 1
 optimizer-trace-max-mem-size 16384
 optimizer-trace-offset -1

=== modified file 'mysql-test/r/optimizer_trace_charset.result'
--- a/mysql-test/r/optimizer_trace_charset.result	2010-10-22 08:31:43 +0000
+++ b/mysql-test/r/optimizer_trace_charset.result	2010-11-17 12:27:39 +0000
@@ -38,7 +38,7 @@ explain extended select '��� from t1 
                 "table": "t1",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               }
             ] /* records_estimation */
           },
@@ -48,6 +48,10 @@ explain extended select '��� from t1 
               "attached_conditions": [
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -83,7 +87,7 @@ explain extended select 'ÁÂÃÄÅ' fro
                 "table": "t1",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               }
             ] /* records_estimation */
           },
@@ -93,6 +97,10 @@ explain extended select 'ÁÂÃÄÅ' fro
               "attached_conditions": [
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -143,7 +151,7 @@ select * from v1 where v1.col = '���	
                 "table": "t1",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               }
             ] /* records_estimation */
           },
@@ -153,6 +161,10 @@ select * from v1 where v1.col = '���	
               "attached_conditions": [
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -191,7 +203,7 @@ select * from v1 where v1.col = '���	
                 "table": "v1",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               }
             ] /* records_estimation */
           },
@@ -201,6 +213,10 @@ select * from v1 where v1.col = '���	
               "attached_conditions": [
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -333,6 +349,15 @@ select * from t1 where c < '���	{
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "index"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */

=== modified file 'mysql-test/r/optimizer_trace_no_prot.result'
--- a/mysql-test/r/optimizer_trace_no_prot.result	2010-11-11 13:25:53 +0000
+++ b/mysql-test/r/optimizer_trace_no_prot.result	2010-11-17 12:27:39 +0000
@@ -46,7 +46,7 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               }
             ]
           },
@@ -56,6 +56,10 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
               "attached_conditions": [
               ]
             }
+          },
+          {
+            "refine_plan": {
+            }
           }
         ]
       }
@@ -130,18 +134,18 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
 select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
 from information_schema.OPTIMIZER_TRACE;
 (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-49.9339
+50.2096
 set optimizer_trace="one_line=on";
 SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5 ;
 RESULT
 NULL
 select * from information_schema.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
-SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5	{"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = `c`)) AS `RESULT` from `test`.`t5`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"constant_tables": ["t5"],"records_estimation": [{"database": "test","table": "t5","records": 1,"cost": 1,"cause": "system_table"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`t6`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "t6.d= NULL","null_rejecting": true}]},{"constant_tables": [],"records_estimation": [{"database": "test","table": "t6","range_analysis": {"table_scan": {"records": 2,"cost": 4.5034},"potential_range_indices": [{"index": "d","usable": true,"key_parts": ["d"]}],"best_covering_index_scan": {"index": "d","cost": 1.4233,"chosen": true},"impossible_condition": {"table": "t6","field": "d","cause": "comparison_with_null_always_false"},"impossible_range": true},"records": 0,"cause": "impossible_where_condition"}]}],"empty_result": {"cause": "no matching row in const table"}}}]}	0	0
+SELECT (SELECT 1 FROM t6 WHERE d = c) AS RESULT FROM t5	{"steps": [{"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`d` = `c`)) AS `RESULT` from `test`.`t5`"},{"join_preparation": {"select#": 1,"steps": [{"join_preparation": {"select#": 2,"steps": []}}]}},{"join_optimization": {"select#": 1,"steps": [{"constant_tables": ["t5"],"records_estimation": [{"database": "test","table": "t5","records": 1,"cost": 1,"table_type": "system_table"}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions": []}},{"refine_plan": {}}]}},{"join_optimization": {"select#": 2,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`test`.`t6`.`d` = NULL)","after_equality_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_constant_propagation": "multiple equal(NULL, `test`.`t6`.`d`)","after_trivial_conditions_removal": "multiple equal(NULL, `test`.`t6`.`d`)"}},{"ref-optimizer-key-uses": [{"condition": "t6.d= NULL","null_rejecting": true}]},{"constant_tables": [],"records_estimation": [{"database": "test","table": "t6","range_analysis": {"table_scan": {"records": 2,"cost": 4.5034},"potential_range_indices": [{"index": "d","usable": true,"key_parts": ["d"]}],"best_covering_index_scan": {"index": "d","cost": 1.4233,"chosen": true},"impossible_condition": {"table": "t6","field": "d","cause": "comparison_with_null_always_false"},"impossible_range": true},"records": 0,"cause": "impossible_where_condition"}]}],"empty_result": {"cause": "no matching row in const table"}}}]}	0	0
 select (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
 from information_schema.OPTIMIZER_TRACE;
 (1-length(replace(TRACE, " ", ""))/length(TRACE))*100
-6.0281
+5.9947
 set optimizer_trace="one_line=off,end_marker=on";
 EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d = ifnull(c,null)) AS RESULT FROM t5 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -182,7 +186,7 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               }
             ] /* records_estimation */
           },
@@ -192,6 +196,10 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
               "attached_conditions": [
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -301,7 +309,7 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               }
             ] /* records_estimation */
           },
@@ -311,6 +319,10 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
               "attached_conditions": [
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -438,7 +450,7 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               }
             ] /* records_estimation */
           },
@@ -448,6 +460,10 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
               "attached_conditions": [
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -640,6 +656,15 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "",
+                "table": "",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -751,7 +776,7 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               }
             ] /* records_estimation */
           },
@@ -867,6 +892,15 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
                       }
                     ] /* attached_conditions */
                   } /* attaching_conditions_to_tables */
+                },
+                {
+                  "refine_plan": {
+                    "refine_plan_for_table": {
+                      "database": "",
+                      "table": "",
+                      "scan_type": "table"
+                    } /* refine_plan_for_table */
+                  } /* refine_plan */
                 }
               ] /* steps */
             } /* join_optimization */
@@ -884,7 +918,7 @@ select (@query:=QUERY)+NULL, (@trace:=TR
 NULL	NULL
 select length(@trace);
 length(@trace)
-8988
+9316
 set optimizer_trace_max_mem_size=8400;
 select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
 length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
@@ -893,7 +927,7 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
 c
 select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;
 (@missing_bytes:=missing_bytes_beyond_max_mem_size)
-702
+1043
 select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
 (@query2:=QUERY)+NULL	(@trace2:=TRACE)+NULL
 NULL	NULL
@@ -901,7 +935,7 @@ select length(@trace2),
 (length(@trace2) + @missing_bytes) = length(@trace),
 @query2 = @query;
 length(@trace2)	(length(@trace2) + @missing_bytes) = length(@trace)	@query2 = @query
-8286	1	1
+8273	1	1
 select length(@query2) + length(@trace2)
 between (@@optimizer_trace_max_mem_size-100) and (@@optimizer_trace_max_mem_size+100);
 length(@query2) + length(@trace2)
@@ -997,7 +1031,7 @@ explain SELECT c FROM t5 where c+1 in (s
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               },
               {
                 "database": "test",
@@ -1064,7 +1098,7 @@ explain SELECT c FROM t5 where c+1 in (s
                       "best_access_path": {
                         "considered_access_paths": [
                           {
-                            "access_type": "index",
+                            "access_type": "ref",
                             "index": "d",
                             "records": 1,
                             "cost": 1,
@@ -1074,8 +1108,8 @@ explain SELECT c FROM t5 where c+1 in (s
                             "access_type": "scan",
                             "cost": 2,
                             "records": 2,
-                            "chosen": false,
-                            "cause": "index_cheaper"
+                            "cause": "cost",
+                            "chosen": false
                           }
                         ] /* considered_access_paths */
                       } /* best_access_path */,
@@ -1116,7 +1150,7 @@ explain SELECT c FROM t5 where c+1 in (s
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "d",
                       "records": 1,
                       "cost": 1,
@@ -1126,8 +1160,8 @@ explain SELECT c FROM t5 where c+1 in (s
                       "access_type": "scan",
                       "cost": 2,
                       "records": 2,
-                      "chosen": false,
-                      "cause": "index_cheaper"
+                      "cause": "cost",
+                      "chosen": false
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
@@ -1175,6 +1209,14 @@ explain SELECT c FROM t5 where c+1 in (s
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t6"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1250,7 +1292,7 @@ explain SELECT c FROM t5 where c+1 in (s
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               }
             ] /* records_estimation */
           },
@@ -1262,6 +1304,10 @@ explain SELECT c FROM t5 where c+1 in (s
             } /* attaching_conditions_to_tables */
           },
           {
+            "refine_plan": {
+            } /* refine_plan */
+          },
+          {
             "transformation": {
               "select#": 2,
               "from": "IN (SELECT)",
@@ -1355,7 +1401,7 @@ explain SELECT c FROM t5 where c+1 in (s
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "d",
                       "records": 1,
                       "cost": 1,
@@ -1365,8 +1411,8 @@ explain SELECT c FROM t5 where c+1 in (s
                       "access_type": "scan",
                       "cost": 2,
                       "records": 2,
-                      "chosen": false,
-                      "cause": "index_cheaper"
+                      "cause": "cost",
+                      "chosen": false
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
@@ -1387,6 +1433,14 @@ explain SELECT c FROM t5 where c+1 in (s
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t6"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1496,6 +1550,15 @@ explain extended select * from t1 where 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1551,6 +1614,15 @@ explain extended select * from t1 where 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1656,6 +1728,15 @@ explain extended select * from t1 where 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1711,6 +1792,15 @@ explain extended select * from t1 where 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1833,6 +1923,15 @@ explain extended select * from t1 where 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1901,6 +2000,15 @@ explain extended select * from t1 where 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2022,6 +2130,15 @@ explain extended select * from t1 where 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2090,6 +2207,15 @@ explain extended select * from t1 where 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2241,6 +2367,20 @@ explain select * from t1,t2	{
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */,
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2314,6 +2454,20 @@ explain select * from t1,t2	{
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */,
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2478,6 +2632,15 @@ where a1 in (select b1 from t2_16 where 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1_16",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2546,6 +2709,15 @@ where a1 in (select b1 from t2_16 where 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2_16",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2727,6 +2899,20 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */,
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2795,6 +2981,15 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2889,6 +3084,15 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2957,6 +3161,15 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3019,6 +3232,15 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "",
+                "table": "",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3169,6 +3391,15 @@ concat(c1,'y') IN
             } /* attaching_conditions_to_tables */
           },
           {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
+          },
+          {
             "transformation": {
               "select#": 3,
               "from": "IN (SELECT)",
@@ -3238,6 +3469,15 @@ concat(c1,'y') IN
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3293,6 +3533,15 @@ concat(c1,'y') IN
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3337,14 +3586,14 @@ select * from t1,t2	{
                 "table": "t1",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               },
               {
                 "database": "test",
                 "table": "t2",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               }
             ] /* records_estimation */
           },
@@ -3354,6 +3603,10 @@ select * from t1,t2	{
               "attached_conditions": [
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3467,21 +3720,21 @@ trace
                 "table": "t3",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               },
               {
                 "database": "test",
                 "table": "t1",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               },
               {
                 "database": "test",
                 "table": "t2",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               },
               {
                 "database": "test",
@@ -3520,7 +3773,7 @@ trace
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "PRIMARY",
                       "records": 1,
                       "cost": 1,
@@ -3530,8 +3783,8 @@ trace
                       "access_type": "scan",
                       "cost": 2,
                       "records": 2,
-                      "chosen": false,
-                      "cause": "index_cheaper"
+                      "cause": "cost",
+                      "chosen": false
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
@@ -3598,21 +3851,21 @@ trace
                 "table": "t3",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               },
               {
                 "database": "test",
                 "table": "t1",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               },
               {
                 "database": "test",
                 "table": "t2",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               },
               {
                 "database": "test",
@@ -3632,7 +3885,7 @@ trace
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "PRIMARY",
                       "records": 1,
                       "cost": 1,
@@ -3642,8 +3895,8 @@ trace
                       "access_type": "scan",
                       "cost": 2,
                       "records": 2,
-                      "chosen": false,
-                      "cause": "index_cheaper"
+                      "cause": "cost",
+                      "chosen": false
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
@@ -3779,6 +4032,20 @@ select * from t1 left join t2 on t2.a=50
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */,
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3901,6 +4168,15 @@ select * from t1 where (t1.a,t1.b) not i
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3977,6 +4253,15 @@ select * from t1 where (t1.a,t1.b) not i
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4125,6 +4410,20 @@ trace
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */,
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4187,6 +4486,8 @@ update t6 set d=5 where d is NULL	{
       "expanded_query": "/* select#1 */ select `d` AS `d` from `test`.`t6` where isnull(`d`)"
     },
     {
+      "database": "test",
+      "table": "t6",
       "range_analysis": {
         "table_scan": {
           "records": 2,
@@ -4233,8 +4534,8 @@ update t6 set d=5 where d is NULL	{
               "NULL <= d <= NULL"
             ] /* ranges */
           } /* range_access_plan */,
-          "records_in_plan": 1,
-          "cost_of_plan": 2.21,
+          "records_for_plan": 1,
+          "cost_for_plan": 2.21,
           "chosen": true
         } /* chosen_range_access_summary */
       } /* range_analysis */
@@ -4250,6 +4551,8 @@ delete from t6 where d=5	{
       "expanded_query": "/* select#1 */ select  from dual where (`d` = 5)"
     },
     {
+      "database": "test",
+      "table": "t6",
       "range_analysis": {
         "table_scan": {
           "records": 2,
@@ -4292,8 +4595,8 @@ delete from t6 where d=5	{
               "5 <= d <= 5"
             ] /* ranges */
           } /* range_access_plan */,
-          "records_in_plan": 1,
-          "cost_of_plan": 2.21,
+          "records_for_plan": 1,
+          "cost_for_plan": 2.21,
           "chosen": true
         } /* chosen_range_access_summary */
       } /* range_analysis */
@@ -4429,6 +4732,15 @@ insert into t6 select * from t6 where d>
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t6",
+                "scan_type": "index"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4477,7 +4789,7 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               },
               {
                 "database": "test",
@@ -4528,8 +4840,8 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
                         "7000 < d"
                       ] /* ranges */
                     } /* range_access_plan */,
-                    "records_in_plan": 1,
-                    "cost_of_plan": 2.21,
+                    "records_for_plan": 1,
+                    "cost_for_plan": 2.21,
                     "chosen": true
                   } /* chosen_range_access_summary */
                 } /* range_analysis */
@@ -4568,6 +4880,15 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t6",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4616,7 +4937,7 @@ delete t6 from t5, t6 where d>7000	{
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "cause": "system_table"
+                "table_type": "system_table"
               },
               {
                 "database": "test",
@@ -4701,6 +5022,15 @@ delete t6 from t5, t6 where d>7000	{
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t6",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4921,6 +5251,15 @@ select sum(data) into ret from t1	{
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4997,6 +5336,15 @@ select sum(data) into ret from t1	{
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5070,6 +5418,15 @@ select sum(data) into ret from t1	{
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5263,7 +5620,7 @@ select * from t6 where d in (select f1()
                     "best_access_path": {
                       "considered_access_paths": [
                         {
-                          "access_type": "index",
+                          "access_type": "ref",
                           "index": "d",
                           "records": 1,
                           "cost": 3,
@@ -5299,7 +5656,7 @@ select * from t6 where d in (select f1()
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "d",
                       "records": 1,
                       "cost": 1,
@@ -5309,8 +5666,8 @@ select * from t6 where d in (select f1()
                       "access_type": "scan",
                       "cost": 2,
                       "records": 4,
-                      "chosen": false,
-                      "cause": "index_cheaper"
+                      "cause": "cost",
+                      "chosen": false
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
@@ -5416,6 +5773,19 @@ select * from t6 where d in (select f1()
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t6"
+              } /* refine_plan_for_table */,
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5435,6 +5805,8 @@ delete from t1 where id="z"	{
       "expanded_query": "/* select#1 */ select  from dual where (`id` = 'z')"
     },
     {
+      "database": "test",
+      "table": "t1",
       "range_analysis": {
         "table_scan": {
           "records": 3,
@@ -5507,6 +5879,15 @@ select sum(data) into ret from t1	{
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5524,6 +5905,8 @@ delete from t1 where id="z"	{
       "expanded_query": "/* select#1 */ select  from dual where (`id` = 'z')"
     },
     {
+      "database": "test",
+      "table": "t1",
       "range_analysis": {
         "table_scan": {
           "records": 3,
@@ -5694,6 +6077,15 @@ select sum(data) into ret from t1	{
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5989,7 +6381,7 @@ select d into res from t6 where d in (se
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "d",
                       "records": 1,
                       "cost": 1,
@@ -5999,8 +6391,8 @@ select d into res from t6 where d in (se
                       "access_type": "scan",
                       "cost": 2,
                       "records": 4,
-                      "chosen": false,
-                      "cause": "index_cheaper"
+                      "cause": "cost",
+                      "chosen": false
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
@@ -6126,6 +6518,19 @@ select d into res from t6 where d in (se
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t6"
+              } /* refine_plan_for_table */,
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -6145,6 +6550,8 @@ delete from t1 where id="z"	{
       "expanded_query": "/* select#1 */ select  from dual where (`id` = 'z')"
     },
     {
+      "database": "test",
+      "table": "t1",
       "range_analysis": {
         "table_scan": {
           "records": 3,
@@ -6217,6 +6624,15 @@ select sum(data) into ret from t1	{
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -6325,6 +6741,15 @@ explain select * from v1 where id="b"	{
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -6366,6 +6791,8 @@ delete from v1 where data=100	{
       "expanded_query": "/* select#1 */ select  from dual where (`data` = 100)"
     },
     {
+      "database": "test",
+      "table": "v1",
       "range_analysis": {
         "table_scan": {
           "records": 3,
@@ -6467,6 +6894,15 @@ explain select * from v1 where id="b"	{
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -6542,6 +6978,15 @@ explain select * from v1 where id="b"	{
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "",
+                "table": "v1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -6549,17 +6994,17 @@ explain select * from v1 where id="b"	{
   ] /* steps */
 }	0	0
 drop view v1;
-select * from information_schema.SESSION_VARIABLES where
+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
 select * from information_schema.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
-select * from information_schema.SESSION_VARIABLES where
+select * from information_schema.session_variables where
 VARIABLE_NAME="optimizer_trace"	{
   "steps": [
     {
-      "expanded_query": "/* select#1 */ select `*` AS `*` from `information_schema`.`SESSION_VARIABLES` where (`VARIABLE_NAME` = 'optimizer_trace')"
+      "expanded_query": "/* select#1 */ select `*` AS `*` from `information_schema`.`session_variables` where (`VARIABLE_NAME` = 'optimizer_trace')"
     },
     {
       "join_preparation": {
@@ -6575,10 +7020,10 @@ VARIABLE_NAME="optimizer_trace"	{
           {
             "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')"
+              "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 */
           },
           {
@@ -6591,7 +7036,7 @@ VARIABLE_NAME="optimizer_trace"	{
             "records_estimation": [
               {
                 "database": "information_schema",
-                "table": "SESSION_VARIABLES",
+                "table": "session_variables",
                 "table_scan": {
                   "records": 2,
                   "cost": 10
@@ -6603,7 +7048,7 @@ VARIABLE_NAME="optimizer_trace"	{
             "considered_execution_plans": [
               {
                 "database": "information_schema",
-                "table": "SESSION_VARIABLES",
+                "table": "session_variables",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
@@ -6623,15 +7068,24 @@ VARIABLE_NAME="optimizer_trace"	{
           },
           {
             "attaching_conditions_to_tables": {
-              "original_condition": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')",
+              "original_condition": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')",
               "attached_conditions": [
                 {
                   "database": "information_schema",
-                  "table": "SESSION_VARIABLES",
-                  "attached": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')"
+                  "table": "session_variables",
+                  "attached": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "information_schema",
+                "table": "session_variables",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -6735,11 +7189,25 @@ select TRACE into dumpfile 'MYSQLTEST_VA
                 }
               ]
             }
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "",
+                "table": "",
+                "scan_type": "table"
+              }
+            }
           }
         ]
       }
     }
   ]
-}drop table t1,t2;
+}set optimizer_switch='default,index_merge=on,index_merge=off,default';
+ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=off,default'
+select @@optimizer_switch;
+@@optimizer_switch
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on
+drop table t1,t2;
 DROP TABLE t5,t6;
 set optimizer_trace=default;

=== modified file 'mysql-test/r/optimizer_trace_range.result'
--- a/mysql-test/r/optimizer_trace_range.result	2010-10-22 13:36:50 +0000
+++ b/mysql-test/r/optimizer_trace_range.result	2010-11-17 12:27:39 +0000
@@ -160,8 +160,8 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5 
                         "1020 < key2"
                       ] /* ranges */
                     } /* range_access_plan */,
-                    "records_in_plan": 47,
-                    "cost_of_plan": 58.41,
+                    "records_for_plan": 47,
+                    "cost_for_plan": 58.41,
                     "chosen": true
                   } /* chosen_range_access_summary */
                 } /* range_analysis */
@@ -200,12 +200,139 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
     }
   ] /* steps */
 }	0	0
+set @@optimizer_trace_features="range_optimizer=off";
+
+EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	i2	i2	4	NULL	47	Using index condition; Using MRR
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020	{
+  "steps": [
+    {
+      "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where ((`key2` < 5) or (`key2` > 1020))"
+    },
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))",
+              "after_equality_propagation": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))",
+              "after_constant_propagation": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))",
+              "after_trivial_conditions_removal": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "database": "test",
+                "table": "t1",
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 1024,
+                    "cost": 217.15
+                  } /* table_scan */,
+                  "potential_range_indices": "...",
+                  "group_index_range": "...",
+                  "analyzing_range_alternatives": "...",
+                  "chosen_range_access_summary": {
+                    "range_access_plan": {
+                      "type": "range_scan",
+                      "index": "i2",
+                      "records": 47,
+                      "ranges": [
+                        "key2 < 5",
+                        "1020 < key2"
+                      ] /* ranges */
+                    } /* range_access_plan */,
+                    "records_for_plan": 47,
+                    "cost_for_plan": 58.41,
+                    "chosen": true
+                  } /* chosen_range_access_summary */
+                } /* range_analysis */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "database": "test",
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "range",
+                      "records": 47,
+                      "cost": 58.41,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 58.41,
+                "records_for_plan": 47,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))",
+              "attached_conditions": [
+                {
+                  "database": "test",
+                  "table": "t1",
+                  "attached": "((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}	0	0
+set @@optimizer_trace_features="range_optimizer=on";
 
 EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -370,8 +497,8 @@ EXPLAIN SELECT * FROM t1 WHERE key1 < 3 
                         }
                       ] /* index_merge_of */
                     } /* range_access_plan */,
-                    "records_in_plan": 45,
-                    "cost_of_plan": 58.363,
+                    "records_for_plan": 45,
+                    "cost_for_plan": 58.363,
                     "chosen": true
                   } /* chosen_range_access_summary */
                 } /* range_analysis */
@@ -410,6 +537,15 @@ EXPLAIN SELECT * FROM t1 WHERE key1 < 3 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -497,12 +633,12 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
                       {
                         "index": "PRIMARY",
                         "usable": false,
-                        "covering": false
+                        "cause": "not_covering"
                       },
                       {
                         "index": "i1b",
                         "usable": false,
-                        "covering": false
+                        "cause": "not_covering"
                       },
                       {
                         "index": "i2_1",
@@ -525,9 +661,11 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
                     "min_aggregate": true,
                     "max_aggregate": false,
                     "distinct_aggregate": false,
-                    "key_parts_used_for_access": 1,
                     "records": 103,
                     "cost": 50.6,
+                    "key_parts_used_for_access": [
+                      "key2"
+                    ] /* key_parts_used_for_access */,
                     "ranges": [
                     ] /* ranges */,
                     "chosen": true
@@ -540,14 +678,16 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
                       "min_aggregate": true,
                       "max_aggregate": false,
                       "distinct_aggregate": false,
-                      "key_parts_used_for_access": 1,
                       "records": 103,
                       "cost": 50.6,
+                      "key_parts_used_for_access": [
+                        "key2"
+                      ] /* key_parts_used_for_access */,
                       "ranges": [
                       ] /* ranges */
                     } /* range_access_plan */,
-                    "records_in_plan": 103,
-                    "cost_of_plan": 50.6,
+                    "records_for_plan": 103,
+                    "cost_for_plan": 50.6,
                     "chosen": true
                   } /* chosen_range_access_summary */
                 } /* range_analysis */
@@ -587,6 +727,15 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -680,12 +829,12 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
                       {
                         "index": "PRIMARY",
                         "usable": false,
-                        "covering": false
+                        "cause": "not_covering"
                       },
                       {
                         "index": "i1b",
                         "usable": false,
-                        "covering": false
+                        "cause": "not_covering"
                       },
                       {
                         "index": "i2_1",
@@ -714,9 +863,11 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
                     "min_aggregate": true,
                     "max_aggregate": false,
                     "distinct_aggregate": false,
-                    "key_parts_used_for_access": 1,
                     "records": 5,
                     "cost": 31,
+                    "key_parts_used_for_access": [
+                      "key2"
+                    ] /* key_parts_used_for_access */,
                     "ranges": [
                       "key2 < 5"
                     ] /* ranges */,
@@ -762,8 +913,8 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
                         "key2 < 5"
                       ] /* ranges */
                     } /* range_access_plan */,
-                    "records_in_plan": 47,
-                    "cost_of_plan": 11.724,
+                    "records_for_plan": 47,
+                    "cost_for_plan": 11.724,
                     "chosen": true
                   } /* chosen_range_access_summary */
                 } /* range_analysis */
@@ -803,6 +954,15 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -937,8 +1097,8 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1 
                         "1 <= key2 <= 1"
                       ] /* ranges */
                     } /* range_access_plan */,
-                    "records_in_plan": 10,
-                    "cost_of_plan": 13.01,
+                    "records_for_plan": 10,
+                    "cost_for_plan": 13.01,
                     "chosen": true
                   } /* chosen_range_access_summary */
                 } /* range_analysis */
@@ -953,14 +1113,14 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1 
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "i2_1",
                       "records": 10,
                       "cost": 10,
                       "chosen": true
                     },
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "i2_2",
                       "records": 10,
                       "cost": 10,
@@ -968,8 +1128,8 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1 
                     },
                     {
                       "access_type": "range",
-                      "chosen": false,
-                      "cause": "heuristic_index_must_be_cheaper"
+                      "cause": "heuristic_index_cheaper",
+                      "chosen": false
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
@@ -990,6 +1150,14 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1195,8 +1363,8 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
                         }
                       ] /* union_of */
                     } /* range_access_plan */,
-                    "records_in_plan": 2,
-                    "cost_of_plan": 6.327,
+                    "records_for_plan": 2,
+                    "cost_for_plan": 6.327,
                     "chosen": true
                   } /* chosen_range_access_summary */
                 } /* range_analysis */
@@ -1235,6 +1403,15 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1360,6 +1537,15 @@ EXPLAIN SELECT * FROM t2 WHERE key2_1 < 
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1490,8 +1676,8 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
                         "5 <= key1a <= 5 : key1b < 10"
                       ] /* ranges */
                     } /* range_access_plan */,
-                    "records_in_plan": 1,
-                    "cost_of_plan": 2.21,
+                    "records_for_plan": 1,
+                    "cost_for_plan": 2.21,
                     "chosen": true
                   } /* chosen_range_access_summary */
                 } /* range_analysis */
@@ -1506,7 +1692,7 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "PRIMARY",
                       "records": 10,
                       "cost": 10.24,
@@ -1537,6 +1723,15 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1723,6 +1918,15 @@ EXPLAIN SELECT * FROM t1 WHERE (key1 > 1
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1845,21 +2049,21 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
                       "intersecting_indices": [
                         {
                           "index": "cola",
-                          "used_in_intersect": true,
+                          "usable": true,
                           "matching_records_now": 533,
                           "cumulated_cost": 320.38,
                           "isect_covering_with_this_index": false
                         },
                         {
                           "index": "colb",
-                          "used_in_intersect": true,
+                          "usable": true,
                           "matching_records_now": 32.639,
                           "cumulated_cost": 53.359,
                           "isect_covering_with_this_index": false
                         }
                       ] /* intersecting_indices */,
                       "clustered_pk": {
-                        "cpk_added_to_intersect": false,
+                        "clustered_pk_added_to_intersect": false,
                         "cause": "no_clustered_pk_index"
                       } /* clustered_pk */,
                       "records": 32,
@@ -1874,7 +2078,7 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
                       "records": 32,
                       "cost": 53.359,
                       "covering": false,
-                      "cpk_scan": false,
+                      "clustered_pk_scan": false,
                       "intersect_of": [
                         {
                           "type": "range_scan",
@@ -1894,8 +2098,8 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
                         }
                       ] /* intersect_of */
                     } /* range_access_plan */,
-                    "records_in_plan": 32,
-                    "cost_of_plan": 53.359,
+                    "records_for_plan": 32,
+                    "cost_for_plan": 53.359,
                     "chosen": true
                   } /* chosen_range_access_summary */
                 } /* range_analysis */
@@ -1910,14 +2114,14 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "cola",
                       "records": 533,
                       "cost": 533,
                       "chosen": true
                     },
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "colb",
                       "records": 533,
                       "cost": 533,
@@ -1948,6 +2152,15 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2056,8 +2269,8 @@ o <= cola <= f
 o"
                       ] /* ranges */
                     } /* range_access_plan */,
-                    "records_in_plan": 1,
-                    "cost_of_plan": 2.21,
+                    "records_for_plan": 1,
+                    "cost_for_plan": 2.21,
                     "chosen": true
                   } /* chosen_range_access_summary */
                 } /* range_analysis */
@@ -2072,7 +2285,7 @@ o"
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "cola",
                       "records": 1,
                       "cost": 1,
@@ -2080,8 +2293,8 @@ o"
                     },
                     {
                       "access_type": "range",
-                      "chosen": false,
-                      "cause": "heuristic_index_must_be_cheaper"
+                      "cause": "heuristic_index_cheaper",
+                      "chosen": false
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
@@ -2102,6 +2315,14 @@ o"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": {
+              "refine_plan_for_table": {
+                "database": "test",
+                "table": "t1"
+              } /* refine_plan_for_table */
+            } /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */

=== modified file 'mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result'
--- a/mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result	2010-09-27 08:00:25 +0000
+++ b/mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result	2010-11-17 12:27:39 +0000
@@ -1,45 +1,45 @@
 SET @start_global_value = @@global.optimizer_trace_features;
 SELECT @start_global_value;
 @start_global_value
-misc=on,greedy_search=on
+misc=on,greedy_search=on,range_optimizer=on
 select @@global.optimizer_trace_features;
 @@global.optimizer_trace_features
-misc=on,greedy_search=on
+misc=on,greedy_search=on,range_optimizer=on
 select @@session.optimizer_trace_features;
 @@session.optimizer_trace_features
-misc=on,greedy_search=on
+misc=on,greedy_search=on,range_optimizer=on
 show global variables like 'optimizer_trace_features';
 Variable_name	Value
-optimizer_trace_features	misc=on,greedy_search=on
+optimizer_trace_features	misc=on,greedy_search=on,range_optimizer=on
 show session variables like 'optimizer_trace_features';
 Variable_name	Value
-optimizer_trace_features	misc=on,greedy_search=on
+optimizer_trace_features	misc=on,greedy_search=on,range_optimizer=on
 select * from information_schema.global_variables where variable_name='optimizer_trace_features';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_TRACE_FEATURES	misc=on,greedy_search=on
+OPTIMIZER_TRACE_FEATURES	misc=on,greedy_search=on,range_optimizer=on
 select * from information_schema.session_variables where variable_name='optimizer_trace_features';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_TRACE_FEATURES	misc=on,greedy_search=on
+OPTIMIZER_TRACE_FEATURES	misc=on,greedy_search=on,range_optimizer=on
 set global optimizer_trace_features=2;
 select @@global.optimizer_trace_features;
 @@global.optimizer_trace_features
-misc=off,greedy_search=on
+misc=off,greedy_search=on,range_optimizer=off
 set session optimizer_trace_features=2;
 select @@session.optimizer_trace_features;
 @@session.optimizer_trace_features
-misc=off,greedy_search=on
+misc=off,greedy_search=on,range_optimizer=off
 set global optimizer_trace_features=0;
 select @@global.optimizer_trace_features;
 @@global.optimizer_trace_features
-misc=off,greedy_search=off
+misc=off,greedy_search=off,range_optimizer=off
 set session optimizer_trace_features=0;
 select @@session.optimizer_trace_features;
 @@session.optimizer_trace_features
-misc=off,greedy_search=off
+misc=off,greedy_search=off,range_optimizer=off
 set session optimizer_trace_features=default;
 select @@session.optimizer_trace_features;
 @@session.optimizer_trace_features
-misc=off,greedy_search=off
+misc=off,greedy_search=off,range_optimizer=off
 set global optimizer_trace_features=1.1;
 ERROR 42000: Incorrect argument type to variable 'optimizer_trace_features'
 set global optimizer_trace_features=1e1;
@@ -49,4 +49,4 @@ ERROR 42000: Variable 'optimizer_trace_f
 SET @@global.optimizer_trace_features = @start_global_value;
 SELECT @@global.optimizer_trace_features;
 @@global.optimizer_trace_features
-misc=on,greedy_search=on
+misc=on,greedy_search=on,range_optimizer=on

=== modified file 'mysql-test/t/optimizer_trace_range.test'
--- a/mysql-test/t/optimizer_trace_range.test	2010-10-22 08:31:43 +0000
+++ b/mysql-test/t/optimizer_trace_range.test	2010-11-17 12:27:39 +0000
@@ -64,6 +64,14 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5 
 --echo
 SELECT * FROM information_schema.OPTIMIZER_TRACE;
 
+# multiple ranges on one key, turn off range_optimizer tracing
+set @@optimizer_trace_features="range_optimizer=off";
+--echo
+EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+set @@optimizer_trace_features="range_optimizer=on";
+
 
 # index merge
 --echo

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2010-10-22 13:36:50 +0000
+++ b/sql/opt_range.cc	2010-11-17 12:27:39 +0000
@@ -804,11 +804,13 @@ static void print_ror_scans_arr(TABLE *t
                                 struct st_ror_scan_info **end);
 #endif
 #if !defined(DBUG_OFF) || defined(OPTIMIZER_TRACE)
+#if 0
 static void print_quick(QUICK_SELECT_I *quick, const key_map *needed_reg);
+#endif
 static void append_range(String *out,
-                         const KEY_PART_INFO *key_parts, 
+                         const KEY_PART_INFO *key_parts,
                          const uchar *min_key, const uint16 min_length,
-                         const uchar *max_key, const uint16 max_length, 
+                         const uchar *max_key, const uint16 max_length,
                          const uint flag);
 #endif
 
@@ -1956,16 +1958,14 @@ public:
   static void operator delete(void *ptr, MEM_ROOT *mem_root) { /* Never called */ }
   virtual ~TABLE_READ_PLAN() {}               /* Remove gcc warning */
 
-#ifdef OPTIMIZER_TRACE
   /**
      Add basic info for this TABLE_READ_PLAN to the optimizer trace.
 
      @param param        Parameters for range analysis of this table
      @param trace_object The optimizer trace object the info is appended to
    */
-  virtual void trace_basic_info(const PARAM *param, 
-                                Opt_trace_object *trace_object) const =0;
-#endif  
+  virtual void trace_basic_info(const PARAM *param,
+                                Opt_trace_object *trace_object) const = 0;
 };
 
 /**
@@ -1977,7 +1977,7 @@ public:
   @param[in]  used_length  Key tuple length
 */
 static void
-print_key2(String *out, const KEY_PART_INFO *key_part, const uchar *key, 
+print_key2(String *out, const KEY_PART_INFO *key_part, const uchar *key,
            uint used_length)
 {
   const uchar *key_end= key + used_length;
@@ -1999,7 +1999,7 @@ print_key2(String *out, const KEY_PART_I
          Otherwise, print the key value starting immediately after the
          null-byte
        */
-      if (*key) 
+      if (*key)
       {
         out->append(STRING_WITH_LEN("NULL"));
         continue;
@@ -2009,9 +2009,9 @@ print_key2(String *out, const KEY_PART_I
     }
     field->set_key_image(key, key_part->length);
     if (field->type() == MYSQL_TYPE_BIT)
-      (void) field->val_int_as_str(&tmp, 1);
+      (void) field->val_int_as_str(&tmp, 1); // may change tmp's charset
     else
-      field->val_str(&tmp);
+      field->val_str(&tmp); // may change tmp's charset
     out->append(tmp.ptr(), tmp.length(), tmp.charset());
     if (key + store_length < key_end)
       out->append('\'');
@@ -2030,7 +2030,7 @@ class TRP_RANGE : public TABLE_READ_PLAN
 {
 public:
   SEL_ARG *key; /* set of intervals to be used in "range" method retrieval */
-  uint     key_idx; /* key number in PARAM::key */
+  uint     key_idx; /* key number in PARAM::key and PARAM::real_keynr*/
   uint     mrr_flags; 
   uint     mrr_buf_size;
 
@@ -2053,11 +2053,11 @@ public:
     DBUG_RETURN(quick);
   }
 
-#ifdef OPTIMIZER_TRACE
   void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
   {
+#ifdef OPTIMIZER_TRACE
     DBUG_ASSERT(param->using_real_indexes);
-    uint keynr_in_table= param->real_keynr[key_idx];
+    const uint keynr_in_table= param->real_keynr[key_idx];
 
     const KEY cur_key= param->table->key_info[keynr_in_table];
     const KEY_PART_INFO *key_part= cur_key.key_part;
@@ -2066,15 +2066,15 @@ public:
       add_str("index", cur_key.name, strlen(cur_key.name)).
       add("records", records);
 
-    Opt_trace_array trace_range(param->thd->opt_trace,"ranges");
-    for (const SEL_ARG *current= key; 
-         current; 
+    Opt_trace_array trace_range(param->thd->opt_trace, "ranges");
+    for (const SEL_ARG *current= key;
+         current;
          current= current->next)
     {
       String range_info;
       range_info.set_charset(system_charset_info);
-      for (const SEL_ARG *part= current; 
-           part; 
+      for (const SEL_ARG *part= current;
+           part;
            part= part->next_key_part)
       {
         const KEY_PART_INFO *cur_key_part= key_part + part->part;
@@ -2085,8 +2085,8 @@ public:
       }
       trace_range.add_str(range_info.ptr(), range_info.length());
     }
-  }
 #endif
+  }
 };
 
 typedef struct st_ror_scan_info
@@ -2129,19 +2129,19 @@ public:
   bool is_covering; /* TRUE if no row retrieval phase is necessary */
   double index_scan_costs; /* SUM(cost(index_scan)) */
 
-#ifdef OPTIMIZER_TRACE
   void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
   {
+#ifdef OPTIMIZER_TRACE
     trace->add_str("type", "index_roworder_intersect").
       add("records", records).
       add("cost", read_cost).
       add("covering", is_covering).
-      add("cpk_scan", cpk_scan != NULL);
-      
+      add("clustered_pk_scan", cpk_scan != NULL);
+
     Opt_trace_context *trace_ctx= param->thd->opt_trace;
-    Opt_trace_array ota(trace_ctx,"intersect_of");
-    for (st_ror_scan_info **cur_scan= first_scan; 
-         cur_scan != last_scan; 
+    Opt_trace_array ota(trace_ctx, "intersect_of");
+    for (st_ror_scan_info **cur_scan= first_scan;
+         cur_scan != last_scan;
          cur_scan++)
     {
       const KEY cur_key= param->table->key_info[(*cur_scan)->keynr];
@@ -2153,14 +2153,14 @@ public:
         add("records", (*cur_scan)->records);
 
       Opt_trace_array trace_range(trace_ctx, "ranges");
-      for (const SEL_ARG *current= (*cur_scan)->sel_arg; 
-           current; 
+      for (const SEL_ARG *current= (*cur_scan)->sel_arg;
+           current;
            current= current->next)
       {
         String range_info;
         range_info.set_charset(system_charset_info);
-        for (const SEL_ARG *part= current; 
-             part; 
+        for (const SEL_ARG *part= current;
+             part;
              part= part->next_key_part)
         {
           const KEY_PART_INFO *cur_key_part= key_part + part->part;
@@ -2172,8 +2172,8 @@ public:
         trace_range.add_str(range_info.ptr(), range_info.length());
       }
     }
-  }
 #endif
+  }
 };
 
 
@@ -2193,23 +2193,22 @@ public:
   TABLE_READ_PLAN **first_ror; /* array of ptrs to plans for merged scans */
   TABLE_READ_PLAN **last_ror;  /* end of the above array */
 
-#ifdef OPTIMIZER_TRACE
   void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
   {
+#ifdef OPTIMIZER_TRACE
     trace->add_str("type", "index_roworder_union");
     Opt_trace_array ota(param->thd->opt_trace, "union_of");
-    for (TABLE_READ_PLAN **current= first_ror; 
+    for (TABLE_READ_PLAN **current= first_ror;
          current != last_ror;
          current++)
     {
       Opt_trace_object trp_info(param->thd->opt_trace);
       (*current)->trace_basic_info(param, &trp_info);
     }
-  }
 #endif
+  }
 };
 
-
 /*
   Plan for QUICK_INDEX_MERGE_SELECT scan.
   QUICK_ROR_INTERSECT_SELECT always retrieves full rows, so retrieve_full_rows
@@ -2226,20 +2225,20 @@ public:
   TRP_RANGE **range_scans; /* array of ptrs to plans of merged scans */
   TRP_RANGE **range_scans_end; /* end of the array */
 
-#ifdef OPTIMIZER_TRACE
   void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
   {
+#ifdef OPTIMIZER_TRACE
     trace->add_str("type", "index_merge");
     Opt_trace_array ota(param->thd->opt_trace, "index_merge_of");
-    for (TRP_RANGE **current= range_scans; 
+    for (TRP_RANGE **current= range_scans;
          current != range_scans_end;
          current++)
     {
       Opt_trace_object trp_info(param->thd->opt_trace);
       (*current)->trace_basic_info(param, &trp_info);
     }
-  }
 #endif
+  }
 };
 
 
@@ -2252,13 +2251,15 @@ class TRP_GROUP_MIN_MAX : public TABLE_R
 private:
   bool have_min;             ///< TRUE if there is a MIN function
   bool have_max;             ///< TRUE if there is a MAX function
-  /** TRUE if there is an aggregate distinct function, e.g.
-      "COUNT(DISTINCT x)"
+  /**
+    TRUE if there is an aggregate distinct function, e.g.
+    "COUNT(DISTINCT x)"
    */
   bool have_agg_distinct;    
-  /** The key_part of the only field used by all MIN/MAX functions.
-      Note that TRP_GROUP_MIN_MAX is not used if there are MIN/MAX
-      functions on more than one field.
+  /**
+    The key_part of the only field used by all MIN/MAX functions.
+    Note that TRP_GROUP_MIN_MAX is not used if there are MIN/MAX
+    functions on more than one field.
   */
   KEY_PART_INFO *min_max_arg_part;     
   uint group_prefix_len;     ///< Length of all key parts in the group prefix
@@ -2277,31 +2278,41 @@ public:
   ha_rows quick_prefix_records;
 public:
 
-#ifdef OPTIMIZER_TRACE
   void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
   {
+#ifdef OPTIMIZER_TRACE
     trace->add_str("type", "index_group").
-      add_str("index", index_info->name, strlen(index_info->name)).
-      add_str("group_attribute", min_max_arg_part->field->field_name,
-              strlen(min_max_arg_part->field->field_name)).
-      add("min_aggregate", have_min).
+      add_str("index", index_info->name, strlen(index_info->name));
+    if (min_max_arg_part)
+      trace->add_str("group_attribute", min_max_arg_part->field->field_name,
+                     strlen(min_max_arg_part->field->field_name));
+    else
+      trace->add_str("group_attribute", "<none>");
+    trace->add("min_aggregate", have_min).
       add("max_aggregate", have_max).
       add("distinct_aggregate", have_agg_distinct).
-      add("key_parts_used_for_access", used_key_parts).      
       add("records", records).
       add("cost", read_cost);
 
     const KEY_PART_INFO *key_part= index_info->key_part;
-
-    Opt_trace_array trace_range(param->thd->opt_trace,"ranges");
-    for (const SEL_ARG *current= index_tree; 
-         current; 
+    {
+      Opt_trace_array trace_keyparts(param->thd->opt_trace,
+                                     "key_parts_used_for_access");
+      for (uint partno= 0; partno < used_key_parts; partno++)
+      {
+        const KEY_PART_INFO *cur_key_part= key_part + partno;
+        trace_keyparts.add_str(cur_key_part->field->field_name);
+      }
+    }
+    Opt_trace_array trace_range(param->thd->opt_trace, "ranges");
+    for (const SEL_ARG *current= index_tree;
+         current;
          current= current->next)
     {
       String range_info;
       range_info.set_charset(system_charset_info);
-      for (const SEL_ARG *part= current; 
-           part; 
+      for (const SEL_ARG *part= current;
+           part;
            part= part->next_key_part)
       {
         const KEY_PART_INFO *cur_key_part= key_part + part->part;
@@ -2312,8 +2323,8 @@ public:
       }
       trace_range.add_str(range_info.ptr(), range_info.length());
     }
-  }
 #endif
+  }
 
   TRP_GROUP_MIN_MAX(bool have_min_arg, bool have_max_arg, 
                     bool have_agg_distinct_arg,
@@ -2483,8 +2494,9 @@ int SQL_SELECT::test_quick_select(THD *t
   else if (read_time <= 2.0 && !force_quick_range)
     DBUG_RETURN(0);				/* No need for quick select */
 
-  Opt_trace_object trace_range(thd->opt_trace, "range_analysis");
-  Opt_trace_object(thd->opt_trace, "table_scan").
+  Opt_trace_context * const trace= thd->opt_trace;
+  Opt_trace_object trace_range(trace, "range_analysis");
+  Opt_trace_object(trace, "table_scan").
     add("records", head->file->stats.records).
     add("cost", read_time);
 
@@ -2537,7 +2549,7 @@ int SQL_SELECT::test_quick_select(THD *t
     thd->mem_root= &alloc;
 
     {
-      Opt_trace_array trace_idx(thd->opt_trace, 
+      Opt_trace_array trace_idx(trace,
                                 "potential_range_indices",
                                 Opt_trace_context::RANGE_OPTIMIZER);
       /*
@@ -2547,8 +2559,8 @@ int SQL_SELECT::test_quick_select(THD *t
       key_info= head->key_info;
       for (idx=0 ; idx < head->s->keys ; idx++, key_info++)
       {
-        Opt_trace_object trace_idx_details(thd->opt_trace);
-        trace_idx_details.add_str("index", 
+        Opt_trace_object trace_idx_details(trace);
+        trace_idx_details.add_str("index",
                                   key_info->name, strlen(key_info->name));
         KEY_PART_INFO *key_part_info;
         if (!keys_to_use.is_set(idx))
@@ -2568,7 +2580,7 @@ int SQL_SELECT::test_quick_select(THD *t
 
         param.key[param.keys]=key_parts;
         key_part_info= key_info->key_part;
-        Opt_trace_array trace_keypart(thd->opt_trace, "key_parts");
+        Opt_trace_array trace_keypart(trace, "key_parts");
         for (uint part=0 ; part < key_info->key_parts ;
              part++, key_parts++, key_part_info++)
         {
@@ -2607,7 +2619,7 @@ int SQL_SELECT::test_quick_select(THD *t
         chosen= true;
       }
 
-      Opt_trace_object trace_cov(thd->opt_trace, 
+      Opt_trace_object trace_cov(trace,
                                  "best_covering_index_scan",
                                  Opt_trace_context::RANGE_OPTIMIZER);
       trace_cov.add_str("index", head->key_info[key_for_use].name,
@@ -2658,12 +2670,10 @@ int SQL_SELECT::test_quick_select(THD *t
     {
       param.table->quick_condition_rows= min(group_trp->records,
                                              head->file->stats.records);
-      Opt_trace_object grp_summary(thd->opt_trace, 
+      Opt_trace_object grp_summary(trace,
                                    "best_group_range_summary",
                                    Opt_trace_context::RANGE_OPTIMIZER);
-#ifdef OPTIMIZER_TRACE
       group_trp->trace_basic_info(&param, &grp_summary);
-#endif
       if (group_trp->read_cost < best_read_time)
       {
         grp_summary.add("chosen", true);
@@ -2682,7 +2692,7 @@ int SQL_SELECT::test_quick_select(THD *t
       */
       if (tree->merges.is_empty())
       {
-        Opt_trace_object trace_range(thd->opt_trace, 
+        Opt_trace_object trace_range(trace,
                                      "analyzing_range_alternatives",
                                      Opt_trace_context::RANGE_OPTIMIZER);
         TRP_RANGE         *range_trp;
@@ -2740,7 +2750,7 @@ int SQL_SELECT::test_quick_select(THD *t
                              " trying to construct index_merge"));
           List_iterator_fast<SEL_IMERGE> it(tree->merges);
           {
-            Opt_trace_array trace_idx_merge(thd->opt_trace, 
+            Opt_trace_array trace_idx_merge(trace, 
                                             "analyzing_index_merge",
                                             Opt_trace_context::RANGE_OPTIMIZER);
             while ((imerge= it++))
@@ -2780,16 +2790,16 @@ int SQL_SELECT::test_quick_select(THD *t
 free_mem:
     if (quick)
     {
-      Opt_trace_object trace_range_summary(thd->opt_trace, 
+      Opt_trace_object trace_range_summary(trace,
                                            "chosen_range_access_summary");
       {
-        Opt_trace_object trace_range_plan(thd->opt_trace, 
+        Opt_trace_object trace_range_plan(trace,
                                            "range_access_plan");
         best_trp->trace_basic_info(&param, &trace_range_plan);
 
       }
-      trace_range_summary.add("records_in_plan", quick->records).
-        add("cost_of_plan", quick->read_time).
+      trace_range_summary.add("records_for_plan", quick->records).
+        add("cost_for_plan", quick->read_time).
         add("chosen", true);
     }
 
@@ -4134,56 +4144,60 @@ TABLE_READ_PLAN *get_best_disjunct_quick
                                              sizeof(TRP_RANGE*)*
                                              n_child_scans)))
     DBUG_RETURN(NULL);
-  {
-    /*
-      Collect best 'range' scan for each of disjuncts, and, while doing so,
-      analyze possibility of ROR scans. Also calculate some values needed by
-      other parts of the code.
-    */
-    Opt_trace_array ota(trace,"indices_to_merge");
-    for (ptree= imerge->trees, cur_child= range_scans;
-         ptree != imerge->trees_next;
-         ptree++, cur_child++)
+  // Note: to_merge.end() is called to close this object after this for-loop.
+  Opt_trace_array to_merge(trace,"indices_to_merge");
+  /*
+    Collect best 'range' scan for each of disjuncts, and, while doing so,
+    analyze possibility of ROR scans. Also calculate some values needed by
+    other parts of the code.
+  */
+  for (ptree= imerge->trees, cur_child= range_scans;
+       ptree != imerge->trees_next;
+       ptree++, cur_child++)
+  {
+    DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map,
+                                        "tree in SEL_IMERGE"););
+    Opt_trace_object trace_idx(trace);
+    if (!(*cur_child=
+          get_key_scans_params(param, *ptree, true, false, read_time)))
     {
-      DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map,
-                                          "tree in SEL_IMERGE"););
-      Opt_trace_object trace_idx(trace);
-      if (!(*cur_child= 
-            get_key_scans_params(param, *ptree, TRUE, FALSE, read_time)))
-      {
-        /*
-          One of index scans in this index_merge is more expensive than entire
-          table read for another available option. The entire index_merge (and
-          any possible ROR-union) will be more expensive then, too. We continue
-          here only to update SQL_SELECT members.
-        */
-        imerge_too_expensive= TRUE;
-      }
-      if (imerge_too_expensive)
-      {
-        trace_idx.add("chosen", false).add_str("cause", "cost");
-        continue;
-      }
-
-      uint keynr_in_table= param->real_keynr[(*cur_child)->key_idx];
-      imerge_cost += (*cur_child)->read_cost;
-      all_scans_ror_able &= ((*ptree)->n_ror_scans > 0);
-      all_scans_rors &= (*cur_child)->is_ror;
-      if (pk_is_clustered &&
-          keynr_in_table == param->table->s->primary_key)
-      {
-        cpk_scan= cur_child;
-        cpk_scan_records= (*cur_child)->records;
-      }
-      else
-        non_cpk_scan_records += (*cur_child)->records;
+      /*
+        One of index scans in this index_merge is more expensive than entire
+        table read for another available option. The entire index_merge (and
+        any possible ROR-union) will be more expensive then, too. We continue
+        here only to update SQL_SELECT members.
+      */
+      imerge_too_expensive= true;
+    }
+    if (imerge_too_expensive)
+    {
+      trace_idx.add("chosen", false).add_str("cause", "cost");
+      continue;
+    }
 
-      trace_idx.
-        add_str("index_to_merge", param->table->key_info[keynr_in_table].name,
-                strlen(param->table->key_info[keynr_in_table].name)).
-        add("cumulated_cost", imerge_cost);
+    const uint keynr_in_table= param->real_keynr[(*cur_child)->key_idx];
+    imerge_cost += (*cur_child)->read_cost;
+    all_scans_ror_able &= ((*ptree)->n_ror_scans > 0);
+    all_scans_rors &= (*cur_child)->is_ror;
+    if (pk_is_clustered &&
+        keynr_in_table == param->table->s->primary_key)
+    {
+      cpk_scan= cur_child;
+      cpk_scan_records= (*cur_child)->records;
     }
+    else
+      non_cpk_scan_records += (*cur_child)->records;
+
+    trace_idx.
+      add_str("index_to_merge", param->table->key_info[keynr_in_table].name,
+              strlen(param->table->key_info[keynr_in_table].name)).
+      add("cumulated_cost", imerge_cost);
   }
+
+  // Note: to_merge trace object is closed here
+  to_merge.end();
+
+
   trace_best_disjunct.add("cost_of_reading_ranges", imerge_cost);
   if (imerge_too_expensive || (imerge_cost > read_time) ||
       ((non_cpk_scan_records+cpk_scan_records >= param->table->file->stats.records) &&
@@ -4220,10 +4234,10 @@ TABLE_READ_PLAN *get_best_disjunct_quick
       Add one ROWID comparison for each row retrieved on non-CPK scan.  (it
       is done in QUICK_RANGE_SELECT::row_in_ranges)
      */
-    
-    imerge_cost += non_cpk_scan_records / TIME_FOR_COMPARE_ROWID;
-    trace_best_disjunct.add("cost_of_mapping_rowid_in_non_cpk_scan", 
-                            non_cpk_scan_records / TIME_FOR_COMPARE_ROWID);
+    double rowid_comp_cost= non_cpk_scan_records / TIME_FOR_COMPARE_ROWID;
+    imerge_cost += rowid_comp_cost;
+    trace_best_disjunct.add("cost_of_mapping_rowid_in_non_clustered_pk_scan",
+                            rowid_comp_cost);
   }
 
   /* Calculate cost(rowid_to_row_scan) */
@@ -4234,7 +4248,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
     get_sweep_read_cost(param->table, non_cpk_scan_records, is_interrupted,
                         &sweep_cost);
     imerge_cost += sweep_cost.total_cost();
-    trace_best_disjunct.add("cost_sort_rowid_and_read_disk", 
+    trace_best_disjunct.add("cost_sort_rowid_and_read_disk",
                             sweep_cost.total_cost());
   }
   DBUG_PRINT("info",("index_merge cost with rowid-to-row scan: %g",
@@ -4934,7 +4948,7 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
       trace_ror.add("usable", false).
         add_str("cause", "too_few_roworder_scans");
     else
-      trace_ror.add("usable", false).add("done_tracing", false);
+      trace_ror.add("usable", false).add("need_tracing", true);
     DBUG_RETURN(NULL);
   }
 
@@ -5013,13 +5027,13 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
       /* S= S + first(R);  R= R - first(R); */
       if (!ror_intersect_add(intersect, *cur_ror_scan, FALSE))
       {
-        trace_idx.add("used_in_intersect", false).
+        trace_idx.add("usable", false).
           add_str("cause", "does_not_reduce_cost_of_intersect");
         cur_ror_scan++;
         continue;
       }
     
-      trace_idx.add("used_in_intersect", true).
+      trace_idx.add("usable", true).
         add("matching_records_now", intersect->out_rows).
         add("cumulated_cost", intersect->total_cost).
         add("isect_covering_with_this_index", intersect->is_covering);
@@ -5038,7 +5052,8 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
 
   if (intersect_scans_best == intersect_scans)
   {
-    trace_ror.add("increases_selectivity", false).add("chosen", false);
+    trace_ror.add("chosen", false).
+      add("cause", "does_not_increase_selectivity");
     DBUG_PRINT("info", ("None of scans increase selectivity"));
     DBUG_RETURN(NULL);
   }
@@ -5063,19 +5078,19 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
         (intersect->total_cost < min_cost))
     {
       Opt_trace_object (param->thd->opt_trace, "clustered_pk").
-        add("cpk_scan_added_to_intersect", true).
+        add("clustered_pk_scan_added_to_intersect", true).
         add("cumulated_cost", intersect->total_cost);
       cpk_scan_used= TRUE;
       intersect_best= intersect; //just set pointer here
     }
     else
       Opt_trace_object (param->thd->opt_trace, "clustered_pk").
-        add("cpk_added_to_intersect", false).add_str("cause", "cost");
+        add("clustered_pk_added_to_intersect", false).add_str("cause", "cost");
   } 
   else 
   {
     Opt_trace_object trace_cpk(param->thd->opt_trace, "clustered_pk");
-    trace_cpk.add("cpk_added_to_intersect", false);
+    trace_cpk.add("clustered_pk_added_to_intersect", false);
     cpk_scan ? trace_cpk.add_str("cause", "ror_is_covering")
              : trace_cpk.add_str("cause", "no_clustered_pk_index");
   }
@@ -6144,6 +6159,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
   uchar *str;
   ulonglong orig_sql_mode;
   int err;
+  const char* impossible_cond_cause= NULL;
   DBUG_ENTER("get_mm_leaf");
 
   /*
@@ -6364,12 +6380,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
           value->result_type() == item_cmp_type(field->result_type(),
                                                 value->result_type()))
       {
-        Opt_trace_object (param->thd->opt_trace, "impossible_condition",
-                          Opt_trace_context::RANGE_OPTIMIZER).
-          add_str("table", *field->table_name, strlen(*field->table_name)).
-          add_str("field", field->field_name, strlen(field->field_name)).
-          add_str("cause", "incomparable_types");
-
+        impossible_cond_cause= "incomparable_types";
         tree= new (alloc) SEL_ARG(field, 0, 0);
         tree->type= SEL_ARG::IMPOSSIBLE;
         field->table->in_use->variables.sql_mode= orig_sql_mode;
@@ -6432,12 +6443,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
   }
   else if (err < 0)
   {
-    Opt_trace_object (param->thd->opt_trace, "impossible_condition",
-                      Opt_trace_context::RANGE_OPTIMIZER).
-      add_str("table", *field->table_name, strlen(*field->table_name)).
-      add_str("field", field->field_name, strlen(field->field_name)).
-      add_str("cause", "null_field_in_non_null_column");
-    
+    impossible_cond_cause= "null_field_in_non_null_column";
     field->table->in_use->variables.sql_mode= orig_sql_mode;
     /* This happens when we try to insert a NULL field in a not null column */
     tree= &null_element;                        // cmp with NULL is never TRUE
@@ -6451,11 +6457,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
   */
   if (type != Item_func::EQUAL_FUNC && field->is_real_null())
   {
-    Opt_trace_object (param->thd->opt_trace, "impossible_condition",
-                      Opt_trace_context::RANGE_OPTIMIZER).
-      add_str("table", *field->table_name, strlen(*field->table_name)).
-      add_str("field", field->field_name, strlen(field->field_name)).
-      add_str("cause", "comparison_with_null_always_false");
+    impossible_cond_cause= "comparison_with_null_always_false";
     tree= &null_element;
     goto end;
   }
@@ -6492,11 +6494,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
     {
       if (type == Item_func::LT_FUNC || type == Item_func::LE_FUNC)
       {
-        Opt_trace_object (param->thd->opt_trace, "impossible_condition",
-                          Opt_trace_context::RANGE_OPTIMIZER).
-          add_str("table", *field->table_name, strlen(*field->table_name)).
-          add_str("field", field->field_name, strlen(field->field_name)).
-          add_str("cause", "unsigned_int_cannot_be_negative");
+        impossible_cond_cause= "unsigned_int_cannot_be_negative";
         tree->type= SEL_ARG::IMPOSSIBLE;
         goto end;
       }
@@ -6576,6 +6574,14 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
   }
 
 end:
+  if (impossible_cond_cause)
+  {
+    Opt_trace_object (param->thd->opt_trace, "impossible_condition",
+                      Opt_trace_context::RANGE_OPTIMIZER).
+      add_str("table", *field->table_name, strlen(*field->table_name)).
+      add_str("field", field->field_name, strlen(field->field_name)).
+      add_str("cause", impossible_cond_cause);
+  }
   param->thd->mem_root= alloc;
   DBUG_RETURN(tree);
 }
@@ -8011,6 +8017,7 @@ range_seq_t sel_arg_range_seq_init(void 
 static void step_down_to(String *s, SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree)
 {
 
+#ifdef OPTIMIZER_TRACE
   if (arg->param->thd->opt_trace && arg->param->thd->opt_trace->is_started())
   {
     /* 
@@ -8018,7 +8025,7 @@ static void step_down_to(String *s, SEL_
        key_tree) to seq. Trace range here since this is where it is
        human readable.
     */
-    KEY_PART_INFO *key_part= 
+    const KEY_PART_INFO *key_part= 
       arg->param->table->key_info[arg->real_keyno].key_part + key_tree->part;
       
     uint16 length= key_part->length;
@@ -8027,6 +8034,7 @@ static void step_down_to(String *s, SEL_
                 key_tree->max_value, length,
                 key_tree->min_flag | key_tree->max_flag);
   }
+#endif
 
   RANGE_SEQ_ENTRY *cur= &arg->stack[arg->i+1];
   RANGE_SEQ_ENTRY *prev= &arg->stack[arg->i];
@@ -8164,20 +8172,22 @@ walk_right_n_up:
                                           &cur->max_key_flag,
                                           MAX_KEY);
 
-        if (seq->param->thd->opt_trace && seq->param->thd->opt_trace->is_started())
+#ifdef OPTIMIZER_TRACE
+        if (seq->param->thd->opt_trace &&
+            seq->param->thd->opt_trace->is_started())
         {
-          // Trace the range we just stored 
-          KEY_PART_INFO *kpi= 
-            seq->param->table->key_info[seq->real_keyno].key_part + 
-            store_key_part->part; 
-      
+          // Trace the range we just stored
+          KEY_PART_INFO *kpi=
+            seq->param->table->key_info[seq->real_keyno].key_part +
+            store_key_part->part;
+
           uint16 length= kpi->length;
           append_range(&key_range_trace, kpi,
                        store_key_part->min_value, length,
                        store_key_part->max_value, length,
                        store_key_part->min_flag | store_key_part->max_flag);
         }
-
+#endif
         break;
       }
     }
@@ -8256,6 +8266,7 @@ walk_up_n_right:
   seq->param->range_count++;
   seq->param->max_key_part=max(seq->param->max_key_part,key_tree->part);
 
+#ifdef OPTIMIZER_TRACE
   if (key_range_trace.length())
   {
     DBUG_ASSERT(seq->param->thd->opt_trace);
@@ -8270,6 +8281,8 @@ walk_up_n_right:
       seq->param->thd->opt_trace->get_current_struct();
     trace_range->add_str(key_range_trace.ptr(), key_range_trace.length());
   }
+#endif
+
   return 0;
 }
 
@@ -10139,47 +10152,61 @@ get_best_group_min_max(PARAM *param, SEL
   Item_field *item_field;
   bool is_agg_distinct;
   List<Item_field> agg_distinct_flds;
+  KEY *cur_index_info= table->key_info;
+  KEY *cur_index_info_end= cur_index_info + table->s->keys;
+  /* Cost-related variables for the best index so far. */
+  double best_read_cost= DBL_MAX;
+  ha_rows best_records= 0;
+  SEL_ARG *best_index_tree= NULL;
+  ha_rows best_quick_prefix_records= 0;
+  uint best_param_idx= 0;
+  List_iterator<Item> select_items_it;
+
+  const uint pk= param->table->s->primary_key;
+  SEL_ARG *cur_index_tree= NULL;
+  ha_rows cur_quick_prefix_records= 0;
+  uint cur_param_idx=MAX_KEY;
 
   DBUG_ENTER("get_best_group_min_max");
 
   Opt_trace_object trace_group(thd->opt_trace, "group_index_range",
                                Opt_trace_context::RANGE_OPTIMIZER);
+  const char* cause= NULL;
 
   /* Perform few 'cheap' tests whether this access method is applicable. */
   if (!join)
   {
-    trace_group.add("chosen", false).add_str("cause", "no_join");
-    DBUG_RETURN(NULL);        /* This is not a select statement. */
+    cause= "no_join";
+    goto return_null_not_chosen_cause;
   }
   if (join->tables != 1)   /* The query must reference one table. */
   {
-    trace_group.add("chosen", false).add_str("cause", "not_single_table");
-    DBUG_RETURN(NULL);
+    cause= "not_single_table";
+    goto return_null_not_chosen_cause;
   }
   if (join->select_lex->olap == ROLLUP_TYPE) /* Check (B3) for ROLLUP */
   {
-    trace_group.add("chosen", false).add_str("cause", "is_rollup");
-    DBUG_RETURN(NULL);
+    cause= "is_rollup";
+    goto return_null_not_chosen_cause;
   }
   if (table->s->keys == 0)        /* There are no indexes to use. */
   {
-    trace_group.add("chosen", false).add_str("cause", "no_index");
-    DBUG_RETURN(NULL);
+    cause= "no_index";
+    goto return_null_not_chosen_cause;
   }
 
   /* Check (SA1,SA4) and store the only MIN/MAX argument - the C attribute.*/
   if (join->make_sum_func_list(join->all_fields, join->fields_list, 1))
     DBUG_RETURN(NULL);
 
-  List_iterator<Item> select_items_it(join->fields_list);
   is_agg_distinct = is_indexed_agg_distinct(join, &agg_distinct_flds);
 
   if ((!join->group_list) && /* Neither GROUP BY nor a DISTINCT query. */
       (!join->select_distinct) &&
       !is_agg_distinct)
   {
-    trace_group.add("chosen", false).add_str("cause", "not_group_by_or_distinct");
-    DBUG_RETURN(NULL);
+    cause= "not_group_by_or_distinct";
+    goto return_null_not_chosen_cause;
   }
   /* Analyze the query in more detail. */
 
@@ -10199,9 +10226,8 @@ get_best_group_min_max(PARAM *param, SEL
         continue;
       else
       {
-        trace_group.add("chosen", false).
-          add_str("cause", "not_applicable_aggregate_function");
-        DBUG_RETURN(NULL);
+        cause= "not_applicable_aggregate_function";
+        goto return_null_not_chosen_cause;
       }
 
       /* The argument of MIN/MAX. */
@@ -10217,6 +10243,8 @@ get_best_group_min_max(PARAM *param, SEL
         DBUG_RETURN(NULL);
     }
   }
+
+  select_items_it= List_iterator<Item>(join->fields_list);
   /* Check (SA5). */
   if (join->select_distinct)
   {
@@ -10233,9 +10261,8 @@ get_best_group_min_max(PARAM *param, SEL
   {
     if ((*tmp_group->item)->real_item()->type() != Item::FIELD_ITEM)
     {
-      trace_group.add("chosen", false).
-        add_str("cause", "group_field_is_expression");
-      DBUG_RETURN(NULL);
+      cause= "group_field_is_expression";
+      goto return_null_not_chosen_cause;
     }
   }
 
@@ -10244,21 +10271,6 @@ get_best_group_min_max(PARAM *param, SEL
     (GA1,GA2) are all TRUE. If there is more than one such index, select the
     first one. Here we set the variables: group_prefix_len and index_info.
   */
-  KEY *cur_index_info= table->key_info;
-  KEY *cur_index_info_end= cur_index_info + table->s->keys;
-  /* Cost-related variables for the best index so far. */
-  double best_read_cost= DBL_MAX;
-  ha_rows best_records= 0;
-  SEL_ARG *best_index_tree= NULL;
-  ha_rows best_quick_prefix_records= 0;
-  uint best_param_idx= 0;
-
-  const uint pk= param->table->s->primary_key;
-  SEL_ARG *cur_index_tree= NULL;
-  ha_rows cur_quick_prefix_records= 0;
-  uint cur_param_idx=MAX_KEY;
-
-
   {
     Opt_trace_array ota(thd->opt_trace, "potential_group_range_indices");
     for (uint cur_index= 0 ; cur_index_info != cur_index_info_end ;
@@ -10287,7 +10299,7 @@ get_best_group_min_max(PARAM *param, SEL
       /* Check (B1) - if current index is covering. */
       if (!table->covering_keys.is_set(cur_index))
       {
-        trace_idx.add("usable", false).add("covering", false);
+        cause= "not_covering";
         goto next_index;
       }
 
@@ -10314,7 +10326,7 @@ get_best_group_min_max(PARAM *param, SEL
           if (bitmap_is_set(table->read_set, cur_field->field_index) &&
               !cur_field->part_of_key_not_clustered.is_set(cur_index))
           {
-            trace_idx.add("usable", false).add("covering", false);
+            cause= "not_covering";
             goto next_index;                  // Field was not part of key
           }
         }
@@ -10349,8 +10361,7 @@ get_best_group_min_max(PARAM *param, SEL
           }
           else
           {
-            trace_idx.add("usable", false).
-              add_str("cause", "group_attribute_not_prefix_in_index");
+            cause= "group_attribute_not_prefix_in_index";
             goto next_index;
           }
         }
@@ -10383,7 +10394,7 @@ get_best_group_min_max(PARAM *param, SEL
           /* not doing loose index scan for derived tables */
           if (!item_field->field)
           {
-            trace_idx.add("usable", false).add_str("cause", "derived_table");
+            cause= "derived_table";
             goto next_index;
           }
 
@@ -10398,8 +10409,7 @@ get_best_group_min_max(PARAM *param, SEL
           if (key_part_nr < 1 ||
               (!is_agg_distinct && key_part_nr > join->fields_list.elements))
           {
-            trace_idx.add("usable", false).
-              add_str("cause", "select_attribute_not_prefix_in_index");
+            cause= "select_attribute_not_prefix_in_index";
             goto next_index;
           }
           cur_part= cur_index_info->key_part + key_part_nr - 1;
@@ -10427,8 +10437,7 @@ get_best_group_min_max(PARAM *param, SEL
         key_part_nr= get_field_keypart(cur_index_info, min_max_arg_item->field);
         if (key_part_nr <= cur_group_key_parts)
         {
-          trace_idx.add("usable", false).
-            add_str("cause", "aggregate_column_not_suffix_in_idx");
+          cause= "aggregate_column_not_suffix_in_idx";
           goto next_index;
         }
         min_max_arg_part= cur_index_info->key_part + key_part_nr - 1;
@@ -10472,8 +10481,7 @@ get_best_group_min_max(PARAM *param, SEL
                                       &cur_key_infix_len,
                                       &first_non_infix_part))
           {
-            trace_idx.add("usable", false).
-              add_str("cause", "nonconst_equality_gap_attribute");
+            cause= "nonconst_equality_gap_attribute";
             goto next_index;
           }
         }
@@ -10484,8 +10492,7 @@ get_best_group_min_max(PARAM *param, SEL
             There is a gap but no range tree, thus no predicates at all for the
             non-group keyparts.
           */
-          trace_idx.add("usable", false).
-            add_str("cause", "no_nongroup_keypart_predicate");
+          cause= "no_nongroup_keypart_predicate";
           goto next_index;
         }
         else if (first_non_group_part && join->conds)
@@ -10510,8 +10517,7 @@ get_best_group_min_max(PARAM *param, SEL
           if (join->conds->walk(&Item::find_item_in_field_list_processor, 0,
                                 (uchar*) key_part_range))
           {
-            trace_idx.add("usable", false).
-              add_str("cause", "indexpart_reference_from_where_clause");
+            cause= "keypart_reference_from_where_clause";
             goto next_index;
           }
         }
@@ -10529,8 +10535,7 @@ get_best_group_min_max(PARAM *param, SEL
         {
           if (bitmap_is_set(table->read_set, cur_part->field->field_index))
           {
-            trace_idx.add("usable", false).
-              add_str("cause", "keypart_after_infix_in_query");
+            cause= "keypart_after_infix_in_query";
             goto next_index;
           }
         }
@@ -10584,7 +10589,12 @@ get_best_group_min_max(PARAM *param, SEL
         used_key_parts= cur_used_key_parts;
       }
 
-  next_index:;
+  next_index:
+      if (cause)
+      {
+        trace_idx.add("usable", false).add_str("cause", cause);
+        cause= NULL;
+      }
     }
   }
   if (!index_info) /* No usable index found. */
@@ -10596,9 +10606,8 @@ get_best_group_min_max(PARAM *param, SEL
                                       (index_info->flags & HA_SPATIAL) ?
                                       Field::itMBR : Field::itRAW))
   {
-    trace_group.add("usable",false).
-      add_str("cause", "unsupported_predicate_on_agg_attribute");
-    DBUG_RETURN(NULL);
+    cause= "unsupported_predicate_on_agg_attribute";
+    goto return_null_not_usable_cause;
   }
   /* The query passes all tests, so construct a new TRP object. */
   read_plan= new (param->mem_root)
@@ -10630,6 +10639,16 @@ get_best_group_min_max(PARAM *param, SEL
   }
 
   DBUG_RETURN(read_plan);
+
+return_null_not_chosen_cause:
+  DBUG_ASSERT(cause);
+  trace_group.add("chosen", false).add_str("cause", cause);
+  DBUG_RETURN(NULL);
+
+return_null_not_usable_cause:
+  DBUG_ASSERT(cause);
+  trace_group.add("usable", false).add_str("cause", cause);
+  DBUG_RETURN(NULL);
 }
 
 
@@ -12330,10 +12349,10 @@ static void print_ror_scans_arr(TABLE *t
   @param[in]     flag         Key range flags defining what min_key
                               and max_key represent @see my_base.h
  */
-static void append_range(String *out, 
+static void append_range(String *out,
                          const KEY_PART_INFO *key_parts,
                          const uchar *min_key, const uint16 min_length,
-                         const uchar *max_key, const uint16 max_length, 
+                         const uchar *max_key, const uint16 max_length,
                          const uint flag)
 {
   if (out->length() > 0)
@@ -12360,6 +12379,7 @@ static void append_range(String *out, 
   }
 }
 
+#if 0
 static void print_quick(QUICK_SELECT_I *quick, const key_map *needed_reg)
 {
   char buf[MAX_KEY/8+1];
@@ -12384,12 +12404,12 @@ static void print_quick(QUICK_SELECT_I *
   DBUG_UNLOCK_FILE;
   DBUG_VOID_RETURN;
 }
-
+#endif /* if 0*/
 void QUICK_RANGE_SELECT::dbug_dump(int indent, bool verbose)
 {
   Opt_trace_context *out= current_thd->opt_trace;
   Opt_trace_object oto(out, "range_scan");
-  oto.add_str("index", head->key_info[index].name, 
+  oto.add_str("index", head->key_info[index].name,
               strlen(head->key_info[index].name)).
     add("key_length", max_used_key_length).
     add("used_keyparts", used_key_parts);
@@ -12407,7 +12427,7 @@ void QUICK_RANGE_SELECT::dbug_dump(int i
       range_info.set_charset(system_charset_info);
       range= *pr;
       append_range(&range_info, head->key_info[index].key_part,
-                        range->min_key, range->min_length, 
+                        range->min_key, range->min_length,
                         range->max_key, range->max_length,
                         range->flag);
       trace_range.add_str(range_info.ptr(), range_info.length());

=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h	2010-10-22 08:31:43 +0000
+++ b/sql/opt_range.h	2010-11-17 12:27:39 +0000
@@ -864,12 +864,6 @@ class SQL_SELECT :public Sql_alloc {
   bool check_quick(THD *thd, bool force_quick_range, ha_rows limit)
   {
     key_map tmp(key_map::ALL_BITS);
-
-    /* Entrypoint for insert/update/deletes with condition.
-       test_quick_select adds tracing with keys, and we are currently
-       in a trace array that does not accept keys.
-    */
-    Opt_trace_object wrapper(thd->opt_trace); 
     return test_quick_select(thd, tmp, 0, limit, force_quick_range, FALSE) < 0;
   }
   inline bool skip_record(THD *thd, bool *skip_record)

=== modified file 'sql/opt_trace.h'
--- a/sql/opt_trace.h	2010-11-11 13:25:53 +0000
+++ b/sql/opt_trace.h	2010-11-17 12:27:39 +0000
@@ -384,7 +384,8 @@ public:
     MISC= 1,
     GREEDY_SEARCH= 2,
     RANGE_OPTIMIZER= 4 ///@todo join cache, semijoin...
-    /* if you add here, update FEATURES_DEFAULT! */
+    /* if you add here, update feature_value of empty implementation
+     * and FEATURES_DEFAULT! */
   };
   static const feature_value FEATURES_DEFAULT;
 
@@ -1309,7 +1310,7 @@ class Opt_trace_context
 {
 public:
   /* We need this one */
-  enum feature_value { MISC= 1, GREEDY_SEARCH= 2 };
+  enum feature_value { MISC= 1, GREEDY_SEARCH= 2, RANGE_OPTIMIZER= 4 };
 };
 
 /** Empty implementation used when optimizer trace is not compiled in */

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	2010-08-12 00:26:10 +0000
+++ b/sql/sql_delete.cc	2010-11-17 12:27:39 +0000
@@ -190,6 +190,13 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   select=make_select(table, 0, 0, conds, 0, &error);
   if (error)
     DBUG_RETURN(TRUE);
+
+  { // Enter scope for optimizer trace wrapper
+  Opt_trace_object wrapper(thd->opt_trace);
+  wrapper.add_str("database", table_list->db,
+                  strlen(table_list->db)).
+    add_str("table", table_list->alias, strlen(table_list->alias));
+
   if ((select && select->check_quick(thd, safe_update, limit)) || !limit)
   {
     delete select;
@@ -205,6 +212,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
     my_ok(thd, 0);
     DBUG_RETURN(0);				// Nothing to delete
   }
+  } // Ends scope for optimizer trace wrapper
 
   /* If running in safe sql mode, don't allow updates without keys */
   if (table->quick_keys.is_clear_all())

=== modified file 'sql/sql_help.cc'
--- a/sql/sql_help.cc	2010-07-29 13:37:49 +0000
+++ b/sql/sql_help.cc	2010-11-17 12:27:39 +0000
@@ -575,6 +575,9 @@ SQL_SELECT *prepare_simple_select(THD *t
   table->covering_keys.clear_all();
 
   SQL_SELECT *res= make_select(table, 0, 0, cond, 0, error);
+
+  // Wrapper for correct JSON in optimizer trace
+  Opt_trace_object wrapper(thd->opt_trace);
   if (*error || (res && res->check_quick(thd, 0, HA_POS_ERROR)) ||
       (res && res->quick && res->quick->reset()))
   {

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-11-11 13:25:53 +0000
+++ b/sql/sql_select.cc	2010-11-17 12:27:39 +0000
@@ -4939,17 +4939,15 @@ make_join_statistics(JOIN *join, TABLE_L
 
       for (s= stat ; s < stat_end ; s++)
       {
-        Opt_trace_object trace_table(join->thd->opt_trace);
-        trace_table.add_str("database",s->join->tables_list->db, 
+        Opt_trace_object trace_table(trace);
+        trace_table.add_str("database",s->join->tables_list->db,
                             strlen(s->join->tables_list->db)).
           add_str("table", s->table->alias, strlen(s->table->alias));
         if (s->type == JT_SYSTEM || s->type == JT_CONST)
         {
-          trace_table.add("records", 1).
-            add("cost", 1);
-
-          trace_table.add_str("cause", (s->type == JT_SYSTEM) ? 
-                          "system_table": "const_table");
+          trace_table.add("records", 1).add("cost", 1);
+          trace_table.add_str("table_type", (s->type == JT_SYSTEM) ?
+                              "system_table": "const_table");
 
           /* Only one matching row */
           s->found_records= s->records= s->read_time=1; s->worst_seeks= 1.0;
@@ -6508,7 +6506,7 @@ add_group_and_distinct_keys(JOIN *join, 
     for (cur_group= join->group_list; cur_group; cur_group= cur_group->next)
       (*cur_group->item)->walk(&Item::collect_item_field_processor, 0,
                                (uchar*) &indexed_fields);
-    cause= (char*)"group_by";
+    cause= "group_by";
   }
   else if (join->select_distinct)
   { /* Collect all query fields referenced in the SELECT clause. */
@@ -6518,12 +6516,12 @@ add_group_and_distinct_keys(JOIN *join, 
     while ((item= select_items_it++))
       item->walk(&Item::collect_item_field_processor, 0,
                  (uchar*) &indexed_fields);
-    cause= (char*)"distinct";
+    cause= "distinct";
   }
   else if (is_indexed_agg_distinct(join, &indexed_fields))
   {
     join->sort_and_group= 1;
-    cause= (char*) "indexed_distinct_aggregate";
+    cause= "indexed_distinct_aggregate";
   }
   else
     return;
@@ -6542,12 +6540,8 @@ add_group_and_distinct_keys(JOIN *join, 
   if (!possible_keys.is_clear_all() && 
       !(possible_keys == join_tab->const_keys))
   {
-    Opt_trace_context *trace= join->thd->opt_trace;
-    if (trace && trace->is_started())
-    {
-      trace_indices_added_group_distinct(trace, join_tab,
-                                         possible_keys, cause);
-    }
+    trace_indices_added_group_distinct(join->thd->opt_trace, join_tab,
+                                       possible_keys, cause);
     join_tab->const_keys.merge(possible_keys);
   }
 }
@@ -6556,24 +6550,27 @@ add_group_and_distinct_keys(JOIN *join, 
   Print keys that were appended to join_tab->const_keys because they
   can be used for GROUP BY or DISTINCT to the optimizer trace.
 
-  @param thd       Thread for the connection that submitted the query
+  @param trace     The optimizer trace context we're adding info to
   @param join_tab  The table the indices cover
-  @param new_keys  The keys that are considered useful because they can 
+  @param new_keys  The keys that are considered useful because they can
                    be used for GROUP BY or DISTINCT
-  @param cause     Zero-terminated string with reason for adding indices 
+  @param cause     Zero-terminated string with reason for adding indices
                    to const_keys
 
   @see add_group_and_distinct_keys()
  */
-static void trace_indices_added_group_distinct(Opt_trace_context *trace, 
+static void trace_indices_added_group_distinct(Opt_trace_context *trace,
                                                const JOIN_TAB *join_tab,
-                                               const key_map new_keys, 
+                                               const key_map new_keys,
                                                const char* cause)
 {
+  if (trace == NULL || !trace->is_started())
+    return;
+
   KEY *key_info= join_tab->table->key_info;
   key_map existing_keys= join_tab->const_keys;
   uint nbrkeys= join_tab->table->s->keys;
-  
+
   Opt_trace_object trace_summary(trace, "const_keys_added");
   {
     Opt_trace_array trace_key(trace,"keys");
@@ -7023,8 +7020,8 @@ best_access_path(JOIN      *join,
       loose_scan_opt.next_ref_key();
       DBUG_PRINT("info", ("Considering ref access on key %s",
                           keyuse->table->key_info[keyuse->key].name));
-      Opt_trace_object trace_access_idx(thd->opt_trace);
-      trace_access_idx.add_str("access_type", "index").
+      Opt_trace_object trace_access_idx(trace);
+      trace_access_idx.add_str("access_type", "ref").
         add_str("index", keyinfo->name, strlen(keyinfo->name));
 
       /* 
@@ -7380,7 +7377,7 @@ best_access_path(JOIN      *join,
     records= best_records;
   }
 
-  Opt_trace_object trace_access_scan(thd->opt_trace);
+  Opt_trace_object trace_access_scan(trace);
   /*
     Don't test table scan if it can't be better.
     Prefer key lookup if we would use the same key for scanning.
@@ -7414,8 +7411,7 @@ best_access_path(JOIN      *join,
     trace_access_scan.add_str("access_type", s->quick ? "range" : "scan");
     trace_access_scan.add("cost", s->read_time).
       add("records", s->found_records).
-      add("chosen", false).
-      add_str("cause", "index_cheaper");
+      add_str("cause", "cost");
 
     goto skip_table_scan;
   }
@@ -7424,16 +7420,14 @@ best_access_path(JOIN      *join,
        best_max_key_part >= s->table->quick_key_parts[best_key->key]))  // (2)
   {
     trace_access_scan.add_str("access_type", "range").
-      add("chosen", false).
-      add_str("cause", "heuristic_index_must_be_cheaper");
+      add_str("cause", "heuristic_index_cheaper");
     goto skip_table_scan;
   }
 
   if (((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) &&     // (3)
        ! s->table->covering_keys.is_clear_all() && best_key && !s->quick))// (3)
   {
-    trace_access_scan.add_str("access_type", s->quick ? "range" : "scan");
-    trace_access_scan.add("chosen", false).
+    trace_access_scan.add_str("access_type", s->quick ? "range" : "scan").
       add_str("cause", "covering_index_better_than_full_scan");
     goto skip_table_scan;
   }
@@ -7441,7 +7435,6 @@ best_access_path(JOIN      *join,
   if ((s->table->force_index && best_key && !s->quick))                 // (4)
   {
       trace_access_scan.add_str("access_type", "scan").
-        add("chosen", false).
         add_str("cause", "force_index");
     goto skip_table_scan;
   }
@@ -7557,9 +7550,9 @@ best_access_path(JOIN      *join,
                                                join->outer_join)));
     }
   }
-  trace_access_scan.add("chosen", best_key == NULL);
 
 skip_table_scan:
+  trace_access_scan.add("chosen", best_key == NULL);
 
   /* Update the cost information for the current partial plan */
   pos->records_read= records;
@@ -11159,6 +11152,11 @@ make_join_readinfo(JOIN *join, ulonglong
   uint last_sjm_table= MAX_TABLES;
   DBUG_ENTER("make_join_readinfo");
 
+  Opt_trace_context * const trace= join->thd->opt_trace;
+  Opt_trace_object wrapper(trace);
+
+  Opt_trace_object trace_refine_plan(trace, "refine_plan");
+
   if (!join->select_lex->sj_nests.is_empty() &&
       setup_semijoin_dups_elimination(join, options, no_jbuf_after))
     DBUG_RETURN(TRUE); /* purecov: inspected */
@@ -11183,6 +11181,11 @@ make_join_readinfo(JOIN *join, ulonglong
     table->status=STATUS_NO_RECORD;
     pick_table_access_method (tab);
 
+    Opt_trace_object trace_refine_table(trace, "refine_plan_for_table");
+    trace_refine_table.add_str("database", join->tables_list->db,
+                              strlen(join->tables_list->db)).
+      add_str("table", table->alias, strlen(table->alias));
+
     if (tab->loosescan_match_tab)
     {
       if (!(tab->loosescan_buf= (uchar*)join->thd->alloc(tab->
@@ -11308,6 +11311,8 @@ make_join_readinfo(JOIN *join, ulonglong
             tab->select->quick->index != MAX_KEY && ! tab->table->key_read)
           push_index_cond(tab, tab->select->quick->index, icp_other_tables_ok);
       }
+      trace_refine_table.add_str("scan_type",
+                                 tab->type==JT_NEXT ? "index" : "table");
       break;
     case JT_FT:
       break;

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2010-08-16 06:58:42 +0000
+++ b/sql/sql_update.cc	2010-11-17 12:27:39 +0000
@@ -340,6 +340,13 @@ int mysql_update(THD *thd,
   table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
 
   select= make_select(table, 0, 0, conds, 0, &error);
+
+  { // Enter scope for optimizer trace wrapper
+  Opt_trace_object wrapper(thd->opt_trace);
+  wrapper.add_str("database", table_list->db,
+                  strlen(table_list->db)).
+    add_str("table", table_list->alias, strlen(table_list->alias));
+
   if (error || !limit ||
       (select && select->check_quick(thd, safe_update, limit)))
   {
@@ -359,6 +366,7 @@ int mysql_update(THD *thd,
     my_ok(thd);				// No matching records
     DBUG_RETURN(0);
   }
+  } // Ends scope for optimizer trace wrapper
 
   /* If running in safe sql mode, don't allow updates without keys */
   if (table->quick_keys.is_clear_all())

=== modified file 'sql/sys_vars.cc'
--- a/sql/sys_vars.cc	2010-11-05 10:20:12 +0000
+++ b/sql/sys_vars.cc	2010-11-17 12:27:39 +0000
@@ -1429,7 +1429,7 @@ static Sys_var_flagset Sys_optimizer_tra
        "optimizer_trace_features",
        "Enables/disables tracing of selected features of the Optimizer:"
        " optimizer_trace_features=option=val[,option=val...], where option is one of"
-       " {misc, greedy_search}"
+       " {misc, greedy_search, range_optimizer}"
        " and val is one of {on, off, default}",
        SESSION_VAR(optimizer_trace_features), CMD_LINE(REQUIRED_ARG),
        Opt_trace_context::feature_names,

Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20101117122739-j15no9ff6u2v7t9w.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3233) WL#5594Jorgen Loland17 Nov
  • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3233)WL#5594Guilhem Bichot19 Nov