maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #08182
[MDEV-7586] Merged derived tables/VIEWs increment created_tmp_tables
Hi Sergey!
As discussed on the mdev page, I've implemented the fix for
incrementing the created_tmp_tables variable.
I've attached the patch for review. The changes in the patch are
explained within the mdev page.
commit 53d78644b793f7b667e71863ccd0b1ba54c894f3
Author: Vicențiu Ciorbaru <cvicentiu@xxxxxxxxx>
Date: Sun Feb 22 03:40:17 2015 +0200
MDEV-7586: Merged derived tables/VIEWs increment created_tmp_tables
Temporary table count fix. The number of temporary tables was increased
when the table is not actually created. (when do_not_open was passed
as TRUE to create_tmp_table).
Another issue fixed is that create_tmp_table was called incorrectly
in create_result_table, having keep_row_order passed for the do_not_open
parameter and keep_row_order always set to false.
diff --git a/mysql-test/r/tmp_table_count-7586.result
b/mysql-test/r/tmp_table_count-7586.result
new file mode 100644
index 0000000..0c526e0
--- /dev/null
+++ b/mysql-test/r/tmp_table_count-7586.result
@@ -0,0 +1,83 @@
+create table t2 (a int);
+insert into t2 values (1),(2),(3);
+create view v2 as select a from t2;
+flush status;
+select * from v2;
+a
+1
+2
+3
+show status like '%Created_tmp%';
+Variable_name Value
+Created_tmp_disk_tables 0
+Created_tmp_files 0
+Created_tmp_tables 0
+explain select * from v2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3
+select * from (select * from t2) T1;
+a
+1
+2
+3
+show status like '%Created_tmp%';
+Variable_name Value
+Created_tmp_disk_tables 0
+Created_tmp_files 0
+Created_tmp_tables 0
+explain select * from (select * from t2) T1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3
+drop view v2;
+drop table t2;
+CREATE TABLE t1(a int);
+INSERT INTO t1 values(1),(2);
+CREATE TABLE t2(a int);
+INSERT INTO t2 values(1),(2);
+EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary
+truncate table performance_schema.events_statements_history_long;
+flush status;
+CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP
BY a HAVING a > 1);
+# Performance schema should be the same as "Created_tmp_tables" variable below
+select sum(created_tmp_tables) from
performance_schema.events_statements_history_long;
+sum(created_tmp_tables)
+2
+show status like '%Created_tmp%';
+Variable_name Value
+Created_tmp_disk_tables 0
+Created_tmp_files 0
+Created_tmp_tables 2
+drop table t3;
+EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
+truncate table performance_schema.events_statements_history_long;
+flush status;
+CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a);
+# Performance schema should be the same as "Created_tmp_tables" variable below
+select sum(created_tmp_tables) from
performance_schema.events_statements_history_long;
+sum(created_tmp_tables)
+1
+show status like '%Created_tmp%';
+Variable_name Value
+Created_tmp_disk_tables 0
+Created_tmp_files 0
+Created_tmp_tables 1
+drop table t1,t2,t3;
+truncate table performance_schema.events_statements_history_long;
+flush status;
+# Performance schema should be the same as "Created_tmp_tables" variable below
+select sum(created_tmp_tables) from
performance_schema.events_statements_history_long;
+sum(created_tmp_tables)
+0
+show status like '%Created_tmp%';
+Variable_name Value
+Created_tmp_disk_tables 0
+Created_tmp_files 0
+Created_tmp_tables 0
diff --git a/mysql-test/t/tmp_table_count-7586.test
b/mysql-test/t/tmp_table_count-7586.test
new file mode 100644
index 0000000..756913e
--- /dev/null
+++ b/mysql-test/t/tmp_table_count-7586.test
@@ -0,0 +1,53 @@
+# MDEV-7586 regression test.
+# Test if created_tmp_tables status variable is correctly incremented.
+--source include/have_perfschema.inc
+
+create table t2 (a int);
+insert into t2 values (1),(2),(3);
+create view v2 as select a from t2;
+
+flush status;
+select * from v2;
+show status like '%Created_tmp%';
+
+explain select * from v2;
+
+select * from (select * from t2) T1;
+show status like '%Created_tmp%';
+
+explain select * from (select * from t2) T1;
+
+
+drop view v2;
+drop table t2;
+
+
+CREATE TABLE t1(a int);
+INSERT INTO t1 values(1),(2);
+CREATE TABLE t2(a int);
+INSERT INTO t2 values(1),(2);
+
+EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);
+truncate table performance_schema.events_statements_history_long;
+flush status;
+CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP
BY a HAVING a > 1);
+--echo # Performance schema should be the same as
"Created_tmp_tables" variable below
+select sum(created_tmp_tables) from
performance_schema.events_statements_history_long;
+show status like '%Created_tmp%';
+drop table t3;
+
+EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a);
+truncate table performance_schema.events_statements_history_long;
+flush status;
+CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a);
+--echo # Performance schema should be the same as
"Created_tmp_tables" variable below
+select sum(created_tmp_tables) from
performance_schema.events_statements_history_long;
+show status like '%Created_tmp%';
+
+drop table t1,t2,t3;
+
+truncate table performance_schema.events_statements_history_long;
+flush status;
+--echo # Performance schema should be the same as
"Created_tmp_tables" variable below
+select sum(created_tmp_tables) from
performance_schema.events_statements_history_long;
+show status like '%Created_tmp%';
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index d5e3334..2122349 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -3890,7 +3890,6 @@ SJ_TMP_TABLE::create_sj_weedout_tmp_table(THD *thd)
/*
STEP 1: Get temporary table name
*/
- thd->inc_status_created_tmp_tables();
if (use_temp_pool && !(test_flags & TEST_KEEP_TMP_TABLES))
temp_pool_slot = bitmap_lock_set_next(&temp_pool);
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 9cdf5ce..4463eb7 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -3676,7 +3676,7 @@ create_result_table(THD *thd_arg, List<Item>
*column_types,
if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
(ORDER*) 0, is_union_distinct, 1,
options, HA_POS_ERROR, (char*) table_alias,
- keep_row_order)))
+ !create_table, keep_row_order)))
return TRUE;
col_stat= (Column_statistics*) table->in_use->alloc(table->s->fields *
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 4393f02..bb381ff 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -15802,7 +15802,6 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM
*param, List<Item> &fields,
(int) distinct, (int) save_sum_fields,
(ulong) rows_limit, MY_TEST(group)));
- thd->inc_status_created_tmp_tables();
thd->query_plan_flags|= QPLAN_TMP_TABLE;
if (use_temp_pool && !(test_flags & TEST_KEEP_TMP_TABLES))
@@ -16756,14 +16755,19 @@ bool open_tmp_table(TABLE *table)
HA_OPEN_TMP_TABLE |
HA_OPEN_INTERNAL_TABLE)))
{
- table->file->print_error(error,MYF(0)); /* purecov: inspected */
- table->db_stat=0;
- return(1);
+ table->file->print_error(error, MYF(0)); /* purecov: inspected */
+ table->db_stat= 0;
+ return 1;
}
table->db_stat= HA_OPEN_KEYFILE+HA_OPEN_RNDFILE;
- (void) table->file->extra(HA_EXTRA_QUICK); /* Faster */
- table->created= TRUE;
- return(0);
+ (void) table->file->extra(HA_EXTRA_QUICK); /* Faster */
+ if (!table->created)
+ {
+ table->created= TRUE;
+ table->in_use->inc_status_created_tmp_tables();
+ }
+
+ return 0;
}
@@ -16780,7 +16784,7 @@ bool open_tmp_table(TABLE *table)
options Option bits
DESCRIPTION
- Create an internal emporary table according to passed description. The is
+ Create an internal temporary table according to passed description. The is
assumed to have one unique index or constraint.
The passed array or TMP_ENGINE_COLUMNDEF structures must have this form:
@@ -16799,7 +16803,7 @@ bool open_tmp_table(TABLE *table)
*/
-bool create_internal_tmp_table(TABLE *table, KEY *keyinfo,
+bool create_internal_tmp_table(TABLE *table, KEY *keyinfo,
TMP_ENGINE_COLUMNDEF *start_recinfo,
TMP_ENGINE_COLUMNDEF **recinfo,
ulonglong options)
@@ -16930,8 +16934,10 @@ bool create_internal_tmp_table(TABLE *table,
KEY *keyinfo,
goto err;
}
table->in_use->inc_status_created_tmp_disk_tables();
+ table->in_use->inc_status_created_tmp_tables();
table->in_use->query_plan_flags|= QPLAN_TMP_DISK;
share->db_record_offset= 1;
+ table->created= TRUE;
DBUG_RETURN(0);
err:
DBUG_RETURN(1);
@@ -16971,7 +16977,7 @@ bool create_internal_tmp_table(TABLE *table,
KEY *keyinfo,
/* Create internal MyISAM temporary table */
-bool create_internal_tmp_table(TABLE *table, KEY *keyinfo,
+bool create_internal_tmp_table(TABLE *table, KEY *keyinfo,
TMP_ENGINE_COLUMNDEF *start_recinfo,
TMP_ENGINE_COLUMNDEF **recinfo,
ulonglong options)
@@ -17076,6 +17082,7 @@ bool create_internal_tmp_table(TABLE *table,
KEY *keyinfo,
goto err;
}
table->in_use->inc_status_created_tmp_disk_tables();
+ table->in_use->inc_status_created_tmp_tables();
table->in_use->query_plan_flags|= QPLAN_TMP_DISK;
share->db_record_offset= 1;
table->created= TRUE;
@@ -17109,7 +17116,7 @@ create_internal_tmp_table_from_heap(THD *thd,
TABLE *table,
if (is_duplicate)
*is_duplicate= FALSE;
- if (table->s->db_type() != heap_hton ||
+ if (table->s->db_type() != heap_hton ||
error != HA_ERR_RECORD_FILE_FULL)
{
/*
@@ -17139,8 +17146,8 @@ create_internal_tmp_table_from_heap(THD *thd,
TABLE *table,
new_table.no_rows= table->no_rows;
if (create_internal_tmp_table(&new_table, table->key_info, start_recinfo,
recinfo,
- thd->lex->select_lex.options |
- thd->variables.option_bits))
+ thd->lex->select_lex.options |
+ thd->variables.option_bits))
goto err2;
if (open_tmp_table(&new_table))
goto err1;
@@ -17203,7 +17210,7 @@ create_internal_tmp_table_from_heap(THD *thd,
TABLE *table,
new_table.s= table->s; // Keep old share
*table= new_table;
*table->s= share;
-
+
table->file->change_table_ptr(table, table->s);
table->use_all_columns();
if (save_proc_info)
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 7d53731..9cad225 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1788,10 +1788,6 @@ Field *create_tmp_field(THD *thd, TABLE
*table,Item *item, Item::Type type,
bool table_cant_handle_bit_fields,
bool make_copy_field,
uint convert_blob_length);
-bool create_internal_tmp_table(TABLE *table, KEY *keyinfo,
- TMP_ENGINE_COLUMNDEF *start_recinfo,
- TMP_ENGINE_COLUMNDEF **recinfo,
- ulonglong options, my_bool big_tables);
/*
General routine to change field->ptr of a NULL-terminated array of Field
@@ -1879,20 +1875,20 @@ void make_possible_keys_line(TABLE *table,
key_map possible_keys, String *line);
#define RATIO_TO_PACK_ROWS 2
#define MIN_STRING_LENGTH_TO_PACK_ROWS 10
-TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
- ORDER *group, bool distinct, bool save_sum_fields,
- ulonglong select_options, ha_rows rows_limit,
- const char* alias, bool do_not_open=FALSE,
+TABLE *create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
+ ORDER *group, bool distinct, bool save_sum_fields,
+ ulonglong select_options, ha_rows rows_limit,
+ const char* alias, bool do_not_open=FALSE,
bool keep_row_order= FALSE);
void free_tmp_table(THD *thd, TABLE *entry);
bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table,
TMP_ENGINE_COLUMNDEF *start_recinfo,
- TMP_ENGINE_COLUMNDEF **recinfo,
+ TMP_ENGINE_COLUMNDEF **recinfo,
int error, bool
ignore_last_dupp_key_error,
bool *is_duplicate);
-bool create_internal_tmp_table(TABLE *table, KEY *keyinfo,
+bool create_internal_tmp_table(TABLE *table, KEY *keyinfo,
TMP_ENGINE_COLUMNDEF *start_recinfo,
- TMP_ENGINE_COLUMNDEF **recinfo,
+ TMP_ENGINE_COLUMNDEF **recinfo,
ulonglong options);
bool open_tmp_table(TABLE *table);
void setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps);
Regards,
Vicențiu
Follow ups
