MySQL Bugs: #36457: ER_TRUNCATED_WRONG_VALUE warning raised as an error

Bug #36457	ER_TRUNCATED_WRONG_VALUE warning raised as an error
Submitted:	1 May 2008 19:52	Modified:	29 May 2008 3:46
Reporter:	Marc ALFF	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.0 and up	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
The condition ER_TRUNCATED_WRONG_VALUE is raised inconsistently.

Most of the time, it's a warning, but in some cases, it's raised as a
"non blocking error": it's a warning, since it does not stop execution,
but shows as an "error" in show warnings.

How to repeat:
grep "Truncated incorrect" mysql-test/r/*.result

Most of the time, it's a warning, as in:
Warning        1292    Truncated incorrect DOUBLE value: 'a'
which is correct

Some times it's an error, as in:
Error 1292    Truncated incorrect DECIMAL value: ''
which is not correct.

Suggested fix:
call push_warning_printf(MYSQL_ERROR::WARN_LEVEL_WARN),
instead of push_warning_printf(MYSQL_ERROR::WARN_LEVEL_ERROR).

Thank you for the report.

I noticed behavior changes depending from SQL Mode:

mysql> \W
Show warnings enabled.
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(f1 decimal);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values('kjh'+10);
Query OK, 1 row affected, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: 'kjh'
mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values('kjh'+10);
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'kjh'

Do you think this is not correct or have you seen "ERROR" in case of not strict SQL Mode?

When SQL_MODE is strict_all_tables, warnings are changed to errors,
this is expected.

The bug is that when SQL_MODE is '', the condition ER_TRUNCATED_WRONG_VALUE
is raised in different places in the code, and is not consistent:

-- test

SET SQL_MODE='';

select @@sql_mode;
select locate('lo','hello',-18446744073709551615);

CREATE TABLE t1 (a CHAR(2));
INSERT INTO t1 VALUES (10), (50), (30), ('1a'), (60), ('t');
SELECT a,(a + 0) FROM t1 ORDER BY a;

-- result

SET SQL_MODE='';
select @@sql_mode;
@@sql_mode

select locate('lo','hello',-18446744073709551615);
locate('lo','hello',-18446744073709551615)
0
Warnings:
Error   1292    Truncated incorrect DECIMAL value: ''
--> "Error" 1292, incorrect

CREATE TABLE t1 (a CHAR(2));
INSERT INTO t1 VALUES (10), (50), (30), ('1a'), (60), ('t');
SELECT a,(a + 0) FROM t1 ORDER BY a;
a       (a + 0)
10      10
1a      1
30      30
50      50
60      60
t       0
Warnings:
Warning 1292    Truncated incorrect DOUBLE value: '1a'
Warning 1292    Truncated incorrect DOUBLE value: 't '
--> Warning 1292, ok

Thank you for the feedback.

Verified as described.

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/46672

ChangeSet@1.2644, 2008-05-13 17:42:43-06:00, malff@lambda.hsd1.co.comcast.net. +4 -0
  Bug#36457 (ER_TRUNCATED_WRONG_VALUE warning raised as an error)
  
  Fixed my_decimal to call push_warning(ER_TRUNCATED_WRONG_VALUE) with a level
  of WARN_LEVEL_WARN.
  
  Adjusted test results

Pushed into 6.0.6-alpha

Noted in 6.0.6 changelog.

The ER_TRUNCATED_WRONG_VALUE warning condition was sometimes raised
as an error.