[Commits] Rev 2848: Fixed LP BUG#675981 in file:///home/tsk/mprog/src/5.3-mwl89-lpb-675981/

timour at askmonty.org timour at askmonty.org
Mon Nov 22 11:07:53 EET 2010


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

------------------------------------------------------------
revno: 2848
revision-id: timour at askmonty.org-20101122090745-gxi7sdbdy2u9fkra
parent: timour at askmonty.org-20101119150148-8ysltmy5s1njl40l
committer: timour at askmonty.org
branch nick: 5.3-mwl89-lpb-675981
timestamp: Mon 2010-11-22 11:07:45 +0200
message:
  Fixed LP BUG#675981
  
  Cause:
  The optimize() phase for the subquery selected to use join buffering via setting
  JOIN_TAB::next_select= sub_select_cache in make_join_readinfo, however, the call
  to check_join_cache_usage() from make_join_readinfo didn't create the corresponding
  JOIN_CACHE_BNL object because of the condition:
      if ((options & SELECT_DESCRIBE) ||
          (((tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache))) &&
           !tab->cache->init()))
  Since EXPLAIN for subqueries runs regular execution, the constant predicates that
  were delayed to be evaluated at the exec() phase, were evaluated during EXPLAIN.
  As a result the outer JOIN::exec called JOIN::exec for the subquery, while the
  subquery execution plan was no properly created, which resulted in an failed ASSERT.
  
  Fix:
  The patch blocks evaluation of constant expensive conditions during EXPLAIN. Notice
  that these conditions are "constant" with respect to the outer query, thus in
  general they could be arbitrarily expensive, which may result in very slow EXPLAINs.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	2010-11-19 15:01:48 +0000
+++ b/mysql-test/r/subselect4.result	2010-11-22 09:07:45 +0000
@@ -437,6 +437,25 @@ SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 W
 c1      c1
 drop table t1, t2, t3, t4;
 #
+# LP BUG#675981 Assertion `cache != __null' failed in sub_select_cache()
+#               on EXPLAIN
+#
+CREATE TABLE t1 (f1 int,f2 int) ;
+INSERT IGNORE INTO t1 VALUES ('2','5'),('2',NULL);
+CREATE TABLE t2 (f1 int, f5 int) ;
+INSERT IGNORE INTO t2 VALUES (1,0);
+CREATE TABLE t3 (f4 int) ;
+INSERT IGNORE INTO t3 VALUES (0),(0);
+set @@optimizer_switch='in_to_exists=on,materialization=off,semijoin=off';
+EXPLAIN
+SELECT * FROM t2
+WHERE f1 IN (SELECT t1.f2 FROM t1 JOIN t3 ON t3.f4);
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
+2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       Using where; 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-19 15:01:48 +0000
+++ b/mysql-test/t/subselect4.test	2010-11-22 09:07:45 +0000
@@ -398,6 +398,27 @@ SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 W
 drop table t1, t2, t3, t4;
 
 --echo #
+--echo # LP BUG#675981 Assertion `cache != __null' failed in sub_select_cache()
+--echo #               on EXPLAIN
+--echo #
+
+CREATE TABLE t1 (f1 int,f2 int) ;
+INSERT IGNORE INTO t1 VALUES ('2','5'),('2',NULL);
+
+CREATE TABLE t2 (f1 int, f5 int) ;
+INSERT IGNORE INTO t2 VALUES (1,0);
+
+CREATE TABLE t3 (f4 int) ;
+INSERT IGNORE INTO t3 VALUES (0),(0);
+
+set @@optimizer_switch='in_to_exists=on,materialization=off,semijoin=off';
+EXPLAIN
+SELECT * FROM t2
+WHERE f1 IN (SELECT t1.f2 FROM t1 JOIN t3 ON t3.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/sql_select.cc'
--- a/sql/sql_select.cc	2010-11-19 10:54:15 +0000
+++ b/sql/sql_select.cc	2010-11-22 09:07:45 +0000
@@ -1821,7 +1821,14 @@ JOIN::exec()
   if (tables)
     thd->limit_found_rows= 0;
 
-  if (exec_const_cond && !exec_const_cond->val_int())
+  /*
+    Evaluate expensive constant conditions that were not evaluated during
+    optimization. Do not evaluate them for EXPLAIN statements as these
+    condtions may be arbitrarily costly, and because the optimize phase
+    might not have produced a complete executable plan for EXPLAINs.
+  */
+  if (exec_const_cond && !(select_options & SELECT_DESCRIBE) &&
+      !exec_const_cond->val_int())
     zero_result_cause= "Impossible WHERE noticed after reading const tables";
 
   if (zero_result_cause)



More information about the commits mailing list