List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:November 22 2010 10:01am
Subject:bzr push into mysql-next-mr-bugfixing branch (jorgen.loland:3233 to 3234)
WL#5594
View as plain text  
 3234 Jorgen Loland	2010-11-22
      WL#5594 - Add optimizer traces to the range optimizer
      
      Minor changes as requested by reviewer.
     @ mysql-test/t/optimizer_trace_range.test
        Added test for group quick select without grouping attribute
     @ sql/sql_delete.cc
        Indentation
     @ sql/sql_update.cc
        Indentation

    modified:
      mysql-test/r/optimizer_trace_charset.result
      mysql-test/r/optimizer_trace_no_prot.result
      mysql-test/r/optimizer_trace_ps_prot.result
      mysql-test/r/optimizer_trace_range.result
      mysql-test/t/optimizer_trace_range.test
      sql/opt_range.cc
      sql/sql_delete.cc
      sql/sql_select.cc
      sql/sql_update.cc
 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 'mysql-test/r/optimizer_trace_charset.result'
--- a/mysql-test/r/optimizer_trace_charset.result	2010-11-17 12:27:39 +0000
+++ b/mysql-test/r/optimizer_trace_charset.result	2010-11-22 09:59:17 +0000
@@ -38,7 +38,7 @@ explain extended select '��� from t1 
                 "table": "t1",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               }
             ] /* records_estimation */
           },
@@ -50,8 +50,8 @@ explain extended select '��� from t1 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-            } /* refine_plan */
+            "refine_plan": [
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -87,7 +87,7 @@ explain extended select 'ÁÂÃÄÅ' fro
                 "table": "t1",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               }
             ] /* records_estimation */
           },
@@ -99,8 +99,8 @@ explain extended select 'ÁÂÃÄÅ' fro
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-            } /* refine_plan */
+            "refine_plan": [
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -151,7 +151,7 @@ select * from v1 where v1.col = '���	
                 "table": "t1",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               }
             ] /* records_estimation */
           },
@@ -163,8 +163,8 @@ select * from v1 where v1.col = ' */
           },
           {
-            "refine_plan": {
-            } /* refine_plan */
+            "refine_plan": [
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -203,7 +203,7 @@ select * from v1 where v1.col = '���	
                 "table": "v1",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               }
             ] /* records_estimation */
           },
@@ -215,8 +215,8 @@ select * from v1 where v1.col = '���	
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-            } /* refine_plan */
+            "refine_plan": [
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -351,13 +351,13 @@ select * from t1 where c < '���	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "index"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* 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-17 12:27:39 +0000
+++ b/mysql-test/r/optimizer_trace_no_prot.result	2010-11-22 09:59:17 +0000
@@ -46,7 +46,7 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               }
             ]
           },
@@ -58,8 +58,8 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
             }
           },
           {
-            "refine_plan": {
-            }
+            "refine_plan": [
+            ]
           }
         ]
       }
@@ -134,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
-50.2096
+50.3069
 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,"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 (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"}]},{"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
-5.9947
+6.0185
 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
@@ -186,7 +186,7 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               }
             ] /* records_estimation */
           },
@@ -198,8 +198,8 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-            } /* refine_plan */
+            "refine_plan": [
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -309,7 +309,7 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               }
             ] /* records_estimation */
           },
@@ -321,8 +321,8 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-            } /* refine_plan */
+            "refine_plan": [
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -450,7 +450,7 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               }
             ] /* records_estimation */
           },
@@ -462,8 +462,8 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-            } /* refine_plan */
+            "refine_plan": [
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -658,13 +658,13 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "",
                 "table": "",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -776,7 +776,7 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               }
             ] /* records_estimation */
           },
@@ -894,13 +894,13 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
                   } /* attaching_conditions_to_tables */
                 },
                 {
-                  "refine_plan": {
-                    "refine_plan_for_table": {
+                  "refine_plan": [
+                    {
                       "database": "",
                       "table": "",
                       "scan_type": "table"
-                    } /* refine_plan_for_table */
-                  } /* refine_plan */
+                    }
+                  ] /* refine_plan */
                 }
               ] /* steps */
             } /* join_optimization */
@@ -918,7 +918,7 @@ select (@query:=QUERY)+NULL, (@trace:=TR
 NULL	NULL
 select length(@trace);
 length(@trace)
-9316
+9257
 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
@@ -927,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)
-1043
+972
 select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
 (@query2:=QUERY)+NULL	(@trace2:=TRACE)+NULL
 NULL	NULL
@@ -935,7 +935,7 @@ select length(@trace2),
 (length(@trace2) + @missing_bytes) = length(@trace),
 @query2 = @query;
 length(@trace2)	(length(@trace2) + @missing_bytes) = length(@trace)	@query2 = @query
-8273	1	1
+8285	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)
@@ -1031,7 +1031,7 @@ explain SELECT c FROM t5 where c+1 in (s
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               },
               {
                 "database": "test",
@@ -1211,12 +1211,12 @@ explain SELECT c FROM t5 where c+1 in (s
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t6"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1292,7 +1292,7 @@ explain SELECT c FROM t5 where c+1 in (s
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               }
             ] /* records_estimation */
           },
@@ -1304,8 +1304,8 @@ explain SELECT c FROM t5 where c+1 in (s
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-            } /* refine_plan */
+            "refine_plan": [
+            ] /* refine_plan */
           },
           {
             "transformation": {
@@ -1435,12 +1435,12 @@ explain SELECT c FROM t5 where c+1 in (s
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t6"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1552,13 +1552,13 @@ explain extended select * from t1 where 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1616,13 +1616,13 @@ explain extended select * from t1 where 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1730,13 +1730,13 @@ explain extended select * from t1 where 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1794,13 +1794,13 @@ explain extended select * from t1 where 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1925,13 +1925,13 @@ explain extended select * from t1 where 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2002,13 +2002,13 @@ explain extended select * from t1 where 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2132,13 +2132,13 @@ explain extended select * from t1 where 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2209,13 +2209,13 @@ explain extended select * from t1 where 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2369,18 +2369,18 @@ explain select * from t1,t2	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "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 */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2456,18 +2456,18 @@ explain select * from t1,t2	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "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 */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2634,13 +2634,13 @@ where a1 in (select b1 from t2_16 where 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1_16",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2711,13 +2711,13 @@ where a1 in (select b1 from t2_16 where 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t2_16",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2901,18 +2901,18 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "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 */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2983,13 +2983,13 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t2",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3086,13 +3086,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3163,13 +3163,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t2",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3234,13 +3234,13 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "",
                 "table": "",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3391,13 +3391,13 @@ concat(c1,'y') IN
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           },
           {
             "transformation": {
@@ -3471,13 +3471,13 @@ concat(c1,'y') IN
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t2",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3535,13 +3535,13 @@ concat(c1,'y') IN
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t2",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3586,14 +3586,14 @@ select * from t1,t2	{
                 "table": "t1",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               },
               {
                 "database": "test",
                 "table": "t2",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               }
             ] /* records_estimation */
           },
@@ -3605,8 +3605,8 @@ select * from t1,t2	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-            } /* refine_plan */
+            "refine_plan": [
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3720,21 +3720,21 @@ trace
                 "table": "t3",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               },
               {
                 "database": "test",
                 "table": "t1",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               },
               {
                 "database": "test",
                 "table": "t2",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               },
               {
                 "database": "test",
@@ -3851,21 +3851,21 @@ trace
                 "table": "t3",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               },
               {
                 "database": "test",
                 "table": "t1",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               },
               {
                 "database": "test",
                 "table": "t2",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               },
               {
                 "database": "test",
@@ -4034,18 +4034,18 @@ select * from t1 left join t2 on t2.a=50
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "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 */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4170,13 +4170,13 @@ select * from t1 where (t1.a,t1.b) not i
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4255,13 +4255,13 @@ select * from t1 where (t1.a,t1.b) not i
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t2",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4412,18 +4412,18 @@ trace
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "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 */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4734,13 +4734,13 @@ insert into t6 select * from t6 where d>
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t6",
                 "scan_type": "index"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4789,7 +4789,7 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               },
               {
                 "database": "test",
@@ -4882,13 +4882,13 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t6",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4937,7 +4937,7 @@ delete t6 from t5, t6 where d>7000	{
                 "table": "t5",
                 "records": 1,
                 "cost": 1,
-                "table_type": "system_table"
+                "table_type": "system"
               },
               {
                 "database": "test",
@@ -5024,13 +5024,13 @@ delete t6 from t5, t6 where d>7000	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t6",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5253,13 +5253,13 @@ select sum(data) into ret from t1	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5338,13 +5338,13 @@ select sum(data) into ret from t1	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5420,13 +5420,13 @@ select sum(data) into ret from t1	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5441,30 +5441,15 @@ select * from t6 where d in (select f1()
 d
 select * from information_schema.OPTIMIZER_TRACE|
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
-select * from t6 where d in (select f1() from t2 where s="c")	{
+select sum(data) into ret from t1	{
   "steps": [
     {
-      "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t6` where `d` in (/* select#2 */ select `f1`() from `test`.`t2` where (`s` = 'c'))"
+      "expanded_query": "/* select#1 */ select sum(`data`) AS `sum(data)` from `test`.`t1`"
     },
     {
       "join_preparation": {
         "select#": 1,
         "steps": [
-          {
-            "join_preparation": {
-              "select#": 2,
-              "steps": [
-                {
-                  "transformation": {
-                    "select#": 2,
-                    "from": "IN (SELECT)",
-                    "to": "semijoin",
-                    "chosen": true
-                  } /* transformation */
-                }
-              ] /* steps */
-            } /* join_preparation */
-          }
         ] /* steps */
       } /* join_preparation */
     },
@@ -5473,319 +5458,61 @@ select * from t6 where d in (select f1()
         "select#": 1,
         "steps": [
           {
-            "transformation": {
-              "select#": 2,
-              "from": "IN (SELECT)",
-              "to": "semijoin",
-              "chosen": true
-            } /* transformation */
-          },
-          {
-            "condition_processing": {
-              "condition": "WHERE",
-              "original_condition": "(1 and (`test`.`t2`.`s` = 'c') and (`test`.`t6`.`d` = `f1`()))",
-              "after_equality_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
-              "after_constant_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
-              "after_trivial_conditions_removal": "(multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))"
-            } /* condition_processing */
-          },
-          {
-            "ref-optimizer-key-uses": [
-              {
-                "condition": "t6.d= `f1`()",
-                "null_rejecting": false
-              }
-            ] /* ref-optimizer-key-uses */
-          },
-          {
             "constant_tables": [
             ] /* constant_tables */,
             "records_estimation": [
               {
                 "database": "test",
-                "table": "t6",
-                "range_analysis": {
-                  "table_scan": {
-                    "records": 4,
-                    "cost": 4.9068
-                  } /* table_scan */,
-                  "potential_range_indices": [
-                    {
-                      "index": "d",
-                      "usable": true,
-                      "key_parts": [
-                        "d"
-                      ] /* key_parts */
-                    }
-                  ] /* potential_range_indices */,
-                  "best_covering_index_scan": {
-                    "index": "d",
-                    "cost": 1.8698,
-                    "chosen": true
-                  } /* best_covering_index_scan */,
-                  "group_index_range": {
-                    "chosen": false,
-                    "cause": "not_single_table"
-                  } /* group_index_range */,
-                  "analyzing_range_alternatives": {
-                    "range_scan_alternatives": [
-                      {
-                        "index": "d",
-                        "ranges": [
-                          "3 <= d <= 3"
-                        ] /* ranges */,
-                        "index_only": true,
-                        "records": 1,
-                        "cost": 2.21,
-                        "rowid_ordered": true,
-                        "chosen": false,
-                        "cause": "cost"
-                      }
-                    ] /* range_scan_alternatives */,
-                    "analyzing_roworder_intersect": {
-                      "usable": false,
-                      "cause": "too_few_roworder_scans"
-                    } /* analyzing_roworder_intersect */
-                  } /* analyzing_range_alternatives */
-                } /* range_analysis */
-              },
-              {
-                "database": "test",
-                "table": "t2",
+                "table": "t1",
                 "table_scan": {
-                  "records": 3,
+                  "records": 2,
                   "cost": 2
                 } /* table_scan */
               }
             ] /* records_estimation */
           },
           {
-            "pulled_out_semijoin_tables": [
-            ] /* pulled_out_semijoin_tables */
-          },
-          {
-            "execution_plan_for_potential_materialization": {
-              "steps": [
-                {
-                  "considered_execution_plans": [
-                    {
-                      "database": "test",
-                      "table": "t2",
-                      "best_access_path": {
-                        "considered_access_paths": [
-                          {
-                            "access_type": "scan",
-                            "using_join_cache": true,
-                            "records": 3,
-                            "cost": 2.0212,
-                            "chosen": true
-                          }
-                        ] /* considered_access_paths */
-                      } /* best_access_path */,
-                      "cost_for_plan": 2.0212,
-                      "records_for_plan": 3,
-                      "semijoin_strategy_choice": [
-                      ] /* semijoin_strategy_choice */,
-                      "chosen": true
-                    }
-                  ] /* considered_execution_plans */
-                }
-              ] /* steps */
-            } /* execution_plan_for_potential_materialization */
-          },
-          {
             "considered_execution_plans": [
               {
                 "database": "test",
-                "table": "t2",
+                "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
                       "access_type": "scan",
                       "using_join_cache": true,
-                      "records": 3,
-                      "cost": 2.0212,
-                      "chosen": true
-                    }
-                  ] /* considered_access_paths */
-                } /* best_access_path */,
-                "cost_for_plan": 2.0212,
-                "records_for_plan": 3,
-                "semijoin_strategy_choice": [
-                ] /* semijoin_strategy_choice */,
-                "rest_of_plan": [
-                  {
-                    "database": "test",
-                    "table": "t6",
-                    "best_access_path": {
-                      "considered_access_paths": [
-                        {
-                          "access_type": "ref",
-                          "index": "d",
-                          "records": 1,
-                          "cost": 3,
-                          "chosen": true
-                        },
-                        {
-                          "access_type": "scan",
-                          "using_join_cache": true,
-                          "records": 1,
-                          "cost": 2.6076,
-                          "chosen": true
-                        }
-                      ] /* considered_access_paths */
-                    } /* best_access_path */,
-                    "cost_for_plan": 4.6289,
-                    "records_for_plan": 3,
-                    "semijoin_strategy_choice": [
-                      {
-                        "strategy": "DuplicatesWeedout",
-                        "cost": 5.2289,
-                        "records": 1,
-                        "duplicate_tables_left": true,
-                        "chosen": true
-                      }
-                    ] /* semijoin_strategy_choice */,
-                    "chosen": true
-                  }
-                ] /* rest_of_plan */
-              },
-              {
-                "database": "test",
-                "table": "t6",
-                "best_access_path": {
-                  "considered_access_paths": [
-                    {
-                      "access_type": "ref",
-                      "index": "d",
-                      "records": 1,
-                      "cost": 1,
+                      "records": 2,
+                      "cost": 2.0154,
                       "chosen": true
-                    },
-                    {
-                      "access_type": "scan",
-                      "cost": 2,
-                      "records": 4,
-                      "cause": "cost",
-                      "chosen": false
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
-                "cost_for_plan": 1,
-                "records_for_plan": 1,
-                "semijoin_strategy_choice": [
-                ] /* semijoin_strategy_choice */,
-                "rest_of_plan": [
-                  {
-                    "database": "test",
-                    "table": "t2",
-                    "best_access_path": {
-                      "considered_access_paths": [
-                        {
-                          "access_type": "scan",
-                          "using_join_cache": true,
-                          "records": 3,
-                          "cost": 2.0213,
-                          "chosen": true
-                        }
-                      ] /* considered_access_paths */
-                    } /* best_access_path */,
-                    "cost_for_plan": 3.0213,
-                    "records_for_plan": 3,
-                    "semijoin_strategy_choice": [
-                      {
-                        "strategy": "FirstMatch",
-                        "recompute_best_access_paths": {
-                          "cause": "join_buffering_not_possible",
-                          "tables": [
-                            {
-                              "database": "test",
-                              "table": "t2",
-                              "best_access_path": {
-                                "considered_access_paths": [
-                                  {
-                                    "access_type": "scan",
-                                    "using_join_cache": true,
-                                    "records": 3,
-                                    "cost": 2.0213,
-                                    "chosen": true
-                                  }
-                                ] /* considered_access_paths */
-                              } /* best_access_path */
-                            }
-                          ] /* tables */
-                        } /* recompute_best_access_paths */,
-                        "cost": 3.0213,
-                        "records": 1,
-                        "chosen": true
-                      },
-                      {
-                        "strategy": "MaterializationLookup",
-                        "cost": 3.2212,
-                        "records": 1,
-                        "duplicate_tables_left": false,
-                        "chosen": false
-                      },
-                      {
-                        "strategy": "DuplicatesWeedout",
-                        "cost": 3.6213,
-                        "records": 1,
-                        "duplicate_tables_left": false,
-                        "chosen": false
-                      }
-                    ] /* semijoin_strategy_choice */,
-                    "chosen": true
-                  }
-                ] /* rest_of_plan */
+                "cost_for_plan": 2.0154,
+                "records_for_plan": 2,
+                "chosen": true
               }
             ] /* considered_execution_plans */
           },
           {
-            "reconsidering_access_paths_for_semijoin": {
-              "strategy": "FirstMatch",
-              "database": "test",
-              "table": "t2",
-              "best_access_path": {
-                "considered_access_paths": [
-                  {
-                    "access_type": "scan",
-                    "records": 3,
-                    "cost": 2.0212,
-                    "chosen": true
-                  }
-                ] /* considered_access_paths */
-              } /* best_access_path */
-            } /* reconsidering_access_paths_for_semijoin */
-          },
-          {
             "attaching_conditions_to_tables": {
-              "original_condition": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
+              "original_condition": null,
               "attached_conditions": [
                 {
                   "database": "test",
-                  "table": "t6",
-                  "attached": "(`test`.`t6`.`d` = `f1`())"
-                },
-                {
-                  "database": "test",
-                  "table": "t2",
-                  "attached": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
+                  "table": "t1",
+                  "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
-                "database": "test",
-                "table": "t6"
-              } /* refine_plan_for_table */,
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
-                "table": "t2",
+                "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5881,13 +5608,13 @@ select sum(data) into ret from t1	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -6079,13 +5806,13 @@ select sum(data) into ret from t1	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -6520,17 +6247,17 @@ select d into res from t6 where d in (se
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "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 */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -6626,13 +6353,115 @@ select sum(data) into ret from t1	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}	0	0
+insert into t1 values("z",0)	{
+  "steps": [
+    {
+      "expanded_query": "/* select#1 */ select  from dual"
+    }
+  ] /* steps */
+}	0	0
+delete from t1 where id="z"	{
+  "steps": [
+    {
+      "expanded_query": "/* select#1 */ select  from dual where (`id` = 'z')"
+    },
+    {
+      "database": "test",
+      "table": "t1",
+      "range_analysis": {
+        "table_scan": {
+          "records": 3,
+          "cost": 4.7154
+        } /* table_scan */
+      } /* range_analysis */
+    }
+  ] /* steps */
+}	0	0
+select sum(data) into ret from t1	{
+  "steps": [
+    {
+      "expanded_query": "/* select#1 */ select sum(`data`) AS `sum(data)` from `test`.`t1`"
+    },
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "database": "test",
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "database": "test",
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "scan",
+                      "using_join_cache": true,
+                      "records": 2,
+                      "cost": 2.0154,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0154,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": null,
+              "attached_conditions": [
+                {
+                  "database": "test",
+                  "table": "t1",
+                  "attached": null
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -6743,13 +6572,13 @@ explain select * from v1 where id="b"	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -6896,13 +6725,13 @@ explain select * from v1 where id="b"	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -6980,13 +6809,13 @@ explain select * from v1 where id="b"	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "",
                 "table": "v1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -7079,13 +6908,13 @@ VARIABLE_NAME="optimizer_trace"	{
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "information_schema",
                 "table": "session_variables",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -7191,13 +7020,13 @@ select TRACE into dumpfile 'MYSQLTEST_VA
             }
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "",
                 "table": "",
                 "scan_type": "table"
               }
-            }
+            ]
           }
         ]
       }

=== modified file 'mysql-test/r/optimizer_trace_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_ps_prot.result	2010-11-06 09:56:51 +0000
+++ b/mysql-test/r/optimizer_trace_ps_prot.result	2010-11-22 09:59:17 +0000
@@ -1,5 +1,6 @@
 select * from information_schema.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
+set optimizer_trace_max_mem_size=1048576;
 set @@session.optimizer_trace="enabled=on";
 select * from information_schema.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
@@ -40,6 +41,13 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
               "t5"
             ],
             "records_estimation": [
+              {
+                "database": "test",
+                "table": "t5",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              }
             ]
           },
           {
@@ -48,6 +56,10 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
               "attached_conditions": [
               ]
             }
+          },
+          {
+            "refine_plan": [
+            ]
           }
         ]
       }
@@ -78,10 +90,36 @@ SELECT (SELECT 1 FROM t6 WHERE d = c) AS
             ],
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                }
+                "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"
               }
             ]
           }
