MySQL Bugs: #14927: HAVING ignores conditions

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:	Windows (Windows XP)
Assigned to:	Igor Babaev	CPU Architecture:	Any

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.

Could you please provide the complete test case, create table,
insert rows, query and the result you got/expected.

Thanks in advance.

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.

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

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 |
+------+------+

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

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

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

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>

Changeset was not actually applied to 4.1.19. Setting back
to verified until it is.

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

I applied the original patch to 4.1
The fix will appear in 4.1.20

Changelog entry added to 4.1.20.