[MDEV-31940] Warning not raised inside Trigger Created: 2023-08-17  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Triggers
Affects Version/s: 10.4, 10.5, 10.6, 10.10, 10.11, 11.0, 11.1, 11.2, 11.0.3
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Major
Reporter: Georg Richter Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This issue was found and reported by ndiamond on stackoverflow.

According to the SQL Standard SQLSTATE codes beginning with 01 are treated as warnings instead of errors.

SIGNAL SQLSTATE '01000';
 
Query OK, 0 rows affected, 1 warning (0.000 sec)
 
 
 
SHOW WARNINGS;
 
+---------+------+------------------------------------------+
 
| Level   | Code | Message                                  |
 
+---------+------+------------------------------------------+
 
| Warning | 1642 | Unhandled user-defined warning condition |
 
+---------+------+------------------------------------------+

While the example above works as expected, the same SIGNAL inside a trigger has no effect:

DDL:

CREATE OR REPLACE TABLE t1 (a int);
 
CREATE OR REPLACE TRIGGER test_warning
 
BEFORE INSERT ON t1
 
FOR EACH ROW
 
BEGIN
 
    IF NEW.a < 0
 
    THEN
 
        SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Negative value inserted';
 
    END IF;
 
END

Test:

insert into t1 values (-1);
 
Query OK, 1 row affected (0.001 sec)
 
 
 
show warnings;
 
Empty set (0.000 sec)



 Comments   
Comment by Elena Stepanova [ 2023-09-18 ]

A somewhat simpler example:

set @a= 1+'x';
 
create table t (f int);
 
create trigger tr after insert on t for each row set @a= 1+'x';
 
insert into t values (1);
 
 
 
drop table t;

10.4 18990f00

 
set @a= 1+'x';
 
Warnings:
 
Warning	1292	Truncated incorrect DOUBLE value: 'x'
 
create table t (f int);
 
create trigger tr after insert on t for each row set @a= 1+'x';
 
insert into t values (1);
 
drop table t;

Generated at Thu Feb 08 10:27:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.