List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:November 18 2011 12:17pm
Subject:bzr push into mysql-trunk branch (jorgen.loland:3624 to 3625) Bug#13400713
View as plain text  
 3625 Jorgen Loland	2011-11-18
      BUG#13400713 - ADD OPTIMIZER TRACING OF TEMPORARY TABLES
      
      Add optimizer tracing to temporary table creation

    added:
      mysql-test/suite/opt_trace/r/temp_table.result
      mysql-test/suite/opt_trace/t/temp_table.test
    modified:
      mysql-test/r/metadata.result
      mysql-test/suite/opt_trace/include/subquery.inc
      mysql-test/suite/opt_trace/r/charset.result
      mysql-test/suite/opt_trace/r/filesort_pq.result
      mysql-test/suite/opt_trace/r/general2_no_prot.result
      mysql-test/suite/opt_trace/r/general2_ps_prot.result
      mysql-test/suite/opt_trace/r/general_no_prot_none.result
      mysql-test/suite/opt_trace/r/general_ps_prot_none.result
      mysql-test/suite/opt_trace/r/range_no_prot.result
      mysql-test/suite/opt_trace/r/range_ps_prot.result
      mysql-test/suite/opt_trace/r/security_no_prot.result
      mysql-test/suite/opt_trace/r/security_ps_prot.result
      mysql-test/suite/opt_trace/r/subquery_no_prot.result
      mysql-test/suite/opt_trace/r/subquery_ps_prot.result
      sql/sql_derived.cc
      sql/sql_select.cc
      sql/sql_select.h
 3624 Ashish Agarwal	2011-11-18
      bug#11760213 - 52599: ALTER TABLE REMOVE PARTITIONING ON
                            NON-PARTITIONED TABLE CORRUPTS MYISAM.
      
      ISSUE: Removing partitioning on a not partitioned table
             was permitted, but wasn't handled properly.
             As a result storage engine and .frm table
             definitions may go out of sync.
      
      SOLUTION: With this patch removing partitioning
                on a not partitioned is forbidden.

    removed:
      mysql-test/r/disabled_partition.require
      mysql-test/r/not_partition.require
    modified:
      mysql-test/r/not_partition.result
      mysql-test/r/partition_disabled.result
      mysql-test/r/partition_myisam.result
      mysql-test/t/not_partition.test
      mysql-test/t/partition_disabled.test
      mysql-test/t/partition_myisam.test
      sql/sql_partition.cc
      sql/sql_yacc.yy
=== modified file 'mysql-test/r/metadata.result'
--- a/mysql-test/r/metadata.result	2011-05-11 11:11:57 +0000
+++ b/mysql-test/r/metadata.result	2011-11-18 12:17:45 +0000
@@ -191,12 +191,12 @@ Catalog	Database	Table	Table_alias	Colum
 2008-01-02	2008-01-02	2008-01-02	2008-01-02	2008-01-02	2008-01-02
 2008-01-03	2008-01-03	2008-01-03	2008-01-03	2008-01-03	2008-01-03
 COALESCE(d, d)	IFNULL(d, d)	IF(i, d, d)	CASE i WHEN i THEN d ELSE d END	GREATEST(d, d)	LEAST(d, d)
-def				CASE i WHEN i THEN d ELSE d END	CASE i WHEN i THEN d ELSE d END	10	10	10	Y	128	0	63
-def				COALESCE(d, d)	COALESCE(d, d)	10	10	10	Y	128	0	63
-def				GREATEST(d, d)	GREATEST(d, d)	10	10	10	Y	128	0	63
-def				IF(i, d, d)	IF(i, d, d)	10	10	10	Y	128	0	63
-def				IFNULL(d, d)	IFNULL(d, d)	10	10	10	Y	128	0	63
-def				LEAST(d, d)	LEAST(d, d)	10	10	10	Y	128	0	63
+def			intermediate_tmp_table	CASE i WHEN i THEN d ELSE d END	CASE i WHEN i THEN d ELSE d END	10	10	10	Y	128	0	63
+def			intermediate_tmp_table	COALESCE(d, d)	COALESCE(d, d)	10	10	10	Y	128	0	63
+def			intermediate_tmp_table	GREATEST(d, d)	GREATEST(d, d)	10	10	10	Y	128	0	63
+def			intermediate_tmp_table	IF(i, d, d)	IF(i, d, d)	10	10	10	Y	128	0	63
+def			intermediate_tmp_table	IFNULL(d, d)	IFNULL(d, d)	10	10	10	Y	128	0	63
+def			intermediate_tmp_table	LEAST(d, d)	LEAST(d, d)	10	10	10	Y	128	0	63
 DROP TABLE t1;
 #
 # Bug#41788 mysql_fetch_field returns org_table == table by a view

=== modified file 'mysql-test/suite/opt_trace/include/subquery.inc'
--- a/mysql-test/suite/opt_trace/include/subquery.inc	2011-11-08 07:51:49 +0000
+++ b/mysql-test/suite/opt_trace/include/subquery.inc	2011-11-18 12:17:45 +0000
@@ -1,6 +1,7 @@
 # Test for optimizer tracing of subqueries
 
 --source include/have_optimizer_trace.inc
+--source include/have_64bit.inc
 
 SET optimizer_trace_max_mem_size=1048576; # 1MB
 SET optimizer_trace="enabled=on,end_marker=on,one_line=off";

