why is the sum of five 1 = 4

Question

the base query works as intenden, but when i try to sum the first columns, its supose to be 5, but insted i get 4, why?

base query:

SET @last_task = 0;
SELECT 
  IF(@last_task = RobotShortestPath, 0, 1) AS new_task,
  @last_task := RobotShortestPath
FROM rob_log
ORDER BY rog_log_id;

1   1456
0   1456
0   1456
1   1234
0   1234
1   1456
1   2556
1   1456

sum query

SET @last_task = 0;
SELECT SUM(new_task) AS tasks_performed
FROM (
  SELECT 
    IF(@last_task = RobotShortestPath, 0, 1) AS new_task,
    @last_task := RobotShortestPath
  FROM rob_log
  ORDER BY rog_log_id
) AS tmp

4

table structure

CREATE TABLE rob_log (
  rog_log_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
#  RobotPosX FLOAT NOT NULL,
#  RobotPosY FLOAT NOT NULL,
#  RobotPosDir TINYINT UNSIGNED NOT NULL,
  RobotShortestPath MEDIUMINT UNSIGNED NOT NULL,
  PRIMARY KEY(rog_log_id),
  KEY (rog_log_id, RobotShortestPath)
);

INSERT INTO rob_log(RobotShortestPath) SELECT 1456;
INSERT INTO rob_log(RobotShortestPath) SELECT 1456;
INSERT INTO rob_log(RobotShortestPath) SELECT 1456;
INSERT INTO rob_log(RobotShortestPath) SELECT 1234;
INSERT INTO rob_log(RobotShortestPath) SELECT 1234;
INSERT INTO rob_log(RobotShortestPath) SELECT 1456;
INSERT INTO rob_log(RobotShortestPath) SELECT 2556;
INSERT INTO rob_log(RobotShortestPath) SELECT 1456;

testing it at sqlfiddle: http://sqlfiddle.com/#!2/e80f5/3 as an answer for Counting changes in timeline with MySQL but got relly confused

Answer 1

Here's the reason (as discussed on Twitter):

The variable @last_task was defined in a separate query "batch". I break up the queries on SQL Fiddle into individual batches, executed separately. I do this so you can see the output from each batch as a distinct result set below. In your Fiddle, you can see that there are two sets of output: http://sqlfiddle.com/#!2/e80f5/3/0 and http://sqlfiddle.com/#!2/e80f5/3/1. These map to the two statements you are running (the set and the select). The problem is, your set statement defines a variable that only exists in the first batch; when the select statement runs, it is a separate batch and your variable isn't defined within that context.

To correct this problem, all you have to do is define a different query terminator. Note the dropdown box/button under both the schema and the query panels ( [ ; ] ) - click on that, and you can choose something other than semicolon (the default). Then your two statements will be included together as part of the same batch, and you'll get the result you want. For example: http://sqlfiddle.com/#!2/e80f5/9

Answer 2

It's probably a some bug in older version of MySQL.

I have tried it on MySQL 5.5 and its working perfectly.