bzr commit into mysql-trunk branch (evgeny.potemkin:3002) Bug#11791677 Bug#11791705

[Evgeny Potemkin]

#At file:///work/bzrroot/11791677-bug/ based on revid:epotemkin@stripped

 3002 Evgeny Potemkin	2011-05-28
      Bug#11791677: ASSERTION FAILED IN JOIN_MATERIALIZE_TABLE IN SQL_SELECT.CC ON 
      NESTED SUBQUERY
      Derived tables materialization mechanism substitutes table's read_first_record
      with the join_materialize_table function to materialize table on the first read,
      after this is done the original read_first_record is restored and called.
      Subqueries in WHERE clause could reinitialize join and restore
      join_materialize_table as the read_first_record function. Assertion is thrown
      on attempt to materialize a derived table second time.
      To avoid this a new materialization callback is added to the JOIN_TAB structure.
      It is called when it's non zero and the table haven't been materialized yet.
      This also fixes the bug#11791705.
     @ mysql-test/r/derived.result
        Added test cases for bugs #11791677 and #11791705.
     @ mysql-test/t/derived.test
        Added test cases for bugs #11791677 and #11791705.
     @ sql/sql_join_cache.cc
        Bug#11791677: ASSERTION FAILED IN JOIN_MATERIALIZE_TABLE IN SQL_SELECT.CC ON 
        NESTED SUBQUERY
        Now JOIN_TAB::materialize_table callback function is called for materializable
        derived tables/views prior to reading them.
     @ sql/sql_select.cc
        Bug#11791677: ASSERTION FAILED IN JOIN_MATERIALIZE_TABLE IN SQL_SELECT.CC ON 
        NESTED SUBQUERY
        Now JOIN_TAB::materialize_table callback is used to call materializing function
        instead of flipping JOIN_TAB::read_first_record.
     @ sql/sql_select.h
        Bug#11791677: ASSERTION FAILED IN JOIN_MATERIALIZE_TABLE IN SQL_SELECT.CC ON 
        NESTED SUBQUERY
        Added materialize_table callback hook to the JOIN_TAB struct.

    modified:
      mysql-test/r/derived.result
      mysql-test/t/derived.test
      sql/sql_join_cache.cc
      sql/sql_select.cc
      sql/sql_select.h
=== modified file 'mysql-test/r/derived.result'
--- a/mysql-test/r/derived.result	2011-02-17 12:14:26 +0000
+++ b/mysql-test/r/derived.result	2011-05-28 08:39:37 +0000
@@ -1328,3 +1328,56 @@ f1	f11	f2	f22	f3	f33
 DROP TABLE t1,t2,t3;
 DROP VIEW v1,v2,v3,v4,v6,v7;
 #
+#
+# Bug#11791677 - ASSERTION FAILED IN JOIN_MATERIALIZE_TABLE IN
+#                SQL_SELECT.CC ON NESTED SUBQUERY 
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_key int(11) DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+INSERT INTO t1 VALUES (10,8,'v'), (29,4,'c');
+CREATE TABLE t2 (  
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_nokey int(11) DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (16,1,'c'), (20,4,'d');
+CREATE TABLE t3 (
+`field1` varchar(1) DEFAULT NULL,
+`field2` int(11) DEFAULT NULL
+);
+INSERT INTO t3 VALUES ('m',6),('c',4);
+SELECT *
+FROM t3
+WHERE (field1, field2) IN (
+SELECT t1.col_varchar_key AS field1,
+t1.col_int_key AS field2
+FROM ( t1 INNER JOIN (
+SELECT t2.*
+FROM t2
+WHERE t2.col_int_nokey < t2.pk ) AS alias2
+ON (alias2.col_varchar_key = t1.col_varchar_key ) )
+GROUP BY field1, field2
+ORDER BY t1.col_int_key, t1 .pk DESC )
+;
+field1	field2
+c	4
+DROP TABLE t1,t2,t3;
+#
+#
+# Bug#11791705 - CRASH IN JOIN_MATERIALIZE_TABLE OR ASSERTION FAIL:
+#                !TAB->SAVE_READ_FIRST_RECORD
+#
+CREATE TABLE t1 (a INTEGER);
+INSERT INTO t1 VALUES (NULL),(NULL);
+SELECT * FROM t1
+WHERE (a, a) NOT IN
+(SELECT * FROM (SELECT 8, 4 UNION SELECT 2, 3) tt) ;
+a
+DROP TABLE t1;
+#

=== modified file 'mysql-test/t/derived.test'
--- a/mysql-test/t/derived.test	2011-02-17 12:14:26 +0000
+++ b/mysql-test/t/derived.test	2011-05-28 08:39:37 +0000
@@ -693,3 +693,64 @@ DROP VIEW v1,v2,v3,v4,v6,v7;
 
 --echo #
 
+--echo #
+--echo # Bug#11791677 - ASSERTION FAILED IN JOIN_MATERIALIZE_TABLE IN
+--echo #                SQL_SELECT.CC ON NESTED SUBQUERY 
+--echo #
+CREATE TABLE t1 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  col_int_key int(11) DEFAULT NULL,
+  col_varchar_key varchar(1) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY col_varchar_key (col_varchar_key,col_int_key)
+);
+
+INSERT INTO t1 VALUES (10,8,'v'), (29,4,'c');
+  
+CREATE TABLE t2 (  
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  col_int_nokey int(11) DEFAULT NULL,
+  col_varchar_key varchar(1) DEFAULT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t2 VALUES (16,1,'c'), (20,4,'d');
+
+CREATE TABLE t3 (
+  `field1` varchar(1) DEFAULT NULL,
+  `field2` int(11) DEFAULT NULL
+);
+
+INSERT INTO t3 VALUES ('m',6),('c',4);
+
+SELECT *
+FROM t3
+WHERE (field1, field2) IN (
+  SELECT t1.col_varchar_key AS field1,
+         t1.col_int_key AS field2
+  FROM ( t1 INNER JOIN (
+      SELECT t2.*
+      FROM t2
+      WHERE t2.col_int_nokey < t2.pk ) AS alias2
+    ON (alias2.col_varchar_key = t1.col_varchar_key ) )
+  GROUP BY field1, field2
+  ORDER BY t1.col_int_key, t1 .pk DESC )
+;
+
+DROP TABLE t1,t2,t3;
+--echo #
+
+--echo #
+--echo # Bug#11791705 - CRASH IN JOIN_MATERIALIZE_TABLE OR ASSERTION FAIL:
+--echo #                !TAB->SAVE_READ_FIRST_RECORD
+--echo #
+CREATE TABLE t1 (a INTEGER);
+INSERT INTO t1 VALUES (NULL),(NULL);
+
+SELECT * FROM t1
+WHERE (a, a) NOT IN
+  (SELECT * FROM (SELECT 8, 4 UNION SELECT 2, 3) tt) ;
+
+DROP TABLE t1; 
+--echo #
+

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2011-02-18 10:46:59 +0000
+++ b/sql/sql_join_cache.cc	2011-05-28 08:39:37 +0000
@@ -1794,6 +1794,12 @@ enum_nested_loop_state JOIN_CACHE_BNL::j
     tab->table->status= 0;
   }
 
