Bug #84972 truncation warning gets eaten by assignment

• Submitted: 14 Feb 2017 1:09
• Modified: 14 Feb 2017 7:02
• Reporter: Domas Mituzas
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6,5.7.17, 5.6.35
• OS: Any
• CPU Architecture: Any

[14 Feb 2017 1:09] Domas Mituzas

Description:
if truncation happens on assignment, there's no warning thrown, thus leading to weird execution anomalies

How to repeat:
mysql> select 1 from dual where (@a:="a");
Empty set (0.00 sec)

mysql> select 1 from dual where "a";
Empty set, 1 warning (0.00 sec)

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

mysql> 

Suggested fix:
throw a warning.

[14 Feb 2017 2:04] Trey Raymond

perhaps a simpler example of the bug:

mysql> select 4*(@a:="a");
+-------------+
| 4*(@a:="a") |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> select 4*"a";
+-------+
| 4*"a" |
+-------+
|     0 |
+-------+
1 row in set, 1 warning (0.00 sec)

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

[14 Feb 2017 7:02] MySQL Verification Team

Hello Domas,

Thank you for the report.

Thanks,
Umesh

[14 Feb 2017 8:37] Roy Lyseng

The actual problem here is that converting from a user variable into an expression does not cause type warnings, as is done when converting from a string literal.

mysql> set @a="a";
Query OK, 0 rows affected (0.00 sec)

mysql> select 1 from dual where @a;
Empty set (3.25 sec)

There is similar behavior when converting variable containing a string value to decimal:

mysql> select 1 from dual where 3.14 + @a > 0;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (2.43 sec)

and to floating point:

mysql> select 1 from dual where 3.14e0 + @a > 0;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (2.13 sec)