| Bug #62406 | new cursor, on table with same name but different structure as used before fails | ||
|---|---|---|---|
| Submitted: | 10 Sep 2011 17:46 | Modified: | 11 Sep 2011 6:43 |
| Reporter: | Shlomi Noach (OCA) | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | >= 5.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | cursor, temporary table | ||
[11 Sep 2011 5:40]
MySQL Verification Team
isn't this bug #12257 ?
[11 Sep 2011 6:09]
Shlomi Noach
Yes, it appears so. My friend, this makes it a 6 year old bug, with quite a few duplicates.
[11 Sep 2011 6:11]
Shlomi Noach
Ummm. while it is in principle similar, do note that the bug I'm presenting here occurs on two different calls to a stored routine (so, this is not twice in the same call to a routine). It may or may not make a difference.
[11 Sep 2011 6:25]
MySQL Verification Team
what is @q here ?
[11 Sep 2011 6:30]
MySQL Verification Team
None-the-less, a dirty workaround is to manually flush the stored procedure cache between calls, but this obviously has a performance impact. It can be done easily with: CREATE OR REPLACE VIEW tmpview AS SELECT 1;
[11 Sep 2011 6:41]
Shlomi Noach
My apologies, the following lines:
"
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
"
are superfluous and should not be there as part of the bug report.
[11 Sep 2011 6:43]
Shlomi Noach
Thank you for the workaround
Description: A stored routine declares a cursor on a temporary table. works well. Cursor is closed. 2nd invocation within same session: stored routine creates new cursor on a temporary table - with same name but different structure - FETCH fails due to unexpected columns. On a new session - not a problem. Seems to me a cursor 'remembers' table structure, even after being closed, even after exiting stored routine (!), perhaps as bug, or as part of optimization. How to repeat: -- create routine delimiter $$ drop procedure if exists test_bug $$ create procedure test_bug() begin drop temporary table if exists tmp; IF @test_param = 1 THEN create temporary table tmp(id int unsigned); insert into tmp (id) values (1); ELSE create temporary table tmp(name varchar(255)); insert into tmp (name) values ('hello'); END IF; begin declare v_id varchar(255); declare csr1 cursor for select * from tmp; PREPARE st FROM @q; EXECUTE st; DEALLOCATE PREPARE st; open csr1; fetch csr1 into v_id; select v_id; close csr1; end; end; $$ delimiter ; -- -- test bug: -- SET @test_param := 1; CALL test_bug(); SET @test_param := 2; CALL test_bug(); ================================= I get the following output: root@mysql-5.1.51> SET @test_param := 1; Query OK, 0 rows affected (0.00 sec) root@mysql-5.1.51> CALL test_bug(); +---+ | 7 | +---+ | 7 | +---+ 1 row in set (0.00 sec) +------+ | v_id | +------+ | 1 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) root@mysql-5.1.51> SET @test_param := 2; Query OK, 0 rows affected (0.00 sec) root@mysql-5.1.51> CALL test_bug(); +---+ | 7 | +---+ | 7 | +---+ 1 row in set (0.01 sec) ERROR 1054 (42S22): Unknown column 'test.tmp.id' in 'field list'