| Bug #27802 | Aggregation functions both inside and outside a subquery | ||
|---|---|---|---|
| Submitted: | 13 Apr 2007 8:16 | Modified: | 13 Apr 2007 9:07 |
| Reporter: | Jordi Giménez | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.37-community-nt | OS: | Windows (XP SP2) |
| Assigned to: | CPU Architecture: | Any | |
[13 Apr 2007 9:07]
Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with latest 5.0.40-BK on Linux:
openxs@suse:~/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 1
Server version: 5.0.40 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DROP TABLE tst;
ERROR 1051 (42S02): Unknown table 'tst'
mysql> CREATE TABLE tst (a int, b int);
INQuery OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tst VALUES (1,2), (2,3), (2,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT (SELECT COUNT(*) FROM tst i WHERE i.b=o.b LIMIT 1)+0*COUNT(*) AS
r1,
-> (SELECT COUNT(*) FROM tst i WHERE i.b=o.b LIMIT 1) as r2
, b as
-> r3
-> FROM tst o GROUP BY b;
+------+------+------+
| r1 | r2 | r3 |
+------+------+------+
| 1 | 1 | 2 |
| 2 | 2 | 3 |
+------+------+------+
2 rows in set (0.00 sec)
mysql> SELECT (SELECT COUNT(*) FROM tst i WHERE i.b=o.b LIMIT 1) AS r1,
(SELECT COUNT(*) FROM tst i WHERE i.b=o.b LIMIT 1) as r2, b as r3 FRO
M tst o GROUP BY b;
+------+------+------+
| r1 | r2 | r3 |
+------+------+------+
| 1 | 1 | 2 |
| 2 | 2 | 3 |
+------+------+------+
2 rows in set (0.00 sec)
So, looks like this problem is already fixed somehow.
Description: It seems that an expression with subqueries can not handle correctly aggregation functions both inside and outside the subquery. It could be related to #27348, not tested. How to repeat: DROP TABLE tst; CREATE TABLE tst (a int, b int); INSERT INTO tst VALUES (1,2), (2,3), (2,3); SELECT (SELECT COUNT(*) FROM tst i WHERE i.b=o.b LIMIT 1)+0*COUNT(*) AS r1, (SELECT COUNT(*) FROM tst i WHERE i.b=o.b LIMIT 1) as r2, b as r3 FROM tst o GROUP BY b; Results: r1 r2 r3 -- -- -- 2 1 2 2 2 3 Suggested fix: It seems that the presence of the 0*COUNT(*) is disturbing somehow the computation of r1. Do not know how to fix.