List:Commits« Previous MessageNext Message »
From:Marc Alff Date:November 18 2011 4:50pm
Subject:bzr push into mysql-trunk-wl5461 branch (marc.alff:3438 to 3440)
View as plain text  
 3440 Marc Alff	2011-11-18
      Fixed more code review comments

    modified:
      mysql-test/suite/perfschema/r/start_server_off.result
      mysql-test/suite/perfschema/t/start_server_off.test
      sql/mysqld.cc
      storage/perfschema/pfs_events_stages.cc
      storage/perfschema/pfs_events_statements.cc
      storage/perfschema/pfs_events_waits.cc
      storage/perfschema/table_setup_instruments.cc
 3439 Marc Alff	2011-11-18 [merge]
      Merge mysql-trunk --> mysql-trunk-wl5461

    removed:
      mysql-test/r/disabled_partition.require
      mysql-test/r/not_partition.require
    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/r/not_partition.result
      mysql-test/r/partition_disabled.result
      mysql-test/r/partition_myisam.result
      mysql-test/suite/innodb_fts/r/innodb_fts_large_records.result
      mysql-test/suite/innodb_fts/t/innodb_fts_large_records.test
      mysql-test/suite/opt_trace/include/general.inc
      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
      mysql-test/t/not_partition.test
      mysql-test/t/partition_disabled.test
      mysql-test/t/partition_myisam.test
      sql/sql_derived.cc
      sql/sql_partition.cc
      sql/sql_select.cc
      sql/sql_select.h
      sql/sql_yacc.yy
 3438 Christopher Powers	2011-11-17
      WL#5461 PERFORMANCE_SCHEMA MY.CNF Default Setup
      
      Update mysqld--help-notwin testcase

    modified:
      mysql-test/r/mysqld--help-notwin.result
=== removed file 'mysql-test/r/disabled_partition.require'
--- a/mysql-test/r/disabled_partition.require	2009-01-08 14:16:44 +0000
+++ b/mysql-test/r/disabled_partition.require	1970-01-01 00:00:00 +0000
@@ -1,2 +0,0 @@
-Variable_name	Value
-have_partitioning	DISABLED

=== 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

=== removed file 'mysql-test/r/not_partition.require'
--- a/mysql-test/r/not_partition.require	2006-10-26 17:11:09 +0000
+++ b/mysql-test/r/not_partition.require	1970-01-01 00:00:00 +0000
@@ -1,2 +0,0 @@
-Variable_name	Value
-have_partitioning	NO

=== modified file 'mysql-test/r/not_partition.result'
--- a/mysql-test/r/not_partition.result	2010-10-05 10:31:55 +0000
+++ b/mysql-test/r/not_partition.result	2011-11-18 06:06:29 +0000
@@ -85,3 +85,18 @@ explain partitions select * from t1 wher
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	Using where
 drop table t1;
+#
+# bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED
+#                                 TABLE CORRUPTS MYISAM
+DROP TABLE if exists `t1`;
+CREATE TABLE  `t1`(`a` INT)ENGINE=myisam;
+ALTER TABLE `t1` ADD COLUMN `b` INT;
+CREATE UNIQUE INDEX `i1` ON `t1`(`b`);
+CREATE UNIQUE INDEX `i2` ON `t1`(`a`);
+ALTER TABLE `t1` ADD PRIMARY KEY  (`a`);
+ALTER TABLE `t1` REMOVE PARTITIONING;
+ERROR HY000: The 'partitioning' feature is disabled; you need MySQL built with '--with-plugin-partition' to have it working
+CHECK TABLE `t1` EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+DROP TABLE t1;

=== modified file 'mysql-test/r/partition_disabled.result'
--- a/mysql-test/r/partition_disabled.result	2010-08-30 06:38:09 +0000
+++ b/mysql-test/r/partition_disabled.result	2011-11-18 06:06:29 +0000
@@ -91,3 +91,18 @@ explain partitions select * from t1 wher
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	Using where
 drop table t1;
+#
+# bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED
+#                                 TABLE CORRUPTS MYISAM
+DROP TABLE if exists `t1`;
+CREATE TABLE  `t1`(`a` INT)ENGINE=myisam;
+ALTER TABLE `t1` ADD COLUMN `b` INT;
+CREATE UNIQUE INDEX `i1` ON `t1`(`b`);
+CREATE UNIQUE INDEX `i2` ON `t1`(`a`);
+ALTER TABLE `t1` ADD PRIMARY KEY  (`a`);
+ALTER TABLE `t1` REMOVE PARTITIONING;
+ERROR HY000: The MySQL server is running with the --skip-partition option so it cannot execute this statement
+CHECK TABLE `t1` EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+DROP TABLE t1;

=== modified file 'mysql-test/r/partition_myisam.result'
--- a/mysql-test/r/partition_myisam.result	2011-10-07 14:20:21 +0000
+++ b/mysql-test/r/partition_myisam.result	2011-11-18 06:06:29 +0000
@@ -327,3 +327,18 @@ PARTITION p1 VALUES LESS THAN (100) MAX_
 PARTITION pMax VALUES LESS THAN MAXVALUE);
 INSERT INTO t1 VALUES (1, "Partition p1, first row");
 DROP TABLE t1;
+#
+# bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED
+#                                 TABLE CORRUPTS MYISAM
+DROP TABLE if exists `t1`;
+CREATE TABLE  `t1`(`a` INT)ENGINE=myisam;
+ALTER TABLE `t1` ADD COLUMN `b` INT;
+CREATE UNIQUE INDEX `i1` ON `t1`(`b`);
+CREATE UNIQUE INDEX `i2` ON `t1`(`a`);
+ALTER TABLE `t1` ADD PRIMARY KEY  (`a`);
+ALTER TABLE `t1` REMOVE PARTITIONING;
+ERROR HY000: Partition management on a not partitioned table is not possible
+CHECK TABLE `t1` EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+DROP TABLE t1;

=== modified file 'mysql-test/suite/innodb_fts/r/innodb_fts_large_records.result'
--- a/mysql-test/suite/innodb_fts/r/innodb_fts_large_records.result	2011-09-09 08:27:24 +0000
+++ b/mysql-test/suite/innodb_fts/r/innodb_fts_large_records.result	2011-11-18 14:16:55 +0000
@@ -1,70 +1,76 @@
-drop table if exists t1;
+DROP TABLE IF EXISTS t1;
 CREATE TABLE t1 (
 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 
 a TEXT,
 b TEXT
 ) ENGINE = InnoDB;
-create unique index FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
+CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
 "Loading data using LOAD DATA Command , File <MYSQLTEST_VARDIR>/tmp/fts_input_data1.txt"
 CREATE FULLTEXT INDEX idx on t1 (a,b);
 SELECT COUNT(*) FROM t1;
 COUNT(*)
 399
-SELECT * FROM t1 WHERE MATCH (a,b)
+SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
 AGAINST ('row35col2word49' IN NATURAL LANGUAGE MODE);