=== modified file 'mysql-test/suite/opt_trace/r/charset.result'
--- a/mysql-test/suite/opt_trace/r/charset.result	2011-10-10 09:08:15 +0000
+++ b/mysql-test/suite/opt_trace/r/charset.result	2011-11-18 12:17:45 +0000
@@ -233,6 +233,19 @@ select * from v1 where v1.col = 'ÁÂÃ�             } /* join_optimization */
           },
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "v1",
+                "row_length": 7,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 149796
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [

=== modified file 'mysql-test/suite/opt_trace/r/filesort_pq.result'
--- a/mysql-test/suite/opt_trace/r/filesort_pq.result	2011-11-07 15:32:36 +0000
+++ b/mysql-test/suite/opt_trace/r/filesort_pq.result	2011-11-18 12:17:45 +0000
@@ -1552,11 +1552,24 @@ SELECT * from t1 ORDER BY rand(2) LIMIT 
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 219,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4788
+              }
+            }
+          },
+          {
             "filesort_information": [
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "tmp_table_column"
               }
             ],
@@ -3934,17 +3947,30 @@ ORDER BY tmp.f1, f0 LIMIT 30	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 235,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4462
+              }
+            }
+          },
+          {
             "filesort_information": [
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f1"
               },
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f0"
               }
             ],
@@ -4209,17 +4235,30 @@ ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 235,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4462
+              }
+            }
+          },
+          {
             "filesort_information": [
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f1"
               },
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f0"
               }
             ],
@@ -4489,17 +4528,30 @@ ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 235,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4462
+              }
+            }
+          },
+          {
             "filesort_information": [
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f1"
               },
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f0"
               }
             ],
@@ -4797,17 +4849,30 @@ ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 235,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4462
+              }
+            }
+          },
+          {
             "filesort_information": [
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f1"
               },
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f0"
               }
             ],
@@ -5101,6 +5166,19 @@ SELECT * FROM v1	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "v1",
+                "row_length": 210,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4993
+              }
+            }
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
@@ -5398,6 +5476,19 @@ SELECT * FROM v1 ORDER BY f2, f0 LIMIT 3
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "v1",
+                "row_length": 210,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4993
+              }
+            }
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
@@ -6034,6 +6125,45 @@ LIMIT 30	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "v1",
+                "row_length": 210,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4993
+              }
+            }
+          },
+          {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "v2",
+                "row_length": 210,
+                "key_length": 5,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4993
+              }
+            }
+          },
+          {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 419,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 2502
+              }
+            }
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
@@ -6116,19 +6246,19 @@ LIMIT 30	{
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f2"
               },
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f0"
               },
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f0"
               }
             ],
@@ -6286,17 +6416,30 @@ GROUP BY 1 ORDER BY 2,1 LIMIT 5	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 18,
+                "key_length": 9,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 58254
+              }
+            }
+          },
+          {
             "filesort_information": [
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "count(f2)"
               },
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f3"
               }
             ],
@@ -7196,6 +7339,19 @@ SELECT * FROM v1 ORDER BY f2, f0 LIMIT 3
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "v1",
+                "row_length": 210,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4993
+              }
+            }
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
@@ -7601,6 +7757,32 @@ ORDER BY d1.f2 DESC LIMIT 30	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "d1",
+                "row_length": 210,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4993
+              }
+            }
+          },
+          {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 206,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 5090
+              }
+            }
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
@@ -7638,7 +7820,7 @@ ORDER BY d1.f2 DESC LIMIT 30	{
               {
                 "direction": "desc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f2"
               }
             ],

=== modified file 'mysql-test/suite/opt_trace/r/general2_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_no_prot.result	2011-11-11 12:36:01 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_no_prot.result	2011-11-18 12:17:45 +0000
@@ -954,11 +954,24 @@ TRACE
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 3,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 349525
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "filesort_information": [
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f2"
               }
             ] /* filesort_information */,
@@ -1333,15 +1346,41 @@ TRACE
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "teeone",
+                "row_length": 16,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 65536
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
                 {
+                  "creating_tmp_table": {
+                    "temporary_table_info": {
+                      "database": "",
+                      "table": "intermediate_tmp_table",
+                      "row_length": 22,
+                      "key_length": 5,
+                      "unique_constraint": false,
+                      "location": "memory (heap)",
+                      "row_limit_estimate": 47662
+                    } /* temporary_table_info */
+                  } /* creating_tmp_table */
+                },
+                {
                   "filesort_information": [
                     {
                       "direction": "asc",
                       "database": "",
-                      "table": "",
+                      "table": "intermediate_tmp_table",
                       "field": "b"
                     }
                   ] /* filesort_information */,
@@ -3648,6 +3687,19 @@ select 497, tmp.ID, NULL from (select 49
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "tmp",
+                "row_length": 10,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 104857
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
@@ -4844,11 +4896,24 @@ GROUP BY field2 ORDER BY alias1.col_int_
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 18,
+                "key_length": 4,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 58254
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "filesort_information": [
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "col_int_key"
               }
             ] /* filesort_information */,

=== modified file 'mysql-test/suite/opt_trace/r/general2_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_ps_prot.result	2011-11-11 12:36:01 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_ps_prot.result	2011-11-18 12:17:45 +0000
@@ -972,11 +972,24 @@ TRACE
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 3,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 349525
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "filesort_information": [
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "f2"
               }
             ] /* filesort_information */,
