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

Stored Aggregate Functions return NULL cross database.

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.3, 10.4
    • N/A
    • Stored routines
    • 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

              varun Varun Gupta (Inactive)
              CSTobey Christopher Tobey
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.