Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21763

Stored Aggregate Functions return NULL cross database.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.3, 10.4
    • Fix Version/s: N/A
    • Component/s: Stored routines
    • Labels:
      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

            Activity

              People

              Assignee:
              varun Varun Gupta
              Reporter:
              CSTobey Christopher Tobey
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: