Bug #16792 | query with subselect, join, and group not returning proper values | ||
---|---|---|---|
Submitted: | 25 Jan 2006 22:27 | Modified: | 25 Sep 2006 17:44 |
Reporter: | John Bafford | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.0.19-nightly-20060115 | OS: | Linux (linux) |
Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[25 Jan 2006 22:27]
John Bafford
[25 Jan 2006 22:31]
John Bafford
Table creation data for reproducing this bug
Attachment: subselect_group_bug.sql.gz (application/x-gzip, text), 31.99 KiB.
[26 Jan 2006 14:01]
MySQL Verification Team
Thank you for the bug report. Your MySQL connection id is 1 to server version: 5.0.19-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> source /home/miguel/t/subselect_group_bug.sql Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 7600 rows affected (0.29 sec) Records: 7600 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.05 sec) Query OK, 0 rows affected (0.04 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 2386 rows affected (0.13 sec) Records: 2386 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.02 sec) mysql> select count(distinct _BUG_AAA.articleID) X, (select count(distinct -> _BUG_AAA.articleID)) Y from _BUG_ASA INNER JOIN _BUG_AAA ON -> (_BUG_ASA.articleID = _BUG_AAA.articleID and _BUG_AAA.attrID=13 and -> _BUG_AAA.biasID=407) group by _BUG_ASA.srcID ; +------+------+ | X | Y | +------+------+ | 514 | 0 | | 1051 | 514 | | 368 | 1051 | | 13 | 368 | | 423 | 13 | | 9 | 423 | | 8 | 9 | +------+------+ 7 rows in set (0.21 sec) mysql> alter table _BUG_ASA drop index articleID; Query OK, 7600 rows affected (0.06 sec) Records: 7600 Duplicates: 0 Warnings: 0 mysql> alter table _BUG_ASA drop index srcID; Query OK, 7600 rows affected (0.03 sec) Records: 7600 Duplicates: 0 Warnings: 0 mysql> alter table _BUG_AAA drop index articleID; Query OK, 2386 rows affected (0.04 sec) Records: 2386 Duplicates: 0 Warnings: 0 mysql> alter table _BUG_AAA drop index attrVal; Query OK, 2386 rows affected (0.02 sec) Records: 2386 Duplicates: 0 Warnings: 0 mysql> alter table _BUG_AAA drop index attrID; Query OK, 2386 rows affected (0.02 sec) Records: 2386 Duplicates: 0 Warnings: 0 mysql> select count(distinct _BUG_AAA.articleID) X, (select count(distinct -> _BUG_AAA.articleID)) Y from _BUG_ASA INNER JOIN _BUG_AAA ON -> (_BUG_ASA.articleID = _BUG_AAA.articleID and _BUG_AAA.attrID=13 and -> _BUG_AAA.biasID=407) group by _BUG_ASA.srcID ; +------+---+ | X | Y | +------+---+ | 514 | 0 | | 1051 | 0 | | 368 | 0 | | 13 | 0 | | 423 | 0 | | 9 | 0 | | 8 | 0 | +------+---+ 7 rows in set (8.16 sec)
[9 Aug 2006 15:25]
Georgi Kodinov
Actually this bug reveals two (separate) problems with the values returned from aggregate functions from sub-queries with no FROM clause : 1. 4.x server calculates the aggregate functions as if those sub-queries have zero rows. 2. 5.x different from 4.x in calculating the values for the aggregate functions as if they're in the outer context for the subquery, but with one row offset. The correct behavior seems to be the following : The server must calculate the aggregate functions in a subquery with no FROM clause as if there is one row. This means that the following query should return 1 : SELECT count(12) This also means that the following should happen : mysql> select count(distinct _BUG_AAA.articleID) X, (select count(distinct -> _BUG_AAA.articleID)) Y from _BUG_ASA INNER JOIN _BUG_AAA ON -> (_BUG_ASA.articleID = _BUG_AAA.articleID and _BUG_AAA.attrID=13 and -> _BUG_AAA.biasID=407) group by _BUG_ASA.srcID ; +------+---+ | X | Y | +------+---+ | 514 | 1 | | 1051 | 1 | | 368 | 1 | | 13 | 1 | | 423 | 1 | | 9 | 1 | | 8 | 1 | +------+---+ 7 rows in set (8.16 sec) This is because the '(select count(distinct _BUG_AAA.articleID))' subquery operates on exactly one row and during the time of it's calculation '_BUG_AAA.articleID' can be considered a constant. I will continue to track the first 'sub-bug' here. I've opened a separate bug report (Bug #21540) for the second 'sub-bug'.
[10 Aug 2006 13:45]
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/10268 ChangeSet@1.2532, 2006-08-10 16:45:02+03:00, gkodinov@macbook.gmz +6 -0 Bug #16792 query with subselect, join, and group not returning proper values Treat queries with no FROM and aggregate functions as normal queries, so the aggregate function get correctly calculated as if there is 1 row. This means that they will be considered to have one row, so COUNT(*) will return 1 instead of 0. Other aggregates will behave in compatible manner.
[14 Aug 2006 11:21]
Sergei Golubchik
See also http://bugs.mysql.com/bug.php?id=13496
[19 Sep 2006 8:27]
Georgi Kodinov
Pushed into 4.1.22/5.0.26/5.1.12-beta
[25 Sep 2006 17:44]
Paul DuBois
Noted in 4.1.22, 5.0.26, 5.1.12.