-FTS_DOC_ID	a	b
-35	row35col1word1 row35col1word2 row35col1word3 row35col1word4 row35col1word5 row35col1word6 row35col1word7 row35col1word8 row35col1word9 row35col1word10 row35col1word11 row35col1word12 row35col1word13 row35col1word14 row35col1word15 row35col1word16 row35col1word17 row35col1word18 row35col1word19 row35col1word20 row35col1word21 row35col1word22 row35col1word23 row35col1word24 row35col1word25 row35col1word26 row35col1word27 row35col1word28 row35col1word29 row35col1word30 row35col1word31 row35col1word32 row35col1word33 row35col1word34 row35col1word35 row35col1word36 row35col1word37 row35col1word38 row35col1word39 row35col1word40 row35col1word41 row35col1word42 row35col1word43 row35col1word44 row35col1word45 row35col1word46 row35col1word47 row35col1word48 row35col1word49 row35col1word50 	row35col2word1 row35col2word2 row35col2word3 row35col2word4 row35col2word5 row35col2word6 row35col2word7 row35col2word8 row35col2word9 row35col2word10 row35col2word11 row35col2word12 row35col2word13 row35col2word14 row35col2word15 row35col2word16 row35col2word17 row35col2word18 row35col2word19 row35col2word20 row35col2word21 row35col2word22 row35col2word23 row35col2word24 row35col2word25 row35col2word26 row35col2word27 row35col2word28 row35col2word29 row35col2word30 row35col2word31 row35col2word32 row35col2word33 row35col2word34 row35col2word35 row35col2word36 row35col2word37 row35col2word38 row35col2word39 row35col2word40 row35col2word41 row35col2word42 row35col2word43 row35col2word44 row35col2word45 row35col2word46 row35col2word47 row35col2word48 row35col2word49 row35col2word50 
-select count(*) from t1 where MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
-count(*)
+FTS_DOC_ID
+35
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH(a,b) AGAINST("+row5col2word49" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row5col2word49" IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH(a,b) AGAINST("+row35col2word49 +(row35col1word49 row35col2word40)" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row35col2word49 +(row35col1word49 row35col2word40)" IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH(a,b) AGAINST("+row35col2word49 -(row45col2word49)" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row35col2word49 -(row45col2word49)" IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH(a,b) AGAINST("row5col2word49 row5col2word40" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("row5col2word49 row5col2word40" IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH a,b AGAINST ("+row5col2word* +row5col1word49*" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ("+row5col2word* +row5col1word49*" IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH a,b AGAINST ('"row35col2word49"' IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ('"row35col2word49"' IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH a,b AGAINST ('"ROW35col2WORD49"' IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ('"ROW35col2WORD49"' IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH(a,b) AGAINST ("row5col2word49" WITH QUERY EXPANSION);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST ("row5col2word49" WITH QUERY EXPANSION);
+COUNT(*)
 1
-SELECT * FROM t1
+SELECT FTS_DOC_ID FROM t1
 WHERE MATCH (a,b)
 AGAINST ('"row5col2word48 row5col2word49"@20' IN BOOLEAN MODE);
-FTS_DOC_ID	a	b
-5	row5col1word1 row5col1word2 row5col1word3 row5col1word4 row5col1word5 row5col1word6 row5col1word7 row5col1word8 row5col1word9 row5col1word10 row5col1word11 row5col1word12 row5col1word13 row5col1word14 row5col1word15 row5col1word16 row5col1word17 row5col1word18 row5col1word19 row5col1word20 row5col1word21 row5col1word22 row5col1word23 row5col1word24 row5col1word25 row5col1word26 row5col1word27 row5col1word28 row5col1word29 row5col1word30 row5col1word31 row5col1word32 row5col1word33 row5col1word34 row5col1word35 row5col1word36 row5col1word37 row5col1word38 row5col1word39 row5col1word40 row5col1word41 row5col1word42 row5col1word43 row5col1word44 row5col1word45 row5col1word46 row5col1word47 row5col1word48 row5col1word49 row5col1word50 	row5col2word1 row5col2word2 row5col2word3 row5col2word4 row5col2word5 row5col2word6 row5col2word7 row5col2word8 row5col2word9 row5col2word10 row5col2word11 row5col2word12 row5col2word13 row5col2word14 row5col2word15 row5col2word16 row5col2word17 row5col2word18 row5col2word19 row5col2word20 row5col2word21 row5col2word22 row5col2word23 row5col2word24 row5col2word25 row5col2word26 row5col2word27 row5col2word28 row5col2word29 row5col2word30 row5col2word31 row5col2word32 row5col2word33 row5col2word34 row5col2word35 row5col2word36 row5col2word37 row5col2word38 row5col2word39 row5col2word40 row5col2word41 row5col2word42 row5col2word43 row5col2word44 row5col2word45 row5col2word46 row5col2word47 row5col2word48 row5col2word49 row5col2word50 
+FTS_DOC_ID
+5
 SELECT * FROM t1
 WHERE MATCH (a,b)
 AGAINST ('"row5col2word48 row5col2word49"@10' IN BOOLEAN MODE);
 FTS_DOC_ID	a	b
 UPDATE t1 SET a = "using update" , b = "changing fulltext index record", FTS_DOC_ID = FTS_DOC_ID + 10000 
 WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
-select sleep(1);
-sleep(1)
-0
-select a,b from t1 where MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
+SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
 a	b
-select a,b from t1 where MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
+SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
 a	b
 using update	changing fulltext index record
-select a,b from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
 a	b
 using update	changing fulltext index record
 DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
-select a,b from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
 a	b
 DROP TABLE t1;
 CREATE TABLE t1 (
@@ -72,70 +78,78 @@ FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMEN
 a TEXT,
 b TEXT
 ) ENGINE = InnoDB;
-create unique index FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
+CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
 "Loading data using LOAD DATA Command , File <MYSQLTEST_VARDIR>/tmp/fts_input_data2.txt"
 CREATE FULLTEXT INDEX idx on t1 (a,b);
 SELECT COUNT(*) FROM t1;
 COUNT(*)
 5000
-SELECT a,b from t1 where b like '%row300col2word30%';
-a	b
-row300col1word1 row300col1word2 row300col1word3 row300col1word4 row300col1word5 row300col1word6 row300col1word7 row300col1word8 row300col1word9 row300col1word10 row300col1word11 row300col1word12 row300col1word13 row300col1word14 row300col1word15 row300col1word16 row300col1word17 row300col1word18 row300col1word19 row300col1word20 row300col1word21 row300col1word22 row300col1word23 row300col1word24 row300col1word25 row300col1word26 row300col1word27 row300col1word28 row300col1word29 row300col1word30 row300col1word31 row300col1word32 row300col1word33 row300col1word34 row300col1word35 row300col1word36 row300col1word37 row300col1word38 row300col1word39 row300col1word40 row300col1word41 row300col1word42 row300col1word43 row300col1word44 row300col1word45 row300col1word46 row300col1word47 row300col1word48 row300col1word49 	row300col2word1 row300col2word2 row300col2word3 row300col2word4 row300col2word5 row300col2word6 row300col2word7 row300col2word8 row300col2word9 row300col2word10 row300col2word11 row300col2word12 row300col2word13 row300col2word14 row300col2word15 row300col2word16 row300col2word17 row300col2word18 row300col2word19 row300col2word20 row300col2word21 row300col2word22 row300col2word23 row300col2word24 row300col2word25 row300col2word26 row300col2word27 row300col2word28 row300col2word29 row300col2word30 row300col2word31 row300col2word32 row300col2word33 row300col2word34 row300col2word35 row300col2word36 row300col2word37 row300col2word38 row300col2word39 row300col2word40 row300col2word41 row300col2word42 row300col2word43 row300col2word44 row300col2word45 row300col2word46 row300col2word47 row300col2word48 row300col2word49 
-SELECT * FROM t1 WHERE MATCH (a,b)
+SELECT FTS_DOC_ID from t1 WHERE b like '%row300col2word30%';
+FTS_DOC_ID
+300
+SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
 AGAINST ('row35col2word49' IN NATURAL LANGUAGE MODE);
-FTS_DOC_ID	a	b
-35	row35col1word1 row35col1word2 row35col1word3 row35col1word4 row35col1word5 row35col1word6 row35col1word7 row35col1word8 row35col1word9 row35col1word10 row35col1word11 row35col1word12 row35col1word13 row35col1word14 row35col1word15 row35col1word16 row35col1word17 row35col1word18 row35col1word19 row35col1word20 row35col1word21 row35col1word22 row35col1word23 row35col1word24 row35col1word25 row35col1word26 row35col1word27 row35col1word28 row35col1word29 row35col1word30 row35col1word31 row35col1word32 row35col1word33 row35col1word34 row35col1word35 row35col1word36 row35col1word37 row35col1word38 row35col1word39 row35col1word40 row35col1word41 row35col1word42 row35col1word43 row35col1word44 row35col1word45 row35col1word46 row35col1word47 row35col1word48 row35col1word49 	row35col2word1 row35col2word2 row35col2word3 row35col2word4 row35col2word5 row35col2word6 row35col2word7 row35col2word8 row35col2word9 row35col2word10 row35col2word11 row35col2word12 row35col2word13 row35col2word14 row35col2word15 row35col2word16 row35col2word17 row35col2word18 row35col2word19 row35col2word20 row35col2word21 row35col2word22 row35col2word23 row35col2word24 row35col2word25 row35col2word26 row35col2word27 row35col2word28 row35col2word29 row35col2word30 row35col2word31 row35col2word32 row35col2word33 row35col2word34 row35col2word35 row35col2word36 row35col2word37 row35col2word38 row35col2word39 row35col2word40 row35col2word41 row35col2word42 row35col2word43 row35col2word44 row35col2word45 row35col2word46 row35col2word47 row35col2word48 row35col2word49 
-select count(*) from t1 where MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
-count(*)
+FTS_DOC_ID
+35
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH(a,b) AGAINST("+row5col2word49" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row5col2word49" IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH(a,b) AGAINST("+row35col2word49 +(row35col1word49 row35col2word40)" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row35col2word49 +(row35col1word49 row35col2word40)" IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH(a,b) AGAINST("+row35col2word49 -(row45col2word49)" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row35col2word49 -(row45col2word49)" IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH(a,b) AGAINST("row5col2word49 row5col2word40" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("row5col2word49 row5col2word40" IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH a,b AGAINST ("+row5col2word* +row5col1word49*" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ("+row5col2word* +row5col1word49*" IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH a,b AGAINST ('"row35col2word49"' IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ('"row35col2word49"' IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH a,b AGAINST ('"ROW35col2WORD49"' IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ('"ROW35col2WORD49"' IN BOOLEAN MODE);
+COUNT(*)
 1
-select count(*) from t1 where MATCH(a,b) AGAINST ("row5col2word49" WITH QUERY EXPANSION);
-count(*)
+SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST ("row5col2word49" WITH QUERY EXPANSION);
+COUNT(*)
 1
-SELECT * FROM t1
+SELECT FTS_DOC_ID FROM t1
 WHERE MATCH (a,b)
 AGAINST ('"row5col2word48 row5col2word49"@20' IN BOOLEAN MODE);
-FTS_DOC_ID	a	b
-5	row5col1word1 row5col1word2 row5col1word3 row5col1word4 row5col1word5 row5col1word6 row5col1word7 row5col1word8 row5col1word9 row5col1word10 row5col1word11 row5col1word12 row5col1word13 row5col1word14 row5col1word15 row5col1word16 row5col1word17 row5col1word18 row5col1word19 row5col1word20 row5col1word21 row5col1word22 row5col1word23 row5col1word24 row5col1word25 row5col1word26 row5col1word27 row5col1word28 row5col1word29 row5col1word30 row5col1word31 row5col1word32 row5col1word33 row5col1word34 row5col1word35 row5col1word36 row5col1word37 row5col1word38 row5col1word39 row5col1word40 row5col1word41 row5col1word42 row5col1word43 row5col1word44 row5col1word45 row5col1word46 row5col1word47 row5col1word48 row5col1word49 	row5col2word1 row5col2word2 row5col2word3 row5col2word4 row5col2word5 row5col2word6 row5col2word7 row5col2word8 row5col2word9 row5col2word10 row5col2word11 row5col2word12 row5col2word13 row5col2word14 row5col2word15 row5col2word16 row5col2word17 row5col2word18 row5col2word19 row5col2word20 row5col2word21 row5col2word22 row5col2word23 row5col2word24 row5col2word25 row5col2word26 row5col2word27 row5col2word28 row5col2word29 row5col2word30 row5col2word31 row5col2word32 row5col2word33 row5col2word34 row5col2word35 row5col2word36 row5col2word37 row5col2word38 row5col2word39 row5col2word40 row5col2word41 row5col2word42 row5col2word43 row5col2word44 row5col2word45 row5col2word46 row5col2word47 row5col2word48 row5col2word49 
+FTS_DOC_ID
+5
 SELECT * FROM t1
 WHERE MATCH (a,b)
 AGAINST ('"row5col2word48 row5col2word49"@10' IN BOOLEAN MODE);
 FTS_DOC_ID	a	b
 UPDATE t1 SET a = "using update" , b = "changing fulltext index record", FTS_DOC_ID = FTS_DOC_ID + 10000
 WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
-select sleep(1);
-sleep(1)
-0
-select a,b from t1 where MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
+SELECT a,b FROM t1 
+WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
 a	b
-select a,b from t1 where MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
+SELECT a,b FROM t1 
+WHERE MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
 a	b
 using update	changing fulltext index record
-select a,b from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+SELECT a,b FROM t1
+WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
 a	b
 using update	changing fulltext index record
 DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
-select a,b from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
 a	b
 ALTER TABLE t1 DROP INDEX idx;
 CREATE FULLTEXT INDEX idx on t1 (a,b);
@@ -153,57 +167,62 @@ FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMEN
 a TEXT,
 b TEXT
 ) ENGINE = InnoDB;
-create unique index FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
+CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
 "Loading data using LOAD DATA Command , File <MYSQLTEST_VARDIR>/tmp/fts_input_data3.txt"
 CREATE FULLTEXT INDEX idx on t1 (a,b);
 SELECT COUNT(*) FROM t1;
 COUNT(*)
 5000
-SELECT count(*) from t1 where b like '%samerowword%';
-count(*)
+SELECT COUNT(*) from t1 WHERE b like '%samerowword%';
+COUNT(*)
 5000
 SELECT COUNT(*) FROM t1 WHERE MATCH (a,b)
 AGAINST ('samerowword' IN NATURAL LANGUAGE MODE);
 COUNT(*)
 5000
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword +samerowword" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword +samerowword" IN BOOLEAN MODE);
+COUNT(*)
 5000
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
+COUNT(*)
 5000
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword -(row45col2word49)" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword -(row45col2word49)" IN BOOLEAN MODE);
+COUNT(*)
 5000
-select count(*) from t1 where MATCH a,b AGAINST ("+sameroww" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ("+sameroww" IN BOOLEAN MODE);
+COUNT(*)
 0
-select count(*) from t1 where MATCH(a,b) AGAINST ("samerowword" WITH QUERY EXPANSION);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST ("samerowword" WITH QUERY EXPANSION);
+COUNT(*)
 5000
-UPDATE t1 SET a = "using update" , b = "changing fulltext index record", FTS_DOC_ID = FTS_DOC_ID + 10000 
+UPDATE t1 SET a = "using update" , b = "changing fulltext index record", 
+FTS_DOC_ID = FTS_DOC_ID + 10000 
 WHERE MATCH(a,b) AGAINST("+samerowword +samerowword" IN BOOLEAN MODE);
-select sleep(1);
-sleep(1)
-0
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword +samerowword" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword +samerowword" IN BOOLEAN MODE);
+COUNT(*)
 0
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
+COUNT(*)
 0
-select count(*) from t1 where MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
+COUNT(*)
 5000
-select count(*) from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+COUNT(*)
 5000
 DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
-select sleep(1);
-sleep(1)
-0
-select count(*) from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+COUNT(*)
 0
 ALTER TABLE t1 DROP INDEX idx;
 CREATE FULLTEXT INDEX idx on t1 (a,b);
@@ -221,54 +240,58 @@ FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMEN
 a TEXT,
 b TEXT
 ) ENGINE = InnoDB;
