Can't materialize a view -- bug?

[Nick Arnett]

I'm doing the equivalent of a materialized view in MySQL 5.0.15 and I've hit
a strange error.

Here's the scenario.

CREATE OR REPLACE VIEW x_view AS
SELECT [select statement, blah, blah, blah];

DROP TABLE IF EXISTS x_mview;
CREATE TABLE x_mview SELECT * FROM x_view;

That was all working fine until I created a function that I use inside of
the view. Now the last statement, the one that would materialize the view,
returns a table locking error!

ERROR 1100 (HY000): Table 'x_mview' was not locked with LOCK TABLES

It's difficult to lock a table that doesn't exist yet...

"SELECT * FROM x_view" works fine.

The function is a simple bit of logic (it tests a count, moving average and
standard deviation to see if the count is more or less than two standard
deviations from the mean):

CREATE FUNCTION get_signal (cnt MEDIUMINT(8), ma MEDIUMINT(8), stdv
MEDIUMINT(8)) RETURNS TINYINT(1)
DETERMINISTIC
RETURN IF (ma > 9 AND stdv > 0 AND (cnt >= ma + (2 * stdv)) OR cnt <= ma -
(2 * stdv),
IF (cnt >= ma + (2 * stdv), 1, -1),
0);

If this is a bug, I'll be happy to file a report... but I'd really like a
solution that will let me use the function.

I hit a problem using functions in stored procedures, too, and I'm wondering
if these are related.

Nick

--
Nick Arnett
narnett@stripped
Messages: 408-904-7198