Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4.4
-
None
-
Ubuntu Nobel
Description
I've just worked around an obscure bug where the error message did not match the error condition.
A stored procedure calls another stored procedure multiple times:
create procedure testCursorCall() |
begin
|
select '1'; |
call testCursor(vPractIds);
|
|
select '2'; |
call testCursor(vPractIds);
|
|
select '3'; |
call testCursor(vPractIds);
|
|
select '4'; |
call testCursor(vPractIds);
|
end |
The routine testCursor creates a cursor to run through a temp table. It then destroys and recreates the temp table before using the cursor:
create procedure testCursor() |
begin
|
declare done, vItem int unsigned; |
declare testCur cursor for |
select x.Item |
from tmpFoo as x |
order by x.Item asc; |
declare continue handler for not found set done = 1; |
 |
drop temporary table if exists tmpFoo; |
create temporary table tmpFoo |
(Id int unsigned primary key auto_increment, |
Item varchar(256) |
) engine = memory;
|
 |
insert into tmpFoo |
(Item)
|
values |
("1"), ("2"); |
 |
open testCur; |
curLoop: loop
|
set done = 0; |
fetch testCur into vItem; |
if done = 1 then |
leave curLoop;
|
end if; |
end loop; |
close testCur; |
end |
The second time testCursorCall() calls testCursor(), I get a syntax error:
MariaDB [mbook_dev]> call testCursorCall();
|
+---+
|
| 1 |
|
+---+
|
| 1 |
|
+---+
|
1 row in set (0.003 sec)
|
 |
+---+
|
| 2 |
|
+---+
|
| 2 |
|
+---+
|
1 row in set (0.007 sec)
|
 |
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select x.Item
|
from tmpFoo as x
|
order by x.Item asc' at line 2
|
After much experimentation, I found that the actual error is that the temp table should not be dropped and recreated.
I suspect Maria is not recompiling the Cursor on the second run, but the table being dropped and recreated is destroying the internal table reference the compiled cursor is using. This is just a guess - I know nothing of MariaDB internals.
If testCursor is changed to the following, then it works:
create procedure testCursor() |
begin
|
declare done, vItem int unsigned; |
declare testCur cursor for |
select x.Item |
from tmpFoo as x |
order by x.Item asc; |
declare continue handler for not found set done = 1; |
 |
create temporary table if not exists tmpFoo |
(Id int unsigned primary key auto_increment, |
Item varchar(256) |
) engine = memory;
|
truncate table tmpFoo; |
 |
insert into tmpFoo |
(Item)
|
values |
("1"), ("2"); |
 |
open testCur; |
curLoop: loop
|
set done = 0; |
fetch testCur into vItem; |
if done = 1 then |
leave curLoop;
|
end if; |
end loop; |
close testCur; |
end |
MariaDB [mbook_dev]> call testCursorCall();
|
+---+
|
| 1 |
|
+---+
|
| 1 |
|
+---+
|
1 row in set (0.001 sec)
|
 |
+---+
|
| 2 |
|
+---+
|
| 2 |
|
+---+
|
1 row in set (0.003 sec)
|
 |
+---+
|
| 3 |
|
+---+
|
| 3 |
|
+---+
|
1 row in set (0.004 sec)
|
 |
+---+
|
| 4 |
|
+---+
|
| 4 |
|
+---+
|
1 row in set (0.005 sec)
|
 |
Query OK, 8 rows affected (0.005 sec)
|
Thanks,
-Oli