Bug #72800 INSERT INTO ... FROM SELECT statement fails with strange error
| Submitted: | 29 May 2014 16:32 | Modified: | 31 May 2014 13:19 |
|---|---|---|---|
| Reporter: | Ioannis Priggouris | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S1 (Critical) |
| Version: | 5.6.16 | OS: | Windows (windows 7) |
| Assigned to: | CPU Architecture: | Any |
[29 May 2014 16:32]
Ioannis Priggouris
[29 May 2014 16:38]
Ioannis Priggouris
The error received is the following: Error Code: 1292. Truncated incorrect DECIMAL value: '23A'
[29 May 2014 17:32]
MySQL Verification Team
Thank you for the bug report. Please check the warning from query you think is OK: mysql> -- The following runs OK mysql> SELECT ADRS_ID, CONVERT(STREET_FROM, DECIMAL) AS STREET_FROM, CONVERT(STREET_TO, DECIMAL) AS STREET_TO FROM test.adrs; +---------+-------------+-----------+ | ADRS_ID | STREET_FROM | STREET_TO | +---------+-------------+-----------+ | 1 | 23 | 25 | | 2 | 23 | 25 | | 3 | 23 | 25 | | 4 | 23 | 25 | +---------+-------------+-----------+ 4 rows in set, 4 warnings (0.00 sec) mysql> show warnings; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1292 | Truncated incorrect DECIMAL value: '23A' | | Warning | 1292 | Truncated incorrect DECIMAL value: '23B' | | Warning | 1292 | Truncated incorrect DECIMAL value: '23C' | | Warning | 1292 | Truncated incorrect DECIMAL value: '25A' | +---------+------+------------------------------------------+ 4 rows in set (0.00 sec)
[30 May 2014 9:02]
Ioannis Priggouris
I know that these warnings are thrown, but the query I do is intentional in order to retrieve the decinal part of the string, if any, because I want later on to perfrom some calculation on them. However, a warning would be what I would expect from my second query (the one with the insert), rather than a failing error!
[30 May 2014 14:59]
MySQL Verification Team
Just change your sql_mode to avoid convert warnings on errors:
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.19 Source distribution
Copyright (c) 2000, 2014, 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 5.6 > USE test
Database changed
mysql 5.6 > CREATE TABLE `test`.`adrs` (
-> `adrs_id` INT NOT NULL AUTO_INCREMENT,
-> `STREET_NAME` VARCHAR(45) NULL,
-> `STREET_FROM` VARCHAR(10) NULL,
-> `STREET_TO` VARCHAR(10) NULL,
-> PRIMARY KEY (`adrs_id`))
-> ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.22 sec)
mysql 5.6 > CREATE TABLE `test`.`check_results` (
-> `CHECK_RESULTS_ID` bigint(20) NOT NULL AUTO_INCREMENT,
-> `CHECK_GROUP_EXECUTION_ID` int(11) NOT NULL,
-> `CHECK_SPEC_ID` int(11) NOT NULL,
-> `CHECK_RESULT` varchar(1000) DEFAULT NULL,
-> PRIMARY KEY (`CHECK_RESULTS_ID`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=583095 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.20 sec)
mysql 5.6 > INSERT INTO test.adrs VALUES(null, 'KIFISIAS', '23', '25');
Query OK, 1 row affected (0.05 sec)
mysql 5.6 > INSERT INTO test.adrs VALUES(null, 'KIFISIAS', '23A', '25');
Query OK, 1 row affected (0.05 sec)
mysql 5.6 > INSERT INTO test.adrs VALUES(null, 'KIFISIAS', '23B', '25');
Query OK, 1 row affected (0.02 sec)
mysql 5.6 > INSERT INTO test.adrs VALUES(null, 'KIFISIAS', '23C', '25A');
Query OK, 1 row affected (0.03 sec)
mysql 5.6 > INSERT INTO `test`.`check_results`
-> (`CHECK_RESULTS_ID`,
-> `CHECK_GROUP_EXECUTION_ID`,
-> `CHECK_SPEC_ID`,
-> `CHECK_RESULT`) SELECT null, 1, 1, ADRS_ID FROM (
-> SELECT ADRS_ID, CONVERT(STREET_FROM, DECIMAL) AS STREET_FROM, CONVERT(STREET_TO, DECIMAL) AS STREET_TO FROM test.adrs
-> ) A;
Query OK, 4 rows affected, 4 warnings (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 4
[31 May 2014 13:19]
Ioannis Priggouris
Thanks for the answer.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.