Bug #28299 To-number conversion warnings work differenly with CHAR and VARCHAR sp variables
Submitted: 8 May 2007 9:03 Modified: 12 Nov 2009 20:58
Reporter: Alexander Barkov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1, 5.0 OS:Any
Assigned to: Staale Smedseng CPU Architecture:Any

[8 May 2007 9:03] Alexander Barkov
Description:
String-to-number conversion produces warnings
only with CHAR variables. No warnings with VARCHAR.

How to repeat:
Put this code in a test case:

DELIMITER |;
CREATE PROCEDURE test()
BEGIN
  DECLARE i CHAR(16);
  DECLARE j INT;
  SET i="string";
  SET j= 1 + i;
END|
DELIMITER ;|
CALL test();
DROP PROCEDURE test;

DELIMITER |;
CREATE PROCEDURE test()
BEGIN
  DECLARE i VARCHAR(16);
  DECLARE j INT;
  SET i="string";
  SET j= 1 + i;
END|
DELIMITER ;|
CALL test();
DROP PROCEDURE test;

Then execute "mysql-test-run testname" and see its output:

CREATE PROCEDURE test()
BEGIN
DECLARE i CHAR(16);
DECLARE j INT;
SET i="string";
SET j= 1 + i;
END|
CALL test();
Warnings:
Warning       1292    Truncated incorrect DOUBLE value: 'string          '
DROP PROCEDURE test;
CREATE PROCEDURE test()
BEGIN
DECLARE i VARCHAR(16);
DECLARE j INT;
SET i="string";
SET j= 1 + i;
END|
CALL test();
DROP PROCEDURE test;

Only the first procedure generates a warning.
The difference in only in the variable "i" datatype.

Suggested fix:
Produce warnings for both CHAR and VARCHAR variables.
[8 May 2007 10:14] Sveta Smirnova
Thank you for the report.

Verified as described.
[15 Oct 2008 8:47] Konstantin Osipov
The issue is not specific to stored procedures:

drop table if exists t1;
create table t1 (i varchar(16), j int);
insert into t1 (i,j) values ("", 0);
update t1 set i="string", j=1+i;
show warnings;
alter table t1 modify column i char(16);
update t1 set i="string", j=1+i;
show warnings;

Output:
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (i varchar(16), j int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 (i,j) values ("", 0);
Query OK, 1 row affected (0.00 sec)

mysql> update t1 set i="string", j=1+i;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show warnings;
Empty set (0.00 sec)

mysql> alter table t1 modify column i char(16);
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update t1 set i="string", j=1+i;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'string          ' | 
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)

Note also that even though 1 warning is produced, the mysql command line client
says Warnings: 0. This is a separate bug.
[29 Oct 2008 18:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57347

2737 Staale Smedseng	2008-10-29
      This is a patch for Bug #28299 To-number conversion warnings
      work differenly with CHAR and VARCHAR sp variables.
                                    
      The patch provides ER_TRUNCATED_WRONG_VALUE warning messages
      for conversion of VARCHAR to numberic values, in line with
      messages provided for CHAR conversions. Conversions are
      checked for success, and the message is emitted in case of
      failure.
                                    
      The tests are amended to accept the added warning messages,
      and explicit conversion of ON/OFF values is added for
      statements checking system variables. In test
      rpl.rpl_switch_stm_row_mixed checking for warnings is
      temporarily disabled for one statement, as this generates
      warning messages for strings that vary between executions.
[13 Nov 2008 11:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/58619

2699 Staale Smedseng	2008-11-13
      This is a patch for Bug #28299 To-number conversion warnings
      work differenly with CHAR and VARCHAR sp variables.
                                          
      The patch provides ER_TRUNCATED_WRONG_VALUE warning messages
      for conversion of VARCHAR to numberic values, in line with
      messages provided for CHAR conversions. Conversions are
      checked for success, and the message is emitted in case
      failure.
                                          
      The tests are amended to accept the added warning messages,
      and explicit conversion of ON/OFF values is added for
      statements checking system variables. In test
      rpl.rpl_switch_stm_row_mixed checking for warnings is
      temporarily disabled for one statement, as this generates
      warning messages for strings that vary between executions.
[24 Nov 2008 9:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/59663

2752 Staale Smedseng	2008-11-24
      This is a patch for Bug #28299 To-number conversion warnings
      work differenly with CHAR and VARCHAR sp variables.
                                                
      The patch provides ER_TRUNCATED_WRONG_VALUE warning messages
      for conversion of VARCHAR to numberic values, in line with
      messages provided for CHAR conversions. Conversions are
      checked for success, and the message is emitted in case
      failure.
                                                
      The tests are amended to accept the added warning messages,
      and explicit conversion of ON/OFF values is added for
      statements checking system variables. In test
      rpl.rpl_switch_stm_row_mixed checking for warnings is
      temporarily disabled for one statement, as this generates
      warning messages for strings that vary between executions.
[25 Nov 2008 10:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/59766

2753 Staale Smedseng	2008-11-25
      Additional commit for Bug#28299, fixing some missing warnings
      in result files.
[26 Nov 2008 14:31] Staale Smedseng
Patch queued in 6.0-runtime.
[8 Dec 2008 21:38] Bugs System
Pushed into 6.0.9-alpha  (revid:staale.smedseng@sun.com-20081125104611-rtxic5d12e83ag2o) (version source revid:staale.smedseng@sun.com-20081125104611-rtxic5d12e83ag2o) (pib:5)
[8 Dec 2008 23:08] Paul Dubois
Noted in 6.0.9 changelog.

There were cases where string-to-number conversions would produce
warnings for CHAR values but not for VARCHAR values.
[9 Oct 2009 13:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86364
[9 Oct 2009 13:38] Staale Smedseng
Pushed into 5.5.0
[12 Oct 2009 15:38] Paul Dubois
Noted in 5.5.0 changelog.
[3 Nov 2009 7:16] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@sun.com-20091023064702-2f8jdmny61bdl94u) (merge vers: 6.0.14-alpha) (pib:13)
[3 Nov 2009 15:36] Paul Dubois
Already fixed in earlier 6.0.x release.
[12 Nov 2009 8:22] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)