From: Roy Lyseng
Date: May 27 2011 12:01pm
Subject: Re: bzr commit into mysql-trunk branch (evgeny.potemkin:3002) Bug#11791677
 Bug#11791705
List-Archive: http://lists.mysql.com/commits/138322
Message-Id: <4DDF9294.2010003@oracle.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------040103070004010309090809"

--------------040103070004010309090809
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Evgeny,

approved with minor issues discussed on IRC.

Thanks,
Roy

On 26.05.11 20.43, Evgeny Potemkin wrote:
> #At file:///work/bzrroot/11791677-bug/ based on revid:epotemkin@stripped
>
>   3002 Evgeny Potemkin	2011-05-26
>        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-26 18:43:11 +0000
> @@ -1328,3 +1328,70 @@ 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_nokey int(11) DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_time_key time DEFAULT NULL,
> +col_varchar_key varchar(1) DEFAULT NULL,
> +PRIMARY KEY (pk),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=InnoDB;
> +INSERT INTO t1 VALUES
> +(10,7,8,'01:27:35','v'),
> +(29,NULL,4,'22:43:58','c')
> +;
> +CREATE TABLE t2 (
> +pk int(11) NOT NULL AUTO_INCREMENT,
> +col_int_nokey int(11) DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_time_key time DEFAULT NULL,
> +col_varchar_key varchar(1) DEFAULT NULL,
> +PRIMARY KEY (pk),
> +KEY col_int_key (col_int_key),
> +KEY col_time_key (col_time_key),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=InnoDB;
> +INSERT INTO t2 VALUES
> +(16,1,1,'04:56:48','c'),
> +(20,4,2,'18:38:59','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 alias1.col_varchar_key AS field1,
> +alias1.col_int_key AS field2
> +FROM ( t1 AS alias1
> +INNER JOIN (
> +SELECT SQ1_alias1.*
> +FROM t2 AS SQ1_alias1
> +WHERE SQ1_alias1.col_int_nokey<  SQ1_alias1.pk ) AS alias2
> +ON (alias2.col_varchar_key = alias1.col_varchar_key ) )
> +GROUP BY field1, field2
> +ORDER BY alias1.col_time_key, alias1 .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) ENGINE=InnoDB;
> +INSERT INTO t1 VALUES (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-26 18:43:11 +0000
> @@ -693,3 +693,78 @@ 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_nokey int(11) DEFAULT NULL,
> +  col_int_key int(11) DEFAULT NULL,
> +  col_time_key time DEFAULT NULL,
> +  col_varchar_key varchar(1) DEFAULT NULL,
> +  PRIMARY KEY (pk),
> +  KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=InnoDB;
> +
> +INSERT INTO t1 VALUES
> +  (10,7,8,'01:27:35','v'),
> +  (29,NULL,4,'22:43:58','c')
> +;
> +
> +CREATE TABLE t2 (
> +  pk int(11) NOT NULL AUTO_INCREMENT,
> +  col_int_nokey int(11) DEFAULT NULL,
> +  col_int_key int(11) DEFAULT NULL,
> +  col_time_key time DEFAULT NULL,
> +  col_varchar_key varchar(1) DEFAULT NULL,
> +  PRIMARY KEY (pk),
> +  KEY col_int_key (col_int_key),
> +  KEY col_time_key (col_time_key),
> +  KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=InnoDB;
> +
> +INSERT INTO t2 VALUES
> +  (16,1,1,'04:56:48','c'),
> +  (20,4,2,'18:38:59','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 alias1.col_varchar_key AS field1,
> +         alias1.col_int_key AS field2
> +  FROM ( t1 AS alias1
> +    INNER JOIN (
> +      SELECT SQ1_alias1.*
> +      FROM t2 AS SQ1_alias1
> +      WHERE SQ1_alias1.col_int_nokey<  SQ1_alias1.pk ) AS alias2
> +    ON (alias2.col_varchar_key = alias1.col_varchar_key ) )
> +  GROUP BY field1, field2
> +  ORDER BY alias1.col_time_key, alias1 .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) ENGINE=InnoDB;
> +INSERT INTO t1 VALUES (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-26 18:43:11 +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)))
>     {
>
> === 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-26 18:43:11 +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-26 18:43:11 +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;
> @@ -453,6 +454,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(),
>
>
>
>


--------------040103070004010309090809--