List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:January 5 2011 8:42am
Subject:bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3243)
View as plain text  
#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-range-subselect/ based on revid:jorgen.loland@stripped

 3243 Jorgen Loland	2011-01-05
      WL4800:
      
      * Updated sys_vars with new options for optimizer_trace_features 
      * Added tests that demonstrate optimizer tracing with subselects

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

=== modified file 'mysql-test/r/optimizer_trace_range.result'
--- a/mysql-test/r/optimizer_trace_range.result	2010-12-13 14:03:16 +0000
+++ b/mysql-test/r/optimizer_trace_range.result	2011-01-05 08:42:16 +0000
@@ -2197,6 +2197,253 @@ EXPLAIN SELECT * FROM t1 WHERE (key1 > 1
     }
   ] /* steps */
 }	0	0
+
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2 
+WHERE t1.key1=t2.key1a AND t1.key2 > 1020;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	i1,i2	i2	4	NULL	42	Using index condition; Using MRR
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.key1	10	
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2 
+WHERE t1.key1=t2.key1a AND t1.key2 > 1020	{
+  "steps": [
+    {
+      "expanded_query": "/* select#1 */ select straight_join `*` AS `*` from `test`.`t1` join `test`.`t2` where ((`t1`.`key1` = `t2`.`key1a`) and (`t1`.`key2` > 1020))"
+    },
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "((`test`.`t1`.`key1` = `test`.`t2`.`key1a`) and (`test`.`t1`.`key2` > 1020))",
+              "after_equality_propagation": "((`test`.`t1`.`key2` > 1020) and multiple equal(`test`.`t1`.`key1`, `test`.`t2`.`key1a`))",
+              "after_constant_propagation": "((`test`.`t1`.`key2` > 1020) and multiple equal(`test`.`t1`.`key1`, `test`.`t2`.`key1a`))",
+              "after_trivial_conditions_removal": "((`test`.`t1`.`key2` > 1020) and multiple equal(`test`.`t1`.`key1`, `test`.`t2`.`key1a`))"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+              {
+                "condition": "t1.key1= `test`.`t2`.`key1a`",
+                "null_rejecting": false
+              },
+              {
+                "condition": "t2.key1a= `test`.`t1`.`key1`",
+                "null_rejecting": false
+              }
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "records_estimation": [
+              {
+                "database": "test",
+                "table": "t1",
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 1024,
+                    "cost": 217.15
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "i1",
+                      "usable": false,
+                      "cause": "not_applicable"
+                    },
+                    {
+                      "index": "i2",
+                      "usable": true,
+                      "key_parts": [
+                        "key2"
+                      ] /* key_parts */
+                    },
+                    {
+                      "index": "i3",
+                      "usable": false,
+                      "cause": "not_applicable"
+                    },
+                    {
+                      "index": "i4",
+                      "usable": false,
+                      "cause": "not_applicable"
+                    },
+                    {
+                      "index": "i5",
+                      "usable": false,
+                      "cause": "not_applicable"
+                    },
+                    {
+                      "index": "i6",
+                      "usable": false,
+                      "cause": "not_applicable"
+                    },
+                    {
+                      "index": "i7",
+                      "usable": false,
+                      "cause": "not_applicable"
+                    },
+                    {
+                      "index": "i8",
+                      "usable": false,
+                      "cause": "not_applicable"
+                    }
+                  ] /* potential_range_indices */,
+                  "setup_range_conditions": [
+                  ] /* setup_range_conditions */,
+                  "group_index_range": {
+                    "chosen": false,
+                    "cause": "not_single_table"
+                  } /* group_index_range */,
+                  "analyzing_range_alternatives": {
+                    "range_scan_alternatives": [
+                      {
+                        "index": "i2",
+                        "ranges": [
+                          "1020 < key2"
+                        ] /* ranges */,
+                        "index_only": false,
+                        "records": 42,
+                        "cost": 51.41,
+                        "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": "i2",
+                      "records": 42,
+                      "ranges": [
+                        "1020 < key2"
+                      ] /* ranges */
+                    } /* range_access_plan */,
+                    "records_for_plan": 42,
+                    "cost_for_plan": 51.41,
+                    "chosen": true
+                  } /* chosen_range_access_summary */
+                } /* range_analysis */
+              },
+              {
+                "database": "test",
+                "table": "t2",
+                "table_scan": {
+                  "records": 1024,
+                  "cost": 8
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "database": "test",
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "ref",
+                      "index": "i1",
+                      "usable": false,
+                      "chosen": false
+                    },
+                    {
+                      "access_type": "range",
+                      "records": 42,
+                      "cost": 51.41,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "cost_for_plan": 51.41,
+                "records_for_plan": 42
+              },
+              {
+                "database": "test",
+                "table": "t2",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "ref",
+                      "index": "PRIMARY",
+                      "records": 10,
+                      "cost": 430.08,
+                      "chosen": true
+                    },
+                    {
+                      "access_type": "scan",
+                      "using_join_cache": true,
+                      "records": 768,
+                      "cost": 59.535,
+                      "chosen": false
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "semijoin_strategy_choice": [
+                ] /* semijoin_strategy_choice */,
+                "cost_for_plan": 481.49,
+                "records_for_plan": 420
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "((`test`.`t2`.`key1a` = `test`.`t1`.`key1`) and (`test`.`t1`.`key2` > 1020))",
+              "attached_conditions": [
+                {
+                  "database": "test",
+                  "table": "t1",
+                  "attached": "(`test`.`t1`.`key2` > 1020)"
+                },
+                {
+                  "database": "test",
+                  "table": "t2",
+                  "attached": null
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              },
+              {
+                "database": "test",
+                "table": "t2"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
 cola char(3) not null, 
@@ -2756,11 +3003,11 @@ EXPLAIN SELECT * FROM t1 WHERE b BETWEEN
   ] /* steps */
 }	0	0
 DROP TABLE t1;
-SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
 CREATE TABLE t1(c INT);
 INSERT INTO t1 VALUES (),();
 CREATE TABLE t2 (b INT, KEY(b));
 INSERT INTO t2 VALUES (),(),();
+SET optimizer_trace_features="greedy_search=off,dynamic_range=on";
 EXPLAIN SELECT 1 FROM 
 (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -2909,10 +3156,76 @@ EXPLAIN SELECT 1 FROM 
         "select#": 2,
         "steps": [
           {
-            "records_estimation_per_record": "..."
-          },
-          {
-            "records_estimation_per_record": "..."
+            "records_estimation_per_record": {
+              "database": "test",
+              "table": "t2",
+              "range_analysis": {
+                "table_scan": {
+                  "records": 3,
+                  "cost": 4.7051
+                } /* table_scan */,
+                "potential_range_indices": [
+                  {
+                    "index": "b",
+                    "usable": true,
+                    "key_parts": [
+                      "b"
+                    ] /* key_parts */
+                  }
+                ] /* potential_range_indices */,
+                "best_covering_index_scan": {
+                  "index": "b",
+                  "cost": 1.6465,
+                  "chosen": true
+                } /* best_covering_index_scan */,
+                "setup_range_conditions": [
+                  {
+                    "impossible_condition": {
+                      "table": "t2",
+                      "field": "b",
+                      "cause": "comparison_with_null_always_false"
+                    } /* impossible_condition */
+                  }
+                ] /* setup_range_conditions */,
+                "impossible_range": true
+              } /* range_analysis */
+            } /* records_estimation_per_record */
+          },
+          {
+            "records_estimation_per_record": {
+              "database": "test",
+              "table": "t2",
+              "range_analysis": {
+                "table_scan": {
+                  "records": 3,
+                  "cost": 4.7051
+                } /* table_scan */,
+                "potential_range_indices": [
+                  {
+                    "index": "b",
+                    "usable": true,
+                    "key_parts": [
+                      "b"
+                    ] /* key_parts */
+                  }
+                ] /* potential_range_indices */,
+                "best_covering_index_scan": {
+                  "index": "b",
+                  "cost": 1.6465,
+                  "chosen": true
+                } /* best_covering_index_scan */,
+                "setup_range_conditions": [
+                  {
+                    "impossible_condition": {
+                      "table": "t2",
+                      "field": "b",
+                      "cause": "comparison_with_null_always_false"
+                    } /* impossible_condition */
+                  }
+                ] /* setup_range_conditions */,
+                "impossible_range": true
+              } /* range_analysis */
+            } /* records_estimation_per_record */
           }
         ] /* steps */
       } /* join_execution */
@@ -2970,62 +3283,272 @@ EXPLAIN SELECT 1 FROM 
   ] /* steps */
 }	0	0
 
-DROP TABLE t1,t2;
-CREATE TABLE `t1` (
-`mot` varchar(4) NOT NULL,
-`topic` int NOT NULL,
-PRIMARY KEY  (`mot`,`topic`)
-);
-INSERT INTO t1 VALUES ('joce','40143'), ('joce','43506');
-SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic);
-mot	topic
+SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
+EXPLAIN SELECT 1 FROM 
+(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t2	ALL	b	NULL	NULL	NULL	3	Range checked for each record (index map: 0x1)
 
 SELECT * FROM information_schema.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
-SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic)	{
+EXPLAIN SELECT 1 FROM 
+(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2	{
   "steps": [
     {
-      "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where `topic` = all (/* select#2 */ select `topic` from `test`.`t1` group by `topic`)"
+      "expanded_query": "/* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`b` < `c`) group by 1 limit 1) `d2`"
     },
     {
       "join_preparation": {
-        "select#": 1,
+        "select#": 2,
         "steps": [
-          {
-            "join_preparation": {
-              "select#": 2,
-              "steps": [
-                {
-                  "transformation": {
-                    "select#": 2,
-                    "from": "IN (SELECT)",
-                    "to": "EXISTS (CORRELATED SELECT)",
-                    "chosen": true
-                  } /* transformation */
-                }
-              ] /* steps */
-            } /* join_preparation */
-          }
         ] /* steps */
       } /* join_preparation */
     },
     {
       "join_optimization": {
-        "select#": 1,
+        "select#": 2,
         "steps": [
           {
             "condition_processing": {
               "condition": "WHERE",
-              "original_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
-              "evaluate_subselect_cond_steps": [
-              ] /* evaluate_subselect_cond_steps */,
-              "after_equality_propagation": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
-              "evaluate_subselect_cond_steps": [
-              ] /* evaluate_subselect_cond_steps */,
-              "after_constant_propagation": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
-              "evaluate_subselect_cond_steps": [
-              ] /* evaluate_subselect_cond_steps */,
-              "after_trivial_conditions_removal": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
+              "original_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+              "after_equality_propagation": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+              "after_constant_propagation": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+              "after_trivial_conditions_removal": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "records_estimation": [
+              {
+                "database": "test",
+                "table": "t2",
+                "table_scan": {
+                  "records": 3,
+                  "cost": 2
+                } /* table_scan */
+              },
+              {
+                "database": "test",
+                "table": "t1",
+                "table_scan": {
+                  "records": 2,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": "..."
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(`test`.`t2`.`b` < `test`.`t1`.`c`)",
+              "attached_conditions": [
+                {
+                  "database": "test",
+                  "table": "t1",
+                  "attached": null
+                },
+                {
+                  "database": "test",
+                  "table": "t2",
+                  "rechecking_index_usage": {
+                    "database": "test",
+                    "table": "t2",
+                    "range_analysis": {
+                      "table_scan": {
+                        "records": 3,
+                        "cost": 5.6
+                      } /* table_scan */,
+                      "potential_range_indices": [
+                        {
+                          "index": "b",
+                          "usable": true,
+                          "key_parts": [
+                            "b"
+                          ] /* key_parts */
+                        }
+                      ] /* potential_range_indices */,
+                      "best_covering_index_scan": {
+                        "index": "b",
+                        "cost": 1.6465,
+                        "chosen": true
+                      } /* best_covering_index_scan */,
+                      "setup_range_conditions": [
+                      ] /* setup_range_conditions */,
+                      "group_index_range": {
+                        "chosen": false,
+                        "cause": "not_single_table"
+                      } /* group_index_range */,
+                      "analyzing_range_alternatives": {
+                        "range_scan_alternatives": [
+                          {
+                            "index": "b",
+                            "index_only": true,
+                            "records": 18446744073709551615,
+                            "cost": 0,
+                            "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 */
+                  } /* rechecking_index_usage */,
+                  "attached": "(`test`.`t2`.`b` < `test`.`t1`.`c`)"
+                }
+              ] /* 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 */
+    },
+    {
+      "join_execution": {
+        "select#": 2,
+        "steps": [
+          {
+            "records_estimation_per_record": "..."
+          },
+          {
+            "records_estimation_per_record": "..."
+          }
+        ] /* steps */
+      } /* join_execution */
+    },
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "records_estimation": [
+              {
+                "database": "",
+                "table": "d2",
+                "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 */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+          {
+            "join_execution": {
+              "select#": 2,
+              "steps": [
+              ] /* steps */
+            } /* join_execution */
+          }
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+
+DROP TABLE t1,t2;
+CREATE TABLE `t1` (
+`mot` varchar(4) NOT NULL,
+`topic` int NOT NULL,
+PRIMARY KEY  (`mot`,`topic`)
+);
+INSERT INTO t1 VALUES ('joce','40143'), ('joce','43506');
+SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic);
+mot	topic
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
+SELECT * from t1 where topic = all (SELECT topic FROM t1 GROUP BY topic)	{
+  "steps": [
+    {
+      "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where `topic` = all (/* select#2 */ select `topic` from `test`.`t1` group by `topic`)"
+    },
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "join_preparation": {
+              "select#": 2,
+              "steps": [
+                {
+                  "transformation": {
+                    "select#": 2,
+                    "from": "IN (SELECT)",
+                    "to": "EXISTS (CORRELATED SELECT)",
+                    "chosen": true
+                  } /* transformation */
+                }
+              ] /* steps */
+            } /* join_preparation */
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
+              "evaluate_subselect_cond_steps": [
+              ] /* evaluate_subselect_cond_steps */,
+              "after_equality_propagation": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
+              "evaluate_subselect_cond_steps": [
+              ] /* evaluate_subselect_cond_steps */,
+              "after_constant_propagation": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))",
+              "evaluate_subselect_cond_steps": [
+              ] /* evaluate_subselect_cond_steps */,
+              "after_trivial_conditions_removal": "<not>(<in_optimizer>(`test`.`t1`.`topic`,<exists>(/* select#2 */ select `test`.`t1`.`topic` from `test`.`t1` group by `test`.`t1`.`topic` having (<cache>(`test`.`t1`.`topic`) <> <ref_null_helper>(`test`.`t1`.`topic`)))))"
             } /* condition_processing */
           },
           {
@@ -3187,3 +3710,780 @@ SELECT * from t1 where topic = all (SELE
 }	0	0
 
 drop table t1;
+CREATE TABLE t1 (
+i1 int,
+i2 int,
+c char(1),
+KEY k1 (i1),
+KEY k2 (i1, i2)
+);
+INSERT INTO t1 VALUES (0,1,'2'),(3,2,'1');
+EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	k1,k2	k2	5	NULL	2	Using where
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
+EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2	{
+  "steps": [
+    {
+      "expanded_query": "/* select#1 */ select `*` AS `*` from `test`.`t1` where (`i1` > '2') order by `i1`,`i2`"
+    },
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`test`.`t1`.`i1` > '2')",
+              "after_equality_propagation": "(`test`.`t1`.`i1` > '2')",
+              "after_constant_propagation": "(`test`.`t1`.`i1` > '2')",
+              "after_trivial_conditions_removal": "(`test`.`t1`.`i1` > '2')"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "records_estimation": [
+              {
+                "database": "test",
+                "table": "t1",
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 2,
+                    "cost": 4.5049
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "k1",
+                      "usable": true,
+                      "key_parts": [
+                        "i1"
+                      ] /* key_parts */
+                    },
+                    {
+                      "index": "k2",
+                      "usable": true,
+                      "key_parts": [
+                        "i1",
+                        "i2"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "setup_range_conditions": [
+                  ] /* setup_range_conditions */,
+                  "group_index_range": {
+                    "chosen": false,
+                    "cause": "not_group_by_or_distinct"
+                  } /* group_index_range */,
+                  "analyzing_range_alternatives": {
+                    "range_scan_alternatives": [
+                      {
+                        "index": "k1",
+                        "ranges": [
+                          "2 < i1"
+                        ] /* ranges */,
+                        "index_only": false,
+                        "records": 2,
+                        "cost": 3.41,
+                        "rowid_ordered": false,
+                        "chosen": true
+                      },
+                      {
+                        "index": "k2",
+                        "ranges": [
+                          "2 < i1"
+                        ] /* ranges */,
+                        "index_only": false,
+                        "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 */,
+                  "chosen_range_access_summary": {
+                    "range_access_plan": {
+                      "type": "range_scan",
+                      "index": "k1",
+                      "records": 2,
+                      "ranges": [
+                        "2 < i1"
+                      ] /* ranges */
+                    } /* range_access_plan */,
+                    "records_for_plan": 2,
+                    "cost_for_plan": 3.41,
+                    "chosen": true
+                  } /* chosen_range_access_summary */
+                } /* range_analysis */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": "..."
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(`test`.`t1`.`i1` > '2')",
+              "attached_conditions": [
+                {
+                  "database": "test",
+                  "table": "t1",
+                  "attached": "(`test`.`t1`.`i1` > '2')"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+          {
+            "records_estimation_for_index_ordering": {
+              "database": "test",
+              "table": "t1",
+              "index": "k2",
+              "range_analysis": {
+                "table_scan": {
+                  "records": 2,
+                  "cost": 4.5049
+                } /* table_scan */,
+                "potential_range_indices": [
+                  {
+                    "index": "k1",
+                    "usable": false,
+                    "cause": "not_applicable"
+                  },
+                  {
+                    "index": "k2",
+                    "usable": true,
+                    "key_parts": [
+                      "i1",
+                      "i2"
+                    ] /* key_parts */
+                  }
+                ] /* potential_range_indices */,
+                "setup_range_conditions": [
+                ] /* setup_range_conditions */,
+                "group_index_range": {
+                  "chosen": false,
+                  "cause": "not_group_by_or_distinct"
+                } /* group_index_range */,
+                "analyzing_range_alternatives": {
+                  "range_scan_alternatives": [
+                    {
+                      "index": "k2",
+                      "ranges": [
+                        "2 < i1"
+                      ] /* ranges */,
+                      "index_only": false,
+                      "records": 2,
+                      "cost": 3.41,
+                      "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": "k2",
+                    "records": 2,
+                    "ranges": [
+                      "2 < i1"
+                    ] /* ranges */
+                  } /* range_access_plan */,
+                  "records_for_plan": 2,
+                  "cost_for_plan": 3.41,
+                  "chosen": true
+                } /* chosen_range_access_summary */
+              } /* range_analysis */
+            } /* records_estimation_for_index_ordering */
+          }
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+
+EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	k1,k2	k1	5	NULL	2	Using where; Using index
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
+EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC	{
+  "steps": [
+    {
+      "expanded_query": "/* select#1 */ select distinct `i1` AS `i1` from `test`.`t1` where (`i1` >= '1') order by `i1` desc"
+    },
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(`test`.`t1`.`i1` >= '1')",
+              "after_equality_propagation": "(`test`.`t1`.`i1` >= '1')",
+              "after_constant_propagation": "(`test`.`t1`.`i1` >= '1')",
+              "after_trivial_conditions_removal": "(`test`.`t1`.`i1` >= '1')"
+            } /* condition_processing */
+          },
+          {
+            "ref-optimizer-key-uses": [
+            ] /* ref-optimizer-key-uses */
+          },
+          {
+            "records_estimation": [
+              {
+                "database": "test",
+                "table": "t1",
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 2,
+                    "cost": 4.5049
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "k1",
+                      "usable": true,
+                      "key_parts": [
+                        "i1"
+                      ] /* key_parts */
+                    },
+                    {
+                      "index": "k2",
+                      "usable": true,
+                      "key_parts": [
+                        "i1",
+                        "i2"
+                      ] /* key_parts */
+                    }
+                  ] /* potential_range_indices */,
+                  "best_covering_index_scan": {
+                    "index": "k1",
+                    "cost": 1.4233,
+                    "chosen": true
+                  } /* best_covering_index_scan */,
+                  "setup_range_conditions": [
+                  ] /* setup_range_conditions */,
+                  "group_index_range": {
+                    "distinct_query": true,
+                    "potential_group_range_indices": [
+                      {
+                        "index": "k1",
+                        "covering": true,
+                        "ranges": [
+                          "1 <= i1"
+                        ] /* ranges */,
+                        "records": 3,
+                        "cost": 1.6
+                      },
+                      {
+                        "index": "k2",
+                        "covering": true,
+                        "ranges": [
+                          "1 <= i1"
+                        ] /* ranges */,
+                        "records": 3,
+                        "cost": 1.6
+                      }
+                    ] /* potential_group_range_indices */
+                  } /* group_index_range */,
+                  "best_group_range_summary": {
+                    "type": "index_group",
+                    "index": "k1",
+                    "group_attribute": null,
+                    "min_aggregate": false,
+                    "max_aggregate": false,
+                    "distinct_aggregate": false,
+                    "records": 3,
+                    "cost": 1.6,
+                    "key_parts_used_for_access": [
+                      "i1"
+                    ] /* key_parts_used_for_access */,
+                    "ranges": [
+                      "1 <= i1"
+                    ] /* ranges */,
+                    "chosen": false,
+                    "cause": "cost"
+                  } /* best_group_range_summary */,
+                  "analyzing_range_alternatives": {
+                    "range_scan_alternatives": [
+                      {
+                        "index": "k1",
+                        "ranges": [
+                          "1 <= i1"
+                        ] /* ranges */,
+                        "index_only": true,
+                        "records": 2,
+                        "cost": 3.41,
+                        "rowid_ordered": false,
+                        "chosen": false,
+                        "cause": "cost"
+                      },
+                      {
+                        "index": "k2",
+                        "ranges": [
+                          "1 <= i1"
+                        ] /* 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": "..."
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(`test`.`t1`.`i1` >= '1')",
+              "attached_conditions": [
+                {
+                  "database": "test",
+                  "table": "t1",
+                  "attached": "(`test`.`t1`.`i1` >= '1')"
+                }
+              ] /* attached_conditions */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "records_estimation_for_index_ordering": {
+              "database": "test",
+              "table": "t1",
+              "index": "k1",
+              "range_analysis": {
+                "table_scan": {
+                  "records": 2,
+                  "cost": 4.5049
+                } /* table_scan */,
+                "potential_range_indices": [
+                  {
+                    "index": "k1",
+                    "usable": true,
+                    "key_parts": [
+                      "i1"
+                    ] /* key_parts */
+                  },
+                  {
+                    "index": "k2",
+                    "usable": false,
+                    "cause": "not_applicable"
+                  }
+                ] /* potential_range_indices */,
+                "best_covering_index_scan": {
+                  "index": "k1",
+                  "cost": 1.4233,
+                  "chosen": true
+                } /* best_covering_index_scan */,
+                "setup_range_conditions": [
+                ] /* setup_range_conditions */,
+                "group_index_range": {
+                  "distinct_query": true,
+                  "potential_group_range_indices": [
+                    {
+                      "index": "k1",
+                      "covering": true,
+                      "ranges": [
+                        "1 <= i1"
+                      ] /* ranges */,
+                      "records": 3,
+                      "cost": 1.6
+                    },
+                    {
+                      "index": "k2",
+                      "covering": true,
+                      "records": 3,
+                      "cost": 1.6
+                    }
+                  ] /* potential_group_range_indices */
+                } /* group_index_range */,
+                "best_group_range_summary": {
+                  "type": "index_group",
+                  "index": "k1",
+                  "group_attribute": null,
+                  "min_aggregate": false,
+                  "max_aggregate": false,
+                  "distinct_aggregate": false,
+                  "records": 3,
+                  "cost": 1.6,
+                  "key_parts_used_for_access": [
+                    "i1"
+                  ] /* key_parts_used_for_access */,
+                  "ranges": [
+                    "1 <= i1"
+                  ] /* ranges */,
+                  "chosen": false,
+                  "cause": "cost"
+                } /* best_group_range_summary */,
+                "analyzing_range_alternatives": {
+                  "range_scan_alternatives": [
+                    {
+                      "index": "k1",
+                      "ranges": [
+                        "1 <= i1"
+                      ] /* 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_for_index_ordering */
+          },
+          {
+            "records_estimation_for_index_ordering": {
+              "database": "test",
+              "table": "t1",
+              "index": "k1",
+              "range_analysis": {
+                "table_scan": {
+                  "records": 2,
+                  "cost": 4.5049
+                } /* table_scan */,
+                "potential_range_indices": [
+                  {
+                    "index": "k1",
+                    "usable": true,
+                    "key_parts": [
+                      "i1"
+                    ] /* key_parts */
+                  },
+                  {
+                    "index": "k2",
+                    "usable": false,
+                    "cause": "not_applicable"
+                  }
+                ] /* potential_range_indices */,
+                "best_covering_index_scan": {
+                  "index": "k1",
+                  "cost": 1.4233,
+                  "chosen": true
+                } /* best_covering_index_scan */,
+                "setup_range_conditions": [
+                ] /* setup_range_conditions */,
+                "group_index_range": {
+                  "distinct_query": true,
+                  "potential_group_range_indices": [
+                    {
+                      "index": "k1",
+                      "covering": true,
+                      "ranges": [
+                        "1 <= i1"
+                      ] /* ranges */,
+                      "records": 3,
+                      "cost": 1.6
+                    },
+                    {
+                      "index": "k2",
+                      "covering": true,
+                      "records": 3,
+                      "cost": 1.6
+                    }
+                  ] /* potential_group_range_indices */
+                } /* group_index_range */,
+                "best_group_range_summary": {
+                  "type": "index_group",
+                  "index": "k1",
+                  "group_attribute": null,
+                  "min_aggregate": false,
+                  "max_aggregate": false,
+                  "distinct_aggregate": false,
+                  "records": 3,
+                  "cost": 1.6,
+                  "key_parts_used_for_access": [
+                    "i1"
+                  ] /* key_parts_used_for_access */,
+                  "ranges": [
+                    "1 <= i1"
+                  ] /* ranges */,
+                  "chosen": false,
+                  "cause": "cost"
+                } /* best_group_range_summary */,
+                "analyzing_range_alternatives": {
+                  "range_scan_alternatives": [
+                    {
+                      "index": "k1",
+                      "ranges": [
+                        "1 <= i1"
+                      ] /* 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_for_index_ordering */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "scan_type": "index"
+              }
+            ] /* refine_plan */
+          },
+          {
+            "records_estimation_for_index_ordering": {
+              "database": "test",
+              "table": "t1",
+              "index": "k1",
+              "range_analysis": {
+                "table_scan": {
+                  "records": 2,
+                  "cost": 4.5049
+                } /* table_scan */,
+                "potential_range_indices": [
+                  {
+                    "index": "k1",
+                    "usable": true,
+                    "key_parts": [
+                      "i1"
+                    ] /* key_parts */
+                  },
+                  {
+                    "index": "k2",
+                    "usable": false,
+                    "cause": "not_applicable"
+                  }
+                ] /* potential_range_indices */,
+                "best_covering_index_scan": {
+                  "index": "k1",
+                  "cost": 1.4233,
+                  "chosen": true
+                } /* best_covering_index_scan */,
+                "setup_range_conditions": [
+                ] /* setup_range_conditions */,
+                "group_index_range": {
+                  "potential_group_range_indices": [
+                    {
+                      "index": "k1",
+                      "covering": true,
+                      "ranges": [
+                        "1 <= i1"
+                      ] /* ranges */,
+                      "records": 3,
+                      "cost": 1.6
+                    },
+                    {
+                      "index": "k2",
+                      "covering": true,
+                      "records": 3,
+                      "cost": 1.6
+                    }
+                  ] /* potential_group_range_indices */
+                } /* group_index_range */,
+                "best_group_range_summary": {
+                  "type": "index_group",
+                  "index": "k1",
+                  "group_attribute": null,
+                  "min_aggregate": false,
+                  "max_aggregate": false,
+                  "distinct_aggregate": false,
+                  "records": 3,
+                  "cost": 1.6,
+                  "key_parts_used_for_access": [
+                    "i1"
+                  ] /* key_parts_used_for_access */,
+                  "ranges": [
+                    "1 <= i1"
+                  ] /* ranges */,
+                  "chosen": false,
+                  "cause": "cost"
+                } /* best_group_range_summary */,
+                "analyzing_range_alternatives": {
+                  "range_scan_alternatives": [
+                    {
+                      "index": "k1",
+                      "ranges": [
+                        "1 <= i1"
+                      ] /* 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_for_index_ordering */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+          {
+            "records_estimation_for_index_ordering": {
+              "database": "test",
+              "table": "t1",
+              "index": "k1",
+              "range_analysis": {
+                "table_scan": {
+                  "records": 2,
+                  "cost": 4.5049
+                } /* table_scan */,
+                "potential_range_indices": [
+                  {
+                    "index": "k1",
+                    "usable": true,
+                    "key_parts": [
+                      "i1"
+                    ] /* key_parts */
+                  },
+                  {
+                    "index": "k2",
+                    "usable": false,
+                    "cause": "not_applicable"
+                  }
+                ] /* potential_range_indices */,
+                "best_covering_index_scan": {
+                  "index": "k1",
+                  "cost": 1.4233,
+                  "chosen": true
+                } /* best_covering_index_scan */,
+                "setup_range_conditions": [
+                ] /* setup_range_conditions */,
+                "group_index_range": {
+                  "potential_group_range_indices": [
+                    {
+                      "index": "k1",
+                      "covering": true,
+                      "ranges": [
+                        "1 <= i1"
+                      ] /* ranges */,
+                      "records": 3,
+                      "cost": 1.6
+                    },
+                    {
+                      "index": "k2",
+                      "covering": true,
+                      "records": 3,
+                      "cost": 1.6
+                    }
+                  ] /* potential_group_range_indices */
+                } /* group_index_range */,
+                "best_group_range_summary": {
+                  "type": "index_group",
+                  "index": "k1",
+                  "group_attribute": null,
+                  "min_aggregate": false,
+                  "max_aggregate": false,
+                  "distinct_aggregate": false,
+                  "records": 3,
+                  "cost": 1.6,
+                  "key_parts_used_for_access": [
+                    "i1"
+                  ] /* key_parts_used_for_access */,
+                  "ranges": [
+                    "1 <= i1"
+                  ] /* ranges */,
+                  "chosen": false,
+                  "cause": "cost"
+                } /* best_group_range_summary */,
+                "analyzing_range_alternatives": {
+                  "range_scan_alternatives": [
+                    {
+                      "index": "k1",
+                      "ranges": [
+                        "1 <= i1"
+                      ] /* 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_for_index_ordering */
+          }
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+
+DROP TABLE t1;

=== modified file 'mysql-test/r/optimizer_trace_subquery.result'
--- a/mysql-test/r/optimizer_trace_subquery.result	2010-12-13 14:03:16 +0000
+++ b/mysql-test/r/optimizer_trace_subquery.result	2011-01-05 08:42:16 +0000
@@ -308,21 +308,129 @@ SELECT (SELECT a FROM t1 WHERE t1.a=t2.a
 
 DROP TABLE t1,t2;
 SET @@optimizer_trace_features="default";
-CREATE TABLE t1 (a int, b int, c int);
-SELECT 50, 3, 3 FROM DUAL
+CREATE TABLE t1 (a FLOAT(5,4) zerofill);
+CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
+SELECT t1.a
+FROM t1
+WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
+t1.a= (SELECT a FROM t2 LIMIT 1) ;
+a
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
+SELECT t1.a
+FROM t1
+WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
+t1.a= (SELECT a FROM t2 LIMIT 1)	{
+  "steps": [
+    {
+      "expanded_query": "/* select#1 */ select `t1`.`a` AS `a` from `test`.`t1` where ((`t1`.`a` = (/* select#2 */ select `b` from `test`.`t2` limit 1)) and (`t1`.`a` <> (/* select#3 */ select `a` from `test`.`t2` limit 1)))"
+    },
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "join_preparation": {
+              "select#": 2,
+              "steps": [
+              ] /* steps */
+            } /* join_preparation */
+          },
+          {
+            "join_preparation": {
+              "select#": 3,
+              "steps": [
+              ] /* steps */
+            } /* join_preparation */
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "((`test`.`t1`.`a` = (/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` limit 1)) and (`test`.`t1`.`a` <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)))",
+              "evaluate_subselect_cond_steps": [
+                {
+                  "subselect_exec": {
+                    "select#": 2,
+                    "steps": [
+                      {
+                        "join_optimization": {
+                          "select#": 2,
+                          "steps": [
+                            {
+                              "records_estimation": [
+                                {
+                                  "database": "test",
+                                  "table": "t2",
+                                  "records": 1,
+                                  "cost": 1,
+                                  "table_type": "system"
+                                }
+                              ] /* records_estimation */
+                            }
+                          ] /* steps */,
+                          "empty_result": {
+                            "cause": "no matching row in const table"
+                          } /* empty_result */
+                        } /* join_optimization */
+                      },
+                      {
+                        "join_execution": {
+                          "select#": 2,
+                          "steps": [
+                          ] /* steps */
+                        } /* join_execution */
+                      }
+                    ] /* steps */
+                  } /* subselect_exec */
+                }
+              ] /* evaluate_subselect_cond_steps */,
+              "after_equality_propagation": "((NULL <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)) and multiple equal((/* select#2 */ select NULL from `test`.`t2` limit 1), `test`.`t1`.`a`))",
+              "evaluate_subselect_cond_steps": [
+              ] /* evaluate_subselect_cond_steps */,
+              "after_constant_propagation": "((NULL <> (/* select#3 */ select `test`.`t2`.`a` from `test`.`t2` limit 1)) and multiple equal((/* select#2 */ select NULL from `test`.`t2` limit 1), `test`.`t1`.`a`))",
+              "evaluate_subselect_cond_steps": [
+              ] /* evaluate_subselect_cond_steps */,
+              "after_trivial_conditions_removal": null
+            } /* condition_processing */
+          }
+        ] /* steps */,
+        "empty_result": {
+          "cause": "Impossible WHERE"
+        } /* empty_result */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+
+SELECT 1 FROM DUAL
 WHERE NOT EXISTS
-(SELECT * FROM t1 WHERE a = 50 AND b = 3);
-50	3	3
-50	3	3
+(SELECT * FROM t2 WHERE a = 50 AND b = 3);
+1
+1
 
 SELECT * FROM information_schema.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	OS_MALLOC_ERROR
-SELECT 50, 3, 3 FROM DUAL
+SELECT 1 FROM DUAL
 WHERE NOT EXISTS
-(SELECT * FROM t1 WHERE a = 50 AND b = 3)	{
+(SELECT * FROM t2 WHERE a = 50 AND b = 3)	{
   "steps": [
     {
-      "expanded_query": "/* select#1 */ select 50 AS `50`,3 AS `3`,3 AS `3` from DUAL  where (not(exists(/* select#2 */ select `*` from `test`.`t1` where ((`a` = 50) and (`b` = 3)))))"
+      "expanded_query": "/* select#1 */ select 1 AS `1` from DUAL  where (not(exists(/* select#2 */ select `*` from `test`.`t2` where ((`a` = 50) and (`b` = 3)))))"
     },
     {
       "join_preparation": {
@@ -345,13 +453,13 @@ WHERE NOT EXISTS
           {
             "condition_processing": {
               "condition": "WHERE",
-              "original_condition": "(not(exists(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a` = 50) and (`test`.`t1`.`b` = 3)))))",
+              "original_condition": "(not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))",
               "evaluate_subselect_cond_steps": [
               ] /* evaluate_subselect_cond_steps */,
-              "after_equality_propagation": "(not(exists(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a` = 50) and (`test`.`t1`.`b` = 3)))))",
+              "after_equality_propagation": "(not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))",
               "evaluate_subselect_cond_steps": [
               ] /* evaluate_subselect_cond_steps */,
-              "after_constant_propagation": "(not(exists(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a` = 50) and (`test`.`t1`.`b` = 3)))))",
+              "after_constant_propagation": "(not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3)))))",
               "evaluate_subselect_cond_steps": [
                 {
                   "subselect_exec": {
@@ -364,10 +472,10 @@ WHERE NOT EXISTS
                             {
                               "condition_processing": {
                                 "condition": "WHERE",
-                                "original_condition": "((`test`.`t1`.`a` = 50) and (`test`.`t1`.`b` = 3))",
-                                "after_equality_propagation": "(multiple equal(50, `test`.`t1`.`a`) and multiple equal(3, `test`.`t1`.`b`))",
-                                "after_constant_propagation": "(multiple equal(50, `test`.`t1`.`a`) and multiple equal(3, `test`.`t1`.`b`))",
-                                "after_trivial_conditions_removal": "(multiple equal(50, `test`.`t1`.`a`) and multiple equal(3, `test`.`t1`.`b`))"
+                                "original_condition": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))",
+                                "after_equality_propagation": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))",
+                                "after_constant_propagation": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))",
+                                "after_trivial_conditions_removal": "((`test`.`t2`.`a` = 50) and (`test`.`t2`.`b` = 3))"
                               } /* condition_processing */
                             },
                             {
@@ -378,7 +486,7 @@ WHERE NOT EXISTS
                               "records_estimation": [
                                 {
                                   "database": "test",
-                                  "table": "t1",
+                                  "table": "t2",
                                   "records": 1,
                                   "cost": 1,
                                   "table_type": "system"
@@ -418,4 +526,4 @@ WHERE NOT EXISTS
   ] /* steps */
 }	0	0
 
-DROP TABLE t1;
+DROP TABLE t1,t2;

=== modified file 'mysql-test/t/optimizer_trace_range.test'
--- a/mysql-test/t/optimizer_trace_range.test	2010-12-13 14:03:16 +0000
+++ b/mysql-test/t/optimizer_trace_range.test	2011-01-05 08:42:16 +0000
@@ -126,6 +126,13 @@ EXPLAIN SELECT * FROM t1 WHERE (key1 > 1
 --echo
 SELECT * FROM information_schema.OPTIMIZER_TRACE;
 
+# Range analysis on straight join
+--echo
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2 
+  WHERE t1.key1=t2.key1a AND t1.key2 > 1020;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
 DROP TABLE t1,t2;
 
 CREATE TABLE t1 (
@@ -184,11 +191,21 @@ DROP TABLE t1;
 
 # Test that range optimization is not shown for every outer record 
 # when there is a dynamic range.
-SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
 CREATE TABLE t1(c INT);
 INSERT INTO t1 VALUES (),();
 CREATE TABLE t2 (b INT, KEY(b));
 INSERT INTO t2 VALUES (),(),();
+
+# First, enable dynamic range optimization tracing
+SET optimizer_trace_features="greedy_search=off,dynamic_range=on";
+EXPLAIN SELECT 1 FROM 
+ (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+# Second, disable dynamic range optimization tracing
+SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
 EXPLAIN SELECT 1 FROM 
  (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
 --echo
@@ -210,4 +227,27 @@ SELECT * from t1 where topic = all (SELE
 SELECT * FROM information_schema.OPTIMIZER_TRACE;
 --echo
 
-drop table t1;
\ No newline at end of file
+drop table t1;
+
+# Range analysis in test_if_skip_sort_order 
+# (records_estimation_for_index_ordering)
+CREATE TABLE t1 (
+  i1 int,
+  i2 int,
+  c char(1),
+  KEY k1 (i1),
+  KEY k2 (i1, i2)
+);
+INSERT INTO t1 VALUES (0,1,'2'),(3,2,'1');
+
+EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+DROP TABLE t1;

=== modified file 'mysql-test/t/optimizer_trace_subquery.test'
--- a/mysql-test/t/optimizer_trace_subquery.test	2010-12-13 14:03:16 +0000
+++ b/mysql-test/t/optimizer_trace_subquery.test	2011-01-05 08:42:16 +0000
@@ -26,14 +26,24 @@ DROP TABLE t1,t2;
 SET @@optimizer_trace_features="default";
 
 
-# evaluate_subquery_cond_step (subquery eval during optimize_cond)
-CREATE TABLE t1 (a int, b int, c int);
+CREATE TABLE t1 (a FLOAT(5,4) zerofill);
+CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
 
-SELECT 50, 3, 3 FROM DUAL
+# evaluate_subselect_cond_steps for build_equal_item()
+SELECT t1.a
+FROM t1
+WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
+      t1.a= (SELECT a FROM t2 LIMIT 1) ;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
+# evaluate_subselect_cond_steps for remove_eq_conds
+SELECT 1 FROM DUAL
 WHERE NOT EXISTS
-  (SELECT * FROM t1 WHERE a = 50 AND b = 3);
+  (SELECT * FROM t2 WHERE a = 50 AND b = 3);
 --echo
 SELECT * FROM information_schema.OPTIMIZER_TRACE;
 --echo
 
-DROP TABLE t1;
\ No newline at end of file
+DROP TABLE t1,t2;

=== modified file 'sql/sys_vars.cc'
--- a/sql/sys_vars.cc	2010-11-23 10:21:53 +0000
+++ b/sql/sys_vars.cc	2011-01-05 08:42:16 +0000
@@ -1433,7 +1433,7 @@ static Sys_var_flagset Sys_optimizer_tra
        "optimizer_trace_features",
        "Enables/disables tracing of selected features of the Optimizer:"
        " optimizer_trace_features=option=val[,option=val...], where option is one of"
-       " {greedy_search, range_optimizer}"
+       " {greedy_search, range_optimizer, dynamic_range, repeated_subselect}"
        " and val is one of {on, off, default}",
        SESSION_VAR(optimizer_trace_features), CMD_LINE(REQUIRED_ARG),
        Opt_trace_context::feature_names,


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110105084216-wlyy01p9uyrd0z8n.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3243) Jorgen Loland5 Jan