+  /* Materialize table prior reading it */
+  if (join_tab->materialize_table &&
+      !join_tab->table->pos_in_table_list->materialized &&
+      (error= (*join_tab->materialize_table)(join_tab)))
+    goto finish;
+
   /* Start retrieving all records of the joined table */
   if ((error= (*join_tab->read_first_record)(join_tab)))
   {
@@ -2297,6 +2303,12 @@ enum_nested_loop_state JOIN_CACHE_BKA::j
   if (!records)
     return NESTED_LOOP_OK;  
                    
+  /* Materialize table prior reading it */
+  if (join_tab->materialize_table &&
+      !join_tab->table->pos_in_table_list->materialized &&
+      (error= (*join_tab->materialize_table)(join_tab)))
+    goto finish;
+
   rc= init_join_matching_records(&seq_funcs, records);
   if (rc != NESTED_LOOP_OK)
     goto finish;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-02-17 12:14:26 +0000
+++ b/sql/sql_select.cc	2011-05-28 08:39:37 +0000
@@ -11414,10 +11414,7 @@ make_join_readinfo(JOIN *join, ulonglong
     // Materialize derived tables prior to accessing them.
     if (tab->table->pos_in_table_list->uses_materialization() &&
         !tab->table->pos_in_table_list->materialized)
-    {
-      tab->save_read_first_record= tab->read_first_record;
-      tab->read_first_record= join_materialize_table;
-    }
+      tab->materialize_table= join_materialize_table;
   }
   join->join_tab[join->tables-1].next_select=0; /* Set by do_select */
 
@@ -17551,7 +17548,7 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
       (*join_tab->next_select)(join,join_tab+1,end_of_records);
     DBUG_RETURN(nls);
   }
-  int error;
+  int error= 0;
   enum_nested_loop_state rc;
   READ_RECORD *info= &join_tab->read_record;
 
@@ -17575,7 +17572,13 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
   }
   join->thd->warning_info->reset_current_row_for_warning();
 
-  error= (*join_tab->read_first_record)(join_tab);
+  /* Materialize table prior reading it */
+  if (join_tab->materialize_table &&
+      !join_tab->table->pos_in_table_list->materialized)
+    error= (*join_tab->materialize_table)(join_tab);
+
+  if (!error)
+    error= (*join_tab->read_first_record)(join_tab);
 
   if (join_tab->keep_current_rowid)
     join_tab->table->file->position(join_tab->table->record[0]);
@@ -18515,11 +18518,7 @@ join_materialize_table(JOIN_TAB *tab)
                                         derived, &mysql_derived_materialize);
   mysql_handle_single_derived(tab->table->in_use->lex,
                               derived, &mysql_derived_cleanup);
-  if (res)
-    return -1;
-  tab->read_first_record= tab->save_read_first_record;
-  tab->save_read_first_record= NULL;
-  return (*tab->read_first_record)(tab);
+  return res ? NESTED_LOOP_ERROR : NESTED_LOOP_OK;
 }
 
 

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-02-18 10:46:59 +0000
+++ b/sql/sql_select.h	2011-05-28 08:39:37 +0000
@@ -264,6 +264,7 @@ typedef struct st_join_table : public Sq
   */
   uint          packed_info;
 
+  READ_RECORD::Setup_func materialize_table;
   READ_RECORD::Setup_func read_first_record;
   Next_select_func next_select;
   READ_RECORD	read_record;
@@ -271,7 +272,6 @@ typedef struct st_join_table : public Sq
     The following two fields are used for a [NOT] IN subquery if it is
     executed by an alternative full table scan when the left operand of
     the subquery predicate is evaluated to NULL.
-    save_read_first_record is also used during derived tables materialization.
   */
   READ_RECORD::Setup_func save_read_first_record;/* to save read_first_record */
   READ_RECORD::Read_func save_read_record;/* to save read_record.read_record */
@@ -453,6 +453,7 @@ st_join_table::st_join_table()
     pre_idx_push_select_cond(NULL),
     info(NULL),
     packed_info(0),
+    materialize_table(NULL),
     read_first_record(NULL),
     next_select(NULL),
     read_record(),


Attachment: [text/bzr-bundle] bzr/[e-mail]