Bug #17409 PREPARE doesn't support queries in local variables
Submitted: 15 Feb 2006 2:21 Modified: 6 Mar 2006 12:37
Reporter: Ian Gulliver Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version:5.1.5-alpha-max OS:Linux (Debian Etch)
Assigned to: CPU Architecture:Any

[15 Feb 2006 2:21] Ian Gulliver
Description:
PREPARE will take literal strings or global variables for the query, but not local variables.

How to repeat:
delimiter |

DROP PROCEDURE IF EXISTS prepare_local|

CREATE PROCEDURE prepare_local ()
        BEGIN
                DECLARE a BLOB DEFAULT 'SHOW DATABASES';

                PREPARE test_prep FROM a;

                EXECUTE test_prep;

                DEALLOCATE PREPARE test_prep;
        END|

Suggested fix:
Support local variables the same way they are supported elsewhere.
[6 Mar 2006 12:37] Valeriy Kravchuk
Thank you for a reasonable feature request.
[17 Jul 2011 4:23] Vishal Jogi
Is the fix for this bug available now?
[1 Dec 2011 13:32] rina rakotoarimaana
Just to ask if the fix is available.
Thank's
[2 Apr 2012 3:33] Rick James
Both DECLAREd variables and parameters fail to be PREPAREable.

DELIMITER //
DROP PROCEDURE IF EXISTS try_vp //
CREATE PROCEDURE try_vp (IN param INT)
BEGIN
    DECLARE declared INT DEFAULT 4;
    SET @at = 2;

    -- All of this set works:
    SELECT 1;
    SELECT @at;
    SELECT param;
    SELECT declared;
    SELECT 5 INTO declared;

    -- Only the first two of these work, in spite of being 'identical' to the previous:
    SET @sql = 'SELECT 1';
    PREPARE s FROM @sql;  EXECUTE s;  DEALLOCATE PREPARE s;
    SET @sql = 'SELECT @at';
    -- Fail:
    PREPARE s FROM @sql;  EXECUTE s;  DEALLOCATE PREPARE s; -- ERROR 1054 (42S22): Unknown column
    SET @sql = 'SELECT param';
    PREPARE s FROM @sql;  EXECUTE s;  DEALLOCATE PREPARE s; -- ERROR 1054 (42S22): Unknown column
    SET @sql = 'SELECT declared';
    PREPARE s FROM @sql;  EXECUTE s;  DEALLOCATE PREPARE s; -- ERROR 1327 (42000): Undeclared variable
    SET @sql = 'SELECT 5 INTO declared';
END //
DELIMITER ;
CALL try_vp(3);
[2 Apr 2012 3:39] Rick James
Even if this 'bug' is not 'fixed', could you please augment the documentation to indicate which types of variables can/cannot be used where.
[18 Feb 2014 23:40] Federico Razzoli
This is a problem when developing a public library, because the user must not use the same user-defined variables. And if 2 libraries work together, they must not use the same variables.

While this is not related to this request, please note that the same problem applies to prepared statements: they must have unique names.