MySQL: If I use a function in an SQL query more than once, will it be recalculated every time?

Question

in MySQL, given the following query:

select column1
, column2
, my_function1(column3) as f1
, my_function2(column4) as f2
, my_function3 (my_function1(column3), my_function2(column4)) as f3
where some condition on column 1 
having f1 > some value

will my_function1 (column3) be called 3 times? Or is there an optimization / cache that reuses the computed value?

Thank you

Answer 1

Here is a short experiment to see what happens.

create function determin_rand (i integer) 
returns float DETERMINISTIC
return rand();

create function not_determin_rand (i integer) 
returns float 
return rand();

select determin_rand(1) as d1 , determin_rand(1) as d2, 
  not_determin_rand(1) as nd1, not_determin_rand(1) as nd2

0.00850549154   0.831901073456  0.133989050984  0.174242004752

Since the values ​​are different, the function is called every time. In the first function, I declared it deterministic, but it did not matter.

I made sqlfiddle for you to try it with different versions of mysql.

http://sqlfiddle.com/#!2/a8536/2

Answer 2

. , .

Answer 3

Your function will be called 3 times if it is not declared as deterministic, but it depends on your version of MySQL, and I'm not sure if the function will be called once if you want to get additional information, read here: http: // dev .mysql.com / doc / refman / 5.6 / en / create-procedure.html