MDEV-10578 sql_mode=ORACLE: SP control functions SQLCODE, SQLERRM

9 files changed, 178 insertions, 106 deletions

diff --git a/sql/item_func.cc b/sql/item_func.cc
index ad3a863fa57..e981722a123 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -6647,6 +6647,19 @@ longlong Item_func_oracle_sql_rowcount::val_int()
 }
 
 
+longlong Item_func_sqlcode::val_int()
+{
+  DBUG_ASSERT(fixed);
+  DBUG_ASSERT(!null_value);
+  Diagnostics_area::Sql_condition_iterator it=
+    current_thd->get_stmt_da()->sql_conditions();
+  const Sql_condition *err;
+  if ((err= it++))
+    return err->get_sql_errno();
+  return 0;
+}
+
+
 /**
   @brief Checks if requested access to function can be granted to user.
     If function isn't found yet, it searches function first.
diff --git a/sql/item_func.h b/sql/item_func.h
index 28cdb78f6dd..88fa78cd4a4 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -2688,6 +2688,30 @@ public:
 };
 
 
+class Item_func_sqlcode: public Item_int_func
+{
+public:
+  Item_func_sqlcode(THD *thd): Item_int_func(thd) { }
+  longlong val_int();
+  const char *func_name() const { return "SQLCODE"; }
+  void print(String *str, enum_query_type query_type)
+  {
+    str->append(func_name());
+  }
+  bool check_vcol_func_processor(void *arg)
+  {
+    return mark_unsupported_function(func_name(), "()", arg, VCOL_IMPOSSIBLE);
+  }
+  void fix_length_and_dec()
+  {
+    maybe_null= null_value= false;
+    max_length= 11;
+  }
+  Item *get_copy(THD *thd, MEM_ROOT *mem_root)
+  { return get_item_copy<Item_func_sqlcode>(thd, mem_root, this); }
+};
+
+
 void uuid_short_init();
 
 class Item_func_uuid_short :public Item_int_func
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 3f2983149e0..996f1ac15da 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -2289,6 +2289,25 @@ String *Item_func_database::val_str(String *str)
 }
 
 
+String *Item_func_sqlerrm::val_str(String *str)
+{
+  DBUG_ASSERT(fixed);
+  DBUG_ASSERT(!null_value);
+  Diagnostics_area::Sql_condition_iterator it=
+    current_thd->get_stmt_da()->sql_conditions();
+  const Sql_condition *err;
+  if ((err= it++))
+  {
+    str->copy(err->get_message_text(), err->get_message_octet_length(),
+              system_charset_info);
+    return str;
+  }
+  str->copy(C_STRING_WITH_LEN("normal, successful completition"),
+            system_charset_info);
+  return str;
+}
+
+
 /**
   @note USER() is replicated correctly if binlog_format=ROW or (as of
   BUG#28086) binlog_format=MIXED, but is incorrectly replicated to ''
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index d3df76b200a..fb7d79d9e0d 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -719,6 +719,27 @@ public:
 };
 
 
+class Item_func_sqlerrm :public Item_func_sysconst
+{
+public:
+  Item_func_sqlerrm(THD *thd): Item_func_sysconst(thd) {}
+  String *val_str(String *);
+  const char *func_name() const { return "SQLERRM"; }
+  const char *fully_qualified_func_name() const { return "SQLERRM"; }
+  void print(String *str, enum_query_type query_type)
+  {
+    str->append(func_name());
+  }
+  void fix_length_and_dec()
+  {
+    max_length= 512 * system_charset_info->mbmaxlen;
+    null_value= maybe_null= false;
+  }
+  Item *get_copy(THD *thd, MEM_ROOT *mem_root)
+  { return get_item_copy<Item_func_sqlerrm>(thd, mem_root, this); }
+};
+
+
 class Item_func_user :public Item_func_sysconst
 {
 protected:
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index d02749db030..05ef6f02063 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -3398,8 +3398,21 @@ sp_instr_freturn::exec_core(THD *thd, uint *nextp)
     That means, Diagnostics Area should be clean before its execution.
   */
 
