diff options
30 files changed, 595 insertions, 124 deletions
diff --git a/client/mysql.cc b/client/mysql.cc index 8d578aac58b..eb6970691bb 100644 --- a/client/mysql.cc +++ b/client/mysql.cc @@ -185,6 +185,7 @@ void tee_fprintf(FILE *file, const char *fmt, ...); void tee_fputs(const char *s, FILE *file); void tee_puts(const char *s, FILE *file); void tee_putc(int c, FILE *file); +static void tee_print_sized_data(const char *data, unsigned int length, unsigned int width); /* The names of functions that actually do the manipulation. */ static int get_options(int argc,char **argv); static int com_quit(String *str,char*), @@ -2308,20 +2309,29 @@ print_table_data(MYSQL_RES *result) for (uint off= 0; off < mysql_num_fields(result); off++) { const char *str= cur[off] ? cur[off] : "NULL"; + uint currlength; + uint maxlength; + uint numcells; + field= mysql_fetch_field(result); - uint maxlength= field->max_length; + maxlength= field->max_length; + currlength= (uint) lengths[off]; + numcells= charset_info->cset->numcells(charset_info, + str, str + currlength); if (maxlength > MAX_COLUMN_LENGTH) { - tee_fputs(str, PAGER); - tee_fputs(" |", PAGER); + tee_print_sized_data(str, currlength, maxlength); + tee_fputs(" |", PAGER); } else { - uint currlength= (uint) lengths[off]; - uint numcells= charset_info->cset->numcells(charset_info, - str, str + currlength); - tee_fprintf(PAGER, num_flag[off] ? "%*s |" : " %-*s|", - maxlength + currlength - numcells, str); + if (num_flag[off] != 0) + tee_fprintf(PAGER, " %-*s|", maxlength + currlength - numcells, str); + else + { + tee_print_sized_data(str, currlength, maxlength); + tee_fputs(" |", PAGER); + } } } (void) tee_fputs("\n", PAGER); @@ -2332,6 +2342,35 @@ print_table_data(MYSQL_RES *result) static void +tee_print_sized_data(const char *data, unsigned int length, unsigned int width) +{ + /* + It is not a number, so print each character justified to the left. + For '\0's print ASCII spaces instead, as '\0' is eaten by (at + least my) console driver, and that messes up the pretty table + grid. (The \0 is also the reason we can't use fprintf() .) + */ + unsigned int i; + const char *p; + + tee_putc(' ', PAGER); + + for (i= 0, p= data; i < length; i+= 1, p+= 1) + { + if (*p == '\0') + tee_putc((int)' ', PAGER); + else + tee_putc((int)*p, PAGER); + } + + i+= 1; + for ( ; i < width; i+= 1) + tee_putc((int)' ', PAGER); +} + + + +static void print_table_data_html(MYSQL_RES *result) { MYSQL_ROW cur; diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result index 40cdfc9848b..5f735ebe926 100644 --- a/mysql-test/r/federated.result +++ b/mysql-test/r/federated.result @@ -1558,6 +1558,49 @@ id 3 4 5 +DROP TABLE IF EXISTS federated.bug_17377_table; +CREATE TABLE federated.bug_17377_table ( +`fld_cid` bigint(20) NOT NULL auto_increment, +`fld_name` varchar(255) NOT NULL default '', +`fld_parentid` bigint(20) NOT NULL default '0', +`fld_delt` int(1) NOT NULL default '0', +PRIMARY KEY (`fld_cid`), +KEY `fld_parentid` (`fld_parentid`), +KEY `fld_delt` (`fld_delt`), +KEY `fld_cid` (`fld_cid`) +) ENGINE=MyISAM; +insert into federated.bug_17377_table( fld_name ) +values +("Mats"), ("Sivert"), ("Sigvard"), ("Torgny"), ("Torkel"); +DROP TABLE IF EXISTS federated.t1; +CREATE TABLE federated.t1 ( +`fld_cid` bigint(20) NOT NULL auto_increment, +`fld_name` varchar(255) NOT NULL default '', +`fld_parentid` bigint(20) NOT NULL default '0', +`fld_delt` int(1) NOT NULL default '0', +PRIMARY KEY (`fld_cid`), +KEY `fld_parentid` (`fld_parentid`), +KEY `fld_delt` (`fld_delt`), +KEY `fld_cid` (`fld_cid`) +) ENGINE=FEDERATED +CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/bug_17377_table'; +select * from federated.t1 where fld_parentid=0 and fld_delt=0 +order by fld_name; +fld_cid fld_name fld_parentid fld_delt +1 Mats 0 0 +3 Sigvard 0 0 +2 Sivert 0 0 +4 Torgny 0 0 +5 Torkel 0 0 +select * from federated.t1 where fld_parentid=0 and fld_delt=0; +fld_cid fld_name fld_parentid fld_delt +1 Mats 0 0 +2 Sivert 0 0 +3 Sigvard 0 0 +4 Torgny 0 0 +5 Torkel 0 0 +DROP TABLE federated.t1; +DROP TABLE federated.bug_17377_table; DROP TABLE IF EXISTS federated.t1; DROP DATABASE IF EXISTS federated; DROP TABLE IF EXISTS federated.t1; diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index 75a73c21958..96755932ca4 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -218,3 +218,23 @@ truncate(-5000111000111000155,-1) select truncate(15000111000111000155,-1); truncate(15000111000111000155,-1) 15000111000111000150 +set names utf8; +create table t1 +(f1 varchar(32) not null, +f2 smallint(5) unsigned not null, +f3 int(10) unsigned not null default '0') +engine=myisam default charset=utf8; +insert into t1 values ('zombie',0,0),('gold',1,10000),('silver',2,10000); +create table t2 +(f1 int(10) unsigned not null, +f2 int(10) unsigned not null, +f3 smallint(5) unsigned not null) +engine=myisam default charset=utf8; +insert into t2 values (16777216,16787215,1),(33554432,33564431,2); +select format(t2.f2-t2.f1+1,0) from t1,t2 +where t1.f2 = t2.f3 order by t1.f1; +format(t2.f2-t2.f1+1,0) +10,000 +10,000 +drop table t1, t2; +set names default; diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 661b7141a59..4815798d807 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -626,3 +626,21 @@ drop user mysqltest_7@; flush privileges; show grants for mysqltest_7@; ERROR 42000: There is no such grant defined for user 'mysqltest_7' on host '' +create database mysqltest; +use mysqltest; +create table t1(f1 int); +GRANT DELETE ON mysqltest.t1 TO mysqltest1@'%'; +GRANT SELECT ON mysqltest.t1 TO mysqltest1@'192.%'; +show grants for mysqltest1@'192.%'; +Grants for mysqltest1@192.% +GRANT USAGE ON *.* TO 'mysqltest1'@'192.%' +GRANT SELECT ON `mysqltest`.`t1` TO 'mysqltest1'@'192.%' +show grants for mysqltest1@'%'; +Grants for mysqltest1@% +GRANT USAGE ON *.* TO 'mysqltest1'@'%' +GRANT DELETE ON `mysqltest`.`t1` TO 'mysqltest1'@'%' +delete from mysql.user where user='mysqltest1'; +delete from mysql.db where user='mysqltest1'; +delete from mysql.tables_priv where user='mysqltest1'; +flush privileges; +drop database mysqltest; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 54536d6c32a..ecf76d477a0 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -475,6 +475,22 @@ b c a c b y 1 10 2 3 1 2 1 3 2 3 1 11 1 3 2 3 1 2 +select * from t5 natural join ((t1 natural join t2), (t3 natural join t4)); +y z b c a c b +11 4 1 10 2 3 1 +11 4 1 3 2 3 1 +select * from ((t1 natural join t2), (t3 natural join t4)) natural join t5; +y b c a c b z +11 1 10 2 3 1 4 +11 1 3 2 3 1 4 +select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4)); +y z b c a c b +11 4 1 10 2 3 1 +11 4 1 3 2 3 1 +select * from ((t1 natural join t2) cross join (t3 natural join t4)) natural join t5; +y b c a c b z +11 1 10 2 3 1 4 +11 1 3 2 3 1 4 select * from (t1 join t2 using (b)) join (t3 join t4 using (c)) using (c); c b a b y 3 1 2 1 11 @@ -665,6 +681,8 @@ select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6; ERROR 23000: Column 'c' in from clause is ambiguous select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6; ERROR 23000: Column 'c' in from clause is ambiguous +select * from t6 natural join ((t1 natural join t2), (t3 natural join t4)); +ERROR 23000: Column 'c' in from clause is ambiguous select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4); ERROR 23000: Column 'b' in from clause is ambiguous select * from (t3 natural join t4) natural join (t1 join t2 on t1.b=t2.b); @@ -673,6 +691,8 @@ select * from (t3 join (t4 natural join t5) on (b < z)) natural join (t1 natural join t2); ERROR 23000: Column 'c' in from clause is ambiguous +select * from (t1 natural join t2) natural join (t3 join (t4 natural join t5) on (b < z)); +ERROR 23000: Column 'c' in from clause is ambiguous select t1.b from v1a; ERROR 42S22: Unknown column 't1.b' in 'field list' select * from v1a join v1b on t1.b = t2.b; @@ -697,3 +717,30 @@ drop view v2b; drop view v3a; drop view v3b; drop view v4; +create table t1 (a1 int, a2 int); +create table t2 (a1 int, b int); +create table t3 (c1 int, c2 int); +create table t4 (c2 int); +insert into t1 values (1,1); +insert into t2 values (1,1); +insert into t3 values (1,1); +insert into t4 values (1); +select * from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2); +c2 a1 a2 b c1 +1 1 1 1 1 +select * from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2); +c2 c1 a1 a2 b +1 1 1 1 1 +select a2 from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2); +a2 +1 +select a2 from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2); +a2 +1 +select a2 from ((t1 join t2 using (a1)) join t3 on b=c1) join t4 using (c2); +a2 +1 +select a2 from ((t1 natural join t2) join t3 on b=c1) natural join t4; +a2 +1 +drop table t1,t2,t3,t4; diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index 611813d9c3f..57067bea36b 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -69,3 +69,10 @@ c_cp932 ソ ソ ソ ++----------------------+------------+--------+ +| concat('>',col1,'<') | col2 | col3 | ++----------------------+------------+--------+ +| >a < | b | 123421 | +| >a < | 0123456789 | 4 | +| >abcd< | | 4 | ++----------------------+------------+--------+ diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 6636f799b5d..9d4730d9e73 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -4811,4 +4811,60 @@ date_format(t3.d, pDateFormat) count(*) 2005-02 2 drop table t3| drop procedure bug17476| +drop table if exists t3| +drop procedure if exists bug16887| +create table t3 ( c varchar(1) )| +insert into t3 values +(' '),('.'),(';'),(','),('-'),('_'),('('),(')'),('/'),('\\')| +create procedure bug16887() +begin +declare i int default 10; +again: +while i > 0 do +begin +declare breakchar varchar(1); +declare done int default 0; +declare t3_cursor cursor for select c from t3; +declare continue handler for not found set done = 1; +set i = i - 1; +select i; +if i = 3 then +iterate again; +end if; +open t3_cursor; +loop +fetch t3_cursor into breakchar; +if done = 1 then +begin +close t3_cursor; +iterate again; +end; +end if; +end loop; +end; +end while; +end| +call bug16887()| +i +9 +i +8 +i +7 +i +6 +i +5 +i +4 +i +3 +i +2 +i +1 +i +0 +drop table t3| +drop procedure bug16887| drop table t1,t2; diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index edb5dee41be..16d3246ed3e 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -787,46 +787,52 @@ drop trigger t1_bi; ERROR 3D000: No database selected create table t1 (id int); create trigger t1_bi before insert on t1 for each row set @a:=new.id; +create trigger t1_ai after insert on test.t1 for each row set @b:=new.id; insert into t1 values (101); -select @a; -@a -101 +select @a, @b; +@a @b +101 101 select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; trigger_schema trigger_name event_object_schema event_object_table action_statement test t1_bi test t1 set @a:=new.id +test t1_ai test t1 set @b:=new.id rename table t1 to t2; insert into t2 values (102); -select @a; -@a -102 +select @a, @b; +@a @b +102 102 select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; trigger_schema trigger_name event_object_schema event_object_table action_statement test t1_bi test t2 set @a:=new.id +test t1_ai test t2 set @b:=new.id alter table t2 rename to t3; insert into t3 values (103); -select @a; -@a -103 +select @a, @b; +@a @b +103 103 select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; trigger_schema trigger_name event_object_schema event_object_table action_statement test t1_bi test t3 set @a:=new.id +test t1_ai test t3 set @b:=new.id alter table t3 rename to t4, add column val int default 0; insert into t4 values (104, 1); -select @a; -@a -104 +select @a, @b; +@a @b +104 104 select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; trigger_schema trigger_name event_object_schema event_object_table action_statement test t1_bi test t4 set @a:=new.id +test t1_ai test t4 set @b:=new.id drop trigger t1_bi; +drop trigger t1_ai; drop table t4; create database mysqltest; use mysqltest; diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index d781e48c54a..9c6a9b07544 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -772,3 +772,10 @@ productid zlevelprice 003trans 39.98 004trans 31.18 drop table t1, t2; +create table t1 (f1 decimal(5)); +insert into t1 values (40); +flush tables; +select f1 from t1 where f1 in (select f1 from t1); +f1 +40 +drop table t1; diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index a6587aa6a13..3c43fb1d1f9 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -1257,4 +1257,59 @@ INSERT INTO federated.t1 VALUES (); SELECT LAST_INSERT_ID(); SELECT * FROM federated.t1; +# +# Bug#17377 Federated Engine returns wrong Data, always the rows +# with the highest ID +# + +connection slave; + +--disable_warnings +DROP TABLE IF EXISTS federated.bug_17377_table; +--enable_warnings + +CREATE TABLE federated.bug_17377_table ( +`fld_cid` bigint(20) NOT NULL auto_increment, +`fld_name` varchar(255) NOT NULL default '', +`fld_parentid` bigint(20) NOT NULL default '0', +`fld_delt` int(1) NOT NULL default '0', +PRIMARY KEY (`fld_cid`), +KEY `fld_parentid` (`fld_parentid`), +KEY `fld_delt` (`fld_delt`), +KEY `fld_cid` (`fld_cid`) +) ENGINE=MyISAM; + +# Insert some test-data +insert into federated.bug_17377_table( fld_name ) +values +("Mats"), ("Sivert"), ("Sigvard"), ("Torgny"), ("Torkel"); + +connection master; +--disable_warnings +DROP TABLE IF EXISTS federated.t1; +--enable_warnings + +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval CREATE TABLE federated.t1 ( +`fld_cid` bigint(20) NOT NULL auto_increment, +`fld_name` varchar(255) NOT NULL default '', +`fld_parentid` bigint(20) NOT NULL default '0', +`fld_delt` int(1) NOT NULL default '0', +PRIMARY KEY (`fld_cid`), +KEY `fld_parentid` (`fld_parentid`), +KEY `fld_delt` (`fld_delt`), +KEY `fld_cid` (`fld_cid`) +) ENGINE=FEDERATED +CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/bug_17377_table'; + +select * from federated.t1 where fld_parentid=0 and fld_delt=0 +order by fld_name; + +select * from federated.t1 where fld_parentid=0 and fld_delt=0; + +DROP TABLE federated.t1; +connection slave; +DROP TABLE federated.bug_17377_table; + + source include/federated_cleanup.inc; diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 8dc4eb215c7..4041c267134 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -155,3 +155,25 @@ select truncate(-5000111000111000155,-1); # truncate on unsigned bigint select truncate(15000111000111000155,-1); +# +# Bug#16678 FORMAT gives wrong result if client run with default-character-set=utf8 +# +set names utf8; +create table t1 +(f1 varchar(32) not null, + f2 smallint(5) unsigned not null, + f3 int(10) unsigned not null default '0') +engine=myisam default charset=utf8; +insert into t1 values ('zombie',0,0),('gold',1,10000),('silver',2,10000); + +create table t2 +(f1 int(10) unsigned not null, + f2 int(10) unsigned not null, + f3 smallint(5) unsigned not null) +engine=myisam default charset=utf8; +insert into t2 values (16777216,16787215,1),(33554432,33564431,2); + +select format(t2.f2-t2.f1+1,0) from t1,t2 +where t1.f2 = t2.f3 order by t1.f1; +drop table t1, t2; +set names default; diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index 9a8b7a19c59..97f13381557 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -511,4 +511,20 @@ flush privileges; # BUG#16297(flush should be removed when that bug is fixed) --error 1141 show grants for mysqltest_7@; +# +# Bug#14385: GRANT and mapping to correct user account problems +# +create database mysqltest; +use mysqltest; +create table t1(f1 int); +GRANT DELETE ON mysqltest.t1 TO mysqltest1@'%'; +GRANT SELECT ON mysqltest.t1 TO mysqltest1@'192.%'; +show grants for mysqltest1@'192.%'; +show grants for mysqltest1@'%'; +delete from mysql.user where user='mysqltest1'; +delete from mysql.db where user='mysqltest1'; +delete from mysql.tables_priv where user='mysqltest1'; +flush privileges; +drop database mysqltest; + # End of 4.1 tests diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 90abc61ca07..f6a57d5e230 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -408,11 +408,10 @@ select * from t1 natural join (t2 natural join (t3 natural join t4)); select * from t5 natural right join (t4 natural right join ((t2 natural right join t1) natural right join t3)); select * from (t1 natural join t2), (t3 natural join t4); -- MySQL extension - nested comma ',' operator instead of cross join. --- BUG#15357 - natural join with nested cross-join results in incorrect columns --- select * from t5 natural join ((t1 natural join t2), (t3 natural join t4)); --- select * from ((t1 natural join t2), (t3 natural join t4)) natural join t5; --- select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4)); --- select * from ((t1 natural join t2) cross join (t3 natural join t4)) natural join t5; +select * from t5 natural join ((t1 natural join t2), (t3 natural join t4)); +select * from ((t1 natural join t2), (t3 natural join t4)) natural join t5; +select * from t5 natural join ((t1 natural join t2) cross join (t3 natural join t4)); +select * from ((t1 natural join t2) cross join (t3 natural join t4)) natural join t5; select * from (t1 join t2 using (b)) join (t3 join t4 using (c)) using (c); select * from (t1 join t2 using (b)) natural join (t3 join t4 using (c)); @@ -500,8 +499,7 @@ select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6; -- error 1052 select * from ((t1 natural join t2), (t3 natural join t4)) natural join t6; -- error 1052 --- BUG#15357: doesn't detect non-unique column 'c', as in the above query. --- select * from t6 natural join ((t1 natural join t2), (t3 natural join t4)); +select * from t6 natural join ((t1 natural join t2), (t3 natural join t4)); -- error 1052 select * from (t1 join t2 on t1.b=t2.b) natural join (t3 natural join t4); -- error 1052 @@ -512,11 +510,7 @@ select * from (t3 join (t4 natural join t5) on (b < z)) natural join (t1 natural join t2); -- error 1052 --- BUG#15357: this query should return an ambiguous column error --- Expected result: the query must return error with duplicate column 'c' ---select * from (t1 natural join t2) --- natural join --- (t3 join (t4 natural join t5) on (b < z)); +select * from (t1 natural join t2) natural join (t3 join (t4 natural join t5) on (b < z)); -- error 1054 select t1.b from v1a; @@ -546,4 +540,27 @@ drop view v3a; drop view v3b; drop view v4; +# +# BUG#15229 - columns of nested joins that are not natural joins incorrectly +# materialized +# +create table t1 (a1 int, a2 int); +create table t2 (a1 int, b int); +create table t3 (c1 int, c2 int); +create table t4 (c2 int); + +insert into t1 values (1,1); +insert into t2 values (1,1); +insert into t3 values (1,1); +insert into t4 values (1); + +select * from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2); +select * from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2); +select a2 from t1 join t2 using (a1) join t3 on b=c1 join t4 using (c2); +select a2 from t3 join (t1 join t2 using (a1)) on b=c1 join t4 using (c2); +select a2 from ((t1 join t2 using (a1)) join t3 on b=c1) join t4 using (c2); +select a2 from ((t1 natural join t2) join t3 on b=c1) natural join t4; + +drop table t1,t2,t3,t4; + # End of tests for WL#2486 - natural/using join diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index 4712e7e8266..dbf65845e6a 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -56,3 +56,8 @@ drop table t1; --exec $MYSQL --default-character-set=utf8 test -e "charset cp932; set character_set_client= cp932; select 'ƒ\'" --exec $MYSQL --default-character-set=utf8 test -e "/*charset cp932 */; set character_set_client= cp932; select 'ƒ\'" --exec $MYSQL --default-character-set=utf8 test -e "/*!\C cp932 */; set character_set_client= cp932; select 'ƒ\'" + +# +# Bug#16859 -- NULLs in columns must not truncate data as if a C-language "string". +# +--exec $MYSQL -t test -e "create table t1 (col1 binary(4), col2 varchar(10), col3 int); insert into t1 values ('a', 'b', 123421),('a ', '0123456789', 4), ('abcd', '', 4); select concat('>',col1,'<'), col2, col3 from t1; drop table t1;" 2>&1 diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 9626f8df4e4..e367f92bcd9 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -5643,6 +5643,7 @@ drop function bug17615| drop table t3| +# # BUG#17476: Stored procedure not returning data when it is called first # time per connection # @@ -5668,6 +5669,60 @@ drop procedure bug17476| # +# BUG#16887: Cursor causes server segfault +# +--disable_warnings +drop table if exists t3| +drop procedure if exists bug16887| +--enable_warnings + +create table t3 ( c varchar(1) )| + +insert into t3 values + (' '),('.'),(';'),(','),('-'),('_'),('('),(')'),('/'),('\\')| + +create procedure bug16887() +begin + declare i int default 10; + + again: + while i > 0 do + begin + declare breakchar varchar(1); + declare done int default 0; + declare t3_cursor cursor for select c from t3; + declare continue handler for not found set done = 1; + + set i = i - 1; + select i; + + if i = 3 then + iterate again; + end if; + + open t3_cursor; + + loop + fetch t3_cursor into breakchar; + + if done = 1 then + begin + close t3_cursor; + iterate again; + end; + end if; + end loop; + end; + end while; +end| + +call bug16887()| + +drop table t3| +drop procedure bug16887| + + +# # BUG#NNNN: New bug synopsis # #--disable_warnings diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 0ac57394c2f..1d68b519f1d 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -960,38 +960,42 @@ drop trigger t1_bi; connection default; # -# Test for bug #13525 "Rename table does not keep info of triggers" +# Tests for bug #13525 "Rename table does not keep info of triggers" +# and bug #17866 "Problem with renaming table with triggers with fully +# qualified subject table". # create table t1 (id int); create trigger t1_bi before insert on t1 for each row set @a:=new.id; +create trigger t1_ai after insert on test.t1 for each row set @b:=new.id; insert into t1 values (101); -select @a; +select @a, @b; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; rename table t1 to t2; # Trigger should work after rename insert into t2 values (102); -select @a; +select @a, @b; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; # Let us check that the same works for simple ALTER TABLE ... RENAME alter table t2 rename to t3; insert into t3 values (103); -select @a; +select @a, @b; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; # And for more complex ALTER TABLE alter table t3 rename to t4, add column val int default 0; insert into t4 values (104, 1); -select @a; +select @a, @b; select trigger_schema, trigger_name, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test'; # .TRN file should be updated with new table name drop trigger t1_bi; +drop trigger t1_ai; drop table t4; # Rename between different databases if triggers exist should fail create database mysqltest; diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 07347322453..441d750004e 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -377,3 +377,11 @@ insert INTO t2 SELECT * FROM t1; select * from t2; drop table t1, t2; +# +# Bug #17826 'type_decimal' fails with ps-protocol +# +create table t1 (f1 decimal(5)); +insert into t1 values (40); +flush tables; +select f1 from t1 where f1 in (select f1 from t1); +drop table t1; diff --git a/sql/ha_federated.cc b/sql/ha_federated.cc index 976f3739386..947c936c2e9 100644 --- a/sql/ha_federated.cc +++ b/sql/ha_federated.cc @@ -758,7 +758,7 @@ error: ha_federated::ha_federated(TABLE_SHARE *table_arg) :handler(&federated_hton, table_arg), - mysql(0), stored_result(0), scan_flag(0), + mysql(0), stored_result(0), ref_length(sizeof(MYSQL_ROW_OFFSET)), current_position(0) { trx_next= 0; @@ -2238,7 +2238,7 @@ int ha_federated::rnd_init(bool scan) containing the correct record, hence update the wrong row! */ - scan_flag= scan; + if (scan) { DBUG_PRINT("info", ("share->select_query %s", share->select_query)); @@ -2362,24 +2362,13 @@ void ha_federated::position(const byte *record) int ha_federated::rnd_pos(byte *buf, byte *pos) { DBUG_ENTER("ha_federated::rnd_pos"); - /* - we do not need to do any of this if there has been a scan performed - already, or if this is an update and index_read_idx already has a result - set in which to build it's update query from - */ - if (scan_flag) - { - int retval; - statistic_increment(table->in_use->status_var.ha_read_rnd_count, - &LOCK_status); - memcpy_fixed(¤t_position, pos, sizeof(MYSQL_ROW_OFFSET)); // pos - /* is not aligned */ - stored_result->current_row= 0; - stored_result->data_cursor= current_position; - retval= rnd_next(buf); - DBUG_RETURN(retval); - } - DBUG_RETURN(0); + + statistic_increment(table->in_use->status_var.ha_read_rnd_count, + &LOCK_status); + memcpy_fixed(¤t_position, pos, sizeof(MYSQL_ROW_OFFSET)); + stored_result->current_row= 0; + stored_result->data_cursor= current_position; + DBUG_RETURN(rnd_next(buf)); } diff --git a/sql/ha_federated.h b/sql/ha_federated.h index 953f4208bc5..458ef42ebda 100644 --- a/sql/ha_federated.h +++ b/sql/ha_federated.h @@ -153,7 +153,6 @@ class ha_federated: public handler FEDERATED_SHARE *share; /* Shared lock info */ MYSQL *mysql; /* MySQL connection */ MYSQL_RES *stored_result; - bool scan_flag; uint ref_length; uint fetch_num; // stores the fetch num MYSQL_ROW_OFFSET current_position; // Current position used by ::position() diff --git a/sql/hostname.cc b/sql/hostname.cc index 3b1eeb63d37..c5c337080cf 100644 --- a/sql/hostname.cc +++ b/sql/hostname.cc @@ -61,7 +61,7 @@ bool hostname_cache_init() if (!(hostname_cache=new hash_filo(HOST_CACHE_SIZE, offset, sizeof(struct in_addr),NULL, (hash_free_key) free, - &my_charset_latin1))) + &my_charset_bin))) return 1; hostname_cache->clear(); (void) pthread_mutex_init(&LOCK_hostname,MY_MUTEX_INIT_SLOW); diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 592b7e9dc6c..6b8a5c2fdf4 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -698,12 +698,6 @@ bool Item_in_optimizer::fix_left(THD *thd, Item **ref) return 1; cache->setup(args[0]); - /* - If it is preparation PS only then we do not know values of parameters => - cant't get there values and do not need that values. - */ - if (!thd->stmt_arena->is_stmt_prepare()) - cache->store(args[0]); if (cache->cols() == 1) { if ((used_tables_cache= args[0]->used_tables())) diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index 50ec0b36ce8..6a95a9e5d1f 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -471,7 +471,9 @@ public: void fix_length_and_dec() { collation.set(default_charset()); - max_length=args[0]->max_length+(args[0]->max_length-args[0]->decimals)/3; + uint char_length= args[0]->max_length/args[0]->collation.collation->mbmaxlen; + max_length= ((char_length + (char_length-args[0]->decimals)/3) * + collation.collation->mbmaxlen); } const char *func_name() const { return "format"; } void print(String *); diff --git a/sql/sp_pcontext.cc b/sql/sp_pcontext.cc index a8bd8cd2aa0..f69053a7c88 100644 --- a/sql/sp_pcontext.cc +++ b/sql/sp_pcontext.cc @@ -122,30 +122,38 @@ sp_pcontext::pop_context() } uint -sp_pcontext::diff_handlers(sp_pcontext *ctx) +sp_pcontext::diff_handlers(sp_pcontext *ctx, bool exclusive) { uint n= 0; sp_pcontext *pctx= this; + sp_pcontext *last_ctx= NULL; while (pctx && pctx != ctx) { n+= pctx->m_handlers; + last_ctx= pctx; pctx= pctx->parent_context(); } if (pctx) - return n; + return (exclusive && last_ctx ? n - last_ctx->m_handlers : n); return 0; // Didn't find ctx } uint -sp_pcontext::diff_cursors(sp_pcontext *ctx) +sp_pcontext::diff_cursors(sp_pcontext *ctx, bool exclusive) { + uint n= 0; sp_pcontext *pctx= this; + sp_pcontext *last_ctx= NULL; while (pctx && pctx != ctx) + { + n+= pctx->m_cursor.elements; + last_ctx= pctx; pctx= pctx->parent_context(); + } if (pctx) - return ctx->current_cursors() - pctx->current_cursors(); + return (exclusive && last_ctx ? n - last_ctx->m_cursor.elements : n); return 0; // Didn't find ctx } diff --git a/sql/sp_pcontext.h b/sql/sp_pcontext.h index d1cd7b964c2..872c7c1d505 100644 --- a/sql/sp_pcontext.h +++ b/sql/sp_pcontext.h @@ -119,11 +119,15 @@ class sp_pcontext : public Sql_alloc return m_parent; } + /* + Number of handlers/cursors to pop between this context and 'ctx'. + If 'exclusive' is true, don't count the last block we are leaving; + this is used for LEAVE where we will jump to the cpop/hpop instructions. + */ uint - diff_handlers(sp_pcontext *ctx); - + diff_handlers(sp_pcontext *ctx, bool exclusive); uint - diff_cursors(sp_pcontext *ctx); + diff_cursors(sp_pcontext *ctx, bool exclusive); // diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 3c2d1328a57..a47a9b03435 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -2387,7 +2387,10 @@ static GRANT_NAME *name_hash_search(HASH *name_hash, { if (exact) { - if (compare_hostname(&grant_name->host, host, ip)) + if ((host && + !my_strcasecmp(system_charset_info, host, + grant_name->host.hostname)) || + (ip && !strcmp(ip, grant_name->host.hostname))) return grant_name; } else diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 26d81eccce6..54cc2eae1e6 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4431,8 +4431,18 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2, Field_iterator_table_ref it_1, it_2; Natural_join_column *nj_col_1, *nj_col_2; Query_arena *arena, backup; - bool add_columns= TRUE; bool result= TRUE; + bool first_outer_loop= TRUE; + /* + Leaf table references to which new natural join columns are added + if the leaves are != NULL. + */ + TABLE_LIST *leaf_1= (table_ref_1->nested_join && + !table_ref_1->is_natural_join) ? + NULL : table_ref_1; + TABLE_LIST *leaf_2= (table_ref_2->nested_join && + !table_ref_2->is_natural_join) ? + NULL : table_ref_2; DBUG_ENTER("mark_common_columns"); DBUG_PRINT("info", ("operand_1: %s operand_2: %s", @@ -4441,36 +4451,14 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2, *found_using_fields= 0; arena= thd->activate_stmt_arena_if_needed(&backup); - /* - TABLE_LIST::join_columns could be allocated by the previous call to - store_natural_using_join_columns() for the lower level of nested tables. - */ - if (!table_ref_1->join_columns) - { - if (!(table_ref_1->join_columns= new List<Natural_join_column>)) - goto err; - table_ref_1->is_join_columns_complete= FALSE; - } - if (!table_ref_2->join_columns) - { - if (!(table_ref_2->join_columns= new List<Natural_join_column>)) - goto err; - table_ref_2->is_join_columns_complete= FALSE; - } - for (it_1.set(table_ref_1); !it_1.end_of_fields(); it_1.next()) { - bool is_created_1; bool found= FALSE; const char *field_name_1; - if (!(nj_col_1= it_1.get_or_create_column_ref(&is_created_1))) + if (!(nj_col_1= it_1.get_or_create_column_ref(leaf_1))) goto err; field_name_1= nj_col_1->name(); - /* If nj_col_1 was just created add it to the list of join columns. */ - if (is_created_1) - table_ref_1->join_columns->push_back(nj_col_1); - /* Find a field with the same name in table_ref_2. @@ -4481,17 +4469,12 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2, nj_col_2= NULL; for (it_2.set(table_ref_2); !it_2.end_of_fields(); it_2.next()) { - bool is_created_2; Natural_join_column *cur_nj_col_2; const char *cur_field_name_2; - if (!(cur_nj_col_2= it_2.get_or_create_column_ref(&is_created_2))) + if (!(cur_nj_col_2= it_2.get_or_create_column_ref(leaf_2))) goto err; cur_field_name_2= cur_nj_col_2->name(); - /* If nj_col_1 was just created add it to the list of join columns. */ - if (add_columns && is_created_2) - table_ref_2->join_columns->push_back(cur_nj_col_2); - /* Compare the two columns and check for duplicate common fields. A common field is duplicate either if it was already found in @@ -4510,9 +4493,15 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2, found= TRUE; } } - /* Force it_2.set() to use table_ref_2->join_columns. */ - table_ref_2->is_join_columns_complete= TRUE; - add_columns= FALSE; + if (first_outer_loop && leaf_2) + { + /* + Make sure that the next inner loop "knows" that all columns + are materialized already. + */ + leaf_2->is_join_columns_complete= TRUE; + first_outer_loop= FALSE; + } if (!found) continue; // No matching field @@ -4600,7 +4589,8 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2, ++(*found_using_fields); } } - table_ref_1->is_join_columns_complete= TRUE; + if (leaf_1) + leaf_1->is_join_columns_complete= TRUE; /* Everything is OK. @@ -5460,16 +5450,15 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, if (tables->is_natural_join) { - bool is_created; TABLE *field_table; /* In this case we are sure that the column ref will not be created because it was already created and stored with the natural join. */ Natural_join_column *nj_col; - if (!(nj_col= field_iterator.get_or_create_column_ref(&is_created))) + if (!(nj_col= field_iterator.get_natural_column_ref())) DBUG_RETURN(TRUE); - DBUG_ASSERT(nj_col->table_field && !is_created); + DBUG_ASSERT(nj_col->table_field); field_table= nj_col->table_ref->table; if (field_table) { diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc index e33dc52df8f..3e8b88c3a2e 100644 --- a/sql/sql_trigger.cc +++ b/sql/sql_trigger.cc @@ -1213,6 +1213,7 @@ Table_triggers_list::change_table_name_in_triggers(THD *thd, buff.append(def->str, before_on_len); buff.append(STRING_WITH_LEN("ON ")); append_identifier(thd, &buff, new_table_name->str, new_table_name->length); + buff.append(STRING_WITH_LEN(" ")); on_q_table_name_len= buff.length() - before_on_len; buff.append(on_table_name->str + on_table_name->length, def->length - (before_on_len + on_table_name->length)); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index a744d941a4b..6c8b52d243c 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -2463,10 +2463,10 @@ sp_proc_stmt_leave: uint ip= sp->instructions(); uint n; - n= ctx->diff_handlers(lab->ctx); + n= ctx->diff_handlers(lab->ctx, TRUE); /* Exclusive the dest. */ if (n) sp->add_instr(new sp_instr_hpop(ip++, ctx, n)); - n= ctx->diff_cursors(lab->ctx); + n= ctx->diff_cursors(lab->ctx, TRUE); /* Exclusive the dest. */ if (n) sp->add_instr(new sp_instr_cpop(ip++, ctx, n)); i= new sp_instr_jump(ip, ctx); @@ -2495,10 +2495,10 @@ sp_proc_stmt_iterate: uint ip= sp->instructions(); uint n; - n= ctx->diff_handlers(lab->ctx); + n= ctx->diff_handlers(lab->ctx, FALSE); /* Inclusive the dest. */ if (n) sp->add_instr(new sp_instr_hpop(ip++, ctx, n)); - n= ctx->diff_cursors(lab->ctx); + n= ctx->diff_cursors(lab->ctx, FALSE); /* Inclusive the dest. */ if (n) sp->add_instr(new sp_instr_cpop(ip++, ctx, n)); i= new sp_instr_jump(ip, ctx, lab->ip); /* Jump back */ @@ -10927,7 +10927,7 @@ view_check_option: trigger_tail: TRIGGER_SYM remember_name sp_name trg_action_time trg_event - ON remember_name table_ident remember_end FOR_SYM EACH_SYM ROW_SYM + ON remember_name table_ident FOR_SYM remember_name EACH_SYM ROW_SYM { LEX *lex= Lex; sp_head *sp; @@ -10945,7 +10945,7 @@ trigger_tail: lex->trigger_definition_begin= $2; lex->ident.str= $7; - lex->ident.length= $9 - $7; + lex->ident.length= $10 - $7; sp->m_type= TYPE_ENUM_TRIGGER; lex->sphead= sp; diff --git a/sql/table.cc b/sql/table.cc index d6a715ae1b4..a7b8949612e 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -3575,11 +3575,31 @@ GRANT_INFO *Field_iterator_table_ref::grant() SYNOPSIS Field_iterator_table_ref::get_or_create_column_ref() - is_created [out] set to TRUE if the column was created, - FALSE if we return an already created colum + parent_table_ref the parent table reference over which the + iterator is iterating DESCRIPTION - TODO + Create a new natural join column for the current field of the + iterator if no such column was created, or return an already + created natural join column. The former happens for base tables or + views, and the latter for natural/using joins. If a new field is + created, then the field is added to 'parent_table_ref' if it is + given, or to the original table referene of the field if + parent_table_ref == NULL. + + NOTES + This method is designed so that when a Field_iterator_table_ref + walks through the fields of a table reference, all its fields + are created and stored as follows: + - If the table reference being iterated is a stored table, view or + natural/using join, store all natural join columns in a list + attached to that table reference. + - If the table reference being iterated is a nested join that is + not natural/using join, then do not materialize its result + fields. This is OK because for such table references + Field_iterator_table_ref iterates over the fields of the nested + table references (recursively). In this way we avoid the storage + of unnecessay copies of result columns of nested joins. RETURN # Pointer to a column of a natural join (or its operand) @@ -3587,22 +3607,28 @@ GRANT_INFO *Field_iterator_table_ref::grant() */ Natural_join_column * -Field_iterator_table_ref::get_or_create_column_ref(bool *is_created) +Field_iterator_table_ref::get_or_create_column_ref(TABLE_LIST *parent_table_ref) { Natural_join_column *nj_col; + bool is_created= TRUE; + uint field_count; + TABLE_LIST *add_table_ref= parent_table_ref ? + parent_table_ref : table_ref; - *is_created= TRUE; if (field_it == &table_field_it) { /* The field belongs to a stored table. */ Field *field= table_field_it.field(); nj_col= new Natural_join_column(field, table_ref); + field_count= table_ref->table->s->fields; } else if (field_it == &view_field_it) { /* The field belongs to a merge view or information schema table. */ Field_translator *translated_field= view_field_it.field_translator(); nj_col= new Natural_join_column(translated_field, table_ref); + field_count= table_ref->field_translation_end - + table_ref->field_translation; } else { @@ -3611,12 +3637,43 @@ Field_iterator_table_ref::get_or_create_column_ref(bool *is_created) already created via one of the two constructor calls above. In this case we just return the already created column reference. */ - *is_created= FALSE; + DBUG_ASSERT(table_ref->is_join_columns_complete); + is_created= FALSE; nj_col= natural_join_it.column_ref(); DBUG_ASSERT(nj_col); } DBUG_ASSERT(!nj_col->table_field || nj_col->table_ref->table == nj_col->table_field->table); + + /* + If the natural join column was just created add it to the list of + natural join columns of either 'parent_table_ref' or to the table + reference that directly contains the original field. + */ + if (is_created) + { + /* Make sure not all columns were materialized. */ + DBUG_ASSERT(!add_table_ref->is_join_columns_complete); + if (!add_table_ref->join_columns) + { + /* Create a list of natural join columns on demand. */ + if (!(add_table_ref->join_columns= new List<Natural_join_column>)) + return NULL; + add_table_ref->is_join_columns_complete= FALSE; + } + add_table_ref->join_columns->push_back(nj_col); + /* + If new fields are added to their original table reference, mark if + all fields were added. We do it here as the caller has no easy way + of knowing when to do it. + If the fields are being added to parent_table_ref, then the caller + must take care to mark when all fields are created/added. + */ + if (!parent_table_ref && + add_table_ref->join_columns->elements == field_count) + add_table_ref->is_join_columns_complete= TRUE; + } + return nj_col; } diff --git a/sql/table.h b/sql/table.h index 2bde68e5919..930777d1993 100644 --- a/sql/table.h +++ b/sql/table.h @@ -834,7 +834,7 @@ public: GRANT_INFO *grant(); Item *create_item(THD *thd) { return field_it->create_item(thd); } Field *field() { return field_it->field(); } - Natural_join_column *get_or_create_column_ref(bool *is_created); + Natural_join_column *get_or_create_column_ref(TABLE_LIST *parent_table_ref); Natural_join_column *get_natural_column_ref(); }; |