Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.3(EOL), 10.4(EOL)
-
None
Description
After creating a Stored Aggregate Function in one database, calling it while using another database always returns NULL. Regular Stored Functions do not work this way and work so long as you reference what database they are stored in. Below is a simple session that demonstrates the problem.
Server version: 10.3.13-MariaDB-log MariaDB Server
|
MariaDB [(none)]> CREATE DATABASE a; |
Query OK, 1 row affected (0.00 sec)
|
|
|
MariaDB [(none)]> CREATE DATABASE b; |
Query OK, 1 row affected (0.00 sec)
|
|
|
MariaDB [(none)]> USE a; |
Database changed |
MariaDB [a]> DELIMITER |
|
MariaDB [a]> CREATE AGGREGATE FUNCTION test_sum(next_value INT) RETURNS INT DETERMINISTIC |
-> BEGIN |
-> DECLARE ret INT DEFAULT 0; |
-> DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN ret; |
-> LOOP
|
-> FETCH GROUP NEXT ROW; |
-> SET ret = ret + next_value; |
-> END LOOP; |
-> END| |
Query OK, 0 rows affected (0.01 sec) |
|
|
MariaDB [a]> DELIMITER ;
|
MariaDB [a]> SELECT a.test_sum(t.c) FROM (SELECT 1 AS c UNION SELECT 2 UNION SELECT 3) AS t; |
+-----------------+ |
| a.test_sum(t.c) |
|
+-----------------+ |
| 6 |
|
+-----------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [a]> USE b; |
Database changed |
MariaDB [b]> SELECT a.test_sum(t.c) FROM (SELECT 1 AS c UNION SELECT 2 UNION SELECT 3) AS t; |
+-----------------+ |
| a.test_sum(t.c) |
|
+-----------------+ |
| NULL | |
+-----------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [b]> DELIMITER |
|
MariaDB [b]> CREATE AGGREGATE FUNCTION test_sum(next_value INT) RETURNS INT DETERMINISTIC |
-> BEGIN |
-> DECLARE ret INT DEFAULT 0; |
-> DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN ret; |
-> LOOP
|
-> FETCH GROUP NEXT ROW; |
-> SET ret = ret + next_value; |
-> END LOOP; |
-> END| |
Query OK, 0 rows affected (0.01 sec) |
|
|
MariaDB [b]> DELIMITER ;
|
MariaDB [b]> SELECT a.test_sum(t.c) FROM (SELECT 1 AS c UNION SELECT 2 UNION SELECT 3) AS t; |
+-----------------+ |
| a.test_sum(t.c) |
|
+-----------------+ |
| NULL | |
+-----------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [b]> SELECT b.test_sum(t.c) FROM (SELECT 1 AS c UNION SELECT 2 UNION SELECT 3) AS t; |
+-----------------+ |
| b.test_sum(t.c) |
|
+-----------------+ |
| 6 |
|
+-----------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [b]> SELECT test_sum(t.c) FROM (SELECT 1 AS c UNION SELECT 2 UNION SELECT 3) AS t; |
+---------------+ |
| test_sum(t.c) |
|
+---------------+ |
| 6 |
|
+---------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [b]> use a; |
Database changed |
MariaDB [a]> SELECT test_sum(t.c) FROM (SELECT 1 AS c UNION SELECT 2 UNION SELECT 3) AS t; |
+---------------+ |
| test_sum(t.c) |
|
+---------------+ |
| 6 |
|
+---------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [a]> use mysql |
Reading table information for completion of table and column names |
You can turn off this feature to get a quicker startup with -A |
|
|
Database changed |
MariaDB [mysql]> drop database a; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [mysql]> drop database b; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [mysql]> exit
|
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
Thanks for the report. Reproducible as described on 10.3-10.5.
I don't see any obvious reason why it should be happening or any mention of it in the documentation, so I'm passing it to varun to determine whether it's designed this way (in which case it needs to be documented) or not.