Hello.
In my opinion it is a bug. You may add your comments at: http://bugs.mysql.com/bug.php?id=15137 >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've noticed that you may create a temporary tables from views like your, so a workaround is to create a temporary table first, and then a persistent table. >I hit a problem using functions in stored procedures, too, and I'm wondering >if these are related. Please provide more information or a test case. Check that you're using the latest MySQL version (5.0.16 now). Functions work in stored procedures: drop procedure if exists psignal; drop function if exists get_signal; delimiter $$ 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); $$ CREATE procedure psignal() DETERMINISTIC BEGIN SELECT get_signal(0,1,1); END; $$ delimiter ; call psignal(); [EMAIL PROTECTED] mysql-debug-5.0.16-linux-i686-glibc23]$ lmysql <p.sql get_signal(0,1,1) 0 Nick Arnett <[EMAIL PROTECTED]> wrote: >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 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
