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:
None 
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
Description:
Unable execute sql statement that inserts values into table A FROM selecting from another table B, despite the fact that the select on table B statement runs OK as a standalaone select, and produces correct and compatible (with table A) results

How to repeat:
See attached script
[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.