MySQL Bugs: #5212: using variables in aggregate functions does not work

Bug #5212	using variables in aggregate functions does not work
Submitted:	25 Aug 2004 19:56	Modified:	25 Aug 2004 22:19
Reporter:	Javier Mendiara	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	4.0.12	OS:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any

Description:
In 
http://dev.mysql.com/doc/mysql/en/Extensions_to_ANSI.html
you say:

mysql> SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg
      -> FROM test_table;

Using variables in agregatte functions does not work propertly. avg doesnt have a correct value.
the correct result is delayed one row in the result

Plus, in the avobe sentence explained in documentation, it doesnt work if you dont add a GROUP BY. 

Best regards
Javier

How to repeat:

CREATE TABLE `afsc_votes` (
  `vote_id` mediumint(8) NOT NULL auto_increment,
  `photo_id` mediumint(8) NOT NULL default '0',
  `user_id` mediumint(8) NOT NULL default '0',
  `vote_vote` int(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`vote_id`),
  KEY `photo_id` (`photo_id`,`user_id`)
) TYPE=MyISAM PACK_KEYS=0 COMMENT='votos de las fotos' AUTO_INCREMENT=155 ;

INSERT INTO `afsc_votes` VALUES (1, 340, -1, 4);
INSERT INTO `afsc_votes` VALUES (2, 101, 53, 4);
INSERT INTO `afsc_votes` VALUES (3, 54, 53, 5);
INSERT INTO `afsc_votes` VALUES (4, 59, 53, 1);
INSERT INTO `afsc_votes` VALUES (5, 56, 53, 0);
INSERT INTO `afsc_votes` VALUES (6, 101, 43, 2);
INSERT INTO `afsc_votes` VALUES (7, 45, 43, 4);
INSERT INTO `afsc_votes` VALUES (8, 257, 43, 4);
INSERT INTO `afsc_votes` VALUES (9, 290, 43, 5);
INSERT INTO `afsc_votes` VALUES (10, 245, 43, 5);
INSERT INTO `afsc_votes` VALUES (11, 257, -1, 5);
INSERT INTO `afsc_votes` VALUES (12, 303, -1, 3);
INSERT INTO `afsc_votes` VALUES (13, 177, 43, 5);
INSERT INTO `afsc_votes` VALUES (14, 172, 43, 5);
INSERT INTO `afsc_votes` VALUES (15, 170, 7, 4);
INSERT INTO `afsc_votes` VALUES (16, 303, 43, 2);
INSERT INTO `afsc_votes` VALUES (17, 329, -1, 1);
INSERT INTO `afsc_votes` VALUES (18, 321, -1, 3);
INSERT INTO `afsc_votes` VALUES (19, 343, 43, 5);
INSERT INTO `afsc_votes` VALUES (20, 342, 43, 4);
INSERT INTO `afsc_votes` VALUES (21, 327, 96, 5);
INSERT INTO `afsc_votes` VALUES (22, 257, 96, 4);
INSERT INTO `afsc_votes` VALUES (23, 100, 43, 4);
INSERT INTO `afsc_votes` VALUES (24, 337, -1, 4);
INSERT INTO `afsc_votes` VALUES (25, 88, 43, 5);
INSERT INTO `afsc_votes` VALUES (26, 345, 43, 5);
INSERT INTO `afsc_votes` VALUES (27, 232, -1, 5);
INSERT INTO `afsc_votes` VALUES (28, 307, -1, 5);
INSERT INTO `afsc_votes` VALUES (29, 103, 87, 5);
INSERT INTO `afsc_votes` VALUES (30, 329, 43, 0);
INSERT INTO `afsc_votes` VALUES (31, 69, -1, 5);
INSERT INTO `afsc_votes` VALUES (141, 53, 43, 3);
INSERT INTO `afsc_votes` VALUES (33, 172, 54, 3);
INSERT INTO `afsc_votes` VALUES (34, 171, 54, 3);
INSERT INTO `afsc_votes` VALUES (35, 109, 54, 3);
INSERT INTO `afsc_votes` VALUES (36, 103, 54, 3);
INSERT INTO `afsc_votes` VALUES (37, 101, 54, 3);
INSERT INTO `afsc_votes` VALUES (38, 100, 54, 3);
INSERT INTO `afsc_votes` VALUES (39, 98, 54, 0);
INSERT INTO `afsc_votes` VALUES (40, 97, 54, 3);
INSERT INTO `afsc_votes` VALUES (41, 351, 54, 3);
INSERT INTO `afsc_votes` VALUES (42, 349, 54, 3);
INSERT INTO `afsc_votes` VALUES (43, 347, 54, 3);
INSERT INTO `afsc_votes` VALUES (44, 346, 54, 3);
INSERT INTO `afsc_votes` VALUES (45, 96, 54, 3);
INSERT INTO `afsc_votes` VALUES (46, 113, 10, 3);
INSERT INTO `afsc_votes` VALUES (147, 161, 17, 5);
INSERT INTO `afsc_votes` VALUES (48, 54, 26, 3);
INSERT INTO `afsc_votes` VALUES (49, 101, 26, 3);
INSERT INTO `afsc_votes` VALUES (50, 12, 26, 3);
INSERT INTO `afsc_votes` VALUES (146, 368, 96, 4);
INSERT INTO `afsc_votes` VALUES (52, 103, 29, 5);
INSERT INTO `afsc_votes` VALUES (53, 98, 29, 3);
INSERT INTO `afsc_votes` VALUES (54, 100, 29, 3);
INSERT INTO `afsc_votes` VALUES (145, 370, 96, 3);
INSERT INTO `afsc_votes` VALUES (56, 96, 29, 3);
INSERT INTO `afsc_votes` VALUES (57, 30, 29, 3);
INSERT INTO `afsc_votes` VALUES (58, 103, 10, 3);
INSERT INTO `afsc_votes` VALUES (59, 10, 29, 3);
INSERT INTO `afsc_votes` VALUES (60, 100, 10, 3);
INSERT INTO `afsc_votes` VALUES (61, 97, 10, 3);
INSERT INTO `afsc_votes` VALUES (62, 104, 10, 5);
INSERT INTO `afsc_votes` VALUES (63, 105, 10, 3);
INSERT INTO `afsc_votes` VALUES (64, 31, 10, 3);
INSERT INTO `afsc_votes` VALUES (65, 21, 10, 3);
INSERT INTO `afsc_votes` VALUES (66, 20, 10, 3);
INSERT INTO `afsc_votes` VALUES (67, 10, 10, 3);
INSERT INTO `afsc_votes` VALUES (68, 95, 10, 3);
INSERT INTO `afsc_votes` VALUES (69, 51, 10, 3);
INSERT INTO `afsc_votes` VALUES (70, 239, 10, 3);
INSERT INTO `afsc_votes` VALUES (71, 238, 10, 3);
INSERT INTO `afsc_votes` VALUES (72, 35, 10, 3);
INSERT INTO `afsc_votes` VALUES (73, 34, 10, 3);
INSERT INTO `afsc_votes` VALUES (74, 54, 10, 3);
INSERT INTO `afsc_votes` VALUES (75, 102, 10, 3);
INSERT INTO `afsc_votes` VALUES (76, 226, 7, 3);
INSERT INTO `afsc_votes` VALUES (77, 53, 10, 3);
INSERT INTO `afsc_votes` VALUES (78, 53, 53, 4);
INSERT INTO `afsc_votes` VALUES (79, 253, -1, 3);
INSERT INTO `afsc_votes` VALUES (80, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (81, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (82, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (83, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (84, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (85, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (86, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (87, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (88, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (89, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (90, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (91, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (92, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (93, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (94, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (95, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (96, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (97, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (98, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (99, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (100, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (101, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (102, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (103, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (104, 340, -1, 4);
INSERT INTO `afsc_votes` VALUES (105, 253, -1, 5);
INSERT INTO `afsc_votes` VALUES (106, 317, 10, 3);
INSERT INTO `afsc_votes` VALUES (107, 154, 96, 3);
INSERT INTO `afsc_votes` VALUES (108, 82, -1, 3);
INSERT INTO `afsc_votes` VALUES (109, 232, -1, 3);
INSERT INTO `afsc_votes` VALUES (110, 224, -1, 3);
INSERT INTO `afsc_votes` VALUES (111, 97, 43, 4);
INSERT INTO `afsc_votes` VALUES (112, 177, 10, 3);
INSERT INTO `afsc_votes` VALUES (144, 103, 43, 4);
INSERT INTO `afsc_votes` VALUES (114, 174, 10, 3);
INSERT INTO `afsc_votes` VALUES (115, 174, 29, 3);
INSERT INTO `afsc_votes` VALUES (143, 102, 43, 5);
INSERT INTO `afsc_votes` VALUES (117, 344, 29, 3);
INSERT INTO `afsc_votes` VALUES (140, 54, 43, 2);
INSERT INTO `afsc_votes` VALUES (119, 173, 29, 3);
INSERT INTO `afsc_votes` VALUES (120, 71, 103, 0);
INSERT INTO `afsc_votes` VALUES (121, 79, 103, 5);
INSERT INTO `afsc_votes` VALUES (122, 77, 103, 0);
INSERT INTO `afsc_votes` VALUES (123, 80, 103, 5);
INSERT INTO `afsc_votes` VALUES (124, 85, 103, 5);
INSERT INTO `afsc_votes` VALUES (125, 74, 103, 0);
INSERT INTO `afsc_votes` VALUES (126, 84, 103, 0);
INSERT INTO `afsc_votes` VALUES (142, 98, 43, 1);
INSERT INTO `afsc_votes` VALUES (128, 343, 53, 3);
INSERT INTO `afsc_votes` VALUES (129, 344, 53, 3);
INSERT INTO `afsc_votes` VALUES (130, 360, 53, 3);
INSERT INTO `afsc_votes` VALUES (131, 97, 29, 3);
INSERT INTO `afsc_votes` VALUES (132, 53, 29, 3);
INSERT INTO `afsc_votes` VALUES (133, 31, 29, 3);
INSERT INTO `afsc_votes` VALUES (134, 34, 29, 3);
INSERT INTO `afsc_votes` VALUES (135, 49, 29, 3);
INSERT INTO `afsc_votes` VALUES (136, 340, 29, 5);
INSERT INTO `afsc_votes` VALUES (137, 339, 29, 5);
INSERT INTO `afsc_votes` VALUES (138, 248, -1, 4);
INSERT INTO `afsc_votes` VALUES (139, 360, 96, 3);
INSERT INTO `afsc_votes` VALUES (148, 353, 17, 3);
INSERT INTO `afsc_votes` VALUES (149, 356, 17, 0);
INSERT INTO `afsc_votes` VALUES (150, 359, 17, 5);
INSERT INTO `afsc_votes` VALUES (151, 348, 77, 3);
INSERT INTO `afsc_votes` VALUES (152, 350, 77, 3);
INSERT INTO `afsc_votes` VALUES (153, 189, 96, 3);
INSERT INTO `afsc_votes` VALUES (154, 338, -1, 3);

SELECT @o := SUM( vote_vote ) AS votes, @p := COUNT( vote_vote ) AS total,
 (@o / @p) AS maxim, photo_id
FROM afsc_votes 
GROUP BY photo_id;

Result: 

votes  total  maxim  photo_id  
6 2 NULL 10                         <--  see here! NULL!!!
3 1 3 12 
3 1 3 20 
3 1 3 21 
3 1 3 30 
6 2 3 31 
6 2 3 34 
3 1 3 35 
4 1 3 45 
3 1 4 49 
3 1 3 51 
13 4 3 53                                 <---- See here (13/4) != 3. Look down!
13 4 3.25 54                            <---- See here. yeah! ok!
0 1 3.25 56                              <---- See here (0/1) != 3.25 
1 1 0 59 
5 1 1 69 
0 1 5 71 
0 1 0 74 
0 1 0 77 
5 1 0 79 
5 1 5 80 

Etc etc....

Suggested fix:
...

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

see http://dev.mysql.com/doc/mysql/en/Variables.html :

Note: In a SELECT statement, each expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, you cannot refer to an expression that involves variables that are set in the SELECT list. For example, the following statement will not work as expected:

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

The reference to b in the HAVING clause refers to an alias for an expression in the SELECT list that uses @aa. This does not work as expected: @aa will not contain the value of the current row, but the value of id from the previous selected row.

The general rule is to never assign and use the same variable in the same statement.