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
• Status: Verified
• Category: MySQL Server: Stored Routines
• Severity: S4 (Feature request)
• Version: 5.1.5-alpha-max
• OS: Linux (Debian Etch)
• 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.