MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Sergey Glukhov Date:March 10 2010 12:43pm
Subject:bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3385)
Bug#48044 Bug#48118 Bug#48916 Bug#51242
View as plain text  
#At file:///home/gluh/MySQL/mysql-5.1-bug-51242/ based on revid:luis.soares@stripped

 3385 Sergey Glukhov	2010-03-10
      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 const table field from
        outer join).
        Note:
        This is applicable only for those conditions
        which do not use aggregate fucntions.

    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-02-26 13:40:01 +0000
+++ b/mysql-test/r/having.result	2010-03-10 12:43:29 +0000
@@ -450,4 +450,39 @@ 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

=== modified file 'mysql-test/t/having.test'
--- a/mysql-test/t/having.test	2010-02-26 11:39:25 +0000
+++ b/mysql-test/t/having.test	2010-03-10 12:43:29 +0000
@@ -467,5 +467,35 @@ 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-09 10:36:26 +0000
+++ b/sql/sql_select.cc	2010-03-10 12:43:29 +0000
@@ -1112,6 +1112,30 @@ 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).
+    Note:
+    This is applicable only for those conditions
+    which do not use aggregate fucntions.
+  */
+
+  if (having && !having->with_sum_func)
+  {
+    COND *const_cond= make_cond_for_table(having, const_table_map, 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 noticed after reading const tables";
+      DBUG_RETURN(0);
+    }
+  }
+
   if (make_join_select(this, select, conds))
   {
     zero_result_cause=


Attachment: [text/bzr-bundle] bzr/sergey.glukhov@sun.com-20100310124329-v0s84ms0nf76lqvz.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3385)Bug#48044 Bug#48118 Bug#48916 Bug#51242Sergey Glukhov10 Mar
  • Re: bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3385)Bug#48044 Bug#48118 Bug#48916 Bug#51242Evgeny Potemkin12 Mar