MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Oystein.Grovlen Date:April 7 2010 12:04pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (oystein.grovlen:3069)
Bug#49630
View as plain text  
#At file:///home/oysteing/mysql/mysql-next-mr-opt-backporting/ based on revid:oystein.grovlen@stripped

 3069 oystein.grovlen@stripped	2010-04-07
      Bug#49630 "Segfault in select_describe() with double 
                 nested subquery and materialization"
      
      (Backporting of revid:jorgen.loland@stripped)
      
      If a JOIN contains const tables, make_join_select() will 
      evaluate the conditions in the WHERE clause. If the WHERE 
      clause contains a subquery, the process of evaluating 
      the condition involves optimize() and exec(). 
      
      Calling optimize() and exec() on a subselect may require the
      use of temp tables, in which case the original JOIN query
      execution plan will be replaced by a simple scan of the 
      temp table. 
      
      To be able to describe the query plan, the original query 
      layout needs to be saved. This was not done for materialized
      subqueries. The fix is to make materialized subqueries save 
      the original join query layout if it is needed by EXPLAIN.
     @ mysql-test/r/subselect4.result
        Added test for BUG#49630
     @ mysql-test/t/subselect4.test
        Added test for BUG#49630
     @ sql/item_subselect.cc
        Make subselect_hash_sj_engine::exec save JOIN layout if needed by explain. Added function subselect_single_select_engine::save_join_if_explain() with code common to single_select_engine and hash_sj_engine
     @ sql/item_subselect.h
        Add function subselect_single_select_engine::save_join_if_explain() that saves the JOIN layout if needed by explain

    modified:
      mysql-test/r/subselect4.result
      mysql-test/t/subselect4.test
      sql/item_subselect.cc
      sql/item_subselect.h
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	2010-03-11 10:29:10 +0000
+++ b/mysql-test/r/subselect4.result	2010-04-07 12:04:27 +0000
@@ -78,3 +78,34 @@ id	select_type	table	type	possible_keys	
 
 DROP TABLE t1;
 End of 5.5 tests.
