MySQL Bugs: #69647: DO syntax turns error signal into a warning

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

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.

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)

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.

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.

> 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.

Posted by developer:
 
Fixed in 5.7.22.

DO turned error signals into warnings.

Posted by developer:
 
Fixed in 5.7.22.

DO turned error signals into warnings.