[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 17:40:27 EET 2010


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

------------------------------------------------------------
revno: 2851
revision-id: timour at askmonty.org-20101126154020-vok9hgwfdch0awut
parent: timour at askmonty.org-20101122220124-8ap7dfnsfz76w1j7
committer: timour at askmonty.org
branch nick: 5.3-mwl89-bugfix
timestamp: Fri 2010-11-26 17:40:20 +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 similarly to the approach
  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 15:40:20 +0000
@@ -526,3 +526,81 @@ 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       Using temporary; Using filesort
+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
+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)
+ORDER 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)
+ORDER 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
+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       Using temporary; Using filesort
+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
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+COUNT(t2.f3)    f9
+EXPLAIN
+SELECT COUNT(t2.f3),
+(SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1
+WHERE ('v') IN (SELECT f4 FROM t2)
+ORDER 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
+WHERE ('v') IN (SELECT f4 FROM t2)
+ORDER 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 15:40:20 +0000
@@ -491,3 +491,69 @@ 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 COUNT(f3) FROM t1 WHERE t2.f1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+ORDER 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)
+ORDER BY f9;
+
+# these queries are like the ones above, but without the ON clause,
+# resulting in a different crash (failed assert)
+EXPLAIN
+SELECT COUNT(t2.f3),
+       (SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1
+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
+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
+WHERE ('v') IN (SELECT f4 FROM t2)
+ORDER BY f9;
+
+SELECT COUNT(t2.f3),
+       (SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1
+WHERE ('v') IN (SELECT f4 FROM t2)
+ORDER 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 15:40:20 +0000
@@ -8279,13 +8279,15 @@ remove_const(JOIN *join,ORDER *first_ord
         (join->tables > 1 && join->rollup.state == ROLLUP::STATE_INITED &&
         join->outer_join))
       *simple_order=0;                          // Must do a temp table to sort
-    else if (!(order_tables & not_const_tables))
+    else if (!(order_tables & not_const_tables) &&
+             !order->item[0]->with_subselect)
     {
-      if (order->item[0]->with_subselect && 
-          !(join->select_lex->options & SELECT_DESCRIBE))
-        order->item[0]->val_str(&order->item[0]->str_value);
+      /*
+        Skip constant expressions in the ORDER/GROUP clause, except when there
+        is a subquery in the expression.
+      */
       DBUG_PRINT("info",("removing: %s", order->item[0]->full_name()));
-      continue;                                 // skip const item
+      continue;
     }
     else
     {



More information about the commits mailing list