MySQL Bugs: #21540: Subqueries with no from and aggregate functions return wrong results

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:	MacOS (MacOSX/Linux Suse)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

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.

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)

See also http://bugs.mysql.com/bug.php?id=13496

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;

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.

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.

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.

Pushed in 5.0.26/5.1.12

Noted in 5.0.26, 5.1.12 changelog entries.