-  Diagnostics_area *da= thd->get_stmt_da();
-  da->clear_warning_info(da->warning_info_id());
+  if (!(thd->variables.sql_mode & MODE_ORACLE))
+  {
+    /*
+      Don't clean warnings in ORACLE mode,
+      as they are needed for SQLCODE and SQLERRM:
+        BEGIN
+          SELECT a INTO a FROM t1;
+          RETURN 'No exception ' || SQLCODE || ' ' || SQLERRM;
+        EXCEPTION WHEN NO_DATA_FOUND THEN
+          RETURN 'Exception ' || SQLCODE || ' ' || SQLERRM;
+        END;
+    */
+    Diagnostics_area *da= thd->get_stmt_da();
+    da->clear_warning_info(da->warning_info_id());
+  }
 
   /*
     Change <next instruction pointer>, so that this will be the last
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 585f9b19370..08e1920a488 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -5951,6 +5951,67 @@ bool LEX::set_variable(struct sys_var_with_base *variable, Item *item)
 }
 
 
+Item *LEX::create_item_ident_nosp(THD *thd, LEX_STRING name)
+{
+  if (current_select->parsing_place != IN_HAVING ||
+      current_select->get_in_sum_expr() > 0)
+    return new (thd->mem_root) Item_field(thd, current_context(),
+                                          NullS, NullS, name.str);
+
+  return new (thd->mem_root) Item_ref(thd, current_context(),
+                                      NullS, NullS, name.str);
+}
+
+
+Item *LEX::create_item_ident_sp(THD *thd, LEX_STRING name,
+                                uint start_in_q,
+                                uint length_in_q)
+{
+  sp_variable *spv;
+  DBUG_ASSERT(spcont);
+  if ((spv= spcont->find_variable(name, false)))
+  {
+    /* We're compiling a stored procedure and found a variable */
+    if (!parsing_options.allows_variable)
+    {
+      my_error(ER_VIEW_SELECT_VARIABLE, MYF(0));
+      return NULL;
+    }
+
+    Item_splocal *splocal;
+    splocal= new (thd->mem_root) Item_splocal(thd, name,
+                                              spv->offset, spv->sql_type(),
+                                              start_in_q, length_in_q);
+    if (splocal == NULL)
+      return NULL;
+#ifndef DBUG_OFF
+    splocal->m_sp= sphead;
+#endif
+    safe_to_cache_query= 0;
+    return splocal;
+  }
+
+  if (thd->variables.sql_mode & MODE_ORACLE)
+  {
+    if (!my_strcasecmp(system_charset_info, name.str, "SQLCODE"))
+      return new (thd->mem_root) Item_func_sqlcode(thd);
+    if (!my_strcasecmp(system_charset_info, name.str, "SQLERRM"))
+      return new (thd->mem_root) Item_func_sqlerrm(thd);
+  }
+  return create_item_ident_nosp(thd, name);
+}
+
+
+Item *LEX::create_item_ident_sp(THD *thd, LEX_STRING name,
+                                const char *start_in_q,
+                                const char *end_in_q)
+{
+  DBUG_ASSERT(sphead);
+  return create_item_ident_sp(thd, name, start_in_q - sphead->m_tmp_query,
+                                         end_in_q - start_in_q);
+}
+
+
 #ifdef MYSQL_SERVER
 uint binlog_unsafe_map[256];
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index b4bf45116c4..ee4c4687ef3 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -3118,6 +3118,21 @@ public:
                                        const char *start_in_q,
                                        const char *end_in_q);
 