+#
+# BUG#49630: Segfault in select_describe() with double 
+#            nested subquery and materialization
+#
+CREATE TABLE t1 (t1i int);
+CREATE TABLE t2 (t2i int);
+CREATE TABLE t3 (t3i int);
+CREATE TABLE t4 (t4i int);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t3 VALUES (1),(2);
+INSERT INTO t4 VALUES (1),(2);
+
+EXPLAIN 
+SELECT t1i
+FROM t1 JOIN t4 ON t1i=t4i  
+WHERE (t1i)  IN (  
+SELECT t2i
+FROM t2  
+WHERE (t2i)  IN (  
+SELECT t3i
+FROM t3  
+GROUP BY t3i
+)  
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+DROP TABLE t1,t2,t3,t4;

=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test	2010-03-11 10:29:10 +0000
+++ b/mysql-test/t/subselect4.test	2010-04-07 12:04:27 +0000
@@ -80,3 +80,34 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (S
 DROP TABLE t1;
 
 --echo End of 5.5 tests.
+
+--echo #
+--echo # BUG#49630: Segfault in select_describe() with double 
+--echo #            nested subquery and materialization
+--echo #
+
+CREATE TABLE t1 (t1i int);
+CREATE TABLE t2 (t2i int);
+CREATE TABLE t3 (t3i int);
+CREATE TABLE t4 (t4i int);
+
+INSERT INTO t1 VALUES (1); # Note: t1 must be const table
+INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t3 VALUES (1),(2);
+INSERT INTO t4 VALUES (1),(2);
+
+--echo
+EXPLAIN 
+SELECT t1i
+FROM t1 JOIN t4 ON t1i=t4i  
+WHERE (t1i)  IN (  
+   SELECT t2i
+   FROM t2  
+   WHERE (t2i)  IN (  
+     SELECT t3i
+     FROM t3  
+     GROUP BY t3i
+     )  
+ );
+
+DROP TABLE t1,t2,t3,t4;

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2010-03-25 11:08:24 +0000
+++ b/sql/item_subselect.cc	2010-04-07 12:04:27 +0000
@@ -1,4 +1,4 @@
-/* Copyright (C) 2000 MySQL AB
+/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -2246,21 +2246,8 @@ int subselect_single_select_engine::exec
       thd->lex->current_select= save_select;
       DBUG_RETURN(join->error ? join->error : 1);
     }
-    if (!select_lex->uncacheable && thd->lex->describe && 
-        !(join->select_options & SELECT_DESCRIBE) && 
-        join->need_tmp && item->const_item())
-    {
-      /*
-        Force join->join_tmp creation, because this subquery will be replaced
-        by a simple select from the materialization temp table by optimize()
-        called by EXPLAIN and we need to preserve the initial query structure
-        so we can display it.
-       */
-      select_lex->uncacheable|= UNCACHEABLE_EXPLAIN;
-      select_lex->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN;
-      if (join->init_save_join_tab())
-        DBUG_RETURN(1);                        /* purecov: inspected */
-    }
+    if (save_join_if_explain())
+      DBUG_RETURN(1);                        /* purecov: inspected */
     if (item->engine_changed)
     {
       DBUG_RETURN(1);
@@ -2775,6 +2762,51 @@ table_map subselect_engine::calc_const_t
   return map;
 }
 
+/**
+  Save the JOIN to join->tmp_join if it is needed by EXPLAIN to
+  display the query plan.
+
+  @retval
+    FALSE OK
+  @retval
+    TRUE  error
+*/
+bool 
+subselect_single_select_engine::save_join_if_explain()
+{
+  /*
+    Save this JOIN to join->tmp_join since the original layout will be
+    replaced when JOIN::exec() calls make_simple_join() if:
+     1) We are executing an EXPLAIN query
+     2) An uncacheable flag has not been set for the select_lex. If
+        set, JOIN::optimize() has already saved the JOIN
+     3) Call does not come from select_describe()). If it does,
+        JOIN::exec() will not call make_simple_join() and the JOIN we
+        plan to save will not be replaced anyway.
+     4) A temp table is needed. This is what triggers JOIN::exec() to
+        make a replacement JOIN by calling make_simple_join(). 
+     5) The Item_subselect is cacheable
+  */
+  if (thd->lex->describe &&                              // 1
+      !select_lex->uncacheable &&                        // 2
+      !(join->select_options & SELECT_DESCRIBE) &&       // 3
+      join->need_tmp &&                                  // 4
+      item->const_item())                                // 5
+  {
+    /*
+      Save this JOIN to join->tmp_join since the original layout will
+      be replaced when JOIN::exec() calls make_simple_join() due to
+      need_tmp==TRUE. The original layout is needed so we can describe
+      the query. No need to do this if uncacheable != 0 since in this
+      case the JOIN has already been saved during JOIN::optimize()
+    */
+    select_lex->uncacheable|= UNCACHEABLE_EXPLAIN;
+    select_lex->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN;
+    if (join->init_save_join_tab())
+      return TRUE;
+  }
+  return FALSE;
+}
 
 table_map subselect_single_select_engine::upper_select_const_tables()
 {
@@ -3269,6 +3301,10 @@ int subselect_hash_sj_engine::exec()
     thd->lex->current_select= materialize_engine->select_lex;
     if ((res= materialize_join->optimize()))
       goto err; /* purecov: inspected */
+
+    if (materialize_engine->save_join_if_explain())
+      goto err;
+
     materialize_join->exec();
     if ((res= test(materialize_join->error || thd->is_fatal_error)))
       goto err;

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2010-03-18 13:48:54 +0000
+++ b/sql/item_subselect.h	2010-04-07 12:04:27 +0000
@@ -1,7 +1,7 @@
 #ifndef ITEM_SUBSELECT_INCLUDED
 #define ITEM_SUBSELECT_INCLUDED
 
-/* Copyright (C) 2000 MySQL AB
+/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -521,6 +521,7 @@ public:
   bool is_executed() const { return executed; }
   bool no_rows();
   virtual enum_engine_type engine_type() { return SINGLE_SELECT_ENGINE; }
+  bool save_join_if_explain(); 
 
   friend class subselect_hash_sj_engine;
   friend class Item_in_subselect;


Attachment: [text/bzr-bundle]
Thread
bzr commit into mysql-next-mr-bugfixing branch (oystein.grovlen:3069)Bug#49630Oystein.Grovlen7 Apr