Bug #69647 DO syntax turns error signal into a warning
Submitted: 2 Jul 2013 12:04 Modified: 12 Apr 2018 14:27
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.10 OS:Any
Assigned to: CPU Architecture:Any

[2 Jul 2013 12:04] Roland Bouman
Description:
The DO syntax can be used to execute expressions while discarding the value.

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

However it has a side effect of turning a raised error signal into a warning.

This is unexpected, and IMO, wrong. Errors should still be propagated as if the function is normally executed. 

How to repeat:
delimiter go

create function f_raise(p_message_text varchar(255)) 
returns int 
begin 
  signal sqlstate '45000' set message_text = p_message_text; 
  return null; 
end;
go

select f_raise('bla');
go

ERROR 1644 (45000): bla

So far everything is as expected. Now using the DO syntax:

do f_raise('bla');
go

Query OK, 0 rows affected, 1 warning (0.00 sec)

show warnings
go

+-------+------+---------+
| Level | Code | Message |
+-------+------+---------+
| Error | 1644 | bla     |
+-------+------+---------+
1 row in set (0.00 sec)

Expected was the exact same behaviour as with the SELECT statement.

Suggested fix:
Please don't let DO turn raised signals into warnings.
[2 Jul 2013 12:39] MySQL Verification Team
Hello Roland,

Thank you for the bug report and the test case. 
Verified as described on recent 5.5, 5.6 GA versions.

Thanks,
Umesh

How to repeat:

use test;

delimiter go

create function f_raise(p_message_text varchar(255)) 
returns int  DETERMINISTIC
begin 
  signal sqlstate '45000' set message_text = p_message_text; 
  return null; 
end;
go
delimiter ;

select f_raise('bla');
do f_raise('bla');

// 5.6.12

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.12    |
+-----------+
1 row in set (0.00 sec)
mysql> use test
Database changed
mysql> delimiter go
mysql>
mysql> create function f_raise(p_message_text varchar(255))
    -> returns int  DETERMINISTIC
    -> begin
    ->   signal sqlstate '45000' set message_text = p_message_text;
    ->   return null;
    -> end;
    -> go
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select f_raise('bla');
ERROR 1644 (45000): bla
mysql>
mysql> do f_raise('bla');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------+
| Level | Code | Message |
+-------+------+---------+
| Error | 1644 | bla     |
+-------+------+---------+
1 row in set (0.00 sec)

// 5.5.32

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.5.32-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> delimiter go
mysql> 
mysql> create function f_raise(p_message_text varchar(255)) 
    -> returns int  DETERMINISTIC
    -> begin 
    ->   signal sqlstate '45000' set message_text = p_message_text; 
    ->   return null; 
    -> end;
    -> go
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select f_raise('bla');
ERROR 1644 (45000): bla
mysql> 
mysql> 

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

mysql> show warnings;
+-------+------+---------+
| Level | Code | Message |
+-------+------+---------+
| Error | 1644 | bla     |
+-------+------+---------+
1 row in set (0.00 sec)
[2 Jul 2013 18:25] Davi Arnaut
The documentation states "DO executes the expressions but does not return any results" . That includes errors and whatnot. It has been this way since MySQL 4.1.
[2 Jul 2013 18:43] Roland Bouman
Davi, 

what does "whatnot" include? Apparently, not warnings. 

The documentation also reads:

"DO is useful primarily with functions that have side effects, such as RELEASE_LOCK()."

I would argue that DO is especially useful for this use case, not reporting errors is a pretty serious flaw in the design of this feature.

It also reads: 

"In most respects, DO is shorthand for SELECT expr, ..., "

This suggests that DO is to all intents and purposes "just like" SELECT. Turning an error into a warning is pretty far from "just like" IMO.
[3 Jul 2013 16:50] Davi Arnaut
> what does "whatnot" include? Apparently, not warnings.

The different kind of results that can be produced by a statement. 

> This suggests that DO is to all intents and purposes "just like" SELECT. Turning an error into a warning is pretty far from "just like" IMO.

I think that "most respects" indicates that there aspects in which it is not like SELECT. Also, if it was "just like" a SELECT, there wouldn't be a point in DO existing.

Additionally, it is not turning a warning into a error. DO will discard the contents of the diagnostics area but not the contents of the message list (where error, warning and note messages are stored). SHOW WARNINGS displays error, warning, and note messages.
[12 Apr 2018 14:26] Paul DuBois
Posted by developer:
 
Fixed in 5.7.22.

DO turned error signals into warnings.
[12 Apr 2018 14:27] Paul DuBois
Posted by developer:
 
Fixed in 5.7.22.

DO turned error signals into warnings.