| 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: | |
| 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 |
[17 Feb 2010 13:24]
Ole John Aske
[17 Feb 2010 13:57]
MySQL Verification Team
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)
