3625 Jorgen Loland 2011-11-18
BUG#13400713 - ADD OPTIMIZER TRACING OF TEMPORARY TABLES
Add optimizer tracing to temporary table creation
added:
mysql-test/suite/opt_trace/r/temp_table.result
mysql-test/suite/opt_trace/t/temp_table.test
modified:
mysql-test/r/metadata.result
mysql-test/suite/opt_trace/include/subquery.inc
mysql-test/suite/opt_trace/r/charset.result
mysql-test/suite/opt_trace/r/filesort_pq.result
mysql-test/suite/opt_trace/r/general2_no_prot.result
mysql-test/suite/opt_trace/r/general2_ps_prot.result
mysql-test/suite/opt_trace/r/general_no_prot_none.result
mysql-test/suite/opt_trace/r/general_ps_prot_none.result
mysql-test/suite/opt_trace/r/range_no_prot.result
mysql-test/suite/opt_trace/r/range_ps_prot.result
mysql-test/suite/opt_trace/r/security_no_prot.result
mysql-test/suite/opt_trace/r/security_ps_prot.result
mysql-test/suite/opt_trace/r/subquery_no_prot.result
mysql-test/suite/opt_trace/r/subquery_ps_prot.result
sql/sql_derived.cc
sql/sql_select.cc
sql/sql_select.h
3624 Ashish Agarwal 2011-11-18
bug#11760213 - 52599: ALTER TABLE REMOVE PARTITIONING ON
NON-PARTITIONED TABLE CORRUPTS MYISAM.
ISSUE: Removing partitioning on a not partitioned table
was permitted, but wasn't handled properly.
As a result storage engine and .frm table
definitions may go out of sync.
SOLUTION: With this patch removing partitioning
on a not partitioned is forbidden.
removed:
mysql-test/r/disabled_partition.require
mysql-test/r/not_partition.require
modified:
mysql-test/r/not_partition.result
mysql-test/r/partition_disabled.result
mysql-test/r/partition_myisam.result
mysql-test/t/not_partition.test
mysql-test/t/partition_disabled.test
mysql-test/t/partition_myisam.test
sql/sql_partition.cc
sql/sql_yacc.yy
=== modified file 'mysql-test/r/metadata.result'
--- a/mysql-test/r/metadata.result 2011-05-11 11:11:57 +0000
+++ b/mysql-test/r/metadata.result 2011-11-18 12:17:45 +0000
@@ -191,12 +191,12 @@ Catalog Database Table Table_alias Colum
2008-01-02 2008-01-02 2008-01-02 2008-01-02 2008-01-02 2008-01-02
2008-01-03 2008-01-03 2008-01-03 2008-01-03 2008-01-03 2008-01-03
COALESCE(d, d) IFNULL(d, d) IF(i, d, d) CASE i WHEN i THEN d ELSE d END GREATEST(d, d) LEAST(d, d)
-def CASE i WHEN i THEN d ELSE d END CASE i WHEN i THEN d ELSE d END 10 10 10 Y 128 0 63
-def COALESCE(d, d) COALESCE(d, d) 10 10 10 Y 128 0 63
-def GREATEST(d, d) GREATEST(d, d) 10 10 10 Y 128 0 63
-def IF(i, d, d) IF(i, d, d) 10 10 10 Y 128 0 63
-def IFNULL(d, d) IFNULL(d, d) 10 10 10 Y 128 0 63
-def LEAST(d, d) LEAST(d, d) 10 10 10 Y 128 0 63
+def intermediate_tmp_table CASE i WHEN i THEN d ELSE d END CASE i WHEN i THEN d ELSE d END 10 10 10 Y 128 0 63
+def intermediate_tmp_table COALESCE(d, d) COALESCE(d, d) 10 10 10 Y 128 0 63
+def intermediate_tmp_table GREATEST(d, d) GREATEST(d, d) 10 10 10 Y 128 0 63
+def intermediate_tmp_table IF(i, d, d) IF(i, d, d) 10 10 10 Y 128 0 63
+def intermediate_tmp_table IFNULL(d, d) IFNULL(d, d) 10 10 10 Y 128 0 63
+def intermediate_tmp_table LEAST(d, d) LEAST(d, d) 10 10 10 Y 128 0 63
DROP TABLE t1;
#
# Bug#41788 mysql_fetch_field returns org_table == table by a view
=== modified file 'mysql-test/suite/opt_trace/include/subquery.inc'
--- a/mysql-test/suite/opt_trace/include/subquery.inc 2011-11-08 07:51:49 +0000
+++ b/mysql-test/suite/opt_trace/include/subquery.inc 2011-11-18 12:17:45 +0000
@@ -1,6 +1,7 @@
# Test for optimizer tracing of subqueries
--source include/have_optimizer_trace.inc
+--source include/have_64bit.inc
SET optimizer_trace_max_mem_size=1048576; # 1MB
SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
=== modified file 'mysql-test/suite/opt_trace/r/charset.result'
--- a/mysql-test/suite/opt_trace/r/charset.result 2011-10-10 09:08:15 +0000
+++ b/mysql-test/suite/opt_trace/r/charset.result 2011-11-18 12:17:45 +0000
@@ -233,6 +233,19 @@ select * from v1 where v1.col = 'ÁÂÃ� } /* join_optimization */
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "v1",
+ "row_length": 7,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 149796
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
=== modified file 'mysql-test/suite/opt_trace/r/filesort_pq.result'
--- a/mysql-test/suite/opt_trace/r/filesort_pq.result 2011-11-07 15:32:36 +0000
+++ b/mysql-test/suite/opt_trace/r/filesort_pq.result 2011-11-18 12:17:45 +0000
@@ -1552,11 +1552,24 @@ SELECT * from t1 ORDER BY rand(2) LIMIT
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 219,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 4788
+ }
+ }
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "tmp_table_column"
}
],
@@ -3934,17 +3947,30 @@ ORDER BY tmp.f1, f0 LIMIT 30 {
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 235,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 4462
+ }
+ }
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f1"
},
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f0"
}
],
@@ -4209,17 +4235,30 @@ ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30 {
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 235,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 4462
+ }
+ }
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f1"
},
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f0"
}
],
@@ -4489,17 +4528,30 @@ ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30 {
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 235,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 4462
+ }
+ }
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f1"
},
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f0"
}
],
@@ -4797,17 +4849,30 @@ ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30 {
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 235,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 4462
+ }
+ }
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f1"
},
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f0"
}
],
@@ -5101,6 +5166,19 @@ SELECT * FROM v1 {
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "v1",
+ "row_length": 210,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 4993
+ }
+ }
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
@@ -5398,6 +5476,19 @@ SELECT * FROM v1 ORDER BY f2, f0 LIMIT 3
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "v1",
+ "row_length": 210,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 4993
+ }
+ }
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
@@ -6034,6 +6125,45 @@ LIMIT 30 {
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "v1",
+ "row_length": 210,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 4993
+ }
+ }
+ },
+ {
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "v2",
+ "row_length": 210,
+ "key_length": 5,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 4993
+ }
+ }
+ },
+ {
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 419,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 2502
+ }
+ }
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
@@ -6116,19 +6246,19 @@ LIMIT 30 {
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f2"
},
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f0"
},
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f0"
}
],
@@ -6286,17 +6416,30 @@ GROUP BY 1 ORDER BY 2,1 LIMIT 5 {
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 18,
+ "key_length": 9,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 58254
+ }
+ }
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "count(f2)"
},
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f3"
}
],
@@ -7196,6 +7339,19 @@ SELECT * FROM v1 ORDER BY f2, f0 LIMIT 3
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "v1",
+ "row_length": 210,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 4993
+ }
+ }
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
@@ -7601,6 +7757,32 @@ ORDER BY d1.f2 DESC LIMIT 30 {
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "d1",
+ "row_length": 210,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 4993
+ }
+ }
+ },
+ {
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 206,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 5090
+ }
+ }
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
@@ -7638,7 +7820,7 @@ ORDER BY d1.f2 DESC LIMIT 30 {
{
"direction": "desc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f2"
}
],
=== modified file 'mysql-test/suite/opt_trace/r/general2_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_no_prot.result 2011-11-11 12:36:01 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_no_prot.result 2011-11-18 12:17:45 +0000
@@ -954,11 +954,24 @@ TRACE
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 3,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 349525
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f2"
}
] /* filesort_information */,
@@ -1333,15 +1346,41 @@ TRACE
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "teeone",
+ "row_length": 16,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 65536
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 22,
+ "key_length": 5,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 47662
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "b"
}
] /* filesort_information */,
@@ -3648,6 +3687,19 @@ select 497, tmp.ID, NULL from (select 49
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "tmp",
+ "row_length": 10,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 104857
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
@@ -4844,11 +4896,24 @@ GROUP BY field2 ORDER BY alias1.col_int_
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 18,
+ "key_length": 4,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 58254
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "col_int_key"
}
] /* filesort_information */,
=== modified file 'mysql-test/suite/opt_trace/r/general2_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2011-11-11 12:36:01 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2011-11-18 12:17:45 +0000
@@ -972,11 +972,24 @@ TRACE
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 3,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 349525
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "f2"
}
] /* filesort_information */,
@@ -1351,15 +1364,41 @@ TRACE
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "teeone",
+ "row_length": 16,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 65536
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 22,
+ "key_length": 5,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 47662
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "b"
}
] /* filesort_information */,
@@ -3701,6 +3740,19 @@ select 497, tmp.ID, NULL from (select 49
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "tmp",
+ "row_length": 10,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 104857
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
@@ -4897,11 +4949,24 @@ GROUP BY field2 ORDER BY alias1.col_int_
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 18,
+ "key_length": 4,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 58254
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "col_int_key"
}
] /* filesort_information */,
=== modified file 'mysql-test/suite/opt_trace/r/general_no_prot_none.result'
--- a/mysql-test/suite/opt_trace/r/general_no_prot_none.result 2011-11-11 12:36:01 +0000
+++ b/mysql-test/suite/opt_trace/r/general_no_prot_none.result 2011-11-18 12:17:45 +0000
@@ -610,6 +610,19 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
} /* join_preparation */
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "",
+ "row_length": 9,
+ "key_length": 8,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 116508
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) union /* select#3 */ select 2 from `test`.`t6` where (`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL))) AS `RESULT` from `test`.`t5`"
}
] /* steps */
@@ -1045,6 +1058,19 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
} /* join_preparation */
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "",
+ "row_length": 9,
+ "key_length": 8,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 116508
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"expanded_query": "/* select#1 */ select `test`.`t5`.`c` AS `c` from `test`.`t5` where <in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))"
}
] /* steps */
@@ -1316,7 +1342,7 @@ select (@query:=QUERY)+NULL, (@trace:=TR
NULL NULL
select length(@trace);
length(@trace)
-14121
+14565
set optimizer_trace_max_mem_size=13500;
select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
@@ -1325,7 +1351,7 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
c
select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;
(@missing_bytes:=missing_bytes_beyond_max_mem_size)
-735
+1187
select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
(@query2:=QUERY)+NULL (@trace2:=TRACE)+NULL
NULL NULL
@@ -1333,7 +1359,7 @@ select length(@trace2),
(length(@trace2) + @missing_bytes) = length(@trace),
@query2 = @query;
length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace) @query2 = @query
-13386 1 1
+13378 1 1
select length(@query2) + length(@trace2)
between (@@optimizer_trace_max_mem_size-200) and (@@optimizer_trace_max_mem_size+200);
length(@query2) + length(@trace2)
@@ -3991,6 +4017,19 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
} /* join_preparation */
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "",
+ "row_length": 5,
+ "key_length": 5,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 209715
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_optimization": {
"select#": 1,
"steps": [
@@ -5817,11 +5856,24 @@ trace
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 30,
+ "key_length": 5,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 34952
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "a"
}
] /* filesort_information */,
@@ -6208,6 +6260,19 @@ insert into t6 select * from t6 where d>
"join_execution": {
"select#": 1,
"steps": [
+ {
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 5,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 209715
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ }
] /* steps */
} /* join_execution */
}
@@ -6366,6 +6431,19 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
] /* considered_execution_plans */
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "",
+ "row_length": 12,
+ "key_length": 7,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 87381
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "(`test`.`t6`.`d` > 7000)",
"attached_conditions_computation": [
@@ -10330,6 +10408,19 @@ explain select * from v1 where id="b" {
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "v1",
+ "row_length": 21,
+ "key_length": 16,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 49932
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_explain": {
"select#": 2,
"steps": [
@@ -10352,6 +10443,19 @@ select * from information_schema.session
VARIABLE_NAME="optimizer_trace" {
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "information_schema",
+ "table": "session_variables",
+ "row_length": 3268,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 320
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_preparation": {
"select#": 1,
"steps": [
@@ -10498,6 +10602,19 @@ select TRACE into dumpfile 'MYSQLTEST_VA
}
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "",
+ "row_length": 9,
+ "key_length": 8,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 116508
+ }
+ }
+ },
+ {
"join_optimization": {
"select#": 1,
"steps": [
=== modified file 'mysql-test/suite/opt_trace/r/general_ps_prot_none.result'
--- a/mysql-test/suite/opt_trace/r/general_ps_prot_none.result 2011-11-11 12:36:01 +0000
+++ b/mysql-test/suite/opt_trace/r/general_ps_prot_none.result 2011-11-18 12:17:45 +0000
@@ -610,6 +610,19 @@ SELECT (SELECT 1 FROM t6 WHERE d = ifnul
} /* join_preparation */
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "",
+ "row_length": 9,
+ "key_length": 8,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 116508
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"expanded_query": "/* select#1 */ select (/* select#2 */ select 1 from `test`.`t6` where (`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) union /* select#3 */ select 2 from `test`.`t6` where (`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL))) AS `RESULT` from `test`.`t5`"
}
] /* steps */
@@ -1025,6 +1038,19 @@ SELECT * FROM t5 WHERE 5 IN (SELECT 1 FR
} /* join_preparation */
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "",
+ "row_length": 9,
+ "key_length": 8,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 116508
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"expanded_query": "/* select#1 */ select `test`.`t5`.`c` AS `c` from `test`.`t5` where <in_optimizer>(5,<exists>(/* select#2 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 1)) union /* select#3 */ select 1 from `test`.`t6` where ((`test`.`t6`.`d` = ifnull(`test`.`t5`.`c`,NULL)) and (<cache>(5) = 2))))"
}
] /* steps */
@@ -1296,16 +1322,16 @@ select (@query:=QUERY)+NULL, (@trace:=TR
NULL NULL
select length(@trace);
length(@trace)
-13343
+13787
set optimizer_trace_max_mem_size=13500;
select length(@query)+length(@trace) > @@optimizer_trace_max_mem_size;
length(@query)+length(@trace) > @@optimizer_trace_max_mem_size
-0
+1
SELECT * FROM t5 WHERE 5 IN (SELECT 1 FROM t6 WHERE d = ifnull(c,null) UNION SELECT 2 FROM t6 WHERE d = ifnull(c,null));
c
select (@missing_bytes:=missing_bytes_beyond_max_mem_size) from information_schema.OPTIMIZER_TRACE;
(@missing_bytes:=missing_bytes_beyond_max_mem_size)
-0
+403
select (@query2:=QUERY)+NULL,(@trace2:=TRACE)+NULL from information_schema.OPTIMIZER_TRACE;
(@query2:=QUERY)+NULL (@trace2:=TRACE)+NULL
NULL NULL
@@ -1313,7 +1339,7 @@ select length(@trace2),
(length(@trace2) + @missing_bytes) = length(@trace),
@query2 = @query;
length(@trace2) (length(@trace2) + @missing_bytes) = length(@trace) @query2 = @query
-13343 1 1
+13384 1 1
select length(@query2) + length(@trace2)
between (@@optimizer_trace_max_mem_size-200) and (@@optimizer_trace_max_mem_size+200);
length(@query2) + length(@trace2)
@@ -3961,6 +3987,19 @@ SELECT * FROM t1 WHERE c1=5 UNION SELECT
} /* join_preparation */
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "",
+ "row_length": 5,
+ "key_length": 5,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 209715
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_optimization": {
"select#": 1,
"steps": [
@@ -5775,11 +5814,24 @@ trace
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 30,
+ "key_length": 5,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 34952
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"filesort_information": [
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "a"
}
] /* filesort_information */,
@@ -6166,6 +6218,19 @@ insert into t6 select * from t6 where d>
"join_execution": {
"select#": 1,
"steps": [
+ {
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 5,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 209715
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ }
] /* steps */
} /* join_execution */
}
@@ -6324,6 +6389,19 @@ update t5, t6 set t6.d=t6.d+t5.c+4-t5.c-
] /* considered_execution_plans */
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "",
+ "row_length": 12,
+ "key_length": 7,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 87381
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "(`test`.`t6`.`d` > 7000)",
"attached_conditions_computation": [
@@ -10246,6 +10324,19 @@ explain select * from v1 where id="b" {
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "v1",
+ "row_length": 21,
+ "key_length": 16,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 49932
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_explain": {
"select#": 2,
"steps": [
@@ -10268,6 +10359,19 @@ select * from information_schema.session
VARIABLE_NAME="optimizer_trace" {
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "information_schema",
+ "table": "session_variables",
+ "row_length": 3268,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 320
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_preparation": {
"select#": 1,
"steps": [
@@ -10414,6 +10518,19 @@ select TRACE into dumpfile 'MYSQLTEST_VA
}
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "",
+ "row_length": 9,
+ "key_length": 8,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 116508
+ }
+ }
+ },
+ {
"join_optimization": {
"select#": 1,
"steps": [
=== modified file 'mysql-test/suite/opt_trace/r/range_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/range_no_prot.result 2011-11-10 13:05:08 +0000
+++ b/mysql-test/suite/opt_trace/r/range_no_prot.result 2011-11-18 12:17:45 +0000
@@ -3945,6 +3945,19 @@ EXPLAIN SELECT 1 FROM
} /* join_optimization */
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "d2",
+ "row_length": 5,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 209715
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
@@ -4283,6 +4296,19 @@ EXPLAIN SELECT 1 FROM
} /* join_optimization */
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "d2",
+ "row_length": 5,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 209715
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
=== modified file 'mysql-test/suite/opt_trace/r/range_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/range_ps_prot.result 2011-11-10 13:05:08 +0000
+++ b/mysql-test/suite/opt_trace/r/range_ps_prot.result 2011-11-18 12:17:45 +0000
@@ -3945,6 +3945,19 @@ EXPLAIN SELECT 1 FROM
} /* join_optimization */
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "d2",
+ "row_length": 5,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 209715
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
@@ -4283,6 +4296,19 @@ EXPLAIN SELECT 1 FROM
} /* join_optimization */
},
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "d2",
+ "row_length": 5,
+ "key_length": 0,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 209715
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_execution": {
"select#": 2,
"steps": [
=== modified file 'mysql-test/suite/opt_trace/r/security_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/security_no_prot.result 2011-09-22 12:51:05 +0000
+++ b/mysql-test/suite/opt_trace/r/security_no_prot.result 2011-11-18 12:17:45 +0000
@@ -869,7 +869,7 @@ insert into t2 values(current_user());
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
insert into t2 values(current_user()) 20 0
-insert into t3 select * from t3 2274 0
+insert into t3 select * from t3 2688 0
# Trace exposed body of trigger, and content of t2/t3, which we
# could see anyway:
show create trigger trg2;
@@ -888,7 +888,7 @@ a
first
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
-select a from (select a from t1 where a like "f%") as tt where a like "fi%" 6939 0
+select a from (select a from t1 where a like "f%") as tt where a like "fi%" 7334 0
# For routines, as they only use t1 and we added only one
# privilege on t1, we have nothing to remove.
=== modified file 'mysql-test/suite/opt_trace/r/security_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/security_ps_prot.result 2011-09-22 12:51:05 +0000
+++ b/mysql-test/suite/opt_trace/r/security_ps_prot.result 2011-11-18 12:17:45 +0000
@@ -935,7 +935,7 @@ select QUERY, length(TRACE), INSUFFICIEN
QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
insert into t2 values(current_user()) 20 0
insert into t2 values(current_user()) 20 0
-insert into t3 select * from t3 2274 0
+insert into t3 select * from t3 2688 0
# Trace exposed body of trigger, and content of t2/t3, which we
# could see anyway:
show create trigger trg2;
@@ -955,7 +955,7 @@ first
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
QUERY length(TRACE) INSUFFICIENT_PRIVILEGES
select a from (select a from t1 where a like "f%") as tt where a like "fi%" 613 0
-select a from (select a from t1 where a like "f%") as tt where a like "fi%" 6939 0
+select a from (select a from t1 where a like "f%") as tt where a like "fi%" 7334 0
# For routines, as they only use t1 and we added only one
# privilege on t1, we have nothing to remove.
=== modified file 'mysql-test/suite/opt_trace/r/subquery_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_no_prot.result 2011-11-17 10:09:13 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_no_prot.result 2011-11-18 12:17:45 +0000
@@ -1720,6 +1720,32 @@ field4,field5,field6 {
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "alias2",
+ "row_length": 46,
+ "key_length": 8,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 22795
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 13,
+ "key_length": 12,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 80659
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_execution": {
"select#": 5,
"steps": [
@@ -1731,19 +1757,19 @@ field4,field5,field6 {
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "col_varchar_key"
},
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "col_date_key"
},
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "col_varchar_nokey"
}
] /* filesort_information */,
=== modified file 'mysql-test/suite/opt_trace/r/subquery_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_ps_prot.result 2011-11-17 10:09:13 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_ps_prot.result 2011-11-18 12:17:45 +0000
@@ -1698,6 +1698,32 @@ field4,field5,field6 {
"select#": 1,
"steps": [
{
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "alias2",
+ "row_length": 46,
+ "key_length": 8,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 22795
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 13,
+ "key_length": 12,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 80659
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
"join_execution": {
"select#": 5,
"steps": [
@@ -1709,19 +1735,19 @@ field4,field5,field6 {
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "col_varchar_key"
},
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "col_date_key"
},
{
"direction": "asc",
"database": "",
- "table": "",
+ "table": "intermediate_tmp_table",
"field": "col_varchar_nokey"
}
] /* filesort_information */,
=== added file 'mysql-test/suite/opt_trace/r/temp_table.result'
--- a/mysql-test/suite/opt_trace/r/temp_table.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/opt_trace/r/temp_table.result 2011-11-18 12:17:45 +0000
@@ -0,0 +1,527 @@
+SET optimizer_trace_max_mem_size=1048576;
+SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
+CREATE TABLE tmp (
+pk INT PRIMARY KEY AUTO_INCREMENT,
+col1 CHAR (1)
+);
+INSERT INTO tmp(col1) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
+CREATE TABLE t1 (
+uniq VARCHAR(10),
+col1 VARCHAR(10),
+col2 VARCHAR(1024)
+);
+INSERT INTO t1 SELECT pk, col1, col1 FROM tmp;
+SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3;
+uniq col1
+1 a
+2 b
+3 c
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`uniq` AS `uniq`,`test`.`t1`.`col1` AS `col1` from `test`.`t1` group by `test`.`t1`.`col2`,`test`.`t1`.`uniq` limit 3"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "table_dependencies": [
+ {
+ "database": "test",
+ "table": "t1",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": [
+ ] /* depends_on_map_bits */
+ }
+ ] /* table_dependencies */
+ },
+ {
+ "rows_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "rows": 8,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* rows_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "rows": 8,
+ "cost": 2.0391,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 3.6391,
+ "rows_for_plan": 8,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "clause_processing": {
+ "clause": "GROUP BY",
+ "original_clause": "`test`.`t1`.`col2`,`test`.`t1`.`uniq`",
+ "items": [
+ {
+ "item": "`test`.`t1`.`col2`"
+ },
+ {
+ "item": "`test`.`t1`.`uniq`"
+ }
+ ] /* items */,
+ "resulting_clause_is_simple": true,
+ "resulting_clause": "`test`.`t1`.`col2`,`test`.`t1`.`uniq`"
+ } /* clause_processing */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "access_type": "table_scan"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 1054,
+ "key_length": 1040,
+ "unique_constraint": true,
+ "location": "disk (MyISAM)",
+ "record_format": "fixed"
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
+ "filesort_information": [
+ {
+ "direction": "asc",
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "field": "col2"
+ },
+ {
+ "direction": "asc",
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "field": "uniq"
+ }
+ ] /* filesort_information */,
+ "filesort_priority_queue_optimization": {
+ "limit": 3,
+ "rows_estimate": 18,
+ "row_size": 1043,
+ "memory_available": 262144,
+ "chosen": true
+ } /* filesort_priority_queue_optimization */,
+ "filesort_execution": [
+ ] /* filesort_execution */,
+ "filesort_summary": {
+ "rows": 4,
+ "examined_rows": 8,
+ "number_of_tmp_files": 0,
+ "sort_buffer_size": 4204,
+ "sort_mode": "<sort_key, rowid>"
+ } /* filesort_summary */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+SELECT uniq, col1, col2 FROM t1 GROUP BY uniq ;
+uniq col1 col2
+1 a a
+2 b b
+3 c c
+4 d d
+5 e e
+6 f f
+7 g g
+8 h h
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+SELECT uniq, col1, col2 FROM t1 GROUP BY uniq {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`uniq` AS `uniq`,`test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` group by `test`.`t1`.`uniq`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "table_dependencies": [
+ {
+ "database": "test",
+ "table": "t1",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": [
+ ] /* depends_on_map_bits */
+ }
+ ] /* table_dependencies */
+ },
+ {
+ "rows_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "rows": 8,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* rows_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "rows": 8,
+ "cost": 2.0391,
+ "chosen": true,
+ "use_tmp_table": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 3.6391,
+ "rows_for_plan": 8,
+ "sort_cost": 8,
+ "new_cost_for_plan": 11.639,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "clause_processing": {
+ "clause": "GROUP BY",
+ "original_clause": "`test`.`t1`.`uniq`",
+ "items": [
+ {
+ "item": "`test`.`t1`.`uniq`"
+ }
+ ] /* items */,
+ "resulting_clause_is_simple": true,
+ "resulting_clause": "`test`.`t1`.`uniq`"
+ } /* clause_processing */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "access_type": "table_scan"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 1050,
+ "key_length": 13,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 998
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
+ "filesort_information": [
+ {
+ "direction": "asc",
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "field": "uniq"
+ }
+ ] /* filesort_information */,
+ "filesort_priority_queue_optimization": {
+ "usable": false,
+ "cause": "not applicable (no LIMIT)"
+ } /* filesort_priority_queue_optimization */,
+ "filesort_execution": [
+ ] /* filesort_execution */,
+ "filesort_summary": {
+ "rows": 8,
+ "examined_rows": 8,
+ "number_of_tmp_files": 0,
+ "sort_buffer_size": 486,
+ "sort_mode": "<sort_key, rowid>"
+ } /* filesort_summary */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+select @@tmp_table_size;
+@@tmp_table_size
+16777216
+SET @old_size= @@tmp_table_size;
+SET SESSION tmp_table_size= 1024;
+INSERT INTO t1 SELECT pk+8, col1, col1 FROM tmp;
+SELECT uniq, col1, col2 FROM t1 GROUP BY uniq;
+uniq col1 col2
+1 a a
+10 b b
+11 c c
+12 d d
+13 e e
+14 f f
+15 g g
+16 h h
+2 b b
+3 c c
+4 d d
+5 e e
+6 f f
+7 g g
+8 h h
+9 a a
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+SELECT uniq, col1, col2 FROM t1 GROUP BY uniq {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`uniq` AS `uniq`,`test`.`t1`.`col1` AS `col1`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` group by `test`.`t1`.`uniq`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "table_dependencies": [
+ {
+ "database": "test",
+ "table": "t1",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": [
+ ] /* depends_on_map_bits */
+ }
+ ] /* table_dependencies */
+ },
+ {
+ "rows_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "rows": 16,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* rows_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "rows": 16,
+ "cost": 2.0781,
+ "chosen": true,
+ "use_tmp_table": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 5.2781,
+ "rows_for_plan": 16,
+ "sort_cost": 16,
+ "new_cost_for_plan": 21.278,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": null,
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "clause_processing": {
+ "clause": "GROUP BY",
+ "original_clause": "`test`.`t1`.`uniq`",
+ "items": [
+ {
+ "item": "`test`.`t1`.`uniq`"
+ }
+ ] /* items */,
+ "resulting_clause_is_simple": true,
+ "resulting_clause": "`test`.`t1`.`uniq`"
+ } /* clause_processing */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "access_type": "table_scan"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "creating_tmp_table": {
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 1050,
+ "key_length": 13,
+ "unique_constraint": false,
+ "location": "memory (heap)",
+ "row_limit_estimate": 1
+ } /* temporary_table_info */
+ } /* creating_tmp_table */
+ },
+ {
+ "converting_temp_table_to_myisam": {
+ "cause": "memory_table_size_exceeded",
+ "temporary_table_info": {
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "row_length": 1050,
+ "key_length": 13,
+ "unique_constraint": false,
+ "location": "disk (MyISAM)",
+ "record_format": "fixed"
+ } /* temporary_table_info */
+ } /* converting_temp_table_to_myisam */
+ },
+ {
+ "filesort_information": [
+ {
+ "direction": "asc",
+ "database": "",
+ "table": "intermediate_tmp_table",
+ "field": "uniq"
+ }
+ ] /* filesort_information */,
+ "filesort_priority_queue_optimization": {
+ "usable": false,
+ "cause": "not applicable (no LIMIT)"
+ } /* filesort_priority_queue_optimization */,
+ "filesort_execution": [
+ ] /* filesort_execution */,
+ "filesort_summary": {
+ "rows": 16,
+ "examined_rows": 16,
+ "number_of_tmp_files": 0,
+ "sort_buffer_size": 676,
+ "sort_mode": "<sort_key, rowid>"
+ } /* filesort_summary */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+SET GLOBAL tmp_table_size= @old_size;
+DROP TABLE t1, tmp;
=== added file 'mysql-test/suite/opt_trace/t/temp_table.test'
--- a/mysql-test/suite/opt_trace/t/temp_table.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/opt_trace/t/temp_table.test 2011-11-18 12:17:45 +0000
@@ -0,0 +1,48 @@
+--source include/have_optimizer_trace.inc
+--source include/have_64bit.inc
+
+if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL
+ + $VIEW_PROTOCOL > 0`)
+{
+ --skip Need normal protocol
+}
+
+SET optimizer_trace_max_mem_size=1048576; # 1MB
+SET optimizer_trace="enabled=on,end_marker=on,one_line=off";
+
+CREATE TABLE tmp (
+ pk INT PRIMARY KEY AUTO_INCREMENT,
+ col1 CHAR (1)
+);
+
+INSERT INTO tmp(col1) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
+
+CREATE TABLE t1 (
+ uniq VARCHAR(10),
+ col1 VARCHAR(10),
+ col2 VARCHAR(1024)
+);
+
+INSERT INTO t1 SELECT pk, col1, col1 FROM tmp;
+
+# Uses MyISAM temporary table due to long VARCHAR in GROUP BY clause.
+SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3;
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# Uses HEAP temporary table
+SELECT uniq, col1, col2 FROM t1 GROUP BY uniq ;
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+select @@tmp_table_size;
+SET @old_size= @@tmp_table_size;
+SET SESSION tmp_table_size= 1024;
+
+INSERT INTO t1 SELECT pk+8, col1, col1 FROM tmp;
+
+# Uses HEAP temporary table. Converts it to MyISAM due to heap size limitation
+SELECT uniq, col1, col2 FROM t1 GROUP BY uniq;
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+SET GLOBAL tmp_table_size= @old_size;
+
+DROP TABLE t1, tmp;
=== modified file 'sql/sql_derived.cc'
--- a/sql/sql_derived.cc 2011-10-27 10:22:55 +0000
+++ b/sql/sql_derived.cc 2011-11-18 12:17:45 +0000
@@ -349,7 +349,7 @@ bool mysql_derived_create(THD *thd, LEX
thd->lex->select_lex.options |
thd->variables.option_bits |
TMP_TABLE_ALL_COLUMNS),
- thd->variables.big_tables))
+ thd->variables.big_tables, &thd->opt_trace))
DBUG_RETURN(TRUE);
table->file->extra(HA_EXTRA_WRITE_CACHE);
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-11-17 10:09:13 +0000
+++ b/sql/sql_select.cc 2011-11-18 12:17:45 +0000
@@ -3701,7 +3701,7 @@ JOIN::create_intermediate_table(List<Ite
TABLE* tab= create_tmp_table(thd, &tmp_table_param, *tmp_table_fields,
tmp_table_group, select_distinct && !group_list,
save_sum_fields, select_options, tmp_rows_limit,
- "");
+ "intermediate_tmp_table");
if (!tab)
DBUG_RETURN(NULL);
@@ -17751,6 +17751,9 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
string_total_length / string_count >= AVG_STRING_LENGTH_TO_PACK_ROWS)))
use_packed_rows= 1;
+ if (!use_packed_rows)
+ share->db_create_options&= ~HA_OPTION_PACK_RECORD;
+
share->reclength= reclength;
{
uint alloc_length=ALIGN_SIZE(reclength+MI_UNIQUE_HASH_LENGTH+1);
@@ -18025,7 +18028,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
{
if (instantiate_tmp_table(table, param->keyinfo, param->start_recinfo,
¶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, "temporary_table_info");
+ trace_tmp.add_utf8_table(table);
+
+ trace_tmp.add("row_length",table->s->reclength).
+ add("key_length", table->s->key_info ?
+ table->s->key_info->key_length :
+ 0).
+ add("unique_constraint", table->s->uniques ? true : false);
+
+ if (table->s->db_type() == myisam_hton)
+ {
+ trace_tmp.add_alnum("location", "disk (MyISAM)");
+ if (table->s->db_create_options & HA_OPTION_PACK_RECORD)
+ trace_tmp.add_alnum("record_format", "packed");
+ else
+ trace_tmp.add_alnum("record_format", "fixed");
+ }
+ else
+ {
+ DBUG_ASSERT(table->s->db_type() == heap_hton);
+ trace_tmp.add_alnum("location", "memory (heap)").
+ add("row_limit_estimate", table->s->max_rows);
+ }
+}
+
/**
@brief
Instantiates temporary table
@@ -18664,6 +18695,7 @@ bool create_myisam_tmp_table(TABLE *tabl
@param start_recinfo Column descriptions
@param recinfo INOUT End of column descriptions
@param options Option bits
+ @param trace Optimizer trace to write info to
@details
Creates tmp table and opens it.
@@ -18676,7 +18708,8 @@ bool create_myisam_tmp_table(TABLE *tabl
bool instantiate_tmp_table(TABLE *table, KEY *keyinfo,
MI_COLUMNDEF *start_recinfo,
MI_COLUMNDEF **recinfo,
- ulonglong options, my_bool big_tables)
+ ulonglong options, my_bool big_tables,
+ Opt_trace_context *trace)
{
if (table->s->db_type() == myisam_hton)
{
@@ -18688,6 +18721,13 @@ bool instantiate_tmp_table(TABLE *table,
}
if (open_tmp_table(table))
return TRUE;
+
+ if (unlikely(trace->is_started()))
+ {
+ Opt_trace_object wrapper(trace);
+ Opt_trace_object convert(trace, "creating_tmp_table");
+ trace_tmp_table(trace, table);
+ }
return FALSE;
}
@@ -18809,6 +18849,18 @@ bool create_myisam_from_heap(THD *thd, T
goto err2;
if (open_tmp_table(&new_table))
goto err1;
+
+
+ if (unlikely(thd->opt_trace.is_started()))
+ {
+ Opt_trace_context * trace= &thd->opt_trace;
+ Opt_trace_object wrapper(trace);
+ Opt_trace_object convert(trace, "converting_temp_table_to_myisam");
+ DBUG_ASSERT(error == HA_ERR_RECORD_FILE_FULL);
+ convert.add_alnum("cause", "memory_table_size_exceeded");
+ trace_tmp_table(trace, &new_table);
+ }
+
if (table->file->indexes_are_disabled())
new_table.file->ha_disable_indexes(HA_KEY_SWITCH_ALL);
table->file->ha_index_or_rnd_end();
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2011-11-11 12:36:01 +0000
+++ b/sql/sql_select.h 2011-11-18 12:17:45 +0000
@@ -2472,7 +2472,8 @@ bool const_expression_in_where(Item *con
bool instantiate_tmp_table(TABLE *table, KEY *keyinfo,
MI_COLUMNDEF *start_recinfo,
MI_COLUMNDEF **recinfo,
- ulonglong options, my_bool big_tables);
+ ulonglong options, my_bool big_tables,
+ Opt_trace_context *trace);
/**
Printing the transformed query in EXPLAIN EXTENDED or optimizer trace
No bundle (reason: useless for push emails).| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:3624 to 3625) Bug#13400713 | Jorgen Loland | 21 Nov |