-create unique index FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
+CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
 "Loading data using LOAD DATA Command , File <MYSQLTEST_VARDIR>/tmp/fts_input_data4.txt"
 CREATE FULLTEXT INDEX idx on t1 (a,b);
 SELECT COUNT(*) FROM t1;
 COUNT(*)
 5000
-SELECT count(*) from t1 where a like '%samerowword%';
-count(*)
+SELECT COUNT(*) from t1 WHERE a like '%samerowword%';
+COUNT(*)
 5000
 SELECT COUNT(*) FROM t1 WHERE MATCH (a,b)
 AGAINST ('samerowword' IN NATURAL LANGUAGE MODE);
 COUNT(*)
 5000
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword +1050" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST("+samerowword +1050" IN BOOLEAN MODE);
+COUNT(*)
 5000
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
+COUNT(*)
 5000
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword -(1050)" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST("+samerowword -(1050)" IN BOOLEAN MODE);
+COUNT(*)
 0
-select count(*) from t1 where MATCH a,b AGAINST ("+2001" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 WHERE MATCH a,b AGAINST ("+2001" IN BOOLEAN MODE);
+COUNT(*)
 0
-select count(*) from t1 where MATCH(a,b) AGAINST ("samerowword" WITH QUERY EXPANSION);
-count(*)
+SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST ("samerowword" WITH QUERY EXPANSION);
+COUNT(*)
 5000
-UPDATE t1 SET a = "using update" , b = "changing fulltext index record", FTS_DOC_ID = FTS_DOC_ID + 10000 
+UPDATE t1 SET a = "using update" , b = "changing fulltext index record", 
+FTS_DOC_ID = FTS_DOC_ID + 10000 
 WHERE MATCH(a,b) AGAINST("+samerowword +1050" IN BOOLEAN MODE);
-select sleep(1);
-sleep(1)
-0
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword +1050" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword +1050" IN BOOLEAN MODE);
+COUNT(*)
 0
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
+COUNT(*)
 0
