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:b...@mariadb.org] >> Envoyé : mardi 14 mars 2017 11:54 >> À : jerome brauge >> Cc : MariaDB Developers (maria-developers@lists.launchpad.net) >> 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:b...@mariadb.org] 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:b...@mariadb.org] 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:b...@mariadb.org] 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:b...@mariadb.org] 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)))
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp