| Bug #75141 | Warnings are not reset on SELECT not touching any tables | ||
|---|---|---|---|
| Submitted: | 8 Dec 2014 14:35 | Modified: | 9 Dec 2014 7:53 |
| Reporter: | Hartmut Holzgraefe | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Errors | Severity: | S3 (Non-critical) |
| Version: | mysql-5.6.20 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Dec 2014 15:21]
MySQL Verification Team
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.23 Source distribution 2014.12.02
Copyright (c) 2000, 2014, 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 5.6 > select cast('foo' as unsigned);
+-------------------------+
| cast('foo' as unsigned) |
+-------------------------+
| 0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)
mysql 5.6 > show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'foo' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql 5.6 > select cast('bar' as unsigned);
+-------------------------+
| cast('bar' as unsigned) |
+-------------------------+
| 0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)
mysql 5.6 > show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'bar' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql 5.6 > select cast(42.0 as unsigned);
+------------------------+
| cast(42.0 as unsigned) |
+------------------------+
| 42 |
+------------------------+
1 row in set (0.00 sec)
mysql 5.6 > show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'bar' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql 5.6 > select cast(42.0 as unsigned) from mysql.user limit 1;
+------------------------+
| cast(42.0 as unsigned) |
+------------------------+
| 42 |
+------------------------+
1 row in set (0.00 sec)
mysql 5.6 > show warnings;
Empty set (0.00 sec)
mysql 5.6 >
[8 Dec 2014 15:27]
MySQL Verification Team
Thank you for the bug report.
C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 --prompt="mysql 5.7 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.6-m16 Source distribution 2014.12.02
Copyright (c) 2000, 2014, 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 5.7 > select cast('foo' as unsigned);
+-------------------------+
| cast('foo' as unsigned) |
+-------------------------+
| 0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)
mysql 5.7 > show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'foo' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql 5.7 > select cast('bar' as unsigned);
+-------------------------+
| cast('bar' as unsigned) |
+-------------------------+
| 0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)
mysql 5.7 > show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'bar' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql 5.7 > select cast(42.0 as unsigned);
+------------------------+
| cast(42.0 as unsigned) |
+------------------------+
| 42 |
+------------------------+
1 row in set (0.00 sec)
mysql 5.7 > show warnings;
Empty set (0.00 sec)
mysql 5.7 > select cast(42.0 as unsigned) from mysql.user limit 1;
+------------------------+
| cast(42.0 as unsigned) |
+------------------------+
| 42 |
+------------------------+
1 row in set (0.00 sec)
mysql 5.7 > show warnings;
Empty set (0.00 sec)
[9 Dec 2014 1:30]
Davi Arnaut
This is clearly documented in the manual: "It shows nothing if the most recent statement used a table and generated no messages. (That is, statements that use a table but generate no messages clear the message list.) Statements that do not use tables and do not generate messages have no effect on the message list." This behavior was also recently changed in 5.7.
[9 Dec 2014 7:53]
Jon Olav Hauglid
Posted by developer: Basically what Davi said: 5.6 behavior is as documented. 5.7 behavior is according to the standard. It was changed in WL#5928 "Most statements should clear the diagnostic area"
[9 Dec 2014 7:54]
Jon Olav Hauglid
http://dev.mysql.com/worklog/task/?id=5928
Description: Warnings are not reset on successful execution of a SELECT statement without FROM clause or when using the dummy "FROM DUAL". If the statement raises a new warning though the old warnings are replaced. How to repeat: MySQL [test]> select cast('foo' as unsigned); +-------------------------+ | cast('foo' as unsigned) | +-------------------------+ | 0 | +-------------------------+ 1 row in set, 1 warning (0.00 sec) MySQL [test]> show warnings; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: 'foo' | +---------+------+------------------------------------------+ 1 row in set (0.00 sec) MySQL [test]> select cast('bar' as unsigned); +-------------------------+ | cast('bar' as unsigned) | +-------------------------+ | 0 | +-------------------------+ 1 row in set, 1 warning (0.00 sec) MySQL [test]> show warnings; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: 'bar' | +---------+------+------------------------------------------+ 1 row in set (0.00 sec) MySQL [test]> select cast(42.0 as unsigned); +------------------------+ | cast(42.0 as unsigned) | +------------------------+ | 42 | +------------------------+ 1 row in set (0.00 sec) MySQL [test]> show warnings; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: 'bar' | +---------+------+------------------------------------------+ 1 row in set (0.00 sec) MySQL [test]> select cast(42.0 as unsigned) from dual; +------------------------+ | cast(42.0 as unsigned) | +------------------------+ | 42 | +------------------------+ 1 row in set (0.00 sec) MySQL [test]> show warnings; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: 'bar' | +---------+------+------------------------------------------+ 1 row in set (0.00 sec) MySQL [test]> select cast(42.0 as unsigned) from mysql.user limit 1; +------------------------+ | cast(42.0 as unsigned) | +------------------------+ | 42 | +------------------------+ 1 row in set (0.00 sec) MySQL [test]> show warnings; Empty set (0.00 sec) Suggested fix: Always reset warnings after on successful execution of a SELECT