-select count(*) from t1 where MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
+COUNT(*)
 5000
-select count(*) from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
-count(*)
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+COUNT(*)
 5000
-DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
-count(*)
+DELETE FROM t1 
+WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+COUNT(*)
 0
 ALTER TABLE t1 DROP INDEX idx;
 CREATE FULLTEXT INDEX idx on t1 (a,b);

=== modified file 'mysql-test/suite/innodb_fts/t/innodb_fts_large_records.test'
--- a/mysql-test/suite/innodb_fts/t/innodb_fts_large_records.test	2011-11-17 11:02:53 +0000
+++ b/mysql-test/suite/innodb_fts/t/innodb_fts_large_records.test	2011-11-18 14:16:55 +0000
@@ -1,12 +1,10 @@
---source include/not_windows.inc
---source include/big_test.inc
-
 # This test for FTS index with big records
 # case a) more words in single record
 # b) more words across records
 
+--SOURCE include/big_test.inc
 --disable_warnings
-drop table if exists t1;
+DROP TABLE IF EXISTS t1;
 --enable_warnings
 
 # Create FTS table
@@ -16,12 +14,12 @@ EVAL CREATE TABLE t1 (
         b TEXT
         ) ENGINE = InnoDB;
 
-create unique index FTS_DOC_ID_INDEX on t1(FTS_DOC_ID); 
-
+CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
 
 let $counter = 1;
 --disable_query_log
 
+# Generate input file using perl
 perl;
 use strict;
 my $fname= "$ENV{'MYSQLTEST_VARDIR'}/tmp/fts_input_data1.txt";
