Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
10.3(EOL)
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: