[MDEV-9705] Field type conversion warnings have changed to worse Created: 2016-03-10  Updated: 2018-06-13

Status: Open
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.2
Fix Version/s: 10.2

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None
Environment:

351026ca535179abb956c5ad1e1d59aac0835254


 Description   

The following SQL fragment converts CHAR field to INT:

--source include/have_innodb.inc
 
 
 
DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a INT, b CHAR(8)) ENGINE=InnoDB;
 
INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c');
 
ALTER TABLE t1 MODIFY b INT DEFAULT 5;
 
 
 
DROP TABLE t1;

It used to produce warnings like this:

Warnings:
 
Warning 1366    Incorrect integer value: 'a' for column 'b' at row 1
 
Warning 1366    Incorrect integer value: 'b' for column 'b' at row 2
 
Warning 1366    Incorrect integer value: 'c' for column 'b' at row 3

Now on 10.2 it produces warnings like this:

Warnings:
 
Warning 1292    Truncated incorrect INTEGER value: 'a       '
 
Warning 1292    Truncated incorrect INTEGER value: 'b       '
 
Warning 1292    Truncated incorrect INTEGER value: 'c       '

It changed with this commit:

commit 454589b67f9609a78f00e521fe2ef0994eed4f3f
 
Author: Alexander Barkov <bar@mariadb.org>
 
Date:   Mon Jan 11 17:20:16 2016 +0400
 
 
 
    MDEV-9393 Split Copy_field::get_copy_func() into virtual methods in Field
 
    
    Also fixes:
 
    MDEV-9391 InnoDB does not produce warnings when doing WHERE int_column=varchar_column
 
    MDEV-9337 ALTER from DECIMAL and INT to DATETIME returns a wrong result
 
    MDEV-9340 Copying from INT/DOUBLE to ENUM is inconsistent
 
    MDEV-9392 Copying from DECIMAL to YEAR is not consistent about warnings

Old warnings look more meaningful, as they show the row number and column name, not just the value; I wonder if it's possible not to lose them.

Note: while reproducing, take into account MDEV-9704 – if it's not fixed yet, use MTR rather than MySQL client.

 Comments   
Comment by Alexander Barkov [ 2016-03-11 ]

The change was made to fix this problem:
MDEV-9391 InnoDB does not produce warnings when doing WHERE int_column=varchar_column

Comment by Elena Stepanova [ 2016-06-19 ]

Apparently, it wasn't the same problem, because the less-informative warnings are still there:

MariaDB [test]> SHOW WARNINGS;
 
+---------+------+-----------------------------------------------+
 
| Level   | Code | Message                                       |
 
+---------+------+-----------------------------------------------+
 
| Warning | 1292 | Truncated incorrect INTEGER value: 'a       ' |
 
| Warning | 1292 | Truncated incorrect INTEGER value: 'b       ' |
 
| Warning | 1292 | Truncated incorrect INTEGER value: 'c       ' |
 
+---------+------+-----------------------------------------------+
 
3 rows in set (0.00 sec)

I have a bunch of storage_engine tests failing because of it, so I need to know whether it is going to be fixed or not (I hope it will be, but if not, I will change the result files).

Comment by Alexander Barkov [ 2016-06-20 ]

A smaller script demonstrating the same problem:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a CHAR(8)) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES ('a'),('b'),('c');
 
ALTER TABLE t1 MODIFY a INT DEFAULT 5;

Comment by Alexander Barkov [ 2017-05-24 ]

If I change INT to DECIMAL(10,1)

SET sql_mode='';
 
DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a INT, b CHAR(8)) ENGINE=InnoDB;
 
INSERT INTO t1 (a,b) VALUES (1,'a');
 
INSERT INTO t1 (a,b) VALUES (2,'b');
 
INSERT INTO t1 (a,b) VALUES (3,'c');
 
ALTER TABLE t1 MODIFY b DECIMAL(10,1);
 
SHOW WARNINGS;

It returns these warnings in all current active versions (10.0, 10.1, 10.2, 10.3)

+---------+------+---------------------------------------------------------+
 
| Level   | Code | Message                                                 |
 
+---------+------+---------------------------------------------------------+
 
| Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
 
| Warning | 1292 | Truncated incorrect DECIMAL value: 'a       '           |
 
| Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
 
| Warning | 1292 | Truncated incorrect DECIMAL value: 'b       '           |
 
| Warning | 1918 | Encountered illegal value '' when converting to DECIMAL |
 
| Warning | 1292 | Truncated incorrect DECIMAL value: 'c       '           |
 
+---------+------+---------------------------------------------------------+

So apparently if we want more informative warnings, it should be implemented generally for all data type pairs. Fixing specificly CHAR-to-INT conversion back to its 10.0-style behavior does not look meaningful.

Generated at Thu Feb 08 07:36:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.