@@ -41,7 +39,8 @@ while ($record_counter < 400) {
 close FH;
 EOF
 
-EVAL LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/fts_input_data1.txt' INTO TABLE t1 FIELDS TERMINATED BY ',' (a,b);
+EVAL LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/fts_input_data1.txt' INTO 
+TABLE t1 FIELDS TERMINATED BY ',' (a,b);
 --enable_query_log
 --echo "Loading data using LOAD DATA Command , File <MYSQLTEST_VARDIR>/tmp/fts_input_data1.txt"
 
@@ -51,23 +50,32 @@ CREATE FULLTEXT INDEX idx on t1 (a,b);
 SELECT COUNT(*) FROM t1;
 
 # Select word "tutorial" in the table
-SELECT * FROM t1 WHERE MATCH (a,b)
+SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
         AGAINST ('row35col2word49' IN NATURAL LANGUAGE MODE);
 
 # boolean mode
-select count(*) from t1 where MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+row5col2word49" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+row35col2word49 +(row35col1word49 row35col2word40)" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+row35col2word49 -(row45col2word49)" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("row5col2word49 row5col2word40" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH a,b AGAINST ("+row5col2word* +row5col1word49*" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH a,b AGAINST ('"row35col2word49"' IN BOOLEAN MODE);
-select count(*) from t1 where MATCH a,b AGAINST ('"ROW35col2WORD49"' IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row5col2word49" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row35col2word49 +(row35col1word49 row35col2word40)" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row35col2word49 -(row45col2word49)" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("row5col2word49 row5col2word40" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ("+row5col2word* +row5col1word49*" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ('"row35col2word49"' IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ('"ROW35col2WORD49"' IN BOOLEAN MODE);
 
 # query expansion
-select count(*) from t1 where MATCH(a,b) AGAINST ("row5col2word49" WITH QUERY EXPANSION);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST ("row5col2word49" WITH QUERY EXPANSION);
 
-SELECT * FROM t1
+SELECT FTS_DOC_ID FROM t1
         WHERE MATCH (a,b)
         AGAINST ('"row5col2word48 row5col2word49"@20' IN BOOLEAN MODE);
 
@@ -78,19 +86,16 @@ SELECT * FROM t1
 UPDATE t1 SET a = "using update" , b = "changing fulltext index record", FTS_DOC_ID = FTS_DOC_ID + 10000 
 WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
 
-select sleep(1);
-
-select a,b from t1 where MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
-select a,b from t1 where MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
-select a,b from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
+SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
+SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
 
 DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
-select a,b from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
 
 --remove_file '$MYSQLTEST_VARDIR/tmp/fts_input_data1.txt';
 DROP TABLE t1;
 
-
 #--------------------------------------------------------------------------------------------
 # Create FTS table
 EVAL CREATE TABLE t1 (
@@ -99,12 +104,12 @@ EVAL CREATE TABLE t1 (
         b TEXT
         ) ENGINE = InnoDB;
 
-create unique index FTS_DOC_ID_INDEX on t1(FTS_DOC_ID); 
-
+CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID); 
 
 let $counter = 1;
 --disable_query_log
 
+# Generate input file using perl
 perl;
 use strict;
 my $fname= "$ENV{'MYSQLTEST_VARDIR'}/tmp/fts_input_data2.txt";
@@ -124,8 +129,8 @@ while ($record_counter < 5001) {
 close FH;
 EOF
 
-
-EVAL LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/fts_input_data2.txt' INTO TABLE t1 FIELDS TERMINATED BY ',' (a,b);
+EVAL LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/fts_input_data2.txt' 
+INTO TABLE t1 FIELDS TERMINATED BY ',' (a,b);
 --enable_query_log
 --echo "Loading data using LOAD DATA Command , File <MYSQLTEST_VARDIR>/tmp/fts_input_data2.txt"
 
@@ -134,25 +139,33 @@ CREATE FULLTEXT INDEX idx on t1 (a,b);
 
 SELECT COUNT(*) FROM t1;
 
-SELECT a,b from t1 where b like '%row300col2word30%';
+SELECT FTS_DOC_ID from t1 WHERE b like '%row300col2word30%';
 
-SELECT * FROM t1 WHERE MATCH (a,b)
+SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
         AGAINST ('row35col2word49' IN NATURAL LANGUAGE MODE);
 
 # boolean mode
-select count(*) from t1 where MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+row5col2word49" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+row35col2word49 +(row35col1word49 row35col2word40)" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+row35col2word49 -(row45col2word49)" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("row5col2word49 row5col2word40" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH a,b AGAINST ("+row5col2word* +row5col1word49*" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH a,b AGAINST ('"row35col2word49"' IN BOOLEAN MODE);
-select count(*) from t1 where MATCH a,b AGAINST ('"ROW35col2WORD49"' IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row5col2word49" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row35col2word49 +(row35col1word49 row35col2word40)" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+row35col2word49 -(row45col2word49)" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("row5col2word49 row5col2word40" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ("+row5col2word* +row5col1word49*" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ('"row35col2word49"' IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ('"ROW35col2WORD49"' IN BOOLEAN MODE);
 
 # query expansion
-select count(*) from t1 where MATCH(a,b) AGAINST ("row5col2word49" WITH QUERY EXPANSION);
+SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST ("row5col2word49" WITH QUERY EXPANSION);
 
-SELECT * FROM t1
+SELECT FTS_DOC_ID FROM t1
         WHERE MATCH (a,b)
         AGAINST ('"row5col2word48 row5col2word49"@20' IN BOOLEAN MODE);
 
@@ -163,14 +176,15 @@ SELECT * FROM t1
 UPDATE t1 SET a = "using update" , b = "changing fulltext index record", FTS_DOC_ID = FTS_DOC_ID + 10000
 WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
 
-select sleep(1);
-
-select a,b from t1 where MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
-select a,b from t1 where MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
-select a,b from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+SELECT a,b FROM t1 
+WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
+SELECT a,b FROM t1 
+WHERE MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
+SELECT a,b FROM t1
+WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
 
 DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
-select a,b from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
 
 ALTER TABLE t1 DROP INDEX idx;
 # Create the FTS index again
@@ -184,7 +198,6 @@ SELECT COUNT(*) FROM t1 WHERE a IS NULL
 --remove_file '$MYSQLTEST_VARDIR/tmp/fts_input_data2.txt';
 DROP TABLE t1;
 
-
 #--------------------------------------------------------------------------------------------
 # Create FTS table
 EVAL CREATE TABLE t1 (
@@ -193,11 +206,12 @@ EVAL CREATE TABLE t1 (
         b TEXT
         ) ENGINE = InnoDB;
 
-create unique index FTS_DOC_ID_INDEX on t1(FTS_DOC_ID); 
+CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID); 
 
 let $counter = 1;
 --disable_query_log
 
+# Generate input file using perl
 perl;
 use strict;
 my $fname= "$ENV{'MYSQLTEST_VARDIR'}/tmp/fts_input_data3.txt";
@@ -217,8 +231,8 @@ while ($record_counter < 5001) {
 close FH;
 EOF
 
-
-EVAL LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/fts_input_data3.txt' INTO TABLE t1 FIELDS TERMINATED BY ',' (a,b);
+EVAL LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/fts_input_data3.txt' 
+INTO TABLE t1 FIELDS TERMINATED BY ',' (a,b);
 --enable_query_log
 --echo "Loading data using LOAD DATA Command , File <MYSQLTEST_VARDIR>/tmp/fts_input_data3.txt"
 
@@ -227,35 +241,42 @@ CREATE FULLTEXT INDEX idx on t1 (a,b);
 
 SELECT COUNT(*) FROM t1;
 
-SELECT count(*) from t1 where b like '%samerowword%';
+SELECT COUNT(*) from t1 WHERE b like '%samerowword%';
 
 SELECT COUNT(*) FROM t1 WHERE MATCH (a,b)
         AGAINST ('samerowword' IN NATURAL LANGUAGE MODE);
 
 # boolean mode
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword +samerowword" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword -(row45col2word49)" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH a,b AGAINST ("+sameroww" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword +samerowword" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword -(row45col2word49)" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH a,b AGAINST ("+sameroww" IN BOOLEAN MODE);
 
 # query expansion
-select count(*) from t1 where MATCH(a,b) AGAINST ("samerowword" WITH QUERY EXPANSION);
-
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST ("samerowword" WITH QUERY EXPANSION);
 
-UPDATE t1 SET a = "using update" , b = "changing fulltext index record", FTS_DOC_ID = FTS_DOC_ID + 10000 
+UPDATE t1 SET a = "using update" , b = "changing fulltext index record", 
+FTS_DOC_ID = FTS_DOC_ID + 10000 
 WHERE MATCH(a,b) AGAINST("+samerowword +samerowword" IN BOOLEAN MODE);
 
-select sleep(1);
-
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword +samerowword" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword +samerowword" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
 
 DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
-select sleep(1);
 
-select count(*) from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
 
 ALTER TABLE t1 DROP INDEX idx;
 # Create the FTS index again
@@ -269,7 +290,6 @@ SELECT COUNT(*) FROM t1 WHERE a IS NULL
 --remove_file '$MYSQLTEST_VARDIR/tmp/fts_input_data3.txt';
 DROP TABLE t1;
 
-
 #--------------------------------------------------------------------------------------------
 # Create FTS with same word and numbers
 EVAL CREATE TABLE t1 (
@@ -278,12 +298,13 @@ EVAL CREATE TABLE t1 (
         b TEXT
         ) ENGINE = InnoDB;
 
-create unique index FTS_DOC_ID_INDEX on t1(FTS_DOC_ID); 
+CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID); 
 
 
 let $counter = 1;
 --disable_query_log
 
+# Generate input file using perl
 perl;
 use strict;
 my $fname= "$ENV{'MYSQLTEST_VARDIR'}/tmp/fts_input_data4.txt";
@@ -303,8 +324,8 @@ while ($record_counter < 5001) {
 close FH;
 EOF
 
-
-EVAL LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/fts_input_data4.txt' INTO TABLE t1 FIELDS TERMINATED BY ',' (a,b);
+EVAL LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/fts_input_data4.txt' 
+INTO TABLE t1 FIELDS TERMINATED BY ',' (a,b);
 --enable_query_log
 --echo "Loading data using LOAD DATA Command , File <MYSQLTEST_VARDIR>/tmp/fts_input_data4.txt"
 
@@ -313,33 +334,37 @@ CREATE FULLTEXT INDEX idx on t1 (a,b);
 
 SELECT COUNT(*) FROM t1;
 
-SELECT count(*) from t1 where a like '%samerowword%';
+SELECT COUNT(*) from t1 WHERE a like '%samerowword%';
 
 SELECT COUNT(*) FROM t1 WHERE MATCH (a,b)
         AGAINST ('samerowword' IN NATURAL LANGUAGE MODE);
 
 # boolean mode
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword +1050" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword -(1050)" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH a,b AGAINST ("+2001" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST("+samerowword +1050" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST("+samerowword -(1050)" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 WHERE MATCH a,b AGAINST ("+2001" IN BOOLEAN MODE);
 
 # query expansion
-select count(*) from t1 where MATCH(a,b) AGAINST ("samerowword" WITH QUERY EXPANSION);
+SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST ("samerowword" WITH QUERY EXPANSION);
 
-
-UPDATE t1 SET a = "using update" , b = "changing fulltext index record", FTS_DOC_ID = FTS_DOC_ID + 10000 
+UPDATE t1 SET a = "using update" , b = "changing fulltext index record", 
+FTS_DOC_ID = FTS_DOC_ID + 10000 
 WHERE MATCH(a,b) AGAINST("+samerowword +1050" IN BOOLEAN MODE);
 
-select sleep(1);
-
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword +1050" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
-
-DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
-select count(*) from t1 where MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword +1050" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+
+DELETE FROM t1 
+WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
+SELECT COUNT(*) from t1 
+WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
 
 ALTER TABLE t1 DROP INDEX idx;
 # Create the FTS index again

=== modified file 'mysql-test/suite/opt_trace/include/general.inc'
--- a/mysql-test/suite/opt_trace/include/general.inc	2011-11-08 07:51:49 +0000
+++ b/mysql-test/suite/opt_trace/include/general.inc	2011-11-18 14:51:40 +0000
@@ -82,12 +82,12 @@ select * from information_schema.OPTIMIZ
 select (@query:=QUERY)+NULL, (@trace:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
 select length(@trace);
 # The concatenation of query and trace above has length:
-# - >13500 in normal mode
-# - <13500 in ps-protocol mode (because IN->EXISTS is done at PREPARE
+# - >13900 in normal mode
+# - <13900 in ps-protocol mode (because IN->EXISTS is done at PREPARE
 # and we trace only EXECUTE)
 # - So in normal mode, the lines below verify truncation,
 # whereas in ps-protocol mode they verify non-truncation.
-set optimizer_trace_max_mem_size=13500;
+set optimizer_trace_max_mem_size=13900;
 select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
 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));
 select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;

=== 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 14:51:40 +0000
@@ -233,6 +233,19 @@ select * from v1 where v1.col = 'ÁÂÃ         {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "v1",
+                "row_length": 7,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 149796
+              } /* tmp_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 14:51:40 +0000
@@ -1552,11 +1552,24 @@ SELECT * from t1 ORDER BY rand(2) LIMIT
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "tmp_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": {
+              "tmp_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": {
+              "tmp_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": {
+              "tmp_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": {
+              "tmp_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": {
+              "tmp_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": {
+              "tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "v1",
+                "row_length": 210,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4993
+              }
+            }
+          },
+          {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "v2",
+                "row_length": 210,
+                "key_length": 5,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4993
+              }
+            }
+          },
+          {
+            "creating_tmp_table": {
+              "tmp_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": {
+              "tmp_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": {
+              "tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "d1",
+                "row_length": 210,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 4993
+              }
+            }
+          },
+          {
+            "creating_tmp_table": {
+              "tmp_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 14:51:40 +0000
@@ -954,11 +954,24 @@ TRACE
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 3,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 349525
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "teeone",
+                "row_length": 16,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 65536
+              } /* tmp_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
                 {
+                  "creating_tmp_table": {
+                    "tmp_table_info": {
+                      "database": "",
+                      "table": "intermediate_tmp_table",
+                      "row_length": 22,
+                      "key_length": 5,
+                      "unique_constraint": false,
+                      "location": "memory (heap)",
+                      "row_limit_estimate": 47662
+                    } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "tmp",
+                "row_length": 10,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 104857
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 18,
+                "key_length": 4,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 58254
+              } /* tmp_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 14:51:40 +0000
@@ -972,11 +972,24 @@ TRACE
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 3,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 349525
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "teeone",
+                "row_length": 16,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 65536
+              } /* tmp_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
                 {
+                  "creating_tmp_table": {
+                    "tmp_table_info": {
+                      "database": "",
+                      "table": "intermediate_tmp_table",
+                      "row_length": 22,
+                      "key_length": 5,
+                      "unique_constraint": false,
+                      "location": "memory (heap)",
+                      "row_limit_estimate": 47662
+                    } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "tmp",
+                "row_length": 10,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 104857
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 18,
+                "key_length": 4,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 58254
+              } /* tmp_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 14:51:40 +0000
@@ -610,6 +610,19 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
             } /* join_preparation */
           },
           {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "",
+                "row_length": 9,
+                "key_length": 8,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 116508
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "",
+                "row_length": 9,
+                "key_length": 8,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 116508
+              } /* tmp_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,8 +1342,8 @@ select (@query:=QUERY)+NULL, (@trace:=TR
 NULL	NULL
 select length(@trace);
 length(@trace)
-14121
-set optimizer_trace_max_mem_size=13500;
+14553
+set optimizer_trace_max_mem_size=13900;
 select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
 length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
 1
@@ -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
+777
 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
+13776	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": {
+        "tmp_table_info": {
+          "database": "",
+          "table": "",
+          "row_length": 5,
+          "key_length": 5,
+          "unique_constraint": false,
+          "location": "memory (heap)",
+          "row_limit_estimate": 209715
+        } /* tmp_table_info */
+      } /* creating_tmp_table */
+    },
+    {
       "join_optimization": {
         "select#": 1,
         "steps": [
@@ -5817,11 +5856,24 @@ trace
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 30,
+                "key_length": 5,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 34952
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 5,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 209715
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "",
+                "row_length": 12,
+                "key_length": 7,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 87381
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "v1",
+                "row_length": 21,
+                "key_length": 16,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 49932
+              } /* tmp_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": {
+        "tmp_table_info": {
+          "database": "information_schema",
+          "table": "session_variables",
+          "row_length": 3268,
+          "key_length": 0,
+          "unique_constraint": false,
+          "location": "memory (heap)",
+          "row_limit_estimate": 320
+        } /* tmp_table_info */
+      } /* creating_tmp_table */
+    },
+    {
       "join_preparation": {
         "select#": 1,
         "steps": [
@@ -10498,6 +10602,19 @@ select TRACE into dumpfile 'MYSQLTEST_VA
       }
     },
     {
+      "creating_tmp_table": {
+        "tmp_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 14:51:40 +0000
@@ -610,6 +610,19 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
             } /* join_preparation */
           },
           {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "",
+                "row_length": 9,
+                "key_length": 8,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 116508
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "",
+                "row_length": 9,
+                "key_length": 8,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 116508
+              } /* tmp_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,8 +1322,8 @@ select (@query:=QUERY)+NULL, (@trace:=TR
 NULL	NULL
 select length(@trace);
 length(@trace)
-13343
-set optimizer_trace_max_mem_size=13500;
+13775
+set optimizer_trace_max_mem_size=13900;
 select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
 length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
 0
@@ -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
+13775	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": {
+        "tmp_table_info": {
+          "database": "",
+          "table": "",
+          "row_length": 5,
+          "key_length": 5,
+          "unique_constraint": false,
+          "location": "memory (heap)",
+          "row_limit_estimate": 209715
+        } /* tmp_table_info */
+      } /* creating_tmp_table */
+    },
+    {
       "join_optimization": {
         "select#": 1,
         "steps": [
@@ -5775,11 +5814,24 @@ trace
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 30,
+                "key_length": 5,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 34952
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 5,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 209715
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "",
+                "row_length": 12,
+                "key_length": 7,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 87381
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "v1",
+                "row_length": 21,
+                "key_length": 16,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 49932
+              } /* tmp_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": {
+        "tmp_table_info": {
+          "database": "information_schema",
+          "table": "session_variables",
+          "row_length": 3268,
+          "key_length": 0,
+          "unique_constraint": false,
+          "location": "memory (heap)",
+          "row_limit_estimate": 320
+        } /* tmp_table_info */
+      } /* creating_tmp_table */
+    },
+    {
       "join_preparation": {
         "select#": 1,
         "steps": [
@@ -10414,6 +10518,19 @@ select TRACE into dumpfile 'MYSQLTEST_VA
       }
     },
     {
+      "creating_tmp_table": {
+        "tmp_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 14:51:40 +0000
@@ -3945,6 +3945,19 @@ EXPLAIN SELECT 1 FROM
             } /* join_optimization */
           },
           {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "d2",
+                "row_length": 5,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 209715
+              } /* tmp_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
@@ -4283,6 +4296,19 @@ EXPLAIN SELECT 1 FROM
             } /* join_optimization */
           },
           {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "d2",
+                "row_length": 5,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 209715
+              } /* tmp_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 14:51:40 +0000
@@ -3945,6 +3945,19 @@ EXPLAIN SELECT 1 FROM
             } /* join_optimization */
           },
           {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "d2",
+                "row_length": 5,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 209715
+              } /* tmp_table_info */
+            } /* creating_tmp_table */
+          },
+          {
             "join_execution": {
               "select#": 2,
               "steps": [
@@ -4283,6 +4296,19 @@ EXPLAIN SELECT 1 FROM
             } /* join_optimization */
           },
           {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "d2",
+                "row_length": 5,
+                "key_length": 0,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 209715
+              } /* tmp_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 14:51:40 +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	2682	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%"	7328	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 14:51:40 +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	2682	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%"	7328	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 14:51:40 +0000
@@ -1720,6 +1720,32 @@ field4,field5,field6	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "alias2",
+                "row_length": 46,
+                "key_length": 8,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 22795
+              } /* tmp_table_info */
+            } /* creating_tmp_table */
+          },
+          {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 13,
+                "key_length": 12,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 80659
+              } /* tmp_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 14:51:40 +0000
@@ -1698,6 +1698,32 @@ field4,field5,field6	{
         "select#": 1,
         "steps": [
           {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "alias2",
+                "row_length": 46,
+                "key_length": 8,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 22795
+              } /* tmp_table_info */
+            } /* creating_tmp_table */
+          },
+          {
+            "creating_tmp_table": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 13,
+                "key_length": 12,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 80659
+              } /* tmp_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 14:51:40 +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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 1054,
+                "key_length": 1040,
+                "unique_constraint": true,
+                "location": "disk (MyISAM)",
+                "record_format": "fixed"
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 1050,
+                "key_length": 13,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 998
+              } /* tmp_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": {
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 1050,
+                "key_length": 13,
+                "unique_constraint": false,
+                "location": "memory (heap)",
+                "row_limit_estimate": 1
+              } /* tmp_table_info */
+            } /* creating_tmp_table */
+          },
+          {
+            "converting_tmp_table_to_myisam": {
+              "cause": "memory_table_size_exceeded",
+              "tmp_table_info": {
+                "database": "",
+                "table": "intermediate_tmp_table",
+                "row_length": 1050,
+                "key_length": 13,
+                "unique_constraint": false,
+                "location": "disk (MyISAM)",
+                "record_format": "fixed"
+              } /* tmp_table_info */
+            } /* converting_tmp_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 'mysql-test/suite/perfschema/r/start_server_off.result'
--- a/mysql-test/suite/perfschema/r/start_server_off.result	2011-11-01 16:33:58 +0000
+++ b/mysql-test/suite/perfschema/r/start_server_off.result	2011-11-18 16:49:29 +0000
@@ -121,3 +121,28 @@ Performance_schema_table_instances_lost
 Performance_schema_thread_classes_lost	0
 Performance_schema_thread_instances_lost	0
 Performance_schema_users_lost	0
+select * from performance_schema.setup_consumers;
+NAME	ENABLED
+events_stages_current	NO
+events_stages_history	NO
+events_stages_history_long	NO
+events_statements_current	NO
+events_statements_history	NO
+events_statements_history_long	NO
+events_waits_current	NO
+events_waits_history	NO
+events_waits_history_long	NO
+global_instrumentation	NO
+thread_instrumentation	NO
+select * from performance_schema.setup_instruments;
+NAME	ENABLED	TIMED
+select * from performance_schema.setup_actors;
+HOST	USER	ROLE
+select * from performance_schema.setup_objects;
+OBJECT_TYPE	OBJECT_SCHEMA	OBJECT_NAME	ENABLED	TIMED
+select * from performance_schema.setup_timers;
+NAME	TIMER_NAME
+idle	MICROSECOND
+wait	CYCLE
+stage	NANOSECOND
+statement	NANOSECOND

=== modified file 'mysql-test/suite/perfschema/t/start_server_off.test'
--- a/mysql-test/suite/perfschema/t/start_server_off.test	2011-10-19 21:49:22 +0000
+++ b/mysql-test/suite/perfschema/t/start_server_off.test	2011-11-18 16:49:29 +0000
@@ -8,3 +8,14 @@
 # Expecting all off / zero
 show status like "performance_schema%";
 
+# Expect all consumers disabled
+
+select * from performance_schema.setup_consumers;
+
+# Expect no instruments
+
+select * from performance_schema.setup_instruments;
+
+select * from performance_schema.setup_actors;
+select * from performance_schema.setup_objects;
+select * from performance_schema.setup_timers;

=== modified file 'mysql-test/t/not_partition.test'
--- a/mysql-test/t/not_partition.test	2010-08-16 12:53:30 +0000
+++ b/mysql-test/t/not_partition.test	2011-11-18 06:06:29 +0000
@@ -2,9 +2,9 @@
 # Run this test only when mysqld don't has partitioning (not compiled with)
 # the statements are not expected to work, just check that we
 # can't crash the server
--- require r/not_partition.require
 disable_query_log;
-show variables like "have_partitioning";
+--require r/true.require
+SELECT (COUNT(*) = 0) AS `TRUE` FROM information_schema.plugins WHERE plugin_name LIKE 'partition%';
 enable_query_log;
 --disable_warnings
 DROP TABLE IF EXISTS t1;
@@ -84,3 +84,18 @@ create table t1 (a varchar(10) charset l
 insert into t1 values (''),(' '),('a'),('a '),('a  ');
 explain partitions select * from t1 where a='a ' OR a='a';
 drop table t1;
+--echo #
+--echo # bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED
+--echo #                                 TABLE CORRUPTS MYISAM
+--disable_warnings
+DROP TABLE if exists `t1`;
+--enable_warnings
+CREATE TABLE  `t1`(`a` INT)ENGINE=myisam;
+ALTER TABLE `t1` ADD COLUMN `b` INT;
+CREATE UNIQUE INDEX `i1` ON `t1`(`b`);
+CREATE UNIQUE INDEX `i2` ON `t1`(`a`);
+ALTER TABLE `t1` ADD PRIMARY KEY  (`a`);
+--error ER_FEATURE_DISABLED
+ALTER TABLE `t1` REMOVE PARTITIONING;
+CHECK TABLE `t1` EXTENDED;
+DROP TABLE t1;

=== modified file 'mysql-test/t/partition_disabled.test'
--- a/mysql-test/t/partition_disabled.test	2009-08-12 10:03:05 +0000
+++ b/mysql-test/t/partition_disabled.test	2011-11-18 06:06:29 +0000
@@ -2,10 +2,10 @@
 # Run this test only when mysqld has partitioning, but it is disabled.
 # The statements are not expected to work, just check that we
 # can't crash the server.
---require r/disabled_partition.require
---disable_query_log
-show variables like "have_partitioning";
---enable_query_log
+disable_query_log;
+--require r/true.require
+SELECT (plugin_status = 'DISABLED') AS `TRUE` FROM information_schema.plugins WHERE plugin_name LIKE 'partition%';
+enable_query_log;
 --disable_warnings
 DROP TABLE IF EXISTS t1;
 --enable_warnings
@@ -83,3 +83,18 @@ create table t1 (a varchar(10) charset l
 insert into t1 values (''),(' '),('a'),('a '),('a  ');
 explain partitions select * from t1 where a='a ' OR a='a';
 drop table t1;
+--echo #
+--echo # bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED
+--echo #                                 TABLE CORRUPTS MYISAM
+--disable_warnings
+DROP TABLE if exists `t1`;
+--enable_warnings
+CREATE TABLE  `t1`(`a` INT)ENGINE=myisam;
+ALTER TABLE `t1` ADD COLUMN `b` INT;
+CREATE UNIQUE INDEX `i1` ON `t1`(`b`);
+CREATE UNIQUE INDEX `i2` ON `t1`(`a`);
+ALTER TABLE `t1` ADD PRIMARY KEY  (`a`);
+--error ER_OPTION_PREVENTS_STATEMENT
+ALTER TABLE `t1` REMOVE PARTITIONING;
+CHECK TABLE `t1` EXTENDED;
+DROP TABLE t1;

=== modified file 'mysql-test/t/partition_myisam.test'
--- a/mysql-test/t/partition_myisam.test	2011-10-07 14:20:21 +0000
+++ b/mysql-test/t/partition_myisam.test	2011-11-18 06:06:29 +0000
@@ -233,3 +233,18 @@ PARTITION BY RANGE (a)
  PARTITION pMax VALUES LESS THAN MAXVALUE);
 INSERT INTO t1 VALUES (1, "Partition p1, first row");
 DROP TABLE t1;
+--echo #
+--echo # bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED
+--echo #                                 TABLE CORRUPTS MYISAM
+--disable_warnings
+DROP TABLE if exists `t1`;
+--enable_warnings
+CREATE TABLE  `t1`(`a` INT)ENGINE=myisam;
+ALTER TABLE `t1` ADD COLUMN `b` INT;
+CREATE UNIQUE INDEX `i1` ON `t1`(`b`);
+CREATE UNIQUE INDEX `i2` ON `t1`(`a`);
+ALTER TABLE `t1` ADD PRIMARY KEY  (`a`);
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+ALTER TABLE `t1` REMOVE PARTITIONING;
+CHECK TABLE `t1` EXTENDED;
+DROP TABLE t1;

=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc	2011-11-17 21:44:20 +0000
+++ b/sql/mysqld.cc	2011-11-18 16:49:29 +0000
@@ -7854,7 +7854,7 @@ mysqld_get_one_option(int optid,
 #ifdef WITH_PERFSCHEMA_STORAGE_ENGINE
     /* Parse instrument name and value from argument string */
     char* name = argument,*p, *val;
-    
+
     /* Assignment required */
     if (!(p= strchr(argument, '=')))
     {
@@ -7863,7 +7863,7 @@ mysqld_get_one_option(int optid,
                              "'%s'", argument);
       return 0;
     }
-    
+
     /* Option value */
     val= p + 1;
     if (!*val)
@@ -7873,7 +7873,7 @@ mysqld_get_one_option(int optid,
                              "'%s'", argument);
       return 0;
     }