@@ -96,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
-45.1596
+50.3069
 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": []},{"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": [{"table": "t6","more_range_optimizer_trace": {"TODO?": "yes!"}}]}],"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"}]},{"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.3688
+6.0185
 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
@@ -143,6 +181,13 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
               "t5"
             ] /* constant_tables */,
             "records_estimation": [
+              {
+                "database": "test",
+                "table": "t5",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              }
             ] /* records_estimation */
           },
           {
@@ -151,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 */
@@ -181,10 +230,36 @@ EXPLAIN SELECT (SELECT 1 FROM t6 WHERE d
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                } /* more_range_optimizer_trace */
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 2,
+                    "cost": 4.5034
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "d",
+                      "usable": true,
+                      "key_parts": [
+                        "d"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "best_covering_index_scan": {
+                    "index": "d",
+                    "cost": 1.4233,
+                    "chosen": true
+                  } /* best_covering_index_scan */,
+                  "impossible_condition": {
+                    "table": "t6",
+                    "field": "d",
+                    "cause": "comparison_with_null_always_false"
+                  } /* impossible_condition */,
+                  "impossible_range": true
+                } /* range_analysis */,
+                "records": 0,
+                "cause": "impossible_where_condition"
               }
             ] /* records_estimation */
           }
@@ -229,6 +304,13 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
               "t5"
             ] /* constant_tables */,
             "records_estimation": [
+              {
+                "database": "test",
+                "table": "t5",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              }
             ] /* records_estimation */
           },
           {
@@ -237,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 */
@@ -267,10 +353,36 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                } /* more_range_optimizer_trace */
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 2,
+                    "cost": 4.5034
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "d",
+                      "usable": true,
+                      "key_parts": [
+                        "d"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "best_covering_index_scan": {
+                    "index": "d",
+                    "cost": 1.4233,
+                    "chosen": true
+                  } /* best_covering_index_scan */,
+                  "impossible_condition": {
+                    "table": "t6",
+                    "field": "d",
+                    "cause": "comparison_with_null_always_false"
+                  } /* impossible_condition */,
+                  "impossible_range": true
+                } /* range_analysis */,
+                "records": 0,
+                "cause": "impossible_where_condition"
               }
             ] /* records_estimation */
           }
@@ -333,6 +445,13 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
               "t5"
             ] /* constant_tables */,
             "records_estimation": [
+              {
+                "database": "test",
+                "table": "t5",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              }
             ] /* records_estimation */
           },
           {
@@ -341,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 */
@@ -371,10 +494,36 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                } /* more_range_optimizer_trace */
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 2,
+                    "cost": 4.5034
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "d",
+                      "usable": true,
+                      "key_parts": [
+                        "d"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "best_covering_index_scan": {
+                    "index": "d",
+                    "cost": 1.4233,
+                    "chosen": true
+                  } /* best_covering_index_scan */,
+                  "impossible_condition": {
+                    "table": "t6",
+                    "field": "d",
+                    "cause": "comparison_with_null_always_false"
+                  } /* impossible_condition */,
+                  "impossible_range": true
+                } /* range_analysis */,
+                "records": 0,
+                "cause": "impossible_where_condition"
               }
             ] /* records_estimation */
           }
@@ -410,10 +559,36 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                } /* more_range_optimizer_trace */
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 2,
+                    "cost": 4.5034
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "d",
+                      "usable": true,
+                      "key_parts": [
+                        "d"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "best_covering_index_scan": {
+                    "index": "d",
+                    "cost": 1.4233,
+                    "chosen": true
+                  } /* best_covering_index_scan */,
+                  "impossible_condition": {
+                    "table": "t6",
+                    "field": "d",
+                    "cause": "comparison_with_null_always_false"
+                  } /* impossible_condition */,
+                  "impossible_range": true
+                } /* range_analysis */,
+                "records": 0,
+                "cause": "impossible_where_condition"
               }
             ] /* records_estimation */
           }
