MySQL Bugs: #64781: Incorrect output warning message

Bug #64781	Incorrect output warning message
Submitted:	28 Mar 2012 3:29	Modified:	28 Mar 2012 18:04
Reporter:	John Isaiah Carmona	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.38	OS:	Windows (Microsoft Windows XP Professional Service Pack 3 (build 2600))
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
Type: Warning
Action: SET @A := 1
Message: 0 row(s) affected, 1 warning(s):#1292 Truncated incorrect DOUBLE value 'h'
Duration / Fetch: 0.000 sec

How to repeat:
Type in SQL Editor : SET @A := 1; SELECT @A;
Execute. Clear.
Type in SQL Editor : SET @A := 1 + NULL; SELECT @A;
Execute. Clear.
Type in SQL Editor : SET @A := CONCAT('h',NULL); SELECT @A;
Execute. Clear.
Type in SQL Editor : SET @A := 1; SELECT @A;
Execute. Clear.

Please, send the output of:

select @@sql_mode, version();

from your server.

'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION', '5.1.37-community'

I forgot to add that to reproduce the bug you need to execute: 
SET @A := 'h' + NULL;

Sorry for the late analysis.

First of all, this has nothing to do with Workbench, as mysql command line client demonstrates the same behavior:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.58-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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> SET @A := 'h' + NULL;
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'h'
1 row in set (0.01 sec)

mysql> select @A;
+------+
| @A   |
+------+
| NULL |
+------+
1 row in set (0.02 sec)

And this is not a bug, as '+' operator assumes numbers as operands, so it tries to convert 'h' to number, finds non-numeric character 'h' and produces a warning. This is an expected behavior.

Yes, but after executing that line in workbench, it will throw the same warning message when setting the @A parameter with any value. Is that the case in MySQL too?

I tested it and it does'nt have the same case in MySQL

mysql> SET @A := 'h' + null; SET @A := 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

There is only one warning message. I'm re-opening this bug.

Now I've got your point. But still, actually this is server's behavior to NOT clear warnings in this case. Look:

mysql> SET @A := 'h' + null;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'h'
1 row in set (0.00 sec)

mysql> SET @A := 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'h'
1 row in set (0.00 sec)

Just mysql client looks "smart enough" to NOT ask for SHOW WARNINGS after every statement. I think we have a valid feature request for Workbench to be at least as smart as mysql command line client in this case.

Check bug #49634, bug #63407 and so on. If statement does NOT access any table, warnings are not cleared...

Duplicate of bug #62454