From: Jorgen Loland
Date: June 6 2011 10:51am
Subject: Re: bzr commit into mysql-trunk branch (guilhem.bichot:3322) Bug#12612201
List-Archive: http://lists.mysql.com/commits/138686
Message-Id: <4DECB125.9010500@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Hi Guilhem,

I would prefer a simpler test case, but the code looks good. Approved.

On 06/01/2011 05:19 PM, Guilhem Bichot wrote:
> #At file:///home/mysql_src/bzrrepos_new/mysql-next-mr-opt-backporting-wl4800/ based on revid:guilhem.bichot@stripped
>
>   3322 Guilhem Bichot	2011-06-01
>        fix for BUG#12612201 - SEGFAULT IN SUBSELECT_UNIQUESUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE
>       @ sql/sql_derived.cc
>          In mysql_derived_cleanup(), don't clean up SELECT_LEX_UNIT or JOIN, if
>          EXPLAIN or optimizer trace. Cleanup happens later then.
>          This was already the case for some mysql_derived_cleanup() calls
>          in SELECT, this is now extended to UPDATE (because optimizer trace
>          works for UPDATE too). It will also be useful for EXPLAIN UPDATE.
>
>      modified:
>        mysql-test/suite/optimizer_trace/r/optimizer_trace_bugs.result
>        mysql-test/suite/optimizer_trace/t/optimizer_trace_bugs.test
>        sql/sql_base.cc
>        sql/sql_derived.cc
> === modified file 'mysql-test/suite/optimizer_trace/r/optimizer_trace_bugs.result'
> --- a/mysql-test/suite/optimizer_trace/r/optimizer_trace_bugs.result	2011-05-31 13:00:14 +0000
> +++ b/mysql-test/suite/optimizer_trace/r/optimizer_trace_bugs.result	2011-06-01 15:19:10 +0000
> @@ -671,3 +671,113 @@ FROM t1
>     ] /* steps */
>   }	0	0
>   DROP TABLE where_subselect_19033,t1,t2;
> +
> +# BUG#12612201 - SEGFAULT IN
> +# SUBSELECT_UNIQUESUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE
> +
> +CREATE TABLE t1 (
> +pk int(11) NOT NULL AUTO_INCREMENT,
> +col_int_key int(11) DEFAULT NULL,
> +col_varchar_key varchar(1) DEFAULT NULL,
> +col_varchar_nokey varchar(1) DEFAULT NULL,
> +PRIMARY KEY (pk),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +);
> +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_date_key date DEFAULT NULL,
> +col_date_nokey date DEFAULT NULL,
> +col_time_key time DEFAULT NULL,
> +col_time_nokey time DEFAULT NULL,
> +col_datetime_key datetime DEFAULT NULL,
> +col_datetime_nokey datetime DEFAULT NULL,
> +col_varchar_key varchar(1) DEFAULT NULL,
> +col_varchar_nokey varchar(1) DEFAULT NULL,
> +PRIMARY KEY (pk),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +);
> +INSERT INTO t2 VALUES
> +(1,2,4,'2008-12-05','2008-12-05','22:34:09','22:34:09','1900-01-01
> +00:00:00','1900-01-01 00:00:00','v','v');
> +INSERT INTO t2 VALUES
> +(20,6,5,'2004-10-10','2004-10-10','20:58:33','20:58:33','2004-03-27
> +09:32:04','2004-03-27 09:32:04','r','r');
> +CREATE TABLE t3 (
> +pk int(11) NOT NULL AUTO_INCREMENT,
> +col_int_nokey int(11) DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_date_key date DEFAULT NULL,
> +col_date_nokey date DEFAULT NULL,
> +col_time_key time DEFAULT NULL,
> +col_time_nokey time DEFAULT NULL,
> +col_datetime_key datetime DEFAULT NULL,
> +col_datetime_nokey datetime DEFAULT NULL,
> +col_varchar_key varchar(1) DEFAULT NULL,
> +col_varchar_nokey varchar(1) DEFAULT NULL,
> +PRIMARY KEY (pk),
> +KEY col_varchar_key (col_varchar_key,col_int_key)
> +);
> +INSERT INTO t3 VALUES
> +(28,9,NULL,'2007-04-09','2007-04-09','08:46:48','08:46:48','2005-03-24
> +07:33:11','2005-03-24 07:33:11','j','j');
> +INSERT INTO t3 VALUES
> +(29,6,8,'2000-09-20','2000-09-20','14:11:27','14:11:27','2003-06-13
> +23:19:49','2003-06-13 23:19:49','c','c');
> +CREATE TABLE where_updatedelete_20769 select  count(  alias2 . col_varchar_key ) as field1
> +from (
> +(select sq1_alias1 . *
> +from ( t3 as sq1_alias1
> +straight_join t1 as sq1_alias2
> +on (sq1_alias2 . col_varchar_key = sq1_alias1 . col_varchar_key)
> +)
> +where  sq1_alias1 . col_int_key  in (
> +select   c_sq1_alias1 . pk as c_sq1_field1
> +from t2 as c_sq1_alias1
> +)
> +) as alias1
> +left outer join t1 as alias2
> +on (alias2 . col_varchar_key = alias1 . col_varchar_key  )
> +)
> +where (  alias2 . col_varchar_key  in (
> +select   sq2_alias1 . col_varchar_nokey as sq2_field1
> +from t2 as sq2_alias1
> +where  sq2_alias1 . col_int_key  in (
> +select distinct  c_sq2_alias1 . col_int_key as c_sq2_field1
> +from t3 as c_sq2_alias1
> +)
> +) )
> +or alias1 . col_int_key = 2
> +and alias2 . col_varchar_nokey<= alias1 . col_varchar_nokey
> +order by alias1 . col_varchar_key  , field1
> +;
> +UPDATE where_updatedelete_20769 SET field1 = ( select  count(  alias2 . col_varchar_key ) as field1
> +from (
> +(select sq1_alias1 . *
> +from ( t3 as sq1_alias1
> +straight_join t1 as sq1_alias2
> +on (sq1_alias2 . col_varchar_key = sq1_alias1 . col_varchar_key)
> +)
> +where  sq1_alias1 . col_int_key  in (
> +select   c_sq1_alias1 . pk as c_sq1_field1
> +from t2 as c_sq1_alias1
> +)
> +) as alias1
> +left outer join t1 as alias2
> +on (alias2 . col_varchar_key = alias1 . col_varchar_key  )
> +)
> +where (  alias2 . col_varchar_key  in (
> +select   sq2_alias1 . col_varchar_nokey as sq2_field1
> +from t2 as sq2_alias1
> +where  sq2_alias1 . col_int_key  in (
> +select distinct  c_sq2_alias1 . col_int_key as c_sq2_field1
> +from t3 as c_sq2_alias1
> +)
> +) )
> +or alias1 . col_int_key = 2
> +and alias2 . col_varchar_nokey<= alias1 . col_varchar_nokey
> +order by alias1 . col_varchar_key  , field1
> + );
> +DROP TABLE where_updatedelete_20769;
> +DROP TABLE t1,t2,t3;
>
> === modified file 'mysql-test/suite/optimizer_trace/t/optimizer_trace_bugs.test'
> --- a/mysql-test/suite/optimizer_trace/t/optimizer_trace_bugs.test	2011-05-31 13:00:14 +0000
> +++ b/mysql-test/suite/optimizer_trace/t/optimizer_trace_bugs.test	2011-06-01 15:19:10 +0000
> @@ -153,3 +153,99 @@ WHERE field1 IN
>   SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
>
>   DROP TABLE where_subselect_19033,t1,t2;
> +
> +--echo
> +--echo # BUG#12612201 - SEGFAULT IN
> +--echo # SUBSELECT_UNIQUESUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE
> +--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,
> +  col_varchar_nokey varchar(1) DEFAULT NULL,
> +  PRIMARY KEY (pk),
> +  KEY col_varchar_key (col_varchar_key,col_int_key)
> +);
> +
> +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_date_key date DEFAULT NULL,
> +  col_date_nokey date DEFAULT NULL,
> +  col_time_key time DEFAULT NULL,
> +  col_time_nokey time DEFAULT NULL,
> +  col_datetime_key datetime DEFAULT NULL,
> +  col_datetime_nokey datetime DEFAULT NULL,
> +  col_varchar_key varchar(1) DEFAULT NULL,
> +  col_varchar_nokey varchar(1) DEFAULT NULL,
> +  PRIMARY KEY (pk),
> +  KEY col_varchar_key (col_varchar_key,col_int_key)
> +);
> +
> +INSERT INTO t2 VALUES
> +(1,2,4,'2008-12-05','2008-12-05','22:34:09','22:34:09','1900-01-01
> +00:00:00','1900-01-01 00:00:00','v','v');
> +INSERT INTO t2 VALUES
> +(20,6,5,'2004-10-10','2004-10-10','20:58:33','20:58:33','2004-03-27
> +09:32:04','2004-03-27 09:32:04','r','r');
> +
> +CREATE TABLE t3 (
> +  pk int(11) NOT NULL AUTO_INCREMENT,
> +  col_int_nokey int(11) DEFAULT NULL,
> +  col_int_key int(11) DEFAULT NULL,
> +  col_date_key date DEFAULT NULL,
> +  col_date_nokey date DEFAULT NULL,
> +  col_time_key time DEFAULT NULL,
> +  col_time_nokey time DEFAULT NULL,
> +  col_datetime_key datetime DEFAULT NULL,
> +  col_datetime_nokey datetime DEFAULT NULL,
> +  col_varchar_key varchar(1) DEFAULT NULL,
> +  col_varchar_nokey varchar(1) DEFAULT NULL,
> +  PRIMARY KEY (pk),
> +  KEY col_varchar_key (col_varchar_key,col_int_key)
> +);
> +
> +INSERT INTO t3 VALUES
> +(28,9,NULL,'2007-04-09','2007-04-09','08:46:48','08:46:48','2005-03-24
> +07:33:11','2005-03-24 07:33:11','j','j');
> +INSERT INTO t3 VALUES
> +(29,6,8,'2000-09-20','2000-09-20','14:11:27','14:11:27','2003-06-13
> +23:19:49','2003-06-13 23:19:49','c','c');
> +
> +let $query=
> +  select  count(  alias2 . col_varchar_key ) as field1
> +  from (
> +    (select sq1_alias1 . *
> +     from ( t3 as sq1_alias1
> +       straight_join t1 as sq1_alias2
> +       on (sq1_alias2 . col_varchar_key = sq1_alias1 . col_varchar_key)
> +     )
> +     where  sq1_alias1 . col_int_key  in (
> +       select   c_sq1_alias1 . pk as c_sq1_field1
> +       from t2 as c_sq1_alias1
> +     )
> +    ) as alias1
> +      left outer join t1 as alias2
> +      on (alias2 . col_varchar_key = alias1 . col_varchar_key  )
> +  )
> +  where (  alias2 . col_varchar_key  in (
> +    select   sq2_alias1 . col_varchar_nokey as sq2_field1
> +    from t2 as sq2_alias1
> +    where  sq2_alias1 . col_int_key  in (
> +      select distinct  c_sq2_alias1 . col_int_key as c_sq2_field1
> +      from t3 as c_sq2_alias1
> +    )
> +  ) )
> +    or alias1 . col_int_key = 2
> +      and alias2 . col_varchar_nokey<= alias1 . col_varchar_nokey
> +  order by alias1 . col_varchar_key  , field1
> +;
> +
> +eval CREATE TABLE where_updatedelete_20769 $query;
> +
> +eval UPDATE where_updatedelete_20769 SET field1 = ( $query );
> +
> +DROP TABLE where_updatedelete_20769;
> +DROP TABLE t1,t2,t3;
>
> === modified file 'sql/sql_base.cc'
> --- a/sql/sql_base.cc	2011-05-24 12:46:22 +0000
> +++ b/sql/sql_base.cc	2011-06-01 15:19:10 +0000
> @@ -5564,16 +5564,7 @@ bool open_and_lock_tables(THD *thd, TABL
>         mysql_handle_derived(thd->lex,&mysql_derived_cleanup);
>         goto err;
>       }
> -    if (!(thd->lex->describe
> -#ifdef OPTIMIZER_TRACE
> -          /*
> -            Printing the expanded query in optimizer trace requires
> -            non-destroyed JOINs for subquery engines.
> -          */
> -          || thd->opt_trace.support_I_S()
> -#endif
> -          ))
> -      mysql_handle_derived(thd->lex,&mysql_derived_cleanup);
> +    mysql_handle_derived(thd->lex,&mysql_derived_cleanup);
>     }
>
>     DBUG_RETURN(FALSE);
>
> === modified file 'sql/sql_derived.cc'
> --- a/sql/sql_derived.cc	2011-05-13 14:02:31 +0000
> +++ b/sql/sql_derived.cc	2011-06-01 15:19:10 +0000
> @@ -318,13 +318,28 @@ bool mysql_derived_filling(THD *thd, LEX
>
>   /**
>      Cleans up the SELECT_LEX_UNIT for the derived table (if any).
> +   Exception: the function does nothing if EXPLAIN or optimizer trace is used,
> +   because these need units to exist longer, so that they can be explained or
> +   printed; they are then cleaned up in st_select_lex_unit::cleanup() at end
> +   of mysql_execute_command().
>   */
>
>   bool mysql_derived_cleanup(THD *thd, LEX *lex, TABLE_LIST *derived)
>   {
>     DBUG_ENTER("mysql_derived_cleanup");
> -  SELECT_LEX_UNIT *unit= derived->derived;
> -  if (unit)
> -    unit->cleanup();
> +  if (!lex->describe
> +#ifdef OPTIMIZER_TRACE
> +          /*
> +            Printing the expanded query in optimizer trace requires
> +            non-destroyed JOINs for subquery engines.
> +          */
> +&&  !thd->opt_trace.support_I_S()
> +#endif
> +      )
> +  {
> +    SELECT_LEX_UNIT *unit= derived->derived;
> +    if (unit)
> +      unit->cleanup();
> +  }
>     DBUG_RETURN(false);
>   }
>
>
>
>
>

-- 
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway