MySQL Bugs: #22587: Bug on view using function

Bug #22587	Bug on view using function
Submitted:	22 Sep 2006 13:12	Modified:	13 Nov 2006 14:30
Reporter:	[ name withheld ]	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.24a	OS:	MacOS (mac os x 10.3.9)
Assigned to:		CPU Architecture:	Any
Tags:	VIEW

Description:
since i use the 24a version (last one) my program doesn't work any more, the problem come from the view i use. The fact is now my views are not anymore recognized. the comment on mysql administrator views is "view references invalid table(s) or column(s) or function. But it was working fine before with 5.0.22 version. I have made some test and the only thing that made this view not working is the fact that i use a function.

How to repeat:

select @Symbol:='$'

view;
CREATE VIEW view_c_value AS SELECT year(V.datDate) AS Year, Emptybase.Currency(sum(V.datValue),TRUE) AS Total, Emptybase.Currency(T.targTarget,TRUE), Emptybase.Currency(sum(V.datValue)-T.targTarget,TRUE) AS FY, concat(round((((sum(V.datValue)/T.targTarget)-1)*100),2),'%') AS FYP, Emptybase.Currency(sum(V.datValue)-(SELECT sum(V1.datValue) FROM distributors D1, products P1, rel_product_distributor R1, datas V1, targets T1 WHERE D1.distID = R1.distID AND P1.prodID = R1.prodID AND R1.rpdID = T1.rpdID AND V1.targID = T1.targID AND year(V1.datDate)=Year-1 GROUP BY year(V1.datDate) DESC, D1.distID, P1.prodID limit 1),TRUE) AS FY2, concat(round(((sum(V.datValue)/(SELECT sum(V1.datValue) FROM distributors D1, products P1, rel_product_distributor R1, datas V1, targets T1 WHERE D1.distID = R1.distID AND P1.prodID = R1.prodID AND R1.rpdID = T1.rpdID AND V1.targID = T1.targID AND year(V1.datDate)=Year-1 GROUP BY year(V1.datDate) DESC, D1.distID, P1.prodID limit 1))-1)*100,2),'%') AS FYP2, P.prodID, D.distID FROM distributors D, products P, rel_product_distributor R, datas V, targets T WHERE D.distID = R.distID AND P.prodID = R.prodID AND R.rpdID = T.rpdID AND V.targID = T.targID GROUP BY year(V.datDate) DESC, D.distID, P.prodID

function Currency;
DELIMITER $$

DROP FUNCTION IF EXISTS `GiroBell`.`Currency`$$
CREATE FUNCTION `GiroBell`.`Currency` (v1 INT, cur1 BOOLEAN) RETURNS VARCHAR(255)
BEGIN
	IF cur1=TRUE THEN
		IF SIGN(FORMAT(v1,0))=-1 THEN
			RETURN INSERT(FORMAT(v1,0),2,0,@Symbol);
		ELSE
			RETURN CONCAT(@Symbol,FORMAT(v1,0));
		END IF;
	ELSE
		RETURN FORMAT(v1,0);
	END IF;
END$$

DELIMITER ;

We just updated a production server and ran across this same issue.  Here is a simple case demonstrating the bug.  Notice that the value for utc_timestamp() is being inserted as a constant if used with the between  syntax.

mysql> create table `table` (`column` text, start datetime, end datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW test as select `column` from `table` where utc_timestamp() between  start and end;
Query OK, 0 rows affected (0.00 sec)

mysql> show create view test;
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| View | Create View                                                                                                                                                                                                  |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE ALGORITHM=UNDEFINED DEFINER=`admin`@`localhost` SQL SECURITY DEFINER VIEW `test` AS select `table`.`column` AS `column` from `table` where (20061001010553 between `table`.`start` and `table`.`end`) |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW test2 as select `column` from `table` where utc_timestamp() > start and utc_timestamp() <  end;
Query OK, 0 rows affected (0.01 sec)

mysql> show create view test2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| View  | Create View                                                                                                                                                                                                                    |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE ALGORITHM=UNDEFINED DEFINER=`admin`@`localhost` SQL SECURITY DEFINER VIEW `test2` AS select `table`.`column` AS `column` from `table` where ((utc_timestamp() > `table`.`start`) and (utc_timestamp() < `table`.`end`)) |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.26, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".