[Commits] Rev 2854: Fix LP BUG#611622 in file:///home/tsk/mprog/src/5.3/

timour at askmonty.org timour at askmonty.org
Thu Nov 25 11:43:42 EET 2010


At file:///home/tsk/mprog/src/5.3/

------------------------------------------------------------
revno: 2854
revision-id: timour at askmonty.org-20101125094323-xksmiwfnic316fnm
parent: timour at askmonty.org-20101124223450-zv9301d7v5t4rot5
committer: timour at askmonty.org
branch nick: 5.3
timestamp: Thu 2010-11-25 11:43:23 +0200
message:
  Fix LP BUG#611622
  Fix MySQL BUG#52344 - Subquery materialization: Assertion if subquery in on-clause of outer join
  
  Original fix and comments from Oysten, adjusted for the different
  subquery optimization in MariaDB.
  "
  Problem: If tables of an outer join are constant tables,
  the associated on-clause will be evaluated in the optimization
  phase. If the on-clause contains a query that is to be
  executed with subquery materialization, this will not work
  since the infrastructure for such execution is not yet set up.
        
  Solution: Do not evaluate on-clause in optimization phase if
  is_expensive() returns true for this clause.  This is how the
  problem is currently avoided for where-clauses.  This works
  because, Item_in_subselect::is_expensive_processor returns true
  if query is to be executed with subquery materialization.
  "
  In addition, after MWL#89, in MariaDB if the IN-EXISTS strategy
  is chosen, the in-to-exists predicates are insterted after
  join_read_const_table() is called, resulting in evaluation of
  the subquery without the in-to-exists predicates.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect_mat.result'
--- a/mysql-test/r/subselect_mat.result	2010-10-25 20:48:43 +0000
+++ b/mysql-test/r/subselect_mat.result	2010-11-25 09:43:23 +0000
@@ -1297,3 +1297,50 @@ id	select_type	table	type	possible_keys	
 SELECT SUM(c1) c1_sum FROM t1 WHERE c1 IN (SELECT c2 FROM t2) HAVING c1_sum;
 c1_sum
 drop table t1, t2;
+#
+# BUG#52344 - Subquery materialization: 
+#            Assertion if subquery in on-clause of outer join
+#
+set @@optimizer_switch='semijoin=off';
+CREATE TABLE t1 (i INTEGER);
+INSERT INTO t1 VALUES (10);
+CREATE TABLE t2 (j INTEGER);
+INSERT INTO t2 VALUES (5);
+CREATE TABLE t3 (k INTEGER);
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
+2       SUBQUERY        t3      system  NULL    NULL    NULL    NULL    0       const row not found
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+i
+10
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
+2       SUBQUERY        t3      system  NULL    NULL    NULL    NULL    0       const row not found
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+i
+10
+DROP TABLE t1, t2, t3;
+#
+# LPBUG#609121: RQG: wrong result on aggregate + NOT IN + HAVING and
+# partial_match_table_scan=on
+#
+CREATE TABLE t1 (c1 int);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (c2 int);
+INSERT INTO t2 VALUES (10);
+PREPARE st1 FROM "
+SELECT *
+FROM t2 LEFT JOIN (SELECT * FROM t2) t3 ON (8, 4) IN (SELECT c1, c1 FROM t1)";
+EXECUTE st1;
+c2      c2
+10      10
+EXECUTE st1;
+c2      c2
+10      10
+DROP TABLE t1, t2;

=== modified file 'mysql-test/t/subselect_mat.test'
--- a/mysql-test/t/subselect_mat.test	2010-10-25 20:48:43 +0000
+++ b/mysql-test/t/subselect_mat.test	2010-11-25 09:43:23 +0000
@@ -943,3 +943,48 @@ SELECT SUM(c1) c1_sum FROM t1 WHERE c1 I
 SELECT SUM(c1) c1_sum FROM t1 WHERE c1 IN (SELECT c2 FROM t2) HAVING c1_sum;
 
 drop table t1, t2;
+
+--echo #
+--echo # BUG#52344 - Subquery materialization: 
+--echo #             Assertion if subquery in on-clause of outer join
+--echo #
+
+set @@optimizer_switch='semijoin=off';
+
+CREATE TABLE t1 (i INTEGER);
+INSERT INTO t1 VALUES (10);
+
+CREATE TABLE t2 (j INTEGER);
+INSERT INTO t2 VALUES (5);
+
+CREATE TABLE t3 (k INTEGER);
+
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+
+DROP TABLE t1, t2, t3;
+
+--echo #
+--echo # LPBUG#609121: RQG: wrong result on aggregate + NOT IN + HAVING and
+--echo # partial_match_table_scan=on
+--echo #
+
+CREATE TABLE t1 (c1 int);
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (c2 int);
+INSERT INTO t2 VALUES (10);
+
+PREPARE st1 FROM "
+SELECT *
+FROM t2 LEFT JOIN (SELECT * FROM t2) t3 ON (8, 4) IN (SELECT c1, c1 FROM t1)";
+
+EXECUTE st1;
+EXECUTE st1;
+
+DROP TABLE t1, t2;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-11-11 12:35:26 +0000
+++ b/sql/sql_select.cc	2010-11-25 09:43:23 +0000
@@ -10599,16 +10599,6 @@ remove_eq_conds(THD *thd, COND *cond, It
     }
   }
   else if (cond->const_item() && !cond->is_expensive())
-  /*
-    DontEvaluateMaterializedSubqueryTooEarly:
-    TODO: 
-    Excluding all expensive functions is too restritive we should exclude only
-    materialized IN subquery predicates because they can't yet be evaluated
-    here (they need additional initialization that is done later on).
-
-    The proper way to exclude the subqueries would be to walk the cond tree and
-    check for materialized subqueries there.
-  */
   {
     *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;
     return (COND*) 0;
@@ -13527,7 +13517,14 @@ join_read_const_table(JOIN_TAB *tab, POS
         DBUG_RETURN(error);
     }
   }
-  if (*tab->on_expr_ref && !table->null_row)
+  /* 
+     Evaluate an on-expression only if it is not considered expensive.
+     This mainly prevents executing subqueries in optimization phase.
+     This is necessary since proper setup for such execution has not been
+     done at this stage.
+  */
+  if (*tab->on_expr_ref && !table->null_row && 
+      !(*tab->on_expr_ref)->is_expensive())
   {
 #if !defined(DBUG_OFF) && defined(NOT_USING_ITEM_EQUAL)
     /*



More information about the commits mailing list