MySQL – Simultaneous float increment and assignment in SELECT breaks prior to 5.6

Question

Why do queries like SELECT (@sum:=(@var:=@sum)+some_table.val)... automatically cast (@var:=@sum) to integer in MySQL ≤5.5 for DECIMAL-type some_table.val and floor it for DOUBLE/FLOAT? What feature was changed to allow for the expected behaviour in 5.6?

---

Context & Elaboration:

Consider the following table:

CREATE TABLE t ( 
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    sum_component FLOAT 
);
INSERT INTO t (sum_component) VALUES (0.5), (0.6), (0.4), (0.5);

I am designing a cumulative sum query, which fetches the id of the entry where the cumulative sum is greater than some value. Normally, this query would fit the bill:

SELECT t.id, 
    @cumulative_sum
    FROM t 
    CROSS JOIN (SELECT @cumulative_sum:=0) a 
    WHERE (@cumulative_sum:=@cumulative_sum+t.sum_component) > 1.3 
    ORDER BY id ASC LIMIT 1;

...but I happen to also need to store the cumulative sum before the selected entry as well for later calculations, and cumulative_sum from this query does not return the expected result, double-counting the last entry. In this case, I would want this query to set a variable that stores the value 1.1 (0.5 + 0.6) without having to do additional math.

If I assign the old value of @cumulative_sum to @another_variable in the incrementation step, I should be able to do precisely this.

SELECT t.id, 
    @cumulative_sum
    FROM t 
    CROSS JOIN (SELECT @cumulative_sum:=0) a 
    WHERE (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3
    ORDER BY id ASC LIMIT 1;

On two of my machines — one running MySQL 5.6 and another running MariaDB 10.0.7 — the above query performs as expected:

MariaDB [a51]>     SELECT  t.id, @cumulative_sum
    FROM  t
    CROSS JOIN  
      ( SELECT  @cumulative_sum:=0) a
    WHERE  (@cumulative_sum:=(@another_variable:=@cumulative_sum)
                             +t.sum_component) > 1.3
    ORDER BY  id ASC
    LIMIT  1;

+----+--------------------+
| id | @cumulative_sum    |
+----+--------------------+
|  3 | 1.5000000298023224 |
+----+--------------------+
1 row in set (0.00 sec)

MariaDB [a51]> SELECT @another_variable;
+-------------------+
| @another_variable |
+-------------------+
| 1.100000023841858 |
+-------------------+
1 row in set (0.01 sec)

but on MySQL 5.5, it doesn't:

mysql>     SELECT  t.id, @cumulative_sum
    FROM  t
    CROSS JOIN  
      ( SELECT  @cumulative_sum:=0) a
    WHERE  (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3
    ORDER BY  id ASC
    LIMIT  1;

Empty set (0.18 sec)

mysql> SELECT @another_variable;
+-------------------+
| @another_variable |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.01 sec)

---

Observing how the query increments, we can see the underlying problem. Here are the results with a FLOAT sum_component:

mysql>     SELECT  t.id, (@cumulative_sum := (@another_variable:=@cumulative_sum)
                  + t.sum_component) AS cumulative_sum,
        sum_component
    FROM  t
    CROSS JOIN  
      ( SELECT  @cumulative_sum:=0) a
    ORDER BY  id ASC;

+----+----------------+---------------+
| id | cumulative_sum | sum_component |
+----+----------------+---------------+
|  1 |            0.5 |           0.5 |
|  2 |            0.6 |           0.6 |
|  3 |            0.4 |           0.4 |
|  4 |            0.5 |           0.5 |
+---+----------------+----------------+
4 rows in set (0.04 sec)

and here's what having a DECIMAL sum_component looks like:

mysql> ALTER TABLE t MODIFY sum_component DECIMAL(4,2);
Query OK, 4 rows affected, 2 warnings (0.16 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql>     SELECT  t.id, (@cumulative_sum := (@another_variable:=@cumulative_sum) 
                           + t.sum_component) AS cumulative_sum,
        sum_component
    FROM  t
    CROSS JOIN  
      ( SELECT  @cumulative_sum:=0) a
    ORDER BY  id ASC;

+----+----------------+---------------+
| id | cumulative_sum | sum_component |
+----+----------------+---------------+
|  1 |           0.50 |          0.50 |
|  2 |           1.60 |          0.60 |
|  3 |           2.40 |          0.40 |
|  4 |           2.50 |          0.50 |
+----+----------------+---------------+
4 rows in set (0.18 sec)

SQL Fiddle

Answer 1

This is because MySQL, in the older version, is treating 0 as an integer; your initial assignment @cumulative_sum:=0 is setting the variable to an integer. Changing the assignment to @cumulative_sum:=0.0 results in the desired behaviour on 5.5:

SELECT t.id,
    @cumulative_sum
    FROM t
    CROSS JOIN (SELECT @cumulative_sum:=0.0) a
    WHERE (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3
    ORDER BY id ASC LIMIT 1;

The manual addresses this issue, but I was unable to find mention of a change in behaviour between 5.5 and 5.6:

Another issue with assigning a value to a variable and reading the value within the same non-SET statement is that the default result type of a variable is based on its type at the start of the statement. The following example illustrates this:

mysql> SET @a='test';

mysql> SELECT @a,(@a:=20) FROM tbl_name;

For this SELECT statement, MySQL reports to the client that column one is a string and converts all accesses of @a to strings, even though @a is set to a number for the second row. After the SELECT statement executes, @a is regarded as a number for the next statement.

To avoid problems with this behavior, either do not assign a value to and read the value of the same variable within a single statement, or else set the variable to 0, 0.0, or '' to define its type before you use it.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.44-log Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE t ( 
    ->     id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    ->     sum_component FLOAT 
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t (sum_component) VALUES (0.5), (0.6), (0.4), (0.5);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT t.id,
    ->     @cumulative_sum
    ->     FROM t
    ->     CROSS JOIN (SELECT @cumulative_sum:=0.0) a
    ->     WHERE (@cumulative_sum:=(@another_variable:=@cumulative_sum)+t.sum_component) > 1.3
    ->     ORDER BY id ASC LIMIT 1;
+----+----------------------------------+
| id | @cumulative_sum                  |
+----+----------------------------------+
|  3 | 1.500000029802322400000000000000 |
+----+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @another_variable;
+-------------------+
| @another_variable |
+-------------------+
| 1.100000023841858 |
+-------------------+
1 row in set (0.00 sec)