@@ -1351,15 +1364,41 @@ TRACE
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "teeone",
+                "row_length": 16,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 65536
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
                 {
+                  "creating_tmp_table": {
+                    "temporary_table_info": {
+                      "database": "",
+                      "table": "intermediate_tmp_table",
+                      "row_length": 22,
+                      "key_length": 5,
+                      "unique_constraint": false,
+                      "location": "memory (heap)",
+                      "row_limit_estimate": 47662
+                    } /* temporary_table_info */
+                  } /* creating_tmp_table */
+                },
+                {
                   "filesort_information": [
                     {
                       "direction": "asc",
                       "database": "",
-                      "table": "",
+                      "table": "intermediate_tmp_table",
                       "field": "b"
                     }
                   ] /* filesort_information */,
@@ -3701,6 +3740,19 @@ select 497, tmp.ID, NULL from (select 49
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "tmp",
+                "row_length": 10,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 104857
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
@@ -4897,11 +4949,24 @@ GROUP BY field2 ORDER BY alias1.col_int_
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 18,
+                "key_length": 4,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 58254
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "filesort_information": [
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "col_int_key"
               }
             ] /* filesort_information */,

=== modified file 'mysql-test/suite/opt_trace/r/general_no_prot_none.result'
--- a/mysql-test/suite/opt_trace/r/general_no_prot_none.result	2011-11-11 12:36:01 +0000
+++ b/mysql-test/suite/opt_trace/r/general_no_prot_none.result	2011-11-18 12:17:45 +0000
@@ -610,6 +610,19 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
             } /* join_preparation */
           },
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "",
+                "row_length": 9,
+                "key_length": 8,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 116508
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) union /* select#3 */ select 2 from `test`.`t6` where (`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL))) AS `RESULT` from `test`.`t5`"
           }
         ] /* steps */
@@ -1045,6 +1058,19 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
             } /* join_preparation */
           },
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "",
+                "row_length": 9,
+                "key_length": 8,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 116508
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "expanded_query": "/* select#1 */ select `test`.`t5`.`c` AS `c` from `test`.`t5` where <in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))"
           }
         ] /* steps */
@@ -1316,7 +1342,7 @@ select (@query:=QUERY)+NULL, (@trace:=TR
 NULL	NULL
 select length(@trace);
 length(@trace)
-14121
+14565
 set optimizer_trace_max_mem_size=13500;
 select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
 length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
@@ -1325,7 +1351,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)
-735
+1187
 select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
 (@query2:=QUERY)+NULL	(@trace2:=TRACE)+NULL
 NULL	NULL
@@ -1333,7 +1359,7 @@ select length(@trace2),
 (length(@trace2) + @missing_bytes) = length(@trace),
 @query2 = @query;
 length(@trace2)	(length(@trace2) + @missing_bytes) = length(@trace)	@query2 = @query
-13386	1	1
+13378	1	1
 select length(@query2) + length(@trace2)
 between (@@optimizer_trace_max_mem_size-200) and (@@optimizer_trace_max_mem_size+200);
 length(@query2) + length(@trace2)
@@ -3991,6 +4017,19 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
       } /* join_preparation */
     },
     {
+      "creating_tmp_table": {
+        "temporary_table_info": {
+          "database": "",
+          "table": "",
+          "row_length": 5,
+          "key_length": 5,
+          "unique_constraint": false,
+          "location": "memory (heap)",
+          "row_limit_estimate": 209715
+        } /* temporary_table_info */
+      } /* creating_tmp_table */
+    },
+    {
       "join_optimization": {
         "select#": 1,
         "steps": [
@@ -5817,11 +5856,24 @@ trace
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 30,
+                "key_length": 5,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 34952
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "filesort_information": [
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "a"
               }
             ] /* filesort_information */,
@@ -6208,6 +6260,19 @@ insert into t6 select * from t6 where d>
       "join_execution": {
         "select#": 1,
         "steps": [
+          {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 5,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 209715
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          }
         ] /* steps */
       } /* join_execution */
     }
@@ -6366,6 +6431,19 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
             ] /* considered_execution_plans */
           },
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "",
+                "row_length": 12,
+                "key_length": 7,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 87381
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "attaching_conditions_to_tables": {
               "original_condition": "(`test`.`t6`.`d` > 7000)",
               "attached_conditions_computation": [
@@ -10330,6 +10408,19 @@ explain select * from v1 where id="b"	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "v1",
+                "row_length": 21,
+                "key_length": 16,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 49932
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_explain": {
               "select#": 2,
               "steps": [
@@ -10352,6 +10443,19 @@ select * from information_schema.session
 VARIABLE_NAME="optimizer_trace"	{
   "steps": [
     {
+      "creating_tmp_table": {
+        "temporary_table_info": {
+          "database": "information_schema",
+          "table": "session_variables",
+          "row_length": 3268,
+          "key_length": 0,
+          "unique_constraint": false,
+          "location": "memory (heap)",
+          "row_limit_estimate": 320
+        } /* temporary_table_info */
+      } /* creating_tmp_table */
+    },
+    {
       "join_preparation": {
         "select#": 1,
         "steps": [
@@ -10498,6 +10602,19 @@ select TRACE into dumpfile 'MYSQLTEST_VA
       }
     },
     {
+      "creating_tmp_table": {
+        "temporary_table_info": {
+          "database": "",
+          "table": "",
+          "row_length": 9,
+          "key_length": 8,
+          "unique_constraint": false,
+          "location": "memory (heap)",
+          "row_limit_estimate": 116508
+        }
+      }
+    },
+    {
       "join_optimization": {
         "select#": 1,
         "steps": [

=== modified file 'mysql-test/suite/opt_trace/r/general_ps_prot_none.result'
--- a/mysql-test/suite/opt_trace/r/general_ps_prot_none.result	2011-11-11 12:36:01 +0000
+++ b/mysql-test/suite/opt_trace/r/general_ps_prot_none.result	2011-11-18 12:17:45 +0000
@@ -610,6 +610,19 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
             } /* join_preparation */
           },
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "",
+                "row_length": 9,
+                "key_length": 8,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 116508
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) union /* select#3 */ select 2 from `test`.`t6` where (`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL))) AS `RESULT` from `test`.`t5`"
           }
         ] /* steps */
