summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/main/sp-code.result12
-rw-r--r--mysql-test/main/sp-for-loop.result2
-rw-r--r--mysql-test/main/sp-for-loop.test2
-rw-r--r--mysql-test/suite/compat/oracle/r/sp-code.result42
-rw-r--r--mysql-test/suite/compat/oracle/r/sp.result60
-rw-r--r--mysql-test/suite/compat/oracle/t/sp-code.test2
-rw-r--r--mysql-test/suite/compat/oracle/t/sp.test45
-rw-r--r--sql/sql_lex.cc16
-rw-r--r--sql/sql_lex.h4
-rw-r--r--sql/sql_yacc.yy7
-rw-r--r--sql/sql_yacc_ora.yy7
-rw-r--r--sql/structs.h29
12 files changed, 169 insertions, 59 deletions
diff --git a/mysql-test/main/sp-code.result b/mysql-test/main/sp-code.result
index c3af01d19e8..0ff30ba7764 100644
--- a/mysql-test/main/sp-code.result
+++ b/mysql-test/main/sp-code.result
@@ -1020,8 +1020,8 @@ i
SHOW PROCEDURE CODE p1;
Pos Instruction
0 set i@0 1
-1 set [upper_bound]@1 3
-2 jump_if_not 6(6) i@0 <= [upper_bound]@1
+1 set [target_bound]@1 3
+2 jump_if_not 6(6) i@0 <= [target_bound]@1
3 stmt 0 "SELECT i"
4 set i@0 i@0 + 1
5 jump 2
@@ -1058,11 +1058,11 @@ i j
SHOW PROCEDURE CODE p1;
Pos Instruction
0 set i@0 1
-1 set [upper_bound]@1 3
-2 jump_if_not 17(17) i@0 <= [upper_bound]@1
+1 set [target_bound]@1 3
+2 jump_if_not 17(17) i@0 <= [target_bound]@1
3 set j@2 1
-4 set [upper_bound]@3 3
-5 jump_if_not 13(13) j@2 <= [upper_bound]@3
+4 set [target_bound]@3 3
+5 jump_if_not 13(13) j@2 <= [target_bound]@3
6 jump_if_not 8(8) i@0 = 3
7 jump 17
8 jump_if_not 10(10) j@2 = 3
diff --git a/mysql-test/main/sp-for-loop.result b/mysql-test/main/sp-for-loop.result
index 0da09586df5..d62d6ae3612 100644
--- a/mysql-test/main/sp-for-loop.result
+++ b/mysql-test/main/sp-for-loop.result
@@ -80,7 +80,7 @@ CREATE FUNCTION f1 (a INT, b INT) RETURNS INT
BEGIN
DECLARE total INT DEFAULT 0;
fori:
-FOR i IN REVERSE a..1
+FOR i IN REVERSE 1..a
DO
SET total= total + i;
IF i = b THEN
diff --git a/mysql-test/main/sp-for-loop.test b/mysql-test/main/sp-for-loop.test
index 6350e9fb9d3..420ab58aaa7 100644
--- a/mysql-test/main/sp-for-loop.test
+++ b/mysql-test/main/sp-for-loop.test
@@ -86,7 +86,7 @@ CREATE FUNCTION f1 (a INT, b INT) RETURNS INT
BEGIN
DECLARE total INT DEFAULT 0;
fori:
- FOR i IN REVERSE a..1
+ FOR i IN REVERSE 1..a
DO
SET total= total + i;
IF i = b THEN
diff --git a/mysql-test/suite/compat/oracle/r/sp-code.result b/mysql-test/suite/compat/oracle/r/sp-code.result
index 1049563511c..1c6aacc8743 100644
--- a/mysql-test/suite/compat/oracle/r/sp-code.result
+++ b/mysql-test/suite/compat/oracle/r/sp-code.result
@@ -579,8 +579,8 @@ SHOW FUNCTION CODE f1;
Pos Instruction
0 set total@2 0
1 set i@3 1
-2 set [upper_bound]@4 a@0
-3 jump_if_not 9(9) i@3 <= [upper_bound]@4
+2 set [target_bound]@4 a@0
+3 jump_if_not 9(9) i@3 <= [target_bound]@4
4 set total@2 total@2 + i@3
5 jump_if_not 7(7) i@3 = b@1
6 jump 9
@@ -598,7 +598,7 @@ CREATE FUNCTION f1 (a INT, b INT) RETURN INT
AS
total INT := 0;
BEGIN
-FOR i IN REVERSE a..1
+FOR i IN REVERSE 1..a
LOOP
total:= total + i;
IF i = b THEN
@@ -612,8 +612,8 @@ SHOW FUNCTION CODE f1;
Pos Instruction
0 set total@2 0
1 set i@3 a@0
-2 set [upper_bound]@4 1
-3 jump_if_not 9(9) i@3 >= [upper_bound]@4
+2 set [target_bound]@4 1
+3 jump_if_not 9(9) i@3 >= [target_bound]@4
4 set total@2 total@2 + i@3
5 jump_if_not 7(7) i@3 = b@1
6 jump 9
@@ -651,12 +651,12 @@ SHOW FUNCTION CODE f1;
Pos Instruction
0 set total@4 0
1 set ia@5 1
-2 set [upper_bound]@6 a@0
-3 jump_if_not 17(17) ia@5 <= [upper_bound]@6
+2 set [target_bound]@6 a@0
+3 jump_if_not 17(17) ia@5 <= [target_bound]@6
4 set total@4 total@4 + 1000
5 set ib@7 1
-6 set [upper_bound]@8 b@2
-7 jump_if_not 15(15) ib@7 <= [upper_bound]@8
+6 set [target_bound]@8 b@2
+7 jump_if_not 15(15) ib@7 <= [target_bound]@8
8 set total@4 total@4 + 1
9 jump_if_not 11(0) ib@7 = limitb@3
10 jump 15
@@ -698,8 +698,8 @@ SHOW FUNCTION CODE f1;
Pos Instruction
0 set total@1 0
1 set i@2 1
-2 set [upper_bound]@3 a@0
-3 jump_if_not 11(11) i@2 <= [upper_bound]@3
+2 set [target_bound]@3 a@0
+3 jump_if_not 11(11) i@2 <= [target_bound]@3
4 set total@1 total@1 + 1000
5 jump_if_not 8(8) i@2 = 5
6 set i@2 i@2 + 1
@@ -735,11 +735,11 @@ SHOW FUNCTION CODE f1;
Pos Instruction
0 set total@1 0
1 set i@2 1
-2 set [upper_bound]@3 a@0
-3 jump_if_not 16(16) i@2 <= [upper_bound]@3
+2 set [target_bound]@3 a@0
+3 jump_if_not 16(16) i@2 <= [target_bound]@3
4 set j@4 1
-5 set [upper_bound]@5 2
-6 jump_if_not 14(14) j@4 <= [upper_bound]@5
+5 set [target_bound]@5 2
+6 jump_if_not 14(14) j@4 <= [target_bound]@5
7 set total@1 total@1 + 1000
8 jump_if_not 11(11) i@2 = 5
9 set i@2 i@2 + 1
@@ -778,11 +778,11 @@ SHOW FUNCTION CODE f1;
Pos Instruction
0 set total@1 0
1 set j@2 1
-2 set [upper_bound]@3 2
-3 jump_if_not 16(16) j@2 <= [upper_bound]@3
+2 set [target_bound]@3 2
+3 jump_if_not 16(16) j@2 <= [target_bound]@3
4 set i@4 1
-5 set [upper_bound]@5 a@0
-6 jump_if_not 14(14) i@4 <= [upper_bound]@5
+5 set [target_bound]@5 a@0
+6 jump_if_not 14(14) i@4 <= [target_bound]@5
7 set total@1 total@1 + 1000
8 jump_if_not 11(11) i@4 = 5
9 set i@4 i@4 + 1
@@ -814,8 +814,8 @@ SHOW FUNCTION CODE f1;
Pos Instruction
0 set total@1 0
1 set i@2 1
-2 set [upper_bound]@3 a@0
-3 jump_if_not 10(10) i@2 <= [upper_bound]@3
+2 set [target_bound]@3 a@0
+3 jump_if_not 10(10) i@2 <= [target_bound]@3
4 jump_if_not 7(0) i@2 = 5
5 set i@2 i@2 + 1
6 jump 3
diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result
index 8e126b3f724..8fa49c4092a 100644
--- a/mysql-test/suite/compat/oracle/r/sp.result
+++ b/mysql-test/suite/compat/oracle/r/sp.result
@@ -843,7 +843,7 @@ CREATE FUNCTION f1 (a INT, b INT) RETURN INT
AS
total INT := 0;
BEGIN
-FOR i IN REVERSE a..1
+FOR i IN REVERSE 1..a
LOOP
total:= total + i;
IF i = b THEN
@@ -2502,3 +2502,61 @@ BEGIN
SELECT 'a' IN ('b',v);
END $$
ERROR HY000: Illegal parameter data types varchar and row for operation 'in'
+#
+# MDEV-17253 Oracle compatibility: The REVERSE key word for FOR loop behaves incorrectly
+#
+DECLARE
+totalprice DECIMAL(12,2):=NULL;
+loop_start INTEGER := 1;
+BEGIN
+FOR idx IN REVERSE loop_start..10 LOOP
+SELECT idx;
+END LOOP;
+END;
+$$
+idx
+10
+idx
+9
+idx
+8
+idx
+7
+idx
+6
+idx
+5
+idx
+4
+idx
+3
+idx
+2
+idx
+1
+CREATE PROCEDURE p1 AS
+loop_start INTEGER := 1;
+BEGIN
+FOR idx IN REVERSE 3..loop_start LOOP
+SELECT idx;
+END LOOP;
+END;
+$$
+CALL p1();
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1 AS
+loop_start INTEGER := 1;
+BEGIN
+FOR idx IN REVERSE loop_start..3 LOOP
+SELECT idx;
+END LOOP;
+END;
+$$
+CALL p1();
+idx
+3
+idx
+2
+idx
+1
+DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/t/sp-code.test b/mysql-test/suite/compat/oracle/t/sp-code.test
index 9a1f64e54b4..ea66ed80d2a 100644
--- a/mysql-test/suite/compat/oracle/t/sp-code.test
+++ b/mysql-test/suite/compat/oracle/t/sp-code.test
@@ -470,7 +470,7 @@ CREATE FUNCTION f1 (a INT, b INT) RETURN INT
AS
total INT := 0;
BEGIN
- FOR i IN REVERSE a..1
+ FOR i IN REVERSE 1..a
LOOP
total:= total + i;
IF i = b THEN
diff --git a/mysql-test/suite/compat/oracle/t/sp.test b/mysql-test/suite/compat/oracle/t/sp.test
index b88271ad3e2..6020bd95993 100644
--- a/mysql-test/suite/compat/oracle/t/sp.test
+++ b/mysql-test/suite/compat/oracle/t/sp.test
@@ -918,7 +918,7 @@ CREATE FUNCTION f1 (a INT, b INT) RETURN INT
AS
total INT := 0;
BEGIN
- FOR i IN REVERSE a..1
+ FOR i IN REVERSE 1..a
LOOP
total:= total + i;
IF i = b THEN
@@ -2352,3 +2352,46 @@ BEGIN
END $$
DELIMITER ;$$
+--echo #
+--echo # MDEV-17253 Oracle compatibility: The REVERSE key word for FOR loop behaves incorrectly
+--echo #
+
+DELIMITER $$;
+DECLARE
+ totalprice DECIMAL(12,2):=NULL;
+ loop_start INTEGER := 1;
+BEGIN
+ FOR idx IN REVERSE loop_start..10 LOOP
+ SELECT idx;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+ loop_start INTEGER := 1;
+BEGIN
+ FOR idx IN REVERSE 3..loop_start LOOP
+ SELECT idx;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+ loop_start INTEGER := 1;
+BEGIN
+ FOR idx IN REVERSE loop_start..3 LOOP
+ SELECT idx;
+ END LOOP;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
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;
View Lorry Controller Status