Ridiculous MySQL Behavior

I’ve been messing with this for a few hours until I finally found this nugget on MySQL:

---

A view definition is “frozen” by certain statements:

If a statement prepared by PREPARE refers to a view, the view definition seen each time the statement is executed later will be the definition of the view at the time it was prepared. This is true even if the view definition is changed after the statement is prepared and before it is executed. Example:

CREATE VIEW v AS SELECT RAND();
PREPARE s FROM ‘SELECT * FROM v’;
ALTER VIEW v AS SELECT NOW();
EXECUTE s;
The result returned by the EXECUTE statement is a random number, not the current date and time.

---

Why on earth would anyone do that? Allow me to elaborate…

I have a MySQL function: Evaluate_Gx which is called in a loop from a procedure Initialize_Gx. Initialize_Gx loops over a lot of tables G1, G2, G3, … Gn for some Integer “n”. For each of those tables I want to call the Evaluate function and populate a table that contains the TableName (G1, G2, …) and the Result (a decimal) from the Evaluate_Gx function.

Now, MySQL functions can’t handle PREPARE statements, and the syntax of the evaluation is complicated — it’s an RMSE calculations based on about 18 subqueries with joins to other tables from the main Gx table. If I could use PREPARE statements, I could build the query based on a parameter to Evaluate_Gx(n), but I cannot.

So before I call Evaluate_Gx, I drop and recreate a view Gx_View that is simply PREPARED in Initialize_Gx as concat(‘SELECT * FROM G’, n) so that the function can always point to Gx_View and not run afoul of the PREPARE restriction. Vis:


WHILE MyCounter < = n DO DROP VIEW IF EXISTS Gx_View; SET @SQLStatement := CONCAT('CREATE VIEW Gx_View AS SELECT * FROM G', MyCounter); PREPARE MyStatement FROM @SQLStatement; EXECUTE MyStatement; DEALLOCATE PREPARE MyStatement; select Evaluate_Gx_DIH() into ThisResult; insert into Gx_seeds values (MyCounter, ThisResult); set MyCounter = MyCounter + 1; END WHILE;

But this DOESN'T WORK. The values in Gx_seeds are all the same:

MySeed, MyResult
1, 0.0038590125
2, 0.0038590125
3, 0.0038590125
4, 0.0038590125
5, 0.0038590125

Why why why? I think because of this weird "Frozen" thing that the MySQL documentation indicates as a View restriction, but I'm not sure. Any ideas?