diff options
| -rw-r--r-- | mysql-test/r/sp.result | 76 | ||||
| -rw-r--r-- | mysql-test/t/sp.test | 92 | ||||
| -rw-r--r-- | sql/sql_class.cc | 3 | ||||
| -rw-r--r-- | sql/sql_select.cc | 3 |
4 files changed, 165 insertions, 9 deletions
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index a828ae9cd19..b188a6ed57d 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -170,13 +170,6 @@ insert into test.t1 values (repeat("b2",3), x); set x = x-1; until x = 0 end repeat; drop procedure b2; -create procedure b3(x int) -repeat -select * from test.t1; # No INTO! -insert into test.t1 values (repeat("b3",3), x); -set x = x-1; -until x = 0 end repeat; -SELECT in a stored procedure must have INTO create procedure c(x int) hmm: while x > 0 do insert into test.t1 values ("c", x); @@ -431,5 +424,74 @@ n f drop table fac; drop procedure ifac; drop function fac; +drop table if exists primes; +create table primes ( +i int unsigned not null primary key, +p bigint unsigned not null +); +insert into primes values +( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13), +( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31), +(10, 37), (11, 41), (12, 43), (13, 47), (14, 53), +(15, 59), (16, 61), (17, 67), (18, 71), (19, 73), +(20, 79), (21, 83), (22, 89), (23, 97), (24, 101), +(25, 103), (26, 107), (27, 109), (28, 113), (29, 127), +(30, 131), (31, 137), (32, 139), (33, 149), (34, 151), +(35, 157), (36, 163), (37, 167), (38, 173), (39, 179), +(40, 181), (41, 191), (42, 193), (43, 197), (44, 199); +create procedure opp(n bigint unsigned, out pp bool) +begin +declare r double; +declare b, s bigint unsigned; +set b = 0, s = 0; +set r = sqrt(n); +again: +loop +if s = 45 then +set b = b+200, s = 0; +else +begin +declare p bigint unsigned; +select t.p into p from test.primes t where t.i = s; +if b+p > r then +set pp = 1; +leave again; +end if; +if mod(n, b+p) = 0 then +set pp = 0; +leave again; +end if; +set s = s+1; +end; +end if; +end loop again; +end; +create procedure ip(m int unsigned) +begin +declare p bigint unsigned; +declare i int unsigned; +set i=45, p=201; +while i < m do +begin +declare pp bool; +set pp = 0; +call opp(p, pp); +if pp then +insert into test.primes values (i, p); +set i = i+1; +end if; +set p = p+2; +end; +end while; +end; +call ip(200); +select * from primes where i=45 or i=100 or i=199; +i p +45 211 +100 557 +199 1229 +drop table primes; +drop procedure opp; +drop procedure ip; drop table t1; drop table t2; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index d56d5859940..0d6a84fa63b 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -454,7 +454,11 @@ drop function append| drop function fun| -# A "real" procedure and function example +# +# Some "real" examples +# + +# fac --disable_warnings drop table if exists fac| @@ -482,6 +486,92 @@ drop table fac| drop procedure ifac| drop function fac| + +# primes + +--disable_warnings +drop table if exists primes| +--enable_warnings + +create table primes ( + i int unsigned not null primary key, + p bigint unsigned not null +)| + +insert into primes values + ( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13), + ( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31), + (10, 37), (11, 41), (12, 43), (13, 47), (14, 53), + (15, 59), (16, 61), (17, 67), (18, 71), (19, 73), + (20, 79), (21, 83), (22, 89), (23, 97), (24, 101), + (25, 103), (26, 107), (27, 109), (28, 113), (29, 127), + (30, 131), (31, 137), (32, 139), (33, 149), (34, 151), + (35, 157), (36, 163), (37, 167), (38, 173), (39, 179), + (40, 181), (41, 191), (42, 193), (43, 197), (44, 199)| + +create procedure opp(n bigint unsigned, out pp bool) +begin + declare r double; + declare b, s bigint unsigned; + + set b = 0, s = 0; + set r = sqrt(n); + + again: + loop + if s = 45 then + set b = b+200, s = 0; + else + begin + declare p bigint unsigned; + + select t.p into p from test.primes t where t.i = s; + if b+p > r then + set pp = 1; + leave again; + end if; + if mod(n, b+p) = 0 then + set pp = 0; + leave again; + end if; + set s = s+1; + end; + end if; + end loop again; +end| + +create procedure ip(m int unsigned) +begin + declare p bigint unsigned; + declare i int unsigned; + + set i=45, p=201; + + while i < m do + begin + declare pp bool; + + set pp = 0; + call opp(p, pp); + if pp then + insert into test.primes values (i, p); + set i = i+1; + end if; + set p = p+2; + end; + end while; +end| + +# This isn't the fastest way in the world to compute prime numbers, so +# don't be too ambition. ;-) +call ip(200)| +# We don't want to select the entire table here, just pick a few +# examples. +select * from primes where i=45 or i=100 or i=199| +drop table primes| +drop procedure opp| +drop procedure ip| + delimiter ;| drop table t1; drop table t2; diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 5ac904393f5..ee14ce16593 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -972,6 +972,8 @@ int select_dumpvar::prepare(List<Item> &list, SELECT_LEX_UNIT *u) Item *item; my_var *mv; LEX_STRING *ls; + + row_count= 0; if (var_list.elements != list.elements) { my_error(ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT, MYF(0)); @@ -996,6 +998,7 @@ int select_dumpvar::prepare(List<Item> &list, SELECT_LEX_UNIT *u) } return 0; } + bool select_dumpvar::send_data(List<Item> &items) { List_iterator_fast<Item_func_set_user_var> li(vars); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4543c6bc5d0..c99da1928ca 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -191,7 +191,8 @@ int handle_select(THD *thd, LEX *lex, select_result *result) send_error(thd, 0, NullS); res= 1; } - delete result; + if (result != lex->result) + delete result; return res; } |