-    
+
     /* Trim leading spaces from instrument name */
     while (*name && my_isspace(mysqld_charset, *name))
       name++;

=== 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_partition.cc'
--- a/sql/sql_partition.cc	2011-11-16 15:24:04 +0000
+++ b/sql/sql_partition.cc	2011-11-18 06:06:29 +0000
@@ -4669,6 +4669,12 @@ uint prep_alter_part_table(THD *thd, TAB
     my_error(ER_FOREIGN_KEY_ON_PARTITIONED, MYF(0));
     DBUG_RETURN(TRUE);
   }
+  /* Remove partitioning on a not partitioned table is not possible */
+  if (!table->part_info && (alter_info->flags & ALTER_REMOVE_PARTITIONING))
+  {
+    my_error(ER_PARTITION_MGMT_ON_NONPARTITIONED, MYF(0));
+    DBUG_RETURN(TRUE);
+  }
 
   thd->work_part_info= thd->lex->part_info;
 

=== 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 14:51:40 +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, "tmp_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_tmp_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

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2011-11-11 17:37:44 +0000
+++ b/sql/sql_yacc.yy	2011-11-18 06:06:29 +0000
@@ -6869,7 +6869,7 @@ alter_commands:
         ;
 
 remove_partitioning:
-          REMOVE_SYM PARTITIONING_SYM
+          REMOVE_SYM PARTITIONING_SYM have_partitioning
           {
             Lex->alter_info.flags|= ALTER_REMOVE_PARTITIONING;
           }

