MDEV-25659 trigger name is empty after upgrade to 10.4bb-10.4-bar-MDEV-25659

Problem:

At some point, we made stored rountines fail at CREATE time
instead of execution time in case of this syntax:

   IF unknown_variable
     ...
   END IF

As a result, a trigger created before this change and contained an unknown
variable worked in a bad way after upgrade:
- It was displayed with an empty trigger name by SHOW CREATE TRIGGER
- It was displayed with an empty trigger name by INFORMATION_SCHEMA.TRIGGERS
- An attempt to DROP this trigger returned errors - nothing happened.
- DROP TABLE did not remove the .TRN file corresponding to this broken trigger.

Underlying code observations:

The old code assumed that the trigger name resides in the current lex:

  if(thd->lex->spname)
    m_trigger_name= &thd->lex->spname->m_name;

This is not always the case. Some SP statements (e.g. IF)
do the following in their beginning:

- create a separate local LEX
- set thd->lex to this new local LEX
- push the new local LEX to the stack in sp_head::m_lex

and the following at the end of the statement:

- pop the previous LEX from the stack sp_head::m_lex
- set thd->lex back to the popped value

So when the parse error happens inside e.g. IF statement, thd->lex->spname
is a NULL pointer, because thd->lex points to the local LEX (without SP name)
rather than the top level LEX (with SP name).

Fix:
- Adding a new method sp_head::find_spname_recursive()
  which walks inside the LEX stack sp_head::m_lex from
  the top (the newest, most local) to the bottom (the oldest),
  and finds the one which contains a non-zero spname pointer.

- Using the new method inside
  Deprecated_trigger_syntax_handler::handle_condition():
  First it still tests thd->lex->spname (like before this change),
  and uses it in case it is not empty.
  Otherwise (if thd->lex->spname is empty), it calls
  sp_head::find_spname_recursive() to find the LEX with a
  non-empty spname inside the LEX stack of the current sphead.

1 files changed, 295 insertions, 0 deletions

diff --git a/mysql-test/main/trigger-compat.result b/mysql-test/main/trigger-compat.result
index 387d4fb1489..656edc63a7d 100644
--- a/mysql-test/main/trigger-compat.result
+++ b/mysql-test/main/trigger-compat.result
@@ -142,3 +142,298 @@ DROP TRIGGER tr12;
 DROP TRIGGER tr11;
 DROP TABLE t1;
 DROP TABLE t2;
