function - Смысл флага deterministic

#mysql #sql #функции

В чём разница между детерменированной и недетерминированной функцией?
Я надеялся, что deterministic заставит СУБД посчитать функцию чистой и закеширует
её результат?  


  The DETERMINISTIC clause for functions is ideal for functions that do
  not have any non-deterministic components. This means that each time
  you provide the function with the same parameter values, the result is
  the same. When you define a function you can simply add the
  DETERMINISTIC option to the declaration section, making sure that the
  function (or any functions or procedures it calls) does not depend on
  the state of session variables or schema objects as the results may
  vary across invocations. This option instructs the optimizer that it
  may use a cached result whenever it encounters a previously calculated
  result.
  Source


create function det() returns INTEGER DETERMINISTIC NO SQL return @param;

create function ndet() returns INTEGER NOT DETERMINISTIC NO SQL return @param;

select 
  @param:=5, det() d1, ndet() n1
, @param:=3, det() d2, ndet() n2
, @param:=1, det() d3, ndet() n3
, @param:=7, det() d4, ndet() n4;


Результат:  


  5, 5, 5, 3, 3, 3, 1, 1, 1, 7, 7, 7  


Т.е. я не вижу никакой разницы.  

Как понять, в чём разница, и в каких ситуациях она проявляется?
    

Ответы

Ответ 1

Если вы определяете DETERMINISTIC, это значит, что для одних и тех же параметров
функция всегда возвращает одно и то же значение. Соответственно, MySQL по-идее может
кешировать значение, чтобы не пересчитывать его для одних и тех же параметров.

Но поддержка DETERMINISTIC в MySQL реализована не полностью, функция будет выполняться
всегда.

Пример на MySQL 5.7.22:

create function rr() returns INTEGER DETERMINISTIC return RAND()*1000;
select rr(), rr();
+------+------+
| rr() | rr() |
+------+------+
|  300 |  451 |
+------+------+


Во WHERE выполняется 1 раз:

CREATE FUNCTION r1() returns INTEGER DETERMINISTIC return RAND()*3;

SELECT * FROM (
  SELECT 1 AS ID UNION ALL
  SELECT 2 AS ID UNION ALL
  SELECT 3 AS ID UNION ALL
  SELECT 4 AS ID UNION ALL
  SELECT 5 AS ID
) AS t1
WHERE r1() < ID;


Выведет, например, 1 | 2 | 3, то есть без "выпадов".


Ответ 2

По-моему в таком виде обе функции заведомо недетерминированы, т.к. зависят от внешней
переменной @param, которая не является параметром функции...

если оформить функцию так:

create function det(param INTEGER) returns INTEGER DETERMINISTIC NO SQL return param;


то ее (по-моему) можно считать детерменированной, т.к. она гарантированно вернет
одинаковый результат при вызове с одинаковыми параметрами...