@@ -439,6 +614,7 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "",
                 "table": "",
                 "table_scan": {
                   "records": 0,
@@ -450,13 +626,15 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
           {
             "considered_execution_plans": [
               {
+                "database": "",
                 "table": "",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 10,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 0,
+                      "cost": 10,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -472,11 +650,21 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "",
                   "table": "",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "",
+                "table": "",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -567,6 +755,13 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
               "t5"
             ] /* constant_tables */,
             "records_estimation": [
+              {
+                "database": "test",
+                "table": "t5",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              }
             ] /* records_estimation */
           },
           {
@@ -639,6 +834,7 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
                   ] /* constant_tables */,
                   "records_estimation": [
                     {
+                      "database": "",
                       "table": "",
                       "table_scan": {
                         "records": 0,
@@ -650,13 +846,15 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
                 {
                   "considered_execution_plans": [
                     {
+                      "database": "",
                       "table": "",
                       "best_access_path": {
                         "considered_access_paths": [
                           {
-                            "access_type": "table scan",
-                            "cost": 10,
+                            "access_type": "scan",
+                            "using_join_cache": true,
                             "records": 0,
+                            "cost": 10,
                             "chosen": true
                           }
                         ] /* considered_access_paths */
@@ -672,11 +870,21 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
                     "original_condition": null,
                     "attached_conditions": [
                       {
+                        "database": "",
                         "table": "",
                         "attached": null
                       }
                     ] /* attached_conditions */
                   } /* attaching_conditions_to_tables */
+                },
+                {
+                  "refine_plan": [
+                    {
+                      "database": "",
+                      "table": "",
+                      "scan_type": "table"
+                    }
+                  ] /* refine_plan */
                 }
               ] /* steps */
             } /* join_optimization */
@@ -694,16 +902,16 @@ select (@query:=QUERY)+NULL, (@trace:=TR
 NULL	NULL
 select length(@trace);
 length(@trace)
-8173
+8802
 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
-0
+1
 SELECT * FROM t5 WHERE 5 IN (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null));
 c
 select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;
 (@missing_bytes:=missing_bytes_beyond_max_mem_size)
-0
+517
 select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
 (@query2:=QUERY)+NULL	(@trace2:=TRACE)+NULL
 NULL	NULL
@@ -711,12 +919,12 @@ select length(@trace2),
 (length(@trace2) + @missing_bytes) = length(@trace),
 @query2 = @query;
 length(@trace2)	(length(@trace2) + @missing_bytes) = length(@trace)	@query2 = @query
-8173	1	1
+8285	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)
 between (@@optimizer_trace_max_mem_size-100) and (@@optimizer_trace_max_mem_size+100)
-0
+1
 select instr(@trace, @trace2) = 1;
 instr(@trace, @trace2) = 1
 1
@@ -734,7 +942,7 @@ select 1;
 select * from information_schema.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
 		381	0
-set optimizer_trace_max_mem_size=default;
+set optimizer_trace_max_mem_size=1048576;
 explain SELECT c FROM t5 where c+1 in (select d+1 from t6 where d is null);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t5	system	NULL	NULL	NULL	NULL	1	
@@ -803,10 +1011,59 @@ explain SELECT c FROM t5 where c+1 in (s
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
+                "table": "t5",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              },
+              {
+                "database": "test",
                 "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                } /* more_range_optimizer_trace */
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 2,
+                    "cost": 4.5034
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "d",
+                      "usable": true,
+                      "key_parts": [
+                        "d"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "best_covering_index_scan": {
+                    "index": "d",
+                    "cost": 1.4233,
+                    "chosen": true
+                  } /* best_covering_index_scan */,
+                  "group_index_range": {
+                    "chosen": false,
+                    "cause": "not_single_table"
+                  } /* group_index_range */,
+                  "analyzing_range_alternatives": {
+                    "range_scan_alternatives": [
+                      {
+                        "index": "d",
+                        "ranges": [
+                          "NULL <= d <= NULL"
+                        ] /* ranges */,
+                        "index_only": true,
+                        "records": 1,
+                        "cost": 2.21,
+                        "rowid_ordered": true,
+                        "chosen": false,
+                        "cause": "cost"
+                      }
+                    ] /* range_scan_alternatives */,
+                    "analyzing_roworder_intersect": {
+                      "usable": false,
+                      "cause": "too_few_roworder_scans"
+                    } /* analyzing_roworder_intersect */
+                  } /* analyzing_range_alternatives */
+                } /* range_analysis */
               }
             ] /* records_estimation */
           },
@@ -820,20 +1077,22 @@ explain SELECT c FROM t5 where c+1 in (s
                 {
                   "considered_execution_plans": [
                     {
+                      "database": "test",
                       "table": "t6",
                       "best_access_path": {
                         "considered_access_paths": [
                           {
-                            "access_type": "index",
+                            "access_type": "ref",
                             "index": "d",
-                            "cost": 1,
                             "records": 1,
+                            "cost": 1,
                             "chosen": true
                           },
                           {
-                            "access_type": "table scan",
+                            "access_type": "scan",
                             "cost": 2,
                             "records": 2,
+                            "cause": "cost",
                             "chosen": false
                           }
                         ] /* considered_access_paths */
@@ -870,20 +1129,22 @@ explain SELECT c FROM t5 where c+1 in (s
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t6",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "d",
-                      "cost": 1,
                       "records": 1,
+                      "cost": 1,
                       "chosen": true
                     },
                     {
-                      "access_type": "table scan",
+                      "access_type": "scan",
                       "cost": 2,
                       "records": 2,
+                      "cause": "cost",
                       "chosen": false
                     }
                   ] /* considered_access_paths */
@@ -926,11 +1187,20 @@ explain SELECT c FROM t5 where c+1 in (s
               "original_condition": "(isnull(`test`.`t6`.`d`) and ((NULL + 1) = (`test`.`t6`.`d` + 1)))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t6",
                   "attached": "(isnull(`test`.`t6`.`d`) and ((NULL + 1) = (`test`.`t6`.`d` + 1)))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t6"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1001,6 +1271,13 @@ explain SELECT c FROM t5 where c+1 in (s
               "t5"
             ] /* constant_tables */,
             "records_estimation": [
+              {
+                "database": "test",
+                "table": "t5",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              }
             ] /* records_estimation */
           },
           {
@@ -1011,6 +1288,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)",
@@ -1047,30 +1328,74 @@ explain SELECT c FROM t5 where c+1 in (s
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                } /* more_range_optimizer_trace */
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 2,
+                    "cost": 4.5034
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "d",
+                      "usable": true,
+                      "key_parts": [
+                        "d"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "best_covering_index_scan": {
+                    "index": "d",
+                    "cost": 1.4233,
+                    "chosen": true
+                  } /* best_covering_index_scan */,
+                  "group_index_range": {
+                    "chosen": false,
+                    "cause": "not_group_by_or_distinct"
+                  } /* group_index_range */,
+                  "analyzing_range_alternatives": {
+                    "range_scan_alternatives": [
+                      {
+                        "index": "d",
+                        "ranges": [
+                          "NULL <= d <= NULL"
+                        ] /* ranges */,
+                        "index_only": true,
+                        "records": 1,
+                        "cost": 2.21,
+                        "rowid_ordered": true,
+                        "chosen": false,
+                        "cause": "cost"
+                      }
+                    ] /* range_scan_alternatives */,
+                    "analyzing_roworder_intersect": {
+                      "usable": false,
+                      "cause": "too_few_roworder_scans"
+                    } /* analyzing_roworder_intersect */
+                  } /* analyzing_range_alternatives */
+                } /* range_analysis */
               }
             ] /* records_estimation */
           },
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t6",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "d",
-                      "cost": 1,
                       "records": 1,
+                      "cost": 1,
                       "chosen": true
                     },
                     {
-                      "access_type": "table scan",
+                      "access_type": "scan",
                       "cost": 2,
                       "records": 2,
+                      "cause": "cost",
                       "chosen": false
                     }
                   ] /* considered_access_paths */
@@ -1086,11 +1411,20 @@ explain SELECT c FROM t5 where c+1 in (s
               "original_condition": "isnull(`test`.`t6`.`d`)",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t6",
                   "attached": "isnull(`test`.`t6`.`d`)"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t6"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1158,6 +1492,7 @@ explain extended select * from t1 where 
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -1169,13 +1504,15 @@ explain extended select * from t1 where 
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0054,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0054,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -1191,11 +1528,21 @@ explain extended select * from t1 where 
               "original_condition": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": "<nop>((`test`.`t1`.`s1` > (/* select#2 */ select min(`test`.`t1`.`s2`) from `test`.`t1`)))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1209,6 +1556,7 @@ explain extended select * from t1 where 
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -1220,13 +1568,15 @@ explain extended select * from t1 where 
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0054,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0054,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -1242,11 +1592,21 @@ explain extended select * from t1 where 
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1310,6 +1670,7 @@ explain extended select * from t1 where 
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -1321,13 +1682,15 @@ explain extended select * from t1 where 
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0054,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0054,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -1343,11 +1706,21 @@ explain extended select * from t1 where 
               "original_condition": "<nop>((`test`.`t1`.`s1` > <min>(/* select#2 */ select max(`test`.`t1`.`s2`) from `test`.`t1`)))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": "<nop>((`test`.`t1`.`s1` > <min>(/* select#2 */ select max(`test`.`t1`.`s2`) from `test`.`t1`)))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1361,6 +1734,7 @@ explain extended select * from t1 where 
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -1372,13 +1746,15 @@ explain extended select * from t1 where 
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0054,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0054,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -1394,11 +1770,21 @@ explain extended select * from t1 where 
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1479,6 +1865,7 @@ explain extended select * from t1 where 
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -1490,13 +1877,15 @@ explain extended select * from t1 where 
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0054,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0054,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -1512,11 +1901,21 @@ explain extended select * from t1 where 
               "original_condition": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": "<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1543,6 +1942,7 @@ explain extended select * from t1 where 
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -1554,13 +1954,15 @@ explain extended select * from t1 where 
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0054,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0054,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -1576,11 +1978,21 @@ explain extended select * from t1 where 
               "original_condition": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": "(<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`)"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1660,6 +2072,7 @@ explain extended select * from t1 where 
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -1671,13 +2084,15 @@ explain extended select * from t1 where 
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0054,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0054,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -1693,11 +2108,21 @@ explain extended select * from t1 where 
               "original_condition": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": "<in_optimizer>((`test`.`t1`.`s1`,`test`.`t1`.`s2`),<exists>(/* select#2 */ select `test`.`t1`.`s2`,`test`.`t1`.`s1` from `test`.`t1` where ((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1724,6 +2149,7 @@ explain extended select * from t1 where 
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -1735,13 +2161,15 @@ explain extended select * from t1 where 
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0054,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0054,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -1757,11 +2185,21 @@ explain extended select * from t1 where 
               "original_condition": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": "((<cache>(`test`.`t1`.`s1`) = `test`.`t1`.`s2`) and (<cache>(`test`.`t1`.`s2`) = `test`.`t1`.`s1`))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1802,6 +2240,7 @@ explain select * from t1,t2	{
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 3,
@@ -1809,6 +2248,7 @@ explain select * from t1,t2	{
                 } /* table_scan */
               },
               {
+                "database": "test",
                 "table": "t2",
                 "table_scan": {
                   "records": 2,
@@ -1820,13 +2260,15 @@ explain select * from t1,t2	{
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t2",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0034,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0034,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -1835,13 +2277,15 @@ explain select * from t1,t2	{
                 "records_for_plan": 2,
                 "rest_of_plan": [
                   {
+                    "database": "test",
                     "table": "t1",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
-                          "access_type": "table scan",
-                          "cost": 2.0053,
+                          "access_type": "scan",
+                          "using_join_cache": true,
                           "records": 3,
+                          "cost": 2.0053,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
@@ -1853,13 +2297,15 @@ explain select * from t1,t2	{
                 ] /* rest_of_plan */
               },
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0051,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 3,
+                      "cost": 2.0051,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -1868,13 +2314,15 @@ explain select * from t1,t2	{
                 "records_for_plan": 3,
                 "rest_of_plan": [
                   {
+                    "database": "test",
                     "table": "t2",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
-                          "access_type": "table scan",
-                          "cost": 2.0036,
+                          "access_type": "scan",
+                          "using_join_cache": true,
                           "records": 2,
+                          "cost": 2.0036,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
@@ -1892,15 +2340,31 @@ explain select * from t1,t2	{
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t2",
                   "attached": null
                 },
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              },
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1909,7 +2373,7 @@ explain select * from t1,t2	{
 }	0	0
 select @@optimizer_trace_features;
 @@optimizer_trace_features
-misc=on,greedy_search=on
+misc=on,greedy_search=on,range_optimizer=on
 set @@optimizer_trace_features="greedy_search=off";
 explain select * from t1,t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1938,6 +2402,7 @@ explain select * from t1,t2	{
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 3,
@@ -1945,6 +2410,7 @@ explain select * from t1,t2	{
                 } /* table_scan */
               },
               {
+                "database": "test",
                 "table": "t2",
                 "table_scan": {
                   "records": 2,
@@ -1961,15 +2427,31 @@ explain select * from t1,t2	{
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t2",
                   "attached": null
                 },
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              },
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2092,6 +2574,7 @@ where a1 in (select b1 from t2_16 where 
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1_16",
                 "table_scan": {
                   "records": 3,
@@ -2103,13 +2586,15 @@ where a1 in (select b1 from t2_16 where 
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1_16",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0293,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 3,
+                      "cost": 2.0293,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -2125,11 +2610,21 @@ where a1 in (select b1 from t2_16 where 
               "original_condition": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1_16",
                   "attached": "<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1_16",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2156,6 +2651,7 @@ where a1 in (select b1 from t2_16 where 
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t2_16",
                 "table_scan": {
                   "records": 3,
@@ -2167,13 +2663,15 @@ where a1 in (select b1 from t2_16 where 
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t2_16",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0293,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 3,
+                      "cost": 2.0293,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -2189,11 +2687,21 @@ where a1 in (select b1 from t2_16 where 
               "original_condition": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t2_16",
                   "attached": "((`test`.`t2_16`.`b1` > '0') and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t2_16",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2283,6 +2791,7 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 3,
@@ -2290,6 +2799,7 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
                 } /* table_scan */
               },
               {
+                "database": "test",
                 "table": "t2",
                 "table_scan": {
                   "records": 3,
@@ -2301,13 +2811,15 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0051,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 3,
+                      "cost": 2.0051,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -2316,13 +2828,15 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
                 "records_for_plan": 3,
                 "rest_of_plan": [
                   {
+                    "database": "test",
                     "table": "t2",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
-                          "access_type": "table scan",
-                          "cost": 2.0054,
+                          "access_type": "scan",
+                          "using_join_cache": true,
                           "records": 3,
+                          "cost": 2.0054,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
@@ -2334,13 +2848,15 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
                 ] /* rest_of_plan */
               },
               {
+                "database": "test",
                 "table": "t2",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0051,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 3,
+                      "cost": 2.0051,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -2356,15 +2872,31 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
               "original_condition": "((`test`.`t2`.`c2` = `test`.`t1`.`c1`) and <in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`)))))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": "<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t2`.`c2`) = `test`.`t2`.`c2`))))"
                 },
                 {
+                  "database": "test",
                   "table": "t2",
                   "attached": "(`test`.`t2`.`c2` = `test`.`t1`.`c1`)"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              },
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2391,6 +2923,7 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t2",
                 "table_scan": {
                   "records": 3,
@@ -2402,13 +2935,15 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t2",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0051,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 3,
+                      "cost": 2.0051,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -2424,11 +2959,21 @@ WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2
               "original_condition": "((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t1`.`c1`) = 1))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t2",
                   "attached": "((`test`.`t2`.`c2` = 1) and (<cache>(`test`.`t1`.`c1`) = 1))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2481,6 +3026,7 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 3,