=== modified file 'storage/perfschema/pfs_events_stages.cc'
--- a/storage/perfschema/pfs_events_stages.cc	2011-07-07 19:06:44 +0000
+++ b/storage/perfschema/pfs_events_stages.cc	2011-11-18 16:49:29 +0000
@@ -32,11 +32,11 @@
 
 ulong events_stages_history_long_size= 0;
 /** Consumer flag for table EVENTS_STAGES_CURRENT. */
-bool flag_events_stages_current= true;
+bool flag_events_stages_current= false;
 /** Consumer flag for table EVENTS_STAGES_HISTORY. */
-bool flag_events_stages_history= true;
+bool flag_events_stages_history= false;
 /** Consumer flag for table EVENTS_STAGES_HISTORY_LONG. */
-bool flag_events_stages_history_long= true;
+bool flag_events_stages_history_long= false;
 
 /** True if EVENTS_STAGES_HISTORY_LONG circular buffer is full. */
 bool events_stages_history_long_full= false;

=== modified file 'storage/perfschema/pfs_events_statements.cc'
--- a/storage/perfschema/pfs_events_statements.cc	2011-07-07 19:06:44 +0000
+++ b/storage/perfschema/pfs_events_statements.cc	2011-11-18 16:49:29 +0000
@@ -32,11 +32,11 @@
 
 ulong events_statements_history_long_size= 0;
 /** Consumer flag for table EVENTS_STATEMENTS_CURRENT. */