@@ -1025,6 +1038,19 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
             } /* join_preparation */
           },
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "",
+                "row_length": 9,
+                "key_length": 8,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 116508
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "expanded_query": "/* select#1 */ select `test`.`t5`.`c` AS `c` from `test`.`t5` where <in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))"
           }
         ] /* steps */
@@ -1296,16 +1322,16 @@ select (@query:=QUERY)+NULL, (@trace:=TR
 NULL	NULL
 select length(@trace);
 length(@trace)
-13343
+13787
 set optimizer_trace_max_mem_size=13500;
 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
+403
 select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
 (@query2:=QUERY)+NULL	(@trace2:=TRACE)+NULL
 NULL	NULL
@@ -1313,7 +1339,7 @@ select length(@trace2),
 (length(@trace2) + @missing_bytes) = length(@trace),
 @query2 = @query;
 length(@trace2)	(length(@trace2) + @missing_bytes) = length(@trace)	@query2 = @query
-13343	1	1
+13384	1	1
 select length(@query2) + length(@trace2)
 between (@@optimizer_trace_max_mem_size-200) and (@@optimizer_trace_max_mem_size+200);
 length(@query2) + length(@trace2)
@@ -3961,6 +3987,19 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
       } /* join_preparation */
     },
     {
+      "creating_tmp_table": {
+        "temporary_table_info": {
+          "database": "",
+          "table": "",
+          "row_length": 5,
+          "key_length": 5,
+          "unique_constraint": false,
+          "location": "memory (heap)",
+          "row_limit_estimate": 209715
+        } /* temporary_table_info */
+      } /* creating_tmp_table */
+    },
+    {
       "join_optimization": {
         "select#": 1,
         "steps": [
@@ -5775,11 +5814,24 @@ trace
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 30,
+                "key_length": 5,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 34952
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "filesort_information": [
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "a"
               }
             ] /* filesort_information */,
@@ -6166,6 +6218,19 @@ insert into t6 select * from t6 where d>
       "join_execution": {
         "select#": 1,
         "steps": [
+          {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 5,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 209715
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          }
         ] /* steps */
       } /* join_execution */
     }
@@ -6324,6 +6389,19 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
             ] /* considered_execution_plans */
           },
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "",
+                "row_length": 12,
+                "key_length": 7,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 87381
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "attaching_conditions_to_tables": {
               "original_condition": "(`test`.`t6`.`d` > 7000)",
               "attached_conditions_computation": [
@@ -10246,6 +10324,19 @@ explain select * from v1 where id="b"	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "v1",
+                "row_length": 21,
+                "key_length": 16,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 49932
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_explain": {
               "select#": 2,
               "steps": [
@@ -10268,6 +10359,19 @@ select * from information_schema.session
 VARIABLE_NAME="optimizer_trace"	{
   "steps": [
     {
+      "creating_tmp_table": {
+        "temporary_table_info": {
+          "database": "information_schema",
+          "table": "session_variables",
+          "row_length": 3268,
+          "key_length": 0,
+          "unique_constraint": false,
+          "location": "memory (heap)",
+          "row_limit_estimate": 320
+        } /* temporary_table_info */
+      } /* creating_tmp_table */
+    },
+    {
       "join_preparation": {
         "select#": 1,
         "steps": [
@@ -10414,6 +10518,19 @@ select TRACE into dumpfile 'MYSQLTEST_VA
       }
     },
     {
+      "creating_tmp_table": {
+        "temporary_table_info": {
+          "database": "",
+          "table": "",
+          "row_length": 9,
+          "key_length": 8,
+          "unique_constraint": false,
+          "location": "memory (heap)",
+          "row_limit_estimate": 116508
+        }
+      }
+    },
+    {
       "join_optimization": {
         "select#": 1,
         "steps": [

=== modified file 'mysql-test/suite/opt_trace/r/range_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/range_no_prot.result	2011-11-10 13:05:08 +0000
+++ b/mysql-test/suite/opt_trace/r/range_no_prot.result	2011-11-18 12:17:45 +0000
@@ -3945,6 +3945,19 @@ EXPLAIN SELECT 1 FROM 
             } /* join_optimization */
           },
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "d2",
+                "row_length": 5,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 209715
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
@@ -4283,6 +4296,19 @@ EXPLAIN SELECT 1 FROM 
             } /* join_optimization */
           },
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "d2",
+                "row_length": 5,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 209715
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [

=== modified file 'mysql-test/suite/opt_trace/r/range_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/range_ps_prot.result	2011-11-10 13:05:08 +0000
+++ b/mysql-test/suite/opt_trace/r/range_ps_prot.result	2011-11-18 12:17:45 +0000
@@ -3945,6 +3945,19 @@ EXPLAIN SELECT 1 FROM 
             } /* join_optimization */
           },
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "d2",
+                "row_length": 5,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 209715
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
@@ -4283,6 +4296,19 @@ EXPLAIN SELECT 1 FROM 
             } /* join_optimization */
           },
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "d2",
+                "row_length": 5,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 209715
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [

=== modified file 'mysql-test/suite/opt_trace/r/security_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/security_no_prot.result	2011-09-22 12:51:05 +0000
+++ b/mysql-test/suite/opt_trace/r/security_no_prot.result	2011-11-18 12:17:45 +0000
@@ -869,7 +869,7 @@ insert into t2 values(current_user());
 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
 QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
 insert into t2 values(current_user())	20	0
-insert into t3 select * from t3	2274	0
+insert into t3 select * from t3	2688	0
 # Trace exposed body of trigger, and content of t2/t3, which we
 # could see anyway:
 show create trigger trg2;
@@ -888,7 +888,7 @@ a
 first
 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
 QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
-select a from (select a from t1 where a like "f%") as tt where a like "fi%"	6939	0
+select a from (select a from t1 where a like "f%") as tt where a like "fi%"	7334	0
 
 # For routines, as they only use t1 and we added only one
 # privilege on t1, we have nothing to remove.

=== modified file 'mysql-test/suite/opt_trace/r/security_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/security_ps_prot.result	2011-09-22 12:51:05 +0000
+++ b/mysql-test/suite/opt_trace/r/security_ps_prot.result	2011-11-18 12:17:45 +0000
@@ -935,7 +935,7 @@ select QUERY, length(TRACE), INSUFFICIEN
 QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
 insert into t2 values(current_user())	20	0
 insert into t2 values(current_user())	20	0
-insert into t3 select * from t3	2274	0
+insert into t3 select * from t3	2688	0
 # Trace exposed body of trigger, and content of t2/t3, which we
 # could see anyway:
 show create trigger trg2;
@@ -955,7 +955,7 @@ first
 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
 QUERY	length(TRACE)	INSUFFICIENT_PRIVILEGES
 select a from (select a from t1 where a like "f%") as tt where a like "fi%"	613	0
-select a from (select a from t1 where a like "f%") as tt where a like "fi%"	6939	0
+select a from (select a from t1 where a like "f%") as tt where a like "fi%"	7334	0
 
 # For routines, as they only use t1 and we added only one
 # privilege on t1, we have nothing to remove.

=== modified file 'mysql-test/suite/opt_trace/r/subquery_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_no_prot.result	2011-11-17 10:09:13 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_no_prot.result	2011-11-18 12:17:45 +0000
@@ -1720,6 +1720,32 @@ field4,field5,field6	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "alias2",
+                "row_length": 46,
+                "key_length": 8,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 22795
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 13,
+                "key_length": 12,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 80659
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 5,
               "steps": [
@@ -1731,19 +1757,19 @@ field4,field5,field6	{
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "col_varchar_key"
               },
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "col_date_key"
               },
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "col_varchar_nokey"
               }
             ] /* filesort_information */,

=== modified file 'mysql-test/suite/opt_trace/r/subquery_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_ps_prot.result	2011-11-17 10:09:13 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_ps_prot.result	2011-11-18 12:17:45 +0000
@@ -1698,6 +1698,32 @@ field4,field5,field6	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "alias2",
+                "row_length": 46,
+                "key_length": 8,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 22795
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 13,
+                "key_length": 12,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 80659
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 5,
               "steps": [
@@ -1709,19 +1735,19 @@ field4,field5,field6	{
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "col_varchar_key"
               },
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "col_date_key"
               },
               {
                 "direction": "asc",
                 "database": "",
-                "table": "",
+                "table": "intermediate_tmp_table",
                 "field": "col_varchar_nokey"
               }
             ] /* filesort_information */,

