diff options
Diffstat (limited to 'mysql-test/t')
48 files changed, 1172 insertions, 184 deletions
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 22847a318d7..140cdccc218 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -670,6 +670,7 @@ alter table t1 max_rows=100000000000; show create table t1; drop table t1; + # End of 5.0 tests # diff --git a/mysql-test/t/create_not_windows.test b/mysql-test/t/create_not_windows.test index 71ad9ccd7fe..2c1700d9e49 100644 --- a/mysql-test/t/create_not_windows.test +++ b/mysql-test/t/create_not_windows.test @@ -18,3 +18,24 @@ show create table `about:text`; drop table `about:text`; # End of 5.0 tests + +# +# Bug#16532:mysql server assert in debug if table det is removed +# +use test; +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1(a int) engine=myisam; +insert into t1 values(1); +--system rm -f $MYSQLTEST_VARDIR/master-data/test/t1.frm +--echo "We get an error because the table is in the definition cache" +--error ER_TABLE_EXISTS_ERROR +create table t1(a int, b int); +--echo "Flush the cache and recreate the table anew to be able to drop it" +flush tables; +show open tables like "t%"; +create table t1(a int, b int, c int); +--echo "Try to select from the table. This should not crash the server" +select count(a) from t1; +drop table t1; diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test index 39a530ba733..fc0985afc21 100644 --- a/mysql-test/t/date_formats.test +++ b/mysql-test/t/date_formats.test @@ -265,6 +265,20 @@ select str_to_date("2003-04-05 g", "%Y-%m-%d") as f1, --enable_ps_protocol # +# Test of locale dependent date format (WL#2928 Date Translation NRE) +# +set names latin1; +select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); +set lc_time_names=ru_RU; +set names koi8r; +select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); +set lc_time_names=de_DE; +set names latin1; +select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); +set names latin1; +set lc_time_names=en_US; + +# # Bug #14016 # create table t1 (f1 datetime); diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 81eb7e6d35c..2845a638b07 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -14,11 +14,13 @@ #events : BUG#17619 2006-02-21 andrey Race conditions #events_scheduling : BUG#19170 2006-04-26 andrey Test case of 19170 fails on some platforms. Has to be checked. #im_instance_conf : Bug#20294 2006-06-06 monty Instance manager test im_instance_conf fails randomly -#im_options : Bug#20294 2006-06-06 monty Instance manager test im_instance_conf fails randomly +im_options : Bug#20294 2006-07-24 stewart Instance manager test im_instance_conf fails randomly #im_life_cycle : Bug#20368 2006-06-10 alik im_life_cycle test fails ndb_autodiscover : BUG#18952 2006-02-16 jmiller Needs to be fixed w.r.t binlog ndb_autodiscover2 : BUG#18952 2006-02-16 jmiller Needs to be fixed w.r.t binlog +ndb_binlog_ignore_db : BUG#21279 2006-07-25 ingo Randomly throws a warning ndb_load : BUG#17233 2006-05-04 tomas failed load data from infile causes mysqld dbug_assert, binlog not flushed +ndb_restore_compat : BUG#21283 2006-07-26 ingo Test fails randomly partition_03ndb : BUG#16385 2006-03-24 mikael Partitions: crash when updating a range partitioned NDB table ps_7ndb : BUG#18950 2006-02-16 jmiller create table like does not obtain LOCK_open rpl_ndb_2innodb : BUG#19227 2006-04-20 pekka pk delete apparently not replicated @@ -36,3 +38,7 @@ rpl_sp_effects : BUG#19862 2006-06-15 mkindahl # the below testcase have been reworked to avoid the bug, test contains comment, keep bug open #ndb_binlog_ddl_multi : BUG#18976 2006-04-10 kent CRBR: multiple binlog, second binlog may miss schema log events +rpl_ndb_idempotent : BUG#21298 2006-07-27 msvensson +rpl_row_basic_7ndb : BUG#21298 2006-07-27 msvensson +rpl_truncate_7ndb : BUG#21298 2006-07-27 msvensson + diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index 77ca38b9587..032e2536f0a 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -1377,6 +1377,7 @@ connection master; # for ON INSERT triggers only. Tests for other types of triggers reside # in ndb_trigger.test. # +connection slave; --disable_warnings drop table if exists federated.t1; --enable_warnings @@ -1459,5 +1460,26 @@ drop table federated.t1, federated.t2; connection slave; drop table federated.t1, federated.t2; +# +# Bug #16494: Updates that set a column to NULL fail sometimes +# +connection slave; +create table t1 (id int not null auto_increment primary key, val int); +connection master; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval create table t1 + (id int not null auto_increment primary key, val int) engine=federated + connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/test/t1'; +insert into t1 values (1,0),(2,0); +update t1 set val = NULL where id = 1; +select * from t1; +connection slave; +select * from t1; +drop table t1; +connection master; +drop table t1; + +--echo End of 5.0 tests source include/federated_cleanup.inc; + diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index e8c5fa18a25..760745dc14e 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -378,13 +378,15 @@ explain select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME'; drop table t1, t2; ---disable_warnings -create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB; ---enable_warnings -insert into t1 values (1, 3); -select count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ from t1 group by MAX_REQ; -select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ; -drop table t1; +# Moved to func_group_innodb +#--disable_warnings +#create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB; +#--enable_warnings +#insert into t1 values (1, 3); +#select count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ from t1 group by MAX_REQ; +#select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ; +#drop table t1; + create table t1 (a char(10)); insert into t1 values ('a'),('b'),('c'); @@ -539,6 +541,11 @@ INSERT INTO t1 VALUES SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6; DROP TABLE t1; + +# +# Bug #12882 min/max inconsistent on empty table +# +# Test case moved to func_group_innodb # # Bug #18206: min/max optimization cannot be applied to partial index # diff --git a/mysql-test/t/func_group_innodb.test b/mysql-test/t/func_group_innodb.test new file mode 100644 index 00000000000..1bdfd8f54bb --- /dev/null +++ b/mysql-test/t/func_group_innodb.test @@ -0,0 +1,85 @@ +# +# Test of group functions that depend on innodb +# + +--source include/have_innodb.inc + +--disable_warnings +create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB; +--enable_warnings +insert into t1 values (1, 3); +select count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ from t1 group by MAX_REQ; +select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ; +drop table t1; + + +# +# Bug #12882 min/max inconsistent on empty table +# + +--disable_warnings +create table t1m (a int) engine=myisam; +create table t1i (a int) engine=innodb; +create table t2m (a int) engine=myisam; +create table t2i (a int) engine=innodb; +--enable_warnings +insert into t2m values (5); +insert into t2i values (5); + +# test with MyISAM +select min(a) from t1m; +select min(7) from t1m; +select min(7) from DUAL; +explain select min(7) from t2m join t1m; +select min(7) from t2m join t1m; + +select max(a) from t1m; +select max(7) from t1m; +select max(7) from DUAL; +explain select max(7) from t2m join t1m; +select max(7) from t2m join t1m; + +select 1, min(a) from t1m where a=99; +select 1, min(a) from t1m where 1=99; +select 1, min(1) from t1m where a=99; +select 1, min(1) from t1m where 1=99; + +select 1, max(a) from t1m where a=99; +select 1, max(a) from t1m where 1=99; +select 1, max(1) from t1m where a=99; +select 1, max(1) from t1m where 1=99; + +# test with InnoDB +select min(a) from t1i; +select min(7) from t1i; +select min(7) from DUAL; +explain select min(7) from t2i join t1i; +select min(7) from t2i join t1i; + +select max(a) from t1i; +select max(7) from t1i; +select max(7) from DUAL; +explain select max(7) from t2i join t1i; +select max(7) from t2i join t1i; + +select 1, min(a) from t1i where a=99; +select 1, min(a) from t1i where 1=99; +select 1, min(1) from t1i where a=99; +select 1, min(1) from t1i where 1=99; + +select 1, max(a) from t1i where a=99; +select 1, max(a) from t1i where 1=99; +select 1, max(1) from t1i where a=99; +select 1, max(1) from t1i where 1=99; + +# mixed MyISAM/InnoDB test +explain select count(*), min(7), max(7) from t1m, t1i; +select count(*), min(7), max(7) from t1m, t1i; + +explain select count(*), min(7), max(7) from t1m, t2i; +select count(*), min(7), max(7) from t1m, t2i; + +explain select count(*), min(7), max(7) from t2m, t1i; +select count(*), min(7), max(7) from t2m, t1i; + +drop table t1m, t1i, t2m, t2i; diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index 874f3cd1a80..fec51a24920 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -659,7 +659,32 @@ select a1 from t1 where a2 = 'b' group by a1; explain select distinct a1 from t1 where a2 = 'b'; select distinct a1 from t1 where a2 = 'b'; +# +# Bug #12672: primary key implcitly included in every innodb index +# +# Test case moved to group_min_max_innodb + + +# +# Bug #6142: a problem with the empty innodb table +# +# Test case moved to group_min_max_innodb + + +# +# Bug #9798: group by with rollup +# +# Test case moved to group_min_max_innodb + + +# +# Bug #13293 Wrongly used index results in endless loop. +# +# Test case moved to group_min_max_innodb + + drop table t1,t2,t3; + # # Bug #14920 Ordering aggregated result sets with composite primary keys # corrupts resultset diff --git a/mysql-test/t/group_min_max_innodb.test b/mysql-test/t/group_min_max_innodb.test new file mode 100644 index 00000000000..ea2a603a8a4 --- /dev/null +++ b/mysql-test/t/group_min_max_innodb.test @@ -0,0 +1,95 @@ +# +# Test file for WL#1724 (Min/Max Optimization for Queries with Group By Clause). +# The queries in this file test query execution via QUICK_GROUP_MIN_MAX_SELECT +# that depends on InnoDB +# + +--source include/have_innodb.inc + +# +# Bug #12672: primary key implcitly included in every innodb index +# + +--disable_warnings +create table t4 ( + pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' +) engine=innodb; +--enable_warnings + +insert into t4 (a1, a2, b, c, d) values +('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), +('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), +('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), +('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), +('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), +('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), +('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), +('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), +('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), +('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), +('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), +('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), +('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), +('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), +('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), +('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), +('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), +('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), +('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), +('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), +('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), +('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), +('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), +('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), +('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), +('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), +('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), +('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), +('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), +('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), +('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), +('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); + +create index idx12672_0 on t4 (a1); +create index idx12672_1 on t4 (a1,a2,b,c); +create index idx12672_2 on t4 (a1,a2,b); +analyze table t4; + +select distinct a1 from t4 where pk_col not in (1,2,3,4); + +drop table t4; + + +# +# Bug #6142: a problem with the empty innodb table +# + +--disable_warnings +create table t1 ( + a varchar(30), b varchar(30), primary key(a), key(b) +) engine=innodb; +--enable_warnings +select distinct a from t1; +drop table t1; + +# +# Bug #9798: group by with rollup +# + +--disable_warnings +create table t1(a int, key(a)) engine=innodb; +--enable_warnings +insert into t1 values(1); +select a, count(a) from t1 group by a with rollup; +drop table t1; + + +# +# Bug #13293 Wrongly used index results in endless loop. +# +create table t1 (f1 int, f2 char(1), primary key(f1,f2)) engine=innodb; +insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d"); +alter table t1 drop primary key, add primary key (f2, f1); +explain select distinct f1 a, f1 b from t1; +explain select distinct f1, f2 from t1; +drop table t1; diff --git a/mysql-test/t/init_connect.test b/mysql-test/t/init_connect.test index 0ee6387d985..31a98df33df 100644 --- a/mysql-test/t/init_connect.test +++ b/mysql-test/t/init_connect.test @@ -35,4 +35,205 @@ select @a; connection con0; drop table t1; -# End of 4.1 tests +disconnect con1; +disconnect con2; +disconnect con3; +disconnect con4; +disconnect con5; + +--echo End of 4.1 tests +# +# Test 5.* features +# + +create table t1 (x int); +insert into t1 values (3), (5), (7); +create table t2 (y int); + +create user mysqltest1@localhost; +grant all privileges on test.* to mysqltest1@localhost; +# +# Create a simple procedure +# +set global init_connect="create procedure p1() select * from t1"; +connect (con1,localhost,mysqltest1,,); +connection con1; +call p1(); +drop procedure p1; + +connection con0; +disconnect con1; +# +# Create a multi-result set procedure +# +set global init_connect="create procedure p1(x int)\ +begin\ + select count(*) from t1;\ + select * from t1;\ + set @x = x; +end"; +connect (con1,localhost,mysqltest1,,); +connection con1; +call p1(42); +select @x; + +connection con0; +disconnect con1; +# +# Just call it - this will not generate any output +# +set global init_connect="call p1(4711)"; +connect (con1,localhost,mysqltest1,,); +connection con1; +select @x; + +connection con0; +disconnect con1; +# +# Drop the procedure +# +set global init_connect="drop procedure if exists p1"; +connect (con1,localhost,mysqltest1,,); +connection con1; +--error ER_SP_DOES_NOT_EXIST +call p1(); + +connection con0; +disconnect con1; +# +# Execution of a more complex procedure +# +delimiter |; +create procedure p1(out sum int) +begin + declare n int default 0; + declare c cursor for select * from t1; + declare exit handler for not found + begin + close c; + set sum = n; + end; + + open c; + loop + begin + declare x int; + + fetch c into x; + if x > 3 then + set n = n + x; + end if; + end; + end loop; +end| +delimiter ;| +# Call the procedure with a cursor +set global init_connect="call p1(@sum)"; +connect (con1,localhost,mysqltest1,,); +connection con1; +select @sum; + +connection con0; +disconnect con1; +drop procedure p1; +# +# Test Dynamic SQL +# +delimiter |; +create procedure p1(tbl char(10), v int) +begin + set @s = concat('insert into ', tbl, ' values (?)'); + set @v = v; + prepare stmt1 from @s; + execute stmt1 using @v; + deallocate prepare stmt1; +end| +delimiter ;| +# Call the procedure with prepared statements +set global init_connect="call p1('t1', 11)"; +connect (con1,localhost,mysqltest1,,); +connection con1; +select * from t1; + +connection con0; +disconnect con1; +drop procedure p1; +# +# Stored functions +# +delimiter |; +create function f1() returns int +begin + declare n int; + + select count(*) into n from t1; + return n; +end| +delimiter ;| +# Invoke a function +set global init_connect="set @x = f1()"; +connect (con1,localhost,mysqltest1,,); +connection con1; +select @x; + +connection con0; +disconnect con1; +# +# Create a view +# +set global init_connect="create view v1 as select f1()"; +connect (con1,localhost,mysqltest1,,); +connection con1; +select * from v1; + +connection con0; +disconnect con1; +# +# Drop the view +# +set global init_connect="drop view v1"; +connect (con1,localhost,mysqltest1,,); +connection con1; +--error ER_NO_SUCH_TABLE +select * from v1; + +connection con0; +disconnect con1; +drop function f1; + +# We can't test "create trigger", since this requires super privileges +# in 5.0, but with super privileges, init_connect is not executed. +# (However, this can be tested in 5.1) +# +#set global init_connect="create trigger trg1\ +# after insert on t2\ +# for each row\ +# insert into t1 values (new.y)"; +#connect (con1,localhost,mysqltest1,,); +#connection con1; +#insert into t2 values (2), (4); +#select * from t1; +# +#connection con0; +#disconnect con1; + +create trigger trg1 + after insert on t2 + for each row + insert into t1 values (new.y); + +# Invoke trigger +set global init_connect="insert into t2 values (13), (17), (19)"; +connect (con1,localhost,mysqltest1,,); +connection con1; +select * from t1; + +connection con0; +disconnect con1; + +drop trigger trg1; +set global init_connect=default; + +revoke all privileges, grant option from mysqltest1@localhost; +drop user mysqltest1@localhost; +drop table t1, t2; diff --git a/mysql-test/t/init_file.test b/mysql-test/t/init_file.test index 8b4b788777b..6b5e032fd99 100644 --- a/mysql-test/t/init_file.test +++ b/mysql-test/t/init_file.test @@ -6,5 +6,15 @@ # mysql-test/t/init_file-master.opt for the actual test # -# End of 4.1 tests -echo ok; +--echo ok +--echo end of 4.1 tests +# +# Chec 5.x features +# +# Expected: +# 3, 5, 7, 11, 13 +select * from t1; +# Expected: +# 30, 3, 11, 13 +select * from t2; +drop table t1, t2; diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index 65e2cc658f5..6796840f2d2 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -280,3 +280,11 @@ TRUNCATE table t2; INSERT INTO t2 select * from t1; SELECT * from t2; drop table t1,t2; + +# +# Bug#17530: Incorrect key truncation on table creation caused server crash. +# +create table t1(f1 varchar(800) binary not null, key(f1)) engine = innodb + character set utf8 collate utf8_general_ci; +insert into t1 values('aaa'); +drop table t1; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index dc4e240750c..3f82219fadb 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -761,24 +761,10 @@ DROP TABLE t1,t2; # # Test for bug #17164: ORed FALSE blocked conversion of outer join into join -# - -CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), - INDEX (name)) ENGINE=InnoDB; -CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11), - FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); -INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); - -EXPLAIN -SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id - WHERE t1.name LIKE 'A%'; +# -EXPLAIN -SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id - WHERE t1.name LIKE 'A%' OR FALSE; +# Test case moved to join_outer_innodb -DROP TABLE t1,t2; # # Bug 19396: LEFT OUTER JOIN over views in curly braces diff --git a/mysql-test/t/join_outer_innodb.test b/mysql-test/t/join_outer_innodb.test new file mode 100644 index 00000000000..40add7f488f --- /dev/null +++ b/mysql-test/t/join_outer_innodb.test @@ -0,0 +1,26 @@ +# +# test of left outer join for tests that depends on innodb +# + +--source include/have_innodb.inc + +# +# Test for bug #17164: ORed FALSE blocked conversion of outer join into join +# + +CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), + INDEX (name)) ENGINE=InnoDB; +CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11), + FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); +INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); + +EXPLAIN +SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id + WHERE t1.name LIKE 'A%'; + +EXPLAIN +SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id + WHERE t1.name LIKE 'A%' OR FALSE; + +DROP TABLE t1,t2; diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test index df88b13a225..97c83310b4d 100644 --- a/mysql-test/t/log_tables.test +++ b/mysql-test/t/log_tables.test @@ -304,6 +304,16 @@ set global general_log='ON'; set global slow_query_log='ON'; use test; +# +# Bug #20139 Infinite loop after "FLUSH" and "LOCK tabX, general_log" +# + +flush tables with read lock; +unlock tables; +use mysql; +lock tables general_log read local, help_category read local; +unlock tables; + # kill all connections disconnect con1; disconnect con2; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index c211ff2ac29..33282348325 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -384,16 +384,6 @@ drop table t1, t2, t3; # End of 4.1 tests # -# BUG#10952 - alter table ... lost data without errors and warnings -# -drop table if exists t1; -create table t1 (c char(20)) engine=MyISAM; -insert into t1 values ("Monty"),("WAX"),("Walrus"); ---error 1031 -alter table t1 engine=MERGE; -drop table t1; - -# # BUG#19648 - Merge table does not work with bit types # create table t1 (b bit(1)); diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 9936b8bfc44..69d7ee51d81 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -718,8 +718,6 @@ UPDATE t1 AS ta1,t1 AS ta2 SET ta1.b='aaaaaa',ta2.b='bbbbbb'; SELECT * FROM t1; DROP TABLE t1; -# End of 4.1 tests - # # Test varchar # @@ -817,6 +815,42 @@ alter table t1 enable keys; show keys from t1; drop table t1; +# +# Bug#8706 - temporary table with data directory option fails +# +connect (session1,localhost,root,,); +connect (session2,localhost,root,,); + +connection session1; +disable_query_log; +eval create temporary table t1 (a int) engine=myisam data directory="$MYSQLTEST_VARDIR/tmp" select 9 a; +enable_query_log; +disable_result_log; +show create table t1; +enable_result_log; + +connection session2; +disable_query_log; +eval create temporary table t1 (a int) engine=myisam data directory="$MYSQLTEST_VARDIR/tmp" select 99 a; +enable_query_log; +disable_result_log; +show create table t1; +enable_result_log; + +connection default; +create table t1 (a int) engine=myisam select 42 a; + +connection session1; +select * from t1; +disconnect session1; +connection session2; +select * from t1; +disconnect session2; +connection default; +select * from t1; +drop table t1; + +--echo End of 4.1 tests # # Bug#10056 - PACK_KEYS option take values greater than 1 while creating table @@ -828,6 +862,8 @@ create table t3 (c1 int) engine=myisam pack_keys=default; create table t4 (c1 int) engine=myisam pack_keys=2; drop table t1, t2, t3; +--echo End of 5.0 tests + # # Test of key_block_size # @@ -890,3 +926,5 @@ drop table t1; create table t1 (a int not null, key key_block_size=1024 (a)); --error 1064 create table t1 (a int not null, key `a` key_block_size=1024 (a)); + +--echo End of 5.1 tests diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 74c3bb2e1d5..4a40f10b171 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1168,12 +1168,11 @@ insert into t values(5, 51); create view v1 as select qty, price, qty*price as value from t; create view v2 as select qty from v1; --echo mysqldump { ---exec $MYSQL_DUMP --compact -F --tab . test ---exec cat v1.sql +--exec $MYSQL_DUMP --compact -F --tab $MYSQLTEST_VARDIR/tmp test +--exec cat $MYSQLTEST_VARDIR/tmp/v1.sql --echo } mysqldump { ---exec cat v2.sql +--exec cat $MYSQLTEST_VARDIR/tmp/v2.sql --echo } mysqldump ---rm v.sql t.sql t.txt drop view v1; drop view v2; drop table t; diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index 42065cfae81..86cfd66ae2b 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -411,8 +411,8 @@ echo - MySQL: The world''s - source database; echo - MySQL: The world''s --- most popular open --- source database; +-- most popular +-- open source database; echo # MySQL: The --world''s @@ -469,8 +469,8 @@ echo $message; let $message= -- MySQL: The -- world''s most --- popular open --- source database; +-- popular +-- open source database; echo $message; let $message= # MySQL: The diff --git a/mysql-test/t/ndb_alter_table.test b/mysql-test/t/ndb_alter_table.test index 73c612b203f..fde9f1479f8 100644 --- a/mysql-test/t/ndb_alter_table.test +++ b/mysql-test/t/ndb_alter_table.test @@ -389,6 +389,7 @@ drop index i1 on t1; --disable_warnings --exec $NDB_TOOLS_DIR/ndb_show_tables --p > $MYSQLTEST_VARDIR/master-data/test/tmp.dat LOAD DATA INFILE 'tmp.dat' INTO TABLE ndb_show_tables; +--exec rm $MYSQLTEST_VARDIR/master-data/test/tmp.dat || true --enable_warnings select 'no_copy' from ndb_show_tables where id = @t1_id and name like '%t1%'; diff --git a/mysql-test/t/ndb_autodiscover3.test b/mysql-test/t/ndb_autodiscover3.test index 5f6d457d140..6c321da8f36 100644 --- a/mysql-test/t/ndb_autodiscover3.test +++ b/mysql-test/t/ndb_autodiscover3.test @@ -17,7 +17,9 @@ insert into t1 values (1); --exec $NDB_MGM --no-defaults -e "all restart" >> $NDB_TOOLS_OUTPUT --exec $NDB_TOOLS_DIR/ndb_waiter --no-defaults >> $NDB_TOOLS_OUTPUT - +# Wait for mysqld to reconnect and exit from readonly mode +# Should preferrably be a "while (!"select ndb_readonly")" loop +sleep 2; --error 1297 insert into t1 values (2); --error 1296 @@ -37,7 +39,7 @@ select * from t2 order by a limit 3; --exec $NDB_TOOLS_DIR/ndb_waiter --no-defaults >> $NDB_TOOLS_OUTPUT # to ensure mysqld has connected again, and recreated system tables --exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -r 30 -d cluster apply_status >> $NDB_TOOLS_OUTPUT - +sleep 2; --connection server2 --error ER_NO_SUCH_TABLE select * from t2; @@ -56,7 +58,7 @@ reset master; --exec $NDB_TOOLS_DIR/ndb_waiter --no-defaults >> $NDB_TOOLS_OUTPUT # to ensure mysqld has connected again, and recreated system tables --exec $NDB_TOOLS_DIR/ndb_desc --no-defaults -r 30 -d cluster apply_status >> $NDB_TOOLS_OUTPUT - +sleep 2; --connection server1 --error ER_NO_SUCH_TABLE select * from t2; diff --git a/mysql-test/t/ndb_binlog_discover.test b/mysql-test/t/ndb_binlog_discover.test index e74bd3380bd..9c86922b82f 100644 --- a/mysql-test/t/ndb_binlog_discover.test +++ b/mysql-test/t/ndb_binlog_discover.test @@ -14,6 +14,20 @@ reset master; --exec $NDB_TOOLS_DIR/ndb_waiter --no-defaults --not-started > /dev/null --exec $NDB_MGM --no-defaults -e "all start" > /dev/null --exec $NDB_TOOLS_DIR/ndb_waiter --no-defaults > /dev/null -insert into t1 values(1); + +--disable_query_log +let $mysql_errno= 1; +while ($mysql_errno) +{ + # Table t1 is readonly until the mysqld has reconnected properly + --error 0,1036 + insert into t1 values(1); + if ($mysql_errno) + { + --sleep 0.1 + } +} +--enable_query_log + --source include/show_binlog_events.inc drop table t1; diff --git a/mysql-test/t/ndb_cache_multi.test b/mysql-test/t/ndb_cache_multi.test index beb8e4bc2ac..404393c211e 100644 --- a/mysql-test/t/ndb_cache_multi.test +++ b/mysql-test/t/ndb_cache_multi.test @@ -63,3 +63,10 @@ show status like "Qcache_hits"; drop table t1, t2; +# Turn off query cache on server1 +connection server1; +set GLOBAL query_cache_size=0; + +# Turn off query cache on server2 +connection server2; +set GLOBAL query_cache_size=0; diff --git a/mysql-test/t/ndb_partition_key.test b/mysql-test/t/ndb_partition_key.test index fb0581eb6f6..d8c1b61b94f 100644 --- a/mysql-test/t/ndb_partition_key.test +++ b/mysql-test/t/ndb_partition_key.test @@ -192,7 +192,10 @@ CREATE TABLE t1 ( c3 INT NOT NULL, PRIMARY KEY(c1,c3)) ENGINE=NDB - PARTITION BY KEY(c3); + PARTITION BY KEY(c3) + (PARTITION p0 NODEGROUP 0, PARTITION p1 NODEGROUP 0); ALTER TABLE t1 ADD COLUMN c4 INT AFTER c1; +SELECT NODEGROUP,PARTITION_NAME FROM information_schema.partitions WHERE +table_name = "t1"; DROP TABLE t1; diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 04519800ff6..d4e930f91ec 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -835,13 +835,6 @@ alter table t1 rebuild partition; drop table t1; # -# Bug #14673: Wrong InnoDB default row format -# -create table t1 (a int) engine=innodb partition by hash(a) ; -show table status like 't1'; -drop table t1; - -# # Bug #14526: Partitions: indexed searches fail # create table t2 (s1 int not null auto_increment, primary key (s1)) partition by list (s1) (partition p1 values in (1),partition p2 values in (2),partition p3 values in (3),partition p4 values in (4)); @@ -931,6 +924,7 @@ delimiter ;| CALL test.p1(12); CALL test.p1(13); drop table t1; +drop procedure test.p1; # # Bug 13520: Problem with delimiters in COMMENT DATA DIRECTORY .. @@ -1261,4 +1255,49 @@ insert into t1 values (1),(2); select * from t1 ORDER BY a DESC; drop table t1; +# +# Bug 20770 Partitions: DATA DIRECTORY clause change in reorganize +# doesn't remove old directory +# +--disable_query_log +--exec mkdir $MYSQLTEST_VARDIR/master-data/tmpdata || true +eval SET @data_dir = 'DATA DIRECTORY = ''$MYSQLTEST_VARDIR/master-data/tmpdata'''; +let $data_directory = `select @data_dir`; + +--exec mkdir $MYSQLTEST_VARDIR/master-data/tmpinx || true +eval SET @inx_dir = 'INDEX DIRECTORY = ''$MYSQLTEST_VARDIR/master-data/tmpinx'''; +let $inx_directory = `select @inx_dir`; +--enable_query_log + +--replace_result $MYSQLTEST_VARDIR "hello" +eval create table t1 (a int) engine myisam +partition by range (a) +subpartition by hash (a) +(partition p0 VALUES LESS THAN (1) $data_directory $inx_directory + (SUBPARTITION subpart00, SUBPARTITION subpart01)); + +--replace_result $MYSQLTEST_VARDIR "hello" +--exec ls $MYSQLTEST_VARDIR/master-data/test/t1* || true +--replace_result $MYSQLTEST_VARDIR "hello" +--exec ls $MYSQLTEST_VARDIR/master-data/tmpdata/t1* || true +--replace_result $MYSQLTEST_VARDIR "hello" +--exec ls $MYSQLTEST_VARDIR/master-data/tmpinx/t1* || true +--replace_result $MYSQLTEST_VARDIR "hello" + +eval ALTER TABLE t1 REORGANIZE PARTITION p0 INTO +(partition p1 VALUES LESS THAN (1) $data_directory $inx_directory + (SUBPARTITION subpart10, SUBPARTITION subpart11), + partition p2 VALUES LESS THAN (2) $data_directory $inx_directory + (SUBPARTITION subpart20, SUBPARTITION subpart21)); + +--replace_result $MYSQLTEST_VARDIR "hello" +--exec ls $MYSQLTEST_VARDIR/master-data/test/t1* || true +--replace_result $MYSQLTEST_VARDIR "hello" +--exec ls $MYSQLTEST_VARDIR/master-data/tmpdata/t1* || true +--replace_result $MYSQLTEST_VARDIR "hello" +--exec ls $MYSQLTEST_VARDIR/master-data/tmpinx/t1* || true + +drop table t1; +--exec rmdir $MYSQLTEST_VARDIR/master-data/tmpdata || true +--exec rmdir $MYSQLTEST_VARDIR/master-data/tmpinx || true --echo End of 5.1 tests diff --git a/mysql-test/t/partition_hash.test b/mysql-test/t/partition_hash.test index 8494de98371..3304f30fb1a 100644 --- a/mysql-test/t/partition_hash.test +++ b/mysql-test/t/partition_hash.test @@ -10,6 +10,36 @@ drop table if exists t1; --enable_warnings # +# More partition pruning tests, especially on interval walking +# +create table t1 (a int unsigned) +partition by hash(a div 2) +partitions 4; +insert into t1 values (null),(0),(1),(2),(3),(4),(5),(6),(7); +select * from t1 where a < 0; +select * from t1 where a is null or (a >= 5 and a <= 7); +select * from t1 where a is null; +select * from t1 where a is not null; +select * from t1 where a >= 1 and a < 3; +select * from t1 where a >= 3 and a <= 5; +select * from t1 where a > 2 and a < 4; +select * from t1 where a > 3 and a <= 6; +select * from t1 where a > 5; +select * from t1 where a >= 1 and a <= 5; +explain partitions select * from t1 where a < 0; +explain partitions select * from t1 where a is null or (a >= 5 and a <= 7); +explain partitions select * from t1 where a is null; +explain partitions select * from t1 where a is not null; +explain partitions select * from t1 where a >= 1 and a < 3; +explain partitions select * from t1 where a >= 3 and a <= 5; +explain partitions select * from t1 where a > 2 and a < 4; +explain partitions select * from t1 where a > 3 and a <= 6; +explain partitions select * from t1 where a > 5; +explain partitions select * from t1 where a >= 1 and a <= 5; + +drop table t1; + +# # Partition by hash, basic # CREATE TABLE t1 ( diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index 51f5b0fec01..a110fb30a3b 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -1,80 +1,10 @@ --- source include/have_innodb.inc --- source include/have_partition.inc -SET @max_row = 20; -let $engine= 'InnoDB'; -let $MAX_VALUE= (2147483646); - -let $max_row= `SELECT @max_row`; - -# Column list with definition for all tables to be checked -let $column_list= f_int1 INTEGER, -f_int2 INTEGER, -f_char1 CHAR(20), -f_char2 CHAR(20), -f_charbig VARCHAR(1000); - -let $sub_part_no= 3; ---disable_warnings -DROP TABLE IF EXISTS t0_template; ---enable_warnings -eval CREATE TABLE t0_template ( -$column_list , -PRIMARY KEY(f_int1)) -ENGINE = MEMORY; -let $num= `SELECT @max_row`; -while ($num) -{ - eval INSERT INTO t0_template -SET f_int1 = $num, f_int2 = $num, f_char1 = '$num', f_char2 = '$num', -f_charbig = '===$num==='; - dec $num; -} -# 1. Create the table ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings -eval CREATE TABLE t1 (f_date DATE, f_varchar VARCHAR(30)) engine=$engine; -# 2. Fill the table t1 with records -INSERT INTO t1 (f_date, f_varchar) -SELECT CONCAT(CAST((f_int1 + 999) AS CHAR),'-02-10'), CAST(f_char1 AS CHAR) -FROM t0_template -WHERE f_int1 + 999 BETWEEN 1000 AND 9999; -# 3. Calculate the number of inserted records. -SELECT IF(9999 - 1000 + 1 > @max_row, @max_row , 9999 - 1000 + 1) - INTO @exp_row_count; -# DEBUG SELECT @exp_row_count; -# 4. Print the layout, check Readability -ALTER TABLE t1 PARTITION BY HASH(CAST(YEAR(f_date) AS SIGNED INTEGER)); ---echo # 1.1.5 Add two named partitions + test -ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7); -drop table t1; - -CREATE TABLE t1 (f_date DATE, f_varchar VARCHAR(30)) -ENGINE=InnoDB -PARTITION BY HASH(CAST(YEAR(f_date) AS SIGNED INTEGER)); - ---echo # This statement crashes the server. ---echo # CREATE partitioned table with three partitions in one step ---echo # would be harmless. -ALTER TABLE t1 ADD PARTITION PARTITIONS 1; - ---disable_warnings -DROP VIEW IF EXISTS v1; -DROP TABLE IF EXISTS t1; -DROP TABLE IF EXISTS t0_aux; -DROP TABLE IF EXISTS t0_definition; -DROP TABLE IF EXISTS t0_template; ---enable_warnings +--source include/have_partition.inc +--source include/have_innodb.inc # -# Bug#20086: Can't get data from key partitioned tables with VARCHAR key +# Bug #14673: Wrong InnoDB default row format # -create table t1 (id varchar(64) primary key) engine=innodb -partition by key(id) partitions 5; -insert into t1 values ('a'); -insert into t1 values ('aa'); -insert into t1 values ('aaa'); -select * from t1 where id = 'a'; -select * from t1 where id = 'aa'; -select * from t1 where id = 'aaa'; +create table t1 (a int) engine=innodb partition by hash(a) ; +show table status like 't1'; drop table t1; + diff --git a/mysql-test/t/partition_list.test b/mysql-test/t/partition_list.test index e243ec468e1..1e420cfe6ed 100644 --- a/mysql-test/t/partition_list.test +++ b/mysql-test/t/partition_list.test @@ -10,6 +10,49 @@ drop table if exists t1; --enable_warnings # +# Bug 20733: Zerofill columns gives wrong result with partitioned tables +# +create table t1 (a int unsigned) +partition by list (a) +(partition p0 values in (0), + partition p1 values in (1), + partition pnull values in (null), + partition p2 values in (2)); + +insert into t1 values (null),(0),(1),(2); +select * from t1 where a < 2; +select * from t1 where a <= 0; +select * from t1 where a < 1; +select * from t1 where a > 0; +select * from t1 where a > 1; +select * from t1 where a >= 0; +select * from t1 where a >= 1; +select * from t1 where a is null; +select * from t1 where a is not null; +select * from t1 where a is null or a > 0; +drop table t1; + +create table t1 (a int unsigned, b int) +partition by list (a) +subpartition by hash (b) +subpartitions 2 +(partition p0 values in (0), + partition p1 values in (1), + partition pnull values in (null, 2), + partition p3 values in (3)); +insert into t1 values (0,0),(0,1),(1,0),(1,1),(null,0),(null,1); +insert into t1 values (2,0),(2,1),(3,0),(3,1); + +explain partitions select * from t1 where a is null; +select * from t1 where a is null; +explain partitions select * from t1 where a = 2; +select * from t1 where a = 2; +select * from t1 where a <= 0; +select * from t1 where a < 3; +select * from t1 where a >= 1 or a is null; +drop table t1; + +# # Test ordinary list partitioning that it works ok # CREATE TABLE t1 ( @@ -136,3 +179,4 @@ insert into t1 values (null); select * from t1; drop table t1; + diff --git a/mysql-test/t/partition_mgm.test b/mysql-test/t/partition_mgm.test index cfb76192de4..39512de154f 100644 --- a/mysql-test/t/partition_mgm.test +++ b/mysql-test/t/partition_mgm.test @@ -12,7 +12,21 @@ ALTER TABLE t1 COALESCE PARTITION 1; SHOW CREATE TABLE t1; --replace_result $MYSQLTEST_VARDIR "hello" --exec ls $MYSQLTEST_VARDIR/master-data/test/t1* +drop table t1; +# +# Bug 20767: REORGANIZE partition crashes +# +create table t1 (a int) +partition by list (a) +subpartition by hash (a) +(partition p11 values in (1,2), + partition p12 values in (3,4)); +alter table t1 REORGANIZE partition p11, p12 INTO +(partition p1 values in (1,2,3,4)); +alter table t1 REORGANIZE partition p1 INTO +(partition p11 values in (1,2), + partition p12 values in (3,4)); - +drop table t1; diff --git a/mysql-test/t/partition_order.test b/mysql-test/t/partition_order.test index 1e1b3339d64..ad956361d00 100644 --- a/mysql-test/t/partition_order.test +++ b/mysql-test/t/partition_order.test @@ -818,11 +818,27 @@ partitions 2 # Insert a couple of tuples INSERT into t1 values (1, 1); INSERT into t1 values (5, NULL); -INSERT into t1 values (2, 5); +INSERT into t1 values (2, 4); +INSERT into t1 values (3, 3); +INSERT into t1 values (4, 5); +INSERT into t1 values (7, 1); +INSERT into t1 values (6, 6); INSERT into t1 values (30, 4); INSERT into t1 values (35, 2); INSERT into t1 values (40, NULL); select * from t1 force index (b) where b < 10 OR b IS NULL order by b; +select * from t1 force index (b) where b < 10 ORDER BY b; +select * from t1 force index (b) where b < 10 ORDER BY b DESC; +drop table t1; +create table t1 (a int not null, b int, c varchar(20), key (a,b,c)) +partition by range (b) +(partition p0 values less than (5), + partition p1 values less than (10)); +INSERT into t1 values (1,1,'1'),(2,2,'2'),(1,3,'3'),(2,4,'4'),(1,5,'5'); +INSERT into t1 values (2,6,'6'),(1,7,'7'),(2,8,'8'),(1,9,'9'); +INSERT into t1 values (1, NULL, NULL), (2, NULL, '10'); +select * from t1 where a = 1 order by a desc, b desc; +select * from t1 where a = 1 order by b desc; drop table t1; diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test index 976466e1578..dd79e8d3acb 100644 --- a/mysql-test/t/partition_pruning.test +++ b/mysql-test/t/partition_pruning.test @@ -137,6 +137,32 @@ explain partitions select * from t6 where a >= 3 and a <= 8; explain partitions select * from t6 where a > 3 and a < 5; +drop table t6; + +create table t6 (a int unsigned not null) partition by LIST(a) ( + partition p1 values in (1), + partition p3 values in (3), + partition p5 values in (5), + partition p7 values in (7), + partition p9 values in (9) +); +insert into t6 values (1),(3),(5); + +explain partitions select * from t6 where a < 1; +explain partitions select * from t6 where a <= 1; +explain partitions select * from t6 where a > 9; +explain partitions select * from t6 where a >= 9; + +explain partitions select * from t6 where a > 0 and a < 5; +explain partitions select * from t6 where a > 5 and a < 12; +explain partitions select * from t6 where a > 3 and a < 8 ; + +explain partitions select * from t6 where a >= 0 and a <= 5; +explain partitions select * from t6 where a >= 5 and a <= 12; +explain partitions select * from t6 where a >= 3 and a <= 8; + +explain partitions select * from t6 where a > 3 and a < 5; + # RANGE(field) partitioning, interval analysis. create table t7 (a int not null) partition by RANGE(a) ( partition p10 values less than (10), @@ -162,6 +188,32 @@ explain partitions select * from t7 where a >= 90; # misc intervals explain partitions select * from t7 where a > 11 and a < 29; +drop table t7; + +create table t7 (a int unsigned not null) partition by RANGE(a) ( + partition p10 values less than (10), + partition p30 values less than (30), + partition p50 values less than (50), + partition p70 values less than (70), + partition p90 values less than (90) +); +insert into t7 values (10),(30),(50); + +# leftmost intervals +explain partitions select * from t7 where a < 5; +explain partitions select * from t7 where a < 10; +explain partitions select * from t7 where a <= 10; +explain partitions select * from t7 where a = 10; + +#rightmost intervals +explain partitions select * from t7 where a < 90; +explain partitions select * from t7 where a = 90; +explain partitions select * from t7 where a > 90; +explain partitions select * from t7 where a >= 90; + +# misc intervals +explain partitions select * from t7 where a > 11 and a < 29; + # LIST(monontonic_func) partitioning create table t8 (a date not null) partition by RANGE(YEAR(a)) ( partition p0 values less than (1980), diff --git a/mysql-test/t/partition_range.test b/mysql-test/t/partition_range.test index 8e1e2e72e69..670b9333ab9 100644 --- a/mysql-test/t/partition_range.test +++ b/mysql-test/t/partition_range.test @@ -10,6 +10,50 @@ drop table if exists t1; --enable_warnings # +# More checks for partition pruning +# +create table t1 (a int unsigned) +partition by range (a) +(partition pnull values less than (0), + partition p0 values less than (1), + partition p1 values less than(2)); +insert into t1 values (null),(0),(1); + +select * from t1 where a is null; +select * from t1 where a >= 0; +select * from t1 where a < 0; +select * from t1 where a <= 0; +select * from t1 where a > 1; +explain partitions select * from t1 where a is null; +explain partitions select * from t1 where a >= 0; +explain partitions select * from t1 where a < 0; +explain partitions select * from t1 where a <= 0; +explain partitions select * from t1 where a > 1; +drop table t1; + +create table t1 (a int unsigned, b int unsigned) +partition by range (a) +subpartition by hash (b) +subpartitions 2 +(partition pnull values less than (0), + partition p0 values less than (1), + partition p1 values less than(2)); +insert into t1 values (null,0),(null,1),(0,0),(0,1),(1,0),(1,1); + +select * from t1 where a is null; +select * from t1 where a >= 0; +select * from t1 where a < 0; +select * from t1 where a <= 0; +select * from t1 where a > 1; +explain partitions select * from t1 where a is null; +explain partitions select * from t1 where a >= 0; +explain partitions select * from t1 where a < 0; +explain partitions select * from t1 where a <= 0; +explain partitions select * from t1 where a > 1; + +drop table t1; + +# # Partition by range, basic # CREATE TABLE t1 ( @@ -555,3 +599,90 @@ reorganize partition p5 into drop table t1; +# +# New test cases for date based partitioning +# +CREATE TABLE t1 (a date) +PARTITION BY RANGE (TO_DAYS(a)) +(PARTITION p3xx VALUES LESS THAN (TO_DAYS('2004-01-01')), + PARTITION p401 VALUES LESS THAN (TO_DAYS('2004-02-01')), + PARTITION p402 VALUES LESS THAN (TO_DAYS('2004-03-01')), + PARTITION p403 VALUES LESS THAN (TO_DAYS('2004-04-01')), + PARTITION p404 VALUES LESS THAN (TO_DAYS('2004-05-01')), + PARTITION p405 VALUES LESS THAN (TO_DAYS('2004-06-01')), + PARTITION p406 VALUES LESS THAN (TO_DAYS('2004-07-01')), + PARTITION p407 VALUES LESS THAN (TO_DAYS('2004-08-01')), + PARTITION p408 VALUES LESS THAN (TO_DAYS('2004-09-01')), + PARTITION p409 VALUES LESS THAN (TO_DAYS('2004-10-01')), + PARTITION p410 VALUES LESS THAN (TO_DAYS('2004-11-01')), + PARTITION p411 VALUES LESS THAN (TO_DAYS('2004-12-01')), + PARTITION p412 VALUES LESS THAN (TO_DAYS('2005-01-01')), + PARTITION p501 VALUES LESS THAN (TO_DAYS('2005-02-01')), + PARTITION p502 VALUES LESS THAN (TO_DAYS('2005-03-01')), + PARTITION p503 VALUES LESS THAN (TO_DAYS('2005-04-01')), + PARTITION p504 VALUES LESS THAN (TO_DAYS('2005-05-01')), + PARTITION p505 VALUES LESS THAN (TO_DAYS('2005-06-01')), + PARTITION p506 VALUES LESS THAN (TO_DAYS('2005-07-01')), + PARTITION p507 VALUES LESS THAN (TO_DAYS('2005-08-01')), + PARTITION p508 VALUES LESS THAN (TO_DAYS('2005-09-01')), + PARTITION p509 VALUES LESS THAN (TO_DAYS('2005-10-01')), + PARTITION p510 VALUES LESS THAN (TO_DAYS('2005-11-01')), + PARTITION p511 VALUES LESS THAN (TO_DAYS('2005-12-01')), + PARTITION p512 VALUES LESS THAN (TO_DAYS('2006-01-01')), + PARTITION p601 VALUES LESS THAN (TO_DAYS('2006-02-01')), + PARTITION p602 VALUES LESS THAN (TO_DAYS('2006-03-01')), + PARTITION p603 VALUES LESS THAN (TO_DAYS('2006-04-01')), + PARTITION p604 VALUES LESS THAN (TO_DAYS('2006-05-01')), + PARTITION p605 VALUES LESS THAN (TO_DAYS('2006-06-01')), + PARTITION p606 VALUES LESS THAN (TO_DAYS('2006-07-01')), + PARTITION p607 VALUES LESS THAN (TO_DAYS('2006-08-01'))); + +INSERT INTO t1 VALUES ('2003-01-13'),('2003-06-20'),('2003-08-30'); +INSERT INTO t1 VALUES ('2003-04-13'),('2003-07-20'),('2003-10-30'); +INSERT INTO t1 VALUES ('2003-05-13'),('2003-11-20'),('2003-12-30'); + +INSERT INTO t1 VALUES ('2004-01-13'),('2004-01-20'),('2004-01-30'); +INSERT INTO t1 VALUES ('2004-02-13'),('2004-02-20'),('2004-02-28'); +INSERT INTO t1 VALUES ('2004-03-13'),('2004-03-20'),('2004-03-30'); +INSERT INTO t1 VALUES ('2004-04-13'),('2004-04-20'),('2004-04-30'); +INSERT INTO t1 VALUES ('2004-05-13'),('2004-05-20'),('2004-05-30'); +INSERT INTO t1 VALUES ('2004-06-13'),('2004-06-20'),('2004-06-30'); +INSERT INTO t1 VALUES ('2004-07-13'),('2004-07-20'),('2004-07-30'); +INSERT INTO t1 VALUES ('2004-08-13'),('2004-08-20'),('2004-08-30'); +INSERT INTO t1 VALUES ('2004-09-13'),('2004-09-20'),('2004-09-30'); +INSERT INTO t1 VALUES ('2004-10-13'),('2004-10-20'),('2004-10-30'); +INSERT INTO t1 VALUES ('2004-11-13'),('2004-11-20'),('2004-11-30'); +INSERT INTO t1 VALUES ('2004-12-13'),('2004-12-20'),('2004-12-30'); + +INSERT INTO t1 VALUES ('2005-01-13'),('2005-01-20'),('2005-01-30'); +INSERT INTO t1 VALUES ('2005-02-13'),('2005-02-20'),('2005-02-28'); +INSERT INTO t1 VALUES ('2005-03-13'),('2005-03-20'),('2005-03-30'); +INSERT INTO t1 VALUES ('2005-04-13'),('2005-04-20'),('2005-04-30'); +INSERT INTO t1 VALUES ('2005-05-13'),('2005-05-20'),('2005-05-30'); +INSERT INTO t1 VALUES ('2005-06-13'),('2005-06-20'),('2005-06-30'); +INSERT INTO t1 VALUES ('2005-07-13'),('2005-07-20'),('2005-07-30'); +INSERT INTO t1 VALUES ('2005-08-13'),('2005-08-20'),('2005-08-30'); +INSERT INTO t1 VALUES ('2005-09-13'),('2005-09-20'),('2005-09-30'); +INSERT INTO t1 VALUES ('2005-10-13'),('2005-10-20'),('2005-10-30'); +INSERT INTO t1 VALUES ('2005-11-13'),('2005-11-20'),('2005-11-30'); +INSERT INTO t1 VALUES ('2005-12-13'),('2005-12-20'),('2005-12-30'); + +INSERT INTO t1 VALUES ('2006-01-13'),('2006-01-20'),('2006-01-30'); +INSERT INTO t1 VALUES ('2006-02-13'),('2006-02-20'),('2006-02-28'); +INSERT INTO t1 VALUES ('2006-03-13'),('2006-03-20'),('2006-03-30'); +INSERT INTO t1 VALUES ('2006-04-13'),('2006-04-20'),('2006-04-30'); +INSERT INTO t1 VALUES ('2006-05-13'),('2006-05-20'),('2006-05-30'); +INSERT INTO t1 VALUES ('2006-06-13'),('2006-06-20'),('2006-06-30'); +INSERT INTO t1 VALUES ('2006-07-13'),('2006-07-20'),('2006-07-30'); + +SELECT * FROM t1 +WHERE a >= '2004-07-01' AND a <= '2004-09-30'; +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE a >= '2004-07-01' AND a <= '2004-09-30'; +SELECT * from t1 +WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR + (a >= '2005-07-01' AND a <= '2005-09-30'); +EXPLAIN PARTITIONS SELECT * from t1 +WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR + (a >= '2005-07-01' AND a <= '2005-09-30'); +DROP TABLE t1; diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test index 76da296c0b0..e9b8a1c95b1 100644 --- a/mysql-test/t/ps_1general.test +++ b/mysql-test/t/ps_1general.test @@ -324,10 +324,8 @@ execute stmt4; # history (actions of the bdb engine). # That is the reason why, we switch the output here off. # (The real output will be tested in ps_6bdb.test) ---disable_warnings -prepare stmt4 from ' show engine bdb logs '; ---enable_warnings --disable_result_log +prepare stmt4 from ' show engine bdb logs '; execute stmt4; --enable_result_log prepare stmt4 from ' show grants for user '; diff --git a/mysql-test/t/rpl_drop_db.test b/mysql-test/t/rpl_drop_db.test index 38ad07f72f1..ffdc605b402 100644 --- a/mysql-test/t/rpl_drop_db.test +++ b/mysql-test/t/rpl_drop_db.test @@ -56,3 +56,7 @@ connection slave; stop slave; #system rm -rf $MYSQLTEST_VARDIR/master-data/mysqltest1; +connection master; +use test; +drop table t1; + diff --git a/mysql-test/t/rpl_insert.test b/mysql-test/t/rpl_insert.test index 9225606c94b..9beaff6bab6 100644 --- a/mysql-test/t/rpl_insert.test +++ b/mysql-test/t/rpl_insert.test @@ -17,7 +17,19 @@ CREATE TABLE t1 (id INT, name VARCHAR(64)); let $query = "INSERT DELAYED INTO t1 VALUES (1, 'Dr. No'), (2, 'From Russia With Love'), (3, 'Goldfinger'), (4, 'Thunderball'), (5, 'You Only Live Twice')"; --exec $MYSQL_SLAP --silent --concurrency=5 --iterations=200 --query=$query --delimiter=";" ---sleep 10 +# Wait until all the 5000 inserts has been inserted into the table +--disable_query_log +let $counter= 300; # Max 30 seconds wait +while (`select count(*)!=5000 from mysqlslap.t1`) +{ + sleep 0.1; + dec $counter; + if (!$counter) + { + Number of records in t1 didnt reach 5000; + } +} +--enable_query_log SELECT COUNT(*) FROM mysqlslap.t1; sync_slave_with_master; diff --git a/mysql-test/t/rpl_ndb_bank.test b/mysql-test/t/rpl_ndb_bank.test index 3601c841c54..d6a10e4ccac 100644 --- a/mysql-test/t/rpl_ndb_bank.test +++ b/mysql-test/t/rpl_ndb_bank.test @@ -121,6 +121,7 @@ RESET MASTER; CREATE TABLE IF NOT EXISTS cluster.backup_info (id INT, backup_id INT) ENGINE = HEAP; DELETE FROM cluster.backup_info; LOAD DATA INFILE '../tmp.dat' INTO TABLE cluster.backup_info FIELDS TERMINATED BY ','; +--exec rm $MYSQLTEST_VARDIR/tmp.dat || true --replace_column 1 <the_backup_id> SELECT @the_backup_id:=backup_id FROM cluster.backup_info; let the_backup_id=`select @the_backup_id`; diff --git a/mysql-test/t/rpl_ndb_dd_advance.test b/mysql-test/t/rpl_ndb_dd_advance.test index 30d5deb47ad..1fe36ecd8a1 100644 --- a/mysql-test/t/rpl_ndb_dd_advance.test +++ b/mysql-test/t/rpl_ndb_dd_advance.test @@ -441,7 +441,7 @@ CREATE TEMPORARY TABLE IF NOT EXISTS cluster.backup_info (id INT, backup_id INT) DELETE FROM cluster.backup_info; LOAD DATA INFILE '../tmp.dat' INTO TABLE cluster.backup_info FIELDS TERMINATED BY ','; - +--exec rm $MYSQLTEST_VARDIR/tmp.dat || true --replace_column 1 <the_backup_id> SELECT @the_backup_id:=backup_id FROM cluster.backup_info; diff --git a/mysql-test/t/rpl_stm_no_op.test b/mysql-test/t/rpl_stm_no_op.test index d1e0b49abe9..f82bbd8cd55 100644 --- a/mysql-test/t/rpl_stm_no_op.test +++ b/mysql-test/t/rpl_stm_no_op.test @@ -89,5 +89,5 @@ select * from t2; # cleanup connection master; -drop table t1; +drop table t1, t2; sync_slave_with_master; diff --git a/mysql-test/t/rpl_variables.test b/mysql-test/t/rpl_variables.test index 57ae2b9c3c4..b1744c57c9b 100644 --- a/mysql-test/t/rpl_variables.test +++ b/mysql-test/t/rpl_variables.test @@ -12,3 +12,6 @@ show variables like 'slave_load_tmpdir'; # We just set some arbitrary values in variables-master.opt so we can test # that a list of values works correctly show variables like 'slave_skip_errors'; + +# Cleanup +set global slave_net_timeout=default; diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 24b0d4674c5..d370cb3037c 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1075,6 +1075,10 @@ execute stmt; drop function bug11834_1; # Attempt to execute statement should return proper error and # should not crash server. + +# NOTE! The error we get from the below query indicates that the sp bug11834_2 +# does not exist(this is wrong but can be accepted) +# This behaviour has been reported as bug#21294 --error ER_SP_DOES_NOT_EXIST execute stmt; deallocate prepare stmt; diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index d323b180216..591e9a3ed70 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -527,29 +527,6 @@ drop database db_bug14533; # -# BUG#7787: Stored procedures: improper warning for "grant execute" statement -# - -# Prepare. - -CREATE DATABASE db_bug7787; -use db_bug7787; - -# Test. - -CREATE PROCEDURE p1() - SHOW INNODB STATUS; - -GRANT EXECUTE ON PROCEDURE p1 TO user_bug7787@localhost; - -# Cleanup. - -DROP DATABASE db_bug7787; -drop user user_bug7787@localhost; -use test; - - -# # WL#2897: Complete definer support in the stored routines. # # The following cases are tested: diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 33259b999fc..99f3bbbbd14 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -5636,23 +5636,6 @@ drop table t3| drop procedure bug16887| # -# Bug#13575 SP funcs in select with distinct/group and order by can -# produce bad data -# -create table t3 (f1 int, f2 varchar(3), primary key(f1)) engine=innodb| -insert into t3 values (1,'aaa'),(2,'bbb'),(3,'ccc')| -CREATE FUNCTION bug13575 ( p1 integer ) -returns varchar(3) -BEGIN -DECLARE v1 VARCHAR(10) DEFAULT null; -SELECT f2 INTO v1 FROM t3 WHERE f1 = p1; -RETURN v1; -END| -select distinct f1, bug13575(f1) from t3 order by f1| -drop function bug13575; -drop table t3| - -# # BUG#16474: SP crashed MySQL # (when using "order by localvar", where 'localvar' is just that. # diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test index 0adbeb2d98b..28abf448089 100644 --- a/mysql-test/t/sp_notembedded.test +++ b/mysql-test/t/sp_notembedded.test @@ -46,6 +46,8 @@ call bug4902_2()| drop procedure bug4902_2| +# Disable until bug#17244 is fixed +--disable_parsing # # BUG#5278: Stored procedure packets out of order if SET PASSWORD. # @@ -63,7 +65,7 @@ select bug5278()| --error 1133 select bug5278()| drop function bug5278| - +--enable_parsing --disable_warnings drop table if exists t1| @@ -265,3 +267,23 @@ drop view v1| drop table t3| delimiter ;| + +# +# Bug#15298 SHOW GRANTS FOR CURRENT_USER: Incorrect output in DEFINER context +# +--disable_warnings +drop procedure if exists bug15298_1; +drop procedure if exists bug15298_2; +--enable_warnings +grant all privileges on test.* to 'mysqltest_1'@'localhost'; +create procedure 15298_1 () sql security definer show grants for current_user; +create procedure 15298_2 () sql security definer show grants; + +connect (con1,localhost,mysqltest_1,,test); +call 15298_1(); +call 15298_2(); + +connection default; +drop user mysqltest_1@localhost; +drop procedure 15298_1; +drop procedure 15298_2; diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test index 1ea32316f1e..325e11b1ec9 100644 --- a/mysql-test/t/sp_trans.test +++ b/mysql-test/t/sp_trans.test @@ -552,6 +552,45 @@ drop table t3, t4| drop procedure bug14210| set @@session.max_heap_table_size=default| +# +# BUG#7787: Stored procedures: improper warning for "grant execute" statement +# + +# Prepare. + +CREATE DATABASE db_bug7787| +use db_bug7787| + +# Test. + +CREATE PROCEDURE p1() + SHOW INNODB STATUS; | + +GRANT EXECUTE ON PROCEDURE p1 TO user_bug7787@localhost| + +# Cleanup. + +DROP DATABASE db_bug7787| +drop user user_bug7787@localhost| +use test| + +# +# Bug#13575 SP funcs in select with distinct/group and order by can +# produce bad data +# +create table t3 (f1 int, f2 varchar(3), primary key(f1)) engine=innodb| +insert into t3 values (1,'aaa'),(2,'bbb'),(3,'ccc')| +CREATE FUNCTION bug13575 ( p1 integer ) +returns varchar(3) +BEGIN +DECLARE v1 VARCHAR(10) DEFAULT null; +SELECT f2 INTO v1 FROM t3 WHERE f1 = p1; +RETURN v1; +END| +select distinct f1, bug13575(f1) from t3 order by f1| +drop function bug13575| +drop table t3| + # # BUG#NNNN: New bug synopsis diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test index 212150e057c..f6a8824c841 100644 --- a/mysql-test/t/strict.test +++ b/mysql-test/t/strict.test @@ -1155,3 +1155,42 @@ create table t2 select date from t1; show create table t2; drop table t2,t1; set @@sql_mode= @org_mode; + +# +# Bug #13934 Silent truncation of table comments +# +set @@sql_mode='traditional'; +--error 1105 +create table t1 (i int) +comment '123456789*123456789*123456789*123456789*123456789* + 123456789*123456789*123456789*123456789*123456789*'; +--error 1105 +create table t1 ( +i int comment +'123456789*123456789*123456789*123456789* + 123456789*123456789*123456789*123456789* + 123456789*123456789*123456789*123456789* + 123456789*123456789*123456789*123456789* + 123456789*123456789*123456789*123456789* + 123456789*123456789*123456789*123456789* + 123456789*123456789*123456789*123456789*'); +set @@sql_mode= @org_mode; +create table t1 +(i int comment + '123456789*123456789*123456789*123456789* + 123456789*123456789*123456789*123456789* + 123456789*123456789*123456789*123456789* + 123456789*123456789*123456789*123456789* + 123456789*123456789*123456789*123456789* + 123456789*123456789*123456789*123456789* + 123456789*123456789*123456789*123456789*'); + +select column_name, column_comment from information_schema.columns where +table_schema = 'test' and table_name = 't1'; +drop table t1; + +set names utf8; +create table t1 (i int) +comment '123456789*123456789*123456789*123456789*123456789*123456789*'; +show create table t1; +drop table t1; diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index 35aff8b3c5a..7afdb857ca2 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1097,6 +1097,17 @@ select * from t1; drop table t1; # +# Bug #18014: problem with 'alter table' +# + +create table t1(a decimal(7,2)); +insert into t1 values(123.12); +select * from t1; +alter table t1 modify a decimal(10,2); +select * from t1; +drop table t1; + +# # Bug#19667 group by a decimal expression yields wrong result # create table t1 (i int, j int); diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 801bd13fab7..daba7dfaa3c 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -872,3 +872,65 @@ DROP VIEW test2.t3; DROP TABLE test2.t1, test1.t0; DROP DATABASE test2; DROP DATABASE test1; + + +# +# BUG#20570: CURRENT_USER() in a VIEW with SQL SECURITY DEFINER +# returns invoker name +# +--disable_warnings +DROP VIEW IF EXISTS v1; +DROP VIEW IF EXISTS v2; +DROP VIEW IF EXISTS v3; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT CURRENT_USER() AS cu; + +CREATE FUNCTION f1() RETURNS VARCHAR(77) SQL SECURITY INVOKER + RETURN CURRENT_USER(); +CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT f1() AS cu; + +CREATE PROCEDURE p1(OUT cu VARCHAR(77)) SQL SECURITY INVOKER + SET cu= CURRENT_USER(); +delimiter |; +CREATE FUNCTION f2() RETURNS VARCHAR(77) SQL SECURITY INVOKER +BEGIN + DECLARE cu VARCHAR(77); + CALL p1(cu); + RETURN cu; +END| +delimiter ;| +CREATE SQL SECURITY DEFINER VIEW v3 AS SELECT f2() AS cu; + +CREATE USER mysqltest_u1@localhost; +GRANT ALL ON test.* TO mysqltest_u1@localhost; + +connect (conn1, localhost, mysqltest_u1,,); + +--echo +--echo The following tests should all return 1. +--echo +SELECT CURRENT_USER() = 'mysqltest_u1@localhost'; +SELECT f1() = 'mysqltest_u1@localhost'; +CALL p1(@cu); +SELECT @cu = 'mysqltest_u1@localhost'; +SELECT f2() = 'mysqltest_u1@localhost'; +SELECT cu = 'root@localhost' FROM v1; +SELECT cu = 'root@localhost' FROM v2; +SELECT cu = 'root@localhost' FROM v3; + +disconnect conn1; +connection default; + +DROP VIEW v3; +DROP FUNCTION f2; +DROP PROCEDURE p1; +DROP FUNCTION f1; +DROP VIEW v2; +DROP VIEW v1; +DROP USER mysqltest_u1@localhost; + +# End of 5.0 tests. diff --git a/mysql-test/t/wait_timeout.test b/mysql-test/t/wait_timeout.test index 195d1a5d3f2..dbd792e48d8 100644 --- a/mysql-test/t/wait_timeout.test +++ b/mysql-test/t/wait_timeout.test @@ -42,6 +42,9 @@ while (!`select @aborted_clients`) } } --enable_query_log +# The server has disconnected, add small sleep to make sure +# the disconnect has reached client +sleep 1; connection default; # When the connection is closed in this way, the error code should @@ -86,6 +89,9 @@ while (!`select @aborted_clients`) } } --enable_query_log +# The server has disconnected, add small sleep to make sure +# the disconnect has reached client +sleep 1; connection con1; # When the connection is closed in this way, the error code should |