Bug #23408 Aggregate functions fail, with join
Submitted: 18 Oct 2006 3:51 Modified: 12 Dec 2006 16:35
Reporter: Arkadiy Sudarikov Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.26 OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any

[18 Oct 2006 3:51] Arkadiy Sudarikov
Description:
Using COUNT, SUM.

Goes haywire.

Order is mixed up.

How to repeat:
CREATE TABLE `A` (
  `Id` int(11) default NULL,
  `Count` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `A` VALUES (1,5),(2,5);

CREATE TABLE `B` (
  `Id` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `B` VALUES (1),(1),(2),(1);

SELECT
	A.*,
	@Sum:=SUM(A.Count) Sum,
	@Count:=(SELECT COUNT(B.Id) FROM B WHERE B.Id=A.Id) Count,
	SUM(A.Count)-(SELECT COUNT(B.Id) FROM B WHERE B.Id=A.Id) X
FROM
	A
GROUP BY
	A.Id;

+------+-------+------+-------+------+
| Id   | Count | Sum  | Count | X    |
+------+-------+------+-------+------+
|    1 |     5 |    5 |     3 |    4 | 
|    2 |     5 |    5 |     1 |    4 | 
+------+-------+------+-------+------+
2 rows in set (0.01 sec)

SELECT
	A.*,
	@Sum:=SUM(A.Count) Sum,
	@Count:=(SELECT COUNT(B.Id) FROM B WHERE B.Id=A.Id) Count,
	@Sum-@Count X
FROM
	A
GROUP BY
	A.Id;

+------+-------+------+-------+----------------------------------+
| Id   | Count | Sum  | Count | X                                |
+------+-------+------+-------+----------------------------------+
|    1 |     5 |    5 |     3 | 2.000000000000000000000000000000 | 
|    2 |     5 |    5 |     1 | 4.000000000000000000000000000000 | 
+------+-------+------+-------+----------------------------------+
2 rows in set (0.02 sec)

SELECT
	A.*,
	@Sum:=SUM(A.Count) Sum,
	@Count:=(SELECT COUNT(B.Id) FROM B WHERE B.Id=A.Id) Count,
	SUM(A.Count)-@Count X
FROM
	A
GROUP BY
	A.Id;

+------+-------+------+-------+------+
| Id   | Count | Sum  | Count | X    |
+------+-------+------+-------+------+
|    1 |     5 |    5 |     3 |    4 | 
|    2 |     5 |    5 |     1 |    2 | 
+------+-------+------+-------+------+
2 rows in set (0.02 sec)

SELECT
	A.*,
	@Sum:=SUM(A.Count) Sum,
	@Count:=(SELECT COUNT(B.Id) FROM B WHERE B.Id=A.Id) Count,
	@Sum-(SELECT COUNT(B.Id) FROM B WHERE B.Id=A.Id) X
FROM
	A
GROUP BY
	A.Id;

+------+-------+------+-------+----------------------------------+
| Id   | Count | Sum  | Count | X                                |
+------+-------+------+-------+----------------------------------+
|    1 |     5 |    5 |     3 | 2.000000000000000000000000000000 | 
|    2 |     5 |    5 |     1 | 4.000000000000000000000000000000 | 
+------+-------+------+-------+----------------------------------+
2 rows in set (0.00 sec)

Suggested fix:
SELECT
	A.*,
	IFNULL(B.X, 0) Count,
	SUM(A.Count)-IFNULL(B.X, 0) X
FROM
	A
	LEFT JOIN (

SELECT
	B.*,
	COUNT(B.Id) X
FROM
	B
GROUP BY
	B.Id

) B USING (Id)
GROUP BY
	A.Id;
[18 Oct 2006 3:55] Arkadiy Sudarikov
Test case.

Attachment: test_case.sql (application/octet-stream, text), 1.10 KiB.

[18 Oct 2006 4:01] Arkadiy Sudarikov
5.0.26-max-log
[18 Oct 2006 7:03] Sveta Smirnova
Thank you for the report.

I can not repeat it using last BK sources.
[18 Oct 2006 7:18] Arkadiy Sudarikov
What about against 5.0.26(-max-log)?
[18 Oct 2006 13:58] Sveta Smirnova
What the result you expect to get?
[18 Oct 2006 16:50] Arkadiy Sudarikov
SELECT
	A.*,
	IFNULL(B.X, 0) Count,
	SUM(A.Count)-IFNULL(B.X, 0) X
FROM
	A
	LEFT JOIN (

SELECT
	B.*,
	COUNT(B.Id) X
FROM
	B
GROUP BY
	B.Id

) B USING (Id)
GROUP BY
	A.Id;

+------+-------+-------+------+
| Id   | Count | Count | X    |
+------+-------+-------+------+
|    1 |     5 |     3 |    2 | 
|    2 |     5 |     1 |    4 | 
+------+-------+-------+------+
2 rows in set (0.00 sec)
[19 Oct 2006 11:23] Hartmut Holzgraefe
verified, works ok with 5.0.25 but fails with latest 5.0bk source
[19 Oct 2006 11:26] Hartmut Holzgraefe
mysqltest test case

Attachment: bug23408.tgz (application/x-gtar, text), 951 bytes.

[12 Dec 2006 16:35] Evgeny Potemkin
Duplicate of bug#23800