MDEV-26009 Server crash when calling twice procedure using FOR-loop

The problem was that instructions sp_instr_cursor_copy_struct and
sp_instr_copen uses the same lex, adding and removing "tail" of
prelocked tables and forgetting that tail of all tables is kept in
LEX::query_tables_last. If the LEX used only by one instruction
or the query do not have prelocked tables it is not important.
But to work correctly in all cases LEX::query_tables_last should
be reset to make new tables added in the correct list (after last
table in the LEX instead after last table of the prelocking "tail"
which was cut).

2 files changed, 119 insertions, 0 deletions

diff --git a/mysql-test/main/sp-cursor.result b/mysql-test/main/sp-cursor.result
index 2656ef8821d..b1c2b335ea4 100644
--- a/mysql-test/main/sp-cursor.result
+++ b/mysql-test/main/sp-cursor.result
@@ -737,3 +737,66 @@ rec.en1
 c
 DROP PROCEDURE p1;
 DROP TABLE t1;
+#
+# MDEV-26009: Server crash when calling twice procedure using FOR-loop
+#
+CREATE TABLE t1 ( id int, name varchar(24));
+INSERT INTO t1 values (1, 'x'), (2, 'y'), (3, 'z');
+create function get_name(_id int) returns varchar(24)
+return (select name from t1 where id = _id);
+select get_name(id) from t1;
+get_name(id)
+x
+y
+z
+create  procedure test_proc()
+begin
+declare _cur cursor for select get_name(id) from t1;
+for row in _cur do select 1; end for;
+end;
+^^
+call test_proc();
+1
+1
+1
+1
+1
+1
+call test_proc();
+1
+1
+1
+1
+1
+1
+drop procedure test_proc;
+drop function get_name;
+drop table t1;
+CREATE TABLE t1 (id int, name varchar(24));
+INSERT INTO t1 (id, name) VALUES (1, 'x'),(2, 'y'),(3, 'z');
+create function get_name(_id int) returns varchar(24) 
+return (select name from t1 where id = _id);
+create view v1 as select get_name(id) from t1;
+create  procedure test_proc()
+begin
+declare _cur cursor for select 1 from v1;   
+for row in _cur do select 1; end for;
+end$$
+call test_proc();
+1
+1
+1
+1
+1
+1
+call test_proc();
+1
+1
+1
+1
+1
+1
+drop procedure test_proc;
+drop view v1;
+drop function get_name;
+drop table t1;
diff --git a/mysql-test/main/sp-cursor.test b/mysql-test/main/sp-cursor.test
index 97483ef9caf..9794815c784 100644
--- a/mysql-test/main/sp-cursor.test
+++ b/mysql-test/main/sp-cursor.test
@@ -744,3 +744,59 @@ DELIMITER ;$$
 CALL p1();
 DROP PROCEDURE p1;
 DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-26009: Server crash when calling twice procedure using FOR-loop
+--echo #
+
+
+CREATE TABLE t1 ( id int, name varchar(24));
+INSERT INTO t1 values (1, 'x'), (2, 'y'), (3, 'z');
+
+create function get_name(_id int) returns varchar(24)
+    return (select name from t1 where id = _id);
+
+select get_name(id) from t1;
+
+delimiter ^^;
+
+create  procedure test_proc()
+begin
+    declare _cur cursor for select get_name(id) from t1;
+    for row in _cur do select 1; end for;
+end;
+^^
+delimiter ;^^
+
+call test_proc();
+call test_proc();
+
+drop procedure test_proc;
+drop function get_name;
+drop table t1;
+
+
+CREATE TABLE t1 (id int, name varchar(24));
+INSERT INTO t1 (id, name) VALUES (1, 'x'),(2, 'y'),(3, 'z');
+ 
+create function get_name(_id int) returns varchar(24) 
+	return (select name from t1 where id = _id);
+ 
+create view v1 as select get_name(id) from t1;
+ 
+delimiter $$;
+create  procedure test_proc()
+begin
+    declare _cur cursor for select 1 from v1;   
+    for row in _cur do select 1; end for;
+end$$
+delimiter ;$$
+
+call test_proc();
+call test_proc();
+
+drop procedure test_proc;
+drop view v1;
+drop function get_name;
+drop table t1;