List:Commits« Previous MessageNext Message »
From:Sergey Glukhov Date:June 16 2010 6:55am
Subject:bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3432)
Bug#48916
View as plain text  
#At file:///home/gluh/MySQL/mysql-5.1-bugteam/ based on revid:bjorn.munch@stripped

 3432 Sergey Glukhov	2010-06-16
      Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
      Before sorting HAVING condition is splitted into two parts,
      first past is a table related condition and the rest of is
      HAVING part. Extraction of HAVING part does not take into account
      the fact that some of conditions might be non-const but
      have 'used_tables' equal to zero(particulary IN subselect)
      and because of that these conditions are cut off by
      make_cond_for_table() function.
      The fix is to split extraction of HAVING part into two steps:
      1. extract condition which does not belong to the sorted table
      2. extract condition which has 'used_tables' equal to zero
      and depending on result use one or union of them.
     @ mysql-test/r/having.result
        test case
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        The fix is to split extraction of HAVING part into two steps:
        1. extract condition which does not belong to sorted table
        2. extract condition which has 'used_tables' equal to zero
        and depending on result use one or union of them.

    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-04-12 10:12:20 +0000
+++ b/mysql-test/r/having.result	2010-06-16 06:55:24 +0000
@@ -530,3 +530,51 @@ MAX(t2.f2)
 NULL
 DROP TABLE t1,t2;
 End of 5.0 tests
+#
+# Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
+#
+CREATE TABLE t1 (f1 INT, f2 INT);
+INSERT INTO t1 VALUES (1, 0), (2, 1), (3, 2);
+CREATE TABLE t2 (f1 INT, f2 INT);
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT f1, f2 FROM t2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+f1
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT 4, 2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+f1
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT t1.f1, t1.f1) AND t1.f1  >= 0
+ORDER BY t1.f1;
+f1
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT rand(), rand() FROM t2) AND
+(3, 2) IN (SELECT f1, f2 FROM t2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+f1
+INSERT INTO t2 VALUES (3, 2);
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT f1, f2 FROM t2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+f1
+1
+2
+3
+DELETE FROM t2;
+INSERT INTO t2 VALUES (1, 2), (3, 2), (3, 4);
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT f1, f2 FROM t2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+f1
+1
+2
+3
+DROP TABLE t1,t2;
+End of 5.1 tests

=== modified file 'mysql-test/t/having.test'
--- a/mysql-test/t/having.test	2010-04-12 10:12:20 +0000
+++ b/mysql-test/t/having.test	2010-06-16 06:55:24 +0000
@@ -544,3 +544,49 @@ ORDER BY t1.f2;
 DROP TABLE t1,t2;
 
 --echo End of 5.0 tests
+
+--echo #
+--echo # Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
+--echo #
+
+CREATE TABLE t1 (f1 INT, f2 INT);
+INSERT INTO t1 VALUES (1, 0), (2, 1), (3, 2);
+CREATE TABLE t2 (f1 INT, f2 INT);
+
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT f1, f2 FROM t2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT 4, 2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT t1.f1, t1.f1) AND t1.f1  >= 0
+ORDER BY t1.f1;
+
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT rand(), rand() FROM t2) AND
+       (3, 2) IN (SELECT f1, f2 FROM t2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+
+INSERT INTO t2 VALUES (3, 2);
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT f1, f2 FROM t2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+
+DELETE FROM t2;
+INSERT INTO t2 VALUES (1, 2), (3, 2), (3, 4);
+SELECT t1.f1
+FROM t1
+HAVING (3, 2) IN (SELECT f1, f2 FROM t2) AND t1.f1  >= 0
+ORDER BY t1.f1;
+
+DROP TABLE t1,t2;
+
+--echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-06-10 20:45:22 +0000
+++ b/sql/sql_select.cc	2010-06-16 06:55:24 +0000
@@ -2183,7 +2183,7 @@ JOIN::exec()
       JOIN_TAB *curr_table= &curr_join->join_tab[curr_join->const_tables];
       table_map used_tables= (curr_join->const_table_map |
 			      curr_table->table->map);
-
+      DBUG_ASSERT(!(used_tables & (OUTER_REF_TABLE_BIT | RAND_TABLE_BIT)));
       Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having,
 						 used_tables,
 						 used_tables);
@@ -2207,9 +2207,31 @@ JOIN::exec()
 	DBUG_EXECUTE("where",print_where(curr_table->select->cond,
 					 "select and having",
                                          QT_ORDINARY););
-	curr_join->tmp_having= make_cond_for_table(curr_join->tmp_having,
-						   ~ (table_map) 0,
-						   ~used_tables);
+
+        Item* having_non_const_cond=
+          make_cond_for_table(curr_join->tmp_having, ~(table_map) 0,
+                              (~used_tables | RAND_TABLE_BIT |
+                               OUTER_REF_TABLE_BIT));
+        Item* having_const_cond=
+          make_cond_for_table(curr_join->tmp_having,
+                              ~(used_tables | RAND_TABLE_BIT|
+                                OUTER_REF_TABLE_BIT), (table_map) 0);
+        if (having_non_const_cond && having_const_cond)
+        {
+          curr_join->tmp_having= new Item_cond_and(having_non_const_cond,
+                                                   having_const_cond);
+          curr_join->tmp_having->fix_fields(thd, 0);
+        }
+        else if (having_non_const_cond)
+          curr_join->tmp_having= having_non_const_cond;
+        else if (having_const_cond)
+          curr_join->tmp_having= having_const_cond;
+        else
+        {
+          DBUG_ASSERT(!(curr_join->tmp_having->used_tables() & ~used_tables) &&
+                      !having_const_cond);
+          curr_join->tmp_having= 0;
+        }
 	DBUG_EXECUTE("where",print_where(curr_join->tmp_having,
                                          "having after sort",
                                          QT_ORDINARY););


Attachment: [text/bzr-bundle] bzr/sergey.glukhov@sun.com-20100616065524-b75rjlz1nu84tvl2.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3432)Bug#48916Sergey Glukhov16 Jun
  • Re: bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3432)Bug#48916Martin Hansson17 Jun
    • Re: bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3432)Bug#48916Sergey Glukhov17 Jun
      • Re: bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3432)Bug#48916Martin Hansson24 Jun