← Back to team overview

maria-developers team mailing list archive

  1. maria-developers team
  2. Mailing list archive
  3. Message #10516

Re: MDEV-10598 - bb-10.2-compatibility

  Thread Previous • Date Previous • • Thread Next 
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.

SHOW PROCEDURE CODE is available in debug builds only.

I moved the related code from sp-cursor-decl.test to sp-code.test

Also removed unnecessary "OR REPLACE" clauses.


> 
> 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..0cd9e68 100644
--- a/mysql-test/suite/compat/oracle/r/sp-code.result
+++ b/mysql-test/suite/compat/oracle/r/sp-code.result
@@ -1277,3 +1277,29 @@ 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 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;
+$$
+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;
diff --git a/mysql-test/suite/compat/oracle/r/sp-cursor-decl.result b/mysql-test/suite/compat/oracle/r/sp-cursor-decl.result
index 1e185f7..8ada172 100644
--- a/mysql-test/suite/compat/oracle/r/sp-cursor-decl.result
+++ b/mysql-test/suite/compat/oracle/r/sp-cursor-decl.result
@@ -5,10 +5,10 @@ SET sql_mode=ORACLE;
 #
 # Variable after cursor declaration
 #
-CREATE OR REPLACE TABLE t1 (a INT);
+CREATE TABLE t1 (a INT);
 insert into t1 values (1);
 insert into t1 values (2);
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
 CURSOR c IS SELECT a FROM t1;
 var1 varchar(10);
@@ -27,10 +27,10 @@ drop table t1;
 #
 # Variable after condition declaration
 #
-CREATE OR REPLACE TABLE t1 (col1 INT);
+CREATE TABLE t1 (col1 INT);
 insert into t1 values (1);
 create unique index t1_col1 on t1 (col1);
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
 dup_key CONDITION FOR SQLSTATE '23000';
 var1 varchar(40);
@@ -52,10 +52,10 @@ drop table t1;
 #
 # Condition after cursor declaration
 #
-CREATE OR REPLACE TABLE t1 (col1 INT);
+CREATE TABLE t1 (col1 INT);
 insert into t1 values (1);
 create unique index t1_col1 on t1 (col1);
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
 var1 varchar(40);
 var2 integer;
@@ -79,10 +79,10 @@ drop table t1;
 #
 # Cursor after handler declaration
 #
-CREATE OR REPLACE TABLE t1 (col1 INT);
+CREATE TABLE t1 (col1 INT);
 insert into t1 values (1);
 create unique index t1_col1 on t1 (col1);
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
 var1 varchar(40);
 var2 integer;
@@ -107,10 +107,10 @@ drop table t1;
 #
 # Condition after handler declaration
 #
-CREATE OR REPLACE TABLE t1 (col1 INT);
+CREATE TABLE t1 (col1 INT);
 insert into t1 values (1);
 create unique index t1_col1 on t1 (col1);
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
 var1 varchar(40);
 var2 integer;
@@ -135,10 +135,10 @@ drop table t1;
 #
 # Variable after handler declaration
 #
-CREATE OR REPLACE TABLE t1 (col1 INT);
+CREATE TABLE t1 (col1 INT);
 insert into t1 values (1);
 create unique index t1_col1 on t1 (col1);
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
 var1 varchar(40);
 var2 integer;
@@ -163,11 +163,11 @@ drop table t1;
 #
 # Variable after cursor (inner block)
 #
-CREATE OR REPLACE TABLE t1 (col1 INT);
+CREATE TABLE t1 (col1 INT);
 insert into t1 values (1);
 insert into t1 values (2);
 create unique index t1_col1 on t1 (col1);
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
 CURSOR c IS SELECT col1 FROM t1;
 var1 varchar(40);
@@ -219,7 +219,7 @@ drop table t1;
 #
 CREATE TABLE t1 (a INT, b VARCHAR(10));
 insert into t1 values(1,'a');
-CREATE or replace PROCEDURE p1()
+CREATE PROCEDURE p1()
 AS
 CURSOR cur1 IS SELECT a FROM t1;
 rec1 cur1%ROWTYPE;
@@ -227,18 +227,10 @@ 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
-currentlty generate error ER_QUERY_EXCEEDED_ROWS_EXAMINED_LIMIT
 call p1;
 DROP PROCEDURE p1;
 drop table t1;
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
 a INT:=10;
 CURSOR cur1 IS SELECT a;
@@ -256,7 +248,6 @@ CLOSE cur2;
 SELECT rec2.a;
 END;
 $$
-currentlty generate error ER_QUERY_EXCEEDED_ROWS_EXAMINED_LIMIT
 CALL p1();
 rec1.a
 10
