[Commits] Rev 2851: Fix LP BUG#680846 in file:///home/tsk/mprog/src/5.3-mwl89-bugfix/

timour at askmonty.org timour at askmonty.org
Fri Nov 26 14:21:39 EET 2010


At file:///home/tsk/mprog/src/5.3-mwl89-bugfix/

------------------------------------------------------------
revno: 2851
revision-id: timour at askmonty.org-20101126122129-dgqr8t2oa83malcs
parent: timour at askmonty.org-20101122220124-8ap7dfnsfz76w1j7
committer: timour at askmonty.org
branch nick: 5.3-mwl89-bugfix
timestamp: Fri 2010-11-26 14:21:29 +0200
message:
  Fix LP BUG#680846
  
  Analysis:
  JOIN::optimize performs constant optimization of GROUP by clauses
  by calling remove_const():
      group_list= remove_const(this, (old_group_list= group_list), conds,
                               rollup.state == ROLLUP::STATE_NONE,
  			     &simple_group);
  If it turns out that a GROUP clause references a field that is
  computed by a single-row subquery, then the said optimization
  performs premature execution of the subquery referenced by the
  group clause.
  
  Solution:
  Block the evaluation of subqueries in this case in the same way
  how it's done for the WHERE and JOIN..ON clauses.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	2010-11-22 22:01:24 +0000
+++ b/mysql-test/r/subselect4.result	2010-11-26 12:21:29 +0000
@@ -526,3 +526,47 @@ id	select_type	table	type	possible_keys	
 2       DEPENDENT SUBQUERY      t2      index_subquery  k       k       5       const   2       Using index
 DROP TABLE t2;
 DROP TABLE t1;
+#
+# BUG#680846: Crash in clear_tables() with subqueries
+#
+CREATE TABLE t1 (f3 int) ;
+INSERT IGNORE INTO t1 VALUES (0),(0);
+CREATE TABLE t2 (f1 int,f3 int,f4 varchar(32)) ;
+INSERT IGNORE INTO t2 VALUES (1,0,'f');
+EXPLAIN
+SELECT COUNT(t2.f3),
+(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+3       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       
+SELECT COUNT(t2.f3),
+(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+COUNT(t2.f3)    f9
+0       2
+EXPLAIN
+SELECT COUNT(t2.f3),
+(SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t2      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+3       DEPENDENT SUBQUERY      NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       
+SELECT COUNT(t2.f3),
+(SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+COUNT(t2.f3)    f9
+0       NULL
+drop table t1,t2;

=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test	2010-11-22 22:01:24 +0000
+++ b/mysql-test/t/subselect4.test	2010-11-26 12:21:29 +0000
@@ -491,3 +491,38 @@ SELECT i FROM t1 WHERE (1) NOT IN (SELEC
 
 DROP TABLE t2;
 DROP TABLE t1;
+
+--echo #
+--echo # BUG#680846: Crash in clear_tables() with subqueries
+--echo #
+
+CREATE TABLE t1 (f3 int) ;
+INSERT IGNORE INTO t1 VALUES (0),(0);
+
+CREATE TABLE t2 (f1 int,f3 int,f4 varchar(32)) ;
+INSERT IGNORE INTO t2 VALUES (1,0,'f');
+
+EXPLAIN
+SELECT COUNT(t2.f3),
+       (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+SELECT COUNT(t2.f3),
+       (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+EXPLAIN
+SELECT COUNT(t2.f3),
+       (SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+SELECT COUNT(t2.f3),
+       (SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+
+drop table t1,t2;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-11-22 14:32:36 +0000
+++ b/sql/sql_select.cc	2010-11-26 12:21:29 +0000
@@ -8282,7 +8282,8 @@ remove_const(JOIN *join,ORDER *first_ord
     else if (!(order_tables & not_const_tables))
     {
       if (order->item[0]->with_subselect && 
-          !(join->select_lex->options & SELECT_DESCRIBE))
+          !(join->select_lex->options & SELECT_DESCRIBE ||
+            order->item[0]->is_expensive()))
         order->item[0]->val_str(&order->item[0]->str_value);
       DBUG_PRINT("info",("removing: %s", order->item[0]->full_name()));
       continue;                                 // skip const item



More information about the commits mailing list