Bug #23408 Aggregate functions fail, with join

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;

Test case.

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

5.0.26-max-log

Thank you for the report.

I can not repeat it using last BK sources.

What about against 5.0.26(-max-log)?

What the result you expect to get?

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)

verified, works ok with 5.0.25 but fails with latest 5.0bk source

mysqltest test case

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

Duplicate of bug#23800