diff --git a/mysql-test/suite/compat/oracle/t/sp-code.test b/mysql-test/suite/compat/oracle/t/sp-code.test
index 00c8109c0..145b848 100644
--- a/mysql-test/suite/compat/oracle/t/sp-code.test
+++ b/mysql-test/suite/compat/oracle/t/sp-code.test
@@ -917,3 +917,29 @@ $$
 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 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 ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1;
+DROP PROCEDURE p1;
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/t/sp-cursor-decl.test b/mysql-test/suite/compat/oracle/t/sp-cursor-decl.test
index 0afbd66..91cf94e 100644
--- a/mysql-test/suite/compat/oracle/t/sp-cursor-decl.test
+++ b/mysql-test/suite/compat/oracle/t/sp-cursor-decl.test
@@ -8,11 +8,11 @@ SET sql_mode=ORACLE;
 --echo # Variable after cursor declaration
 --echo #
 
-CREATE OR REPLACE TABLE t1 (a INT);
+CREATE TABLE t1 (a INT);
 insert into t1 values (1);
 insert into t1 values (2);
 DELIMITER $$;
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
   CURSOR c IS SELECT a FROM t1;
   var1 varchar(10);
@@ -32,11 +32,11 @@ drop table t1;
 --echo # Variable after condition declaration
 --echo #
 
-CREATE OR REPLACE TABLE t1 (col1 INT);
+CREATE TABLE t1 (col1 INT);
 insert into t1 values (1);
 create unique index t1_col1 on t1 (col1);
 DELIMITER $$;
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
   dup_key CONDITION FOR SQLSTATE '23000';
   var1 varchar(40);
@@ -59,11 +59,11 @@ drop table t1;
 --echo # Condition after cursor declaration
 --echo #
 
-CREATE OR REPLACE TABLE t1 (col1 INT);
+CREATE TABLE t1 (col1 INT);
 insert into t1 values (1);
 create unique index t1_col1 on t1 (col1);
 DELIMITER $$;
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
   var1 varchar(40);
   var2 integer;
@@ -88,12 +88,12 @@ drop table t1;
 --echo # Cursor after handler declaration
 --echo #
 
-CREATE OR REPLACE TABLE t1 (col1 INT);
+CREATE TABLE t1 (col1 INT);
 insert into t1 values (1);
 create unique index t1_col1 on t1 (col1);
 DELIMITER $$;
 --error ER_PARSE_ERROR
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
   var1 varchar(40);
   var2 integer;
@@ -116,12 +116,12 @@ drop table t1;
 --echo # Condition after handler declaration
 --echo #
 
-CREATE OR REPLACE TABLE t1 (col1 INT);
+CREATE TABLE t1 (col1 INT);
 insert into t1 values (1);
 create unique index t1_col1 on t1 (col1);
 DELIMITER $$;
 --error ER_PARSE_ERROR
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
   var1 varchar(40);
   var2 integer;
@@ -145,12 +145,12 @@ drop table t1;
 --echo # Variable after handler declaration
 --echo #
 
-CREATE OR REPLACE TABLE t1 (col1 INT);
+CREATE TABLE t1 (col1 INT);
 insert into t1 values (1);
 create unique index t1_col1 on t1 (col1);
 DELIMITER $$;
 --error ER_PARSE_ERROR
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
   var1 varchar(40);
   var2 integer;
@@ -174,12 +174,12 @@ drop table t1;
 --echo # Variable after cursor (inner block)
 --echo #
 
-CREATE OR REPLACE TABLE t1 (col1 INT);
+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 or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
   CURSOR c IS SELECT col1 FROM t1;
   var1 varchar(40);
@@ -223,27 +223,26 @@ 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 or replace PROCEDURE p1()
+CREATE PROCEDURE p1()
 AS
-CURSOR cur1 IS SELECT a FROM t1;
-rec1 cur1%ROWTYPE;
+  CURSOR cur1 IS SELECT a FROM t1;
+  rec1 cur1%ROWTYPE;
 BEGIN
-rec1.a:= 10;
+  rec1.a:= 10;
 END;
 $$
 delimiter ;$$
-SHOW PROCEDURE CODE p1;
---echo currentlty generate error ER_QUERY_EXCEEDED_ROWS_EXAMINED_LIMIT
---disable_warnings
 call p1;
---enable_warnings
 DROP PROCEDURE p1;
 drop table t1;
+
+
 delimiter $$;
-CREATE or replace PROCEDURE p1
+CREATE PROCEDURE p1
 AS
   a INT:=10;
   CURSOR cur1 IS SELECT a;
@@ -262,8 +261,5 @@ BEGIN
 END;
 $$
 DELIMITER ;$$
---echo currentlty generate error ER_QUERY_EXCEEDED_ROWS_EXAMINED_LIMIT
---disable_warnings
 CALL p1();
---enable_warnings
-DROP PROCEDURE p1;
\ No newline at end of file
+DROP PROCEDURE p1;

References

  Thread Previous • Date Previous • • Thread Next