MDEV-12533 sql_mode=ORACLE: Add support for database qualified sequence names in NEXTVAL and CURRVAL

6 files changed, 150 insertions, 164 deletions

diff --git a/mysql-test/suite/compat/oracle/r/sequence.result b/mysql-test/suite/compat/oracle/r/sequence.result
index e3bf9d4daee..dbbabc36683 100644
--- a/mysql-test/suite/compat/oracle/r/sequence.result
+++ b/mysql-test/suite/compat/oracle/r/sequence.result
@@ -58,3 +58,20 @@ View	Create View	character_set_client	collation_connection
 v1	CREATE VIEW "v1" AS select lastval("test"."s1") AS "a"	latin1	latin1_swedish_ci
 DROP VIEW v1;
 DROP SEQUENCE s1;
+#
+# MDEV-12533 sql_mode=ORACLE: Add support for database qualified sequence names in NEXTVAL and CURRVAL
+#
+CREATE SEQUENCE s1;
+SELECT test.s1.nextval;
+test.s1.nextval
+1
+SELECT test.s1.currval;
+test.s1.currval
+1
+SELECT .s1.nextval;
+.s1.nextval
+2
+SELECT .s1.currval;
+.s1.currval
+2
+DROP SEQUENCE s1;
diff --git a/mysql-test/suite/compat/oracle/t/sequence.test b/mysql-test/suite/compat/oracle/t/sequence.test
index bc861c03014..719c4bcd45b 100644
--- a/mysql-test/suite/compat/oracle/t/sequence.test
+++ b/mysql-test/suite/compat/oracle/t/sequence.test
@@ -31,3 +31,13 @@ SELECT * FROM v1;
 SHOW CREATE VIEW v1;
 DROP VIEW v1;
 DROP SEQUENCE s1;
+
+--echo #
+--echo # MDEV-12533 sql_mode=ORACLE: Add support for database qualified sequence names in NEXTVAL and CURRVAL
+--echo #
+CREATE SEQUENCE s1;
+SELECT test.s1.nextval;
+SELECT test.s1.currval;
+SELECT .s1.nextval;
+SELECT .s1.currval;
+DROP SEQUENCE s1;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index e474a2cb313..aad5e2c0e51 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -6387,6 +6387,53 @@ my_var *LEX::create_outvar(THD *thd,
 }
 
 
+Item *LEX::create_item_func_nextval(THD *thd, Table_ident *table_ident)
+{
+  TABLE_LIST *table;
+  if (!(table= current_select->add_table_to_list(thd, table_ident, 0,
+                                                 TL_OPTION_SEQUENCE,
+                                                 TL_WRITE_ALLOW_WRITE,
+                                                 MDL_SHARED_WRITE)))
+    return NULL;
+  return new (thd->mem_root) Item_func_nextval(thd, table);
+
+}
+
+
+Item *LEX::create_item_func_lastval(THD *thd, Table_ident *table_ident)
+{
+  TABLE_LIST *table;
+  if (!(table= current_select->add_table_to_list(thd, table_ident, 0,
+                                                 TL_OPTION_SEQUENCE,
+                                                 TL_READ,
+                                                 MDL_SHARED_READ)))
+    return NULL;
+  return new (thd->mem_root) Item_func_lastval(thd, table);
+}
+
+
+Item *LEX::create_item_func_nextval(THD *thd,
+                                    const LEX_STRING &db,
+                                    const LEX_STRING &name)
+{
+  Table_ident *table_ident;
+  if (!(table_ident= new (thd->mem_root) Table_ident(thd, db, name, false)))
+    return NULL;
+  return create_item_func_nextval(thd, table_ident);
+}
+
+
+Item *LEX::create_item_func_lastval(THD *thd,
+                                    const LEX_STRING &db,
+                                    const LEX_STRING &name)
+{
+  Table_ident *table_ident;
+  if (!(table_ident= new (thd->mem_root) Table_ident(thd, db, name, false)))
+    return NULL;
+  return create_item_func_lastval(thd, table_ident);
+}
+
+
 Item *LEX::create_item_ident(THD *thd,
                              const LEX_STRING &a,
                              const LEX_STRING &b,
@@ -6401,37 +6448,50 @@ Item *LEX::create_item_ident(THD *thd,
     if (!my_strnncoll(system_charset_info,
                       (const uchar *) b.str, 7,
                       (const uchar *) "NEXTVAL", 7))
-    {
-      TABLE_LIST *table;
-      Table_ident *table_ident;
-      if (!(table_ident= new (thd->mem_root) Table_ident(a)) ||
-          !(table= current_select->add_table_to_list(thd, table_ident, 0,
-                                                     TL_OPTION_SEQUENCE,
-                                                     TL_WRITE_ALLOW_WRITE,
-                                                     MDL_SHARED_WRITE)))
-        return NULL;
-      return new (thd->mem_root) Item_func_nextval(thd, table);
-    }
+      return create_item_func_nextval(thd, null_lex_str, a);
     else if (!my_strnncoll(system_charset_info,
                           (const uchar *) b.str, 7,
                           (const uchar *) "CURRVAL", 7))
-    {
-      TABLE_LIST *table;
-      Table_ident *table_ident;
-      if (!(table_ident= new (thd->mem_root) Table_ident(a)) ||
-          !(table= current_select->add_table_to_list(thd, table_ident, 0,
-                                                     TL_OPTION_SEQUENCE,
-                                                     TL_READ,
-                                                     MDL_SHARED_READ)))
-        return NULL;
-      return new (thd->mem_root) Item_func_lastval(thd, table);
-    }
+      return create_item_func_lastval(thd, null_lex_str, a);
   }
 
   return create_item_ident_nospvar(thd, a, b);
 }
 
 
