MySQL Bugs: #28299: To-number conversion warnings work differenly with CHAR and VARCHAR sp variables

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

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.

Thank you for the report.

Verified as described.

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.

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.

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.

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.

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.

Patch queued in 6.0-runtime.

Pushed into 6.0.9-alpha  (revid:staale.smedseng@sun.com-20081125104611-rtxic5d12e83ag2o) (version source revid:staale.smedseng@sun.com-20081125104611-rtxic5d12e83ag2o) (pib:5)

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.

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

Pushed into 5.5.0

Noted in 5.5.0 changelog.

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)

Already fixed in earlier 6.0.x release.

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)