Bug #18433 Lift limitation on using variables in view definition
Submitted: 22 Mar 2006 19:16 Modified: 8 Aug 2007 12:52
Reporter: Dmitry L Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Views Severity:S4 (Feature request)
Version:5.0.25-BK, 5.1.x OS:Any (irrevelant)
Assigned to: CPU Architecture:Any

[22 Mar 2006 19:16] Dmitry L
Description:
Subj;

Just for now i can use smth like following and it works great

How to repeat:
DELIMITER $$
CREATE FUNCTION getVar() RETURNS smallint(6) DETERMINISTIC
  return @SOME_VAR $$
DELIMITER;
--------------------------------
CREATE VIEW AS
SELECT *
  FROM some_table
 WHERE some_column = getVar();
[31 Aug 2006 13:47] Valeriy Kravchuk
Thank you for a feature request. I think, that current behaviour is just a bug (because of inconsistency):

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.25-debug |
+--------------+
1 row in set (0.00 sec)

mysql> USE test;
Database changed
mysql> create view vvvvv as select @a;
ERROR 1351 (HY000): View's SELECT contains a variable or parameter
mysql> create function geta() returns int deterministic return @a;
Query OK, 0 rows affected (0.05 sec)

mysql> create view vvvvv as select geta();
Query OK, 0 rows affected (0.01 sec)

So, either feature you asked for should be implemented, or functions used in CREATE VIEWS should be checked to NOT return any user variables also.
[2 Sep 2006 9:05] Valeriy Kravchuk
Bug #12760 was marked as a duplicate of this one (as this is S3).
[4 Sep 2006 16:51] Dmitry L
Can i hope it would be fixed in near future?
[22 Sep 2006 12:13] Konstantin Osipov
Stealing this, related to Bug#20953
[19 Jun 2007 19:58] Peter Gulutzan
The MySQL Reference Manual says for "CREATE VIEW Syntax" that
"The SELECT statement cannot refer to system or user variables",
so it's not a bug to disallow a variable in a view definition.
And this report is not a claim that it's a big to allow a variable
in a function definition.
And inconsistency is not a bug.
I'll change this to "feature request" till somebody explains
how it's a bug.
[8 Aug 2007 12:52] Konstantin Osipov
Thank you for a valid feature request.