+Item *LEX::create_item_ident(THD *thd,
+                             const LEX_STRING &a,
+                             const LEX_STRING &b,
+                             const LEX_STRING &c)
+{
+  const char *schema= (thd->client_capabilities & CLIENT_NO_SCHEMA ?
+                       NullS : a.str);
+
+  if ((thd->variables.sql_mode & MODE_ORACLE) && c.length == 7)
+  {
+    if (!my_strnncoll(system_charset_info,
+                      (const uchar *) c.str, 7,
+                      (const uchar *) "NEXTVAL", 7))
+      return create_item_func_nextval(thd, a, b);
+    else if (!my_strnncoll(system_charset_info,
+                          (const uchar *) c.str, 7,
+                          (const uchar *) "CURRVAL", 7))
+      return create_item_func_lastval(thd, a, b);
+  }
+
+  if (current_select->no_table_names_allowed)
+  {
+    my_error(ER_TABLENAME_NOT_ALLOWED_HERE, MYF(0), b.str, thd->where);
+    return NULL;
+  }
+  if (current_select->parsing_place != IN_HAVING ||
+      current_select->get_in_sum_expr() > 0)
+    return new (thd->mem_root) Item_field(thd, current_context(),
+                                          schema, b.str, c.str);
+  return new (thd->mem_root) Item_ref(thd, current_context(),
+                                      schema, b.str, c.str);
+}
+
 
 Item *LEX::create_item_limit(THD *thd,
                              const LEX_STRING &a,
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index e131241ea52..c1c751ecbbc 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -3239,6 +3239,35 @@ public:
                           const LEX_STRING &a,
                           const LEX_STRING &b,
                           uint pos_in_q, uint length_in_q);
