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

Functions in packages are slow and seems to ignore deterministic

    XMLWordPrintable

Details

    Description

      A stored function created as DETERMINISTIC is optimized properly. A stored function as part of a SQL/PL package does seem to ignore the DETERMINISTIC aspect completely though. Consider:

      DELIMITER //
      SET SQL_MODE=Oracle//
      CREATE OR REPLACE FUNCTION bugf1()
      RETURN CHAR(1)
      DETERMINISTIC
      IS
      BEGIN
         RETURN 'X';
      END;
      //
      CREATE OR REPLACE FUNCTION bugf2()
      RETURN CHAR(1)
      IS
      BEGIN
         RETURN 'X';
      END;
      //
      CREATE OR REPLACE PACKAGE pkgbug
      IS
      PROCEDURE bugdata(numrows INTEGER);
      FUNCTION bugf3() RETURN CHAR(1) DETERMINISTIC;
      FUNCTION bugf4() RETURN CHAR(1);
      END;
      //
      CREATE OR REPLACE PACKAGE BODY pkgbug
      IS
      PROCEDURE bugdata(numrounds INTEGER)
      IS
      i INTEGER;
      BEGIN
         CREATE OR REPLACE TABLE bugtab(c1 CHAR(1));
         INSERT INTO bugtab VALUES('Y');
         FOR i IN 1..numrounds LOOP
            INSERT INTO bugtab SELECT * FROM bugtab;
         END LOOP;
      END;
       
      FUNCTION bugf3() RETURN CHAR(1) DETERMINISTIC
      IS
      BEGIN
         RETURN 'X';
      END;
       
      FUNCTION bugf4() RETURN CHAR(1)
      IS
      BEGIN
         RETURN 'X';
      END;
      END;
      //
       
      DELIMITER ;
      

      To test this, create some data:

      MariaDB> CALL pkgbug.bugdata(20);
      Query OK, 1048576 rows affected (14.557 sec)
      

      Now we test this in 5 different ways, using a literal, using plain functions, deterministic and non so and then with the 2 packages functions, again on deterministic and one not:

      MariaDB> SELECT 'Literal', COUNT(*) FROM bugtab WHERE c1 = 'X';
      +---------+----------+
      | Literal | COUNT(*) |
      +---------+----------+
      | Literal |        0 |
      +---------+----------+
      1 row in set (0.959 sec)
       
      MariaDB> SELECT 'Deterministic function', COUNT(*) FROM bugtab WHERE c1 = bugf1();
      +------------------------+----------+
      | Deterministic function | COUNT(*) |
      +------------------------+----------+
      | Deterministic function |        0 |
      +------------------------+----------+
      1 row in set (0.956 sec)
       
      MariaDB> SELECT 'Non-deterministic function', COUNT(*) FROM bugtab WHERE c1 = bugf2();
      +----------------------------+----------+
      | Non-deterministic function | COUNT(*) |
      +----------------------------+----------+
      | Non-deterministic function |        0 |
      +----------------------------+----------+
      1 row in set (9.640 sec)
       
      MariaDB> SELECT 'Deterministic package function', COUNT(*) FROM bugtab WHERE c1 = pkgbug.bugf3();
      +--------------------------------+----------+
      | Deterministic package function | COUNT(*) |
      +--------------------------------+----------+
      | Deterministic package function |        0 |
      +--------------------------------+----------+
      1 row in set (9.078 sec)
       
      MariaDB> SELECT 'Non-deterministic package function', COUNT(*) FROM bugtab WHERE c1 = pkgbug.bugf4();
      +------------------------------------+----------+
      | Non-deterministic package function | COUNT(*) |
      +------------------------------------+----------+
      | Non-deterministic package function |        0 |
      +------------------------------------+----------+
      1 row in set (9.946 sec)
      

      As can be seen, the deterministic function provides the expected benefit whereas the deterministic function in a package does not. An extended explain shows this clearly.

      MariaDB> EXPLAIN EXTENDED SELECT 'Literal', COUNT(*) FROM bugtab WHERE c1 = 'X';
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      | id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      |    1 | SIMPLE      | bugtab | ALL  | NULL          | NULL | NULL    | NULL | 1047144 |   100.00 | Using where |
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      1 row in set, 1 warning (0.001 sec)
       
      MariaDB> SHOW WARNINGS;
      +-------+------+------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                    |
      +-------+------+------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select 'Literal' AS "Literal",count(0) AS "COUNT(*)" from "test"."bugtab" where "test"."bugtab"."c1" = 'X' |
      +-------+------+------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB> EXPLAIN EXTENDED SELECT 'Deterministic function', COUNT(*) FROM bugtab WHERE c1 = bugf1();
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      | id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      |    1 | SIMPLE      | bugtab | ALL  | NULL          | NULL | NULL    | NULL | 1047144 |   100.00 | Using where |
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      1 row in set, 1 warning (0.000 sec)
       
      MariaDB> SHOW WARNINGS;
      +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                 |
      +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select 'Deterministic function' AS "Deterministic function",count(0) AS "COUNT(*)" from "test"."bugtab" where "test"."bugtab"."c1" = <cache>("bugf1"()) |
      +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB> EXPLAIN EXTENDED SELECT 'Non-deterministic function', COUNT(*) FROM bugtab WHERE c1 = bugf2();
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      | id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      |    1 | SIMPLE      | bugtab | ALL  | NULL          | NULL | NULL    | NULL | 1047144 |   100.00 | Using where |
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      1 row in set, 1 warning (0.000 sec)
       
      MariaDB> SHOW WARNINGS;
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                |
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select 'Non-deterministic function' AS "Non-deterministic function",count(0) AS "COUNT(*)" from "test"."bugtab" where "test"."bugtab"."c1" = "bugf2"() |
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB> EXPLAIN EXTENDED SELECT 'Deterministic package function', COUNT(*) FROM bugtab WHERE c1 = pkgbug.bugf3();
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      | id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      |    1 | SIMPLE      | bugtab | ALL  | NULL          | NULL | NULL    | NULL | 1047144 |   100.00 | Using where |
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      1 row in set, 1 warning (0.000 sec)
       
      MariaDB> SHOW WARNINGS;
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                        |
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select 'Deterministic package function' AS "Deterministic package function",count(0) AS "COUNT(*)" from "test"."bugtab" where "test"."bugtab"."c1" = "test"."pkgbug"."bugf3"() |
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB> EXPLAIN EXTENDED SELECT 'Non-deterministic package function', COUNT(*) FROM bugtab WHERE c1 = pkgbug.bugf4();
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      | id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      |    1 | SIMPLE      | bugtab | ALL  | NULL          | NULL | NULL    | NULL | 1047144 |   100.00 | Using where |
      +------+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
      1 row in set, 1 warning (0.001 sec)
       
      MariaDB> SHOW WARNINGS;
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                |
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select 'Non-deterministic package function' AS "Non-deterministic package function",count(0) AS "COUNT(*)" from "test"."bugtab" where "test"."bugtab"."c1" = "test"."pkgbug"."bugf4"() |
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      As can be seen in this, the first SELECT with the literal is easy. The second, with the deterministic function clearly shows that the result of this function is cached. This not seen for any other other calls and the function is called for every row, even for the deterministic stored function in a package.

      Attachments

        Activity

          People

            bar Alexander Barkov
            karlsson Anders Karlsson
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.