PageRenderTime 18ms CodeModel.GetById 4ms app.highlight 6ms RepoModel.GetById 1ms app.codeStats 0ms

/mysql-test/r/sp-error.result

https://github.com/programmer10110/webscalesql-5.6
Unknown | 2888 lines | 2787 code | 101 blank | 0 comment | 0 complexity | 9e8d454ac3e9c1ef76acd054ff3e94b3 MD5 | raw file

Large files files are truncated, but you can click here to view the full file

   1drop table if exists t1, t2;
   2SELECT * FROM mysql.proc INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/proc.txt';
   3delete from mysql.proc;
   4create procedure syntaxerror(t int)|
   5ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
   6create procedure syntaxerror(t int)|
   7ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
   8create procedure syntaxerror(t int)|
   9ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
  10drop table if exists t3|
  11create table t3 ( x int )|
  12insert into t3 values (2), (3)|
  13create procedure bad_into(out param int)
  14select x from t3 into param|
  15call bad_into(@x)|
  16ERROR 42000: Result consisted of more than one row
  17drop procedure bad_into|
  18drop table t3|
  19create procedure proc1()
  20set @x = 42|
  21create function func1() returns int
  22return 42|
  23create procedure foo()
  24create procedure bar() set @x=3|
  25ERROR 2F003: Can't create a PROCEDURE from within another stored routine
  26create procedure foo()
  27create function bar() returns double return 2.3|
  28ERROR 2F003: Can't create a FUNCTION from within another stored routine
  29create procedure proc1()
  30set @x = 42|
  31ERROR 42000: PROCEDURE proc1 already exists
  32create function func1() returns int
  33return 42|
  34ERROR 42000: FUNCTION func1 already exists
  35drop procedure proc1|
  36drop function func1|
  37alter procedure foo|
  38ERROR 42000: PROCEDURE test.foo does not exist
  39alter function foo|
  40ERROR 42000: FUNCTION test.foo does not exist
  41drop procedure foo|
  42ERROR 42000: PROCEDURE test.foo does not exist
  43drop function foo|
  44ERROR 42000: FUNCTION test.foo does not exist
  45call foo()|
  46ERROR 42000: PROCEDURE test.foo does not exist
  47drop procedure if exists foo|
  48Warnings:
  49Note	1305	PROCEDURE test.foo does not exist
  50show create procedure foo|
  51ERROR 42000: PROCEDURE foo does not exist
  52show create function foo|
  53ERROR 42000: FUNCTION foo does not exist
  54create procedure foo()
  55foo: loop
  56leave bar;
  57end loop|
  58ERROR 42000: LEAVE with no matching label: bar
  59create procedure foo()
  60foo: loop
  61iterate bar;
  62end loop|
  63ERROR 42000: ITERATE with no matching label: bar
  64create procedure foo()
  65foo: begin
  66iterate foo;
  67end|
  68ERROR 42000: ITERATE with no matching label: foo
  69create procedure foo()
  70foo: loop
  71foo: loop
  72set @x=2;
  73end loop foo;
  74end loop foo|
  75ERROR 42000: Redefining label foo
  76create procedure foo()
  77foo: loop
  78set @x=2;
  79end loop bar|
  80ERROR 42000: End-label bar without match
  81create procedure foo()
  82return 42|
  83ERROR 42000: RETURN is only allowed in a FUNCTION
  84create procedure p(x int)
  85set @x = x|
  86create function f(x int) returns int
  87return x+42|
  88call p()|
  89ERROR 42000: Incorrect number of arguments for PROCEDURE test.p; expected 1, got 0
  90call p(1, 2)|
  91ERROR 42000: Incorrect number of arguments for PROCEDURE test.p; expected 1, got 2
  92select f()|
  93ERROR 42000: Incorrect number of arguments for FUNCTION test.f; expected 1, got 0
  94select f(1, 2)|
  95ERROR 42000: Incorrect number of arguments for FUNCTION test.f; expected 1, got 2
  96drop procedure p|
  97drop function f|
  98create procedure p(val int, out res int)
  99begin
 100declare x int default 0;
 101declare continue handler for foo set x = 1;
 102insert into test.t1 values (val);
 103if (x) then
 104set res = 0;
 105else
 106set res = 1;
 107end if;
 108end|
 109ERROR 42000: Undefined CONDITION: foo
 110create procedure p(val int, out res int)
 111begin
 112declare x int default 0;
 113declare foo condition for 1146;
 114declare continue handler for bar set x = 1;
 115insert into test.t1 values (val);
 116if (x) then
 117set res = 0;
 118else
 119set res = 1;
 120end if;
 121end|
 122ERROR 42000: Undefined CONDITION: bar
 123create function f(val int) returns int
 124begin
 125declare x int;
 126set x = val+3;
 127end|
 128ERROR 42000: No RETURN found in FUNCTION test.f
 129create function f(val int) returns int
 130begin
 131declare x int;
 132set x = val+3;
 133if x < 4 then
 134return x;
 135end if;
 136end|
 137select f(10)|
 138ERROR 2F005: FUNCTION f ended without RETURN
 139drop function f|
 140create procedure p()
 141begin
 142declare c cursor for insert into test.t1 values ("foo", 42);
 143open c;
 144close c;
 145end|
 146ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into test.t1 values ("foo", 42);
 147open c;
 148close c;
 149end' at line 3
 150create procedure p()
 151begin
 152declare x int;
 153declare c cursor for select * into x from test.t limit 1;
 154open c;
 155close c;
 156end|
 157ERROR 42000: Cursor SELECT must not have INTO
 158create procedure p()
 159begin
 160declare c cursor for select * from test.t;
 161open cc;
 162close c;
 163end|
 164ERROR 42000: Undefined CURSOR: cc
 165drop table if exists t1|
 166create table t1 (val int)|
 167create procedure p()
 168begin
 169declare c cursor for select * from test.t1;
 170open c;
 171open c;
 172close c;
 173end|
 174call p()|
 175ERROR 24000: Cursor is already open
 176drop procedure p|
 177create procedure p()
 178begin
 179declare c cursor for select * from test.t1;
 180open c;
 181close c;
 182close c;
 183end|
 184call p()|
 185ERROR 24000: Cursor is not open
 186drop procedure p|
 187alter procedure bar3 sql security invoker|
 188ERROR 42000: PROCEDURE test.bar3 does not exist
 189drop table t1|
 190drop table if exists t1|
 191create table t1 (val int, x float)|
 192insert into t1 values (42, 3.1), (19, 1.2)|
 193create procedure p()
 194begin
 195declare x int;
 196declare c cursor for select * from t1;
 197open c;
 198fetch c into x, y;
 199close c;
 200end|
 201ERROR 42000: Undeclared variable: y
 202create procedure p()
 203begin
 204declare x int;
 205declare c cursor for select * from t1;
 206open c;
 207fetch c into x;
 208close c;
 209end|
 210call p()|
 211ERROR HY000: Incorrect number of FETCH variables
 212drop procedure p|
 213create procedure p()
 214begin
 215declare x int;
 216declare y float;
 217declare z int;
 218declare c cursor for select * from t1;
 219open c;
 220fetch c into x, y, z;
 221close c;
 222end|
 223call p()|
 224ERROR HY000: Incorrect number of FETCH variables
 225drop procedure p|
 226create procedure p(in x int, x char(10))
 227begin
 228end|
 229ERROR 42000: Duplicate parameter: x
 230create function p(x int, x char(10))
 231begin
 232end|
 233ERROR 42000: Duplicate parameter: x
 234create procedure p()
 235begin
 236declare x float;
 237declare x int;
 238end|
 239ERROR 42000: Duplicate variable: x
 240create procedure p()
 241begin
 242declare c condition for 1064;
 243declare c condition for 1065;
 244end|
 245ERROR 42000: Duplicate condition: c
 246create procedure p()
 247begin
 248declare c cursor for select * from t1;
 249declare c cursor for select field from t1;
 250end|
 251ERROR 42000: Duplicate cursor: c
 252create procedure u()
 253use sptmp|
 254ERROR 0A000: USE is not allowed in stored procedures
 255create procedure p()
 256begin
 257declare c cursor for select * from t1;
 258declare x int;
 259end|
 260ERROR 42000: Variable or condition declaration after cursor or handler declaration
 261create procedure p()
 262begin
 263declare x int;
 264declare continue handler for sqlstate '42S99' set x = 1;
 265declare foo condition for sqlstate '42S99';
 266end|
 267ERROR 42000: Variable or condition declaration after cursor or handler declaration
 268create procedure p()
 269begin
 270declare x int;
 271declare continue handler for sqlstate '42S99' set x = 1;
 272declare c cursor for select * from t1;
 273end|
 274ERROR 42000: Cursor declaration after handler declaration
 275drop procedure if exists p|
 276create procedure p(in x int, inout y int, out z int)
 277begin
 278set y = x+y;
 279set z = x+y;
 280end|
 281set @tmp_x = 42|
 282set @tmp_y = 3|
 283set @tmp_z = 0|
 284call p(@tmp_x, @tmp_y, @tmp_z)|
 285select @tmp_x, @tmp_y, @tmp_z|
 286@tmp_x	@tmp_y	@tmp_z
 28742	45	87
 288call p(42, 43, @tmp_z)|
 289ERROR 42000: OUT or INOUT argument 2 for routine test.p is not a variable or NEW pseudo-variable in BEFORE trigger
 290call p(42, @tmp_y, 43)|
 291ERROR 42000: OUT or INOUT argument 3 for routine test.p is not a variable or NEW pseudo-variable in BEFORE trigger
 292drop procedure p|
 293create procedure p() begin end|
 294lock table t1 read|
 295call p()|
 296unlock tables|
 297drop procedure p|
 298lock tables t1 read, mysql.proc write|
 299ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
 300lock tables mysql.proc write, mysql.user write|
 301ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
 302lock tables t1 read, mysql.proc read|
 303unlock tables|
 304lock tables mysql.proc write|
 305unlock tables|
 306drop function if exists f1|
 307create function f1(i int) returns int
 308begin
 309insert into t1 (val) values (i);
 310return 0;
 311end|
 312select val, f1(val) from t1|
 313ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
 314select val, f1(val) from t1 as tab|
 315ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
 316select * from t1|
 317val	x
 31842	3.1
 31919	1.2
 320update t1 set val= f1(val)|
 321ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
 322select * from t1|
 323val	x
 32442	3.1
 32519	1.2
 326select f1(17)|
 327f1(17)
 3280
 329select * from t1|
 330val	x
 33142	3.1
 33219	1.2
 33317	NULL
 334delete from t1 where val= 17|
 335drop function f1|
 336create procedure bug1965()
 337begin
 338declare c cursor for select val from t1 order by valname;
 339open c;
 340close c;
 341end|
 342call bug1965()|
 343ERROR 42S22: Unknown column 'valname' in 'order clause'
 344drop procedure bug1965|
 345select 1 into a|
 346ERROR 42000: Undeclared variable: a
 347drop table if exists t3|
 348create table t3 (column_1_0 int)|
 349create procedure bug1653()
 350update t3 set column_1 = 0|
 351call bug1653()|
 352ERROR 42S22: Unknown column 'column_1' in 'field list'
 353drop table t3|
 354create table t3 (column_1 int)|
 355call bug1653()|
 356drop procedure bug1653|
 357drop table t3|
 358create procedure bug2259()
 359begin
 360declare v1 int;
 361declare c1 cursor for select s1 from t1;
 362fetch c1 into v1;
 363end|
 364call bug2259()|
 365ERROR 24000: Cursor is not open
 366drop procedure bug2259|
 367create procedure bug2272()
 368begin
 369declare v int;
 370update t1 set v = 42;
 371end|
 372insert into t1 values (666, 51.3)|
 373call bug2272()|
 374ERROR 42S22: Unknown column 'v' in 'field list'
 375truncate table t1|
 376drop procedure bug2272|
 377create procedure bug2329_1()
 378begin
 379declare v int;
 380insert into t1 (v) values (5);
 381end|
 382create procedure bug2329_2()
 383begin
 384declare v int;
 385replace t1 set v = 5;
 386end|
 387call bug2329_1()|
 388ERROR 42S22: Unknown column 'v' in 'field list'
 389call bug2329_2()|
 390ERROR 42S22: Unknown column 'v' in 'field list'
 391drop procedure bug2329_1|
 392drop procedure bug2329_2|
 393create function bug3287() returns int
 394begin
 395declare v int default null;
 396case
 397when v is not null then return 1;
 398end case;
 399return 2;
 400end|
 401select bug3287()|
 402ERROR 20000: Case not found for CASE statement
 403drop function bug3287|
 404create procedure bug3287(x int)
 405case x
 406when 0 then
 407insert into test.t1 values (x, 0.1);
 408when 1 then
 409insert into test.t1 values (x, 1.1);
 410end case|
 411call bug3287(2)|
 412ERROR 20000: Case not found for CASE statement
 413drop procedure bug3287|
 414drop table if exists t3|
 415create table t3 (s1 int, primary key (s1))|
 416insert into t3 values (5),(6)|
 417create procedure bug3279(out y int) 
 418begin
 419declare x int default 0;
 420begin
 421declare exit handler for sqlexception set x = x+1;
 422insert into t3 values (5);
 423end;
 424if x < 2 then
 425set x = x+1;
 426insert into t3 values (6);
 427end if;
 428set y = x;
 429end|
 430set @x = 0|
 431call bug3279(@x)|
 432ERROR 23000: Duplicate entry '6' for key 'PRIMARY'
 433select @x|
 434@x
 4350
 436drop procedure bug3279|
 437drop table t3|
 438create procedure nodb.bug3339() begin end|
 439ERROR 42000: Unknown database 'nodb'
 440create procedure bug2653_1(a int, out b int)
 441set b = aa|
 442create procedure bug2653_2(a int, out b int)
 443begin
 444if aa < 0 then
 445set b = - a;
 446else
 447set b = a;
 448end if;
 449end|
 450call bug2653_1(1, @b)|
 451ERROR 42S22: Unknown column 'aa' in 'field list'
 452call bug2653_2(2, @b)|
 453ERROR 42S22: Unknown column 'aa' in 'field list'
 454drop procedure bug2653_1|
 455drop procedure bug2653_2|
 456create procedure bug4344() drop procedure bug4344|
 457ERROR HY000: Can't drop or alter a PROCEDURE from within another stored routine
 458create procedure bug4344() drop function bug4344|
 459ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine
 460drop procedure if exists bug3294|
 461create procedure bug3294()
 462begin
 463declare continue handler for sqlexception drop table t5;
 464drop table t5;
 465drop table t5;
 466end|
 467create table t5 (x int)|
 468call bug3294()|
 469ERROR 42S02: Unknown table 'test.t5'
 470drop procedure bug3294|
 471drop procedure if exists bug8776_1|
 472drop procedure if exists bug8776_2|
 473drop procedure if exists bug8776_3|
 474drop procedure if exists bug8776_4|
 475create procedure bug8776_1()
 476begin
 477declare continue handler for sqlstate '42S0200test' begin end;
 478begin end;
 479end|
 480ERROR 42000: Bad SQLSTATE: '42S0200test'
 481create procedure bug8776_2()
 482begin
 483declare continue handler for sqlstate '4200' begin end;
 484begin end;
 485end|
 486ERROR 42000: Bad SQLSTATE: '4200'
 487create procedure bug8776_3()
 488begin
 489declare continue handler for sqlstate '420000' begin end;
 490begin end;
 491end|
 492ERROR 42000: Bad SQLSTATE: '420000'
 493create procedure bug8776_4()
 494begin
 495declare continue handler for sqlstate '42x00' begin end;
 496begin end;
 497end|
 498ERROR 42000: Bad SQLSTATE: '42x00'
 499create procedure bug6600()
 500check table t1|
 501ERROR 0A000: CHECK is not allowed in stored procedures
 502create procedure bug6600()
 503lock table t1 read|
 504ERROR 0A000: LOCK is not allowed in stored procedures
 505create procedure bug6600()
 506unlock table t1|
 507ERROR 0A000: UNLOCK is not allowed in stored procedures
 508drop procedure if exists bug9566|
 509create procedure bug9566()
 510begin
 511select * from t1;
 512end|
 513lock table t1 read|
 514alter procedure bug9566 comment 'Some comment'|
 515ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
 516unlock tables|
 517drop procedure bug9566|
 518drop procedure if exists bug7299|
 519create procedure bug7299()
 520begin
 521declare v int;
 522declare c cursor for select val from t1;
 523declare exit handler for sqlexception select 'Error!'; 
 524open c;
 525fetch c into v;
 526end|
 527truncate table t1|
 528call bug7299()|
 529ERROR 02000: No data - zero rows fetched, selected, or processed
 530drop procedure bug7299|
 531create procedure bug9073()
 532begin
 533declare continue handler for sqlexception select 1;
 534declare continue handler for sqlexception select 2;
 535end|
 536ERROR 42000: Duplicate handler declared in the same block
 537create procedure bug9073()
 538begin
 539declare condname1 condition for 1234;
 540declare continue handler for condname1 select 1;
 541declare exit handler for condname1 select 2;
 542end|
 543ERROR 42000: Duplicate handler declared in the same block
 544create procedure bug9073()
 545begin
 546declare condname1 condition for sqlstate '42000';
 547declare condname2 condition for sqlstate '42000';
 548declare exit handler for condname1 select 1;
 549declare continue handler for condname2 select 2;
 550end|
 551ERROR 42000: Duplicate handler declared in the same block
 552create procedure bug9073()
 553begin
 554declare condname1 condition for sqlstate '42000';
 555declare exit handler for condname1 select 1;
 556declare exit handler for sqlstate '42000' select 2;
 557end|
 558ERROR 42000: Duplicate handler declared in the same block
 559drop procedure if exists bug9073|
 560create procedure bug9073()
 561begin
 562declare condname1 condition for sqlstate '42000';
 563declare continue handler for condname1 select 1;
 564begin
 565declare exit handler for sqlstate '42000' select 2;
 566begin
 567declare continue handler for sqlstate '42000' select 3;
 568end;
 569end;
 570end|
 571drop procedure bug9073|
 572create procedure bug7047()
 573alter procedure bug7047|
 574ERROR HY000: Can't drop or alter a PROCEDURE from within another stored routine
 575create function bug7047() returns int
 576begin
 577alter function bug7047;
 578return 0;
 579end|
 580ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine
 581create function bug8408() returns int
 582begin
 583select * from t1;
 584return 0;
 585end|
 586ERROR 0A000: Not allowed to return a result set from a function
 587create function bug8408() returns int
 588begin
 589show warnings;
 590return 0;
 591end|
 592ERROR 0A000: Not allowed to return a result set from a function
 593create function bug8408(a int) returns int
 594begin
 595declare b int;
 596select b;
 597return b;
 598end|
 599ERROR 0A000: Not allowed to return a result set from a function
 600drop function if exists bug8408_f|
 601drop procedure if exists bug8408_p|
 602create function bug8408_f() returns int
 603begin
 604call bug8408_p();
 605return 0;
 606end|
 607create procedure bug8408_p()
 608select * from t1|
 609call bug8408_p()|
 610val	x
 611select bug8408_f()|
 612ERROR 0A000: Not allowed to return a result set from a function
 613drop procedure bug8408_p|
 614drop function bug8408_f|
 615create function bug8408() returns int
 616begin
 617declare n int default 0;
 618select count(*) into n from t1;
 619return n;
 620end|
 621insert into t1 value (2, 2.7), (3, 3.14), (7, 7.0)|
 622select *,bug8408() from t1|
 623val	x	bug8408()
 6242	2.7	3
 6253	3.14	3
 6267	7	3
 627drop function bug8408|
 628truncate table t1|
 629drop procedure if exists bug10537|
 630create procedure bug10537()
 631load data local infile '/tmp/somefile' into table t1|
 632ERROR 0A000: LOAD DATA is not allowed in stored procedures
 633drop function if exists bug8409|
 634create function bug8409()
 635returns int
 636begin
 637flush tables;
 638return 5;
 639end|
 640ERROR 0A000: FLUSH is not allowed in stored function or trigger
 641create function bug8409() returns int begin reset query cache;
 642return 1; end|
 643ERROR 0A000: RESET is not allowed in stored function or trigger
 644create function bug8409() returns int begin reset master;
 645return 1; end|
 646ERROR 0A000: RESET is not allowed in stored function or trigger
 647create function bug8409() returns int begin reset slave;
 648return 1; end|
 649ERROR 0A000: RESET is not allowed in stored function or trigger
 650create function bug8409() returns int begin flush hosts;
 651return 1; end|
 652ERROR 0A000: FLUSH is not allowed in stored function or trigger
 653create function bug8409() returns int begin flush privileges;
 654return 1; end|
 655ERROR 0A000: FLUSH is not allowed in stored function or trigger
 656create function bug8409() returns int begin flush tables with read lock;
 657return 1; end|
 658ERROR 0A000: FLUSH is not allowed in stored function or trigger
 659create function bug8409() returns int begin flush tables;
 660return 1; end|
 661ERROR 0A000: FLUSH is not allowed in stored function or trigger
 662create function bug8409() returns int begin flush logs;
 663return 1; end|
 664ERROR 0A000: FLUSH is not allowed in stored function or trigger
 665create function bug8409() returns int begin flush status;
 666return 1; end|
 667ERROR 0A000: FLUSH is not allowed in stored function or trigger
 668create function bug8409() returns int begin flush des_key_file;
 669return 1; end|
 670ERROR 0A000: FLUSH is not allowed in stored function or trigger
 671create function bug8409() returns int begin flush user_resources;
 672return 1; end|
 673ERROR 0A000: FLUSH is not allowed in stored function or trigger
 674create procedure bug9529_901234567890123456789012345678901234567890123456789012345()
 675begin
 676end|
 677ERROR 42000: Identifier name 'bug9529_901234567890123456789012345678901234567890123456789012345' is too long
 678drop procedure if exists bug17015_0123456789012345678901234567890123456789012345678901234|
 679create procedure bug17015_0123456789012345678901234567890123456789012345678901234()
 680begin
 681end|
 682show procedure status like 'bug17015%'|
 683Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
 684test	bug17015_0123456789012345678901234567890123456789012345678901234	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
 685drop procedure bug17015_0123456789012345678901234567890123456789012345678901234|
 686drop procedure if exists bug10969|
 687create procedure bug10969()
 688begin
 689declare s1 int default 0;
 690select default(s1) from t30;
 691end|
 692ERROR 42000: Incorrect column name 's1'
 693create procedure bug10969()
 694begin
 695declare s1 int default 0;
 696select default(t30.s1) from t30;
 697end|
 698drop procedure bug10969|
 699drop table t1|
 700create table t1(f1 int);
 701create table t2(f1 int);
 702CREATE PROCEDURE SP001()
 703P1: BEGIN
 704DECLARE ENDTABLE INT DEFAULT 0;
 705DECLARE TEMP_NUM INT;
 706DECLARE TEMP_SUM INT;
 707DECLARE C1 CURSOR FOR SELECT F1 FROM t1;
 708DECLARE C2 CURSOR FOR SELECT F1 FROM t2;
 709DECLARE CONTINUE HANDLER FOR NOT FOUND SET ENDTABLE = 1;
 710SET ENDTABLE=0;
 711SET TEMP_SUM=0;
 712SET TEMP_NUM=0;
 713OPEN C1;
 714FETCH C1 INTO TEMP_NUM;
 715WHILE ENDTABLE = 0 DO
 716SET TEMP_SUM=TEMP_NUM+TEMP_SUM;
 717FETCH C1 INTO TEMP_NUM;
 718END WHILE;
 719SELECT TEMP_SUM;
 720CLOSE C1;
 721CLOSE C1;
 722SELECT 'end of proc';
 723END P1|
 724call SP001();
 725TEMP_SUM
 7260
 727ERROR 24000: Cursor is not open
 728drop procedure SP001;
 729drop table t1, t2;
 730drop function if exists bug11394|
 731drop function if exists bug11394_1|
 732drop function if exists bug11394_2|
 733drop procedure if exists bug11394|
 734create function bug11394(i int) returns int
 735begin
 736if i <= 0 then
 737return 0;
 738else
 739return (i in (100, 200, bug11394(i-1), 400));
 740end if;
 741end|
 742select bug11394(2)|
 743ERROR HY000: Recursive stored functions and triggers are not allowed.
 744drop function bug11394|
 745create function bug11394_1(i int) returns int
 746begin
 747if i <= 0 then
 748return 0;
 749else
 750return (select bug11394_1(i-1));
 751end if;
 752end|
 753select bug11394_1(2)|
 754ERROR HY000: Recursive stored functions and triggers are not allowed.
 755drop function bug11394_1|
 756create function bug11394_2(i int) returns int return i|
 757select bug11394_2(bug11394_2(10))|
 758bug11394_2(bug11394_2(10))
 75910
 760drop function bug11394_2|
 761create procedure bug11394(i int, j int)
 762begin
 763if i > 0 then
 764call bug11394(i - 1,(select 1));
 765end if;
 766end|
 767call bug11394(2, 1)|
 768ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine bug11394
 769set @@max_sp_recursion_depth=10|
 770call bug11394(2, 1)|
 771set @@max_sp_recursion_depth=default|
 772drop procedure bug11394|
 773CREATE PROCEDURE BUG_12490() HELP CONTENTS;
 774ERROR 0A000: HELP is not allowed in stored procedures
 775CREATE FUNCTION BUG_12490() RETURNS INT HELP CONTENTS;
 776ERROR 0A000: HELP is not allowed in stored procedures
 777CREATE TABLE t_bug_12490(a int);
 778CREATE TRIGGER BUG_12490 BEFORE UPDATE ON t_bug_12490 FOR EACH ROW HELP CONTENTS;
 779ERROR 0A000: HELP is not allowed in stored procedures
 780DROP TABLE t_bug_12490;
 781drop function if exists bug11834_1;
 782drop function if exists bug11834_2;
 783create function bug11834_1() returns int return 10;
 784create function bug11834_2() returns int return bug11834_1();
 785prepare stmt from "select bug11834_2()";
 786execute stmt;
 787bug11834_2()
 78810
 789execute stmt;
 790bug11834_2()
 79110
 792drop function bug11834_1;
 793execute stmt;
 794ERROR 42000: FUNCTION test.bug11834_1 does not exist
 795deallocate prepare stmt;
 796drop function bug11834_2;
 797DROP FUNCTION IF EXISTS bug12953|
 798CREATE FUNCTION bug12953() RETURNS INT
 799BEGIN
 800OPTIMIZE TABLE t1;
 801RETURN 1;
 802END|
 803ERROR 0A000: Not allowed to return a result set from a function
 804DROP FUNCTION IF EXISTS bug12995|
 805CREATE FUNCTION bug12995() RETURNS INT
 806BEGIN
 807HANDLER t1 OPEN;
 808RETURN 1;
 809END|
 810ERROR 0A000: HANDLER is not allowed in stored procedures
 811CREATE FUNCTION bug12995() RETURNS INT
 812BEGIN
 813HANDLER t1 READ FIRST;
 814RETURN 1;
 815END|
 816ERROR 0A000: HANDLER is not allowed in stored procedures
 817CREATE FUNCTION bug12995() RETURNS INT
 818BEGIN
 819HANDLER t1 CLOSE;
 820RETURN 1;
 821END|
 822ERROR 0A000: HANDLER is not allowed in stored procedures
 823SELECT bug12995()|
 824ERROR 42000: FUNCTION test.bug12995 does not exist
 825drop procedure if exists bug12712;
 826drop function if exists bug12712;
 827create procedure bug12712()
 828set session autocommit = 0;
 829select @@autocommit;
 830@@autocommit
 8311
 832set @au = @@autocommit;
 833call bug12712();
 834select @@autocommit;
 835@@autocommit
 8360
 837set session autocommit = @au;
 838create function bug12712()
 839returns int
 840begin
 841call bug12712();
 842return 0;
 843end|
 844set @x = bug12712()|
 845ERROR HY000: Not allowed to set autocommit from a stored function or trigger
 846drop procedure bug12712|
 847drop function bug12712|
 848create function bug12712()
 849returns int
 850begin
 851set session autocommit = 0;
 852return 0;
 853end|
 854ERROR HY000: Not allowed to set autocommit from a stored function or trigger
 855create function bug12712()
 856returns int
 857begin
 858set @@autocommit = 0;
 859return 0;
 860end|
 861ERROR HY000: Not allowed to set autocommit from a stored function or trigger
 862create function bug12712()
 863returns int
 864begin
 865set local autocommit = 0;
 866return 0;
 867end|
 868ERROR HY000: Not allowed to set autocommit from a stored function or trigger
 869create trigger bug12712
 870before insert on t1 for each row set session autocommit = 0;
 871ERROR HY000: Not allowed to set autocommit from a stored function or trigger
 872drop procedure if exists bug13510_1|
 873drop procedure if exists bug13510_2|
 874drop procedure if exists bug13510_3|
 875drop procedure if exists bug13510_4|
 876create procedure bug13510_1()
 877begin
 878declare password varchar(10);
 879set password = 'foo1';
 880select password;
 881end|
 882ERROR 42000: Variable 'password' must be quoted with `...`, or renamed
 883set names='foo2'|
 884ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
 885create procedure bug13510_2()
 886begin
 887declare names varchar(10);
 888set names = 'foo2';
 889select names;
 890end|
 891ERROR 42000: Variable 'names' must be quoted with `...`, or renamed
 892create procedure bug13510_3()
 893begin
 894declare password varchar(10);
 895set `password` = 'foo3';
 896select password;
 897end|
 898create procedure bug13510_4()
 899begin
 900declare names varchar(10);
 901set `names` = 'foo4';
 902select names;
 903end|
 904call bug13510_3()|
 905password
 906foo3
 907call bug13510_4()|
 908names
 909foo4
 910drop procedure bug13510_3|
 911drop procedure bug13510_4|
 912drop function if exists bug_13627_f|
 913CREATE TABLE t1 (a int)|
 914CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DROP TRIGGER test1; END |
 915ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 916CREATE FUNCTION bug_13627_f() returns int BEGIN DROP TRIGGER test1; return 1; END |
 917ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 918CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create table t2 (a int); END |
 919ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 920CREATE FUNCTION bug_13627_f() returns int BEGIN create table t2 (a int); return 1; END |
 921ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 922CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create index t1_i on t1 (a); END |
 923ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 924CREATE FUNCTION bug_13627_f() returns int BEGIN create index t1_i on t1 (a); return 1; END |
 925ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 926CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter table t1 add column  b int; END |
 927ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 928CREATE FUNCTION bug_13627_f() returns int BEGIN alter table t1 add column  b int; return 1; END |
 929ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 930CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename table t1 to t2; END |
 931ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 932CREATE FUNCTION bug_13627_f() returns int BEGIN rename table t1 to t2; return 1; END |
 933ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 934CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN truncate table t1; END |
 935ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 936CREATE FUNCTION bug_13627_f() returns int BEGIN truncate table t1; return 1; END |
 937ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 938CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop table t1; END |
 939ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 940CREATE FUNCTION bug_13627_f() returns int BEGIN drop table t1; return 1; END |
 941ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 942CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop index t1_i on t1; END |
 943ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 944CREATE FUNCTION bug_13627_f() returns int BEGIN drop index t1_i on t1; return 1; END |
 945ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 946CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN unlock tables; END |
 947ERROR 0A000: UNLOCK is not allowed in stored procedures
 948CREATE FUNCTION bug_13627_f() returns int BEGIN unlock tables; return 1; END |
 949ERROR 0A000: UNLOCK is not allowed in stored procedures
 950CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN LOCK TABLE t1 READ; END |
 951ERROR 0A000: LOCK is not allowed in stored procedures
 952CREATE FUNCTION bug_13627_f() returns int BEGIN LOCK TABLE t1 READ; return 1; END |
 953ERROR 0A000: LOCK is not allowed in stored procedures
 954CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create database mysqltest; END |
 955ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 956CREATE FUNCTION bug_13627_f() returns int BEGIN create database mysqltest; return 1; END |
 957ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 958CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop database mysqltest; END |
 959ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 960CREATE FUNCTION bug_13627_f() returns int BEGIN drop database mysqltest; return 1; END |
 961ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 962CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create user 'mysqltest_1'; END |
 963ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 964CREATE FUNCTION bug_13627_f() returns int BEGIN create user 'mysqltest_1'; return 1; END |
 965ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 966CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN grant select on t1 to 'mysqltest_1'; END |
 967ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 968CREATE FUNCTION bug21975() returns int BEGIN grant select on t1 to 'mysqltest_1'; return 1; END |
 969ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 970CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke select on t1 from 'mysqltest_1'; END |
 971ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 972CREATE FUNCTION bug21975() returns int BEGIN revoke select on t1 from 'mysqltest_1'; return 1; END |
 973ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 974CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke all privileges on *.* from 'mysqltest_1'; END |
 975ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 976CREATE FUNCTION bug21975() returns int BEGIN revoke all privileges on *.* from 'mysqltest_1'; return 1; END |
 977ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 978CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop user 'mysqltest_1'; END |
 979ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 980CREATE FUNCTION bug_13627_f() returns int BEGIN drop user 'mysqltest_1'; return 1; END |
 981ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 982CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; END |
 983ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 984CREATE FUNCTION bug_13627_f() returns int BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; return 1; END |
 985ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 986CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as select 1; END |
 987ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 988CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END |
 989ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 990CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END |
 991ERROR 0A000: ALTER VIEW is not allowed in stored procedures
 992CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END |
 993ERROR 0A000: ALTER VIEW is not allowed in stored procedures
 994CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop view v1; END |
 995ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 996CREATE FUNCTION bug_13627_f() returns int BEGIN drop view v1; return 1; END |
 997ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
 998CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create trigger tr2 before insert on t1 for each row do select 1; END |
 999ERROR 2F003: Can't create a TRIGGER from within another stored routine
