MDEV-17253 Oracle compatibility: The REVERSE key word for FOR loop behaves incorrectly

5 files changed, 36 insertions, 27 deletions

diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 38d356add20..c048aeeb585 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -5695,7 +5695,7 @@ bool LEX::sp_for_loop_implicit_cursor_statement(THD *thd,
     return true;
   DBUG_ASSERT(thd->lex == this);
   bounds->m_direction= 1;
-  bounds->m_upper_bound= NULL;
+  bounds->m_target_bound= NULL;
   bounds->m_implicit_cursor= true;
   return false;
 }
@@ -5739,7 +5739,7 @@ bool LEX::sp_for_loop_condition(THD *thd, const Lex_for_loop_st &loop)
   Item_splocal *args[2];
   for (uint i= 0 ; i < 2; i++)
   {
-    sp_variable *src= i == 0 ? loop.m_index : loop.m_upper_bound;
+    sp_variable *src= i == 0 ? loop.m_index : loop.m_target_bound;
     args[i]= new (thd->mem_root)
               Item_splocal(thd, &sp_rcontext_handler_local,
                            &src->name, src->offset, src->type_handler());
@@ -5800,11 +5800,11 @@ bool LEX::sp_for_loop_intrange_declarations(THD *thd, Lex_for_loop_st *loop,
                  sp_add_for_loop_variable(thd, index,
                                           bounds.m_index->get_item()))))
     return true;
-  if (unlikely(!(loop->m_upper_bound=
-                 bounds.m_upper_bound->
-                 sp_add_for_loop_upper_bound(thd,
-                                             bounds.
-                                             m_upper_bound->get_item()))))
+  if (unlikely(!(loop->m_target_bound=
+                 bounds.m_target_bound->
+                 sp_add_for_loop_target_bound(thd,
+                                              bounds.
+                                              m_target_bound->get_item()))))
      return true;
   loop->m_direction= bounds.m_direction;
   loop->m_implicit_cursor= 0;
@@ -5867,7 +5867,7 @@ bool LEX::sp_for_loop_cursor_declarations(THD *thd,
                                                        bounds.m_index,
                                                        item_func_sp)))
     return true;
-  loop->m_upper_bound= NULL;
+  loop->m_target_bound= NULL;
   loop->m_direction= bounds.m_direction;
   loop->m_cursor_offset= coffs;
   loop->m_implicit_cursor= bounds.m_implicit_cursor;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index dbd201d2d7c..13e1a5c0cf8 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -3736,9 +3736,9 @@ public:
   /* Integer range FOR LOOP methods */
   sp_variable *sp_add_for_loop_variable(THD *thd, const LEX_CSTRING *name,
                                         Item *value);
-  sp_variable *sp_add_for_loop_upper_bound(THD *thd, Item *value)
+  sp_variable *sp_add_for_loop_target_bound(THD *thd, Item *value)
   {
-    LEX_CSTRING name= { STRING_WITH_LEN("[upper_bound]") };
+    LEX_CSTRING name= { STRING_WITH_LEN("[target_bound]") };
     return sp_add_for_loop_variable(thd, &name, value);
   }
   bool sp_for_loop_intrange_declarations(THD *thd, Lex_for_loop_st *loop,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 812ded1e5c3..46eac699c04 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -4769,16 +4769,13 @@ sp_for_loop_bounds:
           IN_SYM opt_sp_for_loop_direction for_loop_bound_expr
           DOT_DOT_SYM for_loop_bound_expr
           {
-            $$.m_direction= $2;
-            $$.m_index= $3;
-            $$.m_upper_bound= $5;
-            $$.m_implicit_cursor= false;
+            $$= Lex_for_loop_bounds_intrange($2, $3, $5);
           }
         | IN_SYM opt_sp_for_loop_direction for_loop_bound_expr
           {
             $$.m_direction= $2;
             $$.m_index= $3;
-            $$.m_upper_bound= NULL;
+            $$.m_target_bound= NULL;
             $$.m_implicit_cursor= false;
           }
         | IN_SYM opt_sp_for_loop_direction '(' sp_cursor_stmt ')'
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 538daa8a5a6..b8b2a862469 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -4615,16 +4615,13 @@ sp_for_loop_bounds:
           IN_SYM opt_sp_for_loop_direction for_loop_bound_expr
           DOT_DOT_SYM for_loop_bound_expr
           {
-            $$.m_direction= $2;
-            $$.m_index= $3;
-            $$.m_upper_bound= $5;
-            $$.m_implicit_cursor= false;
+            $$= Lex_for_loop_bounds_intrange($2, $3, $5);
           }
         | IN_SYM opt_sp_for_loop_direction for_loop_bound_expr
           {
             $$.m_direction= $2;
             $$.m_index= $3;
-            $$.m_upper_bound= NULL;
+            $$.m_target_bound= NULL;
             $$.m_implicit_cursor= false;
           }
         | IN_SYM opt_sp_for_loop_direction '(' sp_cursor_stmt ')'
diff --git a/sql/structs.h b/sql/structs.h
index d8b95a3509a..be9abbf4613 100644
--- a/sql/structs.h
+++ b/sql/structs.h
@@ -692,26 +692,41 @@ public:
 struct Lex_for_loop_bounds_st
 {
 public:
-  class sp_assignment_lex *m_index;
-  class sp_assignment_lex *m_upper_bound;
+  class sp_assignment_lex *m_index;  // The first iteration value (or cursor)
+  class sp_assignment_lex *m_target_bound; // The last iteration value
   int8 m_direction;
   bool m_implicit_cursor;
-  bool is_for_loop_cursor() const { return m_upper_bound == NULL; }
+  bool is_for_loop_cursor() const { return m_target_bound == NULL; }
+};
+
+
+class Lex_for_loop_bounds_intrange: public Lex_for_loop_bounds_st
+{
+public:
+  Lex_for_loop_bounds_intrange(int8 direction,
+                               class sp_assignment_lex *left_expr,
+                               class sp_assignment_lex *right_expr)
+  {
+    m_direction= direction;
+    m_index=        direction > 0 ? left_expr  : right_expr;
+    m_target_bound= direction > 0 ? right_expr : left_expr;
+    m_implicit_cursor= false;
+  }
 };
 
 
 struct Lex_for_loop_st
 {
 public:
-  class sp_variable *m_index;
-  class sp_variable *m_upper_bound;
+  class sp_variable *m_index;  // The first iteration value (or cursor)
+  class sp_variable *m_target_bound; // The last iteration value
   int m_cursor_offset;
   int8 m_direction;
   bool m_implicit_cursor;
   void init()
   {
     m_index= 0;
-    m_upper_bound= 0;
+    m_target_bound= 0;
     m_direction= 0;
     m_implicit_cursor= false;
   }
@@ -719,7 +734,7 @@ public:
   {
     *this= other;
   }
-  bool is_for_loop_cursor() const { return m_upper_bound == NULL; }
+  bool is_for_loop_cursor() const { return m_target_bound == NULL; }
   bool is_for_loop_explicit_cursor() const
   {
     return is_for_loop_cursor() && !m_implicit_cursor;