MySQL Bugs: #27579: Table names taken from expressions

Bug #27579	Table names taken from expressions - SELECT * FROM @table
Submitted:	2 Apr 2007 4:30	Modified:	30 Apr 2007 10:56
Reporter:	Ondra Zizka	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.x	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	expressions, stored procedures, table names, variables

Description:
It could be handy if table_references could be given as expressions. Especially in stored procedures, they could be written as libraries that way.

One could use views, but first it's not comfortable, and in certain situations, they don't stand for tables themselves well.

How to repeat:
SET @prefix := 'my_prefix';
SET @table := 'my_table';
SELECT * FROM @prefix+@table;
-- Yes and I would like an operator for concatenating ;-)

Suggested fix:
...

Thank you for a problem report. Please, check if prepared statements can be used for what you requested. Read http://dev.mysql.com/doc/refman/5.0/en/sqlps.html.

Generally, it could be done using Prepared Statements; but, when it comes to writing a Stored Procedure working with a sub-set of tables (e.g. depending on an ENUM parameter), with about 15-20 different statements, and every single one must be CONCAT()enated, PREPAREd, EXECUTEd, and DROPped, then this approach becomes somewhat... verbose, at the best, not talking about possibility of occasionally appearing syntax exceptions and security question (injection).

There is one more drawback: It seems that MySQL does not recognize the type of the statement and automatically assumes it returns a resultset; and this causes other problems like "Can't execute in the given context" exceptions etc. 

Being aware of other things that are on MySQL's development plan, I still wonder that not much developer's will for this feature. Considering Stored Procedures, this is the feature I would welcome most at the current state.

Like in PREPARE ... FROM preparable_stmt suffices with either string or a @user_variable, for "table_reference" a possibility to use either a table name  or a @var would suffice (as the variable would be mostly prepared in advance anyway...).  

So, let it be an idea for a potential future implementation...

Regards, Ondra

I agree, having run into a similar situation. Prepared statements have lots of disadvantages besides the cumbersomeness, including some problems in being able to access local variables.

I am writing a library of stored procedures, but I want them to be able to apply to different tables from different schemas without having to copy the set of stored procedures into each schema I want to use them in.

As an alternative, is it possible to create a temporary table which is really an alias for a specified table in another schema? (with all the same security privileges as the real table) Then I could just create this temporary alias table, and then use a hardcoded select statement.