0

Deterministic functions are described with "always returns the same result set"

What does it mean? If function will always (no matter what) return a value of defined type? If yes, than should I make this function as deterministic?

FUNCTION AccToID(Acc VARCHAR)
  RETURNS INT
BEGIN
RETURN (SELECT `ID` FROM account WHERE accountName=Acc);
END

It can return null (an empty set?) or INT, so I guess it's non-deterministic function

I am confused with this, if I will mark this function as deterministic and it will return null value then it's undefined behaviour?

Maybe I can add a condition to reverse NULL into -1, but this is selfkilling idea

Bartłomiej Sobieszek
  • 2,692
  • 2
  • 25
  • 40
  • It means "given the same parameters, it always returns the same results". For instance, multiplication is deterministic, 2 * 2 is always 4. – Barmar Jan 25 '15 at 04:41
  • So in my case it's non-deterministic, because giving on example "John" it won't return always 15, it can also return null. Do I understand it correctly? :) Btw. I know I am stubborn, but using math calculations can result in NaN, even in sql, it actually can be handled anyway – Bartłomiej Sobieszek Jan 25 '15 at 04:44
  • Yes, you understand correctly. Most procedures that depend on tables that can change dynamically are not deterministic. – Barmar Jan 25 '15 at 04:45
  • 1
    The datatype is irrelevant. Changing `NULL` to `-1` still makes it non-deterministic, since it could return 15 or -1. – Barmar Jan 25 '15 at 04:46

0 Answers0