+
+  /*
+    Create an item from its qualified name.
+    Depending on context, it can be a table field, a table field reference,
+    or a sequence NEXTVAL and CURRVAL.
+      @param thd         - THD, for mem_root
+      @param a           - the first name
+      @param b           - the second name
+      @param c           - the third name
+      @retval            - NULL on error, or a pointer to a new Item.
+  */
+  Item *create_item_ident(THD *thd,
+                          const LEX_STRING &a,
+                          const LEX_STRING &b,
+                          const LEX_STRING &c);
+
+  /*
+    Create an item for "NEXT VALUE FOR sequence_name"
+  */
+  Item *create_item_func_nextval(THD *thd, Table_ident *ident);
+  Item *create_item_func_nextval(THD *thd, const LEX_STRING &db,
+                                           const LEX_STRING &name);
+  /*
+    Create an item for "PREVIOUS VALUE FOR sequence_name"
+  */
+  Item *create_item_func_lastval(THD *thd, Table_ident *ident);
+  Item *create_item_func_lastval(THD *thd, const LEX_STRING &db,
+                                           const LEX_STRING &name);
+  
   /*
     Create an item for a name in LIMIT clause: LIMIT var
       @param THD         - THD, for mem_root
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 960f1b4de3d..1cc9f17cd20 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -9353,46 +9353,22 @@ column_default_non_parenthesized_expr:
           }
         | NEXT_SYM VALUE_SYM FOR_SYM table_ident
           {
-            TABLE_LIST *table;
-            if (!(table= Select->add_table_to_list(thd, $4, 0,
-                                           TL_OPTION_SEQUENCE,
-                                           TL_WRITE_ALLOW_WRITE,
-                                           MDL_SHARED_WRITE)))
-              MYSQL_YYABORT;
-            if (!($$= new (thd->mem_root) Item_func_nextval(thd, table)))
+            if (!($$= Lex->create_item_func_nextval(thd, $4)))
               MYSQL_YYABORT;
           }
         | NEXTVAL_SYM '(' table_ident ')'
           {
-            TABLE_LIST *table;
-            if (!(table= Select->add_table_to_list(thd, $3, 0,
-                                                TL_OPTION_SEQUENCE,
-                                                TL_WRITE_ALLOW_WRITE,
-                                                MDL_SHARED_WRITE)))
-              MYSQL_YYABORT;
-            if (!($$= new (thd->mem_root) Item_func_nextval(thd, table)))
+            if (!($$= Lex->create_item_func_nextval(thd, $3)))
               MYSQL_YYABORT;
           }
         | PREVIOUS_SYM VALUE_SYM FOR_SYM table_ident
           {
-            TABLE_LIST *table;
-            if (!(table= Select->add_table_to_list(thd, $4, 0,
-                                                TL_OPTION_SEQUENCE,
-                                                TL_READ,
-                                                MDL_SHARED_READ)))
-              MYSQL_YYABORT;
-            if (!($$= new (thd->mem_root) Item_func_lastval(thd, table)))
+            if (!($$= Lex->create_item_func_lastval(thd, $4)))
               MYSQL_YYABORT;
           }
         | LASTVAL_SYM '(' table_ident ')'
           {
-            TABLE_LIST *table;
-            if (!(table= Select->add_table_to_list(thd, $3, 0,
-                                                TL_OPTION_SEQUENCE,
-                                                TL_READ,
-                                                MDL_SHARED_WRITE)))
-              MYSQL_YYABORT;
-            if (!($$= new (thd->mem_root) Item_func_lastval(thd, table)))
+            if (!($$= Lex->create_item_func_lastval(thd, $3)))
               MYSQL_YYABORT;
           }
         ;
@@ -14165,53 +14141,12 @@ simple_ident_q:
 simple_ident_q2:
           '.' ident '.' ident
           {
-            LEX *lex= thd->lex;
-            SELECT_LEX *sel= lex->current_select;
-            if (sel->no_table_names_allowed)
-            {
-              my_error(ER_TABLENAME_NOT_ALLOWED_HERE,
-                       MYF(0), $2.str, thd->where);
-            }
-            if ((sel->parsing_place != IN_HAVING) ||
-                (sel->get_in_sum_expr() > 0))
-            {
-              $$= new (thd->mem_root) Item_field(thd, Lex->current_context(),
-                                                 NullS, $2.str, $4.str);
-
-            }
-            else
-            {
-              $$= new (thd->mem_root) Item_ref(thd, Lex->current_context(),
-                                               NullS, $2.str, $4.str);
-            }
-            if ($$ == NULL)
+            if (!($$= Lex->create_item_ident(thd, null_lex_str, $2, $4)))
               MYSQL_YYABORT;
           }
         | ident '.' ident '.' ident
           {
-            LEX *lex= thd->lex;
-            SELECT_LEX *sel= lex->current_select;
-            const char* schema= (thd->client_capabilities & CLIENT_NO_SCHEMA ?
-                                 NullS : $1.str);
-            if (sel->no_table_names_allowed)
-            {
-              my_error(ER_TABLENAME_NOT_ALLOWED_HERE,
-                       MYF(0), $3.str, thd->where);
-            }
-            if ((sel->parsing_place != IN_HAVING) ||
-                (sel->get_in_sum_expr() > 0))
-            {
-              $$= new (thd->mem_root) Item_field(thd, Lex->current_context(),
-                                                 schema,
-                                                 $3.str, $5.str);
-            }
-            else
-            {
-              $$= new (thd->mem_root) Item_ref(thd, Lex->current_context(),
-                                               schema,
-                                               $3.str, $5.str);
-            }
-            if ($$ == NULL)
+            if (!($$= Lex->create_item_ident(thd, $1, $3, $5)))
               MYSQL_YYABORT;
           }
         ;
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 7d272d28088..aa76dabff1f 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -9441,46 +9441,22 @@ column_default_non_parenthesized_expr:
           }
         | NEXT_SYM VALUE_SYM FOR_SYM table_ident
           {
-            TABLE_LIST *table;
-            if (!(table= Select->add_table_to_list(thd, $4, 0,
-                                           TL_OPTION_SEQUENCE,
-                                           TL_WRITE_ALLOW_WRITE,
-                                           MDL_SHARED_WRITE)))
-              MYSQL_YYABORT;
-            if (!($$= new (thd->mem_root) Item_func_nextval(thd, table)))
+            if (!($$= Lex->create_item_func_nextval(thd, $4)))
               MYSQL_YYABORT;
           }
         | NEXTVAL_SYM '(' table_ident ')'
           {
-            TABLE_LIST *table;
-            if (!(table= Select->add_table_to_list(thd, $3, 0,
-                                                TL_OPTION_SEQUENCE,
-                                                TL_WRITE_ALLOW_WRITE,
-                                                MDL_SHARED_WRITE)))
-              MYSQL_YYABORT;
-            if (!($$= new (thd->mem_root) Item_func_nextval(thd, table)))
+            if (!($$= Lex->create_item_func_nextval(thd, $3)))
               MYSQL_YYABORT;
           }
         | PREVIOUS_SYM VALUE_SYM FOR_SYM table_ident
           {
-            TABLE_LIST *table;
-            if (!(table= Select->add_table_to_list(thd, $4, 0,
-                                                TL_OPTION_SEQUENCE,
-                                                TL_READ,
-                                                MDL_SHARED_READ)))
-              MYSQL_YYABORT;
-            if (!($$= new (thd->mem_root) Item_func_lastval(thd, table)))
+            if (!($$= Lex->create_item_func_lastval(thd, $4)))
               MYSQL_YYABORT;
           }
         | LASTVAL_SYM '(' table_ident ')'
           {
-            TABLE_LIST *table;
-            if (!(table= Select->add_table_to_list(thd, $3, 0,
-                                                TL_OPTION_SEQUENCE,
-                                                TL_READ,
-                                                MDL_SHARED_WRITE)))
-              MYSQL_YYABORT;
-            if (!($$= new (thd->mem_root) Item_func_lastval(thd, table)))
+            if (!($$= Lex->create_item_func_lastval(thd, $3)))
               MYSQL_YYABORT;
           }
         ;
@@ -14319,53 +14295,12 @@ simple_ident_q2:
           }
         | '.' ident '.' ident
           {
-            LEX *lex= thd->lex;
-            SELECT_LEX *sel= lex->current_select;
-            if (sel->no_table_names_allowed)
-            {
-              my_error(ER_TABLENAME_NOT_ALLOWED_HERE,
-                       MYF(0), $2.str, thd->where);
-            }
-            if ((sel->parsing_place != IN_HAVING) ||
-                (sel->get_in_sum_expr() > 0))
-            {
-              $$= new (thd->mem_root) Item_field(thd, Lex->current_context(),
-                                                 NullS, $2.str, $4.str);
-
-            }
-            else
-            {
-              $$= new (thd->mem_root) Item_ref(thd, Lex->current_context(),
-                                               NullS, $2.str, $4.str);
-            }
-            if ($$ == NULL)
+            if (!($$= Lex->create_item_ident(thd, null_lex_str, $2, $4)))
               MYSQL_YYABORT;
           }
         | ident '.' ident '.' ident
           {
-            LEX *lex= thd->lex;
-            SELECT_LEX *sel= lex->current_select;
-            const char* schema= (thd->client_capabilities & CLIENT_NO_SCHEMA ?
-                                 NullS : $1.str);
-            if (sel->no_table_names_allowed)
-            {
-              my_error(ER_TABLENAME_NOT_ALLOWED_HERE,
-                       MYF(0), $3.str, thd->where);
-            }
-            if ((sel->parsing_place != IN_HAVING) ||
-                (sel->get_in_sum_expr() > 0))
-            {
-              $$= new (thd->mem_root) Item_field(thd, Lex->current_context(),
-                                                 schema,
-                                                 $3.str, $5.str);
-            }
-            else
-            {
-              $$= new (thd->mem_root) Item_ref(thd, Lex->current_context(),
-                                               schema,
-                                               $3.str, $5.str);
-            }
-            if ($$ == NULL)
+            if (!($$= Lex->create_item_ident(thd, $1, $3, $5)))
               MYSQL_YYABORT;
           }
         ;