[#MDEV-29370] Functions in packages are slow and seems to ignore deterministic

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 ;

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.

[MDEV-29370] Functions in packages are slow and seems to ignore deterministic Created: 2022-08-24 Updated: 2022-11-15 Resolved: 2022-11-15
Status:	Closed
Project:	MariaDB Server
Component/s:	PL/SQL, Stored routines
Affects Version/s:	10.6.8, 10.3, 10.4, 10.5, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s:	10.10.2, 10.11.1, 11.0.0, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5

[MDEV-29370] Functions in packages are slow and seems to ignore deterministic Created: 2022-08-24 Updated: 2022-11-15 Resolved: 2022-11-15