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,
¶m->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 Alff | 21 Nov |