+  Item *create_item_ident_nosp(THD *thd, LEX_STRING name);
+  Item *create_item_ident_sp(THD *thd, LEX_STRING name,
+                             uint start_in_q,
+                             uint length_in_q);
+  Item *create_item_ident_sp(THD *thd, LEX_STRING name,
+                             const char *start_in_q,
+                             const char *end_in_q);
+  Item *create_item_ident(THD *thd, LEX_STRING name,
+                          const char *start_in_q,
+                          const char *end_in_q)
+  {
+    return sphead ?
+           create_item_ident_sp(thd, name, start_in_q, end_in_q) :
+           create_item_ident_nosp(thd, name);
+  }
   bool is_trigger_new_or_old_reference(const LEX_STRING name);
 
   Item *create_and_link_Item_trigger_field(THD *thd, const char *name,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index c21d91004f2..7e134338d62 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -13745,46 +13745,11 @@ order_ident:
 simple_ident:
           ident
           {
-            LEX *lex= thd->lex;
             Lex_input_stream *lip= YYLIP;
-            sp_variable *spv;
-            sp_pcontext *spc = lex->spcont;
-            if (spc && (spv = spc->find_variable($1, false)))
-            {
-              /* We're compiling a stored procedure and found a variable */
-              if (! lex->parsing_options.allows_variable)
-                my_yyabort_error((ER_VIEW_SELECT_VARIABLE, MYF(0)));
-
-              Item_splocal *splocal;
-              splocal= new (thd->mem_root)
-                         Item_splocal(thd, $1, spv->offset, spv->sql_type(),
-                                      lip->get_tok_start_prev() - lex->sphead->m_tmp_query,
-                                      lip->get_tok_end() - lip->get_tok_start_prev());
-              if (splocal == NULL)
-                MYSQL_YYABORT;
-#ifndef DBUG_OFF
-              splocal->m_sp= lex->sphead;
-#endif
-              $$= splocal;
-              lex->safe_to_cache_query=0;
-            }
-            else
-            {
-              SELECT_LEX *sel=Select;
-              if ((sel->parsing_place != IN_HAVING) ||
-                  (sel->get_in_sum_expr() > 0))
-              {
-                $$= new (thd->mem_root) Item_field(thd, Lex->current_context(),
-                                                   NullS, NullS, $1.str);
-              }
-              else
-              {
-                $$= new (thd->mem_root) Item_ref(thd, Lex->current_context(),
-                                                 NullS, NullS, $1.str);
-              }
-              if ($$ == NULL)
-                MYSQL_YYABORT;
-            }
+            if (!($$= Lex->create_item_ident(thd, $1,
+                                             lip->get_tok_start_prev(),
+                                             lip->get_tok_end())))
+              MYSQL_YYABORT;
           }
         | simple_ident_q { $$= $1; }
         ;
@@ -13792,19 +13757,7 @@ simple_ident:
 simple_ident_nospvar:
           ident
           {
-            SELECT_LEX *sel=Select;
-            if ((sel->parsing_place != IN_HAVING) ||
-                (sel->get_in_sum_expr() > 0))
-            {
-              $$= new (thd->mem_root) Item_field(thd, Lex->current_context(),
-                                                 NullS, NullS, $1.str);
-            }
-            else
-            {
-              $$= new (thd->mem_root) Item_ref(thd, Lex->current_context(),
-                                               NullS, NullS, $1.str);
-            }
-            if ($$ == NULL)
+            if (!($$= Lex->create_item_ident_nosp(thd, $1)))
               MYSQL_YYABORT;
           }
         | simple_ident_q { $$= $1; }
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 3dac8c5a669..9ddadee5ffd 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -13674,46 +13674,11 @@ order_ident:
 simple_ident:
           ident
           {
-            LEX *lex= thd->lex;
             Lex_input_stream *lip= YYLIP;
-            sp_variable *spv;
-            sp_pcontext *spc = lex->spcont;
-            if (spc && (spv = spc->find_variable($1, false)))
-            {
-              /* We're compiling a stored procedure and found a variable */
-              if (! lex->parsing_options.allows_variable)
-                my_yyabort_error((ER_VIEW_SELECT_VARIABLE, MYF(0)));
-
-              Item_splocal *splocal;
-              splocal= new (thd->mem_root)
-                         Item_splocal(thd, $1, spv->offset, spv->sql_type(),
-                                      lip->get_tok_start_prev() - lex->sphead->m_tmp_query,
-                                      lip->get_tok_end() - lip->get_tok_start_prev());
-              if (splocal == NULL)
-                MYSQL_YYABORT;
-#ifndef DBUG_OFF
-              splocal->m_sp= lex->sphead;
-#endif
-              $$= splocal;
-              lex->safe_to_cache_query=0;
-            }
-            else
-            {
-              SELECT_LEX *sel=Select;
-              if ((sel->parsing_place != IN_HAVING) ||
-                  (sel->get_in_sum_expr() > 0))
-              {
-                $$= new (thd->mem_root) Item_field(thd, Lex->current_context(),
-                                                   NullS, NullS, $1.str);
-              }
-              else
-              {
-                $$= new (thd->mem_root) Item_ref(thd, Lex->current_context(),
-                                                 NullS, NullS, $1.str);
-              }
-              if ($$ == NULL)
-                MYSQL_YYABORT;
-            }
+            if (!($$= Lex->create_item_ident(thd, $1,
+                                             lip->get_tok_start_prev(),
+                                             lip->get_tok_end())))
+              MYSQL_YYABORT;
           }
         | simple_ident_q { $$= $1; }
         ;
@@ -13721,19 +13686,7 @@ simple_ident:
 simple_ident_nospvar:
           ident
           {
-            SELECT_LEX *sel=Select;
-            if ((sel->parsing_place != IN_HAVING) ||
-                (sel->get_in_sum_expr() > 0))
-            {
-              $$= new (thd->mem_root) Item_field(thd, Lex->current_context(),
-                                                 NullS, NullS, $1.str);
-            }
-            else
-            {
-              $$= new (thd->mem_root) Item_ref(thd, Lex->current_context(),
-                                               NullS, NullS, $1.str);
-            }
-            if ($$ == NULL)
+            if (!($$= Lex->create_item_ident_nosp(thd, $1)))
               MYSQL_YYABORT;
           }
         | simple_ident_q { $$= $1; }