[Commits] Rev 2850: Fix LP BUG#680038 in file:///home/tsk/mprog/src/5.3-mwl89-lpb-680038/

timour at askmonty.org timour at askmonty.org
Tue Nov 23 00:01:35 EET 2010


At file:///home/tsk/mprog/src/5.3-mwl89-lpb-680038/

------------------------------------------------------------
revno: 2850
revision-id: timour at askmonty.org-20101122220124-8ap7dfnsfz76w1j7
parent: timour at askmonty.org-20101122143236-cirfc4rdd4lesh7b
committer: timour at askmonty.org
branch nick: 5.3-mwl89-lpb-680038
timestamp: Tue 2010-11-23 00:01:24 +0200
message:
  Fix LP BUG#680038
  
  Analysis:
  Single-row subqueries are not considered expensive and are
  evaluated both during EXPLAIN in to detect errors like
  "Subquery returns more than 1 row", and during optimization to
  perform constant optimization.
  
  The cause for the failed ASSERT is in JOIN::join_free, where we set
    bool full= (!select_lex->uncacheable && !thd->lex->describe);
  Thus for EXPLAIN statements full == FALSE, and as a result the call to
  JOIN::cleanup doesn't call JOIN_TAB::cleanup which should have
  called table->disable_keyread().
  
  Solution:
  Consider all kinds of subquery predicates as expensive.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	2010-11-22 14:32:36 +0000
+++ b/mysql-test/r/subselect4.result	2010-11-22 22:01:24 +0000
@@ -488,6 +488,30 @@ id	select_type	table	type	possible_keys	
 2       DEPENDENT SUBQUERY      SUBQUERY2_t3    ALL     NULL    NULL    NULL    NULL    11      Using where; Using join buffer
 drop table t1, t2, t3;
 #
+# LP BUG#680038 bool close_thread_table(THD*, TABLE**):
+#               Assertion `table->key_read == 0' failed in EXPLAIN
+#
+CREATE TABLE t1 (f1 int,f3 int,f4 int) ;
+INSERT IGNORE INTO t1 VALUES (NULL,1,0);
+CREATE TABLE t2 (f2 int,f4 int,f5 int) ;
+INSERT IGNORE INTO t2 VALUES (8,0,0),(5,0,0);
+CREATE TABLE t3 (f4 int,KEY (f4)) ;
+INSERT IGNORE INTO t3 VALUES (0),(0);
+set @@optimizer_switch='semijoin=off';
+EXPLAIN
+SELECT * FROM t1 WHERE
+(SELECT f2 FROM t2
+WHERE f4 <= ALL
+(SELECT SQ1_t1.f4
+FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
+GROUP BY SQ1_t1.f4));
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    2       Using where
+3       SUBQUERY        SQ1_t1  index   NULL    f4      5       NULL    2       Using index; Using temporary; Using filesort
+3       SUBQUERY        SQ1_t3  index   NULL    f4      5       NULL    2       Using where; Using index; Using join buffer
+drop table t1, t2, t3;
+#
 # BUG#52317: Assertion failing in Field_varstring::store() 
 #           at field.cc:6833
 #

=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test	2010-11-22 14:32:36 +0000
+++ b/mysql-test/t/subselect4.test	2010-11-22 22:01:24 +0000
@@ -451,6 +451,32 @@ GROUP BY t1.f4 ORDER BY t1.f1 LIMIT 10;
 drop table t1, t2, t3;
 
 --echo #
+--echo # LP BUG#680038 bool close_thread_table(THD*, TABLE**):
+--echo #               Assertion `table->key_read == 0' failed in EXPLAIN
+--echo #
+
+CREATE TABLE t1 (f1 int,f3 int,f4 int) ;
+INSERT IGNORE INTO t1 VALUES (NULL,1,0);
+
+CREATE TABLE t2 (f2 int,f4 int,f5 int) ;
+INSERT IGNORE INTO t2 VALUES (8,0,0),(5,0,0);
+
+CREATE TABLE t3 (f4 int,KEY (f4)) ;
+INSERT IGNORE INTO t3 VALUES (0),(0);
+
+set @@optimizer_switch='semijoin=off';
+
+EXPLAIN
+SELECT * FROM t1 WHERE
+(SELECT f2 FROM t2
+ WHERE f4 <= ALL
+       (SELECT SQ1_t1.f4
+        FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
+        GROUP BY SQ1_t1.f4));
+
+drop table t1, t2, t3;
+
+--echo #
 --echo # BUG#52317: Assertion failing in Field_varstring::store() 
 --echo #            at field.cc:6833
 --echo #

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2010-11-05 12:42:58 +0000
+++ b/sql/item_subselect.cc	2010-11-22 22:01:24 +0000
@@ -2381,23 +2381,6 @@ bool Item_in_subselect::init_cond_guards
 }
 
 
-/*
-  Callback to test if an IN predicate is expensive.
-
-  @details
-    The return value affects the behavior of make_cond_for_table().
-
-  @retval TRUE  if the predicate is expensive
-  @retval FALSE otherwise
-*/
-
-bool Item_in_subselect::is_expensive_processor(uchar *arg)
-{
-  /* TIMOUR: TODO: decide on a cost basis whether it is expensive or not. */
-  return TRUE;
-}
-
-
 Item_subselect::trans_res
 Item_allany_subselect::select_transformer(JOIN *join)
 {

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2010-11-05 12:42:58 +0000
+++ b/sql/item_subselect.h	2010-11-22 22:01:24 +0000
@@ -194,6 +194,7 @@ public:
   */
   bool is_evaluated() const;
   bool is_uncacheable() const;
+  bool is_expensive() { return TRUE; }
 
   /*
     Used by max/min subquery to initialize value presence registration
@@ -210,6 +211,16 @@ public:
   {
     return trace_unsupported_by_check_vcol_func_processor("subselect");
   }
+  /**
+    Callback to test if an IN predicate is expensive.
+
+    @notes
+    The return value affects the behavior of make_cond_for_table().
+
+    @retval TRUE  if the predicate is expensive
+    @retval FALSE otherwise
+  */
+  bool is_expensive_processor(uchar *arg) { return TRUE; }
   Item *safe_charset_converter(CHARSET_INFO *tocs);
 
   /**
@@ -484,8 +495,6 @@ public:
   bool init_left_expr_cache();
   /* Inform 'this' that it was computed, and contains a valid result. */
   void set_first_execution() { if (first_execution) first_execution= FALSE; }
-  bool is_expensive_processor(uchar *arg);
-  bool is_expensive() { return TRUE; }
   bool expr_cache_is_needed(THD *thd);
   
   /* 



More information about the commits mailing list