mysql - MySQL - SELECT中的同时浮点递增和赋值在5.6之前中断

为什么像SELECT (@sum:=(@var:=@sum)+some_table.val)...这样的查询会自动将(@var:=@sum)强制转换为mysql中的整数，对于DECIMAL-typesome_table.val和floor it forDOUBLE/FLOAT？为了实现5.6中的预期行为，更改了什么特性？
背景与阐述：
考虑下表：

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);

我正在设计一个累积和查询，它获取条目的id，其中累积和大于某个值。通常情况下，此查询将适合帐单：

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;

…但是我碰巧也需要在所选条目之前存储累积和，以便以后进行计算，并且来自此查询的cumulative_sum不会返回预期结果，对最后一个条目进行重复计数。在这种情况下，我希望这个查询设置一个变量来存储值1.1（0.5 + 0.6）。
如果我在递增步骤中将@cumulative_sum的旧值赋给@another_variable，我应该能够精确地做到这一点。

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;

在我的两台机器上（一台运行mysql 5.6，另一台运行mariadb 10.0.7），上面的查询按预期执行：

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)

但在MySQL5.5上，它没有：

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)

观察查询是如何增加的，我们可以看到底层问题。以下是一个结果：

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)

下面是拥有一个

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

最佳答案

这是因为在旧版本中，MySQL将0视为一个整数；您的初始赋值将变量设置为一个整数。将分配更改为@cumulative_sum:=0将导致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解决了这个问题，但我没有发现有人提到行为在5.5和5.6之间发生了变化：
为变量赋值并在同一个非set语句中读取值的另一个问题是，变量的默认结果类型基于语句开头的类型。以下示例说明了这一点：
@cumulative_sum:=0.0
mysql> SET @a='test';
对于这个select语句，mysql向客户机报告第一列是一个字符串，并将@a的所有访问转换为字符串，即使@a被设置为第二行的数字。执行select语句后，@a被视为下一个语句的数字。
为避免此行为出现问题，请不要在单个语句中为同一变量赋值和读取该变量的值，或者在使用该变量之前将该变量设置为0、0.0或“”以定义其类型。

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)