[Commits] Rev 2856: Auto-merge fix for LP BUG#611622 in file:///home/tsk/mprog/src/5.3/

timour at askmonty.org timour at askmonty.org
Mon Nov 29 13:51:32 EET 2010


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

------------------------------------------------------------
revno: 2856 [merge]
revision-id: timour at askmonty.org-20101129115056-xwzv0y3dbd5t5a9f
parent: monty at askmonty.org-20101128130212-0kxl3o2rfwiqbdfa
parent: timour at askmonty.org-20101125094323-xksmiwfnic316fnm
committer: timour at askmonty.org
branch nick: 5.3
timestamp: Mon 2010-11-29 13:50:56 +0200
message:
  Auto-merge fix for LP BUG#611622
modified:
  mysql-test/r/subselect_mat.result subselect_mat.result-20100117143924-r0jv32dj80dg3b5h-1
  mysql-test/t/subselect_mat.test subselect_mat.test-20100117143929-iif102ysgna1tyj0-1
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
-------------- 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-27 15:29:52 +0000
+++ b/sql/sql_select.cc	2010-11-29 11:50:56 +0000
@@ -10604,16 +10604,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;
@@ -13533,7 +13523,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