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, 17 insertions, 0 deletions

diff --git a/sql/sp_head.h b/sql/sp_head.h
index 500446c5e16..69232da10c7 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -612,6 +612,23 @@ public:
     DBUG_RETURN(false);
   }
 
+  /**
+    Iterate through the LEX stack from the top (the newest) to the bottom
+    (the oldest) and find the one that contains a non-zero spname.
+    @returns - the address of spname, or NULL of no spname found.
+  */
+  const sp_name *find_spname_recursive()
+  {
+    uint count= m_lex.elements;
+    for (uint i= 0; i < count; i++)
+    {
+      const LEX *tmp= m_lex.elem(count - i - 1);
+      if (tmp->spname)
+        return tmp->spname;
+    }
+    return NULL;
+  }
+
   /// Put the instruction on the backpatch list, associated with the label.
   int
   push_backpatch(THD *thd, sp_instr *, sp_label *);