A fix and a test case for Bug#47648 "main.merge fails sporadically".

If a prepared statement used both a MyISAMMRG table and a stored 
function or trigger, execution could fail with "No such table"
error or crash. 
The error would come from a failure of the MyISAMMRG engine
to meet the expectations of the prelocking algorithm, 
in particular maintain lex->query_tables_own_last pointer
in sync with lex->query_tables_last pointer/the contents
of lex->query_tables. When adding merge children, the merge
engine would extend the table list. Then, when adding 
prelocked tables, the prelocking algorithm would use a pointer
to the last merge child to assign to lex->query_tables_own_last.
Then, when merge children were removed at the end of
open_tables(), lex->query_tables_own_last
was not updated, and kept pointing
to a removed merge child.

The fix ensures that query_tables_own_last is always in
sync with lex->query_tables_last.

This is a regression introduced by WL#4144 and present only
in next-4284 tree and 6.0.

mysql-test/r/merge.result:
  Update results (Bug#47648).
mysql-test/t/merge.test:
  Add a test case for Bug#47648.
  Update the result file to reflect a fix of another bug
  in MyISAMMRG code: not maintaining lex->query_tables_own_last
  allowed a stored function or trigger to modify a merge table
  which was already updated by the main statement.
  It is not allowed for other storage engines, and should
  not be allowed for MyISAMMRG.
storage/myisammrg/ha_myisammrg.cc:
  When adding children to the list of tables to open,
  make sure that we properly set lex->query_tables_own_last.
  When removing the children, update lex->query_tables_own_last
  if necessary.

1 files changed, 55 insertions, 2 deletions

diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test
index c3499037e97..f0d8960322e 100644
--- a/mysql-test/t/merge.test
+++ b/mysql-test/t/merge.test
@@ -1941,7 +1941,9 @@ UNLOCK TABLES;
 DROP TRIGGER t2_ai;
 DROP TABLE tm1, t1, t2;
 --echo #
---echo # Don't select MERGE child when trying to get prelocked table.
+--echo # Don't allow an update of a MERGE child in a trigger
+--echo # if the table's already being modified by the main
+--echo # statement.
 --echo #
 CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
@@ -1949,6 +1951,31 @@ CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
 CREATE TRIGGER tm1_ai AFTER INSERT ON tm1
   FOR EACH ROW INSERT INTO t1 VALUES(11);
 LOCK TABLE tm1 WRITE, t1 WRITE;
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT INTO tm1 VALUES (1);
+SELECT * FROM tm1;
+UNLOCK TABLES;
+LOCK TABLE t1 WRITE, tm1 WRITE;
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT INTO tm1 VALUES (1);
+SELECT * FROM tm1;
+UNLOCK TABLES;
+DROP TRIGGER tm1_ai;
+DROP TABLE tm1, t1;
+
+--echo #
+--echo # Don't select MERGE child when trying to get a prelocked table.
+--echo #
+--echo # Due to a limitation demonstrated by the previous test
+--echo # we can no longer use a write-locked prelocked table.
+--echo # The test is kept for historical purposes.
+--echo #
+CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
+CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
+  INSERT_METHOD=LAST;
+CREATE TRIGGER tm1_ai AFTER INSERT ON tm1
+  FOR EACH ROW SELECT max(c1) FROM t1 INTO @var;
+LOCK TABLE tm1 WRITE, t1 WRITE;
 INSERT INTO tm1 VALUES (1);
 SELECT * FROM tm1;
 UNLOCK TABLES;
@@ -1970,7 +1997,7 @@ CREATE TABLE t5 (c1 INT) ENGINE=MyISAM;
 CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3,t4,t5)
   INSERT_METHOD=LAST;
 CREATE TRIGGER t2_au AFTER UPDATE ON t2
-  FOR EACH ROW INSERT INTO t3 VALUES(33);
+  FOR EACH ROW SELECT MAX(c1) FROM t1 INTO @var;
 CREATE FUNCTION f1() RETURNS INT
   RETURN (SELECT MAX(c1) FROM t4);
 LOCK TABLE tm1 WRITE, t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE, t5 WRITE;
@@ -2037,4 +2064,30 @@ connection default;
 --echo # Disconnecting con1, all mdl_tickets must have been released.
 disconnect con1;
 
+--echo #
+--echo # A test case for Bug#47648   main.merge fails sporadically
+--echo #
+--echo # Make sure we correctly maintain lex->query_tables_last_own.
+--echo #
+create table t1 (c1 int not null);
+create table t2 (c1 int not null);
+create table t3 (c1 int not null);
+
+create function f1 () returns int return (select max(c1) from t3);
+
+create table t4 (c1 int not null) engine=merge union=(t1,t2) insert_method=last ;
+
+select * from t4 where c1 < f1();
+prepare stmt from "select * from t4 where c1 < f1()";
+execute stmt;
+execute stmt;
+execute stmt;
+drop function f1;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+drop table t4, t3, t2, t1;
+
 --echo End of 6.0 tests
+