bk commit into 4.1 tree (igor:1.2469) BUG#14927

[igor]

Below is the list of changes that have just been committed into a local
4.1 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2469 06/01/31 21:48:32 igor@stripped +6 -0
  FIxed bug #14927.
  A query with a group by and having clauses could return a wrong
  result set if the having condition contained a constant conjunct 
  evaluated to FALSE.
  It happened because the pushdown condition for table with
  grouping columns lost its constant conjuncts.
  Pushdown conditions are always built by the function make_cond_for_table
  that ignores constant conjuncts. This is apparently not correct when
  constant false conjuncts are present.

  sql/sql_select.cc
    1.450 06/01/31 21:48:28 igor@stripped +13 -9
    Fixed bug #14927.
    Performed evaluation of constant expressions in having clauses.
    If the having condition contains a constant conjunct that is always false
    an empty result set is returned after the optimization phase.
    In this case the corresponding EXPLAIN command now returns 
    "Impossible HAVING" in the last column.

  sql/sql_prepare.cc
    1.156 06/01/31 21:48:27 igor@stripped +8 -2
    Fixed bug #14927.
    Added code to restore havinf conditions for execution in SP and PS.

  sql/sql_lex.h
    1.188 06/01/31 21:48:27 igor@stripped +1 -0
    Fixed bug #14927.
    Added a field to restore having condititions for execution in SP and PS.

  sql/sql_lex.cc
    1.152 06/01/31 21:48:27 igor@stripped +2 -0
    Fixed bug #14927.
    Initialized fields for having conditions in  st_select_lex::init_query().

  mysql-test/t/having.test
    1.14 06/01/31 21:48:27 igor@stripped +16 -0
    Added A test case for bug #14927.

  mysql-test/r/having.result
    1.15 06/01/31 21:48:27 igor@stripped +17 -0
    Added A test case for bug #14927.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev/mysql-4.1-0

--- 1.151/sql/sql_lex.cc	2005-10-12 13:51:38 -07:00
+++ 1.152/sql/sql_lex.cc	2006-01-31 21:48:27 -08:00
@@ -1074,6 +1074,7 @@
   item_list.empty();
   join= 0;
   where= 0;
+  having= 0;
   olap= UNSPECIFIED_OLAP_TYPE;
   having_fix_field= 0;
   resolve_mode= NOMATTER_MODE;
@@ -1081,6 +1082,7 @@
   ref_pointer_array= 0;
   select_n_having_items= 0;
   prep_where= 0;
+  prep_having= 0;
   subquery_in_having= explicit_limit= 0;
   parsing_place= NO_MATTER;
   is_item_list_lookup= 0;

--- 1.187/sql/sql_lex.h	2005-10-11 16:32:13 -07:00
+++ 1.188/sql/sql_lex.h	2006-01-31 21:48:27 -08:00
@@ -421,6 +421,7 @@
   char *db, *db1, *table1, *db2, *table2;      	/* For outer join using .. */
   Item *where, *having;                         /* WHERE & HAVING clauses */
   Item *prep_where; /* saved WHERE clause for prepared statement processing */
+  Item *prep_having;/* saved HAVING clause for prepared statement processing */
   enum olap_type olap;
   SQL_LIST	      table_list, group_list;   /* FROM & GROUP BY clauses */
   List<Item>          item_list; /* list of fields & expressions */

--- 1.449/sql/sql_select.cc	2006-01-26 06:00:46 -08:00
+++ 1.450/sql/sql_select.cc	2006-01-31 21:48:28 -08:00
@@ -501,12 +501,18 @@
     DBUG_RETURN(1);
   }
 
-  if (cond_value == Item::COND_FALSE ||
-      (!unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS)))
-  {						/* Impossible cond */
-    zero_result_cause= "Impossible WHERE";
-    error= 0;
-    DBUG_RETURN(0);
+  {
+    Item::cond_result having_value;
+    having= optimize_cond(thd, having, &having_value);
+
+    if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE || 
+        (!unit->select_limit_cnt && !(select_options &
OPTION_FOUND_ROWS)))
+    {						/* Impossible cond */
+      zero_result_cause= having_value == Item::COND_FALSE ?
+                           "Impossible HAVING" : "Impossible WHERE";
+      error= 0;
+      DBUG_RETURN(0);
+    }
   }
 
   /* Optimize count(*), min() and max() */
@@ -4611,10 +4617,8 @@
     DBUG_EXECUTE("info", print_where(conds, "after remove"););
   }
   else
-  {
     *cond_value= Item::COND_TRUE;
-    select->prep_where= 0;
-  }
+
   DBUG_RETURN(conds);
 }
 

--- 1.14/mysql-test/r/having.result	2006-01-07 22:59:56 -08:00
+++ 1.15/mysql-test/r/having.result	2006-01-31 21:48:27 -08:00
@@ -141,3 +141,20 @@
 6
 4
 DROP TABLE t1;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2), (1), (3), (2), (1);
+SELECT a FROM t1 GROUP BY a HAVING a > 1;
+a
+2
+3
+SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1;
+a
+SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1;
+x	a
+EXPLAIN SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+DROP table t1;

--- 1.13/mysql-test/t/having.test	2006-01-07 22:59:56 -08:00
+++ 1.14/mysql-test/t/having.test	2006-01-31 21:48:27 -08:00
@@ -135,4 +135,20 @@
 
 DROP TABLE t1;
 
+#
+# Bug #14927: HAVING clause containing constant false conjunct
+#
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2), (1), (3), (2), (1);
+
+SELECT a FROM t1 GROUP BY a HAVING a > 1;
+SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1;
+SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1;
+
+EXPLAIN SELECT a FROM t1 GROUP BY a HAVING 1 != 1 AND a > 1;
+EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1;
+
+DROP table t1;  
+
 # End of 4.1 tests

--- 1.155/sql/sql_prepare.cc	2005-09-25 11:22:21 -07:00
+++ 1.156/sql/sql_prepare.cc	2006-01-31 21:48:27 -08:00
@@ -1665,10 +1665,11 @@
     for (; sl; sl= sl->next_select_in_list())
     {
       /*
-        Save WHERE clause pointers, because they may be changed
+        Save WHERE, HAVING clause pointers, because they may be changed
         during query optimisation.
       */
       sl->prep_where= sl->where;
+      sl->prep_having= sl->having;
       /*
         Switch off a temporary flag that prevents evaluation of
         subqueries in statement prepare.
@@ -1694,12 +1695,17 @@
     /* remove option which was put by mysql_explain_union() */
     sl->options&= ~SELECT_DESCRIBE;
     /*
-      Copy WHERE clause pointers to avoid damaging they by optimisation
+      Copy WHERE, HAVING clause pointers to avoid damaging they by optimisation
     */
     if (sl->prep_where)
     {
       sl->where= sl->prep_where->copy_andor_structure(thd);
       sl->where->cleanup();
+    }
+    if (sl->prep_having)
+    {
+      sl->having= sl->prep_having->copy_andor_structure(thd);
+      sl->having->cleanup();
     }
     DBUG_ASSERT(sl->join == 0);
     ORDER *order;