Bug #21540 Subqueries with no from and aggregate functions return wrong results
Submitted: 9 Aug 2006 15:08 Modified: 10 Oct 2006 18:47
Reporter: Georgi Kodinov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.25-BK/5.1/4.1BK OS:Mac OS X (MacOSX/Linux Suse)
Assigned to: Georgi Kodinov

[9 Aug 2006 15:08] Georgi Kodinov
Description:
MySQL returns garbled values for scalar sub-queries that have no FROM clause and include aggregate function (as reported in bug #16792).
This bug is about the second problem (5.0 specific) that is observed in bug #16792 : the fact that 5.0 returns results of such sub-queries as if they were calculated in the outer context,  but offset with one row.

How to repeat:
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
COUNT(DISTINCT t1.b)    (SELECT COUNT(DISTINCT t1.b))
2       0
1       2
1       1

Suggested fix:
Make these return the correct values as if the sub-queries were executed standalone with the references to outer columns replaced by constants.
[9 Aug 2006 15:33] Miguel Solorzano
Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.25-debug

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

mysql> CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY
    -> t1.a;
+----------------------+-------------------------------+
| COUNT(DISTINCT t1.b) | (SELECT COUNT(DISTINCT t1.b)) |
+----------------------+-------------------------------+
|                    2 |                             0 | 
|                    1 |                             2 | 
|                    1 |                             1 | 
+----------------------+-------------------------------+
3 rows in set (0.06 sec)

mysql> 

miguel@hegel:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta-debug

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

mysql> CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY
    -> t1.a;
+----------------------+-------------------------------+
| COUNT(DISTINCT t1.b) | (SELECT COUNT(DISTINCT t1.b)) |
+----------------------+-------------------------------+
|                    2 |                             0 | 
|                    1 |                             2 | 
|                    1 |                             1 | 
+----------------------+-------------------------------+
3 rows in set (0.05 sec)

mysql> 

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-debug

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

mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY
    -> t1.a;
+----------------------+-------------------------------+
| COUNT(DISTINCT t1.b) | (SELECT COUNT(DISTINCT t1.b)) |
+----------------------+-------------------------------+
|                    2 |                             0 |
|                    1 |                             0 |
|                    1 |                             0 |
+----------------------+-------------------------------+
3 rows in set (0.02 sec)
[14 Aug 2006 10:23] Sergei Golubchik
See also http://bugs.mysql.com/bug.php?id=13496
[31 Aug 2006 12:39] Georgi Kodinov
Handling of aggregates in subqueries that reference columns from the outer context has changed in 5.x.
See the comment in bug #12762 on how the aggregates are evaluted. So what was true for Bug #16792 no longer holds in 5.x. 
The problem observed here is about handling aggregate functions that reference columns in subqueries from outer context only if the outer context has GROUP BY, e.g:
SELECT ... (SELECT ... COUNT(t1.a) ... FROM t2 ... ), ... FROM t1;
[31 Aug 2006 12: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/11159

ChangeSet@1.2253, 2006-08-31 15:43:26+03:00, gkodinov@macbook.gmz +4 -0
  Bug #21540: Subqueries with no from and aggregate functions return
              wrong results
   Mark the containing Item (Item_subselect descendant usually) of 
   a subselect as containing aggregate functions if it has references
   to aggregates functions that are calculated outside its context.
   This tels end_send_group() not to make an Item_subselect descendant in
   select list a copy and causes the correct value being returned.
   Note that for unions it must set the item referenced in the outermost 
   union level.
[7 Sep 2006 9:18] 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/11521

ChangeSet@1.2253, 2006-09-07 12:17:34+03:00, gkodinov@macbook.gmz +4 -0
  Bug #21540: Subqueries with no from and aggregate functions return
              wrong results
   Mark the containing Item (Item_subselect descendant usually) of 
   a subselect as containing aggregate functions if it has references
   to aggregates functions that are calculated outside its context.
   This tels end_send_group() not to make an Item_subselect descendant in
   select list a copy and causes the correct value being returned.
[8 Sep 2006 16: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/11625

ChangeSet@1.2253, 2006-09-08 19:04:46+03:00, gkodinov@macbook.gmz +4 -0
  Bug #21540: Subqueries with no from and aggregate functions return
              wrong results
   Mark the containing Item(s) (Item_subselect descendant usually) of 
   a subselect as containing aggregate functions if it has references
   to aggregates functions that are calculated outside its context.
   This tels end_send_group() not to make an Item_subselect descendant in
   select list a copy and causes the correct value being returned.
[1 Oct 2006 8:59] Georgi Kodinov
Pushed in 5.0.26/5.1.12
[10 Oct 2006 18:47] Paul Dubois
Noted in 5.0.26, 5.1.12 changelog entries.