Усеченное неверное значение DECIMAL в MySQL 5.7

Question

В MySQL 5.6.13 с режимом SQL " STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ", следующий запрос работает нормально, но с предупреждениями:

UPDATE Company SET company_CurrentYearValueGBP = COALESCE((
SELECT SUM(
(COALESCE(salesdata_Month01Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month02Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month03Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month04Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month05Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month06Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month07Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month08Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month09Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month10Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month11Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
(COALESCE(salesdata_Month12Amount,0) / IF(salesdata_ExchangeRate<>'',salesdata_ExchangeRate,1) ) + 
0) FROM SalesData 
WHERE salesdata_SalesDataTypeID = 3 
AND salesdata_CompanyID = company_ID 
AND salesdata_SalesYearID = 6 
AND NOT salesdata_IsBeingProcessed 
),0) 

Выход:

0 row(s) affected, 5 warning(s):
 1265 Data truncated for column 'company_CurrentYearValueGBP' at row 127
 1265 Data truncated for column 'company_CurrentYearValueGBP' at row 127
 1265 Data truncated for column 'company_CurrentYearValueGBP' at row 127
 1265 Data truncated for column 'company_CurrentYearValueGBP' at row 127
 1265 Data truncated for column 'company_CurrentYearValueGBP' at row 127
 Rows matched: 1470  Changed: 0  Warnings: 5

В MySQL 5.7.14 с режимом SQL " STRICT_TRANS_TABLES ", тот же запрос создает следующую ошибку:

Error Code: 1292. Truncated incorrect DECIMAL value: ''

Я понимаю, почему происходит ошибка (из-за сравнения salesdata_ExchangeRate<>''), но может ли кто-нибудь объяснить, почему в MySQL 5.6, несмотря на то, что строгий режим включен, что запрос только выдает предупреждения и не прерывается с ошибкой? Поведение кажется разным между двумя версиями.

Обновление 1 Создана простая таблица со следующей структурой для MySQL 5.6.13 и MySQL 5.7.14:

Добавлены следующие записи:

Выполнение следующего запроса воспроизводит ту же ошибку в MySQL 5.7.14:

UPDATE testtable 
SET testtable_CalculatedValue = IF(testtable_DecimalValue<>'',testtable_DecimalValue,0) 
WHERE testtable_ID > 0

Error Code: 1292. Truncated incorrect DECIMAL value: ''

В MySQL 5.6.13 для testtable_CalculatedValue установлено значение 1.35, как и ожидалось.

Answer 1

Настроить:

mysql> CREATE TABLE so53868586 (
         id INT AUTO_INCREMENT,
         deci  DECIMAL(12,2) DEFAULT NULL,
         calc DECIMAL(12,2) DEFAULT NULL,
         PRIMARY KEY(id)
     ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> SET @@sql_mode = "STRICT_TRANS_TABLES";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO so53868586 (deci)
         VALUES (1.35), (NULL), (0), (12345);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Тестовое задание

mysql> SELECT * FROM so53868586;
+----+----------+------+
| id | deci     | calc |
+----+----------+------+
|  1 |     1.35 | NULL |
|  2 |     NULL | NULL |
|  3 |     0.00 | NULL |
|  4 | 12345.00 | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

mysql> 
mysql> UPDATE so53868586 SET calc = IF(deci <> 0, deci, 0)
         WHERE id > 0;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> 
mysql> SELECT * FROM so53868586;
+----+----------+----------+
| id | deci     | calc     |
+----+----------+----------+
|  1 |     1.35 |     1.35 |
|  2 |     NULL |     0.00 |
|  3 |     0.00 |     0.00 |
|  4 | 12345.00 | 12345.00 |
+----+----------+----------+
4 rows in set (0.01 sec)

(Это было с использованием 5.6.22.)

5.7.15 получил те же результаты, но пожаловался на SET @sql_mode с

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3135 | 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. |
| Warning | 3090 | Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.                                                                          |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Я не смог воспроизвести ваш контрольный пример?

Я не вижу ничего релевантного в Журнале изменений для 5.7.15 (или где-либо еще), но вы можете рассмотреть возможность обновления. (5.7.14 выпущено более 2 лет назад.)