maria-developers team mailing list archive

[timour@xxxxxxxxxxxx]

#At file:///home/tsk/mprog/src/5.3-subqueries/ based on revid:timour@xxxxxxxxxxxx-20100402142706-klcgi39tjzhvy22z

 2789 timour@xxxxxxxxxxxx	2010-04-06
      Fixed a problem where the temp table of a materialized subquery
      was not cleaned up between PS re-executions. The reason was two-fold:
      - a merge with mysql-6.0 missed select_union::cleanup() that should
        have cleaned up the temp table, and
      - the subclass of select_union used by materialization didn't call
        the base class cleanup() method.

    modified:
      mysql-test/r/subselect_mat.result
      mysql-test/t/subselect_mat.test
      sql/sql_class.cc
      sql/sql_class.h
      sql/sql_union.cc
=== modified file 'mysql-test/r/subselect_mat.result'
--- a/mysql-test/r/subselect_mat.result	2010-03-29 14:04:35 +0000
+++ b/mysql-test/r/subselect_mat.result	2010-04-05 21:15:15 +0000
@@ -1246,3 +1246,29 @@ i
 4
 set session optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
+create table t0 (a int);
+insert into t0 values (0),(1),(2);
+create table t1 (a int);
+insert into t1 values (0),(1),(2);
+explain select a, a in (select a from t1) from t0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	3	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
+select a, a in (select a from t1) from t0;
+a	a in (select a from t1)
+0	1
+1	1
+2	1
+prepare s from 'select a, a in (select a from t1) from t0';
+execute s;
+a	a in (select a from t1)
+0	1
+1	1
+2	1
+update t1 set a=123;
+execute s;
+a	a in (select a from t1)
+0	0
+1	0
+2	0
+drop table t0, t1;

=== modified file 'mysql-test/t/subselect_mat.test'
--- a/mysql-test/t/subselect_mat.test	2010-03-13 20:04:52 +0000
+++ b/mysql-test/t/subselect_mat.test	2010-04-05 21:15:15 +0000
@@ -905,3 +905,19 @@ select * from t1 where t1.i in (select t
 set session optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
 
+#
+# Test that the contentes of the temp table of a materialized subquery is
+# cleanup up between PS reexecutions.
+#
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2);
+create table t1 (a int);
+insert into t1 values (0),(1),(2);
+explain select a, a in (select a from t1) from t0;
+select a, a in (select a from t1) from t0;
+prepare s from 'select a, a in (select a from t1) from t0';
+execute s;
+update t1 set a=123;
+execute s;
+drop table t0, t1;

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2010-03-20 12:01:47 +0000
+++ b/sql/sql_class.cc	2010-04-05 21:15:15 +0000
@@ -2942,8 +2942,7 @@ bool select_dumpvar::send_eof()
 }
 
 
-bool
-select_materialize_with_stats::
+bool select_materialize_with_stats::
 create_result_table(THD *thd_arg, List<Item> *column_types,
                     bool is_union_distinct, ulonglong options,
                     const char *table_alias, bool bit_fields_as_long)
@@ -2962,14 +2961,29 @@ create_result_table(THD *thd_arg, List<I
   if (!stat)
     return TRUE;
 
-  cleanup();
-
+  reset();
   table->file->extra(HA_EXTRA_WRITE_CACHE);
   table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
   return FALSE;
 }
 
 
+void select_materialize_with_stats::reset()
+{
+  memset(col_stat, 0, table->s->fields * sizeof(Column_statistics));
+  max_nulls_in_row= 0;
+  count_rows= 0;
+}
+
+
+void select_materialize_with_stats::cleanup()
+{
+  reset();
+  select_union::cleanup();
+}
+
+
+
 /**
   Override select_union::send_data to analyze each row for NULLs and to
   update null_statistics before sending data to the client.

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2010-03-20 12:01:47 +0000
+++ b/sql/sql_class.h	2010-04-05 21:15:15 +0000
@@ -2820,7 +2820,7 @@ public:
   bool send_data(List<Item> &items);
   bool send_eof();
   bool flush();
-
+  void cleanup();
   virtual bool create_result_table(THD *thd, List<Item> *column_types,
                                    bool is_distinct, ulonglong options,
                                    const char *alias, bool bit_fields_as_long);
@@ -2883,6 +2883,9 @@ protected:
   */
   ha_rows count_rows;
 
+protected:
+  void reset();
+
 public:
   select_materialize_with_stats() {}
   virtual bool create_result_table(THD *thd, List<Item> *column_types,
@@ -2890,12 +2893,7 @@ public:
                                    const char *alias, bool bit_fields_as_long);
   bool init_result_table(ulonglong select_options);
   bool send_data(List<Item> &items);