-bool flag_events_statements_current= true;
+bool flag_events_statements_current= false;
 /** Consumer flag for table EVENTS_STATEMENTS_HISTORY. */
-bool flag_events_statements_history= true;
+bool flag_events_statements_history= false;
 /** Consumer flag for table EVENTS_STATEMENTS_HISTORY_LONG. */
-bool flag_events_statements_history_long= true;
+bool flag_events_statements_history_long= false;
 
 /** True if EVENTS_STATEMENTS_HISTORY_LONG circular buffer is full. */
 bool events_statements_history_long_full= false;

=== modified file 'storage/perfschema/pfs_events_waits.cc'
--- a/storage/perfschema/pfs_events_waits.cc	2011-07-29 09:10:56 +0000
+++ b/storage/perfschema/pfs_events_waits.cc	2011-11-18 16:49:29 +0000
@@ -32,15 +32,15 @@
 
 ulong events_waits_history_long_size= 0;
 /** Consumer flag for table EVENTS_WAITS_CURRENT. */
-bool flag_events_waits_current= true;
+bool flag_events_waits_current= false;
 /** Consumer flag for table EVENTS_WAITS_HISTORY. */
-bool flag_events_waits_history= true;
+bool flag_events_waits_history= false;
 /** Consumer flag for table EVENTS_WAITS_HISTORY_LONG. */
-bool flag_events_waits_history_long= true;
+bool flag_events_waits_history_long= false;
 /** Consumer flag for the global instrumentation. */
-bool flag_global_instrumentation= true;
+bool flag_global_instrumentation= false;
 /** Consumer flag for the per thread instrumentation. */
-bool flag_thread_instrumentation= true;
+bool flag_thread_instrumentation= false;
 
 /** True if EVENTS_WAITS_HISTORY_LONG circular buffer is full. */
 bool events_waits_history_long_full= false;

=== modified file 'storage/perfschema/table_setup_instruments.cc'
--- a/storage/perfschema/table_setup_instruments.cc	2011-10-19 17:56:04 +0000
+++ b/storage/perfschema/table_setup_instruments.cc	2011-11-18 16:49:29 +0000
@@ -89,6 +89,10 @@ int table_setup_instruments::rnd_next(vo
 {
   PFS_instr_class *instr_class= NULL;
 
+  /* Do not advertise hard coded instruments when disabled. */
+  if (! pfs_initialized)
+    return HA_ERR_END_OF_FILE;
+
   for (m_pos.set_at(&m_next_pos);
        m_pos.has_more_view();
        m_pos.next_view())
@@ -141,6 +145,10 @@ int table_setup_instruments::rnd_pos(con
 {
   PFS_instr_class *instr_class= NULL;
 
+  /* Do not advertise hard coded instruments when disabled. */
+  if (! pfs_initialized)
+    return HA_ERR_END_OF_FILE;
+
   set_position(pos);
 
   switch (m_pos.m_index_1)

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk-wl5461 branch (marc.alff:3438 to 3440) Marc Alff21 Nov