1000CREATE FUNCTION bug_13627_f() returns int BEGIN create trigger tr2 before insert on t1 for each row do select 1; return 1; END |
1001ERROR 2F003: Can't create a TRIGGER from within another stored routine
1002CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop function bug_13627_f; END |
1003ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine
1004CREATE FUNCTION bug_13627_f() returns int BEGIN drop function bug_13627_f; return 1; END |
1005ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine
1006CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create function f2 () returns int return 1; END |
1007ERROR 2F003: Can't create a FUNCTION from within another stored routine
1008CREATE FUNCTION bug_13627_f() returns int BEGIN create function f2 () returns int return 1; return 1; END |
1009ERROR 2F003: Can't create a FUNCTION from within another stored routine
1010CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW
1011BEGIN
1012CREATE TEMPORARY TABLE t2 (a int);
1013DROP TEMPORARY TABLE t2;
1014END |
1015CREATE FUNCTION bug_13627_f() returns int
1016BEGIN
1017CREATE TEMPORARY TABLE t2 (a int);
1018DROP TEMPORARY TABLE t2;
1019return 1;
1020END |
1021drop table t1|
1022drop function bug_13627_f|
1023drop function if exists bug12329;
1024Warnings:
1025Note	1305	FUNCTION test.bug12329 does not exist
1026create table t1 as select 1 a;
1027create table t2 as select 1 a;
1028create function bug12329() returns int return (select a from t1);
1029prepare stmt1 from 'select bug12329()';
1030execute stmt1;
1031bug12329()
10321
1033drop function bug12329;
1034create function bug12329() returns int return (select a+100 from t2);
1035select bug12329();
1036bug12329()
1037101
1038execute stmt1;
1039bug12329()
1040101
1041deallocate prepare stmt1;
1042drop function bug12329;
1043drop table t1, t2;
1044create database mysqltest1;
1045use mysqltest1;
1046drop database mysqltest1;
1047create function f1() returns int return 1;
1048ERROR 3D000: No database selected
1049create procedure p1(out param1 int)
1050begin
1051select count(*) into param1 from t3;
1052end|
1053ERROR 3D000: No database selected
1054use test;
1055DROP PROCEDURE IF EXISTS bug13037_p1;
1056DROP PROCEDURE IF EXISTS bug13037_p2;
1057DROP PROCEDURE IF EXISTS bug13037_p3;
1058CREATE PROCEDURE bug13037_p1()
1059BEGIN
1060IF bug13037_foo THEN
1061SELECT 1;
1062END IF;
1063END|
1064CREATE PROCEDURE bug13037_p2()
1065BEGIN
1066SET @bug13037_foo = bug13037_bar;
1067END|
1068CREATE PROCEDURE bug13037_p3()
1069BEGIN
1070SELECT bug13037_foo;
1071END|
1072
1073CALL bug13037_p1();
1074ERROR 42S22: Unknown column 'bug13037_foo' in 'field list'
1075CALL bug13037_p2();
1076ERROR 42S22: Unknown column 'bug13037_bar' in 'field list'
1077CALL bug13037_p3();
1078ERROR 42S22: Unknown column 'bug13037_foo' in 'field list'
1079CALL bug13037_p1();
1080ERROR 42S22: Unknown column 'bug13037_foo' in 'field list'
1081CALL bug13037_p2();
1082ERROR 42S22: Unknown column 'bug13037_bar' in 'field list'
1083CALL bug13037_p3();
1084ERROR 42S22: Unknown column 'bug13037_foo' in 'field list'
1085DROP PROCEDURE bug13037_p1;
1086DROP PROCEDURE bug13037_p2;
1087DROP PROCEDURE bug13037_p3;
1088create database mysqltest1;
1089create database mysqltest2;
1090use mysqltest1;
1091drop database mysqltest1;
1092create procedure mysqltest2.p1() select version();
1093create procedure p2() select version();
1094ERROR 3D000: No database selected
1095use mysqltest2;
1096show procedure status;
1097Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
1098mysqltest2	p1	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
1099drop database mysqltest2;
1100use test;
1101DROP FUNCTION IF EXISTS bug13012|
1102CREATE FUNCTION bug13012() RETURNS INT
1103BEGIN
1104REPAIR TABLE t1;
1105RETURN 1;
1106END|
1107ERROR 0A000: Not allowed to return a result set from a function
1108create table t1 (a int)|
1109CREATE PROCEDURE bug13012_1() REPAIR TABLE t1|
1110CREATE FUNCTION bug13012_2() RETURNS INT
1111BEGIN
1112CALL bug13012_1();
1113RETURN 1;
1114END|
1115SELECT bug13012_2()|
1116ERROR 0A000: Not allowed to return a result set from a function
1117drop table t1|
1118drop procedure bug13012_1|
1119drop function bug13012_2|
1120drop function if exists bug11555_1;
1121drop function if exists bug11555_2;
1122drop view if exists v1, v2, v3, v4;
1123create function bug11555_1() returns int return (select max(i) from t1);
1124create function bug11555_2() returns int return bug11555_1();
1125create view v1 as select bug11555_1();
1126drop view v1;
1127create view v2 as select bug11555_2();
1128drop view v2;
1129create table t1 (i int);
1130create view v1 as select bug11555_1();
1131create view v2 as select bug11555_2();
1132create view v3 as select * from v1;
1133drop table t1;
1134select * from v1;
1135ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1136select * from v2;
1137ERROR HY000: View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1138select * from v3;
1139ERROR HY000: View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1140create view v4 as select * from v1;
1141drop view v1, v2, v3, v4;
1142drop function bug11555_1;
1143drop function bug11555_2;
1144create table t1 (i int);
1145create table t2 (i int);
1146create trigger t1_ai after insert on t1 for each row insert into t2 values (new.i);
1147create view v1 as select * from t1;
1148drop table t2;
1149insert into v1 values (1);
1150ERROR 42S02: Table 'test.t2' doesn't exist
1151drop trigger t1_ai;
1152create function bug11555_1() returns int return (select max(i) from t2);
1153create trigger t1_ai after insert on t1 for each row set @a:=bug11555_1();
1154insert into v1 values (2);
1155ERROR 42S02: Table 'test.t2' doesn't exist
1156drop function bug11555_1;
1157drop table t1;
1158drop view v1;
1159drop procedure if exists ` bug15658`;
1160create procedure ``() select 1;
1161ERROR 42000: Incorrect routine name ''
1162create procedure ` `() select 1;
1163ERROR 42000: Incorrect routine name ' '
1164create procedure `bug15658 `() select 1;
1165ERROR 42000: Incorrect routine name 'bug15658 '
1166create procedure ``.bug15658() select 1;
1167ERROR 42000: Incorrect database name ''
1168create procedure `x `.bug15658() select 1;
1169ERROR 42000: Incorrect database name 'x '
1170create procedure ` bug15658`() select 1;
1171call ` bug15658`();
11721
11731
1174show procedure status;
1175Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
1176test	 bug15658	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
1177drop procedure ` bug15658`;
1178drop function if exists bug14270;
1179drop table if exists t1;
1180create table t1 (s1 int primary key);
1181create function bug14270() returns int
1182begin
1183load index into cache t1;
1184return 1;
1185end|
1186ERROR 0A000: Not allowed to return a result set from a function
1187create function bug14270() returns int
1188begin
1189cache index t1 key (`primary`) in keycache1;
1190return 1;
1191end|
1192ERROR 0A000: Not allowed to return a result set from a function
1193drop table t1;
1194drop procedure if exists bug15091;
1195create procedure bug15091()
1196begin
1197declare selectstr varchar(6000) default ' ';
1198declare conditionstr varchar(5000)  default '';
1199set selectstr = concat(selectstr,
1200' and ',
1201c.operatorid,
1202'in (',conditionstr, ')');
1203end|
1204call bug15091();
1205ERROR 42S02: Unknown table 'c' in field list
1206drop procedure bug15091;
1207drop function if exists bug16896;
1208create aggregate function bug16896() returns int return 1;
1209ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '() returns int return 1' at line 1
1210DROP PROCEDURE IF EXISTS bug14702;
1211CREATE IF NOT EXISTS PROCEDURE bug14702()
1212BEGIN
1213END;
1214ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS PROCEDURE bug14702()
1215BEGIN
1216END' at line 1
1217CREATE PROCEDURE IF NOT EXISTS bug14702()
1218BEGIN
1219END;
1220ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS bug14702()
1221BEGIN
1222END' at line 1
1223DROP TABLE IF EXISTS t1;
1224CREATE TABLE t1 (i INT);
1225CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO @a;
1226ERROR HY000: View's SELECT contains a 'INTO' clause
1227CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO DUMPFILE "file";
1228ERROR HY000: View's SELECT contains a 'INTO' clause
1229CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO OUTFILE "file";
1230ERROR HY000: View's SELECT contains a 'INTO' clause
1231CREATE PROCEDURE bug20953()
1232CREATE VIEW v AS SELECT i FROM t1 PROCEDURE ANALYSE();
1233ERROR HY000: View's SELECT contains a 'PROCEDURE' clause
1234CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1;
1235ERROR HY000: View's SELECT contains a subquery in the FROM clause
1236CREATE PROCEDURE bug20953(i INT) CREATE VIEW v AS SELECT i;
1237ERROR HY000: View's SELECT contains a variable or parameter
1238CREATE PROCEDURE bug20953()
1239BEGIN
1240DECLARE i INT;
1241CREATE VIEW v AS SELECT i;
1242END |
1243ERROR HY000: View's SELECT contains a variable or parameter
1244PREPARE stmt FROM "CREATE VIEW v AS SELECT ?";
1245ERROR HY000: View's SELECT contains a variable or parameter
1246DROP TABLE t1;
1247drop tables if exists t1;
1248drop procedure if exists bug24491;
1249create table t1 (id int primary key auto_increment, value varchar(10));
1250insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD');
1251create procedure bug24491()
1252insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on duplicate key update v = 'DUP';
1253call bug24491();
1254ERROR 42S22: Unknown column 'v' in 'field list'
1255call bug24491();
1256ERROR 42S22: Unknown column 'v' in 'field list'
1257drop procedure bug24491;
1258create procedure bug24491()
1259insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as value) as y on duplicate key update y.value = 'DUP';
1260call bug24491();
1261ERROR 42S22: Unknown column 'y.value' in 'field list'
1262call bug24491();
1263ERROR 42S22: Unknown column 'y.value' in 'field list'
1264drop procedure bug24491;
1265drop tables t1;
1266DROP FUNCTION IF EXISTS bug18914_f1;
1267DROP FUNCTION IF EXISTS bug18914_f2;
1268DROP PROCEDURE IF EXISTS bug18914_p1;
1269DROP PROCEDURE IF EXISTS bug18914_p2;
1270DROP TABLE IF EXISTS t1, t2;
1271CREATE TABLE t1 (i INT);
1272CREATE PROCEDURE bug18914_p1() CREATE TABLE t2 (i INT);
1273CREATE PROCEDURE bug18914_p2() DROP TABLE IF EXISTS no_such_table;
1274CREATE FUNCTION bug18914_f1() RETURNS INT
1275BEGIN
1276CALL bug18914_p1();
1277RETURN 1;
1278END |
1279CREATE FUNCTION bug18914_f2() RETURNS INT
1280BEGIN
1281CALL bug18914_p2();
1282RETURN 1;
1283END |
1284CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
1285CALL bug18914_p1();
1286INSERT INTO t1 VALUES (1);
1287ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
1288SELECT bug18914_f1();
1289ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
1290SELECT bug18914_f2();
1291ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
1292SELECT * FROM t2;
1293ERROR 42S02: Table 'test.t2' doesn't exist
1294DROP FUNCTION bug18914_f1;
1295DROP FUNCTION bug18914_f2;
1296DROP PROCEDURE bug18914_p1;
1297DROP PROCEDURE bug18914_p2;
1298DROP TABLE t1;
1299drop table if exists bogus_table_20713;
1300drop function if exists func_20713_a;
1301drop function if exists func_20713_b;
1302create table bogus_table_20713( id int(10) not null primary key);
1303insert into bogus_table_20713 values (1), (2), (3);
1304create function func_20713_a() returns int(11)
1305begin
1306declare id int;
1307declare continue handler for sqlexception set id=null;
1308set @in_func := 1;
1309set id = (select id from bogus_table_20713 where id = 3);
1310set @in_func := 2;
1311return id;
1312end//
1313create function func_20713_b() returns int(11)
1314begin
1315declare id int;
1316declare continue handler for sqlstate value '42S02' set id=null;
1317set @in_func := 1;
1318set id = (select id from bogus_table_20713 where id = 3);
1319set @in_func := 2;
1320return id;
1321end//
1322set @in_func := 0;
1323select func_20713_a();
1324func_20713_a()
1325NULL
1326select @in_func;
1327@in_func
13282
1329set @in_func := 0;
1330select func_20713_b();
1331func_20713_b()
1332NULL
1333select @in_func;
1334@in_func
13352
1336drop table bogus_table_20713;
1337set @in_func := 0;
1338select func_20713_a();
1339func_20713_a()
1340NULL
1341select @in_func;
1342@in_func
13432
1344set @in_func := 0;
1345select func_20713_b();
1346func_20713_b()
1347NULL
1348select @in_func;
1349@in_func
13502
1351drop function if exists func_20713_a;
1352drop function if exists func_20713_b;
1353drop table if exists table_25345_a;
1354drop table if exists table_25345_b;
1355drop procedure if exists proc_25345;
1356drop function if exists func_25345;
1357drop function if exists func_25345_b;
1358create table table_25345_a (a int);
1359create table table_25345_b (b int);
1360create procedure proc_25345()
1361begin
1362declare c1 cursor for select a from table_25345_a;
1363declare c2 cursor for select b from table_25345_b;
1364select 1 as result;
1365end ||
1366create function func_25345() returns int(11)
1367begin
1368call proc_25345();
1369return 1;
1370end ||
1371create function func_25345_b() returns int(11)
1372begin
1373declare c1 cursor for select a from table_25345_a;
1374declare c2 cursor for select b from table_25345_b;
1375return 1;
1376end ||
1377call proc_25345();
1378result
13791
1380select func_25345();
1381ERROR 0A000: Not allowed to return a result set from a function
1382select func_25345_b();
1383func_25345_b()
13841
1385drop table table_25345_a;
1386call proc_25345();
1387result
13881
1389select func_25345();
1390ERROR 0A000: Not allowed to return a result set from a function
1391select func_25345_b();
1392func_25345_b()
13931
1394drop table table_25345_b;
1395drop procedure proc_25345;
1396drop function func_25345;
1397drop function func_25345_b;
1398End of 5.0 tests
1399drop function if exists bug20701;
1400create function bug20701() returns varchar(25) binary return "test";
1401ERROR 42000: This version of MySQL doesn't yet support 'return value collation'
1402create function bug20701() returns varchar(25) return "test";
1403drop function bug20701;
1404create procedure proc_26503_error_1()
1405begin
1406retry:
1407repeat
1408begin
1409declare continue handler for sqlexception
1410begin
1411iterate retry;
1412end
1413select "do something";
1414end
1415until true end repeat retry;
1416end//
1417ERROR 42000: ITERATE with no matching label: retry
1418create procedure proc_26503_error_2()
1419begin
1420retry:
1421repeat
1422begin
1423declare continue handler for sqlexception
1424iterate retry;
1425select "do something";
1426end
1427until true end repeat retry;
1428end//
1429ERROR 42000: ITERATE with no matching label: retry
1430create procedure proc_26503_error_3()
1431begin
1432retry:
1433repeat
1434begin
1435declare continue handler for sqlexception
1436begin
1437leave retry;
1438end
1439select "do something";
1440end
1441until true end repeat retry;
1442end//
1443ERROR 42000: LEAVE with no matching label: retry
1444create procedure proc_26503_error_4()
1445begin
1446retry:
1447repeat
1448begin
1449declare continue handler for sqlexception
1450leave retry;
1451select "do something";
1452end
1453until true end repeat retry;
1454end//
1455ERROR 42000: LEAVE with no matching label: retry
1456drop procedure if exists proc_28360;
1457drop function if exists func_28360;
1458CREATE PROCEDURE proc_28360()
1459BEGIN
1460ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME;
1461END//
1462ERROR HY000: Can't drop or alter a DATABASE from within another stored routine
1463CREATE FUNCTION func_28360() RETURNS int
1464BEGIN
1465ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME;
1466RETURN 0;
1467END//
1468ERROR HY000: Can't drop or alter a DATABASE from within another stored routine
1469DROP PROCEDURE IF EXISTS p1;
1470CREATE PROCEDURE p1()
1471BEGIN
1472DECLARE c char(100);
1473DECLARE cur1 CURSOR FOR SHOW TABLES;
1474OPEN cur1;
1475FETCH cur1 INTO c;
1476select c;
1477CLOSE cur1;
1478END|
1479ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHOW TABLES;
1480OPEN cur1;
1481FETCH cur1 INTO c;
1482select c;
1483CLOSE cur1;
1484END' at line 4
1485DROP DATABASE IF EXISTS mysqltest;
1486CREATE DATABASE mysqltest;
1487USE mysqltest;
1488DROP DATABASE mysqltest;
1489SELECT inexistent(), 1 + ,;
1490ERROR 42000: FUNCTION inexistent does not exist
1491SELECT inexistent();
1492ERROR 42000: FUNCTION inexistent does not exist
1493SELECT .inexistent();
1494ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1
1495SELECT ..inexistent();
1496ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.inexistent()' at line 1
1497USE test;
1498create function f1() returns int
1499begin
1500set @test = 1, password = password('foo');
1501return 1;
1502end|
1503ERROR HY000: Not allowed to set autocommit from a stored function or trigger
1504create trigger t1
1505before insert on t2 for each row set password = password('foo');|
1506ERROR HY000: Not allowed to set autocommit from a stored function or trigger
1507drop function if exists f1;
1508drop function if exists f2;
1509drop table if exists t1, t2;
1510create function f1() returns int
1511begin
1512drop temporary table t1;
1513return 1;
1514end|
1515create temporary table t1 as select f1();
1516ERROR HY000: Can't reopen table: 't1'
1517create function f2() returns int
1518begin
1519create temporary table t2 as select f1();
1520return 1;
1521end|
1522create temporary table t1 as select f2();
1523ERROR HY000: Can't reopen table: 't1'
1524drop function f1;
1525drop function f2;
1526create function f1() returns int
1527begin
1528drop temporary table t2,t1;
1529return 1;
1530end|
1531create function f2() returns int
1532begin
1533create temporary table t2 as select f1();
1534return 1;
1535end|
1536create temporary table t1 as select f2();
1537ERROR HY000: Can't reopen table: 't2'
1538drop function f1;
1539drop function f2;
1540create temporary table t2(a int);
1541select * from t2;
1542a
1543create function f2() returns int
1544begin
1545drop temporary table t2;
1546return 1;
1547end|
1548select f2();
1549f2()
15501
1551drop function f2;
1552drop table t2;
1553ERROR 42S02: Unknown table 'test.t2'
1554End of 5.1 tests
1555drop procedure if exists proc_33983_a;
1556drop procedure if exists proc_33983_b;
1557drop procedure if exists proc_33983_c;
1558drop procedure if exists proc_33983_d;
1559create procedure proc_33983_a()
1560begin
1561label1:
1562begin
1563label2:
1564begin
1565sel…

Large files files are truncated, but you can click here to view the full file