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:
None 
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
Description:
A particular query consisting of a subselect, a join, and a group by is returning incorrect results.

How to repeat:
Create a database and insert the attached .sql.gz file to create the necessary tables and initial data.

Then execute the query:
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 ; 

The query results in the following output on 5.0.18 (release) and 5.0.19-nightly-20060115:
+------+------+
| X    | Y    |
+------+------+
|  514 |    0 |
| 1051 |  514 |
|  368 | 1051 |
|   13 |  368 |
|  423 |   13 |
|    9 |  423 |
|    8 |    9 |
+------+------+

Note that the values in Y appear to be matched up with the "wrong" X.

On 4.0.10, the Y column is filled with zeroes instead.

I expect that the Y column should have the same values as the X column.
[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.