+#
+# MDEV-25659 trigger name is empty after upgrade to 10.4
+#
+# START: Total triggers 1, broken triggers 1, DROP TABLE
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+FLUSH TABLES;
+DELETE FROM t1 WHERE a=1;
+ERROR 42000: Trigger 'tr1' has an error in its body: 'Undeclared variable: unknown_variable'
+INSERT INTO t1 VALUES (2);
+ERROR 42000: Trigger 'tr1' has an error in its body: 'Undeclared variable: unknown_variable'
+SET time_zone='+00:00';
+SHOW TRIGGERS LIKE 't1';
+Trigger	tr1
+Event	DELETE
+Table	t1
+Statement	CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+  IF unknown_variable
+  THEN
+    INSERT INTO t2 VALUES (OLD.a);
+  END IF;
+END
+Timing	AFTER
+Created	2022-01-13 08:23:06.47
+sql_mode	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+Definer	
+character_set_client	utf8
+collation_connection	utf8_general_ci
+Database Collation	latin1_swedish_ci
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='tr1';
+TRIGGER_CATALOG	def
+TRIGGER_SCHEMA	test
+TRIGGER_NAME	tr1
+EVENT_MANIPULATION	DELETE
+EVENT_OBJECT_CATALOG	def
+EVENT_OBJECT_SCHEMA	test
+EVENT_OBJECT_TABLE	t1
+ACTION_ORDER	1
+ACTION_CONDITION	NULL
+ACTION_STATEMENT	CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+  IF unknown_variable
+  THEN
+    INSERT INTO t2 VALUES (OLD.a);
+  END IF;
+END
+ACTION_ORIENTATION	ROW
+ACTION_TIMING	AFTER
+ACTION_REFERENCE_OLD_TABLE	NULL
+ACTION_REFERENCE_NEW_TABLE	NULL
+ACTION_REFERENCE_OLD_ROW	OLD
+ACTION_REFERENCE_NEW_ROW	NEW
+CREATED	2022-01-13 08:23:06.47
+SQL_MODE	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+DEFINER	
+CHARACTER_SET_CLIENT	utf8
+COLLATION_CONNECTION	utf8_general_ci
+DATABASE_COLLATION	latin1_swedish_ci
+SET time_zone=DEFAULT;
+# Listing trigger files
+t1.TRG
+tr1.TRN
+# Listing trigger files done
+DROP TABLE t1;
+# Listing trigger files
+# Listing trigger files done
+# END: Total triggers 1, broken triggers 1, DROP TABLE
+# START: Total triggers 1, broken triggers 1, DROP TRIGGER
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+FLUSH TABLES;
+DELETE FROM t1 WHERE a=1;
+ERROR 42000: Trigger 'tr1' has an error in its body: 'Undeclared variable: unknown_variable'
+INSERT INTO t1 VALUES (2);
+ERROR 42000: Trigger 'tr1' has an error in its body: 'Undeclared variable: unknown_variable'
+SET time_zone='+00:00';
+SHOW TRIGGERS LIKE 't1';
+Trigger	tr1
+Event	DELETE
+Table	t1
+Statement	CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+  IF unknown_variable
+  THEN
+    INSERT INTO t2 VALUES (OLD.a);
+  END IF;
+END
+Timing	AFTER
+Created	2022-01-13 08:23:06.47
+sql_mode	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+Definer	
+character_set_client	utf8
+collation_connection	utf8_general_ci
+Database Collation	latin1_swedish_ci
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='tr1';
+TRIGGER_CATALOG	def
+TRIGGER_SCHEMA	test
+TRIGGER_NAME	tr1
+EVENT_MANIPULATION	DELETE
+EVENT_OBJECT_CATALOG	def
+EVENT_OBJECT_SCHEMA	test
+EVENT_OBJECT_TABLE	t1
+ACTION_ORDER	1
+ACTION_CONDITION	NULL
+ACTION_STATEMENT	CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+  IF unknown_variable
+  THEN
+    INSERT INTO t2 VALUES (OLD.a);
+  END IF;
+END
+ACTION_ORIENTATION	ROW
+ACTION_TIMING	AFTER
+ACTION_REFERENCE_OLD_TABLE	NULL
+ACTION_REFERENCE_NEW_TABLE	NULL
+ACTION_REFERENCE_OLD_ROW	OLD
+ACTION_REFERENCE_NEW_ROW	NEW
+CREATED	2022-01-13 08:23:06.47
+SQL_MODE	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+DEFINER	
+CHARACTER_SET_CLIENT	utf8
+COLLATION_CONNECTION	utf8_general_ci
+DATABASE_COLLATION	latin1_swedish_ci
+SET time_zone=DEFAULT;
+# Listing trigger files
+t1.TRG
+tr1.TRN
+# Listing trigger files done
+DROP TRIGGER tr1;
+# Listing trigger files
+# Listing trigger files done
+DROP TABLE t1;
+# END: Total triggers 1, broken triggers 1, DROP TRIGGER
+# START: Total triggers 2, broken triggers 1, DROP TABLE
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+FLUSH TABLES;
+DELETE FROM t1 WHERE a=1;
+ERROR 42000: Trigger 'tr1' has an error in its body: 'Undeclared variable: unknown_variable'
+INSERT INTO t1 VALUES (2);
+ERROR 42000: Trigger 'tr1' has an error in its body: 'Undeclared variable: unknown_variable'
+SET time_zone='+00:00';
+SHOW TRIGGERS LIKE 't1';
+Trigger	tr2
+Event	INSERT
+Table	t1
+Statement	INSERT INTO t2 VALUES (NEW.a+100)
+Timing	AFTER
+Created	2022-01-13 10:01:48.74
+sql_mode	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+Definer	root@localhost
+character_set_client	utf8
+collation_connection	utf8_general_ci
+Database Collation	latin1_swedish_ci
+Trigger	tr1
+Event	DELETE
+Table	t1
+Statement	CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+  IF unknown_variable
+  THEN
+    INSERT INTO t2 VALUES (OLD.a);
+  END IF;
+END
+Timing	AFTER
+Created	2022-01-13 10:01:48.73
+sql_mode	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+Definer	
+character_set_client	utf8
+collation_connection	utf8_general_ci
+Database Collation	latin1_swedish_ci
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='tr1';
+TRIGGER_CATALOG	def
+TRIGGER_SCHEMA	test
+TRIGGER_NAME	tr1
+EVENT_MANIPULATION	DELETE
+EVENT_OBJECT_CATALOG	def
+EVENT_OBJECT_SCHEMA	test
+EVENT_OBJECT_TABLE	t1
+ACTION_ORDER	1
+ACTION_CONDITION	NULL
+ACTION_STATEMENT	CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+  IF unknown_variable
+  THEN
+    INSERT INTO t2 VALUES (OLD.a);
+  END IF;
+END
+ACTION_ORIENTATION	ROW
+ACTION_TIMING	AFTER
+ACTION_REFERENCE_OLD_TABLE	NULL
+ACTION_REFERENCE_NEW_TABLE	NULL
+ACTION_REFERENCE_OLD_ROW	OLD
+ACTION_REFERENCE_NEW_ROW	NEW
+CREATED	2022-01-13 10:01:48.73
+SQL_MODE	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+DEFINER	
+CHARACTER_SET_CLIENT	utf8
+COLLATION_CONNECTION	utf8_general_ci
+DATABASE_COLLATION	latin1_swedish_ci
+SET time_zone=DEFAULT;
+# Listing trigger files
+t1.TRG
+tr1.TRN
+tr2.TRN
+# Listing trigger files done
+DROP TABLE t1;
+# Listing trigger files
+# Listing trigger files done
+# END: Total triggers 2, broken triggers 1, using DROP TABLE
+# START: Total triggers 2, broken triggers 1, DROP TRIGGER
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+FLUSH TABLES;
+DELETE FROM t1 WHERE a=1;
+ERROR 42000: Trigger 'tr1' has an error in its body: 'Undeclared variable: unknown_variable'
+INSERT INTO t1 VALUES (2);
+ERROR 42000: Trigger 'tr1' has an error in its body: 'Undeclared variable: unknown_variable'
+SET time_zone='+00:00';
+SHOW TRIGGERS LIKE 't1';
+Trigger	tr2
+Event	INSERT
+Table	t1
+Statement	INSERT INTO t2 VALUES (NEW.a+100)
+Timing	AFTER
+Created	2022-01-13 10:01:48.74
+sql_mode	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+Definer	root@localhost
+character_set_client	utf8
+collation_connection	utf8_general_ci
+Database Collation	latin1_swedish_ci
+Trigger	tr1
+Event	DELETE
+Table	t1
+Statement	CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+  IF unknown_variable
+  THEN
+    INSERT INTO t2 VALUES (OLD.a);
+  END IF;
+END
+Timing	AFTER
+Created	2022-01-13 10:01:48.73
+sql_mode	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+Definer	
+character_set_client	utf8
+collation_connection	utf8_general_ci
+Database Collation	latin1_swedish_ci
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='tr1';
+TRIGGER_CATALOG	def
+TRIGGER_SCHEMA	test
+TRIGGER_NAME	tr1
+EVENT_MANIPULATION	DELETE
+EVENT_OBJECT_CATALOG	def
+EVENT_OBJECT_SCHEMA	test
+EVENT_OBJECT_TABLE	t1
+ACTION_ORDER	1
+ACTION_CONDITION	NULL
+ACTION_STATEMENT	CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+  IF unknown_variable
+  THEN
+    INSERT INTO t2 VALUES (OLD.a);
+  END IF;
+END
+ACTION_ORIENTATION	ROW
+ACTION_TIMING	AFTER
+ACTION_REFERENCE_OLD_TABLE	NULL
+ACTION_REFERENCE_NEW_TABLE	NULL
+ACTION_REFERENCE_OLD_ROW	OLD
+ACTION_REFERENCE_NEW_ROW	NEW
+CREATED	2022-01-13 10:01:48.73
+SQL_MODE	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+DEFINER	
+CHARACTER_SET_CLIENT	utf8
+COLLATION_CONNECTION	utf8_general_ci
+DATABASE_COLLATION	latin1_swedish_ci
+SET time_zone=DEFAULT;
+# Listing trigger files
+t1.TRG
+tr1.TRN
+tr2.TRN
+# Listing trigger files done
+DROP TRIGGER tr1;
+# Listing trigger files
+t1.TRG
+tr2.TRN
+# Listing trigger files done
+INSERT INTO t1 VALUES (100);
+ERROR 42S02: Table 'test.t2' doesn't exist
+DROP TABLE t1;
+# Listing trigger files
+# Listing trigger files done
+# END: Total triggers 2, broken triggers 1, using DROP TRIGGER