Bug #51242 HAVING clause on table join produce incorrect results
Submitted: 17 Feb 2010 13:24 Modified: 20 Jun 2010 22:32
Reporter: Ole John Aske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.1.41/5.0/5.5-xx OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[17 Feb 2010 13:24] Ole John Aske
Description:
The HAVING clause on a joined table fails to eliminate rows which should have been excluded from the result set. In the test case no rows qualifies on 'table2.int_nokey = 8' but this part of the having clause seems to be ignored.

If we either remove the WHERE clause or the second term 'table1.pk >= 6' from
the HAVING clause, the query return a correct (empty) result set.

This bugs looks similar to bug#50995, However, I have tested the patch for
this bug on my testcase wo/ success.

Has also tested the next-mr and 6.0 versions which seems to fail also.

How to repeat:
create table test (pk int primary key, int_nokey int, int_key int);
insert into test values (2,7,9), (4,7,9), (6,2,9), (17,0,9);

SELECT table1.pk, table2.int_nokey 
FROM test AS table1
 JOIN test AS table2 ON table1.int_key = table2.int_key
 WHERE table2.pk = 2
 GROUP BY table1.pk, table2.int_nokey
 HAVING (table2.int_nokey = 8 AND table1.pk >= 6) ;

+----+-----------+
| pk | int_nokey |
+----+-----------+
|  6 |         7 |
| 17 |         7 |
+----+-----------+
2 rows in set (0.00 sec)
[17 Feb 2010 13:57] Miguel Solorzano
Thank you for the bug report. Actually an older bug (present on 5.0.17).

C:\DBS>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > use test
Database changed
mysql 5.0 > create table test (pk int primary key, int_nokey int, int_key int);
Query OK, 0 rows affected (0.07 sec)

mysql 5.0 > insert into test values (2,7,9), (4,7,9), (6,2,9), (17,0,9);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql 5.0 >
mysql 5.0 > SELECT table1.pk, table2.int_nokey
    -> FROM test AS table1
    ->  JOIN test AS table2 ON table1.int_key = table2.int_key
    ->  WHERE table2.pk = 2
    ->  GROUP BY table1.pk, table2.int_nokey
    ->  HAVING (table2.int_nokey = 8 AND table1.pk >= 6) ;
+----+-----------+
| pk | int_nokey |
+----+-----------+
|  6 |         7 |
| 17 |         7 |
+----+-----------+
2 rows in set (0.03 sec)

mysql 5.0 > exit
Bye

C:\DBS>55

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.99-m3-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use test
Database changed
mysql 5.5 >create table test (pk int primary key, int_nokey int, int_key int);
Query OK, 0 rows affected (0.26 sec)

mysql 5.5 >insert into test values (2,7,9), (4,7,9), (6,2,9), (17,0,9);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql 5.5 >
mysql 5.5 >SELECT table1.pk, table2.int_nokey
    -> FROM test AS table1
    ->  JOIN test AS table2 ON table1.int_key = table2.int_key
    ->  WHERE table2.pk = 2
    ->  GROUP BY table1.pk, table2.int_nokey
    ->  HAVING (table2.int_nokey = 8 AND table1.pk >= 6) ;
+----+-----------+
| pk | int_nokey |
+----+-----------+
|  6 |         7 |
| 17 |         7 |
+----+-----------+
2 rows in set (0.01 sec)

mysql 5.5 >

C:\bugs\mysql-5.0.17-win32>bin\mysql -uroot --port=3320
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> create table test (pk int primary key, int_nokey int, int_key int);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test values (2,7,9), (4,7,9), (6,2,9), (17,0,9);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT table1.pk, table2.int_nokey
    -> FROM test AS table1
    ->  JOIN test AS table2 ON table1.int_key = table2.int_key
    ->  WHERE table2.pk = 2
    ->  GROUP BY table1.pk, table2.int_nokey
    ->  HAVING (table2.int_nokey = 8 AND table1.pk >= 6) ;
+----+-----------+
| pk | int_nokey |
+----+-----------+
|  6 |         7 |
| 17 |         7 |
+----+-----------+
2 rows in set (0.00 sec)
[1 Mar 2010 10:15] 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/101828

3344 Sergey Glukhov	2010-03-01
      Bug#51242 HAVING clause on table join produce incorrect results
      The problem is that when we make conditon for
      grouped result const part of condition is cut off.
      It happens because some parts of 'having' condition
      which refer to outer join become const after
      make_join_statistics. The fix is adding 'having'
      condition check for 'having' const tables after
      make_join_statistics is performed.
      Note:
      This patch also fixes problems described in
      Bug#48916, Bug #48044, Bug#48118.
     @ mysql-test/r/having.result
        test result
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        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).
[1 Mar 2010 14:05] 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/101874

