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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.