MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Sergey Glukhov Date:March 1 2010 10:15am
Subject:bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3344)
Bug#48044 Bug#48118 Bug#48916 Bug#51242
View as plain text  
#At file:///home/gluh/MySQL/mysql-5.1-bug-50198/ based on revid:mattias.jonsson@stripped

 3344 Sergey Glukhov	2010-03-01
      Bug#51242 HAVING clause on table join produce incorrect results
      The problem is that when we make conditon for
      grouped result const part of condition is cut off.
      It happens because some parts of 'having' condition
      which refer to outer join become const after
      make_join_statistics. The fix is adding 'having'
      condition check for 'having' const tables after
      make_join_statistics is performed.
      Note:
      This patch also fixes problems described in
      Bug#48916, Bug #48044, Bug#48118.
     @ mysql-test/r/having.result
        test result
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        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).

    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	2009-01-16 15:38:38 +0000
+++ b/mysql-test/r/having.result	2010-03-01 10:15:18 +0000
@@ -430,4 +430,17 @@ SELECT b, COUNT(DISTINCT a) FROM t1 GROU
 b	COUNT(DISTINCT a)
 NULL	1
 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
+DROP TABLE t1;
 End of 5.0 tests

=== modified file 'mysql-test/t/having.test'
--- a/mysql-test/t/having.test	2009-01-16 15:38:38 +0000
+++ b/mysql-test/t/having.test	2010-03-01 10:15:18 +0000
@@ -442,4 +442,19 @@ INSERT INTO t1 VALUES (1, 1), (2,2), (3,
 SELECT b, COUNT(DISTINCT a) FROM t1 GROUP BY b HAVING b is NULL;
 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);
+
+DROP TABLE t1;
+
 --echo End of 5.0 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-02-16 09:13:49 +0000
+++ b/sql/sql_select.cc	2010-03-01 10:15:18 +0000
@@ -1119,6 +1119,26 @@ JOIN::optimize()
     DBUG_RETURN(0);				// error == 0
   }
 
+  /*
+    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).
+  */
+  if (having && (having->used_tables() & ~const_table_map))
+  {
+    COND *const_cond=
+      make_cond_for_table(having, (const_table_map & having->used_tables()), 0);
+    DBUG_EXECUTE("where", print_where(const_cond, "const_having_cond",
+                                      QT_ORDINARY););
+    if (const_cond && !const_cond->val_int())
+    {
+      zero_result_cause= "Impossible HAVING";
+      DBUG_RETURN(0);
+    }
+  }
+
   error= -1;					/* if goto err */
 
   /* Optimize distinct away if possible */


Attachment: [text/bzr-bundle] bzr/sergey.glukhov@sun.com-20100301101518-mg44f9rwax3pw1ms.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3344)Bug#48044 Bug#48118 Bug#48916 Bug#51242Sergey Glukhov1 Mar
  • Re: bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3344)Bug#48044 Bug#48118 Bug#48916 Bug#51242Evgeny Potemkin1 Mar