MySQL Bugs: #69985: Manual does not explain what DO statement really does in one specific case

Bug #69985	Manual does not explain what DO statement really does in one specific case
Submitted:	11 Aug 2013 17:40	Modified:	14 Mar 2018 21:37
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	DO

Description:
http://dev.mysql.com/doc/refman/5.6/en/do.html says:

"In most respects, DO is shorthand for SELECT expr, ..., but has the advantage that it is slightly faster when you do not care about the result. "

There is a case when DO expr behavior is different from SELECT expr:

mysql> do '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> select '';
+--+
|  |
+--+
|  |
+--+
1 row in set (0.00 sec)

Note warning in case of DO vs no warning in case of SELECT and see "How to Repeat" below for more details. It would be nice to have explanation what it really does and why.

How to repeat:
C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3314 test
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.13-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> do '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> select '';
+--+
|  |
+--+
|  |
+--+
1 row in set (0.00 sec)

It seems DO '' is processed like this:

mysql> select cast('' as UNSIGNED);
+----------------------+
| cast('' as UNSIGNED) |
+----------------------+
|                    0 |
+----------------------+
1 row in set, 1 warning (0.01 sec)

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

mysql> DO '1';
Query OK, 0 rows affected (0.01 sec)

mysql> DO '1a';
Query OK, 0 rows affected, 1 warning (0.01 sec)

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

At the same time, if expression is a function that returns string (not literal string), then again there is no warning and attempts to convert it to number:

mysql> DO USER();
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
It would be nice to read details explanation of how DO really works with all kinds of expressions.

mysql> do ' ';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> do '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> do null;
Query OK, 0 rows affected (0.00 sec)

mysql> do 0;
Query OK, 0 rows affected (0.00 sec)

mysql> do '' + 1;
Query OK, 0 rows affected (0.00 sec)

mysql> do ' '+1;
Query OK, 0 rows affected (0.00 sec)

I notice that the page says: "you do not care about the result"

So, regarding...

"
It would be nice to read details explanation of how DO really works with all
kinds of expressions.
"

...the point of DO is to evaluate the expression, not return a
meaningful result. I'm not clear why this page should state any
more than it does.

This page should explain why/in what cases DO produces warning when "equal" SELECT does NOT produce it. This is important for writing code that uses DO.

I remain unpersuaded that the manual should explain anything about this discrepancy in behavior (difference between DO and SELECT as to whether a warning is generated). I am reclassifiying it as a server bug so that it can be triaged and analyzed.

For triage/analysis:
* If this is determined to be a bug, I'll assume that it will be fixed.
* If this is determined to be expected behavior, please provide an explanation for the difference that I can use in the docs to satisfy Valeriy's original request. Thanks.

Posted by developer:
 
Not reproducible as of 5.7.22 and later.