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: | |
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 |
[9 Aug 2006 15:08]
Georgi Kodinov
[9 Aug 2006 15:33]
MySQL Verification Team
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.