Details
Minor
Description
Sorry, I understand that this test case is complex, but I wasn't able to simplify it:
MariaDB [test]> DROP PROCEDURE IF EXISTS materialize_sql;
Query OK, 0 rows affected (0.04 sec)
MariaDB [test]> DELIMITER ||
MariaDB [test]> CREATE PROCEDURE `materialize_sql`(IN p_sql TEXT)
-> MODIFIES SQL DATA
-> BEGIN
-> SET @v_materialized_sql := CONCAT_WS('',
-> 'CREATE OR REPLACE TABLE `_xy`'
-> , ' ENGINE = CONNECT'
-> , ' TABLE_TYPE = MYSQL'
-> , ' SRCDEF = ''', REPLACE(p_sql, '''', ''''''), ''''
-> , ' CONNECTION = ''mysql://root:root@localhost/'''
-> );
-> PREPARE stmt_materialized_sql FROM @v_materialized_sql;
-> EXECUTE stmt_materialized_sql;
-> DEALLOCATE PREPARE stmt_materialized_sql;
-> SET @v_materialized_sql := NULL;
-> SELECT * FROM _xy;
-> END ||
Query OK, 0 rows affected (0.04 sec)
MariaDB [test]> DELIMITER ;
MariaDB [test]> CALL materialize_sql('SELECT 1 AS a');
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.14 sec)
Query OK, 0 rows affected (0.14 sec)
MariaDB [test]> CALL materialize_sql('SELECT 1 AS z');
ERROR 1054 (42S22): Unknown column 'test._xy.a' in 'field list'
Just to be clear, the procedure creates a CONNECT table based on a query, so that the same resultset can be read with a SELECT. This allows, for example, to loop the results of SHOW MASTER STATUS with a cursor, use subqueries, etc. That's why I set the bug to "major" despite it being so exotic.
The problem seems to occur on the second call, when the underlying table is replaced with another table which has different columns. Note that the table is correctly replaced. But the error message still references to a column which doesn't exist anymore.
Gliffy Diagrams
Attachments
Issue Links
- is part of
-
MDEV-5816 MySQL WL#4179 - Stored programs: validation of stored program statements
-
- Stalled
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
test case without the Connect engine:
DELIMITER ||;
CREATE PROCEDURE `materialize_sql`(IN p_sql TEXT)
BEGIN
SET @v_materialized_sql := CONCAT('CREATE OR REPLACE TABLE `_xy`(',p_sql,' int)');
PREPARE stmt_materialized_sql FROM @v_materialized_sql;
EXECUTE stmt_materialized_sql;
DEALLOCATE PREPARE stmt_materialized_sql;
SET @v_materialized_sql := NULL;
SELECT * FROM _xy;
END ||
CALL materialize_sql('a')||
CALL materialize_sql('b')||
as a workaround, you can PREPARE and EXECUTE your SELECT statement.
Fixing this issue requires rather big and intrusive changes, we cannot do that in 10.0 anymore.
The only way I found to workaround this bug is dropping and recreate the procedure. Of course this is not an acceptable workaround, but I hope that this information can help you.