2
drop table if exists t1,t2,t3,t4;
3
drop view if exists v1;
4
drop procedure if exists p1;
5
drop procedure if exists p2;
6
drop function if exists f1;
7
drop function if exists f2;
9
id char(16) not null default '',
17
drop procedure if exists foo42;
18
create procedure foo42()
19
insert into test.t1 values ("foo", 42);
26
drop procedure if exists bar;
27
create procedure bar(x char(16), y int)
28
insert into test.t1 values (x, y);
34
drop procedure if exists empty|
35
create procedure empty()
40
drop procedure if exists scope|
41
create procedure scope(a int, b float)
50
drop procedure if exists two|
51
create procedure two(x1 char(16), x2 char(16), y int)
53
insert into test.t1 values (x1, y);
54
insert into test.t1 values (x2, y);
56
call two("one", "two", 3)|
63
drop procedure if exists locset|
64
create procedure locset(x char(16), y int)
69
insert into test.t1 values (x, z2);
71
call locset("locset", 19)|
76
drop procedure locset|
77
drop procedure if exists setcontext|
78
create procedure setcontext()
80
declare data int default 2;
81
insert into t1 (id, data) values ("foo", 1);
82
replace t1 set data = data, id = "bar";
83
update t1 set id = "kaka", data = 3 where t1.data = data;
86
select * from t1 order by data|
91
drop procedure setcontext|
92
create table t3 ( d date, i int, f double, s varchar(32) )|
93
drop procedure if exists nullset|
94
create procedure nullset()
99
declare ls varchar(32);
100
set ld = null, li = null, lf = null, ls = null;
101
insert into t3 values (ld, li, lf, ls);
102
insert into t3 (i, f, s) values ((ld is null), 1, "ld is null"),
103
((li is null), 1, "li is null"),
104
((li = 0), null, "li = 0"),
105
((lf is null), 1, "lf is null"),
106
((lf = 0), null, "lf = 0"),
107
((ls is null), 1, "ls is null");
115
NULL NULL NULL li = 0
117
NULL NULL NULL lf = 0
120
drop procedure nullset|
121
drop procedure if exists mixset|
122
create procedure mixset(x char(16), y int)
125
set @z = y, z = 666, max_join_size = 100;
126
insert into test.t1 values (x, z);
128
call mixset("mixset", 19)|
129
show variables like 'max_join_size'|
132
select id,data,@z from t1|
136
drop procedure mixset|
137
drop procedure if exists zip|
138
create procedure zip(x char(16), y int)
144
drop procedure if exists zap|
145
create procedure zap(x int, out y int)
162
drop procedure if exists c1|
163
create procedure c1(x int)
165
drop procedure if exists c2|
166
create procedure c2(s char(16), x int)
168
drop procedure if exists c3|
169
create procedure c3(x int, s char(16))
170
call c4("level", x, s)|
171
drop procedure if exists c4|
172
create procedure c4(l char(8), x int, s char(16))
173
insert into t1 values (concat(l,s), x)|
183
drop procedure if exists iotest|
184
create procedure iotest(x1 char(16), x2 char(16), y int)
187
insert into test.t1 values (x1, y);
189
drop procedure if exists inc2|
190
create procedure inc2(x char(16), y int)
193
insert into test.t1 values (x, y);
195
drop procedure if exists inc|
196
create procedure inc(inout io int)
198
call iotest("io1", "io2", 1)|
199
select * from t1 order by data desc|
204
drop procedure iotest|
206
drop procedure if exists incr|
207
create procedure incr(inout x int)
218
drop procedure if exists cbv1|
219
create procedure cbv1()
221
declare y int default 3;
223
insert into test.t1 values ("cbv1", y);
225
drop procedure if exists cbv2|
226
create procedure cbv2(y1 int, inout y2 int)
229
insert into test.t1 values ("cbv2", y1);
232
select * from t1 order by data|
239
insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)|
240
drop procedure if exists sub1|
241
create procedure sub1(id char(16), x int)
242
insert into test.t1 values (id, x)|
243
drop procedure if exists sub2|
244
create procedure sub2(id char(16))
247
set x = (select sum(t.i) from test.t2 t);
248
insert into test.t1 values (id, x);
250
drop procedure if exists sub3|
251
create function sub3(i int) returns int deterministic
253
call sub1("sub1a", (select 7))|
254
call sub1("sub1b", (select max(i) from t2))|
255
call sub1("sub1c", (select i,d from t2 limit 1))|
256
ERROR 21000: Operand should contain 1 column(s)
257
call sub1("sub1d", (select 1 from (select 1) a))|
259
select * from t1 order by id|
265
select sub3((select max(i) from t2))|
266
sub3((select max(i) from t2))
273
drop procedure if exists a0|
274
create procedure a0(x int)
277
insert into test.t1 values ("a0", x);
280
select * from t1 order by data desc|
287
drop procedure if exists a|
288
create procedure a(x int)
291
insert into test.t1 values ("a", x);
294
select * from t1 order by data desc|
301
drop procedure if exists b|
302
create procedure b(x int)
304
insert into test.t1 values (repeat("b",3), x);
306
until x = 0 end repeat|
308
select * from t1 order by data desc|
315
drop procedure if exists b2|
316
create procedure b2(x int)
317
repeat(select 1 into outfile 'b2');
318
insert into test.t1 values (repeat("b2",3), x);
320
until x = 0 end repeat|
322
drop procedure if exists c|
323
create procedure c(x int)
325
insert into test.t1 values ("c", x);
328
insert into test.t1 values ("x", x);
331
select * from t1 order by data desc|
338
drop procedure if exists d|
339
create procedure d(x int)
341
insert into test.t1 values ("d", x);
344
insert into test.t1 values ("x", x);
352
drop procedure if exists e|
353
create procedure e(x int)
358
insert into test.t1 values ("e", x);
362
select * from t1 order by data desc|
369
drop procedure if exists f|
370
create procedure f(x int)
372
insert into test.t1 values ("f", 0);
374
insert into test.t1 values ("f", 1);
376
insert into test.t1 values ("f", 2);
381
select * from t1 order by data|
388
drop procedure if exists g|
389
create procedure g(x int)
392
insert into test.t1 values ("g", 0);
394
insert into test.t1 values ("g", 1);
396
insert into test.t1 values ("g", 2);
401
select * from t1 order by data|
408
drop procedure if exists h|
409
create procedure h(x int)
412
insert into test.t1 values ("h0", x);
414
insert into test.t1 values ("h1", x);
416
insert into test.t1 values ("h?", x);
421
select * from t1 order by data|
428
drop procedure if exists i|
429
create procedure i(x int)
435
insert into test.t1 values ("i", x);
444
insert into t1 values ("foo", 3), ("bar", 19)|
445
insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)|
446
drop procedure if exists sel1|
447
create procedure sel1()
449
select * from t1 order by data;
456
drop procedure if exists sel2|
457
create procedure sel2()
459
select * from t1 order by data;
460
select * from t2 order by s;
473
drop procedure if exists into_test|
474
create procedure into_test(x char(16), y int)
476
insert into test.t1 values (x, y);
477
select id,data into x,y from test.t1 limit 1;
478
insert into test.t1 values (concat(x, "2"), y+2);
480
call into_test("into", 100)|
481
select * from t1 order by data|
486
drop procedure into_test|
487
drop procedure if exists into_tes2|
488
create procedure into_test2(x char(16), y int)
490
insert into test.t1 values (x, y);
491
select id,data into x,@z from test.t1 limit 1;
492
insert into test.t1 values (concat(x, "2"), y+2);
494
call into_test2("into", 100)|
495
select id,data,@z from t1 order by data|
500
drop procedure into_test2|
501
drop procedure if exists into_test3|
502
create procedure into_test3()
506
select * into x,y from test.t1 limit 1;
507
insert into test.t2 values (x, y, 0.0);
509
insert into t1 values ("into3", 19)|
518
drop procedure into_test3|
519
drop procedure if exists into_test4|
520
create procedure into_test4()
523
select data into x from test.t1 limit 1;
524
insert into test.t3 values ("into4", x);
527
create table t3 ( s char(16), d int)|
530
Warning 1329 No data - zero rows fetched, selected, or processed
534
insert into t1 values ("i4", 77)|
542
drop procedure into_test4|
543
drop procedure if exists into_outfile|
544
create procedure into_outfile(x char(16), y int)
546
insert into test.t1 values (x, y);
547
select * into outfile "MYSQLTEST_VARDIR/tmp/spout" from test.t1;
548
insert into test.t1 values (concat(x, "2"), y+2);
550
call into_outfile("ofile", 1)|
552
drop procedure into_outfile|
553
drop procedure if exists into_dumpfile|
554
create procedure into_dumpfile(x char(16), y int)
556
insert into test.t1 values (x, y);
557
select * into dumpfile "MYSQLTEST_VARDIR/tmp/spdump" from test.t1 limit 1;
558
insert into test.t1 values (concat(x, "2"), y+2);
560
call into_dumpfile("dfile", 1)|
562
drop procedure into_dumpfile|
563
drop procedure if exists create_select|
564
create procedure create_select(x char(16), y int)
566
insert into test.t1 values (x, y);
567
create temporary table test.t3 select * from test.t1;
568
insert into test.t3 values (concat(x, "2"), y+2);
570
call create_select("cs", 90)|
571
select * from t1, t3|
577
drop procedure create_select|
578
drop function if exists e|
579
create function e() returns double
580
return 2.7182818284590452354|
584
2.71828182845905 2.71828182845905
585
drop function if exists inc|
586
create function inc(i int) returns int
588
select inc(1), inc(99), inc(-71)|
589
inc(1) inc(99) inc(-71)
591
drop function if exists mul|
592
create function mul(x int, y int) returns int
594
select mul(1,1), mul(3,5), mul(4711, 666)|
595
mul(1,1) mul(3,5) mul(4711, 666)
597
drop function if exists append|
598
create function append(s1 char(8), s2 char(8)) returns char(16)
599
return concat(s1, s2)|
600
select append("foo", "bar")|
603
drop function if exists fac|
604
create function fac(n int unsigned) returns bigint unsigned
606
declare f bigint unsigned default 1;
613
select fac(1), fac(2), fac(5), fac(10)|
614
fac(1) fac(2) fac(5) fac(10)
616
drop function if exists fun|
617
create function fun(d double, i int, u int unsigned) returns double
618
return mul(inc(i), fac(u)) / e()|
619
select fun(2.3, 3, 5)|
622
insert into t2 values (append("xxx", "yyy"), mul(4,3), e())|
623
insert into t2 values (append("a", "b"), mul(2,mul(3,4)), fun(1.7, 4, 6))|
624
select * from t2 where s = append("a", "b")|
626
ab 24 1324.36598821719
627
select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2) order by i|
629
xxxyyy 12 2.71828182845905
630
ab 24 1324.36598821719
631
select * from t2 where d = e()|
633
xxxyyy 12 2.71828182845905
634
select * from t2 order by i|
636
xxxyyy 12 2.71828182845905
637
ab 24 1324.36598821719
642
drop function append|
644
drop procedure if exists hndlr1|
645
create procedure hndlr1(val int)
647
declare x int default 0;
648
declare foo condition for 1136;
649
declare bar condition for sqlstate '42S98'; # Just for testing syntax
650
declare zip condition for sqlstate value '42S99'; # Just for testing syntax
651
declare continue handler for foo set x = 1;
652
insert into test.t1 values ("hndlr1", val, 2); # Too many values
654
insert into test.t1 values ("hndlr1", val); # This instead then
662
drop procedure hndlr1|
663
drop procedure if exists hndlr2|
664
create procedure hndlr2(val int)
666
declare x int default 0;
668
declare exit handler for sqlstate '21S01' set x = 1;
669
insert into test.t1 values ("hndlr2", val, 2); # Too many values
671
insert into test.t1 values ("hndlr2", x);
678
drop procedure hndlr2|
679
drop procedure if exists hndlr3|
680
create procedure hndlr3(val int)
682
declare x int default 0;
683
declare continue handler for sqlexception # Any error
693
insert into test.t1 values ("hndlr3", y, 2); # Too many values
695
insert into test.t1 values ("hndlr3", y);
705
drop procedure hndlr3|
706
create table t3 ( id char(16), data int )|
707
drop procedure if exists hndlr4|
708
create procedure hndlr4()
710
declare x int default 0;
711
declare val int; # No default
712
declare continue handler for sqlstate '02000' set x=1;
713
select data into val from test.t3 where id='z' limit 1; # No hits
714
insert into test.t3 values ('z', val);
721
drop procedure hndlr4|
722
drop procedure if exists cur1|
723
create procedure cur1()
728
declare done int default 0;
729
declare c cursor for select * from test.t2;
730
declare continue handler for sqlstate '02000' set done = 1;
733
fetch c into a, b, c;
735
insert into test.t1 values (a, b+c);
737
until done end repeat;
740
insert into t2 values ("foo", 42, -1.9), ("bar", 3, 12.1), ("zap", 666, -3.14)|
748
create table t3 ( s char(16), i int )|
749
drop procedure if exists cur2|
750
create procedure cur2()
752
declare done int default 0;
753
declare c1 cursor for select id,data from test.t1 order by id,data;
754
declare c2 cursor for select i from test.t2 order by i;
755
declare continue handler for sqlstate '02000' set done = 1;
762
fetch from c1 into a, b;
763
fetch next from c2 into c;
766
insert into test.t3 values (a, b);
768
insert into test.t3 values (a, c);
772
until done end repeat;
777
select * from t3 order by i,s|
786
drop procedure if exists chistics|
787
create procedure chistics()
792
comment 'Characteristics procedure test'
793
insert into t1 values ("chistics", 1)|
794
show create procedure chistics|
795
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
796
chistics CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`()
798
COMMENT 'Characteristics procedure test'
799
insert into t1 values ("chistics", 1) latin1 latin1_swedish_ci latin1_swedish_ci
805
alter procedure chistics sql security invoker|
806
show create procedure chistics|
807
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
808
chistics CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`()
811
COMMENT 'Characteristics procedure test'
812
insert into t1 values ("chistics", 1) latin1 latin1_swedish_ci latin1_swedish_ci
813
drop procedure chistics|
814
drop function if exists chistics|
815
create function chistics() returns int
819
comment 'Characteristics procedure test'
821
show create function chistics|
822
Function sql_mode Create Function character_set_client collation_connection Database Collation
823
chistics CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11)
826
COMMENT 'Characteristics procedure test'
827
return 42 latin1 latin1_swedish_ci latin1_swedish_ci
831
alter function chistics
833
comment 'Characteristics function test'|
834
show create function chistics|
835
Function sql_mode Create Function character_set_client collation_connection Database Collation
836
chistics CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11)
840
COMMENT 'Characteristics function test'
841
return 42 latin1 latin1_swedish_ci latin1_swedish_ci
842
drop function chistics|
843
insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)|
844
set @@sql_mode = 'ANSI'|
845
drop procedure if exists modes$
846
create procedure modes(out c1 int, out c2 int)
848
declare done int default 0;
850
declare c cursor for select data from t1;
851
declare continue handler for sqlstate '02000' set done = 1;
852
select 1 || 2 into c1;
860
until done end repeat;
864
set sql_select_limit = 1|
865
call modes(@c1, @c2)|
866
set sql_select_limit = default|
871
drop procedure modes|
872
create database sp_db1|
873
drop database sp_db1|
874
create database sp_db2|
876
create table t3 ( s char(4), t int )|
877
insert into t3 values ("abcd", 42), ("dcba", 666)|
879
drop database sp_db2|
880
create database sp_db3|
882
drop procedure if exists dummy|
883
create procedure dummy(out x int)
886
drop database sp_db3|
887
select type,db,name from mysql.proc where db = 'sp_db3'|
889
drop procedure if exists rc|
890
create procedure rc()
893
insert into t1 values ("a", 1), ("b", 2), ("c", 3);
899
update t1 set data=42 where id = "b";
917
drop function if exists f0|
918
drop function if exists f1|
919
drop function if exists f2|
920
drop function if exists f3|
921
drop function if exists f4|
922
drop function if exists f5|
923
drop function if exists f6|
924
drop function if exists f7|
925
drop function if exists f8|
926
drop function if exists f9|
927
drop function if exists f10|
928
drop function if exists f11|
929
drop function if exists f12_1|
930
drop function if exists f12_2|
931
drop view if exists v0|
932
drop view if exists v1|
933
drop view if exists v2|
936
insert into t1 values ("a", 1), ("b", 2) |
937
insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
938
create function f1() returns int
939
return (select sum(data) from t1)|
943
select id, f1() from t1 order by id|
947
create function f2() returns int
948
return (select data from t1 where data <= (select sum(data) from t1) order by data limit 1)|
952
select id, f2() from t1 order by id|
956
create function f3() returns int
960
set n:= (select min(data) from t1);
961
set m:= (select max(data) from t1);
967
select id, f3() from t1 order by id|
974
select id, f1(), f3() from t1 order by id|
978
create function f4() returns double
979
return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")|
983
select s, f4() from t2 order by s|
988
create function f5(i int) returns int
993
return (select count(*) from t1 where data = i);
995
return (select count(*) + f5( i - 1) from t1 where data = i);
1002
ERROR HY000: Recursive stored functions and triggers are not allowed.
1004
ERROR HY000: Recursive stored functions and triggers are not allowed.
1005
create function f6() returns int
1009
return (select count(*) from t1 where data <= f7() and data <= n);
1011
create function f7() returns int
1012
return (select sum(data) from t1 where data <= f1())|
1016
select id, f6() from t1 order by id|
1020
create view v1 (a) as select f1()|
1024
select id, a from t1, v1 order by id|
1028
select * from v1, v1 as v|
1031
create view v2 (a) as select a*10 from v1|
1035
select id, a from t1, v2 order by id|
1039
select * from v1, v2|
1042
create function f8 () returns int
1043
return (select count(*) from v2)|
1044
select *, f8() from v1|
1049
ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
1050
create function f1() returns int
1051
return (select sum(data) from t1) + (select sum(data) from v1)|
1053
ERROR HY000: Recursive stored functions and triggers are not allowed.
1055
ERROR HY000: Recursive stored functions and triggers are not allowed.
1057
ERROR HY000: Recursive stored functions and triggers are not allowed.
1059
create function f1() returns int
1060
return (select sum(data) from t1)|
1061
create function f0() returns int
1062
return (select * from (select 100) as r)|
1066
select *, f0() from (select 1) as t|
1069
create view v0 as select f0()|
1073
select *, f0() from v0|
1076
lock tables t1 read, t1 as t11 read|
1080
select id, f3() from t1 as t11 order by id|
1090
select *, f0() from v0, (select 123) as d1|
1093
select id, f3() from t1|
1094
ERROR HY000: Table 't1' was not locked with LOCK TABLES
1096
ERROR HY000: Table 't2' was not locked with LOCK TABLES
1098
lock tables v2 read, mysql.proc read|
1105
select * from v1, t1|
1106
ERROR HY000: Table 't1' was not locked with LOCK TABLES
1108
ERROR HY000: Table 't2' was not locked with LOCK TABLES
1110
create function f9() returns int
1113
drop temporary table if exists t3;
1114
create temporary table t3 (id int);
1115
insert into t3 values (1), (2), (3);
1116
set a:= (select count(*) from t3);
1117
set b:= (select count(*) from t3 t3_alias);
1124
Note 1051 Unknown table 't3'
1125
select f9() from t1 limit 1|
1128
create function f10() returns int
1130
drop temporary table if exists t3;
1131
create temporary table t3 (id int);
1132
insert into t3 select id from t4;
1133
return (select count(*) from t3);
1136
ERROR 42S02: Table 'test.t4' doesn't exist
1137
create table t4 as select 1 as id|
1141
create function f11() returns int
1143
drop temporary table if exists t3;
1144
create temporary table t3 (id int);
1145
insert into t3 values (1), (2), (3);
1146
return (select count(*) from t3 as a, t3 as b);
1149
ERROR HY000: Can't reopen table: 'a'
1150
select f11() from t1|
1151
ERROR HY000: Can't reopen table: 'a'
1152
create function f12_1() returns int
1154
drop temporary table if exists t3;
1155
create temporary table t3 (id int);
1156
insert into t3 values (1), (2), (3);
1159
create function f12_2() returns int
1160
return (select count(*) from t3)|
1161
drop temporary table t3|
1166
Note 1051 Unknown table 't3'
1167
select f12_1() from t1 limit 1|
1182
drop function f12_1|
1183
drop function f12_2|
1190
drop table if exists t3|
1191
create table t3 (n int unsigned not null primary key, f bigint unsigned)|
1192
drop procedure if exists ifac|
1193
create procedure ifac(n int unsigned)
1195
declare i int unsigned default 1;
1197
set n = 20; # bigint overflow otherwise
1201
insert into test.t3 values (i, fac(i));
1227
19 121645100408832000
1228
20 2432902008176640000
1230
show function status like '%f%'|
1231
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
1232
test fac FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
1233
drop procedure ifac|
1235
show function status like '%f%'|
1236
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
1237
drop table if exists t3|
1239
i int unsigned not null primary key,
1240
p bigint unsigned not null
1242
insert into t3 values
1243
( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13),
1244
( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31),
1245
(10, 37), (11, 41), (12, 43), (13, 47), (14, 53),
1246
(15, 59), (16, 61), (17, 67), (18, 71), (19, 73),
1247
(20, 79), (21, 83), (22, 89), (23, 97), (24, 101),
1248
(25, 103), (26, 107), (27, 109), (28, 113), (29, 127),
1249
(30, 131), (31, 137), (32, 139), (33, 149), (34, 151),
1250
(35, 157), (36, 163), (37, 167), (38, 173), (39, 179),
1251
(40, 181), (41, 191), (42, 193), (43, 197), (44, 199)|
1252
drop procedure if exists opp|
1253
create procedure opp(n bigint unsigned, out pp bool)
1256
declare b, s bigint unsigned default 0;
1261
set b = b+200, s = 0;
1264
declare p bigint unsigned;
1265
select t.p into p from test.t3 t where t.i = s;
1270
if mod(n, b+p) = 0 then
1279
drop procedure if exists ip|
1280
create procedure ip(m int unsigned)
1282
declare p bigint unsigned;
1283
declare i int unsigned;
1287
declare pp bool default 0;
1290
insert into test.t3 values (i, p);
1297
show create procedure opp|
1298
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
1299
opp CREATE DEFINER=`root`@`localhost` PROCEDURE `opp`(n bigint unsigned, out pp bool)
1302
declare b, s bigint unsigned default 0;
1307
set b = b+200, s = 0;
1310
declare p bigint unsigned;
1311
select t.p into p from test.t3 t where t.i = s;
1316
if mod(n, b+p) = 0 then
1324
end latin1 latin1_swedish_ci latin1_swedish_ci
1325
show procedure status where name like '%p%' and db='test'|
1326
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
1327
test ip PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
1328
test opp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
1330
select * from t3 where i=45 or i=100 or i=199|
1338
show procedure status where name like '%p%' and db='test'|
1339
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
1340
drop procedure if exists bar|
1341
create procedure bar(x char(16), y int)
1342
comment "111111111111" sql security invoker
1343
insert into test.t1 values (x, y)|
1344
show procedure status like 'bar'|
1345
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
1346
test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER 111111111111 latin1 latin1_swedish_ci latin1_swedish_ci
1347
alter procedure bar comment "2222222222" sql security definer|
1348
alter procedure bar comment "3333333333"|
1349
alter procedure bar|
1350
show create procedure bar|
1351
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
1352
bar CREATE DEFINER=`root`@`localhost` PROCEDURE `bar`(x char(16), y int)
1353
COMMENT '3333333333'
1354
insert into test.t1 values (x, y) latin1 latin1_swedish_ci latin1_swedish_ci
1355
show procedure status like 'bar'|
1356
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
1357
test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER 3333333333 latin1 latin1_swedish_ci latin1_swedish_ci
1359
drop procedure if exists p1|
1360
create procedure p1 ()
1361
select (select s1 from t3) from t3|
1362
create table t3 (s1 int)|
1365
insert into t3 values (1)|
1371
drop function if exists foo|
1372
create function `foo` () returns int
1377
drop function `foo`|
1378
drop function if exists t1max|
1379
create function t1max() returns int
1382
select max(data) into x from t1;
1385
insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)|
1389
drop function t1max|
1391
v char(16) not null primary key,
1392
c int unsigned not null
1394
create function getcount(s char(16)) returns int
1397
select count(*) into x from t3 where v = s;
1399
insert into t3 values (s, 1);
1401
update t3 set c = c+1 where v = s;
1405
select * from t1 where data = getcount("bar")|
1411
select getcount("zip")|
1414
select getcount("zip")|
1421
select getcount(id) from t1 where data = 3|
1424
select getcount(id) from t1 where data = 5|
1433
drop function getcount|
1434
drop table if exists t3|
1435
drop procedure if exists h_ee|
1436
drop procedure if exists h_es|
1437
drop procedure if exists h_en|
1438
drop procedure if exists h_ew|
1439
drop procedure if exists h_ex|
1440
drop procedure if exists h_se|
1441
drop procedure if exists h_ss|
1442
drop procedure if exists h_sn|
1443
drop procedure if exists h_sw|
1444
drop procedure if exists h_sx|
1445
drop procedure if exists h_ne|
1446
drop procedure if exists h_ns|
1447
drop procedure if exists h_nn|
1448
drop procedure if exists h_we|
1449
drop procedure if exists h_ws|
1450
drop procedure if exists h_ww|
1451
drop procedure if exists h_xe|
1452
drop procedure if exists h_xs|
1453
drop procedure if exists h_xx|
1454
create table t3 (a smallint primary key)|
1455
insert into t3 (a) values (1)|
1456
create procedure h_ee()
1459
declare continue handler for 1062 -- ER_DUP_ENTRY
1460
select 'Outer (bad)' as 'h_ee';
1462
declare continue handler for 1062 -- ER_DUP_ENTRY
1463
select 'Inner (good)' as 'h_ee';
1464
insert into t3 values (1);
1467
create procedure h_es()
1470
declare continue handler for 1062 -- ER_DUP_ENTRY
1471
select 'Outer (good)' as 'h_es';
1473
-- integrity constraint violation
1474
declare continue handler for sqlstate '23000'
1475
select 'Inner (bad)' as 'h_es';
1476
insert into t3 values (1);
1479
create procedure h_en()
1482
declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA
1483
select 'Outer (good)' as 'h_en';
1486
declare continue handler for sqlstate '02000' -- no data
1487
select 'Inner (bad)' as 'h_en';
1488
select a into x from t3 where a = 42;
1491
create procedure h_ew()
1494
declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE
1495
select 'Outer (good)' as 'h_ew';
1497
declare continue handler for sqlwarning
1498
select 'Inner (bad)' as 'h_ew';
1499
insert into t3 values (123456789012);
1502
insert into t3 values (1);
1504
create procedure h_ex()
1507
declare continue handler for 1062 -- ER_DUP_ENTRY
1508
select 'Outer (good)' as 'h_ex';
1510
declare continue handler for sqlexception
1511
select 'Inner (bad)' as 'h_ex';
1512
insert into t3 values (1);
1515
create procedure h_se()
1518
-- integrity constraint violation
1519
declare continue handler for sqlstate '23000'
1520
select 'Outer (bad)' as 'h_se';
1522
declare continue handler for 1062 -- ER_DUP_ENTRY
1523
select 'Inner (good)' as 'h_se';
1524
insert into t3 values (1);
1527
create procedure h_ss()
1530
-- integrity constraint violation
1531
declare continue handler for sqlstate '23000'
1532
select 'Outer (bad)' as 'h_ss';
1534
-- integrity constraint violation
1535
declare continue handler for sqlstate '23000'
1536
select 'Inner (good)' as 'h_ss';
1537
insert into t3 values (1);
1540
create procedure h_sn()
1543
-- Note: '02000' is more specific than NOT FOUND ;
1544
-- there might be other not found states
1545
declare continue handler for sqlstate '02000' -- no data
1546
select 'Outer (good)' as 'h_sn';
1549
declare continue handler for not found
1550
select 'Inner (bad)' as 'h_sn';
1551
select a into x from t3 where a = 42;
1554
create procedure h_sw()
1557
-- data exception - numeric value out of range
1558
declare continue handler for sqlstate '22003'
1559
select 'Outer (good)' as 'h_sw';
1561
declare continue handler for sqlwarning
1562
select 'Inner (bad)' as 'h_sw';
1563
insert into t3 values (123456789012);
1566
insert into t3 values (1);
1568
create procedure h_sx()
1571
-- integrity constraint violation
1572
declare continue handler for sqlstate '23000'
1573
select 'Outer (good)' as 'h_sx';
1575
declare continue handler for sqlexception
1576
select 'Inner (bad)' as 'h_sx';
1577
insert into t3 values (1);
1580
create procedure h_ne()
1583
declare continue handler for not found
1584
select 'Outer (bad)' as 'h_ne';
1587
declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA
1588
select 'Inner (good)' as 'h_ne';
1589
select a into x from t3 where a = 42;
1592
create procedure h_ns()
1595
declare continue handler for not found
1596
select 'Outer (bad)' as 'h_ns';
1599
declare continue handler for sqlstate '02000' -- no data
1600
select 'Inner (good)' as 'h_ns';
1601
select a into x from t3 where a = 42;
1604
create procedure h_nn()
1607
declare continue handler for not found
1608
select 'Outer (bad)' as 'h_nn';
1611
declare continue handler for not found
1612
select 'Inner (good)' as 'h_nn';
1613
select a into x from t3 where a = 42;
1616
create procedure h_we()
1619
declare continue handler for sqlwarning
1620
select 'Outer (bad)' as 'h_we';
1622
declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE
1623
select 'Inner (good)' as 'h_we';
1624
insert into t3 values (123456789012);
1627
insert into t3 values (1);
1629
create procedure h_ws()
1632
declare continue handler for sqlwarning
1633
select 'Outer (bad)' as 'h_ws';
1635
-- data exception - numeric value out of range
1636
declare continue handler for sqlstate '22003'
1637
select 'Inner (good)' as 'h_ws';
1638
insert into t3 values (123456789012);
1641
insert into t3 values (1);
1643
create procedure h_ww()
1646
declare continue handler for sqlwarning
1647
select 'Outer (bad)' as 'h_ww';
1649
declare continue handler for sqlwarning
1650
select 'Inner (good)' as 'h_ww';
1651
insert into t3 values (123456789012);
1654
insert into t3 values (1);
1656
create procedure h_xe()
1659
declare continue handler for sqlexception
1660
select 'Outer (bad)' as 'h_xe';
1662
declare continue handler for 1062 -- ER_DUP_ENTRY
1663
select 'Inner (good)' as 'h_xe';
1664
insert into t3 values (1);
1667
create procedure h_xs()
1670
declare continue handler for sqlexception
1671
select 'Outer (bad)' as 'h_xs';
1673
-- integrity constraint violation
1674
declare continue handler for sqlstate '23000'
1675
select 'Inner (good)' as 'h_xs';
1676
insert into t3 values (1);
1679
create procedure h_xx()
1682
declare continue handler for sqlexception
1683
select 'Outer (bad)' as 'h_xx';
1685
declare continue handler for sqlexception
1686
select 'Inner (good)' as 'h_xx';
1687
insert into t3 values (1);
1748
drop procedure h_ee|
1749
drop procedure h_es|
1750
drop procedure h_en|
1751
drop procedure h_ew|
1752
drop procedure h_ex|
1753
drop procedure h_se|
1754
drop procedure h_ss|
1755
drop procedure h_sn|
1756
drop procedure h_sw|
1757
drop procedure h_sx|
1758
drop procedure h_ne|
1759
drop procedure h_ns|
1760
drop procedure h_nn|
1761
drop procedure h_we|
1762
drop procedure h_ws|
1763
drop procedure h_ww|
1764
drop procedure h_xe|
1765
drop procedure h_xs|
1766
drop procedure h_xx|
1767
drop procedure if exists bug822|
1768
create procedure bug822(a_id char(16), a_data int)
1771
select count(*) into n from t1 where id = a_id and data = a_data;
1773
insert into t1 (id, data) values (a_id, a_data);
1777
call bug822('foo', 42)|
1778
call bug822('foo', 42)|
1779
call bug822('bar', 666)|
1780
select * from t1 order by data|
1785
drop procedure bug822|
1786
drop procedure if exists bug1495|
1787
create procedure bug1495()
1790
select data into x from t1 order by id limit 1;
1792
insert into t1 values ("less", x-10);
1794
insert into t1 values ("more", x+10);
1797
insert into t1 values ('foo', 12)|
1799
delete from t1 where id='foo'|
1800
insert into t1 values ('bar', 7)|
1802
delete from t1 where id='bar'|
1803
select * from t1 order by data|
1808
drop procedure bug1495|
1809
drop procedure if exists bug1547|
1810
create procedure bug1547(s char(16))
1813
select data into x from t1 where s = id limit 1;
1815
insert into t1 values ("less", x-10);
1817
insert into t1 values ("more", x+10);
1820
insert into t1 values ("foo", 12), ("bar", 7)|
1821
call bug1547("foo")|
1822
call bug1547("bar")|
1823
select * from t1 order by id|
1830
drop procedure bug1547|
1831
drop table if exists t70|
1832
create table t70 (s1 int,s2 int)|
1833
insert into t70 values (1,2)|
1834
drop procedure if exists bug1656|
1835
create procedure bug1656(out p1 int, out p2 int)
1836
select * into p1, p1 from t70|
1837
call bug1656(@1, @2)|
1842
drop procedure bug1656|
1843
create table t3(a int)|
1844
drop procedure if exists bug1862|
1845
create procedure bug1862()
1847
insert into t3 values(2);
1857
drop procedure bug1862|
1858
drop procedure if exists bug1874|
1859
create procedure bug1874()
1863
select max(data) into x from t1;
1864
insert into t2 values ("max", x, 0);
1865
select min(data) into x from t1;
1866
insert into t2 values ("min", x, 0);
1867
select sum(data) into x from t1;
1868
insert into t2 values ("sum", x, 0);
1869
select avg(data) into y from t1;
1870
insert into t2 values ("avg", 0, y);
1872
insert into t1 (data) values (3), (1), (5), (9), (4)|
1874
select * from t2 order by i|
1882
drop procedure bug1874|
1883
drop procedure if exists bug2260|
1884
create procedure bug2260()
1887
declare c1 cursor for select data from t1;
1888
declare continue handler for not found set @x2 = 1;
1898
drop procedure bug2260|
1899
drop procedure if exists bug2267_1|
1900
create procedure bug2267_1()
1902
show procedure status where db='test';
1904
drop procedure if exists bug2267_2|
1905
create procedure bug2267_2()
1907
show function status where db='test';
1909
drop procedure if exists bug2267_3|
1910
create procedure bug2267_3()
1912
show create procedure bug2267_1;
1914
drop procedure if exists bug2267_4|
1915
drop function if exists bug2267_4|
1916
create procedure bug2267_4()
1918
show create function bug2267_4;
1920
create function bug2267_4() returns int return 100|
1922
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
1923
test bug2267_1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
1924
test bug2267_2 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
1925
test bug2267_3 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
1926
test bug2267_4 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
1928
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
1929
test bug2267_4 FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
1931
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
1932
bug2267_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug2267_1`()
1934
show procedure status where db='test';
1935
end latin1 latin1_swedish_ci latin1_swedish_ci
1937
Function sql_mode Create Function character_set_client collation_connection Database Collation
1938
bug2267_4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug2267_4`() RETURNS int(11)
1939
return 100 latin1 latin1_swedish_ci latin1_swedish_ci
1940
drop procedure bug2267_1|
1941
drop procedure bug2267_2|
1942
drop procedure bug2267_3|
1943
drop procedure bug2267_4|
1944
drop function bug2267_4|
1945
drop procedure if exists bug2227|
1946
create procedure bug2227(x int)
1948
declare y float default 2.6;
1949
declare z char(16) default "zzz";
1950
select 1.3, x, y, 42, z;
1955
drop procedure bug2227|
1956
drop procedure if exists bug2614|
1957
create procedure bug2614()
1959
drop table if exists t3;
1960
create table t3 (id int default '0' not null);
1961
insert into t3 select 12;
1962
insert into t3 select * from t3;
1967
drop procedure bug2614|
1968
drop function if exists bug2674|
1969
create function bug2674() returns int
1970
return @@sort_buffer_size|
1971
set @osbs = @@sort_buffer_size|
1972
set @@sort_buffer_size = 262000|
1976
drop function bug2674|
1977
set @@sort_buffer_size = @osbs|
1978
drop procedure if exists bug3259_1 |
1979
create procedure bug3259_1 () begin end|
1980
drop procedure if exists BUG3259_2 |
1981
create procedure BUG3259_2 () begin end|
1982
drop procedure if exists Bug3259_3 |
1983
create procedure Bug3259_3 () begin end|
1990
drop procedure bUg3259_1|
1991
drop procedure BuG3259_2|
1992
drop procedure BUG3259_3|
1993
drop function if exists bug2772|
1994
create function bug2772() returns char(10) character set latin2
1999
drop function bug2772|
2000
drop procedure if exists bug2776_1|
2001
create procedure bug2776_1(out x int)
2007
drop procedure if exists bug2776_2|
2008
create procedure bug2776_2(out x int)
2010
declare v int default 42;
2023
drop procedure bug2776_1|
2024
drop procedure bug2776_2|
2025
create table t3 (s1 smallint)|
2026
insert into t3 values (123456789012)|
2028
Warning 1264 Out of range value for column 's1' at row 1
2029
drop procedure if exists bug2780|
2030
create procedure bug2780()
2032
declare exit handler for sqlwarning set @x = 1;
2034
insert into t3 values (123456789012);
2035
insert into t3 values (0);
2045
drop procedure bug2780|
2047
create table t3 (content varchar(10) )|
2048
insert into t3 values ("test1")|
2049
insert into t3 values ("test2")|
2050
create table t4 (f1 int, rc int, t3 int)|
2051
drop procedure if exists bug1863|
2052
create procedure bug1863(in1 int)
2054
declare ind int default 0;
2058
declare rc int default 0;
2059
declare continue handler for 1065 set rc = 1;
2060
drop temporary table if exists temp_t1;
2061
create temporary table temp_t1 (
2062
f1 int auto_increment, f2 varchar(20), primary key (f1)
2064
insert into temp_t1 (f2) select content from t3;
2065
select f2 into t3 from temp_t1 where f1 = 10;
2067
insert into t4 values (1, rc, t3);
2069
insert into t4 values (2, rc, t3);
2073
Note 1051 Unknown table 'temp_t1'
2074
Warning 1329 No data - zero rows fetched, selected, or processed
2077
Warning 1329 No data - zero rows fetched, selected, or processed
2082
drop procedure bug1863|
2083
drop temporary table temp_t1;
2086
OrderID int not null,
2088
primary key (OrderID)
2091
MarketID int not null,
2094
primary key (MarketID)
2096
insert t3 (OrderID,MarketID) values (1,1)|
2097
insert t3 (OrderID,MarketID) values (2,2)|
2098
insert t4 (MarketID,Market,Status) values (1,"MarketID One","A")|
2099
insert t4 (MarketID,Market,Status) values (2,"MarketID Two","A")|
2100
drop procedure if exists bug2656_1|
2101
create procedure bug2656_1()
2106
ON o.MarketID != 1 and o.MarketID = m.MarketID;
2108
drop procedure if exists bug2656_2|
2109
create procedure bug2656_2()
2116
m.MarketID != 1 and m.MarketID = o.MarketID;
2130
drop procedure bug2656_1|
2131
drop procedure bug2656_2|
2133
drop procedure if exists bug3426|
2134
create procedure bug3426(in_time int unsigned, out x int)
2136
if in_time is null then
2137
set @stamped_time=10;
2140
set @stamped_time=in_time;
2144
set time_zone='+03:00';
2145
call bug3426(1000, @i)|
2146
select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
2148
2 01-01-1970 03:16:40
2149
call bug3426(NULL, @i)|
2150
select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
2152
1 01-01-1970 03:00:10
2153
alter procedure bug3426 sql security invoker|
2154
call bug3426(NULL, @i)|
2155
select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
2157
1 01-01-1970 03:00:10
2158
call bug3426(1000, @i)|
2159
select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
2161
2 01-01-1970 03:16:40
2162
drop procedure bug3426|
2164
id int unsigned auto_increment not null primary key,
2167
fulltext (title,body)
2169
insert into t3 (title,body) values
2170
('MySQL Tutorial','DBMS stands for DataBase ...'),
2171
('How To Use MySQL Well','After you went through a ...'),
2172
('Optimizing MySQL','In this tutorial we will show ...'),
2173
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
2174
('MySQL vs. YourSQL','In the following database comparison ...'),
2175
('MySQL Security','When configured properly, MySQL ...')|
2176
drop procedure if exists bug3734 |
2177
create procedure bug3734 (param1 varchar(100))
2178
select * from t3 where match (title,body) against (param1)|
2179
call bug3734('database')|
2181
5 MySQL vs. YourSQL In the following database comparison ...
2182
1 MySQL Tutorial DBMS stands for DataBase ...
2183
call bug3734('Security')|
2185
6 MySQL Security When configured properly, MySQL ...
2186
drop procedure bug3734|
2188
drop procedure if exists bug3863|
2189
create procedure bug3863()
2204
drop procedure bug3863|
2206
id int(10) unsigned not null default 0,
2207
rid int(10) unsigned not null default 0,
2210
unique key rid (rid, id)
2212
drop procedure if exists bug2460_1|
2213
create procedure bug2460_1(in v int)
2215
( select n0.id from t3 as n0 where n0.id = v )
2217
( select n0.id from t3 as n0, t3 as n1
2218
where n0.id = n1.rid and n1.id = v )
2220
( select n0.id from t3 as n0, t3 as n1, t3 as n2
2221
where n0.id = n1.rid and n1.id = n2.rid and n2.id = v );
2227
insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')|
2236
drop procedure if exists bug2460_2|
2237
create procedure bug2460_2()
2239
drop table if exists t3;
2240
create temporary table t3 (s1 int);
2241
insert into t3 select 1 union select 1;
2248
drop procedure bug2460_1|
2249
drop procedure bug2460_2|
2251
set @@sql_mode = ''|
2252
drop procedure if exists bug2564_1|
2253
create procedure bug2564_1()
2254
comment 'Joe''s procedure'
2255
insert into `t1` values ("foo", 1)|
2256
set @@sql_mode = 'ANSI_QUOTES'|
2257
drop procedure if exists bug2564_2|
2258
create procedure bug2564_2()
2259
insert into "t1" values ('foo', 1)|
2260
set @@sql_mode = ''$
2261
drop function if exists bug2564_3$
2262
create function bug2564_3(x int, y int) returns int
2264
set @@sql_mode = 'ANSI'$
2265
drop function if exists bug2564_4$
2266
create function bug2564_4(x int, y int) returns int
2268
set @@sql_mode = ''|
2269
show create procedure bug2564_1|
2270
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
2271
bug2564_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug2564_1`()
2272
COMMENT 'Joe''s procedure'
2273
insert into `t1` values ("foo", 1) latin1 latin1_swedish_ci latin1_swedish_ci
2274
show create procedure bug2564_2|
2275
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
2276
bug2564_2 ANSI_QUOTES CREATE DEFINER="root"@"localhost" PROCEDURE "bug2564_2"()
2277
insert into "t1" values ('foo', 1) latin1 latin1_swedish_ci latin1_swedish_ci
2278
show create function bug2564_3|
2279
Function sql_mode Create Function character_set_client collation_connection Database Collation
2280
bug2564_3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug2564_3`(x int, y int) RETURNS int(11)
2281
return x || y latin1 latin1_swedish_ci latin1_swedish_ci
2282
show create function bug2564_4|
2283
Function sql_mode Create Function character_set_client collation_connection Database Collation
2284
bug2564_4 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI CREATE DEFINER="root"@"localhost" FUNCTION "bug2564_4"(x int, y int) RETURNS int(11)
2285
return x || y latin1 latin1_swedish_ci latin1_swedish_ci
2286
drop procedure bug2564_1|
2287
drop procedure bug2564_2|
2288
drop function bug2564_3|
2289
drop function bug2564_4|
2290
drop function if exists bug3132|
2291
create function bug3132(s char(20)) returns char(50)
2292
return concat('Hello, ', s, '!')|
2293
select bug3132('Bob') union all select bug3132('Judy')|
2297
drop function bug3132|
2298
drop procedure if exists bug3843|
2299
create procedure bug3843()
2302
Table Op Msg_type Msg_text
2303
test.t1 analyze status OK
2305
Table Op Msg_type Msg_text
2306
test.t1 analyze status Table is already up to date
2310
drop procedure bug3843|
2311
create table t3 ( s1 char(10) )|
2312
insert into t3 values ('a'), ('b')|
2313
drop procedure if exists bug3368|
2314
create procedure bug3368(v char(10))
2316
select group_concat(v) from t3;
2324
drop procedure bug3368|
2326
create table t3 (f1 int, f2 int)|
2327
insert into t3 values (1,1)|
2328
drop procedure if exists bug4579_1|
2329
create procedure bug4579_1 ()
2332
select f1 into sf1 from t3 where f1=1 and f2=1;
2333
update t3 set f2 = f2 + 1 where f1=1 and f2=1;
2336
drop procedure if exists bug4579_2|
2337
create procedure bug4579_2 ()
2343
Warning 1329 No data - zero rows fetched, selected, or processed
2346
Warning 1329 No data - zero rows fetched, selected, or processed
2347
drop procedure bug4579_1|
2348
drop procedure bug4579_2|
2350
drop procedure if exists bug2773|
2351
create function bug2773() returns int return null|
2352
create table t3 as select bug2773()|
2353
show create table t3|
2355
t3 CREATE TABLE `t3` (
2356
`bug2773()` int(11) DEFAULT NULL
2357
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2359
drop function bug2773|
2360
drop procedure if exists bug3788|
2361
create function bug3788() returns date return cast("2005-03-04" as date)|
2365
drop function bug3788|
2366
create function bug3788() returns binary(1) return 5|
2370
drop function bug3788|
2371
create table t3 (f1 int, f2 int, f3 int)|
2372
insert into t3 values (1,1,1)|
2373
drop procedure if exists bug4726|
2374
create procedure bug4726()
2376
declare tmp_o_id INT;
2377
declare tmp_d_id INT default 1;
2378
while tmp_d_id <= 2 do
2380
select f1 into tmp_o_id from t3 where f2=1 and f3=1;
2381
set tmp_d_id = tmp_d_id + 1;
2388
drop procedure bug4726|
2390
drop procedure if exists bug4902|
2391
create procedure bug4902()
2393
show charset like 'foo';
2394
show collation like 'foo';
2396
show create table t1;
2397
show create database test;
2398
show databases like 'foo';
2400
show columns from t1;
2402
show open tables like 'foo';
2403
# Removed because result will differ in embedded mode.
2405
show status like 'foo';
2406
show tables like 'foo';
2407
show variables like 'foo';
2411
Charset Description Default collation Maxlen
2412
Collation Charset Id Default Compiled Sortlen
2413
Type Size Min_Value Max_Value Prec Scale Nullable Auto_Increment Unsigned Zerofill Searchable Case_Sensitive Default Comment
2414
tinyint 1 -128 127 0 0 YES YES NO YES YES NO NULL,0 A very small integer
2415
tinyint unsigned 1 0 255 0 0 YES YES YES YES YES NO NULL,0 A very small integer
2417
t1 CREATE TABLE `t1` (
2418
`id` char(16) NOT NULL DEFAULT '',
2419
`data` int(11) NOT NULL
2420
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2421
Database Create Database
2422
test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */
2425
Field Type Null Key Default Extra
2427
data int(11) NO NULL
2428
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
2429
Database Table In_use Name_locked
2431
Tables_in_test (foo)
2435
Charset Description Default collation Maxlen
2436
Collation Charset Id Default Compiled Sortlen
2437
Type Size Min_Value Max_Value Prec Scale Nullable Auto_Increment Unsigned Zerofill Searchable Case_Sensitive Default Comment
2438
tinyint 1 -128 127 0 0 YES YES NO YES YES NO NULL,0 A very small integer
2439
tinyint unsigned 1 0 255 0 0 YES YES YES YES YES NO NULL,0 A very small integer
2441
t1 CREATE TABLE `t1` (
2442
`id` char(16) NOT NULL DEFAULT '',
2443
`data` int(11) NOT NULL
2444
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2445
Database Create Database
2446
test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */
2449
Field Type Null Key Default Extra
2451
data int(11) NO NULL
2452
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
2453
Database Table In_use Name_locked
2455
Tables_in_test (foo)
2458
drop procedure bug4902|
2459
drop procedure if exists bug4904|
2460
create procedure bug4904()
2462
declare continue handler for sqlstate 'HY000' begin end;
2463
create table t2 as select * from t3;
2466
ERROR 42S02: Table 'test.t3' doesn't exist
2467
drop procedure bug4904|
2468
create table t3 (s1 char character set latin1, s2 char character set latin2)|
2469
drop procedure if exists bug4904|
2470
create procedure bug4904 ()
2472
declare continue handler for sqlstate 'HY000' begin end;
2473
select s1 from t3 union select s2 from t3;
2476
drop procedure bug4904|
2478
drop procedure if exists bug336|
2479
create procedure bug336(out y int)
2482
set x = (select sum(t.data) from test.t1 t);
2485
insert into t1 values ("a", 2), ("b", 3)|
2491
drop procedure bug336|
2492
drop procedure if exists bug3157|
2493
create procedure bug3157()
2495
if exists(select * from t1) then
2498
if (select count(*) from t1) then
2503
insert into t1 values ("a", 1)|
2509
drop procedure bug3157|
2510
drop procedure if exists bug5251|
2511
create procedure bug5251()
2514
select created into @c1 from mysql.proc
2515
where db='test' and name='bug5251'|
2516
alter procedure bug5251 comment 'foobar'|
2517
select count(*) from mysql.proc
2518
where db='test' and name='bug5251' and created = @c1|
2521
drop procedure bug5251|
2522
drop procedure if exists bug5251|
2523
create procedure bug5251()
2531
drop procedure bug5251|
2532
drop procedure if exists bug5287|
2533
create procedure bug5287(param1 int)
2536
declare c cursor for select 5;
2544
drop procedure bug5287|
2545
drop procedure if exists bug5307|
2546
create procedure bug5307()
2553
drop procedure bug5307|
2554
drop procedure if exists bug5258|
2555
create procedure bug5258()
2558
drop procedure if exists bug5258_aux|
2559
create procedure bug5258_aux()
2561
declare c, m char(19);
2562
select created,modified into c,m from mysql.proc where name = 'bug5258';
2572
drop procedure bug5258|
2573
drop procedure bug5258_aux|
2574
drop function if exists bug4487|
2575
create function bug4487() returns char
2583
drop function bug4487|
2584
drop procedure if exists bug4941|
2585
drop procedure if exists bug4941|
2586
create procedure bug4941(out x int)
2588
declare c cursor for select i from t2 limit 1;
2593
insert into t2 values (null, null, null)|
2600
drop procedure bug4941|
2601
drop procedure if exists bug4905|
2602
create table t3 (s1 int,primary key (s1))|
2603
drop procedure if exists bug4905|
2604
create procedure bug4905()
2607
declare continue handler for sqlstate '23000' set v = 5;
2608
insert into t3 values (1);
2625
drop procedure bug4905|
2627
drop procedure if exists bug6029|
2628
drop procedure if exists bug6029|
2629
create procedure bug6029()
2631
declare exit handler for 1136 select '1136';
2632
declare exit handler for sqlstate '23000' select 'sqlstate 23000';
2633
declare continue handler for sqlexception select 'sqlexception';
2634
insert into t3 values (1);
2635
insert into t3 values (1,2);
2637
create table t3 (s1 int, primary key (s1))|
2638
insert into t3 values (1)|
2646
drop procedure bug6029|
2648
drop procedure if exists bug8540|
2649
create procedure bug8540()
2651
declare x int default 1;
2652
select x as y, x+0 as z;
2657
drop procedure bug8540|
2658
create table t3 (s1 int)|
2659
drop procedure if exists bug6642|
2660
create procedure bug6642()
2661
select abs(count(s1)) from t3|
2668
drop procedure bug6642|
2669
insert into t3 values (0),(1)|
2670
drop procedure if exists bug7013|
2671
create procedure bug7013()
2672
select s1,count(s1) from t3 group by s1 with rollup|
2683
drop procedure bug7013|
2684
drop table if exists t4|
2686
a mediumint(8) unsigned not null auto_increment,
2687
b smallint(5) unsigned not null,
2688
c char(32) not null,
2690
) engine=myisam default charset=latin1|
2691
insert into t4 values (1, 2, 'oneword')|
2692
insert into t4 values (2, 2, 'anotherword')|
2693
drop procedure if exists bug7743|
2694
create procedure bug7743 ( searchstring char(28) )
2696
declare var mediumint(8) unsigned;
2697
select a into var from t4 where b = 2 and c = binary searchstring limit 1;
2700
call bug7743("oneword")|
2703
call bug7743("OneWord")|
2707
Warning 1329 No data - zero rows fetched, selected, or processed
2708
call bug7743("anotherword")|
2711
call bug7743("AnotherWord")|
2715
Warning 1329 No data - zero rows fetched, selected, or processed
2716
drop procedure bug7743|
2719
insert into t3 values(1)|
2720
drop procedure if exists bug7992_1|
2722
Note 1305 PROCEDURE bug7992_1 does not exist
2723
drop procedure if exists bug7992_2|
2725
Note 1305 PROCEDURE bug7992_2 does not exist
2726
create procedure bug7992_1()
2729
select max(s1)+1 into i from t3;
2731
create procedure bug7992_2()
2732
insert into t3 (s1) select max(t4.s1)+1 from t3 as t4|
2737
drop procedure bug7992_1|
2738
drop procedure bug7992_2|
2740
create table t3 ( userid bigint(20) not null default 0 )|
2741
drop procedure if exists bug8116|
2742
create procedure bug8116(in _userid int)
2743
select * from t3 where userid = _userid|
2748
drop procedure bug8116|
2750
drop procedure if exists bug6857|
2751
create procedure bug6857(counter int)
2754
declare plus bool default 0;
2755
set t0 = current_time();
2756
while counter > 0 do
2757
set counter = counter - 1;
2759
set t1 = current_time();
2765
drop procedure bug6857|
2766
drop procedure if exists bug8757|
2767
create procedure bug8757()
2770
declare c1 cursor for select data from t1 limit 1;
2773
declare c2 cursor for select i from t2 limit 1;
2786
insert into t1 values ("x", 1)|
2787
insert into t2 values ("y", 2, 0.0)|
2795
drop procedure bug8757|
2796
drop procedure if exists bug8762|
2797
drop procedure if exists bug8762; create procedure bug8762() begin end|
2798
drop procedure if exists bug8762; create procedure bug8762() begin end|
2799
drop procedure bug8762|
2800
drop function if exists bug5240|
2801
create function bug5240 () returns int
2804
declare c cursor for select data from t1 limit 1;
2811
insert into t1 values ("answer", 42)|
2812
select id, bug5240() from t1|
2815
drop function bug5240|
2816
drop procedure if exists p1|
2817
create table t3(id int)|
2818
insert into t3 values(1)|
2819
create procedure bug7992()
2822
select max(id)+1 into i from t3;
2826
drop procedure bug7992|
2829
lpitnumber int(11) default null,
2830
lrecordtype int(11) default null
2833
lbsiid int(11) not null default '0',
2834
ltradingmodeid int(11) not null default '0',
2835
ltradingareaid int(11) not null default '0',
2836
csellingprice decimal(19,4) default null,
2837
primary key (lbsiid,ltradingmodeid,ltradingareaid)
2840
lbsiid int(11) not null default '0',
2841
ltradingareaid int(11) not null default '0',
2842
primary key (lbsiid,ltradingareaid)
2844
drop procedure if exists bug8849|
2845
create procedure bug8849()
2852
select distinct t3.lpitnumber, t4.ltradingareaid
2855
t3.lpitnumber = t4.lbsiid
2856
and t3.lrecordtype = 1
2857
left join t4 as price01 on
2858
price01.lbsiid = t4.lbsiid and
2859
price01.ltradingmodeid = 1 and
2860
t4.ltradingareaid = price01.ltradingareaid;
2865
drop procedure bug8849|
2866
drop tables t3,t4,t5|
2867
drop procedure if exists bug8937|
2868
create procedure bug8937()
2870
declare s,x,y,z int;
2872
select sum(data),avg(data),min(data),max(data) into s,x,y,z from t1;
2874
select avg(data) into a from t1;
2878
insert into t1 (data) values (1), (2), (3), (4), (6)|
2884
drop procedure bug8937|
2886
drop procedure if exists bug6900|
2887
drop procedure if exists bug9074|
2888
drop procedure if exists bug6900_9074|
2889
create table t3 (w char unique, x char)|
2890
insert into t3 values ('a', 'b')|
2891
create procedure bug6900()
2893
declare exit handler for sqlexception select '1';
2895
declare exit handler for sqlexception select '2';
2896
insert into t3 values ('x', 'y', 'z');
2899
create procedure bug9074()
2901
declare x1, x2, x3, x4, x5, x6 int default 0;
2903
declare continue handler for sqlstate '23000' set x5 = 1;
2904
insert into t3 values ('a', 'b');
2909
declare continue handler for sqlstate '23000' set x1 = 1;
2910
insert into t3 values ('a', 'b');
2914
declare exit handler for sqlstate '23000' set x3 = 1;
2916
insert into t3 values ('a','b');
2920
select x1, x2, x3, x4, x5, x6;
2922
create procedure bug6900_9074(z int)
2924
declare exit handler for sqlstate '23000' select '23000';
2926
declare exit handler for sqlexception select 'sqlexception';
2928
insert into t3 values ('a', 'b');
2930
insert into t3 values ('x', 'y', 'z');
2940
call bug6900_9074(0)|
2943
call bug6900_9074(1)|
2946
drop procedure bug6900|
2947
drop procedure bug9074|
2948
drop procedure bug6900_9074|
2950
drop procedure if exists avg|
2951
create procedure avg ()
2956
drop procedure if exists bug6129|
2957
set @old_mode= @@sql_mode;
2958
set @@sql_mode= "ERROR_FOR_DIVISION_BY_ZERO";
2959
create procedure bug6129()
2963
ERROR_FOR_DIVISION_BY_ZERO
2964
set @@sql_mode= "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"|
2967
ERROR_FOR_DIVISION_BY_ZERO
2968
set @@sql_mode= "NO_ZERO_IN_DATE"|
2971
ERROR_FOR_DIVISION_BY_ZERO
2972
set @@sql_mode=@old_mode;
2973
drop procedure bug6129|
2974
drop procedure if exists bug9856|
2975
create procedure bug9856()
2978
declare c cursor for select data from t1;
2979
declare exit handler for sqlexception, not found select '16';
2991
drop procedure bug9856|
2992
drop procedure if exists bug9674_1|
2993
drop procedure if exists bug9674_2|
2994
create procedure bug9674_1(out arg int)
2996
declare temp_in1 int default 0;
2997
declare temp_fl1 int default 0;
2999
set temp_fl1 = temp_in1/10;
3002
create procedure bug9674_2()
3004
declare v int default 100;
3007
call bug9674_1(@sptmp)|
3008
call bug9674_1(@sptmp)|
3018
drop procedure bug9674_1|
3019
drop procedure bug9674_2|
3020
drop procedure if exists bug9598_1|
3021
drop procedure if exists bug9598_2|
3022
create procedure bug9598_1(in var_1 char(16),
3023
out var_2 integer, out var_3 integer)
3028
create procedure bug9598_2(in v1 char(16),
3034
select v1,v2,v3,v4,v5;
3035
call bug9598_1(v1,@tmp1,@tmp2);
3036
select v1,v2,v3,v4,v5;
3038
call bug9598_2('Test',2,3,4,5)|
3043
select @tmp1, @tmp2|
3046
drop procedure bug9598_1|
3047
drop procedure bug9598_2|
3048
drop procedure if exists bug9902|
3049
create function bug9902() returns int(11)
3054
set @qcs1 = @@query_cache_size|
3055
set global query_cache_size = 100000|
3057
insert into t1 values ("qc", 42)|
3058
select bug9902() from t1|
3061
select bug9902() from t1|
3067
set global query_cache_size = @qcs1|
3069
drop function bug9902|
3070
drop function if exists bug9102|
3071
create function bug9102() returns blob return 'a'|
3075
drop function bug9102|
3076
drop function if exists bug7648|
3077
create function bug7648() returns bit(8) return 'a'|
3081
drop function bug7648|
3082
drop function if exists bug9775|
3083
create function bug9775(v1 char(1)) returns enum('a','b') return v1|
3084
select bug9775('a'),bug9775('b'),bug9775('c')|
3085
bug9775('a') bug9775('b') bug9775('c')
3088
Warning 1265 Data truncated for column 'bug9775('c')' at row 1
3089
drop function bug9775|
3090
create function bug9775(v1 int) returns enum('a','b') return v1|
3091
select bug9775(1),bug9775(2),bug9775(3)|
3092
bug9775(1) bug9775(2) bug9775(3)
3095
Warning 1265 Data truncated for column 'bug9775(3)' at row 1
3096
drop function bug9775|
3097
create function bug9775(v1 char(1)) returns set('a','b') return v1|
3098
select bug9775('a'),bug9775('b'),bug9775('a,b'),bug9775('c')|
3099
bug9775('a') bug9775('b') bug9775('a,b') bug9775('c')
3102
Warning 1265 Data truncated for column 'v1' at row 1
3103
Warning 1265 Data truncated for column 'bug9775('c')' at row 1
3104
drop function bug9775|
3105
create function bug9775(v1 int) returns set('a','b') return v1|
3106
select bug9775(1),bug9775(2),bug9775(3),bug9775(4)|
3107
bug9775(1) bug9775(2) bug9775(3) bug9775(4)
3110
Warning 1265 Data truncated for column 'bug9775(4)' at row 1
3111
drop function bug9775|
3112
drop function if exists bug8861|
3113
create function bug8861(v1 int) returns year return v1|
3117
set @x = bug8861(05)|
3121
drop function bug8861|
3122
drop procedure if exists bug9004_1|
3123
drop procedure if exists bug9004_2|
3124
create procedure bug9004_1(x char(16))
3126
insert into t1 values (x, 42);
3127
insert into t1 values (x, 17);
3129
create procedure bug9004_2(x char(16))
3131
call bug9004_1('12345678901234567')|
3133
Warning 1265 Data truncated for column 'x' at row 1
3134
call bug9004_2('12345678901234567890')|
3136
Warning 1265 Data truncated for column 'x' at row 1
3138
drop procedure bug9004_1|
3139
drop procedure bug9004_2|
3140
drop procedure if exists bug7293|
3141
insert into t1 values ('secret', 0)|
3142
create procedure bug7293(p1 varchar(100))
3144
if exists (select id from t1 where soundex(p1)=soundex(id)) then
3148
call bug7293('secret')|
3151
call bug7293 ('secrete')|
3154
drop procedure bug7293|
3156
drop procedure if exists bug9841|
3157
drop view if exists v1|
3158
create view v1 as select * from t1, t2 where id = s|
3159
create procedure bug9841 ()
3160
update v1 set data = 10|
3163
drop procedure bug9841|
3164
drop procedure if exists bug5963|
3165
create procedure bug5963_1 () begin declare v int; set v = (select s1 from t3); select v; end;|
3166
create table t3 (s1 int)|
3167
insert into t3 values (5)|
3174
drop procedure bug5963_1|
3176
create procedure bug5963_2 (cfk_value int)
3178
if cfk_value in (select cpk from t3) then
3183
create table t3 (cpk int)|
3184
insert into t3 values (1)|
3187
drop procedure bug5963_2|
3189
drop function if exists bug9559|
3190
create function bug9559()
3199
drop function bug9559|
3200
drop procedure if exists bug10961|
3201
create procedure bug10961()
3205
declare c cursor for select * from dual;
3206
declare continue handler for sqlexception select x;
3228
drop procedure bug10961|
3229
DROP PROCEDURE IF EXISTS bug6866|
3230
DROP VIEW IF EXISTS tv|
3232
Note 1051 Unknown table 'test.tv'
3233
DROP TABLE IF EXISTS tt1,tt2,tt3|
3235
Note 1051 Unknown table 'tt1'
3236
Note 1051 Unknown table 'tt2'
3237
Note 1051 Unknown table 'tt3'
3238
CREATE TABLE tt1 (a1 int, a2 int, a3 int, data varchar(10))|
3239
CREATE TABLE tt2 (a2 int, data2 varchar(10))|
3240
CREATE TABLE tt3 (a3 int, data3 varchar(10))|
3241
INSERT INTO tt1 VALUES (1, 1, 4, 'xx')|
3242
INSERT INTO tt2 VALUES (1, 'a')|
3243
INSERT INTO tt2 VALUES (2, 'b')|
3244
INSERT INTO tt2 VALUES (3, 'c')|
3245
INSERT INTO tt3 VALUES (4, 'd')|
3246
INSERT INTO tt3 VALUES (5, 'e')|
3247
INSERT INTO tt3 VALUES (6, 'f')|
3249
SELECT tt1.*, tt2.data2, tt3.data3
3250
FROM tt1 INNER JOIN tt2 ON tt1.a2 = tt2.a2
3251
LEFT JOIN tt3 ON tt1.a3 = tt3.a3
3252
ORDER BY tt1.a1, tt2.a2, tt3.a3|
3253
CREATE PROCEDURE bug6866 (_a1 int)
3255
SELECT * FROM tv WHERE a1 = _a1;
3258
a1 a2 a3 data data2 data3
3261
a1 a2 a3 data data2 data3
3264
a1 a2 a3 data data2 data3
3266
DROP PROCEDURE bug6866;
3268
DROP TABLE tt1, tt2, tt3|
3269
DROP PROCEDURE IF EXISTS bug10136|
3270
create table t3 ( name char(5) not null primary key, val float not null)|
3271
insert into t3 values ('aaaaa', 1), ('bbbbb', 2), ('ccccc', 3)|
3272
create procedure bug10136()
3274
declare done int default 3;
3277
set done = done - 1;
3278
until done <= 0 end repeat;
3319
drop procedure bug10136|
3321
drop procedure if exists bug11529|
3322
create procedure bug11529()
3324
declare c cursor for select id, data from t1 where data in (10,13);
3327
declare vid char(16);
3329
declare exit handler for not found begin end;
3331
fetch c into vid, vdata;
3336
insert into t1 values
3345
drop procedure bug11529|
3346
drop procedure if exists bug6063|
3347
drop procedure if exists bug7088_1|
3348
drop procedure if exists bug7088_2|
3349
drop procedure if exists bug9565_sub|
3350
drop procedure if exists bug9565|
3351
create procedure bug9565_sub()
3355
create procedure bug9565()
3357
insert into t1 values ("one", 1);
3364
drop procedure bug9565_sub|
3365
drop procedure bug9565|
3366
drop procedure if exists bug9538|
3367
create procedure bug9538()
3368
set @@sort_buffer_size = 1000000|
3369
set @x = @@sort_buffer_size|
3370
set @@sort_buffer_size = 2000000|
3371
select @@sort_buffer_size|
3375
select @@sort_buffer_size|
3378
set @@sort_buffer_size = @x|
3379
drop procedure bug9538|
3380
drop procedure if exists bug8692|
3381
create table t3 (c1 varchar(5), c2 char(5), c3 enum('one','two'), c4 text, c5 blob, c6 char(5), c7 varchar(5))|
3382
insert into t3 values ('', '', '', '', '', '', NULL)|
3384
Warning 1265 Data truncated for column 'c3' at row 1
3385
create procedure bug8692()
3387
declare v1 VARCHAR(10);
3388
declare v2 VARCHAR(10);
3389
declare v3 VARCHAR(10);
3390
declare v4 VARCHAR(10);
3391
declare v5 VARCHAR(10);
3392
declare v6 VARCHAR(10);
3393
declare v7 VARCHAR(10);
3394
declare c8692 cursor for select c1,c2,c3,c4,c5,c6,c7 from t3;
3396
fetch c8692 into v1,v2,v3,v4,v5,v6,v7;
3397
select v1, v2, v3, v4, v5, v6, v7;
3400
v1 v2 v3 v4 v5 v6 v7
3402
drop procedure bug8692|
3404
drop function if exists bug10055|
3405
create function bug10055(v char(255)) returns char(255) return lower(v)|
3406
select t.column_name, bug10055(t.column_name)
3407
from information_schema.columns as t
3408
where t.table_schema = 'test' and t.table_name = 't1'|
3409
column_name bug10055(t.column_name)
3412
drop function bug10055|
3413
drop procedure if exists bug12297|
3414
create procedure bug12297(lim int)
3418
insert into t1(id,data)
3425
drop procedure bug12297|
3426
drop function if exists f_bug11247|
3427
drop procedure if exists p_bug11247|
3428
create function f_bug11247(param int)
3431
create procedure p_bug11247(lim int)
3433
declare v int default 0;
3435
set v= f_bug11247(v);
3438
call p_bug11247(10)|
3439
drop function f_bug11247|
3440
drop procedure p_bug11247|
3441
drop procedure if exists bug12168|
3442
drop table if exists t3, t4|
3443
create table t3 (a int)|
3444
insert into t3 values (1),(2),(3),(4)|
3445
create table t4 (a int)|
3446
create procedure bug12168(arg1 char(1))
3448
declare b, c integer;
3451
declare c1 cursor for select a from t3 where a % 2;
3452
declare continue handler for not found set b = 1;
3460
insert into t4 values (c);
3467
declare c2 cursor for select a from t3 where not a % 2;
3468
declare continue handler for not found set b = 1;
3476
insert into t4 values (c);
3507
drop procedure if exists bug12168|
3508
drop table if exists t3|
3509
drop procedure if exists bug11333|
3510
create table t3 (c1 char(128))|
3511
insert into t3 values
3512
('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')|
3513
create procedure bug11333(i int)
3515
declare tmp varchar(128);
3518
select c1 into tmp from t3
3519
where c1 = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
3525
drop procedure bug11333|
3527
drop function if exists bug9048|
3528
create function bug9048(f1 char binary) returns char
3530
set f1= concat( 'hello', f1 );
3533
drop function bug9048|
3534
create function bug9048(f1 char binary) returns char binary
3536
set f1= concat( 'hello', f1 );
3539
ERROR 42000: This version of MySQL doesn't yet support 'return value collation'
3540
drop procedure if exists bug12849_1|
3541
create procedure bug12849_1(inout x char) select x into x|
3543
call bug12849_1(@var)|
3547
drop procedure bug12849_1|
3548
drop procedure if exists bug12849_2|
3549
create procedure bug12849_2(inout foo varchar(15))
3551
select concat(foo, foo) INTO foo;
3554
call bug12849_2(@var)|
3558
drop procedure bug12849_2|
3559
drop procedure if exists bug131333|
3560
drop function if exists bug131333|
3561
create procedure bug131333()
3574
create function bug131333()
3596
drop procedure bug131333|
3597
drop function bug131333|
3598
drop function if exists bug12379|
3599
drop procedure if exists bug12379_1|
3600
drop procedure if exists bug12379_2|
3601
drop procedure if exists bug12379_3|
3602
drop table if exists t3|
3603
create table t3 (c1 char(1) primary key not null)|
3604
create function bug12379()
3607
insert into t3 values('X');
3608
insert into t3 values('X');
3611
create procedure bug12379_1()
3613
declare exit handler for sqlexception select 42;
3616
create procedure bug12379_2()
3618
declare exit handler for sqlexception begin end;
3621
create procedure bug12379_3()
3626
ERROR 23000: Duplicate entry 'X' for key 'PRIMARY'
3643
ERROR 23000: Duplicate entry 'X' for key 'PRIMARY'
3647
drop function bug12379|
3648
drop procedure bug12379_1|
3649
drop procedure bug12379_2|
3650
drop procedure bug12379_3|
3652
drop procedure if exists bug13124|
3653
create procedure bug13124()
3659
drop procedure bug13124|
3660
drop procedure if exists bug12979_1|
3661
create procedure bug12979_1(inout d decimal(5)) set d = d / 2|
3662
set @bug12979_user_var = NULL|
3663
call bug12979_1(@bug12979_user_var)|
3664
drop procedure bug12979_1|
3665
drop procedure if exists bug12979_2|
3666
create procedure bug12979_2()
3668
declare internal_var decimal(5);
3669
set internal_var= internal_var / 2;
3670
select internal_var;
3675
drop procedure bug12979_2|
3676
drop table if exists t3|
3677
drop procedure if exists bug6127|
3678
create table t3 (s1 int unique)|
3680
set sql_mode='traditional'|
3681
create procedure bug6127()
3683
declare continue handler for sqlstate '23000'
3685
declare continue handler for sqlstate '22003'
3686
insert into t3 values (0);
3687
insert into t3 values (1000000000000000);
3689
insert into t3 values (1);
3690
insert into t3 values (1);
3698
ERROR 23000: Duplicate entry '0' for key 's1'
3705
drop procedure bug6127|
3706
drop procedure if exists bug12589_1|
3707
drop procedure if exists bug12589_2|
3708
drop procedure if exists bug12589_3|
3709
create procedure bug12589_1()
3711
declare spv1 decimal(3,3);
3714
create temporary table tm1 as select spv1;
3715
show create table tm1;
3716
drop temporary table tm1;
3718
create procedure bug12589_2()
3720
declare spv1 decimal(6,3);
3722
create temporary table tm1 as select spv1;
3723
show create table tm1;
3724
drop temporary table tm1;
3726
create procedure bug12589_3()
3728
declare spv1 decimal(6,3);
3730
create temporary table tm1 as select spv1;
3731
show create table tm1;
3732
drop temporary table tm1;
3736
tm1 CREATE TEMPORARY TABLE `tm1` (
3737
`spv1` decimal(3,3) DEFAULT NULL
3738
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3740
Warning 1264 Out of range value for column 'spv1' at row 1
3741
Warning 1366 Incorrect decimal value: 'test' for column 'spv1' at row 1
3744
tm1 CREATE TEMPORARY TABLE `tm1` (
3745
`spv1` decimal(6,3) DEFAULT NULL
3746
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3749
tm1 CREATE TEMPORARY TABLE `tm1` (
3750
`spv1` decimal(6,3) DEFAULT NULL
3751
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3752
drop procedure bug12589_1|
3753
drop procedure bug12589_2|
3754
drop procedure bug12589_3|
3755
drop table if exists t3|
3756
drop procedure if exists bug7049_1|
3757
drop procedure if exists bug7049_2|
3758
drop procedure if exists bug7049_3|
3759
drop procedure if exists bug7049_4|
3760
drop function if exists bug7049_1|
3761
drop function if exists bug7049_2|
3762
create table t3 ( x int unique )|
3763
create procedure bug7049_1()
3765
insert into t3 values (42);
3766
insert into t3 values (42);
3768
create procedure bug7049_2()
3770
declare exit handler for sqlexception
3771
select 'Caught it' as 'Result';
3773
select 'Missed it' as 'Result';
3775
create procedure bug7049_3()
3777
create procedure bug7049_4()
3779
declare exit handler for sqlexception
3780
select 'Caught it' as 'Result';
3782
select 'Missed it' as 'Result';
3784
create function bug7049_1()
3787
insert into t3 values (42);
3788
insert into t3 values (42);
3791
create function bug7049_2()
3794
declare x int default 0;
3795
declare continue handler for sqlexception
3797
set x = bug7049_1();
3817
drop procedure bug7049_1|
3818
drop procedure bug7049_2|
3819
drop procedure bug7049_3|
3820
drop procedure bug7049_4|
3821
drop function bug7049_1|
3822
drop function bug7049_2|
3823
drop function if exists bug13941|
3824
drop procedure if exists bug13941|
3825
create function bug13941(p_input_str text)
3828
declare p_output_str text;
3829
set p_output_str = p_input_str;
3830
set p_output_str = replace(p_output_str, 'xyzzy', 'plugh');
3831
set p_output_str = replace(p_output_str, 'test', 'prova');
3832
set p_output_str = replace(p_output_str, 'this', 'questo');
3833
set p_output_str = replace(p_output_str, ' a ', 'una ');
3834
set p_output_str = replace(p_output_str, 'is', '');
3835
return p_output_str;
3837
create procedure bug13941(out sout varchar(128))
3840
set sout = ifnull(sout, 'DEF');
3842
select bug13941('this is a test')|
3843
bug13941('this is a test')
3849
drop function bug13941|
3850
drop procedure bug13941|
3851
DROP PROCEDURE IF EXISTS bug13095;
3852
DROP TABLE IF EXISTS bug13095_t1;
3853
DROP VIEW IF EXISTS bug13095_v1;
3854
CREATE PROCEDURE bug13095(tbl_name varchar(32))
3857
CONCAT("CREATE TABLE ", tbl_name, "(stuff char(15))");
3859
PREPARE stmt FROM @str;
3862
CONCAT("INSERT INTO ", tbl_name, " VALUES('row1'),('row2'),('row3')" );
3864
PREPARE stmt FROM @str;
3867
CONCAT("CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM ", tbl_name);
3869
PREPARE stmt FROM @str;
3871
SELECT * FROM bug13095_v1;
3873
"DROP VIEW bug13095_v1";
3875
PREPARE stmt FROM @str;
3878
CALL bug13095('bug13095_t1');
3880
CREATE TABLE bug13095_t1(stuff char(15))
3882
INSERT INTO bug13095_t1 VALUES('row1'),('row2'),('row3')
3884
CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM bug13095_t1
3890
DROP VIEW bug13095_v1
3891
DROP PROCEDURE IF EXISTS bug13095;
3892
DROP VIEW IF EXISTS bug13095_v1;
3893
DROP TABLE IF EXISTS bug13095_t1;
3894
drop function if exists bug14723|
3895
drop procedure if exists bug14723|
3896
/*!50003 create function bug14723()
3901
show create function bug14723;;
3902
Function sql_mode Create Function character_set_client collation_connection Database Collation
3903
bug14723 CREATE DEFINER=`root`@`localhost` FUNCTION `bug14723`() RETURNS bigint(20)
3906
end latin1 latin1_swedish_ci latin1_swedish_ci
3910
/*!50003 create procedure bug14723()
3914
show create procedure bug14723;;
3915
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
3916
bug14723 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug14723`()
3919
end latin1 latin1_swedish_ci latin1_swedish_ci
3923
drop function bug14723|
3924
drop procedure bug14723|
3925
create procedure bug14845()
3927
declare a char(255);
3928
declare done int default 0;
3929
declare c cursor for select count(*) from t1 where 1 = 0;
3930
declare continue handler for sqlstate '02000' set done = 1;
3937
until done end repeat;
3943
drop procedure bug14845|
3944
drop procedure if exists bug13549_1|
3945
drop procedure if exists bug13549_2|
3946
CREATE PROCEDURE `bug13549_2`()
3950
CREATE PROCEDURE `bug13549_1`()
3952
declare done int default 0;
3956
drop procedure bug13549_2|
3957
drop procedure bug13549_1|
3958
drop function if exists bug10100f|
3959
drop procedure if exists bug10100p|
3960
drop procedure if exists bug10100t|
3961
drop procedure if exists bug10100pt|
3962
drop procedure if exists bug10100pv|
3963
drop procedure if exists bug10100pd|
3964
drop procedure if exists bug10100pc|
3965
create function bug10100f(prm int) returns int
3968
return prm * bug10100f(prm - 1);
3972
create procedure bug10100p(prm int, inout res int)
3974
set res = res * prm;
3976
call bug10100p(prm - 1, res);
3979
create procedure bug10100t(prm int)
3983
call bug10100p(prm, res);
3986
create table t3 (a int)|
3987
insert into t3 values (0)|
3988
create view v1 as select a from t3|
3989
create procedure bug10100pt(level int, lim int)
3992
update t3 set a=level;
3994
call bug10100pt(level+1, lim);
3999
create procedure bug10100pv(level int, lim int)
4002
update v1 set a=level;
4004
call bug10100pv(level+1, lim);
4009
prepare stmt2 from "select * from t3;"|
4010
create procedure bug10100pd(level int, lim int)
4014
prepare stmt1 from "update t3 set a=a+2";
4021
deallocate prepare stmt1;
4024
call bug10100pd(level+1, lim);
4029
create procedure bug10100pc(level int, lim int)
4032
declare c cursor for select a from t3;
4038
update t3 set a=level+lv;
4040
call bug10100pc(level+1, lim);
4046
set @@max_sp_recursion_depth=4|
4047
select @@max_sp_recursion_depth|
4048
@@max_sp_recursion_depth
4050
select bug10100f(3)|
4051
ERROR HY000: Recursive stored functions and triggers are not allowed.
4052
select bug10100f(6)|
4053
ERROR HY000: Recursive stored functions and triggers are not allowed.
4057
call bug10100pt(1,5)|
4060
call bug10100pv(1,5)|
4064
call bug10100pd(1,5)|
4095
call bug10100pc(1,5)|
4117
set @@max_sp_recursion_depth=0|
4118
select @@max_sp_recursion_depth|
4119
@@max_sp_recursion_depth
4121
select bug10100f(5)|
4122
ERROR HY000: Recursive stored functions and triggers are not allowed.
4124
ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine bug10100p
4125
deallocate prepare stmt2|
4126
drop function bug10100f|
4127
drop procedure bug10100p|
4128
drop procedure bug10100t|
4129
drop procedure bug10100pt|
4130
drop procedure bug10100pv|
4131
drop procedure bug10100pd|
4132
drop procedure bug10100pc|
4134
drop procedure if exists bug13729|
4135
drop table if exists t3|
4136
create table t3 (s1 int, primary key (s1))|
4137
insert into t3 values (1),(2)|
4138
create procedure bug13729()
4140
declare continue handler for sqlexception select 55;
4141
update t3 set s1 = 1;
4150
drop procedure bug13729|
4152
drop procedure if exists bug14643_1|
4153
drop procedure if exists bug14643_2|
4154
create procedure bug14643_1()
4156
declare continue handler for sqlexception select 'boo' as 'Handler';
4158
declare v int default undefined_var;
4162
select v, isnull(v);
4166
create procedure bug14643_2()
4168
declare continue handler for sqlexception select 'boo' as 'Handler';
4175
select undefined_var;
4187
drop procedure bug14643_1|
4188
drop procedure bug14643_2|
4189
drop procedure if exists bug14304|
4190
drop table if exists t3, t4|
4191
create table t3(a int primary key auto_increment)|
4192
create table t4(a int primary key auto_increment)|
4193
create procedure bug14304()
4195
insert into t3 set a=null;
4196
insert into t4 set a=null;
4197
insert into t4 set a=null;
4198
insert into t4 set a=null;
4199
insert into t4 set a=null;
4200
insert into t4 set a=null;
4201
insert into t4 select null as a;
4202
insert into t3 set a=null;
4203
insert into t3 set a=null;
4211
drop procedure bug14304|
4213
drop procedure if exists bug14376|
4214
create procedure bug14376()
4216
declare x int default x;
4219
ERROR 42S22: Unknown column 'x' in 'field list'
4220
drop procedure bug14376|
4221
create procedure bug14376()
4223
declare x int default 42;
4225
declare x int default x;
4232
drop procedure bug14376|
4233
create procedure bug14376(x int)
4235
declare x int default x;
4238
call bug14376(4711)|
4241
drop procedure bug14376|
4242
drop procedure if exists bug5967|
4243
drop table if exists t3|
4244
create table t3 (a varchar(255))|
4245
insert into t3 (a) values ("a - table column")|
4246
create procedure bug5967(a varchar(255))
4248
declare i varchar(255);
4249
declare c cursor for select a from t3;
4251
select a from t3 into i;
4252
select i as 'Parameter takes precedence over table column'; open c;
4255
select i as 'Parameter takes precedence over table column in cursors';
4257
declare a varchar(255) default 'a - local variable';
4258
declare c1 cursor for select a from t3;
4259
select a as 'A local variable takes precedence over parameter';
4263
select i as 'A local variable takes precedence over parameter in cursors';
4265
declare a varchar(255) default 'a - local variable in a nested compound statement';
4266
declare c2 cursor for select a from t3;
4267
select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement';
4268
select a from t3 into i;
4269
select i as 'A local variable in a nested compound statement takes precedence over table column';
4273
select i as 'A local variable in a nested compound statement takes precedence over table column in cursors';
4277
call bug5967("a - stored procedure parameter")|
4279
a - stored procedure parameter
4280
Parameter takes precedence over table column
4281
a - stored procedure parameter
4282
Parameter takes precedence over table column in cursors
4283
a - stored procedure parameter
4284
A local variable takes precedence over parameter
4286
A local variable takes precedence over parameter in cursors
4288
A local variable in a nested compound statement takes precedence over a local variable in the outer statement
4289
a - local variable in a nested compound statement
4290
A local variable in a nested compound statement takes precedence over table column
4291
a - local variable in a nested compound statement
4292
A local variable in a nested compound statement takes precedence over table column in cursors
4293
a - local variable in a nested compound statement
4294
drop procedure bug5967|
4295
drop procedure if exists bug13012|
4296
create procedure bug13012()
4299
BACKUP TABLE t1 to '<MYSQLTEST_VARDIR>/tmp/';
4301
RESTORE TABLE t1 FROM '<MYSQLTEST_VARDIR>/tmp/';
4304
Table Op Msg_type Msg_text
4305
test.t1 repair status OK
4306
Table Op Msg_type Msg_text
4307
test.t1 backup Warning 'BACKUP TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead
4308
test.t1 backup status OK
4309
Table Op Msg_type Msg_text
4310
test.t1 restore Warning 'RESTORE TABLE' is deprecated and will be removed in a future release. Please use MySQL Administrator (mysqldump, mysql) instead
4311
test.t1 restore status OK
4312
drop procedure bug13012|
4313
create view v1 as select * from t1|
4314
create procedure bug13012()
4316
REPAIR TABLE t1,t2,t3,v1;
4317
OPTIMIZE TABLE t1,t2,t3,v1;
4318
ANALYZE TABLE t1,t2,t3,v1;
4321
Table Op Msg_type Msg_text
4322
test.t1 repair status OK
4323
test.t2 repair status OK
4324
test.t3 repair status OK
4325
test.v1 repair Error 'test.v1' is not BASE TABLE
4326
test.v1 repair error Corrupt
4327
Table Op Msg_type Msg_text
4328
test.t1 optimize status OK
4329
test.t2 optimize status OK
4330
test.t3 optimize status OK
4331
test.v1 optimize Error 'test.v1' is not BASE TABLE
4332
test.v1 optimize error Corrupt
4333
Table Op Msg_type Msg_text
4334
test.t1 analyze status Table is already up to date
4335
test.t2 analyze status Table is already up to date
4336
test.t3 analyze status Table is already up to date
4337
test.v1 analyze Error 'test.v1' is not BASE TABLE
4338
test.v1 analyze error Corrupt
4340
Table Op Msg_type Msg_text
4341
test.t1 repair status OK
4342
test.t2 repair status OK
4343
test.t3 repair status OK
4344
test.v1 repair Error 'test.v1' is not BASE TABLE
4345
test.v1 repair error Corrupt
4346
Table Op Msg_type Msg_text
4347
test.t1 optimize status OK
4348
test.t2 optimize status OK
4349
test.t3 optimize status OK
4350
test.v1 optimize Error 'test.v1' is not BASE TABLE
4351
test.v1 optimize error Corrupt
4352
Table Op Msg_type Msg_text
4353
test.t1 analyze status Table is already up to date
4354
test.t2 analyze status Table is already up to date
4355
test.t3 analyze status Table is already up to date
4356
test.v1 analyze Error 'test.v1' is not BASE TABLE
4357
test.v1 analyze error Corrupt
4359
Table Op Msg_type Msg_text
4360
test.t1 repair status OK
4361
test.t2 repair status OK
4362
test.t3 repair status OK
4363
test.v1 repair Error 'test.v1' is not BASE TABLE
4364
test.v1 repair error Corrupt
4365
Table Op Msg_type Msg_text
4366
test.t1 optimize status OK
4367
test.t2 optimize status OK
4368
test.t3 optimize status OK
4369
test.v1 optimize Error 'test.v1' is not BASE TABLE
4370
test.v1 optimize error Corrupt
4371
Table Op Msg_type Msg_text
4372
test.t1 analyze status Table is already up to date
4373
test.t2 analyze status Table is already up to date
4374
test.t3 analyze status Table is already up to date
4375
test.v1 analyze Error 'test.v1' is not BASE TABLE
4376
test.v1 analyze error Corrupt
4377
drop procedure bug13012|
4379
select * from t1 order by data|
4391
drop schema if exists mysqltest1|
4393
Note 1008 Can't drop database 'mysqltest1'; database doesn't exist
4394
drop schema if exists mysqltest2|
4396
Note 1008 Can't drop database 'mysqltest2'; database doesn't exist
4397
drop schema if exists mysqltest3|
4399
Note 1008 Can't drop database 'mysqltest3'; database doesn't exist
4400
create schema mysqltest1|
4401
create schema mysqltest2|
4402
create schema mysqltest3|
4404
create procedure mysqltest1.p1 (out prequestid varchar(100))
4406
call mysqltest2.p2('call mysqltest3.p3(1, 2)');
4408
create procedure mysqltest2.p2(in psql text)
4412
prepare lstatement from @lsql;
4414
deallocate prepare lstatement;
4416
create procedure mysqltest3.p3(in p1 int)
4420
call mysqltest1.p1(@rs)|
4421
ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2
4422
call mysqltest1.p1(@rs)|
4423
ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2
4424
call mysqltest1.p1(@rs)|
4425
ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2
4426
drop schema if exists mysqltest1|
4427
drop schema if exists mysqltest2|
4428
drop schema if exists mysqltest3|
4430
drop table if exists t3|
4431
drop procedure if exists bug15441|
4432
create table t3 (id int not null primary key, county varchar(25))|
4433
insert into t3 (id, county) values (1, 'York')|
4434
create procedure bug15441(c varchar(25))
4436
update t3 set id=2, county=values(c);
4438
call bug15441('county')|
4439
ERROR 42S22: Unknown column 'c' in 'field list'
4440
drop procedure bug15441|
4441
create procedure bug15441(county varchar(25))
4443
declare c varchar(25) default "hello";
4444
insert into t3 (id, county) values (1, county)
4445
on duplicate key update county= values(county);
4447
update t3 set id=2, county=values(id);
4450
call bug15441('Yale')|
4456
drop procedure bug15441|
4457
drop procedure if exists bug14498_1|
4458
drop procedure if exists bug14498_2|
4459
drop procedure if exists bug14498_3|
4460
drop procedure if exists bug14498_4|
4461
drop procedure if exists bug14498_5|
4462
create procedure bug14498_1()
4464
declare continue handler for sqlexception select 'error' as 'Handler';
4466
select 'yes' as 'v';
4470
select 'done' as 'End';
4472
create procedure bug14498_2()
4474
declare continue handler for sqlexception select 'error' as 'Handler';
4476
select 'yes' as 'v';
4478
select 'done' as 'End';
4480
create procedure bug14498_3()
4482
declare continue handler for sqlexception select 'error' as 'Handler';
4484
select 'maybe' as 'v';
4486
select 'done' as 'End';
4488
create procedure bug14498_4()
4490
declare continue handler for sqlexception select 'error' as 'Handler';
4499
select 'done' as 'End';
4501
create procedure bug14498_5()
4503
declare continue handler for sqlexception select 'error' as 'Handler';
4512
select 'done' as 'End';
4541
drop procedure bug14498_1|
4542
drop procedure bug14498_2|
4543
drop procedure bug14498_3|
4544
drop procedure bug14498_4|
4545
drop procedure bug14498_5|
4546
drop table if exists t3|
4547
drop procedure if exists bug15231_1|
4548
drop procedure if exists bug15231_2|
4549
drop procedure if exists bug15231_3|
4550
drop procedure if exists bug15231_4|
4551
create table t3 (id int not null)|
4552
create procedure bug15231_1()
4554
declare xid integer;
4555
declare xdone integer default 0;
4556
declare continue handler for not found set xdone = 1;
4558
call bug15231_2(xid);
4561
create procedure bug15231_2(inout ioid integer)
4563
select "Before NOT FOUND condition is triggered" as '1';
4564
select id into ioid from t3 where id=ioid;
4565
select "After NOT FOUND condtition is triggered" as '2';
4566
if ioid is null then
4570
create procedure bug15231_3()
4572
declare exit handler for sqlwarning
4573
select 'Caught it (wrong)' as 'Result';
4576
create procedure bug15231_4()
4578
declare x decimal(2,1);
4580
select 'Missed it (correct)' as 'Result';
4584
Before NOT FOUND condition is triggered
4586
After NOT FOUND condtition is triggered
4590
Warning 1329 No data - zero rows fetched, selected, or processed
4595
Warning 1366 Incorrect decimal value: 'zap' for column 'x' at row 1
4596
drop table if exists t3|
4597
drop procedure if exists bug15231_1|
4598
drop procedure if exists bug15231_2|
4599
drop procedure if exists bug15231_3|
4600
drop procedure if exists bug15231_4|
4601
drop procedure if exists bug15011|
4602
create table t3 (c1 int primary key)|
4603
insert into t3 values (1)|
4604
create procedure bug15011()
4607
declare continue handler for 1062
4608
select 'Outer' as 'Handler';
4610
declare continue handler for 1062
4611
select 'Inner' as 'Handler';
4612
insert into t3 values (1);
4618
drop procedure bug15011|
4620
drop procedure if exists bug17476|
4621
create table t3 ( d date )|
4622
insert into t3 values
4623
( '2005-01-01' ), ( '2005-01-02' ), ( '2005-01-03' ),
4624
( '2005-01-04' ), ( '2005-02-01' ), ( '2005-02-02' )|
4625
create procedure bug17476(pDateFormat varchar(10))
4626
select date_format(t3.d, pDateFormat), count(*)
4628
group by date_format(t3.d, pDateFormat)|
4629
call bug17476('%Y-%m')|
4630
date_format(t3.d, pDateFormat) count(*)
4633
call bug17476('%Y-%m')|
4634
date_format(t3.d, pDateFormat) count(*)
4638
drop procedure bug17476|
4639
drop table if exists t3|
4640
drop procedure if exists bug16887|
4641
create table t3 ( c varchar(1) )|
4642
insert into t3 values
4643
(' '),('.'),(';'),(','),('-'),('_'),('('),(')'),('/'),('\\')|
4644
create procedure bug16887()
4646
declare i int default 10;
4650
declare breakchar varchar(1);
4651
declare done int default 0;
4652
declare t3_cursor cursor for select c from t3;
4653
declare continue handler for not found set done = 1;
4661
fetch t3_cursor into breakchar;
4694
drop procedure bug16887|
4695
drop procedure if exists bug16474_1|
4696
drop procedure if exists bug16474_2|
4698
insert into t1 values ('c', 2), ('b', 3), ('a', 1)|
4699
create procedure bug16474_1()
4702
select id from t1 order by x, id;
4704
drop procedure if exists bug14945|
4705
create table t3 (id int not null auto_increment primary key)|
4706
create procedure bug14945() deterministic truncate t3|
4707
insert into t3 values (null)|
4709
insert into t3 values (null)|
4714
drop procedure bug14945|
4715
create procedure bug16474_2(x int)
4716
select id from t1 order by x, id|
4732
drop procedure bug16474_1|
4733
drop procedure bug16474_2|
4735
select * from t1 order by @x, data|
4741
drop function if exists bug15728|
4742
drop table if exists t3|
4744
id int not null auto_increment,
4747
create function bug15728() returns int(11)
4748
return last_insert_id()|
4749
insert into t3 values (0)|
4750
select last_insert_id()|
4756
drop function bug15728|
4758
drop procedure if exists bug18787|
4759
create procedure bug18787()
4761
declare continue handler for sqlexception begin end;
4762
select no_such_function();
4765
drop procedure bug18787|
4766
create database bug18344_012345678901|
4767
use bug18344_012345678901|
4768
create procedure bug18344() begin end|
4769
create procedure bug18344_2() begin end|
4770
create database bug18344_0123456789012|
4771
use bug18344_0123456789012|
4772
create procedure bug18344() begin end|
4773
create procedure bug18344_2() begin end|
4775
select schema_name from information_schema.schemata where
4776
schema_name like 'bug18344%'|
4778
bug18344_012345678901
4779
bug18344_0123456789012
4780
select routine_name,routine_schema from information_schema.routines where
4781
routine_schema like 'bug18344%'|
4782
routine_name routine_schema
4783
bug18344 bug18344_012345678901
4784
bug18344_2 bug18344_012345678901
4785
bug18344 bug18344_0123456789012
4786
bug18344_2 bug18344_0123456789012
4787
drop database bug18344_012345678901|
4788
drop database bug18344_0123456789012|
4789
select schema_name from information_schema.schemata where
4790
schema_name like 'bug18344%'|
4792
select routine_name,routine_schema from information_schema.routines where
4793
routine_schema like 'bug18344%'|
4794
routine_name routine_schema
4795
drop function if exists bug12472|
4796
create function bug12472() returns int return (select count(*) from t1)|
4797
create table t3 as select bug12472() as i|
4798
show create table t3|
4800
t3 CREATE TABLE `t3` (
4801
`i` int(11) DEFAULT NULL
4802
) ENGINE=MyISAM DEFAULT CHARSET=latin1
4807
create view v1 as select bug12472() as j|
4808
create table t3 as select * from v1|
4809
show create table t3|
4811
t3 CREATE TABLE `t3` (
4812
`j` int(11) DEFAULT NULL
4813
) ENGINE=MyISAM DEFAULT CHARSET=latin1
4819
drop function bug12472|
4820
DROP FUNCTION IF EXISTS bug18589_f1|
4821
DROP PROCEDURE IF EXISTS bug18589_p1|
4822
DROP PROCEDURE IF EXISTS bug18589_p2|
4823
CREATE FUNCTION bug18589_f1(arg TEXT) RETURNS TEXT
4825
RETURN CONCAT(arg, "");
4827
CREATE PROCEDURE bug18589_p1(arg TEXT, OUT ret TEXT)
4829
SET ret = CONCAT(arg, "");
4831
CREATE PROCEDURE bug18589_p2(arg TEXT)
4834
CALL bug18589_p1(arg, v);
4837
SELECT bug18589_f1(REPEAT("a", 767))|
4838
bug18589_f1(REPEAT("a", 767))
4839
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
4840
SET @bug18589_v1 = ""|
4841
CALL bug18589_p1(REPEAT("a", 767), @bug18589_v1)|
4842
SELECT @bug18589_v1|
4844
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
4845
CALL bug18589_p2(REPEAT("a", 767))|
4847
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
4848
DROP FUNCTION bug18589_f1|
4849
DROP PROCEDURE bug18589_p1|
4850
DROP PROCEDURE bug18589_p2|
4851
DROP FUNCTION IF EXISTS bug18037_f1|
4852
DROP PROCEDURE IF EXISTS bug18037_p1|
4853
DROP PROCEDURE IF EXISTS bug18037_p2|
4854
CREATE FUNCTION bug18037_f1() RETURNS INT
4858
CREATE PROCEDURE bug18037_p1()
4860
DECLARE v INT DEFAULT @@server_id;
4862
CREATE PROCEDURE bug18037_p2()
4871
SELECT bug18037_f1()|
4878
DROP FUNCTION bug18037_f1|
4879
DROP PROCEDURE bug18037_p1|
4880
DROP PROCEDURE bug18037_p2|
4882
create table t3 (i int)|
4883
insert into t3 values (1), (2)|
4884
create database mysqltest1|
4886
create function bug17199() returns varchar(2) deterministic return 'ok'|
4888
select *, mysqltest1.bug17199() from t3|
4889
i mysqltest1.bug17199()
4893
create function bug18444(i int) returns int no sql deterministic return i + 1|
4895
select mysqltest1.bug18444(i) from t3|
4896
mysqltest1.bug18444(i)
4899
drop database mysqltest1|
4900
create database mysqltest1 charset=utf8|
4901
create database mysqltest2 charset=utf8|
4902
create procedure mysqltest1.p1()
4904
-- alters the default collation of database test
4905
alter database character set koi8r;
4909
show create database mysqltest1|
4910
Database Create Database
4911
mysqltest1 CREATE DATABASE `mysqltest1` /*!40100 DEFAULT CHARACTER SET koi8r */
4912
show create database mysqltest2|
4913
Database Create Database
4914
mysqltest2 CREATE DATABASE `mysqltest2` /*!40100 DEFAULT CHARACTER SET utf8 */
4915
alter database mysqltest1 character set utf8|
4917
call mysqltest1.p1()|
4918
show create database mysqltest1|
4919
Database Create Database
4920
mysqltest1 CREATE DATABASE `mysqltest1` /*!40100 DEFAULT CHARACTER SET koi8r */
4921
show create database mysqltest2|
4922
Database Create Database
4923
mysqltest2 CREATE DATABASE `mysqltest2` /*!40100 DEFAULT CHARACTER SET utf8 */
4924
drop database mysqltest1|
4925
drop database mysqltest2|
4927
drop table if exists t3|
4928
drop procedure if exists bug15217|
4929
create table t3 as select 1|
4930
create procedure bug15217()
4932
declare var1 char(255);
4933
declare cur1 cursor for select * from t3;
4935
fetch cur1 into var1;
4936
select concat('data was: /', var1, '/');
4940
concat('data was: /', var1, '/')
4944
concat('data was: /', var1, '/')
4947
drop procedure bug15217|
4948
DROP PROCEDURE IF EXISTS bug21013 |
4949
CREATE PROCEDURE bug21013(IN lim INT)
4951
DECLARE i INT DEFAULT 0;
4953
SET @b = LOCATE(_latin1'b', @a, 1);
4957
SET @a = _latin2"aaaaaaaaaa" |
4959
DROP PROCEDURE bug21013 |
4960
DROP DATABASE IF EXISTS mysqltest1|
4961
DROP DATABASE IF EXISTS mysqltest2|
4962
CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8|
4963
CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8|
4965
CREATE FUNCTION bug16211_f1() RETURNS CHAR(10)
4967
CREATE FUNCTION bug16211_f2() RETURNS CHAR(10) CHARSET koi8r
4969
CREATE FUNCTION mysqltest2.bug16211_f3() RETURNS CHAR(10)
4971
CREATE FUNCTION mysqltest2.bug16211_f4() RETURNS CHAR(10) CHARSET koi8r
4973
SHOW CREATE FUNCTION bug16211_f1|
4974
Function sql_mode Create Function character_set_client collation_connection Database Collation
4975
bug16211_f1 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8
4976
RETURN "" latin1 latin1_swedish_ci utf8_general_ci
4977
SHOW CREATE FUNCTION bug16211_f2|
4978
Function sql_mode Create Function character_set_client collation_connection Database Collation
4979
bug16211_f2 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r
4980
RETURN "" latin1 latin1_swedish_ci utf8_general_ci
4981
SHOW CREATE FUNCTION mysqltest2.bug16211_f3|
4982
Function sql_mode Create Function character_set_client collation_connection Database Collation
4983
bug16211_f3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8
4984
RETURN "" latin1 latin1_swedish_ci utf8_general_ci
4985
SHOW CREATE FUNCTION mysqltest2.bug16211_f4|
4986
Function sql_mode Create Function character_set_client collation_connection Database Collation
4987
bug16211_f4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r
4988
RETURN "" latin1 latin1_swedish_ci utf8_general_ci
4989
SELECT dtd_identifier
4990
FROM INFORMATION_SCHEMA.ROUTINES
4991
WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"|
4993
char(10) CHARSET utf8
4994
SELECT dtd_identifier
4995
FROM INFORMATION_SCHEMA.ROUTINES
4996
WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"|
4998
char(10) CHARSET koi8r
4999
SELECT dtd_identifier
5000
FROM INFORMATION_SCHEMA.ROUTINES
5001
WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"|
5003
char(10) CHARSET utf8
5004
SELECT dtd_identifier
5005
FROM INFORMATION_SCHEMA.ROUTINES
5006
WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"|
5008
char(10) CHARSET koi8r
5009
SELECT CHARSET(bug16211_f1())|
5010
CHARSET(bug16211_f1())
5012
SELECT CHARSET(bug16211_f2())|
5013
CHARSET(bug16211_f2())
5015
SELECT CHARSET(mysqltest2.bug16211_f3())|
5016
CHARSET(mysqltest2.bug16211_f3())
5018
SELECT CHARSET(mysqltest2.bug16211_f4())|
5019
CHARSET(mysqltest2.bug16211_f4())
5021
ALTER DATABASE mysqltest1 CHARACTER SET cp1251|
5022
ALTER DATABASE mysqltest2 CHARACTER SET cp1251|
5023
SHOW CREATE FUNCTION bug16211_f1|
5024
Function sql_mode Create Function character_set_client collation_connection Database Collation
5025
bug16211_f1 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8
5026
RETURN "" latin1 latin1_swedish_ci utf8_general_ci
5027
SHOW CREATE FUNCTION bug16211_f2|
5028
Function sql_mode Create Function character_set_client collation_connection Database Collation
5029
bug16211_f2 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r
5030
RETURN "" latin1 latin1_swedish_ci utf8_general_ci
5031
SHOW CREATE FUNCTION mysqltest2.bug16211_f3|
5032
Function sql_mode Create Function character_set_client collation_connection Database Collation
5033
bug16211_f3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8
5034
RETURN "" latin1 latin1_swedish_ci utf8_general_ci
5035
SHOW CREATE FUNCTION mysqltest2.bug16211_f4|
5036
Function sql_mode Create Function character_set_client collation_connection Database Collation
5037
bug16211_f4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r
5038
RETURN "" latin1 latin1_swedish_ci utf8_general_ci
5039
SELECT dtd_identifier
5040
FROM INFORMATION_SCHEMA.ROUTINES
5041
WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"|
5043
char(10) CHARSET utf8
5044
SELECT dtd_identifier
5045
FROM INFORMATION_SCHEMA.ROUTINES
5046
WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"|
5048
char(10) CHARSET koi8r
5049
SELECT dtd_identifier
5050
FROM INFORMATION_SCHEMA.ROUTINES
5051
WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"|
5053
char(10) CHARSET utf8
5054
SELECT dtd_identifier
5055
FROM INFORMATION_SCHEMA.ROUTINES
5056
WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"|
5058
char(10) CHARSET koi8r
5059
SELECT CHARSET(bug16211_f1())|
5060
CHARSET(bug16211_f1())
5062
SELECT CHARSET(bug16211_f2())|
5063
CHARSET(bug16211_f2())
5065
SELECT CHARSET(mysqltest2.bug16211_f3())|
5066
CHARSET(mysqltest2.bug16211_f3())
5068
SELECT CHARSET(mysqltest2.bug16211_f4())|
5069
CHARSET(mysqltest2.bug16211_f4())
5072
DROP DATABASE mysqltest1|
5073
DROP DATABASE mysqltest2|
5074
DROP DATABASE IF EXISTS mysqltest1|
5075
CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8|
5077
CREATE PROCEDURE bug16676_p1(
5082
SELECT CHARSET(p1), COLLATION(p1);
5083
SELECT CHARSET(p2), COLLATION(p2);
5084
SELECT CHARSET(p3), COLLATION(p3);
5086
CREATE PROCEDURE bug16676_p2(
5087
IN p1 CHAR(10) CHARSET koi8r,
5088
INOUT p2 CHAR(10) CHARSET cp1251,
5089
OUT p3 CHAR(10) CHARSET greek)
5091
SELECT CHARSET(p1), COLLATION(p1);
5092
SELECT CHARSET(p2), COLLATION(p2);
5093
SELECT CHARSET(p3), COLLATION(p3);
5097
CALL bug16676_p1('a', @v2, @v3)|
5098
CHARSET(p1) COLLATION(p1)
5099
utf8 utf8_general_ci
5100
CHARSET(p2) COLLATION(p2)
5101
utf8 utf8_general_ci
5102
CHARSET(p3) COLLATION(p3)
5103
utf8 utf8_general_ci
5104
CALL bug16676_p2('a', @v2, @v3)|
5105
CHARSET(p1) COLLATION(p1)
5106
koi8r koi8r_general_ci
5107
CHARSET(p2) COLLATION(p2)
5108
cp1251 cp1251_general_ci
5109
CHARSET(p3) COLLATION(p3)
5110
greek greek_general_ci
5112
DROP DATABASE mysqltest1|
5113
drop table if exists t3|
5114
drop table if exists t4|
5115
drop procedure if exists bug8153_subselect|
5116
drop procedure if exists bug8153_subselect_a|
5117
drop procedure if exists bug8153_subselect_b|
5118
drop procedure if exists bug8153_proc_a|
5119
drop procedure if exists bug8153_proc_b|
5120
create table t3 (a int)|
5121
create table t4 (a int)|
5122
insert into t3 values (1), (1), (2), (3)|
5123
insert into t4 values (1), (1)|
5124
create procedure bug8153_subselect()
5126
declare continue handler for sqlexception
5128
select 'statement failed';
5130
update t3 set a=a+1 where (select a from t4 where a=1) is null;
5131
select 'statement after update';
5133
call bug8153_subselect()|
5136
statement after update
5137
statement after update
5144
call bug8153_subselect()|
5147
statement after update
5148
statement after update
5155
drop procedure bug8153_subselect|
5156
create procedure bug8153_subselect_a()
5158
declare continue handler for sqlexception
5160
select 'in continue handler';
5162
select 'reachable code a1';
5163
call bug8153_subselect_b();
5164
select 'reachable code a2';
5166
create procedure bug8153_subselect_b()
5168
select 'reachable code b1';
5169
update t3 set a=a+1 where (select a from t4 where a=1) is null;
5170
select 'unreachable code b2';
5172
call bug8153_subselect_a()|
5187
call bug8153_subselect_a()|
5202
drop procedure bug8153_subselect_a|
5203
drop procedure bug8153_subselect_b|
5204
create procedure bug8153_proc_a()
5206
declare continue handler for sqlexception
5208
select 'in continue handler';
5210
select 'reachable code a1';
5211
call bug8153_proc_b();
5212
select 'reachable code a2';
5214
create procedure bug8153_proc_b()
5216
select 'reachable code b1';
5217
select no_such_function();
5218
select 'unreachable code b2';
5220
call bug8153_proc_a()|
5229
drop procedure bug8153_proc_a|
5230
drop procedure bug8153_proc_b|
5233
drop procedure if exists bug19862|
5234
CREATE TABLE t11 (a INT)|
5235
CREATE TABLE t12 (a INT)|
5236
CREATE FUNCTION bug19862(x INT) RETURNS INT
5238
INSERT INTO t11 VALUES (x);
5241
INSERT INTO t12 VALUES (1), (2)|
5242
SELECT bug19862(a) FROM t12 ORDER BY 1|
5250
DROP TABLE t11, t12|
5251
DROP FUNCTION bug19862|
5252
drop table if exists t3|
5253
drop database if exists mysqltest1|
5254
create table t3 (a int)|
5255
insert into t3 (a) values (1), (2)|
5256
create database mysqltest1|
5258
drop database mysqltest1|
5262
select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2|
5267
DROP PROCEDURE IF EXISTS bug16899_p1|
5268
DROP FUNCTION IF EXISTS bug16899_f1|
5269
CREATE DEFINER=1234567890abcdefGHIKL@localhost PROCEDURE bug16899_p1()
5273
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
5274
CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY
5275
FUNCTION bug16899_f1() RETURNS INT
5279
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
5280
drop procedure if exists bug21416|
5281
create procedure bug21416() show create procedure bug21416|
5283
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
5284
bug21416 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21416`()
5285
show create procedure bug21416 latin1 latin1_swedish_ci latin1_swedish_ci
5286
drop procedure bug21416|
5287
DROP PROCEDURE IF EXISTS bug21414|
5288
CREATE PROCEDURE bug21414() SELECT 1|
5289
FLUSH TABLES WITH READ LOCK|
5290
DROP PROCEDURE bug21414|
5291
ERROR HY000: Can't execute the query because you have a conflicting read lock
5293
The following should succeed.
5294
DROP PROCEDURE bug21414|
5296
drop database if exists ŃŠ¾Š²Š°_Šµ_Š“ŃŠ»Š³Š¾_ŠøŠ¼Šµ_Š·Š°_Š±Š°Š·Š°_Š“Š°Š½Š½Šø_Š½Š°Š»Šø|
5297
create database ŃŠ¾Š²Š°_Šµ_Š“ŃŠ»Š³Š¾_ŠøŠ¼Šµ_Š·Š°_Š±Š°Š·Š°_Š“Š°Š½Š½Šø_Š½Š°Š»Šø|
5298
INSERT INTO mysql.proc VALUES ('ŃŠ¾Š²Š°_Šµ_Š“ŃŠ»Š³Š¾_ŠøŠ¼Šµ_Š·Š°_Š±Š°Š·Š°_Š“Š°Š½Š½Šø_Š½Š°Š»Šø','ŃŠ¾Š²Š°_Šµ_ŠæŃŠ¾ŃŠµŠ“ŃŃŠ°_Ń_Š“Š¾ŃŃŠ°_Š“ŃŠ»Š³Š¾_ŠøŠ¼Šµ_Š½Š°Š»Šø_Šø_Š¾ŃŠµ_ŠæŠ¾_Š“ŃŠ»Š³Š¾','PROCEDURE','ŃŠ¾Š²Š°_Šµ_ŠæŃŠ¾ŃŠµŠ“ŃŃŠ°_Ń_Š“Š¾ŃŃŠ°_Š“ŃŠ»Š³Š¾_ŠøŠ¼Šµ_Š½Š°Š»Šø_Šø_Š¾ŃŠµ_ŠæŠ¾_Š“ŃŠ»Š³Š¾','SQL','CONTAINS_SQL','NO','DEFINER','','','bad_body','root@localhost',now(), now(),'','', 'utf8', 'utf8_general_ci', 'utf8_general_ci', 'n/a')|
5299
call ŃŠ¾Š²Š°_Šµ_Š“ŃŠ»Š³Š¾_ŠøŠ¼Šµ_Š·Š°_Š±Š°Š·Š°_Š“Š°Š½Š½Šø_Š½Š°Š»Šø.ŃŠ¾Š²Š°_Šµ_ŠæŃŠ¾ŃŠµŠ“ŃŃŠ°_Ń_Š“Š¾ŃŃŠ°_Š“ŃŠ»Š³Š¾_ŠøŠ¼Šµ_Š½Š°Š»Šø_Šø_Š¾ŃŠµ_ŠæŠ¾_Š“ŃŠ»Š³Š¾()|
5300
ERROR HY000: Failed to load routine ŃŠ¾Š²Š°_Šµ_Š“ŃŠ»Š³Š¾_ŠøŠ¼Šµ_Š·Š°_Š±Š°Š·Š°_Š“Š°Š½Š½Šø_Š½Š°Š»Šø.ŃŠ¾Š²Š°_Šµ_ŠæŃŠ¾ŃŠµŠ“ŃŃŠ°_Ń_Š“Š¾ŃŃŠ°_Š“ŃŠ»Š³Š¾_ŠøŠ¼Šµ_Š½Š°Š»Šø_Šø_Š¾ŃŠµ_ŠæŠ¾_Š“ŃŠ»Š³Š¾. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
5301
drop database ŃŠ¾Š²Š°_Šµ_Š“ŃŠ»Š³Š¾_ŠøŠ¼Šµ_Š·Š°_Š±Š°Š·Š°_Š“Š°Š½Š½Šø_Š½Š°Š»Šø|
5303
Member_ID varchar(15) NOT NULL,
5304
PRIMARY KEY (Member_ID)
5307
ID int(10) unsigned NOT NULL auto_increment,
5308
Member_ID varchar(15) NOT NULL default '',
5309
Action varchar(12) NOT NULL,
5310
Action_Date datetime NOT NULL,
5311
Track varchar(15) default NULL,
5312
User varchar(12) default NULL,
5313
Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update
5316
KEY Action (Action),
5317
KEY Action_Date (Action_Date)
5319
INSERT INTO t3(Member_ID) VALUES
5320
('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666')|
5321
INSERT INTO t4(Member_ID, Action, Action_Date, Track) VALUES
5322
('111111', 'Disenrolled', '2006-03-01', 'CAD' ),
5323
('111111', 'Enrolled', '2006-03-01', 'CAD' ),
5324
('111111', 'Disenrolled', '2006-07-03', 'CAD' ),
5325
('222222', 'Enrolled', '2006-03-07', 'CAD' ),
5326
('222222', 'Enrolled', '2006-03-07', 'CHF' ),
5327
('222222', 'Disenrolled', '2006-08-02', 'CHF' ),
5328
('333333', 'Enrolled', '2006-03-01', 'CAD' ),
5329
('333333', 'Disenrolled', '2006-03-01', 'CAD' ),
5330
('444444', 'Enrolled', '2006-03-01', 'CAD' ),
5331
('555555', 'Disenrolled', '2006-03-01', 'CAD' ),
5332
('555555', 'Enrolled', '2006-07-21', 'CAD' ),
5333
('555555', 'Disenrolled', '2006-03-01', 'CHF' ),
5334
('666666', 'Enrolled', '2006-02-09', 'CAD' ),
5335
('666666', 'Enrolled', '2006-05-12', 'CHF' ),
5336
('666666', 'Disenrolled', '2006-06-01', 'CAD' )|
5337
DROP FUNCTION IF EXISTS bug21493|
5338
CREATE FUNCTION bug21493(paramMember VARCHAR(15)) RETURNS varchar(45)
5340
DECLARE tracks VARCHAR(45);
5341
SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM t4
5342
WHERE Member_ID=paramMember AND Action='Enrolled' AND
5343
(Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t4
5344
WHERE Member_ID=paramMember GROUP BY Track);
5347
SELECT bug21493('111111')|
5350
SELECT bug21493('222222')|
5353
SELECT bug21493(Member_ID) FROM t3|
5361
DROP FUNCTION bug21493|
5363
drop function if exists func_20028_a|
5364
drop function if exists func_20028_b|
5365
drop function if exists func_20028_c|
5366
drop procedure if exists proc_20028_a|
5367
drop procedure if exists proc_20028_b|
5368
drop procedure if exists proc_20028_c|
5369
drop table if exists table_20028|
5370
create table table_20028 (i int)|
5371
SET @save_sql_mode=@@sql_mode|
5373
create function func_20028_a() returns integer
5375
declare temp integer;
5376
select i into temp from table_20028 limit 1;
5377
return ifnull(temp, 0);
5379
create function func_20028_b() returns integer
5381
return func_20028_a();
5383
create function func_20028_c() returns integer
5385
declare div_zero integer;
5386
set SQL_MODE='TRADITIONAL';
5387
select 1/0 into div_zero;
5390
create procedure proc_20028_a()
5392
declare temp integer;
5393
select i into temp from table_20028 limit 1;
5395
create procedure proc_20028_b()
5397
call proc_20028_a();
5399
create procedure proc_20028_c()
5401
declare div_zero integer;
5402
set SQL_MODE='TRADITIONAL';
5403
select 1/0 into div_zero;
5405
select func_20028_a()|
5409
Warning 1329 No data - zero rows fetched, selected, or processed
5410
select func_20028_b()|
5414
Warning 1329 No data - zero rows fetched, selected, or processed
5415
select func_20028_c()|
5416
ERROR 22012: Division by 0
5417
call proc_20028_a()|
5419
Warning 1329 No data - zero rows fetched, selected, or processed
5420
call proc_20028_b()|
5422
Warning 1329 No data - zero rows fetched, selected, or processed
5423
call proc_20028_c()|
5424
ERROR 22012: Division by 0
5425
SET sql_mode='TRADITIONAL'|
5426
drop function func_20028_a|
5427
drop function func_20028_b|
5428
drop function func_20028_c|
5429
drop procedure proc_20028_a|
5430
drop procedure proc_20028_b|
5431
drop procedure proc_20028_c|
5432
create function func_20028_a() returns integer
5434
declare temp integer;
5435
select i into temp from table_20028 limit 1;
5436
return ifnull(temp, 0);
5438
create function func_20028_b() returns integer
5440
return func_20028_a();
5442
create function func_20028_c() returns integer
5444
declare div_zero integer;
5446
select 1/0 into div_zero;
5449
create procedure proc_20028_a()
5451
declare temp integer;
5452
select i into temp from table_20028 limit 1;
5454
create procedure proc_20028_b()
5456
call proc_20028_a();
5458
create procedure proc_20028_c()
5460
declare div_zero integer;
5462
select 1/0 into div_zero;
5464
select func_20028_a()|
5468
Warning 1329 No data - zero rows fetched, selected, or processed
5469
select func_20028_b()|
5473
Warning 1329 No data - zero rows fetched, selected, or processed
5474
select func_20028_c()|
5477
call proc_20028_a()|
5479
Warning 1329 No data - zero rows fetched, selected, or processed
5480
call proc_20028_b()|
5482
Warning 1329 No data - zero rows fetched, selected, or processed
5483
call proc_20028_c()|
5484
SET @@sql_mode=@save_sql_mode|
5485
drop function func_20028_a|
5486
drop function func_20028_b|
5487
drop function func_20028_c|
5488
drop procedure proc_20028_a|
5489
drop procedure proc_20028_b|
5490
drop procedure proc_20028_c|
5491
drop table table_20028|
5492
drop procedure if exists proc_21462_a|
5493
drop procedure if exists proc_21462_b|
5494
create procedure proc_21462_a()
5498
create procedure proc_21462_b(x int)
5505
call proc_21462_a()|
5508
call proc_21462_a(1)|
5509
ERROR 42000: Incorrect number of arguments for PROCEDURE test.proc_21462_a; expected 0, got 1
5511
ERROR 42000: Incorrect number of arguments for PROCEDURE test.proc_21462_b; expected 1, got 0
5512
call proc_21462_b()|
5513
ERROR 42000: Incorrect number of arguments for PROCEDURE test.proc_21462_b; expected 1, got 0
5514
call proc_21462_b(1)|
5517
drop procedure proc_21462_a|
5518
drop procedure proc_21462_b|
5519
drop table if exists t3|
5520
drop procedure if exists proc_bug19733|
5521
create table t3 (s1 int)|
5522
create procedure proc_bug19733()
5524
declare v int default 0;
5526
create index i on t3 (s1);
5531
call proc_bug19733()|
5532
call proc_bug19733()|
5533
call proc_bug19733()|
5534
drop procedure proc_bug19733|
5536
DROP PROCEDURE IF EXISTS p1|
5537
DROP VIEW IF EXISTS v1, v2|
5538
DROP TABLE IF EXISTS t3, t4|
5539
CREATE TABLE t3 (t3_id INT)|
5540
INSERT INTO t3 VALUES (0)|
5541
INSERT INTO t3 VALUES (1)|
5542
CREATE TABLE t4 (t4_id INT)|
5543
INSERT INTO t4 VALUES (2)|
5545
SELECT t3.t3_id, t4.t4_id
5546
FROM t3 JOIN t4 ON t3.t3_id = 0|
5548
SELECT t3.t3_id AS t3_id_1, v1.t3_id AS t3_id_2, v1.t4_id
5549
FROM t3 LEFT JOIN v1 ON t3.t3_id = 0|
5550
CREATE PROCEDURE p1() SELECT * FROM v2|
5552
t3_id_1 t3_id_2 t4_id
5556
t3_id_1 t3_id_2 t4_id
5564
drop function if exists pi;
5565
create function pi() returns varchar(50)
5566
return "pie, my favorite desert.";
5568
Note 1585 This function 'pi' has the same name as a native function
5569
SET @save_sql_mode=@@sql_mode;
5570
SET SQL_MODE='IGNORE_SPACE';
5574
select test.pi(), test.pi ();
5575
test.pi() test.pi ()
5576
pie, my favorite desert. pie, my favorite desert.
5581
select test.pi(), test.pi ();
5582
test.pi() test.pi ()
5583
pie, my favorite desert. pie, my favorite desert.
5584
SET @@sql_mode=@save_sql_mode;
5586
drop function if exists test.database;
5587
drop function if exists test.current_user;
5588
drop function if exists test.md5;
5589
create database nowhere;
5591
drop database nowhere;
5592
SET @save_sql_mode=@@sql_mode;
5593
SET SQL_MODE='IGNORE_SPACE';
5594
select database(), database ();
5595
database() database ()
5597
select current_user(), current_user ();
5598
current_user() current_user ()
5599
root@localhost root@localhost
5600
select md5("aaa"), md5 ("aaa");
5601
md5("aaa") md5 ("aaa")
5602
47bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808
5604
select database(), database ();
5605
database() database ()
5607
select current_user(), current_user ();
5608
current_user() current_user ()
5609
root@localhost root@localhost
5610
select md5("aaa"), md5 ("aaa");
5611
md5("aaa") md5 ("aaa")
5612
47bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808
5614
create function `database`() returns varchar(50)
5615
return "Stored function database";
5617
Note 1585 This function 'database' has the same name as a native function
5618
create function `current_user`() returns varchar(50)
5619
return "Stored function current_user";
5621
Note 1585 This function 'current_user' has the same name as a native function
5622
create function md5(x varchar(50)) returns varchar(50)
5623
return "Stored function md5";
5625
Note 1585 This function 'md5' has the same name as a native function
5626
SET SQL_MODE='IGNORE_SPACE';
5627
select database(), database ();
5628
database() database ()
5630
select current_user(), current_user ();
5631
current_user() current_user ()
5632
root@localhost root@localhost
5633
select md5("aaa"), md5 ("aaa");
5634
md5("aaa") md5 ("aaa")
5635
47bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808
5636
select test.database(), test.database ();
5637
test.database() test.database ()
5638
Stored function database Stored function database
5639
select test.current_user(), test.current_user ();
5640
test.current_user() test.current_user ()
5641
Stored function current_user Stored function current_user
5642
select test.md5("aaa"), test.md5 ("aaa");
5643
test.md5("aaa") test.md5 ("aaa")
5644
Stored function md5 Stored function md5
5646
select database(), database ();
5647
database() database ()
5649
select current_user(), current_user ();
5650
current_user() current_user ()
5651
root@localhost root@localhost
5652
select md5("aaa"), md5 ("aaa");
5653
md5("aaa") md5 ("aaa")
5654
47bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808
5655
select test.database(), test.database ();
5656
test.database() test.database ()
5657
Stored function database Stored function database
5658
select test.current_user(), test.current_user ();
5659
test.current_user() test.current_user ()
5660
Stored function current_user Stored function current_user
5661
select test.md5("aaa"), test.md5 ("aaa");
5662
test.md5("aaa") test.md5 ("aaa")
5663
Stored function md5 Stored function md5
5664
SET @@sql_mode=@save_sql_mode;
5665
drop function test.database;
5666
drop function test.current_user;
5670
DROP TABLE IF EXISTS bug23760|
5671
DROP TABLE IF EXISTS bug23760_log|
5672
DROP PROCEDURE IF EXISTS bug23760_update_log|
5673
DROP PROCEDURE IF EXISTS bug23760_test_row_count|
5674
DROP FUNCTION IF EXISTS bug23760_rc_test|
5675
CREATE TABLE bug23760 (
5676
id INT NOT NULL AUTO_INCREMENT ,
5680
CREATE TABLE bug23760_log (
5681
id INT NOT NULL AUTO_INCREMENT ,
5682
reason VARCHAR(50)NULL ,
5683
ammount INT NOT NULL ,
5686
CREATE PROCEDURE bug23760_update_log(r Varchar(50), a INT)
5688
INSERT INTO bug23760_log (reason, ammount) VALUES(r, a);
5690
CREATE PROCEDURE bug23760_test_row_count()
5692
UPDATE bug23760 SET num = num + 1;
5693
CALL bug23760_update_log('Test is working', ROW_COUNT());
5694
UPDATE bug23760 SET num = num - 1;
5696
CREATE PROCEDURE bug23760_test_row_count2(level INT)
5699
UPDATE bug23760 SET num = num + 1;
5700
CALL bug23760_update_log('Test2 is working', ROW_COUNT());
5701
CALL bug23760_test_row_count2(level - 1);
5704
CREATE FUNCTION bug23760_rc_test(in_var INT) RETURNS INT RETURN in_var|
5705
INSERT INTO bug23760 (num) VALUES (0), (1), (1), (2), (3), (5), (8)|
5709
CALL bug23760_test_row_count()|
5710
SELECT * FROM bug23760_log ORDER BY id|
5713
SET @save_max_sp_recursion= @@max_sp_recursion_depth|
5714
SELECT @save_max_sp_recursion|
5715
@save_max_sp_recursion
5717
SET max_sp_recursion_depth= 5|
5718
SELECT @@max_sp_recursion_depth|
5719
@@max_sp_recursion_depth
5721
CALL bug23760_test_row_count2(2)|
5725
SELECT * FROM bug23760_log ORDER BY id|
5728
2 Test2 is working 7
5729
3 Test2 is working 7
5730
SELECT * FROM bug23760 ORDER by ID|
5739
SET max_sp_recursion_depth= @save_max_sp_recursion|
5740
SELECT bug23760_rc_test(123)|
5741
bug23760_rc_test(123)
5743
INSERT INTO bug23760 (num) VALUES (13), (21), (34), (55)|
5744
SELECT bug23760_rc_test(ROW_COUNT())|
5745
bug23760_rc_test(ROW_COUNT())
5747
DROP TABLE bug23760, bug23760_log|
5748
DROP PROCEDURE bug23760_update_log|
5749
DROP PROCEDURE bug23760_test_row_count|
5750
DROP PROCEDURE bug23760_test_row_count2|
5751
DROP FUNCTION bug23760_rc_test|
5752
DROP PROCEDURE IF EXISTS bug24117|
5753
DROP TABLE IF EXISTS t3|
5754
CREATE TABLE t3(c1 ENUM('abc'))|
5755
INSERT INTO t3 VALUES('abc')|
5756
CREATE PROCEDURE bug24117()
5758
DECLARE t3c1 ENUM('abc');
5759
DECLARE mycursor CURSOR FOR SELECT c1 FROM t3;
5762
FETCH mycursor INTO t3c1;
5766
DROP PROCEDURE bug24117|
5768
drop function if exists func_8407_a|
5769
drop function if exists func_8407_b|
5770
create function func_8407_a() returns int
5773
declare continue handler for sqlexception
5776
select 1 from no_such_view limit 1 into x;
5779
create function func_8407_b() returns int
5781
declare x int default 0;
5782
declare continue handler for sqlstate '42S02'
5786
case (select 1 from no_such_view limit 1)
5787
when 1 then set x:= x+1;
5788
when 2 then set x:= x+2;
5794
select func_8407_a()|
5797
select func_8407_b()|
5800
drop function func_8407_a|
5801
drop function func_8407_b|
5802
drop table if exists table_26503|
5803
drop procedure if exists proc_26503_ok_1|
5804
drop procedure if exists proc_26503_ok_2|
5805
drop procedure if exists proc_26503_ok_3|
5806
drop procedure if exists proc_26503_ok_4|
5807
create table table_26503(a int unique)|
5808
create procedure proc_26503_ok_1(v int)
5810
declare i int default 5;
5811
declare continue handler for sqlexception
5813
select 'caught something';
5818
select 'looping', i;
5823
select 'leaving handler';
5825
select 'do something';
5826
insert into table_26503 values (v);
5827
select 'do something again';
5828
insert into table_26503 values (v);
5830
create procedure proc_26503_ok_2(v int)
5832
declare i int default 5;
5833
declare continue handler for sqlexception
5835
select 'caught something';
5840
select 'looping', i;
5845
select 'leaving handler';
5847
select 'do something';
5848
insert into table_26503 values (v);
5849
select 'do something again';
5850
insert into table_26503 values (v);
5852
create procedure proc_26503_ok_3(v int)
5854
declare i int default 5;
5857
declare continue handler for sqlexception
5859
select 'caught something';
5864
select 'looping', i;
5869
select 'leaving handler';
5871
select 'do something';
5872
insert into table_26503 values (v);
5873
select 'do something again';
5874
insert into table_26503 values (v);
5877
create procedure proc_26503_ok_4(v int)
5879
declare i int default 5;
5882
declare continue handler for sqlexception
5884
select 'caught something';
5889
select 'looping', i;
5894
select 'leaving handler';
5896
select 'do something';
5897
insert into table_26503 values (v);
5898
select 'do something again';
5899
insert into table_26503 values (v);
5902
call proc_26503_ok_1(1)|
5921
call proc_26503_ok_2(2)|
5932
call proc_26503_ok_3(3)|
5951
call proc_26503_ok_4(4)|
5962
drop table table_26503|
5963
drop procedure proc_26503_ok_1|
5964
drop procedure proc_26503_ok_2|
5965
drop procedure proc_26503_ok_3|
5966
drop procedure proc_26503_ok_4|
5967
DROP FUNCTION IF EXISTS bug25373|
5968
CREATE FUNCTION bug25373(p1 INTEGER) RETURNS INTEGER
5969
LANGUAGE SQL DETERMINISTIC
5971
CREATE TABLE t3 (f1 INT, f2 FLOAT)|
5972
INSERT INTO t3 VALUES (1, 3.4), (1, 2), (1, 0.9), (2, 8), (2, 7)|
5973
SELECT SUM(f2), bug25373(f1) FROM t3 GROUP BY bug25373(f1) WITH ROLLUP|
5974
SUM(f2) bug25373(f1)
5977
21.3000000715256 NULL
5978
DROP FUNCTION bug25373|
5980
DROP DATABASE IF EXISTS mysqltest1|
5981
DROP DATABASE IF EXISTS mysqltest2|
5982
CREATE DATABASE mysqltest1|
5983
CREATE DATABASE mysqltest2|
5984
CREATE PROCEDURE mysqltest1.p1()
5985
DROP DATABASE mysqltest2|
5987
CALL mysqltest1.p1()|
5989
Note 1049 Unknown database 'mysqltest2'
5993
DROP DATABASE mysqltest1|
5995
drop function if exists bug20777|
5996
drop table if exists examplebug20777|
5997
create function bug20777(f1 bigint unsigned) returns bigint unsigned
5999
set f1 = (f1 - 10); set f1 = (f1 + 10);
6002
select bug20777(9223372036854775803) as '9223372036854775803 2**63-5';
6003
9223372036854775803 2**63-5
6005
select bug20777(9223372036854775804) as '9223372036854775804 2**63-4';
6006
9223372036854775804 2**63-4
6008
select bug20777(9223372036854775805) as '9223372036854775805 2**63-3';
6009
9223372036854775805 2**63-3
6011
select bug20777(9223372036854775806) as '9223372036854775806 2**63-2';
6012
9223372036854775806 2**63-2
6014
select bug20777(9223372036854775807) as '9223372036854775807 2**63-1';
6015
9223372036854775807 2**63-1
6017
select bug20777(9223372036854775808) as '9223372036854775808 2**63+0';
6018
9223372036854775808 2**63+0
6020
select bug20777(9223372036854775809) as '9223372036854775809 2**63+1';
6021
9223372036854775809 2**63+1
6023
select bug20777(9223372036854775810) as '9223372036854775810 2**63+2';
6024
9223372036854775810 2**63+2
6026
select bug20777(-9223372036854775808) as 'lower bounds signed bigint';
6027
lower bounds signed bigint
6030
Warning 1264 Out of range value for column 'f1' at row 1
6031
select bug20777(9223372036854775807) as 'upper bounds signed bigint';
6032
upper bounds signed bigint
6034
select bug20777(0) as 'lower bounds unsigned bigint';
6035
lower bounds unsigned bigint
6037
select bug20777(18446744073709551615) as 'upper bounds unsigned bigint';
6038
upper bounds unsigned bigint
6039
18446744073709551615
6040
select bug20777(18446744073709551616) as 'upper bounds unsigned bigint + 1';
6041
upper bounds unsigned bigint + 1
6042
18446744073709551615
6044
Warning 1264 Out of range value for column 'f1' at row 1
6045
select bug20777(-1) as 'lower bounds unsigned bigint - 1';
6046
lower bounds unsigned bigint - 1
6049
Warning 1264 Out of range value for column 'f1' at row 1
6050
create table examplebug20777 as select
6052
bug20777(9223372036854775806) as '2**63-2',
6053
bug20777(9223372036854775807) as '2**63-1',
6054
bug20777(9223372036854775808) as '2**63',
6055
bug20777(9223372036854775809) as '2**63+1',
6056
bug20777(18446744073709551614) as '2**64-2',
6057
bug20777(18446744073709551615) as '2**64-1',
6058
bug20777(18446744073709551616) as '2**64',
6060
bug20777(-1) as '-1';
6062
Warning 1264 Out of range value for column 'f1' at row 1
6063
Warning 1264 Out of range value for column 'f1' at row 1
6064
insert into examplebug20777 values (1, 9223372036854775806, 9223372036854775807, 223372036854775808, 9223372036854775809, 18446744073709551614, 18446744073709551615, 8446744073709551616, 0, -1);
6066
Warning 1264 Out of range value for column '-1' at row 1
6067
show create table examplebug20777;
6069
examplebug20777 CREATE TABLE `examplebug20777` (
6070
`i` int(1) NOT NULL DEFAULT '0',
6071
`2**63-2` bigint(20) unsigned DEFAULT NULL,
6072
`2**63-1` bigint(20) unsigned DEFAULT NULL,
6073
`2**63` bigint(20) unsigned DEFAULT NULL,
6074
`2**63+1` bigint(20) unsigned DEFAULT NULL,
6075
`2**64-2` bigint(20) unsigned DEFAULT NULL,
6076
`2**64-1` bigint(20) unsigned DEFAULT NULL,
6077
`2**64` bigint(20) unsigned DEFAULT NULL,
6078
`0` bigint(20) unsigned DEFAULT NULL,
6079
`-1` bigint(20) unsigned DEFAULT NULL
6080
) ENGINE=MyISAM DEFAULT CHARSET=latin1
6081
select * from examplebug20777 order by i;
6082
i 2**63-2 2**63-1 2**63 2**63+1 2**64-2 2**64-1 2**64 0 -1
6083
0 9223372036854775806 9223372036854775807 9223372036854775808 9223372036854775809 18446744073709551614 18446744073709551615 18446744073709551615 0 0
6084
1 9223372036854775806 9223372036854775807 223372036854775808 9223372036854775809 18446744073709551614 18446744073709551615 8446744073709551616 0 0
6085
drop table examplebug20777;
6086
select bug20777(18446744073709551613)+1;
6087
bug20777(18446744073709551613)+1
6088
18446744073709551614
6089
drop function bug20777;
6090
DROP FUNCTION IF EXISTS bug5274_f1|
6091
DROP FUNCTION IF EXISTS bug5274_f2|
6092
CREATE FUNCTION bug5274_f1(p1 CHAR) RETURNS CHAR
6093
RETURN CONCAT(p1, p1)|
6094
CREATE FUNCTION bug5274_f2() RETURNS CHAR
6096
DECLARE v1 INT DEFAULT 0;
6097
DECLARE v2 CHAR DEFAULT 'x';
6100
SET v2 = bug5274_f1(v2);
6104
SELECT bug5274_f2()|
6108
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6109
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6110
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6111
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6112
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6113
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6114
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6115
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6116
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6117
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6118
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6119
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6120
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6121
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6122
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6123
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6124
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6125
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6126
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6127
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6128
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6129
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6130
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6131
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6132
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6133
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6134
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6135
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6136
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6137
Warning 1265 Data truncated for column 'bug5274_f1' at row 1
6138
DROP FUNCTION bug5274_f1|
6139
DROP FUNCTION bug5274_f2|
6140
drop procedure if exists proc_21513|
6141
create procedure proc_21513()`my_label`:BEGIN END|
6142
show create procedure proc_21513|
6143
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
6144
proc_21513 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_21513`()
6145
`my_label`:BEGIN END utf8 utf8_general_ci latin1_swedish_ci
6146
drop procedure proc_21513|
6149
CREATE TABLE t1 (a int auto_increment primary key) engine=MyISAM;
6150
CREATE TABLE t2 (a int auto_increment primary key, b int) engine=innodb;
6152
CREATE function bug27354() RETURNS int not deterministic
6154
insert into t1 values (null);
6158
update t2 set b=1 where a=bug27354();
6159
select count(t_1.a),count(t_2.a) from t1 as t_1, t2 as t_2 /* must be 0,0 */;
6160
count(t_1.a) count(t_2.a)
6162
insert into t2 values (1,1),(2,2),(3,3);
6163
update t2 set b=-b where a=bug27354();
6164
select * from t2 /* must return 1,-1 ... */;
6169
select count(*) from t1 /* must be 3 */;
6173
drop function bug27354;
6174
CREATE TABLE t1 (a INT);
6175
INSERT INTO t1 VALUES (1),(2);
6176
CREATE FUNCTION metered(a INT) RETURNS INT RETURN 12;
6177
CREATE VIEW v1 AS SELECT test.metered(a) as metered FROM t1;
6178
SHOW CREATE VIEW v1;
6179
View Create View character_set_client collation_connection
6180
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`metered`(`t1`.`a`) AS `metered` from `t1` utf8 utf8_general_ci
6182
DROP FUNCTION metered;
6185
CREATE TABLE t1 (c1 INT);
6186
CREATE VIEW v1 AS SELECT * FROM t1;
6187
PREPARE s1 FROM 'SELECT c1 FROM v1';
6192
CREATE PROCEDURE p1(IN loops BIGINT(19) UNSIGNED)
6196
SET loops = loops - 1;
6199
CREATE PROCEDURE p2(IN loops BIGINT(19) UNSIGNED)
6203
CALL p1(@p1_p2_cnt);
6204
SET loops = loops - 1;
6207
CREATE FUNCTION f1(loops INT UNSIGNED)
6212
SELECT c1 INTO tmp FROM v1;
6213
SET loops = loops - 1;
6231
Warning 1329 No data - zero rows fetched, selected, or processed
6232
Warning 1329 No data - zero rows fetched, selected, or processed
6233
PREPARE s1 FROM 'SELECT f1(2)';
6238
Warning 1329 No data - zero rows fetched, selected, or processed
6239
Warning 1329 No data - zero rows fetched, selected, or processed
6244
Warning 1329 No data - zero rows fetched, selected, or processed
6245
Warning 1329 No data - zero rows fetched, selected, or processed
6251
drop database if exists mysqltest_db1;
6252
create database mysqltest_db1;
6253
create procedure mysqltest_db1.sp_bug28551() begin end;
6254
call mysqltest_db1.sp_bug28551();
6257
drop database mysqltest_db1;
6258
drop database if exists mysqltest_db1;
6259
drop table if exists test.t1;
6260
create database mysqltest_db1;
6262
drop database mysqltest_db1;
6263
create table test.t1 (id int);
6264
insert into test.t1 (id) values (1);
6265
create procedure test.sp_bug29050() begin select * from t1; end//
6268
call test.sp_bug29050();
6274
drop procedure sp_bug29050;
6277
CREATE PROCEDURE p1()
6279
DECLARE ļæ½ļæ½ļæ½ INT;
6287
drop procedure if exists proc_25411_a;
6288
drop procedure if exists proc_25411_b;
6289
drop procedure if exists proc_25411_c;
6290
create procedure proc_25411_a()
6296
/*!00000 select 4; */
6297
/*!99999 select 5; */
6300
create procedure proc_25411_b(
6310
create procedure proc_25411_c()
6312
select 1/*!,2*//*!00000,3*//*!99999,4*/;
6313
select 1/*! ,2*//*!00000 ,3*//*!99999 ,4*/;
6314
select 1/*!,2 *//*!00000,3 *//*!99999,4 */;
6315
select 1/*! ,2 *//*!00000 ,3 *//*!99999 ,4 */;
6316
select 1 /*!,2*/ /*!00000,3*/ /*!99999,4*/ ;
6319
show create procedure proc_25411_a;
6320
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
6321
proc_25411_a CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_25411_a`()
6329
end latin1 latin1_swedish_ci latin1_swedish_ci
6330
call proc_25411_a();
6339
show create procedure proc_25411_b;
6340
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
6341
proc_25411_b CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_25411_b`(
6349
end latin1 latin1_swedish_ci latin1_swedish_ci
6350
select name, param_list, body from mysql.proc where name like "%25411%";
6351
name param_list body
6375
call proc_25411_b(10, 20);
6378
show create procedure proc_25411_c;
6379
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
6380
proc_25411_c CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_25411_c`()
6387
end latin1 latin1_swedish_ci latin1_swedish_ci
6388
call proc_25411_c();
6399
drop procedure proc_25411_a;
6400
drop procedure proc_25411_b;
6401
drop procedure proc_25411_c;
6402
drop procedure if exists proc_26302;
6403
create procedure proc_26302()
6404
select 1 /* testing */;
6405
show create procedure proc_26302;
6406
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
6407
proc_26302 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_26302`()
6408
select 1 /* testing */ latin1 latin1_swedish_ci latin1_swedish_ci
6409
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES
6410
where ROUTINE_NAME = "proc_26302";
6411
ROUTINE_NAME ROUTINE_DEFINITION
6412
proc_26302 select 1 /* testing */
6413
drop procedure proc_26302;
6414
CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 2;
6415
CREATE FUNCTION f2(I INT) RETURNS INT DETERMINISTIC RETURN 3;
6416
CREATE TABLE t1 (c1 INT, INDEX(c1));
6417
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
6418
CREATE VIEW v1 AS SELECT c1 FROM t1;
6419
EXPLAIN SELECT * FROM t1 WHERE c1=1;
6420
id select_type table type possible_keys key key_len ref rows Extra
6421
1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
6422
EXPLAIN SELECT * FROM t1 WHERE c1=f1();
6423
id select_type table type possible_keys key key_len ref rows Extra
6424
1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
6425
EXPLAIN SELECT * FROM v1 WHERE c1=1;
6426
id select_type table type possible_keys key key_len ref rows Extra
6427
1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
6428
EXPLAIN SELECT * FROM v1 WHERE c1=f1();
6429
id select_type table type possible_keys key key_len ref rows Extra
6430
1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
6431
EXPLAIN SELECT * FROM t1 WHERE c1=f2(10);
6432
id select_type table type possible_keys key key_len ref rows Extra
6433
1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
6434
EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1);
6435
id select_type table type possible_keys key key_len ref rows Extra
6436
1 SIMPLE t1 index NULL c1 5 NULL 5 Using where; Using index
6437
EXPLAIN SELECT * FROM t1 WHERE c1=f2(rand());
6438
id select_type table type possible_keys key key_len ref rows Extra
6439
1 SIMPLE t1 index NULL c1 5 NULL 5 Using where; Using index
6444
create function f1()
6448
sql security definer
6455
create view v1 as select 1 as one, f1() as days;
6456
show create view test.v1;
6457
View Create View character_set_client collation_connection
6458
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select 1 AS `one`,`f1`() AS `days` latin1 latin1_swedish_ci
6459
select column_name from information_schema.columns
6460
where table_name='v1' and table_schema='test';
6469
DROP PROCEDURE IF EXISTS p1;
6470
DROP PROCEDURE IF EXISTS p2;
6471
DROP TABLE IF EXISTS t1;
6473
CREATE PROCEDURE p1(v DATETIME) CREATE TABLE t1 SELECT v;
6474
CREATE PROCEDURE p2(v INT) CREATE TABLE t1 SELECT v;
6477
SHOW CREATE TABLE t1;
6479
t1 CREATE TABLE `t1` (
6480
`v` datetime DEFAULT NULL
6481
) ENGINE=MyISAM DEFAULT CHARSET=latin1
6487
Warning 1264 Out of range value for column 'v' at row 1
6488
SHOW CREATE TABLE t1;
6490
t1 CREATE TABLE `t1` (
6491
`v` datetime DEFAULT NULL
6492
) ENGINE=MyISAM DEFAULT CHARSET=latin1
6497
SHOW CREATE TABLE t1;
6499
t1 CREATE TABLE `t1` (
6500
`v` bigint(11) DEFAULT NULL
6501
) ENGINE=MyISAM DEFAULT CHARSET=latin1
6507
Warning 1366 Incorrect integer value: 'text' for column 'v' at row 1
6508
SHOW CREATE TABLE t1;
6510
t1 CREATE TABLE `t1` (
6511
`v` bigint(11) DEFAULT NULL
6512
) ENGINE=MyISAM DEFAULT CHARSET=latin1
6527
DROP TABLE IF EXISTS t1;
6528
DROP FUNCTION IF EXISTS f1;
6529
DROP FUNCTION IF EXISTS f2;
6530
DROP FUNCTION IF EXISTS f3;
6531
DROP FUNCTION IF EXISTS f4;
6534
# - Create required objects.
6537
CREATE TABLE t1(c1 INT);
6539
INSERT INTO t1 VALUES (1), (2), (3);
6541
CREATE FUNCTION f1()
6546
CREATE FUNCTION f2(p INT)
6551
CREATE FUNCTION f3()
6556
CREATE FUNCTION f4(p INT)
6565
SELECT f1() AS a FROM t1 GROUP BY a;
6569
SELECT f2(@a) AS a FROM t1 GROUP BY a;
6573
SELECT f3() AS a FROM t1 GROUP BY a;
6577
SELECT f4(0) AS a FROM t1 GROUP BY a;
6581
SELECT f4(@a) AS a FROM t1 GROUP BY a;
6603
DROP TABLE IF EXISTS t1;
6604
DROP TABLE IF EXISTS t2;
6605
DROP FUNCTION IF EXISTS f1;
6608
# - Create required objects.
6612
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
6613
barcode INT(8) UNSIGNED ZEROFILL nOT NULL,
6615
UNIQUE KEY barcode (barcode)
6618
INSERT INTO t1 (id, barcode) VALUES (1, 12345678);
6619
INSERT INTO t1 (id, barcode) VALUES (2, 12345679);
6621
CREATE TABLE test.t2 (
6622
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
6623
barcode BIGINT(11) UNSIGNED ZEROFILL NOT NULL,
6627
INSERT INTO test.t2 (id, barcode) VALUES (1, 12345106708);
6628
INSERT INTO test.t2 (id, barcode) VALUES (2, 12345106709);
6630
CREATE FUNCTION f1(p INT(8))
6631
RETURNS BIGINT(11) UNSIGNED
6633
RETURN FLOOR(p/1000)*1000000 + 100000 + FLOOR((p MOD 1000)/10)*100 + (p MOD 10);
6639
SELECT DISTINCT t1.barcode, f1(t1.barcode)
6642
ON f1(t1.barcode) = t2.barcode
6643
WHERE t1.barcode=12345678;
6644
barcode f1(t1.barcode)
6645
12345678 12345106708
6663
DROP TABLE IF EXISTS t1;
6664
DROP FUNCTION IF EXISTS f1;
6667
# - Create required objects.
6670
CREATE TABLE t1(id INT);
6672
INSERT INTO t1 VALUES (1), (2), (3);
6674
CREATE FUNCTION f1()
6676
NOT DETERMINISTIC NO SQL
6683
SELECT f1() FROM t1 GROUP BY 1;
6694
DROP PROCEDURE IF EXISTS db28318_a.t1;
6695
DROP PROCEDURE IF EXISTS db28318_b.t2;
6696
DROP DATABASE IF EXISTS db28318_a;
6697
DROP DATABASE IF EXISTS db28318_b;
6698
CREATE DATABASE db28318_a;
6699
CREATE DATABASE db28318_b;
6700
CREATE PROCEDURE db28318_a.t1() SELECT "db28318_a.t1";
6701
CREATE PROCEDURE db28318_b.t2() CALL t1();
6703
CALL db28318_b.t2();
6704
ERROR 42000: PROCEDURE db28318_b.t1 does not exist
6705
DROP PROCEDURE db28318_a.t1;
6706
DROP PROCEDURE db28318_b.t2;
6707
DROP DATABASE db28318_a;
6708
DROP DATABASE db28318_b;
6710
DROP TABLE IF EXISTS t1;
6711
DROP PROCEDURE IF EXISTS bug29770;
6712
CREATE TABLE t1(a int);
6713
CREATE PROCEDURE bug29770()
6715
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S22' SET @state:= 'run';
6716
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @exception:= 'run';
6720
SELECT @state, @exception;
6724
DROP PROCEDURE bug29770;
6726
drop table if exists t_33618;
6727
drop procedure if exists proc_33618;
6728
create table t_33618 (`a` int, unique(`a`), `b` varchar(30)) engine=myisam;
6729
insert into t_33618 (`a`,`b`) values (1,'1'),(2,'2');
6730
create procedure proc_33618(num int)
6732
declare count1 int default '0';
6733
declare vb varchar(30);
6734
declare last_row int;
6738
declare cur1 cursor for select `a` from t_33618;
6739
declare continue handler for not found set last_row = 1;
6745
declare exit handler for 1062 begin end;
6747
if (last_row = 1) then
6757
call proc_33618(20);
6759
drop procedure proc_33618;
6761
# Bug#30787: Stored function ignores user defined alias.
6764
drop function if exists func30787;
6765
create table t1(f1 int);
6766
insert into t1 values(1),(2);
6767
create function func30787(p1 int) returns int
6771
select (select func30787(f1)) as ttt from t1;
6775
drop function func30787;
6777
CREATE TABLE t1 (id INT);
6778
INSERT INTO t1 VALUES (1),(2),(3),(4);
6779
CREATE PROCEDURE test_sp()
6780
SELECT t1.* FROM t1 RIGHT JOIN t1 t2 ON t1.id=t2.id;
6793
DROP PROCEDURE test_sp;
6795
create table t1(c1 INT);
6796
create function f1(p1 int) returns varchar(32)
6798
create view v1 as select f1(c1) as parent_control_name from t1;
6799
create procedure p1()
6801
select parent_control_name as c1 from v1;
6811
drop procedure if exists `p2` $
6812
create procedure `p2`(in `a` text charset utf8)
6814
declare `pos` int default 1;
6815
declare `str` text charset utf8;
6817
select substr(`str`, `pos`+ 1 ) into `str`;
6819
call `p2`('s s s s s s');
6820
drop procedure `p2`;
6821
drop table if exists t1;
6822
drop procedure if exists p1;
6823
create procedure p1() begin select * from t1; end$
6825
ERROR 42S02: Table 'test.t1' doesn't exist
6826
create table t1 (a integer)$
6829
alter table t1 add b integer;
6834
# ------------------------------------------------------------------
6835
# -- End of 5.0 tests
6836
# ------------------------------------------------------------------
6846
DROP VIEW IF EXISTS v1;
6847
DROP VIEW IF EXISTS v2;
6848
DROP FUNCTION IF EXISTS f1;
6849
DROP FUNCTION IF EXISTS f2;
6852
# - Create required objects.
6855
CREATE FUNCTION f1() RETURNS VARCHAR(65525) RETURN 'Hello';
6857
CREATE FUNCTION f2() RETURNS TINYINT RETURN 1;
6859
CREATE VIEW v1 AS SELECT f1();
6861
CREATE VIEW v2 AS SELECT f2();
6867
SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v1';
6871
SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v2';
6885
# - Bug#24923: prepare.
6888
DROP FUNCTION IF EXISTS f1;
6891
# - Bug#24923: create required objects.
6894
CREATE FUNCTION f1(p INT)
6895
RETURNS ENUM ('Very_long_enum_element_identifier',
6896
'Another_very_long_enum_element_identifier')
6900
RETURN 'Very_long_enum_element_identifier';
6902
RETURN 'Another_very_long_enum_element_identifier';
6907
# - Bug#24923: check.
6912
Very_long_enum_element_identifier
6916
Another_very_long_enum_element_identifier
6918
SHOW CREATE FUNCTION f1;
6919
Function sql_mode Create Function character_set_client collation_connection Database Collation
6920
f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(p INT) RETURNS enum('Very_long_enum_element_identifier','Another_very_long_enum_element_identifier') CHARSET latin1
6924
RETURN 'Very_long_enum_element_identifier';
6926
RETURN 'Another_very_long_enum_element_identifier';
6928
END latin1 latin1_swedish_ci latin1_swedish_ci
6930
# - Bug#24923: cleanup.
6935
drop procedure if exists p;
6936
set @old_mode= @@sql_mode;
6937
set @@sql_mode= pow(2,32)-1;
6938
select @@sql_mode into @full_mode;
6939
create procedure p() begin end;
6943
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,?,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH
6944
set @@sql_mode= @old_mode;
6945
select replace(@full_mode, '?', 'NOT_USED') into @full_mode;
6946
select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mode;
6947
select name from mysql.proc where name = 'p' and sql_mode = @full_mode;
6951
CREATE DEFINER = 'root'@'localhost' PROCEDURE p1()
6954
SQL SECURITY DEFINER
6957
SHOW TABLE STATUS like 't1';
6959
CREATE TABLE t1 (f1 INT);
6966
CREATE TABLE t1 ( f1 integer, primary key (f1));
6967
CREATE TABLE t2 LIKE t1;
6968
CREATE TEMPORARY TABLE t3 LIKE t1;
6969
CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ;
6972
ERROR HY000: Can't reopen table: 'A'
6973
CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 );
6983
# Bug #46629: Item_in_subselect::val_int(): Assertion `0'
6984
# on subquery inside a SP
6986
CREATE TABLE t1(a INT);
6987
CREATE TABLE t2(a INT, b INT PRIMARY KEY);
6988
CREATE PROCEDURE p1 ()
6990
SELECT a FROM t1 A WHERE A.b IN (SELECT b FROM t2 AS B);
6993
ERROR 42S22: Unknown column 'A.b' in 'IN/ALL/ANY subquery'
6995
ERROR 42S22: Unknown column 'A.b' in 'IN/ALL/ANY subquery'
6999
# Bug#47627: SET @@{global.session}.local_variable in stored routine causes crash
7000
# Bug#48626: Crash or lost connection using SET for declared variables with @@
7002
DROP PROCEDURE IF EXISTS p1;
7003
DROP PROCEDURE IF EXISTS p2;
7004
DROP PROCEDURE IF EXISTS p3;
7005
CREATE PROCEDURE p1()
7007
DECLARE v INT DEFAULT 0;
7008
SET @@SESSION.v= 10;
7010
ERROR HY000: Unknown system variable 'v'
7011
CREATE PROCEDURE p2()
7013
DECLARE v INT DEFAULT 0;
7017
CREATE PROCEDURE p3()
7019
DECLARE v INT DEFAULT 0;
7022
ERROR HY000: Unknown system variable 'v'
7023
CREATE PROCEDURE p4()
7025
DECLARE v INT DEFAULT 0;
7028
ERROR HY000: Unknown system variable 'v'
7029
CREATE PROCEDURE p5()
7031
DECLARE init_connect INT DEFAULT 0;
7032
SET init_connect= 10;
7033
SET @@GLOBAL.init_connect= 'SELECT 1';
7034
SET @@SESSION.IDENTITY= 1;
7035
SELECT @@SESSION.IDENTITY;
7036
SELECT @@GLOBAL.init_connect;
7037
SELECT init_connect;
7039
CREATE PROCEDURE p6()
7041
DECLARE v INT DEFAULT 0;
7044
ERROR HY000: Unknown system variable 'v'
7045
SET @old_init_connect= @@GLOBAL.init_connect;
7049
@@GLOBAL.init_connect
7053
SET @@GLOBAL.init_connect= @old_init_connect;
7056
# ------------------------------------------------------------------
7057
# -- End of 5.1 tests
7058
# ------------------------------------------------------------------
added
removed
mysql-test/r/sp.result
