Description:
I created a stored procedure that accepts a query as varchar and creates a temporary table to use in cursor.
When I execute first time works fine, but if I change the column names, in passed query as varchar, I get some errors.
How to repeat:
1. Create a procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_test$$
CREATE PROCEDURE `sp_test`(p_query varchar(250))
BEGIN
-- Declarations
declare var1 varchar(250); -- change as need
declare var2 varchar(250); -- change as need
declare no_more_rows bool default false;
DECLARE cursor1 CURSOR FOR select * from tmp_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
-- creating temporary table and open as cursor
drop temporary table if exists tmp_table;
SET @prepared_sql = concat('create temporary table tmp_table ',p_query);
PREPARE stmt FROM @prepared_sql;
EXECUTE stmt;
OPEN cursor1;
-- Business Logic
cursor_loop: LOOP
fetch cursor1 into var1, var2;
IF no_more_rows THEN
LEAVE cursor_loop;
END IF;
-- DO SOMETHING WITH var1,var2
END LOOP cursor_loop;
-- closing cursors
CLOSE cursor1;
DEALLOCATE PREPARE stmt;
-- select only for test purposes
select * from tmp_table;
drop temporary table tmp_table;
END$$
2. Execute
mysql> call sp_test('select table_name, table_collation from information_schema.tables where table_schema = \'mysql\';');
+---------------------------+-------------------+
| table_name | table_collation |
+---------------------------+-------------------+
| columns_priv | utf8_bin |
| db | utf8_bin |
| event | utf8_general_ci |
| func | utf8_bin |
| general_log | utf8_general_ci |
| help_category | utf8_general_ci |
| help_keyword | utf8_general_ci |
| help_relation | utf8_general_ci |
| help_topic | utf8_general_ci |
| host | utf8_bin |
| ndb_binlog_index | latin1_swedish_ci |
| plugin | utf8_bin |
| proc | utf8_general_ci |
| procs_priv | utf8_bin |
| servers | utf8_general_ci |
| slow_log | utf8_general_ci |
| tables_priv | utf8_bin |
| time_zone | utf8_general_ci |
| time_zone_leap_second | utf8_general_ci |
| time_zone_name | utf8_general_ci |
| time_zone_transition | utf8_general_ci |
| time_zone_transition_type | utf8_general_ci |
| user | utf8_bin |
| user_info | utf8_bin |
| zap17_codigo | latin1_swedish_ci |
| zap17_ganador | latin1_swedish_ci |
+---------------------------+-------------------+
26 rows in set (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show warnings;
+-------+------+---------------------------+
| Level | Code | Message |
+-------+------+---------------------------+
| Note | 1051 | Unknown table 'tmp_table' |
+-------+------+---------------------------+
1 row in set (0.00 sec)
3. Execute again with column order changed
mysql> call sp_test('select table_collation,table_name from information_schema.tables where table_schema = \'mysql\';');
+---------------------------+-------------------+
| table_name | table_collation |
+---------------------------+-------------------+
| columns_priv | utf8_bin |
| db | utf8_bin |
| event | utf8_general_ci |
| func | utf8_bin |
| general_log | utf8_general_ci |
| help_category | utf8_general_ci |
| help_keyword | utf8_general_ci |
| help_relation | utf8_general_ci |
| help_topic | utf8_general_ci |
| host | utf8_bin |
| ndb_binlog_index | latin1_swedish_ci |
| plugin | utf8_bin |
| proc | utf8_general_ci |
| procs_priv | utf8_bin |
| servers | utf8_general_ci |
| slow_log | utf8_general_ci |
| tables_priv | utf8_bin |
| time_zone | utf8_general_ci |
| time_zone_leap_second | utf8_general_ci |
| time_zone_name | utf8_general_ci |
| time_zone_transition | utf8_general_ci |
| time_zone_transition_type | utf8_general_ci |
| user | utf8_bin |
| user_info | utf8_bin |
| zap17_codigo | latin1_swedish_ci |
| zap17_ganador | latin1_swedish_ci |
+---------------------------+-------------------+
26 rows in set (0.01 sec)
4. Execute again with column names changed:
mysql> call sp_test('select table_name as name,table_collation as collation from information_schema.tables where table_schema = \'mysql\';');
ERROR 1054 (42S22): Unknown column 'test.tmp_table.table_name' in 'field list'
Description: I created a stored procedure that accepts a query as varchar and creates a temporary table to use in cursor. When I execute first time works fine, but if I change the column names, in passed query as varchar, I get some errors. How to repeat: 1. Create a procedure: DELIMITER $$ DROP PROCEDURE IF EXISTS sp_test$$ CREATE PROCEDURE `sp_test`(p_query varchar(250)) BEGIN -- Declarations declare var1 varchar(250); -- change as need declare var2 varchar(250); -- change as need declare no_more_rows bool default false; DECLARE cursor1 CURSOR FOR select * from tmp_table; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE; -- creating temporary table and open as cursor drop temporary table if exists tmp_table; SET @prepared_sql = concat('create temporary table tmp_table ',p_query); PREPARE stmt FROM @prepared_sql; EXECUTE stmt; OPEN cursor1; -- Business Logic cursor_loop: LOOP fetch cursor1 into var1, var2; IF no_more_rows THEN LEAVE cursor_loop; END IF; -- DO SOMETHING WITH var1,var2 END LOOP cursor_loop; -- closing cursors CLOSE cursor1; DEALLOCATE PREPARE stmt; -- select only for test purposes select * from tmp_table; drop temporary table tmp_table; END$$ 2. Execute mysql> call sp_test('select table_name, table_collation from information_schema.tables where table_schema = \'mysql\';'); +---------------------------+-------------------+ | table_name | table_collation | +---------------------------+-------------------+ | columns_priv | utf8_bin | | db | utf8_bin | | event | utf8_general_ci | | func | utf8_bin | | general_log | utf8_general_ci | | help_category | utf8_general_ci | | help_keyword | utf8_general_ci | | help_relation | utf8_general_ci | | help_topic | utf8_general_ci | | host | utf8_bin | | ndb_binlog_index | latin1_swedish_ci | | plugin | utf8_bin | | proc | utf8_general_ci | | procs_priv | utf8_bin | | servers | utf8_general_ci | | slow_log | utf8_general_ci | | tables_priv | utf8_bin | | time_zone | utf8_general_ci | | time_zone_leap_second | utf8_general_ci | | time_zone_name | utf8_general_ci | | time_zone_transition | utf8_general_ci | | time_zone_transition_type | utf8_general_ci | | user | utf8_bin | | user_info | utf8_bin | | zap17_codigo | latin1_swedish_ci | | zap17_ganador | latin1_swedish_ci | +---------------------------+-------------------+ 26 rows in set (0.01 sec) Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show warnings; +-------+------+---------------------------+ | Level | Code | Message | +-------+------+---------------------------+ | Note | 1051 | Unknown table 'tmp_table' | +-------+------+---------------------------+ 1 row in set (0.00 sec) 3. Execute again with column order changed mysql> call sp_test('select table_collation,table_name from information_schema.tables where table_schema = \'mysql\';'); +---------------------------+-------------------+ | table_name | table_collation | +---------------------------+-------------------+ | columns_priv | utf8_bin | | db | utf8_bin | | event | utf8_general_ci | | func | utf8_bin | | general_log | utf8_general_ci | | help_category | utf8_general_ci | | help_keyword | utf8_general_ci | | help_relation | utf8_general_ci | | help_topic | utf8_general_ci | | host | utf8_bin | | ndb_binlog_index | latin1_swedish_ci | | plugin | utf8_bin | | proc | utf8_general_ci | | procs_priv | utf8_bin | | servers | utf8_general_ci | | slow_log | utf8_general_ci | | tables_priv | utf8_bin | | time_zone | utf8_general_ci | | time_zone_leap_second | utf8_general_ci | | time_zone_name | utf8_general_ci | | time_zone_transition | utf8_general_ci | | time_zone_transition_type | utf8_general_ci | | user | utf8_bin | | user_info | utf8_bin | | zap17_codigo | latin1_swedish_ci | | zap17_ganador | latin1_swedish_ci | +---------------------------+-------------------+ 26 rows in set (0.01 sec) 4. Execute again with column names changed: mysql> call sp_test('select table_name as name,table_collation as collation from information_schema.tables where table_schema = \'mysql\';'); ERROR 1054 (42S22): Unknown column 'test.tmp_table.table_name' in 'field list'