-  void cleanup()
-  {
-    memset(col_stat, 0, table->s->fields * sizeof(Column_statistics));
-    max_nulls_in_row= 0;
-    count_rows= 0;
-  }
+  void cleanup();
   ha_rows get_null_count_of_col(uint idx)
   {
     DBUG_ASSERT(idx < table->s->fields);

=== modified file 'sql/sql_union.cc'
--- a/sql/sql_union.cc	2010-03-20 12:01:47 +0000
+++ b/sql/sql_union.cc	2010-04-05 21:15:15 +0000
@@ -136,6 +136,22 @@ select_union::create_result_table(THD *t
 }
 
 
+/**
+  Reset and empty the temporary table that stores the materialized query result.
+
+  @note The cleanup performed here is exactly the same as for the two temp
+  tables of JOIN - exec_tmp_table_[1 | 2].
+*/
+
+void select_union::cleanup()
+{
+  table->file->extra(HA_EXTRA_RESET_STATE);
+  table->file->ha_delete_all_rows();
+  free_io_cache(table);
+  filesort_free_buffers(table,0);
+}
+
+
 /*
   initialization procedures before fake_select_lex preparation()
 

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: timour@xxxxxxxxxxxx-20100405211515-istsgehaz7zafg0l
# target_branch: file:///home/tsk/mprog/src/5.3-subqueries/
# testament_sha1: 91cce17bb36ec18af03b182d0e656b4b48665e0a
# timestamp: 2010-04-06 00:15:19 +0300
# source_branch: file:///home/tsk/mprog/src/5.3-mwl68/
# base_revision_id: timour@xxxxxxxxxxxx-20100402142706-\
#   klcgi39tjzhvy22z
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWWG2/NsABRDfgGAwWPf//3t3
/mq////+YAqr57s1ob7AAAtzuih1oUJCQ1oUoSkyRoppiek00DQbUGgPUAA00ANNNMT1Cp6p5Rpt
QaNNMgaBowQ00ANBoA0Bkw4BhGE0xDAIBkAMI0yZMIwENBKaSE00gyekZpNDQ0MmJoAAAAGgBFIR
U9o0ZCGmkb1TaTT1NBoNAAAAAASSBGgATTQhlMEmyNTI0EwmIAZD1NK7ywLNYUUUujNgYBX9iPx3
vi/f7h5VEpNt1uuxcuIKoLilFC+AzK/juI2FIg1yrBemGJB/NCQAeppBH06VA8ZCyVlrSdbnHbuX
NngyyZnbOhTtLJUSSVbPGAqckyPKfHYhLyhKl7qKaWCup1sbnwaupvVeoJ4g/0bgwKVrMEOW8N5M
SMJCEmSGWGTGeoGSOaq7aEbRQs2Ihi05o1IOc4tUwZ8mm0FlcyMKtHDXFucE8yBniQObtgBBpHgQ
e9mKeMCuFbnwnzkiUP25D3nQXegfNyBmCwJTmzQJDUY/rKhFRSMf8ejCdn9KGdYToROiRmrF+kRo
pahWwKWMRiNgcQIHHYLJVIhDFzqiDSiFwjc6qLZz3xvrhpURouk2WpkWp+NMOPZCeh2KJdCtFIWT
dtu2nTqLLbQhFPimifdERZp2RBN8GN5vw4AzDZs6AdAOzueEWEXUYrH7E4TD7AeDwi3a34+QVBRD
wr+jKVwq6VWA6NDQ5DMcSlhg5iRsFOLBy8lufz6T+coEmO1a8zvMKSQ9Vij41Xfq1edpPpED5kkU
i5b9hfcjeimjVFlCn0+C3IwgWMzFCEjcgR03Lga7kmQUdRW5gRdZhkhDDQCQQMhuNVrUVwFYQROR
IQlE2QtCTQ5DdXt+Lh+R2BMT7sAZ5Ud5gXHn0O4S4KpqfQrXEGDBnEhyYP2iWvQcQtQUsgJjBuGD
AyHG47aonBJRC6VD15kIcN/ro293UciEz8rioi9GeAsE/MMVeUiTJKqpkzKBVBFFhaS6aDS6m2DU
fabGIsw9yJFp73pWFwxoqSpdYYbo56v5BRksGEJlmtYCTLEv27kxQaFJYNRywrhxUyCpGCkm5QGC
hFADplZeYFJ26DzcRWJouZi+yBXeXnKQYFUX4mfbKAvkSI2W0krRjQ7KiZOuJkRrrCo3HcnHcZLz
NSYydA5JszStqLWm3bpYPRQ9XPcJMLeTNS81ykFDBG2hzoUv1qoU4FnAmRNwqS8rKyBMoMD/sFAr
eHblLCl9jncDG9Jfbf2HKkiJewsVVAlfBEj5KjE3xV5I1vHFhIoD95FhEyIjHDeUUBKvC5YWOhC2
y65dVFQ8o41cSAxN1fErKLaR5kOKbzg+5Wsb0sTMb93KEsKylZFgR3F4XklNEsT4L89sNSh2Riaj
zW3EzOrlNJ5UOPmzHlpA0O02+wSlbvbexlvjwv8SF6Ik2a0zM5FUS+2m0jUOGMIPOoiZHNY75TLw
6sSFcdnhoMTILIYqLiQRLokwZzOM9qDA0KSwzGWSozLVWx9B700QffeuRb0qqyjMCuw8EENUJZKJ
EZfdETKQEFJD38REhjZRpwp2G1E6V0TpKuwOCSxrPYRQWutCiorSMQZW3hJYIOQDZoMOT7yWUMxE
c52W91Qe+O/TU1JIslbAISgfhjJQMjoyivVyyTRsK+GdHTFBH04QIYk/OZZqvv+4Xb4CgLtXqcI6
QQfg0Y/m6mApMhz6M+XxSKoYLHY8mpDWwOwICcDUd8yA2noOcw1PrMSJoHvLCr7T0PwsPrNRBA32
cMM/gXGR9pkRLhKg1A4n3gW9PyCXAWVO8rN7INtACMfqMPkkijAD+VSrcBsRByWcA/jKHBwl7sDm
dRdSfpBQPSQQwVnEXGnyBx4iZsLXEgYdaXBM22Pl4SQ8WcskL9GF8mXcdujgapik0PA1Nhxzuu/E
Wh6zgUmd52rDXlMdXxtE4PzGZhoeT1vB8zESUzQcK4jbKA8c5OBkdaOuexebzyMypKkoS/Q1IV22
iTiJzKiY43ZWmxpEXFV3ludMh/h4d/PycRR5OcM4kDkBnGcHg/hceICJMViK/QQKxmWiyYS8UIpz
F2w4qOR4FRR1sGopkuYt2bPcJmgBJYG72kUm8nZki2SXXbisnBlK9QacZkIfs5hdWGRF5L60dMH5
laI9acgxYpdDQ7J99iIpM7obPCQQStlXevocGUaz4UIdEKkEX+JAFmd4aexxNI/jtahiqjNMy70G
J0Nwww8ius5PMCJxHngHQ0IpennUWFouTxLqWGn+EMb0cErw593KAbvJwguMIxVNAu8azgoaBMBb
ZpvvHQgsbTJtaHLxc0iCeZN0VUC1e9Z9BJ3cwzJQ3R8YU21qBwEoh3L6THQCabduop6xchLlupkU
EBTPAv4HMmkDFIIzEcizqAvRAD06PfG2nIcyYQ4cQN4LnC4S2B+h++dSGEzbHu1JkDxXPzHlRauZ
aviZ8mA6i1vVOTgJzymXCS6AhKLN0UGqcnhLgLHbVp3MyQDEERCRAxWpNFldFJ4jAC4oSLC2AsHP
CvSzm7oeqKAtq2wEx1uTBWk9x/P1YPbUdyvVldy8t6SJiC8Lse8T8E1LTPecL9i4lYIYcOGOLJJ2
OpQLgezGAxIChw5qrUqfq3Ur+xkqgqAvRN7ngyZkDkkccS1BFbjWyp4d7mzUn+9tRoTNXtG69wy/
7GgT2mVwhXqwFMipudBbNAHUjmVJhjpRjrld2FDdttEKw1EtDBiIkhkBxedDXUZHf39yWNR2ExVF
NgZL6TofScinXy3FsF7SzLIZ8ckS1zWXDf4scWSUTPFUHWsNlp3cO/xOpaCDEiBU0KBqjsDmO5Vy
ErynfJdE3Li5HJk+8upSQySrbzMCAb1zUS6nFAT+Ygh2TG4oRUwzJkTubVGbdkCpbhLOKOJDyS6t
GOuSshNO0wTSqAsD93Ogo3XdkPRB/c7KGbqEy4sE0dMQeYIua8S2JD0RrzbptNYNSVaVTXjW0nbz
jsiIiPC9NvnIiBmHEgJFnNyVqH4gXOEmpYnWwzUBL2FWKSgtrOYRNG63SRqEHQIGDoC2vBMyA3e2
BVUi1BU7YQ56JzQdTMysaIrusKxTvhVQiu6hJ3gsCIht4OQp/FVW4kTU1N45JkW4jfFTxzcYhahD
TSYieJgy1iyXhz5y8rwrVJd437rW9o7S0R44nv7A4B3a3zmHlKBclKlZEGYDwKd8hBG0Vh5r1nrI
hRUrahvnv4AqFcvn9FXjjGVh3jusbRPK3Ny582/C2QaSFPNF5GDqtkZJAMuVlUM6EzjLHHCso9DV
QZzSSXASvgMheeZNU2VdR19/DiuLL/4u5IpwoSDDbfm2