Why the sum of five 1 = 4

Question

the base query works like intenden, but when I try to sum the first columns, its value should be 5, but insted I get 4, why?

basic request:

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 

amount request

 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 on sqlfiddle: http://sqlfiddle.com/#!2/e80f5/3 as an answer. Counting changes in history with MySQL but confusedly confused

Answer 1

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

The @last_task variable was defined in a separate package request. I am breaking SQL Fiddle queries into separate batches executed separately. I do this so that you can see the output from each batch as a separate result set below. In your Fiddle, you can see that there are two sets of results: http://sqlfiddle.com/#!2/e80f5/3/0 and http://sqlfiddle.com/#!2/e80f5/3/1 . They are mapped to the two operations you are performing (dialing and selecting). The problem is that your set statement defines a variable that exists only in the first batch; when the select statement is executed, this is a separate batch, and your variable is not defined in this context.

To fix this problem, all you have to do is define a different query terminator. Pay attention to the drop-down list / button both in the diagram and in the query panel ([;]) - click on it and you can choose something other than a semicolon (by default). Then your two statements will be included together as part of one batch, and you will get the desired result. For example: http://sqlfiddle.com/#!2/e80f5/9

Answer 2

This is probably a bug in an older version of MySQL.

I tried it on MySQL 5.5 and worked great.