MDEV-22180 Planner opens unnecessary tables when updated table is referenced by foreign keys

under LOCK TABLES we still have to open everything, otherwise DML
prelocking will try to take an MDL on a table that wasn't in the
LOCK TABLES list.

3 files changed, 57 insertions, 1 deletions

diff --git a/mysql-test/suite/innodb/r/foreign-keys.result b/mysql-test/suite/innodb/r/foreign-keys.result
index 3778dd082c7..d76f01f7095 100644
--- a/mysql-test/suite/innodb/r/foreign-keys.result
+++ b/mysql-test/suite/innodb/r/foreign-keys.result
@@ -144,4 +144,32 @@ show status like '%opened_tab%';
 Variable_name	Value
 Opened_table_definitions	1
 Opened_tables	1
+flush tables;
+flush status;
+lock tables t1 write;
+show status like '%opened_tab%';
+Variable_name	Value
+Opened_table_definitions	2
+Opened_tables	2
+insert t1 values (4,4);
+show status like '%opened_tab%';
+Variable_name	Value
+Opened_table_definitions	2
+Opened_tables	2
+unlock tables;
+create function foo() returns int
+begin
+insert t1 values (5,5);
+return 5;
+end|
+flush tables;
+flush status;
+select foo();
+foo()
+5
+show status like '%opened_tab%';
+Variable_name	Value
+Opened_table_definitions	2
+Opened_tables	2
+drop function foo;
 drop table t2, t1;
diff --git a/mysql-test/suite/innodb/t/foreign-keys.test b/mysql-test/suite/innodb/t/foreign-keys.test
index b3293e88676..9f1622d74cd 100644
--- a/mysql-test/suite/innodb/t/foreign-keys.test
+++ b/mysql-test/suite/innodb/t/foreign-keys.test
@@ -162,7 +162,6 @@ DROP TABLE t1, t2;
 #
 # MDEV-22180 Planner opens unnecessary tables when updated table is referenced by foreign keys
 #
-source include/have_innodb.inc;
 
 create table t1 (pk int primary key, data int) engine=innodb;
 insert t1 values (1,1),(2,2),(3,3);
@@ -180,4 +179,24 @@ flush status;
 # neither are parent tables
 update t2 set t1_pk=11 where t1_pk+1>10;
 show status like '%opened_tab%';
+# under LOCK TABLES
+flush tables;
+flush status;
+lock tables t1 write;
+show status like '%opened_tab%';
+insert t1 values (4,4);
+show status like '%opened_tab%';
+unlock tables;
+delimiter |;
+create function foo() returns int
+begin
+  insert t1 values (5,5);
+  return 5;
+end|
+delimiter ;|
+flush tables;
+flush status;
+select foo();
+show status like '%opened_tab%';
+drop function foo;
 drop table t2, t1;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 1dbed8c55d1..c092faa986b 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5037,10 +5037,19 @@ bool Lock_tables_prelocking_strategy::
 handle_table(THD *thd, Query_tables_list *prelocking_ctx,
              TABLE_LIST *table_list, bool *need_prelocking)
 {
+  TABLE_LIST **last= prelocking_ctx->query_tables_last;
+
   if (DML_prelocking_strategy::handle_table(thd, prelocking_ctx, table_list,
                                             need_prelocking))
     return TRUE;
 
+  /*
+    normally we don't need to open FK-prelocked tables for RESTRICT,
+    MDL is enough. But under LOCK TABLES we have to open everything
+  */
+  for (TABLE_LIST *tl= *last; tl; tl= tl->next_global)
+    tl->open_strategy= TABLE_LIST::OPEN_NORMAL;
+
   /* We rely on a caller to check that table is going to be changed. */
   DBUG_ASSERT(table_list->lock_type >= TL_WRITE_ALLOW_WRITE);