@@ -2492,13 +3038,15 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0051,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 3,
+                      "cost": 2.0051,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -2514,11 +3062,21 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
               "original_condition": "(`test`.`t1`.`c1` = 5)",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": "(`test`.`t1`.`c1` = 5)"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2545,6 +3103,7 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t2",
                 "table_scan": {
                   "records": 3,
@@ -2556,13 +3115,15 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t2",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0051,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 3,
+                      "cost": 2.0051,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -2578,11 +3139,21 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
               "original_condition": "(`test`.`t2`.`c2` = 5)",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t2",
                   "attached": "(`test`.`t2`.`c2` = 5)"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2603,6 +3174,7 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "",
                 "table": "",
                 "table_scan": {
                   "records": 1,
@@ -2614,13 +3186,15 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
           {
             "considered_execution_plans": [
               {
+                "database": "",
                 "table": "",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 10.05,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 1,
+                      "cost": 10.05,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -2636,11 +3210,21 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "",
                   "table": "",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "",
+                "table": "",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2747,6 +3331,7 @@ concat(c1,'y') IN
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 3,
@@ -2758,13 +3343,15 @@ concat(c1,'y') IN
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0051,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 3,
+                      "cost": 2.0051,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -2780,6 +3367,7 @@ concat(c1,'y') IN
               "original_condition": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": "(<in_optimizer>(concat(`test`.`t1`.`c1`,'x'),concat(`test`.`t1`.`c1`,'x') in (/* select#2 */ select left(`test`.`t2`.`c2`,8) from `test`.`t2`)) and <in_optimizer>(concat(`test`.`t1`.`c1`,'y'),concat(`test`.`t1`.`c1`,'y') in (/* select#3 */ select left(`test`.`t2`.`c2`,9) from `test`.`t2`)))"
                 }
@@ -2787,6 +3375,15 @@ concat(c1,'y') IN
             } /* attaching_conditions_to_tables */
           },
           {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
+          },
+          {
             "transformation": {
               "select#": 3,
               "from": "IN (SELECT)",
@@ -2814,6 +3411,7 @@ concat(c1,'y') IN
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t2",
                 "table_scan": {
                   "records": 3,
@@ -2825,13 +3423,15 @@ concat(c1,'y') IN
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t2",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0051,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 3,
+                      "cost": 2.0051,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -2847,11 +3447,21 @@ concat(c1,'y') IN
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t2",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2865,6 +3475,7 @@ concat(c1,'y') IN
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t2",
                 "table_scan": {
                   "records": 3,
@@ -2876,13 +3487,15 @@ concat(c1,'y') IN
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t2",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0051,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 3,
+                      "cost": 2.0051,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -2898,11 +3511,21 @@ concat(c1,'y') IN
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t2",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2942,14 +3565,32 @@ select * from t1,t2	{
               "t2"
             ] /* constant_tables */,
             "records_estimation": [
-            ] /* records_estimation */
-          },
-          {
-            "attaching_conditions_to_tables": {
-              "original_condition": null,
+              {
+                "database": "test",
+                "table": "t1",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              },
+              {
+                "database": "test",
+                "table": "t2",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              }
+            ] /* records_estimation */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": null,
               "attached_conditions": [
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3059,6 +3700,28 @@ trace
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
+                "table": "t3",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              },
+              {
+                "database": "test",
+                "table": "t1",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              },
+              {
+                "database": "test",
+                "table": "t2",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              },
+              {
+                "database": "test",
                 "table": "t4",
                 "table_scan": {
                   "records": 2,
@@ -3070,14 +3733,17 @@ trace
           {
             "pulled_out_semijoin_tables": [
               {
+                "database": "test",
                 "table": "t2",
                 "constant": true
               },
               {
+                "database": "test",
                 "table": "t1",
                 "constant": true
               },
               {
+                "database": "test",
                 "table": "t4",
                 "functionally_dependent": true
               }
@@ -3086,20 +3752,22 @@ trace
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t4",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "PRIMARY",
-                      "cost": 1,
                       "records": 1,
+                      "cost": 1,
                       "chosen": true
                     },
                     {
-                      "access_type": "table scan",
+                      "access_type": "scan",
                       "cost": 2,
                       "records": 2,
+                      "cause": "cost",
                       "chosen": false
                     }
                   ] /* considered_access_paths */
@@ -3163,6 +3831,28 @@ trace
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
+                "table": "t3",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              },
+              {
+                "database": "test",
+                "table": "t1",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              },
+              {
+                "database": "test",
+                "table": "t2",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              },
+              {
+                "database": "test",
                 "table": "t4",
                 "table_scan": {
                   "records": 2,
@@ -3174,20 +3864,22 @@ trace
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t4",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "PRIMARY",
-                      "cost": 1,
                       "records": 1,
+                      "cost": 1,
                       "chosen": true
                     },
                     {
-                      "access_type": "table scan",
+                      "access_type": "scan",
                       "cost": 2,
                       "records": 2,
+                      "cause": "cost",
                       "chosen": false
                     }
                   ] /* considered_access_paths */
@@ -3251,6 +3943,7 @@ select * from t1 left join t2 on t2.a=50
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -3258,6 +3951,7 @@ select * from t1 left join t2 on t2.a=50
                 } /* table_scan */
               },
               {
+                "database": "test",
                 "table": "t2",
                 "table_scan": {
                   "records": 2,
@@ -3269,13 +3963,15 @@ select * from t1 left join t2 on t2.a=50
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0034,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0034,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -3284,13 +3980,14 @@ select * from t1 left join t2 on t2.a=50
                 "records_for_plan": 2,
                 "rest_of_plan": [
                   {
+                    "database": "test",
                     "table": "t2",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
-                          "access_type": "table scan",
-                          "cost": 4.0068,
+                          "access_type": "scan",
                           "records": 2,
+                          "cost": 4.0068,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
@@ -3308,15 +4005,31 @@ select * from t1 left join t2 on t2.a=50
               "original_condition": "isnull(`test`.`t2`.`a`)",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": null
                 },
                 {
+                  "database": "test",
                   "table": "t2",
                   "attached": "(trigcond_if(found_match(t2), isnull(`test`.`t2`.`a`), true) and trigcond_if(is_not_null_compl(t2), (`test`.`t2`.`a` = 500), true))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              },
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3389,6 +4102,7 @@ select * from t1 where (t1.a,t1.b) not i
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -3400,13 +4114,15 @@ select * from t1 where (t1.a,t1.b) not i
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0044,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0044,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -3422,11 +4138,21 @@ select * from t1 where (t1.a,t1.b) not i
               "original_condition": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": "(not(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select `test`.`t2`.`c`,`test`.`t2`.`d` from `test`.`t2` where ((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true)) having (trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`c`), true) and trigcond_if(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`d`), true))))))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3461,6 +4187,7 @@ select * from t1 where (t1.a,t1.b) not i
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t2",
                 "table_scan": {
                   "records": 2,
@@ -3472,13 +4199,15 @@ select * from t1 where (t1.a,t1.b) not i
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t2",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0044,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0044,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -3494,11 +4223,21 @@ select * from t1 where (t1.a,t1.b) not i
               "original_condition": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t2",
                   "attached": "((`test`.`t2`.`c` > 0) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`) or isnull(`test`.`t2`.`c`)), true) and trigcond_if(outer_field_is_not_null, ((<cache>(`test`.`t1`.`b`) = `test`.`t2`.`d`) or isnull(`test`.`t2`.`d`)), true))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3554,6 +4293,7 @@ trace
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -3561,6 +4301,7 @@ trace
                 } /* table_scan */
               },
               {
+                "database": "test",
                 "table": "t2",
                 "table_scan": {
                   "records": 2,
@@ -3572,14 +4313,17 @@ trace
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0044,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
-                      "chosen": true
+                      "cost": 2.0044,
+                      "chosen": true,
+                      "use_temp_table": true
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
@@ -3587,13 +4331,15 @@ trace
                 "records_for_plan": 2,
                 "rest_of_plan": [
                   {
+                    "database": "test",
                     "table": "t2",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
-                          "access_type": "table scan",
-                          "cost": 2.0045,
+                          "access_type": "scan",
+                          "using_join_cache": true,
                           "records": 2,
+                          "cost": 2.0045,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
@@ -3605,13 +4351,15 @@ trace
                 ] /* rest_of_plan */
               },
               {
+                "database": "test",
                 "table": "t2",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0044,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0044,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -3627,15 +4375,31 @@ trace
               "original_condition": "(`test`.`t2`.`c` > -(1))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": null
                 },
                 {
+                  "database": "test",
                   "table": "t2",
                   "attached": "(`test`.`t2`.`c` > -(1))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              },
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3696,6 +4460,61 @@ update t6 set d=5 where d is NULL	{
   "steps": [
     {
       "expanded_query": "/* select#1 */ select `d` AS `d` from `test`.`t6` where isnull(`d`)"
+    },
+    {
+      "database": "test",
+      "table": "t6",
+      "range_analysis": {
+        "table_scan": {
+          "records": 2,
+          "cost": 4.5034
+        } /* table_scan */,
+        "potential_range_indices": [
+          {
+            "index": "d",
+            "usable": true,
+            "key_parts": [
+              "d"
+            ] /* key_parts */
+          }
+        ] /* potential_range_indices */,
+        "group_index_range": {
+          "chosen": false,
+          "cause": "no_join"
+        } /* group_index_range */,
+        "analyzing_range_alternatives": {
+          "range_scan_alternatives": [
+            {
+              "index": "d",
+              "ranges": [
+                "NULL <= d <= NULL"
+              ] /* ranges */,
+              "index_only": false,
+              "records": 1,
+              "cost": 2.21,
+              "rowid_ordered": true,
+              "chosen": true
+            }
+          ] /* range_scan_alternatives */,
+          "analyzing_roworder_intersect": {
+            "usable": false,
+            "cause": "too_few_roworder_scans"
+          } /* analyzing_roworder_intersect */
+        } /* analyzing_range_alternatives */,
+        "chosen_range_access_summary": {
+          "range_access_plan": {
+            "type": "range_scan",
+            "index": "d",
+            "records": 1,
+            "ranges": [
+              "NULL <= d <= NULL"
+            ] /* ranges */
+          } /* range_access_plan */,
+          "records_for_plan": 1,
+          "cost_for_plan": 2.21,
+          "chosen": true
+        } /* chosen_range_access_summary */
+      } /* range_analysis */
     }
   ] /* steps */
 }	0	0
@@ -3706,6 +4525,57 @@ delete from t6 where d=5	{
   "steps": [
     {
       "expanded_query": "/* select#1 */ select  from dual where (`d` = 5)"
+    },
+    {
+      "database": "test",
+      "table": "t6",
+      "range_analysis": {
+        "table_scan": {
+          "records": 2,
+          "cost": 4.5034
+        } /* table_scan */,
+        "potential_range_indices": [
+          {
+            "index": "d",
+            "usable": true,
+            "key_parts": [
+              "d"
+            ] /* key_parts */
+          }
+        ] /* potential_range_indices */,
+        "group_index_range": {
+          "chosen": false,
+          "cause": "no_join"
+        } /* group_index_range */,
+        "analyzing_range_alternatives": {
+          "range_scan_alternatives": [
+            {
+              "index": "d",
+              "ranges": [
+                "5 <= d <= 5"
+              ] /* ranges */,
+              "index_only": false,
+              "records": 1,
+              "cost": 2.21,
+              "rowid_ordered": true,
+              "chosen": true
+            }
+          ] /* range_scan_alternatives */
+        } /* analyzing_range_alternatives */,
+        "chosen_range_access_summary": {
+          "range_access_plan": {
+            "type": "range_scan",
+            "index": "d",
+            "records": 1,
+            "ranges": [
+              "5 <= d <= 5"
+            ] /* ranges */
+          } /* range_access_plan */,
+          "records_for_plan": 1,
+          "cost_for_plan": 2.21,
+          "chosen": true
+        } /* chosen_range_access_summary */
+      } /* range_analysis */
     }
   ] /* steps */
 }	0	0
@@ -3756,23 +4626,67 @@ insert into t6 select * from t6 where d>
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                } /* more_range_optimizer_trace */
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 3,
+                    "cost": 4.7051
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "d",
+                      "usable": true,
+                      "key_parts": [
+                        "d"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "best_covering_index_scan": {
+                    "index": "d",
+                    "cost": 1.6465,
+                    "chosen": true
+                  } /* best_covering_index_scan */,
+                  "group_index_range": {
+                    "chosen": false,
+                    "cause": "not_group_by_or_distinct"
+                  } /* group_index_range */,
+                  "analyzing_range_alternatives": {
+                    "range_scan_alternatives": [
+                      {
+                        "index": "d",
+                        "ranges": [
+                          "7 < d"
+                        ] /* ranges */,
+                        "index_only": true,
+                        "records": 2,
+                        "cost": 3.41,
+                        "rowid_ordered": false,
+                        "chosen": false,
+                        "cause": "cost"
+                      }
+                    ] /* range_scan_alternatives */,
+                    "analyzing_roworder_intersect": {
+                      "usable": false,
+                      "cause": "too_few_roworder_scans"
+                    } /* analyzing_roworder_intersect */
+                  } /* analyzing_range_alternatives */
+                } /* range_analysis */
               }
             ] /* records_estimation */
           },
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t6",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.2051,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.2051,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -3788,11 +4702,21 @@ insert into t6 select * from t6 where d>
               "original_condition": "(`test`.`t6`.`d` > 7)",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t6",
                   "attached": "(`test`.`t6`.`d` > 7)"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t6",
+                "scan_type": "index"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3837,23 +4761,80 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
+                "table": "t5",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              },
+              {
+                "database": "test",
                 "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                } /* more_range_optimizer_trace */
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 4,
+                    "cost": 4.9068
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "d",
+                      "usable": true,
+                      "key_parts": [
+                        "d"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "group_index_range": {
+                    "chosen": false,
+                    "cause": "not_single_table"
+                  } /* group_index_range */,
+                  "analyzing_range_alternatives": {
+                    "range_scan_alternatives": [
+                      {
+                        "index": "d",
+                        "ranges": [
+                          "7000 < d"
+                        ] /* ranges */,
+                        "index_only": false,
+                        "records": 1,
+                        "cost": 2.21,
+                        "rowid_ordered": false,
+                        "chosen": true
+                      }
+                    ] /* range_scan_alternatives */,
+                    "analyzing_roworder_intersect": {
+                      "usable": false,
+                      "cause": "too_few_roworder_scans"
+                    } /* analyzing_roworder_intersect */
+                  } /* analyzing_range_alternatives */,
+                  "chosen_range_access_summary": {
+                    "range_access_plan": {
+                      "type": "range_scan",
+                      "index": "d",
+                      "records": 1,
+                      "ranges": [
+                        "7000 < d"
+                      ] /* ranges */
+                    } /* range_access_plan */,
+                    "records_for_plan": 1,
+                    "cost_for_plan": 2.21,
+                    "chosen": true
+                  } /* chosen_range_access_summary */
+                } /* range_analysis */
               }
             ] /* records_estimation */
           },
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t6",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.21,
+                      "access_type": "range",
                       "records": 1,
