Bug #14927 HAVING ignores conditions
Submitted: 14 Nov 2005 16:30 Modified: 10 May 2006 16:28
Reporter: Gabriel Sava Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.12a OS:Microsoft Windows (Windows XP)
Assigned to: Igor Babaev CPU Architecture:Any

[14 Nov 2005 16:30] Gabriel Sava
Description:
If I use in HAVING clause a column from the GROUP BY clause, which contains more than one column, the other conditions in HAVING are ignored.

example:

SELECT 0 AS x, col1 AS y FROM table GROUP BY col1, col2 HAVING y > 0 AND x = 1

this should not return any rows, since x is 0 for all rows. But it returns the same as if the x = 1 condition wasn't there.

If I only group by col1 or not group by col1, it works.

How to repeat:
Try the query I used as example.
[14 Nov 2005 16:49] Miguel Solorzano
Could you please provide the complete test case, create table,
insert rows, query and the result you got/expected.

Thanks in advance.
[14 Nov 2005 16:56] Gabriel Sava
CREATE TABLE `table` (
  `col1` int(11) NOT NULL default '0',
  `col2` int(11) NOT NULL default '0'
)

INSERT INTO `table` VALUES (1, 1);
INSERT INTO `table` VALUES (2, 2);

Executing query:

SELECT 0 AS x, col1 AS y
FROM `table` 
GROUP BY col1, col2
HAVING y >0
AND x =1

Returns:
+---+---+
| x | y |
+---+---+
| 0 | 1 |
| 0 | 2 |
+---+---+
2 rows in set (0.00 sec)

x is 0 on both rows, so they shouldn't be returned.
[14 Nov 2005 21:22] Timothy Smith
To clarify things, I've changed your example a bit.  In your example, since you were not grouping by 'x', there is some reason to think that perhaps the behavior is undefined in this case (although I'd still argue that isn't true).  But my example groups by x, which makes it clear that there is something wrong happening here.

mysql> select * from t;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql> select 0 as x, col1, col2 from t group by x, col1, col2;
+---+------+------+
| x | col1 | col2 |
+---+------+------+
| 0 |    1 |    1 |
| 0 |    2 |    2 |
+---+------+------+
2 rows in set (0.00 sec)

mysql> select 0 as x, col1, col2 from t group by x, col1, col2 having x = 1;
Empty set (0.00 sec)

mysql> select 0 as x, col1, col2 from t group by x, col1, col2 having x = 1 and col1 > 0;
+---+------+------+
| x | col1 | col2 |
+---+------+------+
| 0 |    1 |    1 |
| 0 |    2 |    2 |
+---+------+------+
2 rows in set (0.00 sec)

I did this on a recent MySQL 4.1 pull from the source tree (4.1.16).

Regards,

Timothy
[31 Jan 2006 6:20] Igor Babaev
I found a simpler query demonstrating this problem:

mysql> elect col1, col2 from t group by col1, col2 having 1 != 1 and col1 > 1;

+------+------+
| col1 | col2 |
+------+------+
|    2 |    2 |
+------+------+
[1 Feb 2006 5:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1980
[3 Feb 2006 4:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2111
[10 Feb 2006 5:32] Igor Babaev
ChangeSet
  1.2469 06/01/31 21:48:32 igor@rurik.mysql.com +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@rurik.mysql.com +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@rurik.mysql.com +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@rurik.mysql.com +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@rurik.mysql.com +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@rurik.mysql.com +16 -0
    Added A test case for bug #14927.

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

The fix will appear in 4.1.19, 5.0.19 and 5.1.7
[21 Feb 2006 22:27] Mike Hillyer
Documented in 4.1.19, 5.0.19, 5.1.7 changelogs:

  <listitem>
        <para>
          A statement containing <literal>GROUP BY</literal> and
          <literal>HAVING</literal> clauses could return incorrect
          results when the <literal>HAVING</literal> clause contained
          logic that returned <literal>FALSE</literal> for every row.
          (Bug #14927)
        </para>
      </listitem>
[6 May 2006 13:32] Paul Dubois
Changeset was not actually applied to 4.1.19. Setting back
to verified until it is.
[7 May 2006 6:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6062
[8 May 2006 2:03] Igor Babaev
I applied the original patch to 4.1
The fix will appear in 4.1.20
[10 May 2006 16:28] Paul Dubois
Changelog entry added to 4.1.20.