maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #10517
Re: MDEV-10598 - bb-10.2-compatibility
Hello Jerome,
On 03/14/2017 06:07 PM, jerome brauge wrote:
> Hi Alexander,
> Can you review this patch ?
> I could add more tests when your others points will be corrected.
I tried to simplify your patch slightly.
sp_instr_cpush instructions are now created from
the array sp_pcontext::m_cursors, which already
stores all cursors declared on the current frame.
So there is now no a need for a separate list sp_head::m_delayed_cpush.
This seems to work fine.
Also, I changed the meaning of the last argument of sp_declare_cursor()
to the opposite: from "delay_instr" to "add_cpush_instr".
I find "bool do_something" easier to read than "bool dont_do_something",
especially in this context:
if (do_something)
vs
if (!dont_do_something)
but this is probably my personal preference :)
Also I removed overloading (and fixed sql_yacc.yy instead).
Can you please review the attached patch?
(It also includes my previous suggestions)
Many thanks!!!
>
> Thanks.
> Jérôme.
>
>
>> -----Message d'origine-----
>> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx]
>> Envoyé : mardi 14 mars 2017 11:54
>> À : jerome brauge
>> Cc : MariaDB Developers (maria-developers@xxxxxxxxxxxxxxxxxxx)
>> Objet : Re: MDEV-10598 - bb-10.2-compatibility
>>
>> Hi Jerome,
>>
>> On 03/14/2017 02:29 PM, jerome brauge wrote:
>>> Thanks Alexander,
>>>
>>> I have two other problems to finalize my patch:
>>> - Attached script cur_err_warning.sql produce a warning 1931 (Query
>> execution was interrupted. The query examined at least 2 rows, which
>> exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete)
>> and I don't see why.
>>
>> That's my fault. Please ignore this warning for now.
>> This warning happens when the procedure opens a cursor to resolve its
>> structure. I will suppress this warning later.
>>
>>
>>>
>>> - Attached script cur_err_field_name.sql produce an error 4057 (HY000) at
>> line 23: Row variable 'rec2' does not have a field 'a'
>>
>> This is also my bug.
>> The problem is that rec2 has a name "rec1.a" instead of just "a".
>>
>>
>>
>> Please change
>>
>> CURSOR cur2 IS SELECT rec1.a ;
>>
>> to
>>
>> CURSOR cur2 IS SELECT rec1.a AS a;
>>
>>
>>>
>>> These two scripts work fine on oracle.
>>>
>>> Jérôme.
>>>
>>>
>>>> -----Message d'origine-----
>>>> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx] Envoyé : mardi 14 mars
>>>> 2017 11:11 À : jerome brauge Objet : Re: MDEV-10598 -
>>>> bb-10.2-compatibility
>>>>
>>>> Hello Jerome,
>>>>
>>>>
>>>>
>>>> On 03/13/2017 07:46 PM, jerome brauge wrote:
>>>>> Alexander,
>>>>> I think there is a little bug with ROWTYPE: when I affect a variable
>>>>> with a
>>>> field value, the server crash.
>>>>> I attached the script which cause the issue.
>>>>
>>>> Thanks for reporting this.
>>>>
>>>>
>>>> This quick patch fixes the problem:
>>>>
>>>>
>>>> diff --git a/sql/sp_head.cc b/sql/sp_head.cc index faf9f5f..745f982
>>>> 100644
>>>> --- a/sql/sp_head.cc
>>>> +++ b/sql/sp_head.cc
>>>> @@ -347,6 +347,12 @@ Item *
>>>> sp_prepare_func_item(THD* thd, Item **it_addr, uint cols) {
>>>> DBUG_ENTER("sp_prepare_func_item");
>>>> + if (!(*it_addr)->fixed &&
>>>> + (*it_addr)->fix_fields(thd, it_addr)) {
>>>> + DBUG_PRINT("info", ("fix_fields() failed"));
>>>> + DBUG_RETURN(NULL);
>>>> + }
>>>> it_addr= (*it_addr)->this_item_addr(thd, it_addr);
>>>>
>>>> if ((!(*it_addr)->fixed &&
>>>>
>>>>
>>>>
>>>> But I'm still thinking.
>>>> Perhaps I'll end some with some different patch.
>>>> I let you know when the final fix is pushed.
>>>>
>>>>
>>>>>
>>>>> This is the call stack :
>>>>>
>>>>>> mysqld.exe!my_sigabrt_handler(int sig) Line 477 C
>>>>> mysqld.exe!raise(int signum) Line 516 C++
>>>>> mysqld.exe!abort() Line 64 C++
>>>>> mysqld.exe!common_assert_to_stderr_direct(const wchar_t * const
>>>> expression, const wchar_t * const file_name, const unsigned int
>>>> line_number) Line 124 C++
>>>>> mysqld.exe!common_assert_to_stderr<wchar_t>(const wchar_t *
>>>> const expression, const wchar_t * const file_name, const unsigned int
>>>> line_number) Line 138 C++
>>>>> mysqld.exe!common_assert<wchar_t>(const wchar_t * const
>>>> expression, const wchar_t * const file_name, const unsigned int
>>>> line_number, void * const return_address) Line 383 C++
>>>>> mysqld.exe!_wassert(const wchar_t * expression, const wchar_t *
>>>> file_name, unsigned int line_number) Line 404 C++
>>>>> mysqld.exe!Item_splocal_row_field_by_name::this_item_addr(THD
>>>> * thd, Item * * it) Line 1814 C++
>>>>> mysqld.exe!sp_prepare_func_item(THD * thd, Item * * it_addr,
>>>> unsigned int cols) Line 350 C++
>>>>> mysqld.exe!sp_eval_expr(THD * thd, Item * result_item, Field *
>>>> result_field, Item * * expr_item_ptr) Line 391 C++
>>>>> mysqld.exe!sp_rcontext::set_variable(THD * thd, unsigned int idx,
>>>> Item * * value) Line 566 C++
>>>>> mysqld.exe!sp_instr_set::exec_core(THD * thd, unsigned int *
>>>> nextp) Line 3378 C++
>>>>> mysqld.exe!sp_lex_keeper::reset_lex_and_exec_core(THD * thd,
>>>> unsigned int * nextp, bool open_tables, sp_instr * instr) Line 3097 C++
>>>>> mysqld.exe!sp_instr_set::execute(THD * thd, unsigned int * nextp)
>>>> Line 3371 C++
>>>>> mysqld.exe!sp_head::execute(THD * thd, bool
>>>> merge_da_on_success) Line 1261 C++
>>>>> mysqld.exe!sp_head::execute_procedure(THD * thd, List<Item> *
>>>> args) Line 2086 C++
>>>>> mysqld.exe!do_execute_sp(THD * thd, sp_head * sp) Line 2890
>>>> C++
>>>>> mysqld.exe!mysql_execute_command(THD * thd) Line 5919 C++
>>>>> mysqld.exe!mysql_parse(THD * thd, char * rawbuf, unsigned int
>>>> length, Parser_state * parser_state, bool is_com_multi, bool
>>>> is_next_command) Line 8006 C++
>>>>> mysqld.exe!dispatch_command(enum_server_command command,
>>>> THD * thd, char * packet, unsigned int packet_length, bool is_com_multi,
>>>> bool is_next_command) Line 1821 C++
>>>>> mysqld.exe!do_command(THD * thd) Line 1369 C++
>>>>> mysqld.exe!threadpool_process_request(THD * thd) Line 346
>>>> C++
>>>>> mysqld.exe!tp_callback(TP_connection * c) Line 192 C++
>>>>> mysqld.exe!tp_callback(_TP_CALLBACK_INSTANCE * instance, void *
>>>> context) Line 377 C++
>>>>> mysqld.exe!work_callback(_TP_CALLBACK_INSTANCE * instance,
>>>> void * context, _TP_WORK * work) Line 451 C++
>>>>>
>>>>> Can you reproduce this ?
>>>>>
>>>>> Jérôme.
>>>>>
>>>>>> -----Message d'origine-----
>>>>>> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx] Envoyé : lundi 13
>>>>>> mars
>>>>>> 2017 14:47 À : jerome brauge Objet : Re: MDEV-10598 -
>>>>>> bb-10.2-compatibility
>>>>>>
>>>>>> Jérôme,
>>>>>>
>>>>>> On 03/13/2017 05:43 PM, jerome brauge wrote:
>>>>>>> Hello Alexander,
>>>>>>> I have to do some changes in the patch and add some tests cases
>>>>>>> (with row type) I think it will be ready this afternoon (CET).
>>>>>>
>>>>>> Excellent. You rock!
>>>>>>
>>>>>>>
>>>>>>> Jérôme.
>>>>>>>
>>>>>>>
>>>>>>>> -----Message d'origine-----
>>>>>>>> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx] Envoyé : lundi 13
>>>>>>>> mars
>>>>>>>> 2017 14:38 À : jerome brauge Cc : maria-developers Objet : Re:
>>>>>>>> MDEV-10598 - bb-10.2-compatibility
>>>>>>>>
>>>>>>>> Hello Jerome,
>>>>>>>>
>>>>>>>> will you try to apply your patch on top of the current bb-10.2-
>>>>>> compatibility?
>>>>>>>>
>>>>>>>> Or should I do that?
>>>>>>>>
>>>>>>>> Thanks!
>>>>>>>>
>>>>>>>>
>>>>>>>> On 03/10/2017 02:36 PM, Alexander Barkov wrote:
>>>>>>>>> Hello Jerome,
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On 02/27/2017 11:39 PM, jerome brauge wrote:
>>>>>>>>>> Hello Alexander,
>>>>>>>>>> Thanks for the explanation.
>>>>>>>>>> It's something we do not use. I did not think about it.
>>>>>>>>>> I look forward to your patch.
>>>>>>>>>
>>>>>>>>> I pushed these tasks:
>>>>>>>>>
>>>>>>>>> MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP
>>>>>>>>> MDEV-12098 sql_mode=ORACLE: Implicit cursor FOR loop
>>>>>>>>> MDEV-12011 sql_mode=ORACLE: cursor%ROWTYPE in variable
>>>>>> declarations
>>>>>>>>> MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable
>>>>>> declarations
>>>>>>>>>
>>>>>>>>> Please clone the branch again.
>>>>>>>>> Git pull will not work, because I recently rebased
>>>>>>>>> bb-10.2-compatibility on top of the latest 10.2.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> When implementing cursor%ROWTYPE, I had your patch in mind
>> and
>>>>>> made
>>>>>>>>> some refactoring to help us apply MDEV-10598 easier.
>>>>>>>>> Please see a comment to MDEV-12011 in "git log".
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Now the tricky thing (when adding your patch) is to make sure
>>>>>>>>> that this work fine:
>>>>>>>>>
>>>>>>>>> CREATE PROCEDURE p1
>>>>>>>>> AS
>>>>>>>>> a INT:=10;
>>>>>>>>> CURSOR cur1 IS SELECT a;
>>>>>>>>> rec1 cur1%ROWTYPE;
>>>>>>>>> CURSOR cur2 IS SELECT rec1.a;
>>>>>>>>> rec2 cur2%ROWTYPE;
>>>>>>>>> BEGIN
>>>>>>>>> OPEN cur2;
>>>>>>>>> FETCH cur2 INTO rec2;
>>>>>>>>> CLOSE cur2;
>>>>>>>>> SELECT rec2.a;
>>>>>>>>> END;
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> I.e. a set of intermixed CURSOR and cursor%ROWTYPE variable
>>>>>>>>> declarations referencing each other recursively.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Thanks.
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Regards,
>>>>>>>>>> Jérôme.
>>>>>>>>>>
>>>>>>>>>>> -----Message d'origine-----
>>>>>>>>>>> De : Alexander Barkov [mailto:bar@xxxxxxxxxxx] Envoyé : lundi
>>>>>>>>>>> 27 février 2017 11:28 À : jerome brauge Cc : maria-developers
>> Objet :
>>>>>>>>>>> Re: MDEV-10598 - bb-10.2-compatibility
>>>>>>>>>>>
>>>>>>>>>>> Hello Jerome,
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> On 02/21/2017 07:18 PM, jerome brauge wrote:
>>>>>>>>>>>> Hello Alexander,
>>>>>>>>>>>> I've done this patch for MDEV-10598.
>>>>>>>>>>>> Can you review it ?
>>>>>>>>>>>
>>>>>>>>>>> It seems we'll have to postpone this patch.
>>>>>>>>>>>
>>>>>>>>>>> I'm currently working on:
>>>>>>>>>>>
>>>>>>>>>>> MDEV-10598 Variable declarations can go after cursor
>>>>>>>>>>> declarations
>>>>>>>>>>> MDEV-12011 sql_mode=ORACLE: cursor%ROWTYPE in variable
>>>>>>>> declarations
>>>>>>>>>>> So the trick with postponing variable declarations using a
>>>>>>>>>>> temporary list might not work properly after adding MDEV-10598
>>>>>>>>>>> abd MDEV-12011, the order of cursors and variables is important.
>>>>>>>>>>>
>>>>>>>>>>> Example:
>>>>>>>>>>>
>>>>>>>>>>> DECLARE
>>>>>>>>>>> CURSOR cur1 IS SELECT a,b FROM t1;
>>>>>>>>>>> v cur1%ROWTYPE;
>>>>>>>>>>> CURSOR cur2 IS SELECT v.a, v.b FROM DUAL; BEGIN
>>>>>>>>>>> ...
>>>>>>>>>>> END;
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> So the order of cur1, v and cur2 is important.
>>>>>>>>>>>
>>>>>>>>>>> I'll let you known when I'm ready with %ROWTYPE tasks.
>>>>>>>>>>>
>>>>>>>>>>> Thanks!
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> Regards,
>>>>>>>>>>>> Jérôme.
>>>>>>>>>>>>
diff --git a/mysql-test/suite/compat/oracle/r/sp-code.result b/mysql-test/suite/compat/oracle/r/sp-code.result
index 557906a..4ac4b61 100644
--- a/mysql-test/suite/compat/oracle/r/sp-code.result
+++ b/mysql-test/suite/compat/oracle/r/sp-code.result
@@ -1277,3 +1277,74 @@ Pos Instruction
28 jump 4
29 cpop 1
DROP PROCEDURE p1;
+#
+# MDEV-10598 sql_mode=ORACLE: Variable declarations can go after cursor declarations
+#
+#
+# Cursor declaration and cursor%ROWTYPE declaration in the same block
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (1,'a');
+CREATE PROCEDURE p1()
+AS
+CURSOR cur1 IS SELECT a FROM t1;
+rec1 cur1%ROWTYPE;
+BEGIN
+rec1.a:= 10;
+END;
+$$
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 cursor_copy_struct cur1 rec1@0
+1 set rec1@0 NULL
+2 cpush cur1@0
+3 set rec1.a@0["a"] 10
+4 cpop 1
+CALL p1;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# Recursive cursor and cursor%ROWTYPE declarations in the same block
+#
+CREATE PROCEDURE p1
+AS
+a INT:=10;
+CURSOR cur1 IS SELECT a;
+rec1 cur1%ROWTYPE;
+CURSOR cur2 IS SELECT rec1.a + 1 "a";
+rec2 cur2%ROWTYPE;
+BEGIN
+OPEN cur1;
+FETCH cur1 INTO rec1;
+CLOSE cur1;
+SELECT rec1.a;
+open cur2;
+FETCH cur2 INTO rec2;
+CLOSE cur2;
+SELECT rec2.a;
+END;
+$$
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 set a@0 10
+1 cursor_copy_struct cur1 rec1@1
+2 set rec1@1 NULL
+3 cursor_copy_struct cur2 rec2@2
+4 set rec2@2 NULL
+5 cpush cur1@0
+6 cpush cur2@1
+7 copen cur1@0
+8 cfetch cur1@0 rec1@1
+9 cclose cur1@0
+10 stmt 0 "SELECT rec1.a"
+11 copen cur2@1
+12 cfetch cur2@1 rec2@2
+13 cclose cur2@1
+14 stmt 0 "SELECT rec2.a"
+15 cpop 2
+CALL p1();
+rec1.a
+10
+rec2.a
+11
+DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/r/sp-cursor-decl.result b/mysql-test/suite/compat/oracle/r/sp-cursor-decl.result
new file mode 100644
index 0000000..fe2f9dd
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/sp-cursor-decl.result
@@ -0,0 +1,270 @@
+SET sql_mode=ORACLE;
+#
+# MDEV-10598 sql_mode=ORACLE: Variable declarations can go after cursor declarations
+#
+#
+# Variable after cursor declaration
+#
+CREATE TABLE t1 (a INT);
+insert into t1 values (1);
+insert into t1 values (2);
+CREATE PROCEDURE p1
+AS
+CURSOR c IS SELECT a FROM t1;
+var1 varchar(10);
+BEGIN
+OPEN c;
+fetch c into var1;
+SELECT c%ROWCOUNT,var1;
+close c;
+END;
+$$
+CALL p1;
+c%ROWCOUNT var1
+1 1
+DROP PROCEDURE p1;
+drop table t1;
+#
+# Variable after condition declaration
+#
+CREATE TABLE t1 (col1 INT);
+insert into t1 values (1);
+create unique index t1_col1 on t1 (col1);
+CREATE PROCEDURE p1
+AS
+dup_key CONDITION FOR SQLSTATE '23000';
+var1 varchar(40);
+CONTINUE HANDLER FOR dup_key
+BEGIN
+var1:='duplicate key in index';
+END;
+BEGIN
+var1:='';
+insert into t1 values (1);
+select var1;
+END;
+$$
+CALL p1;
+var1
+duplicate key in index
+DROP PROCEDURE p1;
+drop table t1;
+#
+# Condition after cursor declaration
+#
+CREATE TABLE t1 (col1 INT);
+insert into t1 values (1);
+create unique index t1_col1 on t1 (col1);
+CREATE PROCEDURE p1
+AS
+var1 varchar(40);
+var2 integer;
+CURSOR c IS SELECT col1 FROM t1;
+dup_key CONDITION FOR SQLSTATE '23000';
+CONTINUE HANDLER FOR dup_key
+BEGIN
+var1:='duplicate key in index';
+END;
+BEGIN
+var1:='';
+insert into t1 values (1);
+SELECT var1;
+END;
+$$
+CALL p1;
+var1
+duplicate key in index
+DROP PROCEDURE p1;
+drop table t1;
+#
+# Cursor after handler declaration
+#
+CREATE TABLE t1 (col1 INT);
+insert into t1 values (1);
+create unique index t1_col1 on t1 (col1);
+CREATE PROCEDURE p1
+AS
+var1 varchar(40);
+var2 integer;
+dup_key CONDITION FOR SQLSTATE '23000';
+CONTINUE HANDLER FOR dup_key
+BEGIN
+var1:='duplicate key in index';
+END;
+CURSOR c IS SELECT col1 FROM t1;
+BEGIN
+var1:='';
+insert into t1 values (1);
+SELECT var1;
+END;
+$$
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CURSOR c IS SELECT col1 FROM t1;
+BEGIN
+var1:='';
+insert into t1 values (1);
+SELE' at line 6
+drop table t1;
+#
+# Condition after handler declaration
+#
+CREATE TABLE t1 (col1 INT);
+insert into t1 values (1);
+create unique index t1_col1 on t1 (col1);
+CREATE PROCEDURE p1
+AS
+var1 varchar(40);
+var2 integer;
+dup_key CONDITION FOR SQLSTATE '23000';
+CURSOR c IS SELECT col1 FROM t1;
+CONTINUE HANDLER FOR dup_key
+BEGIN
+var1:='duplicate key in index';
+END;
+divide_by_zero CONDITION FOR SQLSTATE '22012';
+BEGIN
+var1:='';
+insert into t1 values (1);
+SELECT var1;
+END;
+$$
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'divide_by_zero CONDITION FOR SQLSTATE '22012';
+BEGIN
+var1:='';
+insert into t1 va' at line 7
+drop table t1;
+#
+# Variable after handler declaration
+#
+CREATE TABLE t1 (col1 INT);
+insert into t1 values (1);
+create unique index t1_col1 on t1 (col1);
+CREATE PROCEDURE p1
+AS
+var1 varchar(40);
+var2 integer;
+dup_key CONDITION FOR SQLSTATE '23000';
+CURSOR c IS SELECT col1 FROM t1;
+CONTINUE HANDLER FOR dup_key
+BEGIN
+var1:='duplicate key in index';
+END;
+divide_by_zero CONDITION FOR SQLSTATE '22012';
+BEGIN
+var1:='';
+insert into t1 values (1);
+SELECT var1;
+END;
+$$
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'divide_by_zero CONDITION FOR SQLSTATE '22012';
+BEGIN
+var1:='';
+insert into t1 va' at line 7
+drop table t1;
+#
+# Variable after cursor (inner block)
+#
+CREATE TABLE t1 (col1 INT);
+insert into t1 values (1);
+insert into t1 values (2);
+create unique index t1_col1 on t1 (col1);
+CREATE PROCEDURE p1
+AS
+CURSOR c IS SELECT col1 FROM t1;
+var1 varchar(40);
+BEGIN
+OPEN c;
+begin
+declare
+CURSOR c IS SELECT col1 FROM t1 where col1=2;
+var2 integer;
+dup_key CONDITION FOR SQLSTATE '23000';
+CONTINUE HANDLER FOR dup_key
+BEGIN
+var1:='duplicate key in index';
+END;
+begin
+OPEN c;
+fetch c into var1;
+SELECT 'inner cursor',var1;
+insert into t1 values (2);
+close c;
+end;
+end;
+SELECT var1;
+fetch c into var1;
+SELECT c%ROWCOUNT,var1;
+begin
+insert into t1 values (2);
+exception when 1062 then
+begin
+SELECT 'dup key caugth';
+end;
+end;
+close c;
+END;
+$$
+CALL p1;
+inner cursor var1
+inner cursor 2
+var1
+duplicate key in index
+c%ROWCOUNT var1
+1 1
+dup key caugth
+dup key caugth
+DROP PROCEDURE p1;
+drop table t1;
+#
+# Cursor declaration and row type declaration in same block
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+insert into t1 values(1,'a');
+CREATE PROCEDURE p1()
+AS
+CURSOR cur1 IS SELECT a FROM t1;
+rec1 cur1%ROWTYPE;
+BEGIN
+rec1.a:= 10;
+END;
+$$
+call p1;
+DROP PROCEDURE p1;
+drop table t1;
+#
+# Recursive cursor and cursor%ROWTYPE declarations in the same block
+#
+CREATE PROCEDURE p1
+AS
+a INT:=10;
+b VARCHAR(10):='b0';
+c DOUBLE:=0.1;
+CURSOR cur1 IS SELECT a, b, c;
+rec1 cur1%ROWTYPE;
+CURSOR cur2 IS SELECT rec1.a + 1 "a", rec1.b||'0' AS b, rec1.c AS c;
+rec2 cur2%ROWTYPE;
+BEGIN
+OPEN cur1;
+FETCH cur1 INTO rec1;
+CLOSE cur1;
+SELECT rec1.a;
+OPEN cur2;
+FETCH cur2 INTO rec2;
+CLOSE cur2;
+SELECT rec2.a;
+CREATE TABLE t2 AS SELECT rec2.a AS a, rec2.b AS b, rec2.c AS c;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+END;
+$$
+CALL p1();
+rec1.a
+10
+rec2.a
+11
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "a" bigint(21) DEFAULT NULL,
+ "b" varchar(11) DEFAULT NULL,
+ "c" double DEFAULT NULL
+)
+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 00c8109c0..ce2cb01 100644
--- a/mysql-test/suite/compat/oracle/t/sp-code.test
+++ b/mysql-test/suite/compat/oracle/t/sp-code.test
@@ -917,3 +917,57 @@ $$
DELIMITER ;$$
SHOW PROCEDURE CODE p1;
DROP PROCEDURE p1;
+
+
+--echo #
+--echo # MDEV-10598 sql_mode=ORACLE: Variable declarations can go after cursor declarations
+--echo #
+
+--echo #
+--echo # Cursor declaration and cursor%ROWTYPE declaration in the same block
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (1,'a');
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+ CURSOR cur1 IS SELECT a FROM t1;
+ rec1 cur1%ROWTYPE;
+BEGIN
+ rec1.a:= 10;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+--echo #
+--echo # Recursive cursor and cursor%ROWTYPE declarations in the same block
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+ a INT:=10;
+ CURSOR cur1 IS SELECT a;
+ rec1 cur1%ROWTYPE;
+ CURSOR cur2 IS SELECT rec1.a + 1 "a";
+ rec2 cur2%ROWTYPE;
+BEGIN
+ OPEN cur1;
+ FETCH cur1 INTO rec1;
+ CLOSE cur1;
+ SELECT rec1.a;
+ open cur2;
+ FETCH cur2 INTO rec2;
+ CLOSE cur2;
+ SELECT rec2.a;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1();
+DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/t/sp-cursor-decl.test b/mysql-test/suite/compat/oracle/t/sp-cursor-decl.test
new file mode 100644
index 0000000..dd90cd8
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/sp-cursor-decl.test
@@ -0,0 +1,274 @@
+SET sql_mode=ORACLE;
+
+--echo #
+--echo # MDEV-10598 sql_mode=ORACLE: Variable declarations can go after cursor declarations
+--echo #
+
+--echo #
+--echo # Variable after cursor declaration
+--echo #
+
+CREATE TABLE t1 (a INT);
+insert into t1 values (1);
+insert into t1 values (2);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+ CURSOR c IS SELECT a FROM t1;
+ var1 varchar(10);
+BEGIN
+ OPEN c;
+ fetch c into var1;
+ SELECT c%ROWCOUNT,var1;
+ close c;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+DROP PROCEDURE p1;
+drop table t1;
+
+--echo #
+--echo # Variable after condition declaration
+--echo #
+
+CREATE TABLE t1 (col1 INT);
+insert into t1 values (1);
+create unique index t1_col1 on t1 (col1);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+ dup_key CONDITION FOR SQLSTATE '23000';
+ var1 varchar(40);
+ CONTINUE HANDLER FOR dup_key
+ BEGIN
+ var1:='duplicate key in index';
+ END;
+BEGIN
+ var1:='';
+ insert into t1 values (1);
+ select var1;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+DROP PROCEDURE p1;
+drop table t1;
+
+--echo #
+--echo # Condition after cursor declaration
+--echo #
+
+CREATE TABLE t1 (col1 INT);
+insert into t1 values (1);
+create unique index t1_col1 on t1 (col1);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+ var1 varchar(40);
+ var2 integer;
+ CURSOR c IS SELECT col1 FROM t1;
+ dup_key CONDITION FOR SQLSTATE '23000';
+ CONTINUE HANDLER FOR dup_key
+ BEGIN
+ var1:='duplicate key in index';
+ END;
+BEGIN
+ var1:='';
+ insert into t1 values (1);
+ SELECT var1;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+DROP PROCEDURE p1;
+drop table t1;
+
+--echo #
+--echo # Cursor after handler declaration
+--echo #
+
+CREATE TABLE t1 (col1 INT);
+insert into t1 values (1);
+create unique index t1_col1 on t1 (col1);
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PROCEDURE p1
+AS
+ var1 varchar(40);
+ var2 integer;
+ dup_key CONDITION FOR SQLSTATE '23000';
+ CONTINUE HANDLER FOR dup_key
+ BEGIN
+ var1:='duplicate key in index';
+ END;
+ CURSOR c IS SELECT col1 FROM t1;
+BEGIN
+ var1:='';
+ insert into t1 values (1);
+ SELECT var1;
+END;
+$$
+DELIMITER ;$$
+drop table t1;
+
+--echo #
+--echo # Condition after handler declaration
+--echo #
+
+CREATE TABLE t1 (col1 INT);
+insert into t1 values (1);
+create unique index t1_col1 on t1 (col1);
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PROCEDURE p1
+AS
+ var1 varchar(40);
+ var2 integer;
+ dup_key CONDITION FOR SQLSTATE '23000';
+ CURSOR c IS SELECT col1 FROM t1;
+ CONTINUE HANDLER FOR dup_key
+ BEGIN
+ var1:='duplicate key in index';
+ END;
+ divide_by_zero CONDITION FOR SQLSTATE '22012';
+BEGIN
+ var1:='';
+ insert into t1 values (1);
+ SELECT var1;
+END;
+$$
+DELIMITER ;$$
+drop table t1;
+
+--echo #
+--echo # Variable after handler declaration
+--echo #
+
+CREATE TABLE t1 (col1 INT);
+insert into t1 values (1);
+create unique index t1_col1 on t1 (col1);
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PROCEDURE p1
+AS
+ var1 varchar(40);
+ var2 integer;
+ dup_key CONDITION FOR SQLSTATE '23000';
+ CURSOR c IS SELECT col1 FROM t1;
+ CONTINUE HANDLER FOR dup_key
+ BEGIN
+ var1:='duplicate key in index';
+ END;
+ divide_by_zero CONDITION FOR SQLSTATE '22012';
+BEGIN
+ var1:='';
+ insert into t1 values (1);
+ SELECT var1;
+END;
+$$
+DELIMITER ;$$
+drop table t1;
+
+--echo #
+--echo # Variable after cursor (inner block)
+--echo #
+
+CREATE TABLE t1 (col1 INT);
+insert into t1 values (1);
+insert into t1 values (2);
+create unique index t1_col1 on t1 (col1);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+ CURSOR c IS SELECT col1 FROM t1;
+ var1 varchar(40);
+BEGIN
+ OPEN c;
+ begin
+ declare
+ CURSOR c IS SELECT col1 FROM t1 where col1=2;
+ var2 integer;
+ dup_key CONDITION FOR SQLSTATE '23000';
+ CONTINUE HANDLER FOR dup_key
+ BEGIN
+ var1:='duplicate key in index';
+ END;
+ begin
+ OPEN c;
+ fetch c into var1;
+ SELECT 'inner cursor',var1;
+ insert into t1 values (2);
+ close c;
+ end;
+ end;
+ SELECT var1;
+ fetch c into var1;
+ SELECT c%ROWCOUNT,var1;
+ begin
+ insert into t1 values (2);
+ exception when 1062 then
+ begin
+ SELECT 'dup key caugth';
+ end;
+ end;
+ close c;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+DROP PROCEDURE p1;
+drop table t1;
+
+--echo #
+--echo # Cursor declaration and row type declaration in same block
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+insert into t1 values(1,'a');
+delimiter $$;
+CREATE PROCEDURE p1()
+AS
+ CURSOR cur1 IS SELECT a FROM t1;
+ rec1 cur1%ROWTYPE;
+BEGIN
+ rec1.a:= 10;
+END;
+$$
+delimiter ;$$
+call p1;
+DROP PROCEDURE p1;
+drop table t1;
+
+
+--echo #
+--echo # Recursive cursor and cursor%ROWTYPE declarations in the same block
+--echo #
+
+delimiter $$;
+CREATE PROCEDURE p1
+AS
+ a INT:=10;
+ b VARCHAR(10):='b0';
+ c DOUBLE:=0.1;
+ CURSOR cur1 IS SELECT a, b, c;
+ rec1 cur1%ROWTYPE;
+ CURSOR cur2 IS SELECT rec1.a + 1 "a", rec1.b||'0' AS b, rec1.c AS c;
+ rec2 cur2%ROWTYPE;
+BEGIN
+ OPEN cur1;
+ FETCH cur1 INTO rec1;
+ CLOSE cur1;
+ SELECT rec1.a;
+ OPEN cur2;
+ FETCH cur2 INTO rec2;
+ CLOSE cur2;
+ SELECT rec2.a;
+ CREATE TABLE t2 AS SELECT rec2.a AS a, rec2.b AS b, rec2.c AS c;
+ SHOW CREATE TABLE t2;
+ DROP TABLE t2;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 745f982..55b7773 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -2459,6 +2459,23 @@ sp_head::do_cont_backpatch()
}
}
+
+bool
+sp_head::sp_add_instr_cpush_for_cursors(THD *thd, sp_pcontext *pcontext)
+{
+ for (uint i= 0; i < pcontext->frame_cursor_count(); i++)
+ {
+ const sp_pcursor *c= pcontext->get_cursor_by_local_frame_offset(i);
+ sp_instr_cpush *instr= new (thd->mem_root)
+ sp_instr_cpush(instructions(), pcontext, c->lex(),
+ pcontext->cursor_offset() + i);
+ if (instr == NULL || add_instr(instr))
+ return true;
+ }
+ return false;
+}
+
+
void
sp_head::set_info(longlong created, longlong modified,
st_sp_chistics *chistics, sql_mode_t sql_mode)
diff --git a/sql/sp_head.h b/sql/sp_head.h
index ee11974..2a10fba 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -560,6 +560,9 @@ class sp_head :private Query_arena
void
do_cont_backpatch();
+ /// Add cpush instructions for all cursors declared in the current frame
+ bool sp_add_instr_cpush_for_cursors(THD *thd, sp_pcontext *pcontext);
+
char *name(uint *lenp = 0) const
{
if (lenp)
diff --git a/sql/sp_pcontext.h b/sql/sp_pcontext.h
index 6d8f5e1..bb26b1e 100644
--- a/sql/sp_pcontext.h
+++ b/sql/sp_pcontext.h
@@ -646,6 +646,15 @@ class sp_pcontext : public Sql_alloc
/// Find cursor by offset (for SHOW {PROCEDURE|FUNCTION} CODE only).
const sp_pcursor *find_cursor(uint offset) const;
+ const sp_pcursor *get_cursor_by_local_frame_offset(uint offset) const
+ { return &m_cursors.at(offset); }
+
+ uint cursor_offset() const
+ { return m_cursor_offset; }
+
+ uint frame_cursor_count() const
+ { return m_cursors.elements(); }
+
uint max_cursor_index() const
{ return m_max_cursor_index + m_cursors.elements(); }
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 27d0d4d..19f2365 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -5687,7 +5687,7 @@ bool LEX::sp_for_loop_cursor_finalize(THD *thd, const Lex_for_loop_st &loop)
bool LEX::sp_declare_cursor(THD *thd, const LEX_STRING name,
sp_lex_cursor *cursor_stmt,
- sp_pcontext *param_ctx)
+ sp_pcontext *param_ctx, bool add_cpush_instr)
{
uint offp;
sp_instr_cpush *i;
@@ -5698,12 +5698,18 @@ bool LEX::sp_declare_cursor(THD *thd, const LEX_STRING name,
return true;
}
cursor_stmt->set_cursor_name(name);
- i= new (thd->mem_root)
- sp_instr_cpush(sphead->instructions(), spcont, cursor_stmt,
- spcont->current_cursor_count());
- return i == NULL ||
- sphead->add_instr(i) ||
- spcont->add_cursor(name, param_ctx, cursor_stmt);
+
+ if (spcont->add_cursor(name, param_ctx, cursor_stmt))
+ return true;
+
+ if (add_cpush_instr)
+ {
+ i= new (thd->mem_root)
+ sp_instr_cpush(sphead->instructions(), spcont, cursor_stmt,
+ spcont->current_cursor_count() - 1);
+ return i == NULL || sphead->add_instr(i);
+ }
+ return false;
}
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index f18a0a4..ea5ac51 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -3154,7 +3154,8 @@ struct LEX: public Query_tables_list
bool sp_declare_cursor(THD *thd, const LEX_STRING name,
class sp_lex_cursor *cursor_stmt,
- sp_pcontext *param_ctx);
+ sp_pcontext *param_ctx, bool add_cpush_instr);
+
bool sp_open_cursor(THD *thd, const LEX_STRING name,
List<sp_assignment_lex> *parameters);
Item_splocal *create_item_for_sp_var(LEX_STRING name, sp_variable *spvar,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index d6bbfa4..194c89b 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -3020,7 +3020,7 @@ sp_decl_body:
}
| ident CURSOR_SYM FOR_SYM sp_cursor_stmt
{
- if (Lex->sp_declare_cursor(thd, $1, $4, NULL))
+ if (Lex->sp_declare_cursor(thd, $1, $4, NULL, true))
MYSQL_YYABORT;
$$.vars= $$.conds= $$.hndlrs= 0;
$$.curs= 1;
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index f6fc323..21cc1ca 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -1334,7 +1334,9 @@ END_OF_INPUT
%type <num> sp_decl_idents sp_handler_type sp_hcond_list
%type <spcondvalue> sp_cond sp_hcond sqlstate signal_value opt_signal_value
-%type <spblock> sp_decl_body sp_decl_body_list opt_sp_decl_body_list
+%type <spblock> sp_decl_body_list opt_sp_decl_body_list
+%type <spblock> sp_decl_non_handler sp_decl_non_handler_list
+%type <spblock> sp_decl_handler sp_decl_handler_list opt_sp_decl_handler_list
%type <spblock_handlers> sp_block_statements_and_exceptions
%type <sp_instr_addr> sp_instr_addr
%type <sp_cursor_name_and_offset> sp_cursor_name_and_offset
@@ -2572,12 +2574,37 @@ opt_sp_decl_body_list:
;
sp_decl_body_list:
- sp_decl_body ';' { $$= $1; }
- | sp_decl_body_list sp_decl_body ';'
+ sp_decl_non_handler_list
{
- if (Lex->sp_declarations_join(&$$, $1, $2))
+ if (Lex->sphead->sp_add_instr_cpush_for_cursors(thd, Lex->spcont))
MYSQL_YYABORT;
}
+ opt_sp_decl_handler_list
+ {
+ $$.join($1, $3);
+ }
+ | sp_decl_handler_list
+ ;
+
+sp_decl_non_handler_list:
+ sp_decl_non_handler ';' { $$= $1; }
+ | sp_decl_non_handler_list sp_decl_non_handler ';'
+ {
+ $$.join($1, $2);
+ }
+ ;
+
+sp_decl_handler_list:
+ sp_decl_handler ';' { $$= $1; }
+ | sp_decl_handler_list sp_decl_handler ';'
+ {
+ $$.join($1, $2);
+ }
+ ;
+
+opt_sp_decl_handler_list:
+ /* Empty*/ { $$.init(); }
+ | sp_decl_handler_list
;
qualified_column_ident:
@@ -2655,7 +2682,7 @@ type_or_rowtype:
| ROWTYPE_SYM { $$= 1; }
;
-sp_decl_body:
+sp_decl_non_handler:
sp_decl_idents
{
Lex->sp_variable_declarations_init(thd, $1);
@@ -2709,18 +2736,6 @@ sp_decl_body:
$$.vars= $$.hndlrs= $$.curs= 0;
$$.conds= 1;
}
- | sp_handler_type HANDLER_SYM FOR_SYM
- {
- if (Lex->sp_handler_declaration_init(thd, $1))
- MYSQL_YYABORT;
- }
- sp_hcond_list sp_proc_stmt
- {
- if (Lex->sp_handler_declaration_finalize(thd, $1))
- MYSQL_YYABORT;
- $$.vars= $$.conds= $$.curs= 0;
- $$.hndlrs= 1;
- }
| CURSOR_SYM ident_directly_assignable
{
Lex->sp_block_init(thd);
@@ -2731,13 +2746,28 @@ sp_decl_body:
sp_pcontext *param_ctx= Lex->spcont;
if (Lex->sp_block_finalize(thd))
MYSQL_YYABORT;
- if (Lex->sp_declare_cursor(thd, $2, $6, param_ctx))
+ if (Lex->sp_declare_cursor(thd, $2, $6, param_ctx, false))
MYSQL_YYABORT;
$$.vars= $$.conds= $$.hndlrs= 0;
$$.curs= 1;
}
;
+sp_decl_handler:
+ sp_handler_type HANDLER_SYM FOR_SYM
+ {
+ if (Lex->sp_handler_declaration_init(thd, $1))
+ MYSQL_YYABORT;
+ }
+ sp_hcond_list sp_proc_stmt
+ {
+ if (Lex->sp_handler_declaration_finalize(thd, $1))
+ MYSQL_YYABORT;
+ $$.vars= $$.conds= $$.curs= 0;
+ $$.hndlrs= 1;
+ }
+ ;
+
opt_parenthesized_cursor_formal_parameters:
/* Empty */
| '(' sp_fdparams ')'
@@ -3983,7 +4013,7 @@ sp_for_loop_bounds:
{
DBUG_ASSERT(Lex->sphead);
LEX_STRING name= {C_STRING_WITH_LEN("[implicit_cursor]") };
- if (Lex->sp_declare_cursor(thd, name, $4, NULL))
+ if (Lex->sp_declare_cursor(thd, name, $4, NULL, true))
MYSQL_YYABORT;
$$.m_direction= 1;
if (!($$.m_index= new (thd->mem_root) sp_assignment_lex(thd, thd->lex)))
Follow ups
References