+                      "cost": 2.21,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -3869,11 +4850,21 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
               "original_condition": "(`test`.`t6`.`d` > 7000)",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t6",
                   "attached": "(`test`.`t6`.`d` > 7000)"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t6",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -3918,23 +4909,74 @@ delete t6 from t5, t6 where d>7000	{
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
+                "table": "t5",
+                "records": 1,
+                "cost": 1,
+                "table_type": "system"
+              },
+              {
+                "database": "test",
                 "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                } /* more_range_optimizer_trace */
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 4,
+                    "cost": 4.9068
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "d",
+                      "usable": true,
+                      "key_parts": [
+                        "d"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "best_covering_index_scan": {
+                    "index": "d",
+                    "cost": 1.8698,
+                    "chosen": true
+                  } /* best_covering_index_scan */,
+                  "group_index_range": {
+                    "chosen": false,
+                    "cause": "not_single_table"
+                  } /* group_index_range */,
+                  "analyzing_range_alternatives": {
+                    "range_scan_alternatives": [
+                      {
+                        "index": "d",
+                        "ranges": [
+                          "7000 < d"
+                        ] /* ranges */,
+                        "index_only": true,
+                        "records": 1,
+                        "cost": 2.21,
+                        "rowid_ordered": false,
+                        "chosen": false,
+                        "cause": "cost"
+                      }
+                    ] /* range_scan_alternatives */,
+                    "analyzing_roworder_intersect": {
+                      "usable": false,
+                      "cause": "too_few_roworder_scans"
+                    } /* analyzing_roworder_intersect */
+                  } /* analyzing_range_alternatives */
+                } /* range_analysis */
               }
             ] /* records_estimation */
           },
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t6",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.6068,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 1,
+                      "cost": 2.6068,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -3950,11 +4992,21 @@ delete t6 from t5, t6 where d>7000	{
               "original_condition": "(`test`.`t6`.`d` > 7000)",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t6",
                   "attached": "(`test`.`t6`.`d` > 7000)"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t6",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4133,6 +5185,7 @@ select sum(data) into ret from t1	{
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -4144,13 +5197,15 @@ select sum(data) into ret from t1	{
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0154,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0154,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -4166,11 +5221,21 @@ select sum(data) into ret from t1	{
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4205,6 +5270,7 @@ select sum(data) into ret from t1	{
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -4216,13 +5282,15 @@ select sum(data) into ret from t1	{
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0154,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0154,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -4238,11 +5306,21 @@ select sum(data) into ret from t1	{
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4274,6 +5352,7 @@ select sum(data) into ret from t1	{
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -4285,13 +5364,15 @@ select sum(data) into ret from t1	{
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0154,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0154,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -4307,11 +5388,21 @@ select sum(data) into ret from t1	{
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4326,30 +5417,15 @@ select * from t6 where d in (select f1()
 d
 select * from information_schema.OPTIMIZER_TRACE|
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
-select * from t6 where d in (select f1() from t2 where s="c")	{
+select sum(data) into ret from t1	{
   "steps": [
     {
-      "expanded_query": "/* select#1 */ select `test`.`t6`.`d` AS `d` from `test`.`t6` where `d` in (/* select#2 */ select `f1`() from `test`.`t2` where (`s` = 'c'))"
+      "expanded_query": "/* select#1 */ select sum(`data`) AS `sum(data)` from `test`.`t1`"
     },
     {
       "join_preparation": {
         "select#": 1,
         "steps": [
-          {
-            "join_preparation": {
-              "select#": 2,
-              "steps": [
-                {
-                  "transformation": {
-                    "select#": 2,
-                    "from": "IN (SELECT)",
-                    "to": "semijoin",
-                    "chosen": true
-                  } /* transformation */
-                }
-              ] /* steps */
-            } /* join_preparation */
-          }
         ] /* steps */
       } /* join_preparation */
     },
@@ -4358,248 +5434,61 @@ select * from t6 where d in (select f1()
         "select#": 1,
         "steps": [
           {
-            "transformation": {
-              "select#": 2,
-              "from": "IN (SELECT)",
-              "to": "semijoin",
-              "chosen": true
-            } /* transformation */
-          },
-          {
-            "condition_processing": {
-              "condition": "WHERE",
-              "original_condition": "(1 and (`test`.`t2`.`s` = 'c') and (`test`.`t6`.`d` = `f1`()))",
-              "after_equality_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
-              "after_constant_propagation": "(1 and multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))",
-              "after_trivial_conditions_removal": "(multiple equal('c', `test`.`t2`.`s`) and multiple equal(`f1`(), `test`.`t6`.`d`))"
-            } /* condition_processing */
-          },
-          {
-            "ref-optimizer-key-uses": [
-              {
-                "condition": "t6.d= `f1`()",
-                "null_rejecting": false
-              }
-            ] /* ref-optimizer-key-uses */
-          },
-          {
             "constant_tables": [
             ] /* constant_tables */,
             "records_estimation": [
               {
-                "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                } /* more_range_optimizer_trace */
-              },
-              {
-                "table": "t2",
+                "database": "test",
+                "table": "t1",
                 "table_scan": {
-                  "records": 3,
+                  "records": 2,
                   "cost": 2
                 } /* table_scan */
               }
             ] /* records_estimation */
           },
           {
-            "pulled_out_semijoin_tables": [
-            ] /* pulled_out_semijoin_tables */
-          },
-          {
-            "execution_plan_for_potential_materialization": {
-              "steps": [
-                {
-                  "considered_execution_plans": [
-                    {
-                      "table": "t2",
-                      "best_access_path": {
-                        "considered_access_paths": [
-                          {
-                            "access_type": "table scan",
-                            "cost": 2.0212,
-                            "records": 3,
-                            "chosen": true
-                          }
-                        ] /* considered_access_paths */
-                      } /* best_access_path */,
-                      "cost_for_plan": 2.0212,
-                      "records_for_plan": 3,
-                      "semijoin_strategy_choice": [
-                      ] /* semijoin_strategy_choice */,
-                      "chosen": true
-                    }
-                  ] /* considered_execution_plans */
-                }
-              ] /* steps */
-            } /* execution_plan_for_potential_materialization */
-          },
-          {
             "considered_execution_plans": [
               {
-                "table": "t2",
+                "database": "test",
+                "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0212,
-                      "records": 3,
+                      "access_type": "scan",
+                      "using_join_cache": true,
+                      "records": 2,
+                      "cost": 2.0154,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
                 } /* best_access_path */,
-                "cost_for_plan": 2.0212,
-                "records_for_plan": 3,
-                "semijoin_strategy_choice": [
-                ] /* semijoin_strategy_choice */,
-                "rest_of_plan": [
-                  {
-                    "table": "t6",
-                    "best_access_path": {
-                      "considered_access_paths": [
-                        {
-                          "access_type": "index",
-                          "index": "d",
-                          "cost": 3,
-                          "records": 1,
-                          "chosen": true
-                        },
-                        {
-                          "access_type": "table scan",
-                          "cost": 2.6076,
-                          "records": 1,
-                          "chosen": true
-                        }
-                      ] /* considered_access_paths */
-                    } /* best_access_path */,
-                    "cost_for_plan": 4.6289,
-                    "records_for_plan": 3,
-                    "semijoin_strategy_choice": [
-                      {
-                        "strategy": "DuplicatesWeedout",
-                        "cost": 5.2289,
-                        "records": 1,
-                        "duplicate_tables_left": true,
-                        "chosen": true
-                      }
-                    ] /* semijoin_strategy_choice */,
-                    "chosen": true
-                  }
-                ] /* rest_of_plan */
-              },
-              {
-                "table": "t6",
-                "best_access_path": {
-                  "considered_access_paths": [
-                    {
-                      "access_type": "index",
-                      "index": "d",
-                      "cost": 1,
-                      "records": 1,
-                      "chosen": true
-                    },
-                    {
-                      "access_type": "table scan",
-                      "cost": 2,
-                      "records": 4,
-                      "chosen": false
-                    }
-                  ] /* considered_access_paths */
-                } /* best_access_path */,
-                "cost_for_plan": 1,
-                "records_for_plan": 1,
-                "semijoin_strategy_choice": [
-                ] /* semijoin_strategy_choice */,
-                "rest_of_plan": [
-                  {
-                    "table": "t2",
-                    "best_access_path": {
-                      "considered_access_paths": [
-                        {
-                          "access_type": "table scan",
-                          "cost": 2.0213,
-                          "records": 3,
-                          "chosen": true
-                        }
-                      ] /* considered_access_paths */
-                    } /* best_access_path */,
-                    "cost_for_plan": 3.0213,
-                    "records_for_plan": 3,
-                    "semijoin_strategy_choice": [
-                      {
-                        "strategy": "FirstMatch",
-                        "recompute_best_access_paths": {
-                          "cause": "join_buffering_not_possible",
-                          "tables": [
-                            {
-                              "table": "t2",
-                              "best_access_path": {
-                                "considered_access_paths": [
-                                  {
-                                    "access_type": "table scan",
-                                    "cost": 2.0213,
-                                    "records": 3,
-                                    "chosen": true
-                                  }
-                                ] /* considered_access_paths */
-                              } /* best_access_path */
-                            }
-                          ] /* tables */
-                        } /* recompute_best_access_paths */,
-                        "cost": 3.0213,
-                        "records": 1,
-                        "chosen": true
-                      },
-                      {
-                        "strategy": "MaterializationLookup",
-                        "cost": 3.2212,
-                        "records": 1,
-                        "duplicate_tables_left": false,
-                        "chosen": false
-                      },
-                      {
-                        "strategy": "DuplicatesWeedout",
-                        "cost": 3.6213,
-                        "records": 1,
-                        "duplicate_tables_left": false,
-                        "chosen": false
-                      }
-                    ] /* semijoin_strategy_choice */,
-                    "chosen": true
-                  }
-                ] /* rest_of_plan */
+                "cost_for_plan": 2.0154,
+                "records_for_plan": 2,
+                "chosen": true
               }
             ] /* considered_execution_plans */
           },
           {
-            "reconsidering_access_paths_for_semijoin": {
-              "strategy": "FirstMatch",
-              "table": "t2",
-              "best_access_path": {
-                "considered_access_paths": [
-                  {
-                    "access_type": "table scan",
-                    "cost": 2.0212,
-                    "records": 3,
-                    "chosen": true
-                  }
-                ] /* considered_access_paths */
-              } /* best_access_path */
-            } /* reconsidering_access_paths_for_semijoin */
-          },
-          {
             "attaching_conditions_to_tables": {
-              "original_condition": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))",
+              "original_condition": null,
               "attached_conditions": [
                 {
-                  "table": "t6",
-                  "attached": "(`test`.`t6`.`d` = `f1`())"
-                },
-                {
-                  "table": "t2",
-                  "attached": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = 'c'))"
+                  "database": "test",
+                  "table": "t1",
+                  "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4617,6 +5506,16 @@ delete from t1 where id="z"	{
   "steps": [
     {
       "expanded_query": "/* select#1 */ select  from dual where (`id` = 'z')"
+    },
+    {
+      "database": "test",
+      "table": "t1",
+      "range_analysis": {
+        "table_scan": {
+          "records": 3,
+          "cost": 4.7154
+        } /* table_scan */
+      } /* range_analysis */
     }
   ] /* steps */
 }	0	0
@@ -4641,6 +5540,7 @@ select sum(data) into ret from t1	{
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -4652,13 +5552,15 @@ select sum(data) into ret from t1	{
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0154,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0154,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -4674,11 +5576,21 @@ select sum(data) into ret from t1	{
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4694,6 +5606,16 @@ delete from t1 where id="z"	{
   "steps": [
     {
       "expanded_query": "/* select#1 */ select  from dual where (`id` = 'z')"
+    },
+    {
+      "database": "test",
+      "table": "t1",
+      "range_analysis": {
+        "table_scan": {
+          "records": 3,
+          "cost": 4.7154
+        } /* table_scan */
+      } /* range_analysis */
     }
   ] /* steps */
 }	0	0
@@ -4747,10 +5669,36 @@ select d+1 into res from t6 where d= NAM
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                } /* more_range_optimizer_trace */
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 4,
+                    "cost": 4.9068
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "d",
+                      "usable": true,
+                      "key_parts": [
+                        "d"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "best_covering_index_scan": {
+                    "index": "d",
+                    "cost": 1.8698,
+                    "chosen": true
+                  } /* best_covering_index_scan */,
+                  "impossible_condition": {
+                    "table": "t6",
+                    "field": "d",
+                    "cause": "comparison_with_null_always_false"
+                  } /* impossible_condition */,
+                  "impossible_range": true
+                } /* range_analysis */,
+                "records": 0,
+                "cause": "impossible_where_condition"
               }
             ] /* records_estimation */
           }
@@ -4790,6 +5738,7 @@ select sum(data) into ret from t1	{
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -4801,13 +5750,15 @@ select sum(data) into ret from t1	{
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0154,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0154,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -4823,11 +5774,21 @@ select sum(data) into ret from t1	{
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -4895,10 +5856,36 @@ select @trace;
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                } /* more_range_optimizer_trace */
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 4,
+                    "cost": 4.9068
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "d",
+                      "usable": true,
+                      "key_parts": [
+                        "d"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "best_covering_index_scan": {
+                    "index": "d",
+                    "cost": 1.8698,
+                    "chosen": true
+                  } /* best_covering_index_scan */,
+                  "impossible_condition": {
+                    "table": "t6",
+                    "field": "d",
+                    "cause": "comparison_with_null_always_false"
+                  } /* impossible_condition */,
+                  "impossible_range": true
+                } /* range_analysis */,
+                "records": 0,
+                "cause": "impossible_where_condition"
               }
             ] /* records_estimation */
           }
@@ -4998,12 +5985,55 @@ select d into res from t6 where d in (se
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t6",
-                "more_range_optimizer_trace": {
-                  "TODO?": "yes!"
-                } /* more_range_optimizer_trace */
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 4,
+                    "cost": 4.9068
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "d",
+                      "usable": true,
+                      "key_parts": [
+                        "d"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "best_covering_index_scan": {
+                    "index": "d",
+                    "cost": 1.8698,
+                    "chosen": true
+                  } /* best_covering_index_scan */,
+                  "group_index_range": {
+                    "chosen": false,
+                    "cause": "not_single_table"
+                  } /* group_index_range */,
+                  "analyzing_range_alternatives": {
+                    "range_scan_alternatives": [
+                      {
+                        "index": "d",
+                        "ranges": [
+                          "3 <= d <= 3"
+                        ] /* ranges */,
+                        "index_only": true,
+                        "records": 1,
+                        "cost": 2.21,
+                        "rowid_ordered": true,
+                        "chosen": false,
+                        "cause": "cost"
+                      }
+                    ] /* range_scan_alternatives */,
+                    "analyzing_roworder_intersect": {
+                      "usable": false,
+                      "cause": "too_few_roworder_scans"
+                    } /* analyzing_roworder_intersect */
+                  } /* analyzing_range_alternatives */
+                } /* range_analysis */
               },
               {
+                "database": "test",
                 "table": "t2",
                 "table_scan": {
                   "records": 7,
@@ -5022,13 +6052,15 @@ select d into res from t6 where d in (se
                 {
                   "considered_execution_plans": [
                     {
+                      "database": "test",
                       "table": "t2",
                       "best_access_path": {
                         "considered_access_paths": [
                           {
-                            "access_type": "table scan",
-                            "cost": 2.0496,
+                            "access_type": "scan",
+                            "using_join_cache": true,
                             "records": 7,
+                            "cost": 2.0496,
                             "chosen": true
                           }
                         ] /* considered_access_paths */
@@ -5047,20 +6079,22 @@ select d into res from t6 where d in (se
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t6",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "index",
+                      "access_type": "ref",
                       "index": "d",
-                      "cost": 1,
                       "records": 1,
+                      "cost": 1,
                       "chosen": true
                     },
                     {
-                      "access_type": "table scan",
+                      "access_type": "scan",
                       "cost": 2,
                       "records": 4,
+                      "cause": "cost",
                       "chosen": false
                     }
                   ] /* considered_access_paths */
@@ -5071,13 +6105,15 @@ select d into res from t6 where d in (se
                 ] /* semijoin_strategy_choice */,
                 "rest_of_plan": [
                   {
+                    "database": "test",
                     "table": "t2",
                     "best_access_path": {
                       "considered_access_paths": [
                         {
-                          "access_type": "table scan",
-                          "cost": 2.0496,
+                          "access_type": "scan",
+                          "using_join_cache": true,
                           "records": 7,
+                          "cost": 2.0496,
                           "chosen": true
                         }
                       ] /* considered_access_paths */
@@ -5091,13 +6127,15 @@ select d into res from t6 where d in (se
                           "cause": "join_buffering_not_possible",
                           "tables": [
                             {
+                              "database": "test",
                               "table": "t2",
                               "best_access_path": {
                                 "considered_access_paths": [
                                   {
-                                    "access_type": "table scan",
-                                    "cost": 2.0496,
+                                    "access_type": "scan",
+                                    "using_join_cache": true,
                                     "records": 7,
+                                    "cost": 2.0496,
                                     "chosen": true
                                   }
                                 ] /* considered_access_paths */
@@ -5129,13 +6167,15 @@ select d into res from t6 where d in (se
                 ] /* rest_of_plan */
               },
               {
+                "database": "test",
                 "table": "t2",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0496,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 7,
+                      "cost": 2.0496,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -5151,13 +6191,14 @@ select d into res from t6 where d in (se
           {
             "reconsidering_access_paths_for_semijoin": {
               "strategy": "FirstMatch",
+              "database": "test",
               "table": "t2",
               "best_access_path": {
                 "considered_access_paths": [
                   {
-                    "access_type": "table scan",
-                    "cost": 2.0496,
+                    "access_type": "scan",
                     "records": 7,
+                    "cost": 2.0496,
                     "chosen": true
                   }
                 ] /* considered_access_paths */
@@ -5169,15 +6210,30 @@ select d into res from t6 where d in (se
               "original_condition": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t6",
                   "attached": "(`test`.`t6`.`d` = `f1`())"
                 },
                 {
+                  "database": "test",
                   "table": "t2",
                   "attached": "((`test`.`t6`.`d` = `f1`()) and (`test`.`t2`.`s` = arg@0))"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t6"
+              },
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5195,6 +6251,16 @@ delete from t1 where id="z"	{
   "steps": [
     {
       "expanded_query": "/* select#1 */ select  from dual where (`id` = 'z')"
+    },
+    {
+      "database": "test",
+      "table": "t1",
+      "range_analysis": {
+        "table_scan": {
+          "records": 3,
+          "cost": 4.7154
+        } /* table_scan */
+      } /* range_analysis */
     }
   ] /* steps */
 }	0	0
@@ -5219,6 +6285,7 @@ select sum(data) into ret from t1	{
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -5230,13 +6297,117 @@ select sum(data) into ret from t1	{
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
+                      "access_type": "scan",
+                      "using_join_cache": true,
+                      "records": 2,
                       "cost": 2.0154,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 2.0154,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": null,
+              "attached_conditions": [
+                {
+                  "database": "test",
+                  "table": "t1",
+                  "attached": null
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}	0	0
+insert into t1 values("z",0)	{
+  "steps": [
+    {
+      "expanded_query": "/* select#1 */ select  from dual"
+    }
+  ] /* steps */
+}	0	0
+delete from t1 where id="z"	{
+  "steps": [
+    {
+      "expanded_query": "/* select#1 */ select  from dual where (`id` = 'z')"
+    },
+    {
+      "database": "test",
+      "table": "t1",
+      "range_analysis": {
+        "table_scan": {
+          "records": 3,
+          "cost": 4.7154
+        } /* table_scan */
+      } /* range_analysis */
+    }
+  ] /* steps */
+}	0	0
+select sum(data) into ret from t1	{
+  "steps": [
+    {
+      "expanded_query": "/* select#1 */ select sum(`data`) AS `sum(data)` from `test`.`t1`"
+    },
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "database": "test",
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "database": "test",
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0154,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -5252,11 +6423,21 @@ select sum(data) into ret from t1	{
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": null
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5323,6 +6504,7 @@ explain select * from v1 where id="b"	{
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 2,
@@ -5334,13 +6516,15 @@ explain select * from v1 where id="b"	{
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0154,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 2.0154,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -5356,11 +6540,21 @@ explain select * from v1 where id="b"	{
               "original_condition": "(`test`.`t1`.`id` = 'b')",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": "(`test`.`t1`.`id` = 'b')"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5384,6 +6578,16 @@ delete from v1 where data=100	{
   "steps": [
     {
       "expanded_query": "/* select#1 */ select  from dual where ((`data` = 100) and (`test`.`t1`.`id` < 'c'))"
+    },
+    {
+      "database": "test",
+      "table": "v1",
+      "range_analysis": {
+        "table_scan": {
+          "records": 3,
+          "cost": 4.7154
+        } /* table_scan */
+      } /* range_analysis */
     }
   ] /* steps */
 }	0	0
@@ -5437,6 +6641,7 @@ explain select * from v1 where id="b"	{
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "test",
                 "table": "t1",
                 "table_scan": {
                   "records": 3,
@@ -5448,13 +6653,15 @@ explain select * from v1 where id="b"	{
           {
             "considered_execution_plans": [
               {
+                "database": "test",
                 "table": "t1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 2.0154,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 3,
+                      "cost": 2.0154,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -5470,11 +6677,21 @@ explain select * from v1 where id="b"	{
               "original_condition": "(`test`.`t1`.`id` < 'c')",
               "attached_conditions": [
                 {
+                  "database": "test",
                   "table": "t1",
                   "attached": "(`test`.`t1`.`id` < 'c')"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5508,6 +6725,7 @@ explain select * from v1 where id="b"	{
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "",
                 "table": "v1",
                 "table_scan": {
                   "records": 2,
@@ -5519,13 +6737,15 @@ explain select * from v1 where id="b"	{
           {
             "considered_execution_plans": [
               {
+                "database": "",
                 "table": "v1",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 10.1,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 10.1,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -5541,11 +6761,21 @@ explain select * from v1 where id="b"	{
               "original_condition": "(`v1`.`id` = 'b')",
               "attached_conditions": [
                 {
+                  "database": "",
                   "table": "v1",
                   "attached": "(`v1`.`id` = 'b')"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "",
+                "table": "v1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5594,6 +6824,7 @@ VARIABLE_NAME="optimizer_trace"	{
             ] /* constant_tables */,
             "records_estimation": [
               {
+                "database": "information_schema",
                 "table": "session_variables",
                 "table_scan": {
                   "records": 2,
@@ -5605,13 +6836,15 @@ VARIABLE_NAME="optimizer_trace"	{
           {
             "considered_execution_plans": [
               {
+                "database": "information_schema",
                 "table": "session_variables",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 10.1,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 10.1,
                       "chosen": true
                     }
                   ] /* considered_access_paths */
@@ -5627,11 +6860,21 @@ 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')"
                 }
               ] /* attached_conditions */
             } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "information_schema",
+                "table": "session_variables",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -5693,6 +6936,7 @@ select TRACE into dumpfile 'MYSQLTEST_VA
             ],
             "records_estimation": [
               {
+                "database": "",
                 "table": "",
                 "table_scan": {
                   "records": 2,
@@ -5704,13 +6948,15 @@ select TRACE into dumpfile 'MYSQLTEST_VA
           {
             "considered_execution_plans": [
               {
+                "database": "",
                 "table": "",
                 "best_access_path": {
                   "considered_access_paths": [
                     {
-                      "access_type": "table scan",
-                      "cost": 10.1,
+                      "access_type": "scan",
+                      "using_join_cache": true,
                       "records": 2,
+                      "cost": 10.1,
                       "chosen": true
                     }
                   ]
@@ -5726,11 +6972,21 @@ select TRACE into dumpfile 'MYSQLTEST_VA
               "original_condition": null,
               "attached_conditions": [
                 {
+                  "database": "",
                   "table": "",
                   "attached": null
                 }
               ]
             }
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "",
+                "table": "",
+                "scan_type": "table"
+              }
+            ]
           }
         ]
       }

=== modified file 'mysql-test/r/optimizer_trace_range.result'
--- a/mysql-test/r/optimizer_trace_range.result	2010-11-17 12:27:39 +0000
+++ b/mysql-test/r/optimizer_trace_range.result	2010-11-22 09:59:17 +0000
@@ -202,13 +202,13 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -319,13 +319,13 @@ EXPLAIN SELECT * FROM t1 WHERE key2 < 5 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -539,13 +539,13 @@ EXPLAIN SELECT * FROM t1 WHERE key1 < 3 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -729,13 +729,202 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    }
+  ] /* steps */
+}	0	0
+EXPLAIN SELECT DISTINCT key2 FROM t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	NULL	i2_1	4	NULL	103	Using index for group-by
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
+EXPLAIN SELECT DISTINCT key2 FROM t2	{
+  "steps": [
+    {
+      "expanded_query": "/* select#1 */ select distinct `key2` AS `key2` from `test`.`t2`"
+    },
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "constant_tables": [
+            ] /* constant_tables */,
+            "records_estimation": [
+              {
+                "database": "test",
+                "table": "t2",
+                "const_keys_added": {
+                  "keys": [
+                    "i2_1",
+                    "i2_2"
+                  ] /* keys */,
+                  "cause": "distinct"
+                } /* const_keys_added */,
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 1024,
+                    "cost": 215.15
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "PRIMARY",
+                      "usable": false,
+                      "cause": "not_usable_by_conditions"
+                    },
+                    {
+                      "index": "i1b",
+                      "usable": false,
+                      "cause": "not_usable_by_conditions"
+                    },
+                    {
+                      "index": "i2_1",
+                      "usable": true,
+                      "key_parts": [
+                        "key2",
+                        "key2_1"
+                      ] /* key_parts */
+                    },
+                    {
+                      "index": "i2_2",
+                      "usable": true,
+                      "key_parts": [
+                        "key2",
+                        "key2_1"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "best_covering_index_scan": {
+                    "index": "i2_1",
+                    "cost": 235.03,
+                    "chosen": false,
+                    "cause": "cost"
+                  } /* best_covering_index_scan */,
+                  "group_index_range": {
+                    "distinct_query": true,
+                    "potential_group_range_indices": [
+                      {
+                        "index": "PRIMARY",
+                        "usable": false,
+                        "cause": "not_covering"
+                      },
+                      {
+                        "index": "i1b",
+                        "usable": false,
+                        "cause": "not_covering"
+                      },
+                      {
+                        "index": "i2_1",
+                        "covering": true,
+                        "records": 103,
+                        "cost": 50.6
+                      },
+                      {
+                        "index": "i2_2",
+                        "covering": true,
+                        "records": 103,
+                        "cost": 50.6
+                      }
+                    ] /* potential_group_range_indices */
+                  } /* group_index_range */,
+                  "best_group_range_summary": {
+                    "type": "index_group",
+                    "index": "i2_1",
+                    "group_attribute": "null",
+                    "min_aggregate": false,
+                    "max_aggregate": false,
+                    "distinct_aggregate": false,
+                    "records": 103,
+                    "cost": 50.6,
+                    "key_parts_used_for_access": [
+                      "key2"
+                    ] /* key_parts_used_for_access */,
+                    "ranges": [
+                    ] /* ranges */,
+                    "chosen": true
+                  } /* best_group_range_summary */,
+                  "chosen_range_access_summary": {
+                    "range_access_plan": {
+                      "type": "index_group",
+                      "index": "i2_1",
+                      "group_attribute": "null",
+                      "min_aggregate": false,
+                      "max_aggregate": false,
+                      "distinct_aggregate": false,
+                      "records": 103,
+                      "cost": 50.6,
+                      "key_parts_used_for_access": [
+                        "key2"
+                      ] /* key_parts_used_for_access */,
+                      "ranges": [
+                      ] /* ranges */
+                    } /* range_access_plan */,
+                    "records_for_plan": 103,
+                    "cost_for_plan": 50.6,
+                    "chosen": true
+                  } /* chosen_range_access_summary */
+                } /* range_analysis */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "database": "test",
+                "table": "t2",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "range",
+                      "records": 103,
+                      "cost": 50.6,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 50.6,
+                "records_for_plan": 103,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": null,
+              "attached_conditions": [
+                {
+                  "database": "test",
+                  "table": "t2",
+                  "attached": null
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t2",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -956,13 +1145,13 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t2",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1152,12 +1341,12 @@ EXPLAIN SELECT * FROM t2 WHERE key2 = 1 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t2"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1405,13 +1594,13 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1539,13 +1728,13 @@ EXPLAIN SELECT * FROM t2 WHERE key2_1 < 
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t2",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1641,7 +1830,7 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
                       {
                         "index": "PRIMARY",
                         "ranges": [
-                          "5 <= key1a <= 5 : key1b < 10"
+                          "5 <= key1a <= 5 AND key1b < 10"
                         ] /* ranges */,
                         "index_only": false,
                         "records": 1,
@@ -1652,7 +1841,7 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
                       {
                         "index": "i1b",
                         "ranges": [
-                          "key1b < 10 : 5 <= key1a <= 5"
+                          "key1b < 10 AND 5 <= key1a <= 5"
                         ] /* ranges */,
                         "index_only": false,
                         "records": 9,
@@ -1673,7 +1862,7 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
                       "index": "PRIMARY",
                       "records": 1,
                       "ranges": [
-                        "5 <= key1a <= 5 : key1b < 10"
+                        "5 <= key1a <= 5 AND key1b < 10"
                       ] /* ranges */
                     } /* range_access_plan */,
                     "records_for_plan": 1,
@@ -1725,13 +1914,13 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t2",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -1920,13 +2109,13 @@ EXPLAIN SELECT * FROM t1 WHERE (key1 > 1
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2154,13 +2343,13 @@ EXPLAIN SELECT * FROM t1 WHERE cola = 'f
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1",
                 "scan_type": "table"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */
@@ -2317,12 +2506,12 @@ o"
             } /* attaching_conditions_to_tables */
           },
           {
-            "refine_plan": {
-              "refine_plan_for_table": {
+            "refine_plan": [
+              {
                 "database": "test",
                 "table": "t1"
-              } /* refine_plan_for_table */
-            } /* refine_plan */
+              }
+            ] /* refine_plan */
           }
         ] /* steps */
       } /* join_optimization */

=== modified file 'mysql-test/t/optimizer_trace_range.test'
--- a/mysql-test/t/optimizer_trace_range.test	2010-11-17 12:27:39 +0000
+++ b/mysql-test/t/optimizer_trace_range.test	2010-11-22 09:59:17 +0000
@@ -85,6 +85,11 @@ EXPLAIN SELECT key2, MIN(key2_1) FROM t2
 --echo
 SELECT * FROM information_schema.OPTIMIZER_TRACE;
 
+# distinct - group quick select without grouping attribute
+EXPLAIN SELECT DISTINCT key2 FROM t2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
 # group with range
 --echo
 EXPLAIN SELECT key2, MIN(key2_1) FROM t2 where key2 < 5 GROUP BY key2;

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2010-11-17 12:27:39 +0000
+++ b/sql/opt_range.cc	2010-11-22 09:59:17 +0000
@@ -1995,9 +1995,10 @@ print_key2(String *out, const KEY_PART_I
 
     if (field->real_maybe_null())
     {
-      /* Byte 0 of key is the null-byte. If set, key is NULL.
-         Otherwise, print the key value starting immediately after the
-         null-byte
+      /*
+        Byte 0 of key is the null-byte. If set, key is NULL.
+        Otherwise, print the key value starting immediately after the
+        null-byte
        */
       if (*key)
       {
@@ -2053,7 +2054,8 @@ public:
     DBUG_RETURN(quick);
   }
 
-  void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+  void trace_basic_info(const PARAM *param,
+                        Opt_trace_object *trace_object) const
   {
 #ifdef OPTIMIZER_TRACE
     DBUG_ASSERT(param->using_real_indexes);
@@ -2062,7 +2064,7 @@ public:
     const KEY cur_key= param->table->key_info[keynr_in_table];
     const KEY_PART_INFO *key_part= cur_key.key_part;
 
-    trace->add_str("type", "range_scan").
+    trace_object->add_str("type", "range_scan").
       add_str("index", cur_key.name, strlen(cur_key.name)).
       add("records", records);
 
@@ -2129,10 +2131,11 @@ public:
   bool is_covering; /* TRUE if no row retrieval phase is necessary */
   double index_scan_costs; /* SUM(cost(index_scan)) */
 
-  void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+  void trace_basic_info(const PARAM *param,
+                        Opt_trace_object *trace_object) const
   {
 #ifdef OPTIMIZER_TRACE
-    trace->add_str("type", "index_roworder_intersect").
+    trace_object->add_str("type", "index_roworder_intersect").
       add("records", records).
       add("cost", read_cost).
       add("covering", is_covering).
@@ -2193,10 +2196,11 @@ public:
   TABLE_READ_PLAN **first_ror; /* array of ptrs to plans for merged scans */
   TABLE_READ_PLAN **last_ror;  /* end of the above array */
 
-  void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+  void trace_basic_info(const PARAM *param,
+                        Opt_trace_object *trace_object) const
   {
 #ifdef OPTIMIZER_TRACE
-    trace->add_str("type", "index_roworder_union");
+    trace_object->add_str("type", "index_roworder_union");
     Opt_trace_array ota(param->thd->opt_trace, "union_of");
     for (TABLE_READ_PLAN **current= first_ror;
          current != last_ror;
@@ -2225,10 +2229,11 @@ public:
   TRP_RANGE **range_scans; /* array of ptrs to plans of merged scans */
   TRP_RANGE **range_scans_end; /* end of the array */
 
-  void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+  void trace_basic_info(const PARAM *param,
+                        Opt_trace_object *trace_object) const
   {
 #ifdef OPTIMIZER_TRACE
-    trace->add_str("type", "index_merge");
+    trace_object->add_str("type", "index_merge");
     Opt_trace_array ota(param->thd->opt_trace, "index_merge_of");
     for (TRP_RANGE **current= range_scans;
          current != range_scans_end;
@@ -2278,17 +2283,19 @@ public:
   ha_rows quick_prefix_records;
 public:
 
-  void trace_basic_info(const PARAM *param, Opt_trace_object *trace) const
+  void trace_basic_info(const PARAM *param,
+                        Opt_trace_object *trace_object) const
   {
 #ifdef OPTIMIZER_TRACE
-    trace->add_str("type", "index_group").
+    trace_object->add_str("type", "index_group").
       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));
+      trace_object->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).
+      trace_object->add_str("group_attribute", "null");
+    trace_object->add("min_aggregate", have_min).
       add("max_aggregate", have_max).
       add("distinct_aggregate", have_agg_distinct).
       add("records", records).
@@ -2749,27 +2756,25 @@ int SQL_SELECT::test_quick_select(THD *t
           DBUG_PRINT("info",("No range reads possible,"
                              " trying to construct index_merge"));
           List_iterator_fast<SEL_IMERGE> it(tree->merges);
+          Opt_trace_array trace_idx_merge(trace,
+                                          "analyzing_index_merge",
+                                          Opt_trace_context::RANGE_OPTIMIZER);
+          while ((imerge= it++))
           {
-            Opt_trace_array trace_idx_merge(trace, 
-                                            "analyzing_index_merge",
-                                            Opt_trace_context::RANGE_OPTIMIZER);
-            while ((imerge= it++))
+            new_conj_trp= get_best_disjunct_quick(&param, imerge,
+                                                  best_read_time);
+            if (new_conj_trp)
+              set_if_smaller(param.table->quick_condition_rows,
+                             new_conj_trp->records);
+            if (!best_conj_trp ||
+                (new_conj_trp &&
+                 new_conj_trp->read_cost < best_conj_trp->read_cost))
             {
-              new_conj_trp= get_best_disjunct_quick(&param, imerge, 
-                                                    best_read_time);
-              if (new_conj_trp)
-                set_if_smaller(param.table->quick_condition_rows, 
-                               new_conj_trp->records);
-              if (!best_conj_trp || 
-                  (new_conj_trp && 
-                   new_conj_trp->read_cost < best_conj_trp->read_cost))
-              {
-                best_conj_trp= new_conj_trp;
-              }
+              best_conj_trp= new_conj_trp;
             }
-            if (best_conj_trp)
-              best_trp= best_conj_trp;
           }
+          if (best_conj_trp)
+            best_trp= best_conj_trp;
         }
       }
     }
@@ -4145,7 +4150,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
                                              n_child_scans)))
     DBUG_RETURN(NULL);
   // Note: to_merge.end() is called to close this object after this for-loop.
-  Opt_trace_array to_merge(trace,"indices_to_merge");
+  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
@@ -4234,10 +4239,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)
      */
-    double rowid_comp_cost= non_cpk_scan_records / TIME_FOR_COMPARE_ROWID;
-    imerge_cost += rowid_comp_cost;
+    const double rid_comp_cost= non_cpk_scan_records / TIME_FOR_COMPARE_ROWID;
+    imerge_cost+= rid_comp_cost;
     trace_best_disjunct.add("cost_of_mapping_rowid_in_non_clustered_pk_scan",
-                            rowid_comp_cost);
+                            rid_comp_cost);
   }
 
   /* Calculate cost(rowid_to_row_scan) */
@@ -4319,54 +4324,56 @@ skip_to_ror_scan:
   roru_total_records= 0;
   cur_roru_plan= roru_read_plans;
 
+  /*
+    Note: trace_analyze_ror.end() is called to close this object after
+    this for-loop.
+  */
+  Opt_trace_array trace_analyze_ror(trace, "analyzing_roworder_scans");
   /* Find 'best' ROR scan for each of trees in disjunction */
-  
+  for (ptree= imerge->trees, cur_child= range_scans;
+       ptree != imerge->trees_next;
+       ptree++, cur_child++, cur_roru_plan++)
   {
-    Opt_trace_array ota(trace, "analyzing_roworder_scans");
-    for (ptree= imerge->trees, cur_child= range_scans;
-         ptree != imerge->trees_next;
-         ptree++, cur_child++, cur_roru_plan++)
-    {
-
-      Opt_trace_object trp_info(trace);
-      (*cur_child)->trace_basic_info(param, &trp_info);
+    Opt_trace_object trp_info(trace);
+    (*cur_child)->trace_basic_info(param, &trp_info);
 
-      /*
-        Assume the best ROR scan is the one that has cheapest
-        full-row-retrieval scan cost. 
-        Also accumulate index_only scan costs as we'll need them to
-        calculate overall index_intersection cost.
-      */
-      double cost;
-      if ((*cur_child)->is_ror)
-      {
-        /* Ok, we have index_only cost, now get full rows scan cost */
-        cost= param->table->file->
-          read_time(param->real_keynr[(*cur_child)->key_idx], 1,
-                    (*cur_child)->records) +
-          rows2double((*cur_child)->records) / TIME_FOR_COMPARE;
-      }
-      else
-        cost= read_time;
+    /*
+      Assume the best ROR scan is the one that has cheapest
+      full-row-retrieval scan cost.
+      Also accumulate index_only scan costs as we'll need them to
+      calculate overall index_intersection cost.
+    */
+    double cost;
+    if ((*cur_child)->is_ror)
+    {
+      /* Ok, we have index_only cost, now get full rows scan cost */
+      cost= param->table->file->
+        read_time(param->real_keynr[(*cur_child)->key_idx], 1,
+                  (*cur_child)->records) +
+        rows2double((*cur_child)->records) / TIME_FOR_COMPARE;
+    }
+    else
+      cost= read_time;
 
-      TABLE_READ_PLAN *prev_plan= *cur_child;
-      if (!(*cur_roru_plan= get_best_ror_intersect(param, *ptree, cost,
-                                                   &dummy)))
-      {
-        if (prev_plan->is_ror)
-          *cur_roru_plan= prev_plan;
-        else
-          DBUG_RETURN(imerge_trp);
-        roru_index_costs += (*cur_roru_plan)->read_cost;
-      }
+    TABLE_READ_PLAN *prev_plan= *cur_child;
+    if (!(*cur_roru_plan= get_best_ror_intersect(param, *ptree, cost,
+                                                 &dummy)))
+    {
+      if (prev_plan->is_ror)
+        *cur_roru_plan= prev_plan;
       else
-        roru_index_costs +=
-          ((TRP_ROR_INTERSECT*)(*cur_roru_plan))->index_scan_costs;
-      roru_total_records += (*cur_roru_plan)->records;
-      roru_intersect_part *= (*cur_roru_plan)->records /
-        param->table->file->stats.records;
+        DBUG_RETURN(imerge_trp);
+      roru_index_costs += (*cur_roru_plan)->read_cost;
     }
+    else
+      roru_index_costs +=
+        ((TRP_ROR_INTERSECT*)(*cur_roru_plan))->index_scan_costs;
+    roru_total_records += (*cur_roru_plan)->records;
+    roru_intersect_part *= (*cur_roru_plan)->records /
+      param->table->file->stats.records;
   }
+  // Note: trace_analyze_ror trace object is closed here
+  trace_analyze_ror.end();
 
   /*
     rows to retrieve=
@@ -5017,38 +5024,43 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
   ROR_SCAN_INFO **intersect_scans_best;
   cur_ror_scan= tree->ror_scans;
   intersect_scans_best= intersect_scans;
-  {
-    Opt_trace_array ota(param->thd->opt_trace, "intersecting_indices");
-    while (cur_ror_scan != tree->ror_scans_end && !intersect->is_covering)
-    {
-      Opt_trace_object trace_idx(param->thd->opt_trace);
-      char *idx_name= param->table->key_info[(*cur_ror_scan)->keynr].name;
-      trace_idx.add_str("index", idx_name, strlen(idx_name));
-      /* S= S + first(R);  R= R - first(R); */
-      if (!ror_intersect_add(intersect, *cur_ror_scan, FALSE))
-      {
-        trace_idx.add("usable", false).
-          add_str("cause", "does_not_reduce_cost_of_intersect");
-        cur_ror_scan++;
-        continue;
-      }
+  /*
+    Note: trace_isect_idx.end() is called to close this object after
+    this while-loop.
+  */
+  Opt_trace_array trace_isect_idx(param->thd->opt_trace,
+                                  "intersecting_indices");
+  while (cur_ror_scan != tree->ror_scans_end && !intersect->is_covering)
+  {
+    Opt_trace_object trace_idx(param->thd->opt_trace);
+    char *idx_name= param->table->key_info[(*cur_ror_scan)->keynr].name;
+    trace_idx.add_str("index", idx_name, strlen(idx_name));
+    /* S= S + first(R);  R= R - first(R); */
+    if (!ror_intersect_add(intersect, *cur_ror_scan, FALSE))
+    {
+      trace_idx.add("usable", false).
+        add_str("cause", "does_not_reduce_cost_of_intersect");
+      cur_ror_scan++;
+      continue;
+    }
     
-      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);
-
-      *(intersect_scans_end++)= *(cur_ror_scan++);
-
-      if (intersect->total_cost < min_cost)
-      {
-        /* Local minimum found, save it */
-        ror_intersect_cpy(intersect_best, intersect);
-        intersect_scans_best= intersect_scans_end;
-        min_cost = intersect->total_cost;
-      }
+    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);
+
+    *(intersect_scans_end++)= *(cur_ror_scan++);
+
+    if (intersect->total_cost < min_cost)
+    {
+      /* Local minimum found, save it */
+      ror_intersect_cpy(intersect_best, intersect);
+      intersect_scans_best= intersect_scans_end;
+      min_cost = intersect->total_cost;
     }
   }
+  // Note: trace_isect_idx trace object is closed here
+  trace_isect_idx.end();
 
   if (intersect_scans_best == intersect_scans)
   {
@@ -5086,13 +5098,13 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
     else
       Opt_trace_object (param->thd->opt_trace, "clustered_pk").
         add("clustered_pk_added_to_intersect", false).add_str("cause", "cost");
-  } 
-  else 
+  }
+  else
   {
     Opt_trace_object trace_cpk(param->thd->opt_trace, "clustered_pk");
-    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");
+    trace_cpk.add("clustered_pk_added_to_intersect", false).
+      add_str("cause", cpk_scan ?
+              "roworder_is_covering" : "no_clustered_pk_index");
   }
 
   /* Ok, return ROR-intersect plan if we have found one */
@@ -6360,16 +6372,6 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
        field->type() == MYSQL_TYPE_DATETIME))
     field->table->in_use->variables.sql_mode|= MODE_INVALID_DATES;
   {
-#ifdef NO_OPT_TRACE_FOR_RANGE_OPT
-    /*
-      if we have a condition like
-        field=stored_function_SELECTing_table(),
-      save_in_field() call below will evaluate the function and thus
-      this will add a join_preparation object which does not fit well
-      (sometimes crashes).
-    */
-    Opt_trace_disable_I_S otd(param->thd->opt_trace, TRUE);
-#endif
     err= value->save_in_field_no_warnings(field, 1);
   }
   if (err > 0)
@@ -8018,16 +8020,17 @@ static void step_down_to(String *s, SEL_
 {
 
 #ifdef OPTIMIZER_TRACE
-  if (arg->param->thd->opt_trace && arg->param->thd->opt_trace->is_started())
+  if (unlikely(arg->param->thd->opt_trace != NULL) &&
+      arg->param->thd->opt_trace->is_started())
   {
-    /* 
+    /*
        Stepping down will append the range for the current keypart (in
        key_tree) to seq. Trace range here since this is where it is
        human readable.
     */
-    const 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;
     append_range(s, key_part,
                 key_tree->min_value, length,
@@ -8173,7 +8176,7 @@ walk_right_n_up:
                                           MAX_KEY);
 
 #ifdef OPTIMIZER_TRACE
-        if (seq->param->thd->opt_trace &&
+        if (unlikely(seq->param->thd->opt_trace != NULL) &&
             seq->param->thd->opt_trace->is_started())
         {
           // Trace the range we just stored
@@ -10152,8 +10155,6 @@ 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;
@@ -10162,11 +10163,6 @@ get_best_group_min_max(PARAM *param, SEL
   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",
@@ -10186,7 +10182,7 @@ get_best_group_min_max(PARAM *param, SEL
   }
   if (join->select_lex->olap == ROLLUP_TYPE) /* Check (B3) for ROLLUP */
   {
-    cause= "is_rollup";
+    cause= "rollup";
     goto return_null_not_chosen_cause;
   }
   if (table->s->keys == 0)        /* There are no indexes to use. */
@@ -10248,7 +10244,7 @@ get_best_group_min_max(PARAM *param, SEL
   /* Check (SA5). */
   if (join->select_distinct)
   {
-    trace_group.add("is_distinct_query", true);
+    trace_group.add("distinct_query", true);
     while ((item= select_items_it++))
     {
       if (item->real_item()->type() != Item::FIELD_ITEM)
@@ -10272,6 +10268,12 @@ get_best_group_min_max(PARAM *param, SEL
     first one. Here we set the variables: group_prefix_len and index_info.
   */
   {
+    const uint pk= param->table->s->primary_key;
+    KEY *cur_index_info= table->key_info;
+    KEY *cur_index_info_end= cur_index_info + table->s->keys;
+    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 ;
          cur_index_info++, cur_index++)
@@ -10628,7 +10630,7 @@ get_best_group_min_max(PARAM *param, SEL
     read_plan->records=   best_records;
     if (read_time < best_read_cost && is_agg_distinct)
     {
-      trace_group.add("is_index_scan", true);
+      trace_group.add("index_scan", true);
       read_plan->read_cost= 0;
       read_plan->use_index_scan();
     }
@@ -12356,7 +12358,7 @@ static void append_range(String *out,
                          const uint flag)
 {
   if (out->length() > 0)
-    out->append(STRING_WITH_LEN(" : "));
+    out->append(STRING_WITH_LEN(" AND "));
 
   if (!(flag & NO_MIN_RANGE))
   {

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	2010-11-17 12:27:39 +0000
+++ b/sql/sql_delete.cc	2010-11-22 09:59:17 +0000
@@ -192,26 +192,26 @@ bool mysql_delete(THD *thd, TABLE_LIST *
     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));
+    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;
-    free_underlaid_joins(thd, select_lex);
-    /* 
-      Error was already created by quick select evaluation (check_quick()).
-      TODO: Add error code output parameter to Item::val_xxx() methods.
-      Currently they rely on the user checking DA for
-      errors when unwinding the stack after calling Item::val_xxx().
-    */
-    if (thd->is_error())
-      DBUG_RETURN(TRUE);
-    my_ok(thd, 0);
-    DBUG_RETURN(0);				// Nothing to delete
-  }
+    if ((select && select->check_quick(thd, safe_update, limit)) || !limit)
+    {
+      delete select;
+      free_underlaid_joins(thd, select_lex);
+      /*
+         Error was already created by quick select evaluation (check_quick()).
+         TODO: Add error code output parameter to Item::val_xxx() methods.
+         Currently they rely on the user checking DA for
+         errors when unwinding the stack after calling Item::val_xxx().
+      */
+      if (thd->is_error())
+        DBUG_RETURN(TRUE);
+      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 */

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-11-17 12:27:39 +0000
+++ b/sql/sql_select.cc	2010-11-22 09:59:17 +0000
@@ -4947,7 +4947,7 @@ make_join_statistics(JOIN *join, TABLE_L
         {
           trace_table.add("records", 1).add("cost", 1);
           trace_table.add_str("table_type", (s->type == JT_SYSTEM) ?
-                              "system_table": "const_table");
+                              "system": "const");
 
           /* Only one matching row */
           s->found_records= s->records= s->read_time=1; s->worst_seeks= 1.0;
@@ -6564,7 +6564,7 @@ static void trace_indices_added_group_di
                                                const key_map new_keys,
                                                const char* cause)
 {
-  if (trace == NULL || !trace->is_started())
+  if (likely(trace == NULL) || !trace->is_started())
     return;
 
   KEY *key_info= join_tab->table->key_info;
@@ -11155,7 +11155,7 @@ make_join_readinfo(JOIN *join, ulonglong
   Opt_trace_context * const trace= join->thd->opt_trace;
   Opt_trace_object wrapper(trace);
 
-  Opt_trace_object trace_refine_plan(trace, "refine_plan");
+  Opt_trace_array trace_refine_plan(trace, "refine_plan");
 
   if (!join->select_lex->sj_nests.is_empty() &&
       setup_semijoin_dups_elimination(join, options, no_jbuf_after))
@@ -11181,7 +11181,7 @@ 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");
+    Opt_trace_object trace_refine_table(trace);
     trace_refine_table.add_str("database", join->tables_list->db,
                               strlen(join->tables_list->db)).
       add_str("table", table->alias, strlen(table->alias));

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2010-11-17 12:27:39 +0000
+++ b/sql/sql_update.cc	2010-11-22 09:59:17 +0000
@@ -342,30 +342,30 @@ int mysql_update(THD *thd,
   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));
+    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)))
-  {
-    delete select;
-    free_underlaid_joins(thd, select_lex);
-    /*
-      There was an error or the error was already sent by
-      the quick select evaluation.
-      TODO: Add error code output parameter to Item::val_xxx() methods.
-      Currently they rely on the user checking DA for
-      errors when unwinding the stack after calling Item::val_xxx().
-    */
-    if (error || thd->is_error())
+    if (error || !limit ||
+        (select && select->check_quick(thd, safe_update, limit)))
     {
-      DBUG_RETURN(1);				// Error in where
+      delete select;
+      free_underlaid_joins(thd, select_lex);
+      /*
+        There was an error or the error was already sent by
+        the quick select evaluation.
+        TODO: Add error code output parameter to Item::val_xxx() methods.
+        Currently they rely on the user checking DA for
+        errors when unwinding the stack after calling Item::val_xxx().
+      */
+      if (error || thd->is_error())
+      {
+        DBUG_RETURN(1);				// Error in where
+      }
+      my_ok(thd);				// No matching records
+      DBUG_RETURN(0);
     }
-    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 */

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-next-mr-bugfixing branch (jorgen.loland:3233 to 3234)WL#5594Jorgen Loland22 Nov