MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Sergey Glukhov Date:March 19 2010 8:42am
Subject:bzr commit into mysql-pe branch (Sergey.Glukhov:3982) Bug#51242
View as plain text  
#At file:///home/gluh/MySQL/mysql-pe/ based on revid:sergey.glukhov@stripped

 3982 Sergey Glukhov	2010-03-19
      Bug#51242 HAVING clause on table join produce incorrect results
      version for mysql-pe
     @ mysql-test/r/having.result
        test result
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        Bug#51242 HAVING clause on table join produce incorrect results
        version for mysql-pe

    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-19 08:42:40 +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-19 08:42:40 +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-19 07:05:41 +0000
+++ b/sql/sql_select.cc	2010-03-19 08:42:40 +0000
@@ -2131,6 +2131,31 @@ JOIN::optimize()
     }
   }
 
+  /*
+    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 && !need_tmp)
+  {
+    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 && !const_cond->val_int())
+    {
+      zero_result_cause= "Impossible HAVING noticed after reading const tables";
+      error= 0;
+      DBUG_RETURN(0);
+    }
+  }
+
   tmp_having= having;
   if (select_options & SELECT_DESCRIBE)
   {


Attachment: [text/bzr-bundle] bzr/sergey.glukhov@sun.com-20100319084240-hqxpnmbsiulegjmz.bundle
Thread
bzr commit into mysql-pe branch (Sergey.Glukhov:3982) Bug#51242Sergey Glukhov19 Mar