=== added file 'mysql-test/suite/opt_trace/r/temp_table.result'
--- a/mysql-test/suite/opt_trace/r/temp_table.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/opt_trace/r/temp_table.result	2011-11-18 12:17:45 +0000
@@ -0,0 +1,527 @@
+SET optimizer_trace_max_mem_size=1048576;
+SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
+CREATE TABLE tmp (
+pk INT PRIMARY KEY AUTO_INCREMENT,
+col1 CHAR (1)
+);
+INSERT INTO tmp(col1) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
+CREATE TABLE t1 (
+uniq VARCHAR(10),
+col1 VARCHAR(10),
+col2 VARCHAR(1024)
+);
+INSERT INTO t1 SELECT pk, col1, col1 FROM tmp;
+SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3;
+uniq	col1
+1	a
+2	b
+3	c
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
+SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "expanded_query": "/* select#1 */ select `test`.`t1`.`uniq` AS `uniq`,`test`.`t1`.`col1` AS `col1` from `test`.`t1` group by `test`.`t1`.`col2`,`test`.`t1`.`uniq` limit 3"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "table_dependencies": [
+              {
+                "database": "test",
+                "table": "t1",
+                "row_may_be_null": false,
+                "map_bit": 0,
+                "depends_on_map_bits": [
+                ] /* depends_on_map_bits */
+              }
+            ] /* table_dependencies */
+          },
+          {
+            "rows_estimation": [
+              {
+                "database": "test",
+                "table": "t1",
+                "table_scan": {
+                  "rows": 8,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* rows_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "database": "test",
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "scan",
+                      "rows": 8,
+                      "cost": 2.0391,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 3.6391,
+                "rows_for_plan": 8,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": null,
+              "attached_conditions_computation": [
+              ] /* attached_conditions_computation */,
+              "attached_conditions_summary": [
+                {
+                  "database": "test",
+                  "table": "t1",
+                  "attached": null
+                }
+              ] /* attached_conditions_summary */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "clause_processing": {
+              "clause": "GROUP BY",
+              "original_clause": "`test`.`t1`.`col2`,`test`.`t1`.`uniq`",
+              "items": [
+                {
+                  "item": "`test`.`t1`.`col2`"
+                },
+                {
+                  "item": "`test`.`t1`.`uniq`"
+                }
+              ] /* items */,
+              "resulting_clause_is_simple": true,
+              "resulting_clause": "`test`.`t1`.`col2`,`test`.`t1`.`uniq`"
+            } /* clause_processing */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "access_type": "table_scan"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+          {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 1054,
+                "key_length": 1040,
+                "unique_constraint": true,
+                "location": "disk (MyISAM)",
+                "record_format": "fixed"
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
+            "filesort_information": [
+              {
+                "direction": "asc",
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "field": "col2"
+              },
+              {
+                "direction": "asc",
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "field": "uniq"
+              }
+            ] /* filesort_information */,
+            "filesort_priority_queue_optimization": {
+              "limit": 3,
+              "rows_estimate": 18,
+              "row_size": 1043,
+              "memory_available": 262144,
+              "chosen": true
+            } /* filesort_priority_queue_optimization */,
+            "filesort_execution": [
+            ] /* filesort_execution */,
+            "filesort_summary": {
+              "rows": 4,
+              "examined_rows": 8,
+              "number_of_tmp_files": 0,
+              "sort_buffer_size": 4204,
+              "sort_mode": "<sort_key, rowid>"
+            } /* filesort_summary */
+          }
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+SELECT uniq, col1, col2 FROM t1 GROUP BY uniq ;
+uniq	col1	col2
+1	a	a
+2	b	b
+3	c	c
+4	d	d
+5	e	e
+6	f	f
+7	g	g
+8	h	h
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
+SELECT uniq, col1, col2 FROM t1 GROUP BY uniq	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "expanded_query": "/* select#1 */ select `test`.`t1`.`uniq` AS `uniq`,`test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` group by `test`.`t1`.`uniq`"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "table_dependencies": [
+              {
+                "database": "test",
+                "table": "t1",
+                "row_may_be_null": false,
+                "map_bit": 0,
+                "depends_on_map_bits": [
+                ] /* depends_on_map_bits */
+              }
+            ] /* table_dependencies */
+          },
+          {
+            "rows_estimation": [
+              {
+                "database": "test",
+                "table": "t1",
+                "table_scan": {
+                  "rows": 8,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* rows_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "database": "test",
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "scan",
+                      "rows": 8,
+                      "cost": 2.0391,
+                      "chosen": true,
+                      "use_tmp_table": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 3.6391,
+                "rows_for_plan": 8,
+                "sort_cost": 8,
+                "new_cost_for_plan": 11.639,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": null,
+              "attached_conditions_computation": [
+              ] /* attached_conditions_computation */,
+              "attached_conditions_summary": [
+                {
+                  "database": "test",
+                  "table": "t1",
+                  "attached": null
+                }
+              ] /* attached_conditions_summary */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "clause_processing": {
+              "clause": "GROUP BY",
+              "original_clause": "`test`.`t1`.`uniq`",
+              "items": [
+                {
+                  "item": "`test`.`t1`.`uniq`"
+                }
+              ] /* items */,
+              "resulting_clause_is_simple": true,
+              "resulting_clause": "`test`.`t1`.`uniq`"
+            } /* clause_processing */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "access_type": "table_scan"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+          {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 1050,
+                "key_length": 13,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 998
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
+            "filesort_information": [
+              {
+                "direction": "asc",
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "field": "uniq"
+              }
+            ] /* filesort_information */,
+            "filesort_priority_queue_optimization": {
+              "usable": false,
+              "cause": "not applicable (no LIMIT)"
+            } /* filesort_priority_queue_optimization */,
+            "filesort_execution": [
+            ] /* filesort_execution */,
+            "filesort_summary": {
+              "rows": 8,
+              "examined_rows": 8,
+              "number_of_tmp_files": 0,
+              "sort_buffer_size": 486,
+              "sort_mode": "<sort_key, rowid>"
+            } /* filesort_summary */
+          }
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+select @@tmp_table_size;
+@@tmp_table_size
+16777216
+SET @old_size= @@tmp_table_size;
+SET SESSION tmp_table_size= 1024;
+INSERT INTO t1 SELECT pk+8, col1, col1 FROM tmp;
+SELECT uniq, col1, col2 FROM t1 GROUP BY uniq;
+uniq	col1	col2
+1	a	a
+10	b	b
+11	c	c
+12	d	d
+13	e	e
+14	f	f
+15	g	g
+16	h	h
+2	b	b
+3	c	c
+4	d	d
+5	e	e
+6	f	f
+7	g	g
+8	h	h
+9	a	a
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
+SELECT uniq, col1, col2 FROM t1 GROUP BY uniq	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "expanded_query": "/* select#1 */ select `test`.`t1`.`uniq` AS `uniq`,`test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` group by `test`.`t1`.`uniq`"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "table_dependencies": [
+              {
+                "database": "test",
+                "table": "t1",
+                "row_may_be_null": false,
+                "map_bit": 0,
+                "depends_on_map_bits": [
+                ] /* depends_on_map_bits */
+              }
+            ] /* table_dependencies */
+          },
+          {
+            "rows_estimation": [
+              {
+                "database": "test",
+                "table": "t1",
+                "table_scan": {
+                  "rows": 16,
+                  "cost": 2
+                } /* table_scan */
+              }
+            ] /* rows_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "database": "test",
+                "table": "t1",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "scan",
+                      "rows": 16,
+                      "cost": 2.0781,
+                      "chosen": true,
+                      "use_tmp_table": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 5.2781,
+                "rows_for_plan": 16,
+                "sort_cost": 16,
+                "new_cost_for_plan": 21.278,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": null,
+              "attached_conditions_computation": [
+              ] /* attached_conditions_computation */,
+              "attached_conditions_summary": [
+                {
+                  "database": "test",
+                  "table": "t1",
+                  "attached": null
+                }
+              ] /* attached_conditions_summary */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "clause_processing": {
+              "clause": "GROUP BY",
+              "original_clause": "`test`.`t1`.`uniq`",
+              "items": [
+                {
+                  "item": "`test`.`t1`.`uniq`"
+                }
+              ] /* items */,
+              "resulting_clause_is_simple": true,
+              "resulting_clause": "`test`.`t1`.`uniq`"
+            } /* clause_processing */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t1",
+                "access_type": "table_scan"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+          {
+            "creating_tmp_table": {
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 1050,
+                "key_length": 13,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 1
+              } /* temporary_table_info */
+            } /* creating_tmp_table */
+          },
+          {
+            "converting_temp_table_to_myisam": {
+              "cause": "memory_table_size_exceeded",
+              "temporary_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 1050,
+                "key_length": 13,
+                "unique_constraint": false,
+                "location": "disk (MyISAM)",
+                "record_format": "fixed"
+              } /* temporary_table_info */
+            } /* converting_temp_table_to_myisam */
+          },
+          {
+            "filesort_information": [
+              {
+                "direction": "asc",
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "field": "uniq"
+              }
+            ] /* filesort_information */,
+            "filesort_priority_queue_optimization": {
+              "usable": false,
+              "cause": "not applicable (no LIMIT)"
+            } /* filesort_priority_queue_optimization */,
+            "filesort_execution": [
+            ] /* filesort_execution */,
+            "filesort_summary": {
+              "rows": 16,
+              "examined_rows": 16,
+              "number_of_tmp_files": 0,
+              "sort_buffer_size": 676,
+              "sort_mode": "<sort_key, rowid>"
+            } /* filesort_summary */
+          }
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0	0
+SET GLOBAL tmp_table_size= @old_size;
+DROP TABLE t1, tmp;

=== added file 'mysql-test/suite/opt_trace/t/temp_table.test'
--- a/mysql-test/suite/opt_trace/t/temp_table.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/opt_trace/t/temp_table.test	2011-11-18 12:17:45 +0000
@@ -0,0 +1,48 @@
+--source include/have_optimizer_trace.inc
+--source include/have_64bit.inc
+
+if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL
+            + $VIEW_PROTOCOL > 0`)
+{
+   --skip Need normal protocol
+}
+
+SET optimizer_trace_max_mem_size=1048576; # 1MB
+SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
+
+CREATE TABLE tmp (
+  pk INT PRIMARY KEY AUTO_INCREMENT,
+  col1 CHAR (1)
+);
+
+INSERT INTO tmp(col1) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
+
+CREATE TABLE t1 (
+  uniq VARCHAR(10),
+  col1 VARCHAR(10),
+  col2 VARCHAR(1024)
+);
+
+INSERT INTO t1 SELECT pk, col1, col1 FROM tmp;
+
+# Uses MyISAM temporary table due to long VARCHAR in GROUP BY clause. 
+SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3;
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# Uses HEAP temporary table
+SELECT uniq, col1, col2 FROM t1 GROUP BY uniq ;   
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+select @@tmp_table_size;
+SET @old_size= @@tmp_table_size;
+SET SESSION tmp_table_size= 1024;
+
+INSERT INTO t1 SELECT pk+8, col1, col1 FROM tmp;
+
+# Uses HEAP temporary table. Converts it to MyISAM due to heap size limitation
+SELECT uniq, col1, col2 FROM t1 GROUP BY uniq;   
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+SET GLOBAL tmp_table_size= @old_size;
+
+DROP TABLE t1, tmp;

=== modified file 'sql/sql_derived.cc'
--- a/sql/sql_derived.cc	2011-10-27 10:22:55 +0000
+++ b/sql/sql_derived.cc	2011-11-18 12:17:45 +0000
@@ -349,7 +349,7 @@ bool mysql_derived_create(THD *thd, LEX 
                              thd->lex->select_lex.options |
                              thd->variables.option_bits |
                              TMP_TABLE_ALL_COLUMNS),
-                             thd->variables.big_tables))
+                            thd->variables.big_tables, &thd->opt_trace))
     DBUG_RETURN(TRUE);
 
   table->file->extra(HA_EXTRA_WRITE_CACHE);

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-11-17 10:09:13 +0000
+++ b/sql/sql_select.cc	2011-11-18 12:17:45 +0000
@@ -3701,7 +3701,7 @@ JOIN::create_intermediate_table(List<Ite
   TABLE* tab= create_tmp_table(thd, &tmp_table_param, *tmp_table_fields,
                                tmp_table_group, select_distinct && !group_list,
                                save_sum_fields, select_options, tmp_rows_limit, 
-                               "");
+                               "intermediate_tmp_table");
   if (!tab)
     DBUG_RETURN(NULL);
 
@@ -17751,6 +17751,9 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
        string_total_length / string_count >= AVG_STRING_LENGTH_TO_PACK_ROWS)))
     use_packed_rows= 1;
 
+  if (!use_packed_rows)
+    share->db_create_options&= ~HA_OPTION_PACK_RECORD;
+
   share->reclength= reclength;
   {
     uint alloc_length=ALIGN_SIZE(reclength+MI_UNIQUE_HASH_LENGTH+1);
@@ -18025,7 +18028,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
   {
     if (instantiate_tmp_table(table, param->keyinfo, param->start_recinfo,
                               &param->recinfo, select_options,
-                              thd->variables.big_tables))
+                              thd->variables.big_tables, &thd->opt_trace))
       goto err;
   }
 
@@ -18338,7 +18341,8 @@ TABLE *create_duplicate_weedout_tmp_tabl
   share->db_record_offset= 1;
   if (share->db_type() == myisam_hton)
     recinfo++;
-  if (instantiate_tmp_table(table, keyinfo, start_recinfo, &recinfo, 0, 0))
+  if (instantiate_tmp_table(table, keyinfo, start_recinfo, &recinfo, 
+                            0, 0, &thd->opt_trace))
     goto err;
 
   sjtbl->start_recinfo= start_recinfo;
@@ -18654,6 +18658,33 @@ bool create_myisam_tmp_table(TABLE *tabl
 }
 
 
+void trace_tmp_table(Opt_trace_context *trace, const TABLE *table)
+{
+  Opt_trace_object trace_tmp(trace, "temporary_table_info");
+  trace_tmp.add_utf8_table(table);
+
+  trace_tmp.add("row_length",table->s->reclength).
+    add("key_length", table->s->key_info ? 
+        table->s->key_info->key_length : 
+        0).
+    add("unique_constraint", table->s->uniques ? true : false);
+
+  if (table->s->db_type() == myisam_hton)
+  {
+    trace_tmp.add_alnum("location", "disk (MyISAM)");
+    if (table->s->db_create_options & HA_OPTION_PACK_RECORD)
+      trace_tmp.add_alnum("record_format", "packed");
+    else 
+      trace_tmp.add_alnum("record_format", "fixed");
+  }
+  else
+  {
+    DBUG_ASSERT(table->s->db_type() == heap_hton);
+    trace_tmp.add_alnum("location", "memory (heap)").
+      add("row_limit_estimate", table->s->max_rows);
+  }
+}
+
 /**
   @brief
   Instantiates temporary table
@@ -18664,6 +18695,7 @@ bool create_myisam_tmp_table(TABLE *tabl
   @param  start_recinfo   Column descriptions
   @param  recinfo INOUT   End of column descriptions
   @param  options         Option bits
+  @param  trace           Optimizer trace to write info to
 
   @details
     Creates tmp table and opens it.
@@ -18676,7 +18708,8 @@ bool create_myisam_tmp_table(TABLE *tabl
 bool instantiate_tmp_table(TABLE *table, KEY *keyinfo, 
                            MI_COLUMNDEF *start_recinfo,
                            MI_COLUMNDEF **recinfo, 
-                           ulonglong options, my_bool big_tables)
+                           ulonglong options, my_bool big_tables,
+                           Opt_trace_context *trace)
 {
   if (table->s->db_type() == myisam_hton)
   {
@@ -18688,6 +18721,13 @@ bool instantiate_tmp_table(TABLE *table,
   }
   if (open_tmp_table(table))
     return TRUE;
+
+  if (unlikely(trace->is_started()))
+  {
+    Opt_trace_object wrapper(trace);
+    Opt_trace_object convert(trace, "creating_tmp_table");
+    trace_tmp_table(trace, table);
+  }
   return FALSE;
 }
 
@@ -18809,6 +18849,18 @@ bool create_myisam_from_heap(THD *thd, T
     goto err2;
   if (open_tmp_table(&new_table))
     goto err1;
+
+
+  if (unlikely(thd->opt_trace.is_started()))
+  {
+    Opt_trace_context * trace= &thd->opt_trace;
+    Opt_trace_object wrapper(trace);
+    Opt_trace_object convert(trace, "converting_temp_table_to_myisam");
+    DBUG_ASSERT(error == HA_ERR_RECORD_FILE_FULL);
+    convert.add_alnum("cause", "memory_table_size_exceeded");
+    trace_tmp_table(trace, &new_table);
+  }
+
   if (table->file->indexes_are_disabled())
     new_table.file->ha_disable_indexes(HA_KEY_SWITCH_ALL);
   table->file->ha_index_or_rnd_end();

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-11-11 12:36:01 +0000
+++ b/sql/sql_select.h	2011-11-18 12:17:45 +0000
@@ -2472,7 +2472,8 @@ bool const_expression_in_where(Item *con
 bool instantiate_tmp_table(TABLE *table, KEY *keyinfo,
                            MI_COLUMNDEF *start_recinfo,
                            MI_COLUMNDEF **recinfo,
-                           ulonglong options, my_bool big_tables);
+                           ulonglong options, my_bool big_tables,
+                           Opt_trace_context *trace);
 
 /**
   Printing the transformed query in EXPLAIN EXTENDED or optimizer trace

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (jorgen.loland:3624 to 3625) Bug#13400713Jorgen Loland21 Nov