Details
Critical 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.