3344 Sergey Glukhov	2010-03-01
      Bug#51242 HAVING clause on table join produce incorrect results
      The problem is that when we make conditon for
      grouped result const part of condition is cut off.
      It happens because some parts of 'having' condition
      which refer to outer join become const after
      make_join_statistics. The fix is adding 'having'
      condition check for 'having' const tables after
      make_join_statistics is performed.
      Note:
      This patch also fixes problems described in
      Bug#48916, Bug #48044, Bug#48118.
     @ mysql-test/r/having.result
        test result
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        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).
[10 Mar 2010 12:43] 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/102877

3385 Sergey Glukhov	2010-03-10
      Bug#51242 HAVING clause on table join produce incorrect results
      The problem is that when we make conditon for
      grouped result const part of condition is cut off.
      It happens because some parts of 'having' condition
      which refer to outer join become const after
      make_join_statistics. The fix is adding 'having'
      condition check for 'having' const tables after
      make_join_statistics is performed.
      Note:
      This patch also fixes problems described in
      Bug#48916, Bug #48044, Bug#48118.
     @ mysql-test/r/having.result
        test result
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        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 const table field from
        outer join).
        Note:
        This is applicable only for those conditions
        which do not use aggregate fucntions.
[15 Mar 2010 16:37] 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/103289

3385 Sergey Glukhov	2010-03-15
      Bug#51242 HAVING clause on table join produce incorrect results
      The problem is that when we make conditon for
      grouped result const part of condition is cut off.
      It happens because some parts of 'having' condition
      which refer to outer join become const after
      make_join_statistics. These parts may be lost
      during further having condition transformation
      in JOIN::exec. The fix is adding 'having'
      condition check for const tables after
      make_join_statistics is performed.
     @ sql/sql_select.cc
        added 'having' condition check for const tables
        after make_join_statistics is performed.
[16 Mar 2010 8:50] 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/103363

3401 Sergey Glukhov	2010-03-16
      Bug#51242 HAVING clause on table join produce incorrect results
      The problem is that when we make conditon for
      grouped result const part of condition is cut off.
      It happens because some parts of 'having' condition
      which refer to outer join become const after
      make_join_statistics. These parts may be lost
      during further having condition transformation
      in JOIN::exec. The fix is adding 'having'
      condition check for const tables after
      make_join_statistics is performed.
     @ mysql-test/r/having.result
        test case
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        added 'having' condition check for const tables
        after make_join_statistics is performed.
[16 Mar 2010 10:30] 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/103418

3972 Sergey Glukhov	2010-03-16 [merge]
      mysql-5.1-bugteam->mysql-pe merge(Bug#51242)
     @ mysql-test/r/having.result
        mysql-5.1-bugteam->mysql-pe merge(Bug#51242)
     @ mysql-test/t/having.test
        mysql-5.1-bugteam->mysql-pe merge(Bug#51242)
     @ sql/sql_select.cc
        mysql-5.1-bugteam->mysql-pe merge(Bug#51242)
[19 Mar 2010 8:42] 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/103769

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
[19 Mar 2010 9:09] 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/103783

3411 Sergey Glukhov	2010-03-19
      Bug#51242 HAVING clause on table join produce incorrect results
      The problem is that when we make conditon for
      grouped result const part of condition is cut off.
      It happens because some parts of 'having' condition
      which refer to outer join become const after
      make_join_statistics. These parts may be lost
      during further having condition transformation
      in JOIN::exec. The fix is adding 'having'
      condition check for const tables after
      make_join_statistics is performed.
     @ mysql-test/r/having.result
        test case
     @ mysql-test/t/having.test
        test result
     @ sql/sql_select.cc
        added 'having' condition check for const tables
        after make_join_statistics is performed.
[26 Mar 2010 8:20] Bugs System
Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100320202342-3oapaq7r0t6qhexq) (merge vers: 5.5.3-m2) (pib:16)
[26 Mar 2010 8:25] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)
[26 Mar 2010 8:31] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)
[6 Apr 2010 8:00] Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:john.embretsen@sun.com-20100322090656-w4ixy7p67fb3vr29) (merge vers: 5.1.46) (pib:16)
[8 Apr 2010 8:12] Jon Stephens
Documented bugfix in the 5.1.46, 5.5.4, and 6.0.14 changelogs, as follows:

      A HAVING clause on a joined table in some cases failed to eliminate 
      rows which should have been excluded from the result set.

Closed.
[8 Apr 2010 15:08] Jon Stephens
5.5.4 changelog entry moved to 5.5.5 changelog.
[17 Jun 2010 12:00] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:40] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:27] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)