From: Date: November 21 2005 9:08pm
Subject: Can't materialize a view -- bug?
List-Archive: http://lists.mysql.com/mysql/191980
Message-Id: <ebccd1440511211208v3a7810b9n2ba8b9f0c8158e37@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; 
	boundary="----=_Part_36501_24631266.1132603712915"

------=_Part_36501_24631266.1132603712915
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I'm doing the equivalent of a materialized view in MySQL 5.0.15 and I've hi=
t
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 >=3D ma + (2 * stdv)) OR cnt <=3D m=
a -
(2 * stdv),
IF (cnt >=3D 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 wonderin=
g
if these are related.

Nick

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

------=_Part_36501_24631266.1132603712915--