MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Sergey Glukhov Date:March 16 2010 10:31am
Subject:bzr commit into mysql-pe branch (Sergey.Glukhov:3972) Bug#51242
View as plain text  
#At file:///home/gluh/MySQL/mysql-pe/ based on revid:davi.arnaut@stripped

 3972 Sergey Glukhov	2010-03-16 [merge]
      mysql-5.1-bugteam->mysql-pe merge(Bug#51242)
     @ mysql-test/r/having.result
        mysql-5.1-bugteam->mysql-pe merge(Bug#51242)
     @ mysql-test/t/having.test
        mysql-5.1-bugteam->mysql-pe merge(Bug#51242)
     @ sql/sql_select.cc
        mysql-5.1-bugteam->mysql-pe merge(Bug#51242)

    modified:
      mysql-test/r/having.result
      mysql-test/t/having.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/having.result'
--- a/mysql-test/r/having.result	2010-03-01 10:04:07 +0000
+++ b/mysql-test/r/having.result	2010-03-16 10:30:14 +0000
@@ -450,6 +450,41 @@ HAVING amount > 0
 ORDER BY t1.id1;
 id1	amount
 DROP TABLE t1;
+#
+# Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
+#
+CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT);
+INSERT INTO t1 VALUES (2,7,9), (4,7,9), (6,2,9), (17,0,9);
+SELECT table1.f1, table2.f2
+FROM t1 AS table1
+JOIN t1 AS table2 ON table1.f3 = table2.f3
+WHERE table2.f1 = 2
+GROUP BY table1.f1, table2.f2
+HAVING (table2.f2 = 8 AND table1.f1 >= 6);
+f1	f2
+EXPLAIN EXTENDED
+SELECT table1.f1, table2.f2
+FROM t1 AS table1
+JOIN t1 AS table2 ON table1.f3 = table2.f3
+WHERE table2.f1 = 2
+GROUP BY table1.f1, table2.f2
+HAVING (table2.f2 = 8 AND table1.f1 >= 6);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING noticed after reading const tables
+Warnings:
+Note	1003	select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having (('7' = 8) and (`test`.`table1`.`f1` >= 6))
+EXPLAIN EXTENDED
+SELECT table1.f1, table2.f2
+FROM t1 AS table1
+JOIN t1 AS table2 ON table1.f3 = table2.f3
+WHERE table2.f1 = 2
+GROUP BY table1.f1, table2.f2
+HAVING (table2.f2 = 8);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING noticed after reading const tables
+Warnings:
+Note	1003	select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having ('7' = 8)
+DROP TABLE t1;
 End of 5.0 tests
 #
 # BUG#46077 "wrong result: HAVING + ORDER BY + MyISAM + ICP

=== modified file 'mysql-test/t/having.test'
--- a/mysql-test/t/having.test	2010-02-26 12:15:31 +0000
+++ b/mysql-test/t/having.test	2010-03-16 10:30:14 +0000
@@ -467,6 +467,36 @@ ORDER BY t1.id1;
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
+--echo #
+CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT);
+INSERT INTO t1 VALUES (2,7,9), (4,7,9), (6,2,9), (17,0,9);
+
+SELECT table1.f1, table2.f2
+FROM t1 AS table1
+JOIN t1 AS table2 ON table1.f3 = table2.f3
+WHERE table2.f1 = 2
+GROUP BY table1.f1, table2.f2
+HAVING (table2.f2 = 8 AND table1.f1 >= 6);
+
+EXPLAIN EXTENDED
+SELECT table1.f1, table2.f2
+FROM t1 AS table1
+JOIN t1 AS table2 ON table1.f3 = table2.f3
+WHERE table2.f1 = 2
+GROUP BY table1.f1, table2.f2
+HAVING (table2.f2 = 8 AND table1.f1 >= 6);
+
+EXPLAIN EXTENDED
+SELECT table1.f1, table2.f2
+FROM t1 AS table1
+JOIN t1 AS table2 ON table1.f3 = table2.f3
+WHERE table2.f1 = 2
+GROUP BY table1.f1, table2.f2
+HAVING (table2.f2 = 8);
+
+DROP TABLE t1;
 
 --echo End of 5.0 tests
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-03-14 16:19:24 +0000
+++ b/sql/sql_select.cc	2010-03-16 10:30:14 +0000
@@ -1451,6 +1451,7 @@ JOIN::optimize()
   bool need_distinct;
   ulonglong select_opts_for_readinfo;
   uint no_jbuf_after;
+  bool subquery_materialization_is_done= FALSE;
 
   DBUG_ENTER("JOIN::optimize");
   // to prevent double initialization on EXPLAIN
@@ -1750,6 +1751,36 @@ JOIN::optimize()
     conds=new Item_int((longlong) 0,1);	// Always false
   }
 
+  /*
+    It's necessary to check const part of HAVING cond as
+    there is a chance that some cond parts may become
+    const items after make_join_statisctics(for example
+    when Item is a reference to cost table field from
+    outer join).
+    This check is performed only for those conditions
+    which do not use aggregate functions. In such case
+    temporary table may not be used and const condition
+    elements may be lost during further having
+    condition transformation in JOIN::exec.
+  */
+  if (having && !having->with_sum_func)
+  {
+    COND *const_cond= make_cond_for_table(having, const_table_map, 0, 0);
+    DBUG_EXECUTE("where", print_where(const_cond, "const_having_cond",
+                                      QT_ORDINARY););
+    if (const_cond)
+    {
+      if (setup_subquery_materialization())
+        DBUG_RETURN(1);
+      subquery_materialization_is_done= TRUE;
+      if (!const_cond->val_int())
+      {
+        zero_result_cause= "Impossible HAVING noticed after reading const tables";
+        goto setup_subq_exit;
+      }
+    }
+  }
+
   if (make_join_select(this, conds))
   {
     zero_result_cause=
@@ -2287,7 +2318,7 @@ setup_subq_exit:
     query. If we have planned to materialize the subquery, we need to
     set it up properly before prematurely leaving optimize().
   */
-  if (setup_subquery_materialization())
+  if (!subquery_materialization_is_done && setup_subquery_materialization())
     DBUG_RETURN(1);
   error= 0;
   DBUG_RETURN(0);


Attachment: [text/bzr-bundle] bzr/sergey.glukhov@sun.com-20100316103014-9axbfvgvb9vka5n9.bundle
Thread
bzr commit into mysql-pe branch (Sergey.Glukhov:3972) Bug#51242Sergey Glukhov16 Mar