MySQL - одновременное приращение и присвоение поплавка в разрывах SELECT до 5.6
Почему запросы типа SELECT (@sum:=(@var:[email protected])+some_table.val)... автоматически присваивают (@var:[email protected]) целому числу в MySQL ≤5.5 для DECIMAL -type some_table.val и заполняют его для DOUBLE/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:[email protected]_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:[email protected]_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:[email protected]_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)
но в MySQL 5.5 это не так:
mysql> SELECT t.id, @cumulative_sum
FROM t
CROSS JOIN
( SELECT @cumulative_sum:=0) a
WHERE (@cumulative_sum:=(@another_variable:[email protected]_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)
Наблюдая за тем, как увеличивается запрос, мы можем увидеть основную проблему. Ниже приведены результаты с помощью FLOAT sum_component:
mysql> SELECT t.id, (@cumulative_sum := (@another_variable:[email protected]_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)
и вот что имеет DECIMAL sum_component выглядит следующим образом:
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:[email protected]_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
Ответы
Ответ 1
Это связано с тем, что MySQL в старой версии обрабатывает 0 как целое число; ваше начальное назначение @cumulative_sum:=0 устанавливает переменную в целое. Изменение назначения на @cumulative_sum:=0.0 приводит к желаемому поведению на 5.5:
SELECT t.id,
@cumulative_sum
FROM t
CROSS JOIN (SELECT @cumulative_sum:=0.0) a
WHERE (@cumulative_sum:=(@another_variable:[email protected]_sum)+t.sum_component) > 1.3
ORDER BY id ASC LIMIT 1;
В руководстве рассматривается эта проблема, но мне не удалось найти упоминание об изменении поведения между 5.5 и 5.6:
Другая проблема с назначением значения переменной и считыванием значения в одном и том же не-SET-заявлении заключается в том, что тип результата по умолчанию для переменной основан на ее типе в начале инструкции. Следующий пример иллюстрирует это:
mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;
Для этого оператора 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:[email protected]_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)
