MySQL Internals Manual  /  ...  /  Case Expressions Case Expressions

For CASE expressions, temporary variables are generated automatically. Like CURSOR, there are some constraints that prevent treating these special local variables like regular local variables.

The difficulty with CASE is that the real type of the expression is only known when the case statement is executed, so that allocating space in a statically computed TABLE is not practical. For example, CASE (SELECT col1 FROM t1 WHERE ...) is a case expression that involves a single row subselect. During parsing, the table might not even exists, so evaluating the type of col1 is impossible. Creation of the table can be delayed until execution, with statements like CREATE TEMPORARY TABLE.

Instead, a array of Item * is used, implemented by sp_rcontext::m_case_expr_holders. The size of the array is static (it's the total number of cases), but the content of each element is dynamic (to account for the type of the case expression).


Note the wording used here: static means something that can be evaluated when compiling the code, in the parser, whereas dynamic means something that can be evaluated only when interpreting the code, during runtime. Of course, from a C++ coding point of view, everything is dynamic.

Inside a CASE statement, temporary local variables in a Stored Program are represented by the dedicated C++ class Item_case_expr. The class Item_case_expr is also a proxy, similar in nature to Item_splocal, and delegates to sp_rcontext for accessing the underlying case expression value. The coupling between Item_case_expr and sp_rcontext is based on Item_case_expr::m_case_expr_id, which is the case expression index in the symbol table (see sp_pcontext).

User Comments
Sign Up Login You must be logged in to post a comment.