MDEV-16686 - Assertion `(*tables)->reginfo.lock_type >= TL_READ' failedbb-5.5-svoj-MDEV-18783

             in lock_external upon using a mix of trigger, view, SP

Prevent TL_IGNORE tables from being added to the SP's m_sptabs hash.

Test cases merged from MySQL rev 41c5f4a0235ac9375080c4c79d207fe429e94a2c
BUG#19988193: ASSERTION `(*TABLES)->REGINFO.LOCK_TYPE >= TL_READ'
              FAILED IN LOCK_EXTERNAL
BUG#21198646: ASSERTION FAILED: (*TABLES)->REGINFO.LOCK_TYPE >=
              TL_READ FILE LOCK.CC, LINE 356

3 files changed, 114 insertions, 1 deletions

diff --git a/mysql-test/r/tablelock.result b/mysql-test/r/tablelock.result
index 6923ad40916..379e4e908b4 100644
--- a/mysql-test/r/tablelock.result
+++ b/mysql-test/r/tablelock.result
@@ -55,3 +55,54 @@ f1	int(11)	YES		NULL
 insert into t1 values(2);
 drop table t1;
 unlock tables;
+#
+# Bug#19988193 ASSERTION `(*TABLES)->REGINFO.LOCK_TYPE >= TL_READ'
+# FAILED IN LOCK_EXTERNAL
+#
+CREATE TABLE t1(a INT);
+CREATE PROCEDURE p1() CREATE VIEW v1 AS SELECT * FROM t1;
+
+# Create trigger calling proc creating view, when view DOES NOT
+# exist already
+CREATE TRIGGER trg_p1_t1 AFTER INSERT ON t1 FOR EACH ROW CALL p1();
+
+# Verify that it is possible to lock table
+LOCK TABLES t1 WRITE;
+UNLOCK TABLES;
+
+# Fails, as expected
+INSERT INTO t1 VALUES (1);
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+
+# Make sure v1 already exists
+CREATE VIEW v1 AS SELECT a+1 FROM t1;
+
+# Verify that it is possible to lock table
+LOCK TABLES t1 WRITE;
+UNLOCK TABLES;
+
+# Verify that we get the expected error when inserting into the table
+INSERT INTO t1 VALUES (1);
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+
+# Cleanup
+DROP TRIGGER trg_p1_t1;
+DROP PROCEDURE p1;
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# Bug#21198646 ASSERTION FAILED: (*TABLES)->REGINFO.LOCK_TYPE >= TL_READ
+# FILE LOCK.CC, LINE 356
+#
+CREATE TABLE t2(a INT);
+# Create procedure p1 invoking RENAME TABLE
+CREATE PROCEDURE p1() RENAME TABLE t2 TO t3;
+# Create function f1 calling p1
+CREATE FUNCTION f1() RETURNS INT BEGIN CALL p1(); RETURN 1; END $
+# Invoke function f1 and verify that we get the expected error
+SELECT f1();
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+# Cleanup
+DROP PROCEDURE p1;
+DROP FUNCTION f1;
+DROP TABLE t2;
diff --git a/mysql-test/t/tablelock.test b/mysql-test/t/tablelock.test
index 5ac93f09ac1..0152a014855 100644
--- a/mysql-test/t/tablelock.test
+++ b/mysql-test/t/tablelock.test
@@ -62,3 +62,65 @@ drop table t1;
 unlock tables;
 
 # End of 5.0 tests
+
+--echo #
+--echo # Bug#19988193 ASSERTION `(*TABLES)->REGINFO.LOCK_TYPE >= TL_READ'
+--echo # FAILED IN LOCK_EXTERNAL
+--echo #
+
+CREATE TABLE t1(a INT);
+CREATE PROCEDURE p1() CREATE VIEW v1 AS SELECT * FROM t1;
+--echo
+--echo # Create trigger calling proc creating view, when view DOES NOT
+--echo # exist already
+CREATE TRIGGER trg_p1_t1 AFTER INSERT ON t1 FOR EACH ROW CALL p1();
+--echo
+--echo # Verify that it is possible to lock table
+LOCK TABLES t1 WRITE;
+UNLOCK TABLES;
+--echo
+--echo # Fails, as expected
+--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+INSERT INTO t1 VALUES (1);
+--echo
+--echo # Make sure v1 already exists
+CREATE VIEW v1 AS SELECT a+1 FROM t1;
+--echo
+--echo # Verify that it is possible to lock table
+LOCK TABLES t1 WRITE;
+UNLOCK TABLES;
+--echo
+--echo # Verify that we get the expected error when inserting into the table
+--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+INSERT INTO t1 VALUES (1);
+--echo
+--echo # Cleanup
+DROP TRIGGER trg_p1_t1;
+DROP PROCEDURE p1;
+DROP VIEW v1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Bug#21198646 ASSERTION FAILED: (*TABLES)->REGINFO.LOCK_TYPE >= TL_READ
+--echo # FILE LOCK.CC, LINE 356
+--echo #
+
+CREATE TABLE t2(a INT);
+
+--echo # Create procedure p1 invoking RENAME TABLE
+CREATE PROCEDURE p1() RENAME TABLE t2 TO t3;
+
+--echo # Create function f1 calling p1
+DELIMITER $;
+CREATE FUNCTION f1() RETURNS INT BEGIN CALL p1(); RETURN 1; END $
+DELIMITER ;$
+
+--echo # Invoke function f1 and verify that we get the expected error
+--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+SELECT f1();
+
+--echo # Cleanup
+DROP PROCEDURE p1;
+DROP FUNCTION f1;
+DROP TABLE t2;
\ No newline at end of file
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 5d8ea512e0f..f07ec6c5bae 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -4106,7 +4106,7 @@ sp_head::merge_table_list(THD *thd, TABLE_LIST *table, LEX *lex_for_tmp_check)
   }
 
   for (; table ; table= table->next_global)
-    if (!table->derived && !table->schema_table)
+    if (!table->derived && !table->schema_table && table->lock_type != TL_IGNORE)
     {
       /*
         Structure of key for the multi-set is "db\0table\0alias\0".