[MDEV-16305] DECLARE HANDLER is ignored Created: 2018-05-27  Updated: 2018-07-02  Resolved: 2018-07-02

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.3
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Federico Razzoli Assignee: Elena Stepanova
Resolution: Not a Bug Votes: 0
Labels: stored_aggregate_functions


 Description   

I found these anomaly while testing aggregate stored functions. I did not test if they occur in regular stored function/procedure.

CREATE AGGREGATE FUNCTION geometric_mean(p_number DECIMAL(65, 2) UNSIGNED)
 
    RETURNS DECIMAL(65, 2) UNSIGNED
 
BEGIN
 
    DECLARE v_out DECIMAL(65, 3) UNSIGNED DEFAULT NULL;
 
    DECLARE v_count BIGINT UNSIGNED DEFAULT 0;
 
 
 
    DECLARE CONTINUE HANDLER
 
        FOR 1265, 4094
 
    BEGIN END;
 
    
    DECLARE CONTINUE HANDLER
 
        FOR NOT FOUND
 
    BEGIN
 
        RETURN IF(v_count = 0, NULL, POWER(v_out, 1 / v_count));
 
    END;
 
    
    FETCH GROUP NEXT ROW;
 
    SET v_count := v_count + 1;
 
    SET v_out := p_number;
 
    
    LOOP
 
        FETCH GROUP NEXT ROW;
 
        SET v_count := v_count + 1;
 
        SET v_out := v_out * p_number;
 
    END LOOP;
 
END;

The first handler should catch the warnings, do nothing and continue; this is something I often do to suppress a useless warning, and it used to work.

But now it doesn't:

MariaDB [test]> DROP TABLE IF EXISTS t;
 
Query OK, 0 rows affected (0.009 sec)
 
 
 
MariaDB [test]> CREATE TABLE t (
 
    ->     n INT UNSIGNED
 
    -> ) ENGINE InnoDB;
 
Query OK, 0 rows affected (0.024 sec)
 
 
 
MariaDB [test]> -- put at least 2 values here to see the warning
 
MariaDB [test]> INSERT INTO t VALUES (2), (4);
 
Query OK, 2 rows affected (0.002 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SELECT geometric_mean(n) FROM t;
 
+-------------------+
 
| geometric_mean(n) |
 
+-------------------+
 
| 2.83              |
 
+-------------------+
 
1 row in set, 1 warning (0.000 sec)
 
 
 
Note (Code 1265): Data truncated for column 'geometric_mean(n)' at row 1

A funny thing happens if I have at least 4 values. I put this here because it could be strictly related to the above anomaly, but please let me know if I should open a new bug for this:

MariaDB [test]> DROP TABLE IF EXISTS t;
 
Query OK, 0 rows affected (0.021 sec)
 
 
 
MariaDB [test]> CREATE TABLE t (
 
    ->     n INT UNSIGNED
 
    -> ) ENGINE InnoDB;
 
Query OK, 0 rows affected (0.024 sec)
 
 
 
MariaDB [test]> -- put at least 4 values and the warning is different
 
MariaDB [test]> INSERT INTO t VALUES (2), (4), (8), (16);
 
Query OK, 4 rows affected (0.002 sec)
 
Records: 4  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SELECT geometric_mean(0.1) FROM t;
 
+---------------------+
 
| geometric_mean(0.1) |
 
+---------------------+
 
| 0.00                |
 
+---------------------+
 
1 row in set, 1 warning (0.000 sec)
 
 
 
Note (Code 4094): At line 20 in test.geometric_mean

Note that 4094 error is not listed in the relevant pages:

 Comments   
Comment by Elena Stepanova [ 2018-05-29 ]

For the non-working handler

The truncation error occurs when you return value, which is supposed to be DECIMAL(65, 2) UNSIGNED, but of course has a much longer fraction part; but you return the value from another handler, so by definition the handler for 1265 is out of scope. Here is a simpler example:

DELIMITER $
 
CREATE OR REPLACE FUNCTION f() RETURNS DECIMAL(65, 2)
 
BEGIN
 
  DECLARE CONTINUE HANDLER FOR 1265 BEGIN END;
 
  DECLARE CONTINUE HANDLER FOR 1054 RETURN 0.123;
 
  SELECT foo INTO @a;
 
END $
 
DELIMITER ;
 
 
 
SELECT f();
 
SHOW WARNINGS;


MariaDB [test]> SELECT f();
 
+------+
 
| f()  |
 
+------+
 
| 0.12 |
 
+------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> SHOW WARNINGS;
 
+-------+------+------------------------------------------+
 
| Level | Code | Message                                  |
 
+-------+------+------------------------------------------+
 
| Note  | 1265 | Data truncated for column 'f()' at row 1 |
 
+-------+------+------------------------------------------+
 
1 row in set (0.00 sec)

but if you return from the function body instead,

DELIMITER $
 
CREATE OR REPLACE FUNCTION f() RETURNS DECIMAL(65, 2)
 
BEGIN
 
  DECLARE CONTINUE HANDLER FOR 1265 BEGIN END;
 
  RETURN 0.123;
 
END $
 
DELIMITER ;
 
 
 
SELECT f();
 
SHOW WARNINGS;


MariaDB [test]> SELECT f();
 
+------+
 
| f()  |
 
+------+
 
| 0.12 |
 
+------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SHOW WARNINGS;
 
Empty set (0.00 sec)

– it works just fine.

For error 4094 (ER_SP_STACK_TRACE), I think it's a separate problem, please do create another bug report.

Comment by Federico Razzoli [ 2018-05-30 ]

If I understand correctly your comment, a HANDLER doesn't isn't triggered in the body of another HANDLER. Thanks for the explaination.

For 4094 I have filed MDEV-16326.

Comment by Elena Stepanova [ 2018-05-30 ]

Right, it's documented so, e.g. here:

A handler declared in a BEGIN ... END block is in scope only for the SQL statements following the handler declarations in the block. If the handler itself raises a condition, it cannot handle that condition, nor can any other handlers declared in the block.

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