WL#6406: Stacked diagnostic areas

Affects: Server-5.7   —   Status: Complete

Implement support for stacked diagnostic areas according to the SQL-standard.

This includes updating current implementation of HANDLER statements, 
RESIGNAL and stored program execution.

Currently every connection has one diagnostic area for storing completion
and exception condition information. According to the SQL standard each
connection should instead have a stack of diagnostic areas.

The diagnostic area on top of the stack is called the first diagnostic area.
The diagnostic area just below is the second diagnostic area. There can be
more than just two diagnostic areas, but only the top two are accessible.

Two operations are defined for the diagnostics area stack: Push and pop.

Pushing a diagnostic area makes it the new first diagnostic area. The
previous first diagnostic area becomes the second diagnostic area.
The new first diagnostic area initially contains a copy of the contents
of the second diagnostic area (i.e. the first diagnostic area before the push).

Popping a diagnostic area, destroys it and makes the second diagnostic 
area the new first diagnostic area. Note that pop does not by definition
merge the contents of the first and second diagnostics area - all conditions in
the first area are destroyed.

Normally all interactions happen with the first diagnostic area. This is
where new conditions appear. The contents of the first diagnostic area
can be queried with GET [CURRENT] DIAGNOSTICS.

This worklog will add support for GET STACKED DIAGNOSTICS which queries the 
contents of the second diagnostic area in a similar manner. There is no way to 
access diagnostic areas below the second (this is according to the SQL 

Pushing and popping of diagnostic areas happens in the following 3 cases:

1) Execution of a stored program
Push before executing a stored program and pop afterwards.
Note that there can be more than one diagnostic area to pop if the
stored program ends while handlers are executing (due to an exception
for which there are no appropriate handlers or due to RETURN in the handler).
Any warnings or errors during execution is then added to the current
diagnostics area. Exception: For triggers, only errors are added.

2) Execution of SP handlers
Push before executing a SP handler and pop if the handler exits successfully.
Handler activation and execution will be like this:
- A condition is raised during SP execution. It is entered into the
  first DA - let's call this the SP DA.
- Handler is activated and a new DA is pushed. Let's call this new
  DA the handler DA. Stack is now handler DA (top) and SP DA (below)
  and the both contain the same conditions.
- Handler executes. Normal clearing rules applies to the first
  (handler) DA. Also as normal, new conditions that are raised
  are entered into the first (handler) DA.
- If handler ends successfully: Pop DA. This means that SP DA is
  now again the first DA. Since the handler executed successfully,
  all conditions that were present when the handler was activated,
  are cleared. This in effect means emptying the SP DA since
  it was read-only during the handler. Then we examine the contents
  of the handler DA as it is at the end of the handler. Any conditions
  that were added during the handler execution, are copied to the
  SP DA. So at the end, we have just SP DA, and it contains only the
  conditions present in the handler DA when the handler ended that
  were added during handler execution.
- If handler ends with exception: Add the exception to the first (handler) DA
  and activate an appropriate handler (if any).

3) Execution of RESIGNAL
Pop at the start of RESIGNAL and push at the end of RESIGNAL.
Note that the push is not part of the the SQL standard. However, if the
handler continues to execute after RESIGNAL, we will get out of sync with
respect to diagnostic areas unless we push at the end. Continuing after
RESIGNAL can happen if we RESIGNAL a warning or if we RESIGNAL an error
for which there's an outer CONTINUE handler that is appropriate.
In other cases (RESIGNAL of error which is not handled and RESIGNAL of
error which is handled by EXIT handler), we will pop the DA pushed at the
end of RESIGNAL when we exit the handler scope (as normal).
By doing pop+push rather than not doing either, we make sure that a 
condition modified by RESIGNAL (if any) is present in both the first and 
second diagnostics area when RESIGNAL ends. The diagnostics area pushed will
be popped when the handler exits (as normal).

We have discussed introducing a limit to the number of diagnostic areas in the 
diagnostic areas stack. The SQL standard (2008) says this:
  "The maximum number of diagnostics areas in a diagnostics area
  stack is implementation-dependent."
and regarding pushing diagnostics area:
  "If the number of diagnostics areas in DAS is equal to the implementation-
  dependent maximum number of diagnostics areas per diagnostics area stack, 
  then an exception condition is raised: diagnostics exception — maximum number 
  of stacked diagnostics areas exceeded."
However, we use push/pop a bit more than what is determined by the SQL standard
(e.g. we push before executing a stored program). This means that the number
of diagnostic areas (and therefore the meaning of the limit) would not be
identical to that described in the SQL standard. It also means that it will
be hard to explain properly what such a limit in fact means. The reason for
introducing a limit would be to restrict memory usage. However, we already
have "max_sp_recursion_depth" and "thread_stack" which addresses this issue.
Therefore we don't plan to introduce any limit on number of diagnostics areas
in the stack.

Functional requirements

1. Syntax for GET DIAGNOSTICS is extended. GET [CURRENT] DIAGNOSTICS becomes

2. The behavior of GET [CURRENT] DIAGNOSTICS is unchanged.

3. GET STACKED DIAGNOSTICS will retrieve information from the second 
   diagnostic area in the same manner that GET [CURRENT] DIAGNOSTICS
   currently retrieves information from the first diagnostics area.
   The same syntax applies.

4. Issuing GET STACKED DIAGNOSTICS outside of a handler, will give the error 
   "GET STACKED DIAGNOSTICS when handler not active"
   (matching the current "RESIGNAL when handler not active" error).

5. Right after a new diagnostics area has been pushed, the contents of
   the first and second diagnostics areas should be the same. This means
   that the result of matching GET [CURRENT] DIAGNOSTICS and GET STACKED 
   DIAGNOSTICS queries should be the same.

6. The contents of the second diagnostics area should not change during
   SP handler execution. This means that a given GET STACKED DIAGNOSTICS
   query should give the same result at any point during SP handler
   execution, regardless of any changes to the first diagnostics area.
   An exception to this rule is if RESIGNAL is used to modify a condition
   and the handler continues to execute. This can happen with RESIGNAL of
   a warning or with RESIGNAL of an error for which there's an outer
   CONTINUE handler.

7. If a SP handler exits with RESIGNAL, the diagnostic area should at this
   point contain all the conditions it had when the handler was activated +
   the condition added by RESIGNAL.