diff options
Diffstat (limited to 'mysql-test')
87 files changed, 2751 insertions, 452 deletions
diff --git a/mysql-test/include/common-tests.inc b/mysql-test/include/common-tests.inc index 46d0182d17f..882ac689498 100644 --- a/mysql-test/include/common-tests.inc +++ b/mysql-test/include/common-tests.inc @@ -1296,9 +1296,9 @@ explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; # The next should give an error # --- error 1072 +-- error 1176 explain select fld3 from t2 ignore index (fld3,not_used); --- error 1072 +-- error 1176 explain select fld3 from t2 use index (not_used); # diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 4a9628c0721..92407380ac2 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -671,6 +671,12 @@ sub command_line_setup () { { push(@opt_extra_mysqld_opt, $arg); } + elsif ( $arg =~ /^--$/ ) + { + # It is an effect of setting 'pass_through' in option processing + # that the lone '--' separating options from arguments survives, + # simply ignore it. + } elsif ( $arg =~ /^-/ ) { usage("Invalid option \"$arg\""); @@ -3428,6 +3434,13 @@ sub valgrind_arguments { ############################################################################## sub usage ($) { + my $message= shift; + + if ( $message ) + { + print STDERR "$message \n"; + } + print STDERR <<HERE; mysql-test-run [ OPTIONS ] [ TESTCASE ] diff --git a/mysql-test/r/analyze.result b/mysql-test/r/analyze.result index fc267cb598d..7b476c3cca2 100644 --- a/mysql-test/r/analyze.result +++ b/mysql-test/r/analyze.result @@ -46,6 +46,7 @@ Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_ execute stmt1; Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype deallocate prepare stmt1; +drop table t1; create temporary table t1(a int, index(a)); insert into t1 values('1'),('2'),('3'),('4'),('5'); analyze table t1; diff --git a/mysql-test/r/compress.result b/mysql-test/r/compress.result index efcafbbe736..cce66fd84ef 100644 --- a/mysql-test/r/compress.result +++ b/mysql-test/r/compress.result @@ -145,9 +145,9 @@ explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index explain select fld3 from t2 ignore index (fld3,not_used); -ERROR 42000: Key column 'not_used' doesn't exist in table +ERROR HY000: Key 'not_used' doesn't exist in table 't2' explain select fld3 from t2 use index (not_used); -ERROR 42000: Key column 'not_used' doesn't exist in table +ERROR HY000: Key 'not_used' doesn't exist in table 't2' select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; fld3 honeysuckle diff --git a/mysql-test/r/create_not_windows.result b/mysql-test/r/create_not_windows.result new file mode 100644 index 00000000000..b975c98c2b1 --- /dev/null +++ b/mysql-test/r/create_not_windows.result @@ -0,0 +1,14 @@ +drop table if exists `about:text`; +create table `about:text` ( +_id int not null auto_increment, +`about:text` varchar(255) not null default '', +primary key (_id) +); +show create table `about:text`; +Table Create Table +about:text CREATE TABLE `about:text` ( + `_id` int(11) NOT NULL auto_increment, + `about:text` varchar(255) NOT NULL default '', + PRIMARY KEY (`_id`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table `about:text`; diff --git a/mysql-test/r/ctype_sjis.result b/mysql-test/r/ctype_sjis.result index d1976a516d2..dab5991b505 100644 --- a/mysql-test/r/ctype_sjis.result +++ b/mysql-test/r/ctype_sjis.result @@ -172,6 +172,6 @@ c2h ab_def drop table t1; SET NAMES sjis; -SELECT HEX('²“‘@\Œ\') FROM DUAL; -HEX('²“‘@_Œ\') +SELECT HEX('²“‘@Œ\') FROM DUAL; +HEX('²“‘@Œ\') 8DB2939181408C5C diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 75e1548cdee..3bd7b2ccc15 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -24,9 +24,9 @@ explain select * from t1 use key (str,str) where str="foo"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const str str 11 const 1 explain select * from t1 use key (str,str,foo) where str="foo"; -ERROR 42000: Key column 'foo' doesn't exist in table +ERROR HY000: Key 'foo' doesn't exist in table 't1' explain select * from t1 ignore key (str,str,foo) where str="foo"; -ERROR 42000: Key column 'foo' doesn't exist in table +ERROR HY000: Key 'foo' doesn't exist in table 't1' drop table t1; explain select 1; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result index 16c308e3450..a7f5e5e8fec 100644 --- a/mysql-test/r/flush.result +++ b/mysql-test/r/flush.result @@ -48,3 +48,10 @@ lock table t1 read, t2 read, t3 read; flush tables with read lock; unlock tables; drop table t1, t2, t3; +create table t1 (c1 int); +create table t2 (c1 int); +lock table t1 write; + flush tables with read lock; + insert into t2 values(1); +unlock tables; +drop table t1, t2; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index cfa4cc0ef68..1542794798a 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -821,144 +821,6 @@ SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6; MAX(id) NULL DROP TABLE t1; -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; -insert into t2m values (5); -insert into t2i values (5); -select min(a) from t1m; -min(a) -NULL -select min(7) from t1m; -min(7) -NULL -select min(7) from DUAL; -min(7) -NULL -explain select min(7) from t2m join t1m; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -select min(7) from t2m join t1m; -min(7) -NULL -select max(a) from t1m; -max(a) -NULL -select max(7) from t1m; -max(7) -NULL -select max(7) from DUAL; -max(7) -NULL -explain select max(7) from t2m join t1m; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -select max(7) from t2m join t1m; -max(7) -NULL -select 1, min(a) from t1m where a=99; -1 min(a) -1 NULL -select 1, min(a) from t1m where 1=99; -1 min(a) -1 NULL -select 1, min(1) from t1m where a=99; -1 min(1) -1 NULL -select 1, min(1) from t1m where 1=99; -1 min(1) -1 NULL -select 1, max(a) from t1m where a=99; -1 max(a) -1 NULL -select 1, max(a) from t1m where 1=99; -1 max(a) -1 NULL -select 1, max(1) from t1m where a=99; -1 max(1) -1 NULL -select 1, max(1) from t1m where 1=99; -1 max(1) -1 NULL -select min(a) from t1i; -min(a) -NULL -select min(7) from t1i; -min(7) -NULL -select min(7) from DUAL; -min(7) -NULL -explain select min(7) from t2i join t1i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 -select min(7) from t2i join t1i; -min(7) -NULL -select max(a) from t1i; -max(a) -NULL -select max(7) from t1i; -max(7) -NULL -select max(7) from DUAL; -max(7) -NULL -explain select max(7) from t2i join t1i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 -select max(7) from t2i join t1i; -max(7) -NULL -select 1, min(a) from t1i where a=99; -1 min(a) -1 NULL -select 1, min(a) from t1i where 1=99; -1 min(a) -1 NULL -select 1, min(1) from t1i where a=99; -1 min(1) -1 NULL -select 1, min(1) from t1i where 1=99; -1 min(1) -1 NULL -select 1, max(a) from t1i where a=99; -1 max(a) -1 NULL -select 1, max(a) from t1i where 1=99; -1 max(a) -1 NULL -select 1, max(1) from t1i where a=99; -1 max(1) -1 NULL -select 1, max(1) from t1i where 1=99; -1 max(1) -1 NULL -explain select count(*), min(7), max(7) from t1m, t1i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 -select count(*), min(7), max(7) from t1m, t1i; -count(*) min(7) max(7) -0 NULL NULL -explain select count(*), min(7), max(7) from t1m, t2i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 -select count(*), min(7), max(7) from t1m, t2i; -count(*) min(7) max(7) -0 NULL NULL -explain select count(*), min(7), max(7) from t2m, t1i; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2m system NULL NULL NULL NULL 1 -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 -select count(*), min(7), max(7) from t2m, t1i; -count(*) min(7) max(7) -0 NULL NULL -drop table t1m, t1i, t2m, t2i; create table t2 (ff double); insert into t2 values (2.2); select cast(sum(distinct ff) as decimal(5,2)) from t2; diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index e3257ce5fd0..e38e2624e19 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -326,3 +326,20 @@ deallocate prepare s; set @str=NULL; drop table t2; drop table t1; +create table t1 ( +some_id smallint(5) unsigned, +key (some_id) +); +insert into t1 values (1),(2); +select some_id from t1 where some_id not in(2,-1); +some_id +1 +select some_id from t1 where some_id not in(-4,-1,-4); +some_id +1 +2 +select some_id from t1 where some_id not in(-4,-1,3423534,2342342); +some_id +1 +2 +drop table t1; diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 6f8269bdcbd..a17661d26d0 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1046,4 +1046,10 @@ cast(ltrim(' 20.06 ') as decimal(19,2)) select cast(rtrim(ltrim(' 20.06 ')) as decimal(19,2)); cast(rtrim(ltrim(' 20.06 ')) as decimal(19,2)) 20.06 +select conv("18383815659218730760",10,10) + 0; +conv("18383815659218730760",10,10) + 0 +1.8383815659219e+19 +select "18383815659218730760" + 0; +"18383815659218730760" + 0 +1.8383815659219e+19 End of 5.0 tests diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 0e029802555..bc1bd3b6757 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -7,20 +7,20 @@ period_add("9602",-12) period_diff(199505,"9404") 199502 13 select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now()); now()-now() weekday(curdate())-weekday(now()) unix_timestamp()-unix_timestamp(now()) -0 0 0 +0.000000 0 0 select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0; from_unixtime(unix_timestamp("1994-03-02 10:11:12")) from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s") from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0 -1994-03-02 10:11:12 1994-03-02 10:11:12 19940302101112 +1994-03-02 10:11:12 1994-03-02 10:11:12 19940302101112.000000 select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"), sec_to_time(time_to_sec("0:30:47")/6.21); sec_to_time(9001) sec_to_time(9001)+0 time_to_sec("15:12:22") sec_to_time(time_to_sec("0:30:47")/6.21) -02:30:01 23001 54742 00:04:57 +02:30:01 23001.000000 54742 00:04:57 select sec_to_time(time_to_sec('-838:59:59')); sec_to_time(time_to_sec('-838:59:59')) -838:59:59 select now()-curdate()*1000000-curtime(); now()-curdate()*1000000-curtime() -0 +0.000000 select strcmp(current_timestamp(),concat(current_date()," ",current_time())); strcmp(current_timestamp(),concat(current_date()," ",current_time())) 0 @@ -751,6 +751,10 @@ select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m')); monthname(str_to_date(null, '%m')) monthname(str_to_date(null, '%m')) monthname(str_to_date(1, '%m')) monthname(str_to_date(0, '%m')) NULL NULL January NULL +select now() - now() + 0, curtime() - curtime() + 0, +sec_to_time(1) + 0, from_unixtime(1) + 0; +now() - now() + 0 curtime() - curtime() + 0 sec_to_time(1) + 0 from_unixtime(1) + 0 +0.000000 0.000000 1.000000 19700101030001.000000 explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1, timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a2; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 3432de5179a..3f3325354ee 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -357,12 +357,12 @@ show grants for grant_user@localhost; Grants for grant_user@localhost GRANT USAGE ON *.* TO 'grant_user'@'localhost' GRANT INSERT (a, d, c, b) ON `test`.`t1` TO 'grant_user'@'localhost' -select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv; +select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name; Host Db User Table_name Column_name Column_priv -localhost test grant_user t1 b Insert -localhost test grant_user t1 d Insert localhost test grant_user t1 a Insert +localhost test grant_user t1 b Insert localhost test grant_user t1 c Insert +localhost test grant_user t1 d Insert revoke ALL PRIVILEGES on t1 from grant_user@localhost; show grants for grant_user@localhost; Grants for grant_user@localhost @@ -381,13 +381,27 @@ grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost; grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost; grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost; grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost; -show grants for mysqltest_3@localhost; -Grants for mysqltest_3@localhost -GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost' -GRANT SELECT (b) ON `mysqltest_1`.`t2` TO 'mysqltest_3'@'localhost' -GRANT UPDATE (a) ON `mysqltest_1`.`t1` TO 'mysqltest_3'@'localhost' -GRANT UPDATE (d) ON `mysqltest_2`.`t2` TO 'mysqltest_3'@'localhost' -GRANT SELECT (c) ON `mysqltest_2`.`t1` TO 'mysqltest_3'@'localhost' +SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES +WHERE GRANTEE = '''mysqltest_3''@''localhost''' +ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE +'mysqltest_3'@'localhost' NULL mysqltest_1 t1 a UPDATE NO +'mysqltest_3'@'localhost' NULL mysqltest_2 t1 c SELECT NO +'mysqltest_3'@'localhost' NULL mysqltest_1 t2 b SELECT NO +'mysqltest_3'@'localhost' NULL mysqltest_2 t2 d UPDATE NO +SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES +WHERE GRANTEE = '''mysqltest_3''@''localhost''' +ORDER BY TABLE_NAME,PRIVILEGE_TYPE; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE +SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES +WHERE GRANTEE = '''mysqltest_3''@''localhost''' +ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE; +GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE +SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES +WHERE GRANTEE = '''mysqltest_3''@''localhost''' + ORDER BY TABLE_CATALOG,PRIVILEGE_TYPE; +GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE +'mysqltest_3'@'localhost' NULL USAGE NO update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1; ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for column 'q' in table 't1' update mysqltest_1.t2, mysqltest_2.t2 set d=20 where d=1; @@ -591,6 +605,7 @@ insert into tables_priv values ('','test_db','mysqltest_1','test_table','test_gr flush privileges; delete from tables_priv where host = '' and user = 'mysqltest_1'; flush privileges; +use test; set @user123="non-existent"; select * from mysql.db where user=@user123; Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv @@ -621,7 +636,6 @@ show grants for mysqltest_7@; Grants for mysqltest_7@ GRANT USAGE ON *.* TO 'mysqltest_7'@'' IDENTIFIED BY PASSWORD '*2FB071A056F9BB745219D9C876814231DAF46517' 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; @@ -642,3 +656,214 @@ delete from mysql.db where user='mysqltest1'; delete from mysql.tables_priv where user='mysqltest1'; flush privileges; drop database mysqltest; +use test; +create table t1 (a int); +create table t2 as select * from mysql.user where user=''; +delete from mysql.user where user=''; +flush privileges; +create user mysqltest_8@''; +create user mysqltest_8; +create user mysqltest_8@host8; +create user mysqltest_8@''; +ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@'' +create user mysqltest_8; +ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@'%' +create user mysqltest_8@host8; +ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@'host8' +select user, QUOTE(host) from mysql.user where user="mysqltest_8"; +user QUOTE(host) +mysqltest_8 '' +mysqltest_8 '%' +mysqltest_8 'host8' +Schema privileges +grant select on mysqltest.* to mysqltest_8@''; +show grants for mysqltest_8@''; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'' +grant select on mysqltest.* to mysqltest_8@; +show grants for mysqltest_8@; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'' +grant select on mysqltest.* to mysqltest_8; +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'%' +select * from information_schema.schema_privileges +where grantee like "'mysqltest_8'%"; +GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE +'mysqltest_8'@'%' NULL mysqltest SELECT NO +'mysqltest_8'@'' NULL mysqltest SELECT NO +select * from t1; +a +revoke select on mysqltest.* from mysqltest_8@''; +revoke select on mysqltest.* from mysqltest_8; +show grants for mysqltest_8@''; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +select * from information_schema.schema_privileges +where grantee like "'mysqltest_8'%"; +GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE +flush privileges; +show grants for mysqltest_8@''; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +show grants for mysqltest_8@; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +grant select on mysqltest.* to mysqltest_8@''; +flush privileges; +show grants for mysqltest_8@; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'' +revoke select on mysqltest.* from mysqltest_8@''; +flush privileges; +Column privileges +grant update (a) on t1 to mysqltest_8@''; +grant update (a) on t1 to mysqltest_8; +show grants for mysqltest_8@''; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'' +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' +flush privileges; +show grants for mysqltest_8@''; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'' +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' +select * from information_schema.column_privileges; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE +'mysqltest_8'@'%' NULL test t1 a UPDATE NO +'mysqltest_8'@'' NULL test t1 a UPDATE NO +select * from t1; +a +revoke update (a) on t1 from mysqltest_8@''; +revoke update (a) on t1 from mysqltest_8; +show grants for mysqltest_8@''; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +select * from information_schema.column_privileges; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE +flush privileges; +show grants for mysqltest_8@''; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +Table privileges +grant update on t1 to mysqltest_8@''; +grant update on t1 to mysqltest_8; +show grants for mysqltest_8@''; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'' +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%' +flush privileges; +show grants for mysqltest_8@''; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'' +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%' +select * from information_schema.table_privileges; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE +'mysqltest_8'@'%' NULL test t1 UPDATE NO +'mysqltest_8'@'' NULL test t1 UPDATE NO +select * from t1; +a +revoke update on t1 from mysqltest_8@''; +revoke update on t1 from mysqltest_8; +show grants for mysqltest_8@''; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +select * from information_schema.table_privileges; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE +flush privileges; +show grants for mysqltest_8@''; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +"DROP USER" should clear privileges +grant all privileges on mysqltest.* to mysqltest_8@''; +grant select on mysqltest.* to mysqltest_8@''; +grant update on t1 to mysqltest_8@''; +grant update (a) on t1 to mysqltest_8@''; +grant all privileges on mysqltest.* to mysqltest_8; +show grants for mysqltest_8@''; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'' +GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'' +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%' +select * from information_schema.user_privileges +where grantee like "'mysqltest_8'%"; +GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE +'mysqltest_8'@'host8' NULL USAGE NO +'mysqltest_8'@'%' NULL USAGE NO +'mysqltest_8'@'' NULL USAGE NO +select * from t1; +a +flush privileges; +show grants for mysqltest_8@''; +Grants for mysqltest_8@ +GRANT USAGE ON *.* TO 'mysqltest_8'@'' +GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'' +GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'' +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%' +drop user mysqltest_8@''; +show grants for mysqltest_8@''; +ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host '' +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO 'mysqltest_8'@'%' +GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%' +select * from information_schema.user_privileges +where grantee like "'mysqltest_8'%"; +GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE +'mysqltest_8'@'host8' NULL USAGE NO +'mysqltest_8'@'%' NULL USAGE NO +drop user mysqltest_8; +connect(localhost,mysqltest_8,,test,MASTER_PORT,MASTER_SOCKET); +ERROR 28000: Access denied for user 'mysqltest_8'@'localhost' (using password: NO) +show grants for mysqltest_8; +ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host '%' +drop user mysqltest_8@host8; +show grants for mysqltest_8@host8; +ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host 'host8' +insert into mysql.user select * from t2; +flush privileges; +drop table t2; +drop table t1; diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index f9b55cc6a7b..d62586dba85 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -1981,46 +1981,7 @@ a b c d -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; -insert into t4 (a1, a2, b, c, d, dummy) select * from t1; -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 t1; -Table Op Msg_type Msg_text -test.t1 analyze status Table is already up to date -select distinct a1 from t4 where pk_col not in (1,2,3,4); -a1 -a -b -c -d -drop table t1,t2,t3,t4; -create table t1 ( -a varchar(30), b varchar(30), primary key(a), key(b) -) engine=innodb; -select distinct a from t1; -a -drop table t1; -create table t1(a int, key(a)) engine=innodb; -insert into t1 values(1); -select a, count(a) from t1 group by a with rollup; -a count(a) -1 1 -NULL 1 -drop table t1; -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; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index; Using temporary -explain select distinct f1, f2 from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL PRIMARY 5 NULL 3 Using index for group-by; Using temporary -drop table t1; +drop table t1,t2,t3; create table t1 (c1 int not null,c2 int not null, primary key(c1,c2)); insert into t1 (c1,c2) values (10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9); diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index a37f260ff31..68b13b5fc0a 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -12,7 +12,7 @@ explain extended select count(a) as b from t1 where a=0 having b >=0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 select count(`test`.`t1`.`a`) AS `b` from `test`.`t1` where 0 having (count(`test`.`t1`.`a`) >= 0) +Note 1003 select count(`test`.`t1`.`a`) AS `b` from `test`.`t1` where 0 having (`b` >= 0) drop table t1; CREATE TABLE t1 ( raw_id int(10) NOT NULL default '0', diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result index d72f5771f15..906c431b834 100644 --- a/mysql-test/r/heap.result +++ b/mysql-test/r/heap.result @@ -718,3 +718,16 @@ Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length I t1 MEMORY 10 Fixed 0 11 0 # 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 MEMORY 10 Fixed 0 12 0 # 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL drop table t1, t2; +CREATE TABLE t1(a VARCHAR(1), b VARCHAR(2), c VARCHAR(256), +KEY(a), KEY(b), KEY(c)) ENGINE=MEMORY; +INSERT INTO t1 VALUES('a','aa',REPEAT('a', 256)),('a','aa',REPEAT('a',256)); +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE b='aa'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE c=REPEAT('a',256); +COUNT(*) +2 +DROP TABLE t1; diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result index b63eaf7e48c..4b05e8f44e1 100644 --- a/mysql-test/r/heap_btree.result +++ b/mysql-test/r/heap_btree.result @@ -256,3 +256,6 @@ SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() INDEX_LENGTH 21 DROP TABLE t1; +CREATE TABLE t1 (a INT, UNIQUE USING BTREE(a)) ENGINE=MEMORY; +INSERT INTO t1 VALUES(NULL),(NULL); +DROP TABLE t1; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 633b11e1f1b..6da07922251 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -866,58 +866,62 @@ grant select (f1) on mysqltest.t1 to user1@localhost; grant select on mysqltest.t2 to user2@localhost; grant select on mysqltest.* to user3@localhost; grant select on *.* to user4@localhost; -select * from information_schema.column_privileges; +select * from information_schema.column_privileges order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 'user1'@'localhost' NULL mysqltest t1 f1 SELECT NO -select * from information_schema.table_privileges; +select * from information_schema.table_privileges order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE -select * from information_schema.schema_privileges; +select * from information_schema.schema_privileges order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE -select * from information_schema.user_privileges; +select * from information_schema.user_privileges order by grantee; GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'user1'@'localhost' NULL USAGE NO show grants; Grants for user1@localhost GRANT USAGE ON *.* TO 'user1'@'localhost' GRANT SELECT (f1) ON `mysqltest`.`t1` TO 'user1'@'localhost' -select * from information_schema.column_privileges; +select * from information_schema.column_privileges order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE -select * from information_schema.table_privileges; +select * from information_schema.table_privileges order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 'user2'@'localhost' NULL mysqltest t2 SELECT NO -select * from information_schema.schema_privileges; +select * from information_schema.schema_privileges order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE -select * from information_schema.user_privileges; +select * from information_schema.user_privileges order by grantee; GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'user2'@'localhost' NULL USAGE NO show grants; Grants for user2@localhost GRANT USAGE ON *.* TO 'user2'@'localhost' GRANT SELECT ON `mysqltest`.`t2` TO 'user2'@'localhost' -select * from information_schema.column_privileges; +select * from information_schema.column_privileges order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE -select * from information_schema.table_privileges; +select * from information_schema.table_privileges order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE -select * from information_schema.schema_privileges; +select * from information_schema.schema_privileges order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 'user3'@'localhost' NULL mysqltest SELECT NO -select * from information_schema.user_privileges; +select * from information_schema.user_privileges order by grantee; GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'user3'@'localhost' NULL USAGE NO show grants; Grants for user3@localhost GRANT USAGE ON *.* TO 'user3'@'localhost' GRANT SELECT ON `mysqltest`.* TO 'user3'@'localhost' -select * from information_schema.column_privileges where grantee like '%user%'; +select * from information_schema.column_privileges where grantee like '%user%' +order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 'user1'@'localhost' NULL mysqltest t1 f1 SELECT NO -select * from information_schema.table_privileges where grantee like '%user%'; +select * from information_schema.table_privileges where grantee like '%user%' +order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 'user2'@'localhost' NULL mysqltest t2 SELECT NO -select * from information_schema.schema_privileges where grantee like '%user%'; +select * from information_schema.schema_privileges where grantee like '%user%' +order by grantee; GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 'user3'@'localhost' NULL mysqltest SELECT NO -select * from information_schema.user_privileges where grantee like '%user%'; +select * from information_schema.user_privileges where grantee like '%user%' +order by grantee; GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'user1'@'localhost' NULL USAGE NO 'user2'@'localhost' NULL USAGE NO @@ -1108,3 +1112,25 @@ routine_name delete from proc where name=''; use test; +grant select on test.* to mysqltest_1@localhost; +create table t1 (id int); +create view v1 as select * from t1; +create definer = mysqltest_1@localhost +sql security definer view v2 as select 1; +select * from information_schema.views +where table_name='v1' or table_name='v2'; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE +NULL test v1 NONE YES root@localhost DEFINER +NULL test v2 select 1 AS `1` NONE NO mysqltest_1@localhost DEFINER +drop view v1, v2; +drop table t1; +drop user mysqltest_1@localhost; +set @a:= '.'; +create table t1(f1 char(5)); +create table t2(f1 char(5)); +select concat(@a, table_name), @a, table_name +from information_schema.tables where table_schema = 'test'; +concat(@a, table_name) @a table_name +.t1 . t1 +.t2 . t2 +drop table t1,t2; diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 6295bac34a0..61a10c5f72c 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -1,3 +1,7 @@ +drop table if exists t1,t2; +drop view if exists v1,v2; +drop function if exists f1; +drop function if exists f2; use INFORMATION_SCHEMA; show tables; Tables_in_information_schema @@ -24,10 +28,12 @@ TABLE_CONSTRAINTS TABLE_PRIVILEGES TRIGGERS create database `inf%`; +create database mbase; use `inf%`; show tables; Tables_in_inf% grant all privileges on `inf%`.* to 'mysqltest_1'@'localhost'; +grant all privileges on `mbase`.* to 'mysqltest_1'@'localhost'; create table t1 (f1 int); create function func1(curr_int int) returns int begin @@ -36,9 +42,58 @@ select max(f1) from t1 into ret_val; return ret_val; end| create view v1 as select f1 from t1 where f1 = func1(f1); +create function func2() returns int return 1; +use mbase; +create procedure p1 () +begin +select table_name from information_schema.key_column_usage +order by table_name; +end| +create table t1 +(f1 int(10) unsigned not null, +f2 varchar(100) not null, +primary key (f1), unique key (f2)); select * from information_schema.tables; +call mbase.p1(); +call mbase.p1(); +call mbase.p1(); +use `inf%`; drop user mysqltest_1@localhost; +drop table t1; +select table_name, table_type, table_comment from information_schema.tables +where table_schema='inf%' and func2(); +table_name table_type table_comment +v1 VIEW View 'inf%.v1' references invalid table(s) or column(s) or function(s) or define +select table_name, table_type, table_comment from information_schema.tables +where table_schema='inf%' and func2(); +table_name table_type table_comment +v1 VIEW View 'inf%.v1' references invalid table(s) or column(s) or function(s) or define drop view v1; drop function func1; -drop table t1; +drop function func2; drop database `inf%`; +drop procedure mbase.p1; +drop database mbase; +use test; +create table t1 (i int); +create function f1 () returns int return (select max(i) from t1); +create view v1 as select f1(); +create table t2 (id int); +create function f2 () returns int return (select max(i) from t2); +create view v2 as select f2(); +drop table t2; +select table_name, table_type, table_comment from information_schema.tables +where table_schema='test'; +table_name table_type table_comment +t1 BASE TABLE +v1 VIEW VIEW +v2 VIEW View 'test.v2' references invalid table(s) or column(s) or function(s) or define +drop table t1; +select table_name, table_type, table_comment from information_schema.tables +where table_schema='test'; +table_name table_type table_comment +v1 VIEW View 'test.v1' references invalid table(s) or column(s) or function(s) or define +v2 VIEW View 'test.v2' references invalid table(s) or column(s) or function(s) or define +drop function f1; +drop function f2; +drop view v1, v2; diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 2a4e3555e3b..8dbfa906fe1 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -54,3 +54,217 @@ c.c_id = 218 and expiredate is null; slai_id 12 drop table t1, t2; +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; +insert into t2m values (5); +insert into t2i values (5); +select min(a) from t1m; +min(a) +NULL +select min(7) from t1m; +min(7) +NULL +select min(7) from DUAL; +min(7) +NULL +explain select min(7) from t2m join t1m; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +select min(7) from t2m join t1m; +min(7) +NULL +select max(a) from t1m; +max(a) +NULL +select max(7) from t1m; +max(7) +NULL +select max(7) from DUAL; +max(7) +NULL +explain select max(7) from t2m join t1m; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +select max(7) from t2m join t1m; +max(7) +NULL +select 1, min(a) from t1m where a=99; +1 min(a) +1 NULL +select 1, min(a) from t1m where 1=99; +1 min(a) +1 NULL +select 1, min(1) from t1m where a=99; +1 min(1) +1 NULL +select 1, min(1) from t1m where 1=99; +1 min(1) +1 NULL +select 1, max(a) from t1m where a=99; +1 max(a) +1 NULL +select 1, max(a) from t1m where 1=99; +1 max(a) +1 NULL +select 1, max(1) from t1m where a=99; +1 max(1) +1 NULL +select 1, max(1) from t1m where 1=99; +1 max(1) +1 NULL +select min(a) from t1i; +min(a) +NULL +select min(7) from t1i; +min(7) +NULL +select min(7) from DUAL; +min(7) +NULL +explain select min(7) from t2i join t1i; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 +select min(7) from t2i join t1i; +min(7) +NULL +select max(a) from t1i; +max(a) +NULL +select max(7) from t1i; +max(7) +NULL +select max(7) from DUAL; +max(7) +NULL +explain select max(7) from t2i join t1i; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 +select max(7) from t2i join t1i; +max(7) +NULL +select 1, min(a) from t1i where a=99; +1 min(a) +1 NULL +select 1, min(a) from t1i where 1=99; +1 min(a) +1 NULL +select 1, min(1) from t1i where a=99; +1 min(1) +1 NULL +select 1, min(1) from t1i where 1=99; +1 min(1) +1 NULL +select 1, max(a) from t1i where a=99; +1 max(a) +1 NULL +select 1, max(a) from t1i where 1=99; +1 max(a) +1 NULL +select 1, max(1) from t1i where a=99; +1 max(1) +1 NULL +select 1, max(1) from t1i where 1=99; +1 max(1) +1 NULL +explain select count(*), min(7), max(7) from t1m, t1i; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 +select count(*), min(7), max(7) from t1m, t1i; +count(*) min(7) max(7) +0 NULL NULL +explain select count(*), min(7), max(7) from t1m, t2i; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +select count(*), min(7), max(7) from t1m, t2i; +count(*) min(7) max(7) +0 NULL NULL +explain select count(*), min(7), max(7) from t2m, t1i; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2m system NULL NULL NULL NULL 1 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 +select count(*), min(7), max(7) from t2m, t1i; +count(*) min(7) max(7) +0 NULL NULL +drop table t1m, t1i, t2m, t2i; +create table t1 ( +a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' +); +insert into t1 (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 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; +insert into t4 (a1, a2, b, c, d, dummy) select * from t1; +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 t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select distinct a1 from t4 where pk_col not in (1,2,3,4); +a1 +a +b +c +d +drop table t1,t4; +create table t1 ( +a varchar(30), b varchar(30), primary key(a), key(b) +) engine=innodb; +select distinct a from t1; +a +drop table t1; +create table t1(a int, key(a)) engine=innodb; +insert into t1 values(1); +select a, count(a) from t1 group by a with rollup; +a count(a) +1 1 +NULL 1 +drop table t1; +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index; Using temporary +explain select distinct f1, f2 from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL PRIMARY 5 NULL 3 Using index for group-by; Using temporary +drop table t1; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 712a60828f7..eae023813b5 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1151,8 +1151,8 @@ EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index -1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index +1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where DROP TABLE t1,t2; DROP VIEW IF EXISTS v1,v2; DROP TABLE IF EXISTS t1,t2; @@ -1176,3 +1176,38 @@ a b 3 3 DROP VIEW v1,v2; DROP TABLE t1,t2; +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); +INSERT INTO t1 VALUES (1), (2), (3), (4); +INSERT INTO t2 VALUES (2), (3); +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1); +a b +1 NULL +2 2 +3 3 +4 NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1); +a b +1 NULL +2 2 +3 3 +4 NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1); +a b +1 NULL +2 2 +3 3 +4 NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2); +a b +1 NULL +2 2 +3 3 +4 NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0); +a b +1 NULL +2 2 +3 3 +4 NULL +DROP TABLE t1,t2; diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result index 99f5277f817..406a92b9a08 100644 --- a/mysql-test/r/key_cache.result +++ b/mysql-test/r/key_cache.result @@ -191,10 +191,10 @@ cache index t1 in unknown_key_cache; ERROR HY000: Unknown key cache 'unknown_key_cache' cache index t1 key (unknown_key) in keycache1; Table Op Msg_type Msg_text -test.t1 assign_to_keycache error Key column 'unknown_key' doesn't exist in table +test.t1 assign_to_keycache error Key 'unknown_key' doesn't exist in table 't1' test.t1 assign_to_keycache status Operation failed Warnings: -Error 1072 Key column 'unknown_key' doesn't exist in table +Error 1176 Key 'unknown_key' doesn't exist in table 't1' select @@keycache2.key_buffer_size; @@keycache2.key_buffer_size 4194304 diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index 73e3a9d32e3..2cb122fb988 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -43,3 +43,27 @@ Field Type Null Key Default Extra a int(11) YES NULL unlock tables; drop table t1; +CREATE DATABASE mysqltest_1; +FLUSH TABLES WITH READ LOCK; + DROP DATABASE mysqltest_1; +DROP DATABASE mysqltest_1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +UNLOCK TABLES; +DROP DATABASE mysqltest_1; +ERROR HY000: Can't drop database 'mysqltest_1'; database doesn't exist +use mysql; +LOCK TABLES columns_priv WRITE, db WRITE, host WRITE, user WRITE; +FLUSH TABLES; +use mysql; + SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1; +OPTIMIZE TABLES columns_priv, db, host, user; +Table Op Msg_type Msg_text +mysql.columns_priv optimize status OK +mysql.db optimize status OK +mysql.host optimize status OK +mysql.user optimize status OK +UNLOCK TABLES; +Select_priv +N +use test; +use test; diff --git a/mysql-test/r/lowercase_table2.result b/mysql-test/r/lowercase_table2.result index 44235cbf900..e369fb7e482 100644 --- a/mysql-test/r/lowercase_table2.result +++ b/mysql-test/r/lowercase_table2.result @@ -165,3 +165,12 @@ create table t1Aa (col1 int); select t1Aa.col1 from t1aA,t2Aa where t1Aa.col1 = t2aA.col1; col1 drop table t2aA, t1Aa; +create database mysqltest_LC2; +use mysqltest_LC2; +create table myUC (i int); +select TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES +where TABLE_SCHEMA ='mysqltest_LC2'; +TABLE_SCHEMA TABLE_NAME +mysqltest_LC2 myUC +use test; +drop database mysqltest_LC2; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 9a34d6fba58..568f83b7d6d 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -776,3 +776,9 @@ insert into t1 values ("Monty"),("WAX"),("Walrus"); alter table t1 engine=MERGE; ERROR HY000: Table storage engine for 't1' doesn't have this option drop table t1; +create table t1 (b bit(1)); +create table t2 (b bit(1)); +create table tm (b bit(1)) engine = merge union = (t1,t2); +select * from tm; +b +drop table tm, t1, t2; diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 664833fab2a..c3be791b523 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -190,4 +190,8 @@ select HEX(f) from t4; HEX(f) 835C flush logs; -drop table t1, t2, t03, t04, t3, t4; +select * from t5 /* must be (1),(1) */; +a +1 +1 +drop table t1, t2, t03, t04, t3, t4, t5; diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index ad31b8e2a65..6e521a69bda 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -1,4 +1,4 @@ -DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa; +DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa, t3; drop database if exists mysqldump_test_db; drop database if exists db1; drop database if exists db2; @@ -1507,6 +1507,7 @@ a b 12 meg drop table t1, t2; drop database db1; +--fields-optionally-enclosed-by=" CREATE DATABASE mysqldump_test_db; USE mysqldump_test_db; CREATE TABLE t1 ( a INT ); @@ -2656,6 +2657,44 @@ UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; drop table t1; +create table t1(a int); +create table t2(a int); +create table t3(a int); + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS `t3`; +CREATE TABLE `t3` ( + `a` int(11) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +DROP TABLE IF EXISTS `t1`; +CREATE TABLE `t1` ( + `a` int(11) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +DROP TABLE IF EXISTS `t2`; +CREATE TABLE `t2` ( + `a` int(11) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +drop table t1, t2, t3; +End of 4.1 tests create table t1 (a int); insert into t1 values (289), (298), (234), (456), (789); create definer = CURRENT_USER view v1 as select * from t1; diff --git a/mysql-test/r/ndb_alter_table.result b/mysql-test/r/ndb_alter_table.result index acb67250c26..89999eca051 100644 --- a/mysql-test/r/ndb_alter_table.result +++ b/mysql-test/r/ndb_alter_table.result @@ -1,4 +1,4 @@ -DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1, t2; drop database if exists mysqltest; CREATE TABLE t1 ( a INT NOT NULL, @@ -315,3 +315,24 @@ unique key tx1 (c002, c003, c004, c005)) engine=ndb; create index tx2 on t1 (c010, c011, c012, c013); drop table t1; +create table t1 (a int primary key auto_increment, b int) engine=ndb; +insert into t1 (b) values (101),(102),(103); +select * from t1 where a = 3; +a b +3 103 +alter table t1 rename t2; +insert into t2 (b) values (201),(202),(203); +select * from t2 where a = 6; +a b +6 203 +alter table t2 add c int; +insert into t2 (b) values (301),(302),(303); +select * from t2 where a = 9; +a b c +9 303 NULL +alter table t2 rename t1; +insert into t1 (b) values (401),(402),(403); +select * from t1 where a = 12; +a b c +12 403 NULL +drop table t1; diff --git a/mysql-test/r/ndb_blob.result b/mysql-test/r/ndb_blob.result index a6faafa9612..a5a40cffa91 100644 --- a/mysql-test/r/ndb_blob.result +++ b/mysql-test/r/ndb_blob.result @@ -500,3 +500,69 @@ select count(*) from t1; count(*) 0 drop table t1; +create table t1 ( +a varchar(40) not null, +b mediumint not null, +t text, +c varchar(2) not null, +d bigint not null, +primary key (a,b,c), +key (c,a), +unique key (d) +) engine=ndb; +insert into t1 (a,b,c,d,t) values ('a',1110,'a',1,@v1); +insert into t1 (a,b,c,d,t) values ('b',1110,'a',2,@v2); +insert into t1 (a,b,c,d,t) values ('a',1110,'b',3,@v3); +insert into t1 (a,b,c,d,t) values ('b',1110,'b',4,@v4); +select a,b,c,d,sha1(t) from t1 order by c,a; +a b c d sha1(t) +a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5 +b 1110 a 2 b238654911689bfb626a3ef9dba4a1ca074e6a5e +a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612 +b 1110 b 4 NULL +select a,b,c,d,sha1(t) from t1 where a='a' and b=1110 and c='a'; +a b c d sha1(t) +a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5 +select a,b,c,d,sha1(t) from t1 where a='a' and b=1110 and c='b'; +a b c d sha1(t) +a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612 +update t1 set t=@v4 where a='b' and b=1110 and c='a'; +update t1 set t=@v2 where a='b' and b=1110 and c='b'; +select a,b,c,d,sha1(t) from t1 order by c,a; +a b c d sha1(t) +a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5 +b 1110 a 2 NULL +a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612 +b 1110 b 4 b238654911689bfb626a3ef9dba4a1ca074e6a5e +update t1 set t=@v2 where d=2; +update t1 set t=@v4 where d=4; +select a,b,c,d,sha1(t) from t1 order by c,a; +a b c d sha1(t) +a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5 +b 1110 a 2 b238654911689bfb626a3ef9dba4a1ca074e6a5e +a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612 +b 1110 b 4 NULL +update t1 set t=@v4 where a='b' and c='a'; +update t1 set t=@v2 where a='b' and c='b'; +select a,b,c,d,sha1(t) from t1 order by c,a; +a b c d sha1(t) +a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5 +b 1110 a 2 NULL +a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612 +b 1110 b 4 b238654911689bfb626a3ef9dba4a1ca074e6a5e +update t1 set t=@v2 where b+d=1112; +update t1 set t=@v4 where b+d=1114; +select a,b,c,d,sha1(t) from t1 order by c,a; +a b c d sha1(t) +a 1110 a 1 558a30713786aa72f66abc1e6a521d55aacdeeb5 +b 1110 a 2 b238654911689bfb626a3ef9dba4a1ca074e6a5e +a 1110 b 3 2b6515f29c20b8e9e17cc597527e516c0de8d612 +b 1110 b 4 NULL +delete from t1 where a='a' and b=1110 and c='a'; +delete from t1 where a='b' and c='a'; +delete from t1 where d=3; +delete from t1 where b+d=1114; +select count(*) from t1; +count(*) +0 +drop table t1; diff --git a/mysql-test/r/ndb_lock.result b/mysql-test/r/ndb_lock.result index b8c2c58aac4..3b433023843 100644 --- a/mysql-test/r/ndb_lock.result +++ b/mysql-test/r/ndb_lock.result @@ -63,3 +63,83 @@ pk u o 5 5 5 insert into t1 values (1,1,1); drop table t1; +create table t1 (x integer not null primary key, y varchar(32), z integer, key(z)) engine = ndb; +insert into t1 values (1,'one',1), (2,'two',2),(3,"three",3); +begin; +select * from t1 where x = 1 for update; +x y z +1 one 1 +begin; +select * from t1 where x = 2 for update; +x y z +2 two 2 +select * from t1 where x = 1 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +rollback; +commit; +begin; +select * from t1 where y = 'one' or y = 'three' order by x for update; +x y z +1 one 1 +3 three 3 +begin; +select * from t1 where x = 1 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +rollback; +commit; +begin; +select * from t1 where z > 1 and z < 3 for update; +x y z +2 two 2 +begin; +select * from t1 where x = 1 for update; +x y z +1 one 1 +select * from t1 where x = 2 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +rollback; +commit; +begin; +select * from t1 where x = 1 lock in share mode; +x y z +1 one 1 +begin; +select * from t1 where x = 1 lock in share mode; +x y z +1 one 1 +select * from t1 where x = 2 for update; +x y z +2 two 2 +select * from t1 where x = 1 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +rollback; +commit; +begin; +select * from t1 where y = 'one' or y = 'three' order by x lock in share mode; +x y z +1 one 1 +3 three 3 +begin; +select * from t1 where y = 'one' lock in share mode; +x y z +1 one 1 +select * from t1 where x = 1 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +rollback; +commit; +begin; +select * from t1 where z > 1 and z < 3 lock in share mode; +x y z +2 two 2 +begin; +select * from t1 where z = 1 lock in share mode; +x y z +1 one 1 +select * from t1 where x = 1 for update; +x y z +1 one 1 +select * from t1 where x = 2 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +rollback; +commit; +drop table t1; diff --git a/mysql-test/r/ndb_rename.result b/mysql-test/r/ndb_rename.result new file mode 100644 index 00000000000..cacef136311 --- /dev/null +++ b/mysql-test/r/ndb_rename.result @@ -0,0 +1,14 @@ +DROP TABLE IF EXISTS t1,t2; +drop database if exists mysqltest; +CREATE TABLE t1 ( +pk1 INT NOT NULL PRIMARY KEY, +attr1 INT NOT NULL, +attr2 INT, +attr3 VARCHAR(10), +INDEX i1(attr1) +) ENGINE=ndbcluster; +alter table t1 rename t2; +create database ndbtest; +alter table t2 rename ndbtest.t2; +drop table ndbtest.t2; +drop database ndbtest; diff --git a/mysql-test/r/ndb_truncate.result b/mysql-test/r/ndb_truncate.result index 38f3a78029c..811e5e3afeb 100644 --- a/mysql-test/r/ndb_truncate.result +++ b/mysql-test/r/ndb_truncate.result @@ -1,14 +1,23 @@ -DROP TABLE IF EXISTS t2; -CREATE TABLE t2 ( -a bigint unsigned NOT NULL PRIMARY KEY, +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 ( +a bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, b int unsigned not null, c int unsigned ) engine=ndbcluster; -select count(*) from t2; +select count(*) from t1; count(*) 5000 -truncate table t2; -select count(*) from t2; +select * from t1 order by a limit 2; +a b c +1 509 2500 +2 510 7 +truncate table t1; +select count(*) from t1; count(*) 0 -drop table t2; +insert into t1 values(NULL,1,1),(NULL,2,2); +select * from t1 order by a; +a b c +1 1 1 +2 2 2 +drop table t1; diff --git a/mysql-test/r/preload.result b/mysql-test/r/preload.result index b668b07b398..145fd22ffb6 100644 --- a/mysql-test/r/preload.result +++ b/mysql-test/r/preload.result @@ -160,11 +160,11 @@ Key_reads 0 load index into cache t3 key (b), t2 key (c) ; Table Op Msg_type Msg_text test.t3 preload_keys error Table 'test.t3' doesn't exist -test.t2 preload_keys error Key column 'c' doesn't exist in table +test.t2 preload_keys error Key 'c' doesn't exist in table 't2' test.t2 preload_keys status Operation failed Warnings: Error 1146 Table 'test.t3' doesn't exist -Error 1072 Key column 'c' doesn't exist in table +Error 1176 Key 'c' doesn't exist in table 't2' show status like "key_read%"; Variable_name Value Key_read_requests 0 diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index fccaa9b1b66..abebfc8cd93 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1157,3 +1157,4 @@ test.t1 analyze status Table is already up to date Warnings: Error 1146 Table 'test.t4' doesn't exist deallocate prepare stmt; +drop table t1, t2, t3; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 335637b787f..86e1a8ada19 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -144,9 +144,9 @@ explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index explain select fld3 from t2 ignore index (fld3,not_used); -ERROR 42000: Key column 'not_used' doesn't exist in table +ERROR HY000: Key 'not_used' doesn't exist in table 't2' explain select fld3 from t2 use index (not_used); -ERROR 42000: Key column 'not_used' doesn't exist in table +ERROR HY000: Key 'not_used' doesn't exist in table 't2' select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; fld3 honeysuckle @@ -2716,6 +2716,16 @@ select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 fro f1 f2 1 1 drop table t1,t2; +CREATE TABLE t1 (a int, INDEX idx(a)); +INSERT INTO t1 VALUES (2), (3), (1); +EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +EXPLAIN SELECT * FROM t1 IGNORE INDEX (a); +ERROR HY000: Key 'a' doesn't exist in table 't1' +EXPLAIN SELECT * FROM t1 FORCE INDEX (a); +ERROR HY000: Key 'a' doesn't exist in table 't1' +DROP TABLE t1; CREATE TABLE t1 ( city char(30) ); INSERT INTO t1 VALUES ('London'); INSERT INTO t1 VALUES ('Paris'); diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 746cd8f00d4..ff378f1f43b 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -4960,4 +4960,34 @@ aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa DROP FUNCTION bug18589_f1| DROP PROCEDURE bug18589_p1| DROP PROCEDURE bug18589_p2| +DROP FUNCTION IF EXISTS bug18037_f1| +DROP PROCEDURE IF EXISTS bug18037_p1| +DROP PROCEDURE IF EXISTS bug18037_p2| +CREATE FUNCTION bug18037_f1() RETURNS INT +BEGIN +RETURN @@server_id; +END| +CREATE PROCEDURE bug18037_p1() +BEGIN +DECLARE v INT DEFAULT @@server_id; +END| +CREATE PROCEDURE bug18037_p2() +BEGIN +CASE @@server_id +WHEN -1 THEN +SELECT 0; +ELSE +SELECT 1; +END CASE; +END| +SELECT bug18037_f1()| +bug18037_f1() +1 +CALL bug18037_p1()| +CALL bug18037_p2()| +1 +1 +DROP FUNCTION bug18037_f1| +DROP PROCEDURE bug18037_p1| +DROP PROCEDURE bug18037_p2| drop table t1,t2; diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result index bb7297d6807..cd8bf52139e 100644 --- a/mysql-test/r/ssl.result +++ b/mysql-test/r/ssl.result @@ -145,9 +145,9 @@ explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index explain select fld3 from t2 ignore index (fld3,not_used); -ERROR 42000: Key column 'not_used' doesn't exist in table +ERROR HY000: Key 'not_used' doesn't exist in table 't2' explain select fld3 from t2 use index (not_used); -ERROR 42000: Key column 'not_used' doesn't exist in table +ERROR HY000: Key 'not_used' doesn't exist in table 't2' select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; fld3 honeysuckle diff --git a/mysql-test/r/ssl_compress.result b/mysql-test/r/ssl_compress.result index 9c1cf4b0ec3..78e83dabdd5 100644 --- a/mysql-test/r/ssl_compress.result +++ b/mysql-test/r/ssl_compress.result @@ -148,9 +148,9 @@ explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index explain select fld3 from t2 ignore index (fld3,not_used); -ERROR 42000: Key column 'not_used' doesn't exist in table +ERROR HY000: Key 'not_used' doesn't exist in table 't2' explain select fld3 from t2 use index (not_used); -ERROR 42000: Key column 'not_used' doesn't exist in table +ERROR HY000: Key 'not_used' doesn't exist in table 't2' select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; fld3 honeysuckle diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index 2b1a47ed337..271cd7bf486 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -1288,3 +1288,13 @@ ERROR 22001: Data too long for column 'a' at row 1 select * from t1; a drop table t1; +set sql_mode='traditional'; +create table t1 (date date not null); +create table t2 select date from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `date` date NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t2,t1; +set @@sql_mode= @org_mode; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 39cb95be9e1..e4bc59e4d19 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3177,3 +3177,30 @@ ERROR 42S22: Unknown column 'no_such_column' in 'where clause' SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); ERROR 42S22: Unknown column 'no_such_column' in 'IN/ALL/ANY subquery' DROP TABLE t1; +create table t1 (i int, j bigint); +insert into t1 values (1, 2), (2, 2), (3, 2); +select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3; +min(i) +1 +drop table t1; +CREATE TABLE t1 (i BIGINT UNSIGNED); +INSERT INTO t1 VALUES (10000000000000000000); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (i BIGINT UNSIGNED); +INSERT INTO t2 VALUES (10000000000000000000); +INSERT INTO t2 VALUES (1); +/* simple test */ +SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i; +i +10000000000000000000 +1 +/* subquery test */ +SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2); +i +10000000000000000000 +/* subquery test with cast*/ +SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED); +i +10000000000000000000 +DROP TABLE t1; +DROP TABLE t2; diff --git a/mysql-test/r/sysdate_is_now.result b/mysql-test/r/sysdate_is_now.result index 82861436ff6..1ebbb8c1588 100644 --- a/mysql-test/r/sysdate_is_now.result +++ b/mysql-test/r/sysdate_is_now.result @@ -1,4 +1,4 @@ set timestamp=1; SELECT sleep(1),NOW()-SYSDATE() as zero; sleep(1) zero -0 0 +0 0.000000 diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index bcdba3dca76..bd89c09e94d 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -241,7 +241,7 @@ Field Type Collation Null Key Default Extra Privileges Comment auto int(5) unsigned NULL NO 0 # string char(10) latin1_swedish_ci YES newdefault # tiny tinyint(4) NULL NO 0 # -short smallint(6) NULL NO 0 # +short smallint(6) NULL NO # medium mediumint(8) NULL NO 0 # long_int int(11) NULL NO 0 # longlong bigint(13) NULL NO 0 # @@ -259,7 +259,7 @@ date_time datetime NULL YES NULL # new_blob_col varchar(20) latin1_swedish_ci YES NULL # tinyblob_col tinyblob NULL YES NULL # mediumblob_col mediumblob NULL NO # -options enum('one','two','tree') latin1_swedish_ci NO one # +options enum('one','two','tree') latin1_swedish_ci NO # flags set('one','two','tree') latin1_swedish_ci NO # new_field char(10) latin1_swedish_ci NO new # select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.time_field<>t2.time_field and (t1.time_field is not null or t2.time_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.new_blob_col<>t2.new_blob_col and (t1.new_blob_col is not null or t2.new_blob_col is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field is not null))); diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 70282c5143e..8cee60cf49a 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -639,6 +639,35 @@ select @@version, @@version_comment, @@version_compile_machine, @@version_compile_os; @@version @@version_comment @@version_compile_machine @@version_compile_os # # # # +select @@basedir, @@datadir, @@tmpdir; +@@basedir @@datadir @@tmpdir +# # # +show variables like 'basedir'; +Variable_name Value +basedir # +show variables like 'datadir'; +Variable_name Value +datadir # +show variables like 'tmpdir'; +Variable_name Value +tmpdir # +select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_key; +@@ssl_ca @@ssl_capath @@ssl_cert @@ssl_cipher @@ssl_key +# # # # # +show variables like 'ssl%'; +Variable_name Value +ssl_ca # +ssl_capath # +ssl_cert # +ssl_cipher # +ssl_key # +select @@log_queries_not_using_indexes; +@@log_queries_not_using_indexes +0 +show variables like 'log_queries_not_using_indexes'; +Variable_name Value +log_queries_not_using_indexes OFF +End of 5.0 tests set global binlog_cache_size =@my_binlog_cache_size; set global connect_timeout =@my_connect_timeout; set global delayed_insert_timeout =@my_delayed_insert_timeout; @@ -666,4 +695,3 @@ set global server_id =@my_server_id; set global slow_launch_time =@my_slow_launch_time; set global storage_engine =@my_storage_engine; set global thread_cache_size =@my_thread_cache_size; -End of 5.0 tests diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 4baa56070b7..5bb407f4256 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -614,7 +614,7 @@ drop table t1; create table t1 (a int, b int); create view v1 as select a, sum(b) from t1 group by a; select b from v1 use index (some_index) where b=1; -ERROR 42000: Key column 'some_index' doesn't exist in table +ERROR HY000: Key 'some_index' doesn't exist in table 'v1' drop view v1; drop table t1; create table t1 (col1 char(5),col2 char(5)); @@ -2660,3 +2660,79 @@ SELECT * FROM v1; id t COUNT(*) DROP VIEW v1; DROP TABLE t1; +CREATE TABLE t1 (i INT, j BIGINT); +INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2); +CREATE VIEW v1 AS SELECT MIN(j) AS j FROM t1; +CREATE VIEW v2 AS SELECT MIN(i) FROM t1 WHERE j = ( SELECT * FROM v1 ); +SELECT * FROM v2; +MIN(i) +1 +DROP VIEW v2, v1; +DROP TABLE t1; +CREATE TABLE t1( +fName varchar(25) NOT NULL, +lName varchar(25) NOT NULL, +DOB date NOT NULL, +uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY); +INSERT INTO t1(fName, lName, DOB) VALUES +('Hank', 'Hill', '1964-09-29'), +('Tom', 'Adams', '1908-02-14'), +('Homer', 'Simpson', '1968-03-05'); +CREATE VIEW v1 AS +SELECT (year(now())-year(DOB)) AS Age +FROM t1 HAVING Age < 75; +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache (year(now()) - year(`t1`.`DOB`)) AS `Age` from `t1` having (`Age` < 75) +SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75; +Age +42 +38 +SELECT * FROM v1; +Age +42 +38 +DROP VIEW v1; +DROP TABLE t1; +CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a char(6) DEFAULT 'xxx'); +INSERT INTO t1(id) VALUES (1), (2), (3), (4); +INSERT INTO t1 VALUES (5,'yyy'), (6,'yyy'); +SELECT * FROM t1; +id a +1 xxx +2 xxx +3 xxx +4 xxx +5 yyy +6 yyy +CREATE VIEW v1(a, m) AS SELECT a, MIN(id) FROM t1 GROUP BY a; +SELECT * FROM v1; +a m +xxx 1 +yyy 5 +CREATE TABLE t2 SELECT * FROM v1; +INSERT INTO t2(m) VALUES (0); +SELECT * FROM t2; +a m +xxx 1 +yyy 5 +xxx 0 +DROP VIEW v1; +DROP TABLE t1,t2; +CREATE TABLE t1 (id int PRIMARY KEY, e ENUM('a','b') NOT NULL DEFAULT 'b'); +INSERT INTO t1(id) VALUES (1), (2), (3); +INSERT INTO t1 VALUES (4,'a'); +SELECT * FROM t1; +id e +1 b +2 b +3 b +4 a +CREATE VIEW v1(m, e) AS SELECT MIN(id), e FROM t1 GROUP BY e; +CREATE TABLE t2 SELECT * FROM v1; +SELECT * FROM t2; +m e +4 a +1 b +DROP VIEW v1; +DROP TABLE IF EXISTS t1,t2; diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 3feffb4a510..a8619201810 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -533,3 +533,117 @@ View Create View v2 CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select 1 AS `1` drop view v1; drop view v2; +CREATE DATABASE mysqltest1; +CREATE USER readonly@localhost; +CREATE TABLE mysqltest1.t1 (x INT); +INSERT INTO mysqltest1.t1 VALUES (1), (2); +CREATE SQL SECURITY INVOKER VIEW mysqltest1.v_t1 AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ts AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ti AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tu AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tus AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_td AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tds AS SELECT * FROM mysqltest1.t1; +GRANT SELECT, INSERT, UPDATE, DELETE ON mysqltest1.v_t1 TO readonly; +GRANT SELECT ON mysqltest1.v_ts TO readonly; +GRANT INSERT ON mysqltest1.v_ti TO readonly; +GRANT UPDATE ON mysqltest1.v_tu TO readonly; +GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly; +GRANT DELETE ON mysqltest1.v_td TO readonly; +GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly; +SELECT * FROM mysqltest1.v_t1; +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +INSERT INTO mysqltest1.v_t1 VALUES(4); +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +DELETE FROM mysqltest1.v_t1 WHERE x = 1; +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +UPDATE mysqltest1.v_t1 SET x = 3 WHERE x = 2; +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +UPDATE mysqltest1.v_t1 SET x = 3; +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +DELETE FROM mysqltest1.v_t1; +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SELECT 1 FROM mysqltest1.v_t1; +ERROR HY000: View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SELECT * FROM mysqltest1.t1; +ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 't1' +SELECT * FROM mysqltest1.v_ts; +x +1 +2 +SELECT * FROM mysqltest1.v_ts, mysqltest1.t1 WHERE mysqltest1.t1.x = mysqltest1.v_ts.x; +ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 't1' +SELECT * FROM mysqltest1.v_ti; +ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for table 'v_ti' +INSERT INTO mysqltest1.v_ts VALUES (100); +ERROR 42000: INSERT command denied to user 'readonly'@'localhost' for table 'v_ts' +INSERT INTO mysqltest1.v_ti VALUES (100); +UPDATE mysqltest1.v_ts SET x= 200 WHERE x = 100; +ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table 'v_ts' +UPDATE mysqltest1.v_ts SET x= 200; +ERROR 42000: UPDATE command denied to user 'readonly'@'localhost' for table 'v_ts' +UPDATE mysqltest1.v_tu SET x= 200 WHERE x = 100; +UPDATE mysqltest1.v_tus SET x= 200 WHERE x = 100; +UPDATE mysqltest1.v_tu SET x= 200; +DELETE FROM mysqltest1.v_ts WHERE x= 200; +ERROR 42000: DELETE command denied to user 'readonly'@'localhost' for table 'v_ts' +DELETE FROM mysqltest1.v_ts; +ERROR 42000: DELETE command denied to user 'readonly'@'localhost' for table 'v_ts' +DELETE FROM mysqltest1.v_td WHERE x= 200; +ERROR 42000: SELECT command denied to user 'readonly'@'localhost' for column 'x' in table 'v_td' +DELETE FROM mysqltest1.v_tds WHERE x= 200; +DELETE FROM mysqltest1.v_td; +DROP VIEW mysqltest1.v_tds; +DROP VIEW mysqltest1.v_td; +DROP VIEW mysqltest1.v_tus; +DROP VIEW mysqltest1.v_tu; +DROP VIEW mysqltest1.v_ti; +DROP VIEW mysqltest1.v_ts; +DROP VIEW mysqltest1.v_t1; +DROP TABLE mysqltest1.t1; +DROP USER readonly@localhost; +DROP DATABASE mysqltest1; +CREATE TABLE t1 (a INT PRIMARY KEY); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE DEFINER = 'no-such-user'@localhost VIEW v AS SELECT a from t1; +Warnings: +Note 1449 There is no 'no-such-user'@'localhost' registered +SHOW CREATE VIEW v; +View Create View +v CREATE ALGORITHM=UNDEFINED DEFINER=`no-such-user`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `t1`.`a` AS `a` from `t1` +Warnings: +Note 1449 There is no 'no-such-user'@'localhost' registered +SELECT * FROM v; +ERROR HY000: There is no 'no-such-user'@'localhost' registered +DROP VIEW v; +DROP TABLE t1; +USE test; +CREATE USER mysqltest_db1@localhost identified by 'PWD'; +GRANT ALL ON mysqltest_db1.* TO mysqltest_db1@localhost WITH GRANT OPTION; +CREATE SCHEMA mysqltest_db1 ; +USE mysqltest_db1 ; +CREATE TABLE t1 (f1 INTEGER); +CREATE VIEW view1 AS +SELECT * FROM t1; +SHOW CREATE VIEW view1; +View Create View +view1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view1` AS select `t1`.`f1` AS `f1` from `t1` +CREATE VIEW view2 AS +SELECT * FROM view1; +# Here comes a suspicious warning +SHOW CREATE VIEW view2; +View Create View +view2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view2` AS select `view1`.`f1` AS `f1` from `view1` +# But the view view2 is usable +SELECT * FROM view2; +f1 +CREATE VIEW view3 AS +SELECT * FROM view2; +SELECT * from view3; +f1 +DROP VIEW mysqltest_db1.view3; +DROP VIEW mysqltest_db1.view2; +DROP VIEW mysqltest_db1.view1; +DROP TABLE mysqltest_db1.t1; +DROP SCHEMA mysqltest_db1; +DROP USER mysqltest_db1@localhost; diff --git a/mysql-test/std_data/bug15328.cnf b/mysql-test/std_data/bug15328.cnf new file mode 100644 index 00000000000..e23d33bfa54 --- /dev/null +++ b/mysql-test/std_data/bug15328.cnf @@ -0,0 +1,2 @@ +[mysqldump] +fields-optionally-enclosed-by=" diff --git a/mysql-test/t/analyze.test b/mysql-test/t/analyze.test index 1801a4a440f..7c9830bb468 100644 --- a/mysql-test/t/analyze.test +++ b/mysql-test/t/analyze.test @@ -61,6 +61,7 @@ prepare stmt1 from "SELECT * FROM t1 PROCEDURE ANALYSE()"; execute stmt1; execute stmt1; deallocate prepare stmt1; +drop table t1; # # bug#15225 (ANALYZE temporary has no effect) diff --git a/mysql-test/t/create_not_windows.test b/mysql-test/t/create_not_windows.test new file mode 100644 index 00000000000..71ad9ccd7fe --- /dev/null +++ b/mysql-test/t/create_not_windows.test @@ -0,0 +1,20 @@ +# Non-windows specific create tests. + +--source include/not_windows.inc + +# +# Bug#19479:mysqldump creates invalid dump +# +--disable_warnings +drop table if exists `about:text`; +--enable_warnings +create table `about:text` ( +_id int not null auto_increment, +`about:text` varchar(255) not null default '', +primary key (_id) +); + +show create table `about:text`; +drop table `about:text`; + +# End of 5.0 tests diff --git a/mysql-test/t/ctype_sjis.test b/mysql-test/t/ctype_sjis.test index 1d807b5e9a8..01e0b334554 100644 --- a/mysql-test/t/ctype_sjis.test +++ b/mysql-test/t/ctype_sjis.test @@ -78,6 +78,6 @@ SET collation_connection='sjis_bin'; --character_set sjis SET NAMES sjis; -SELECT HEX('²“‘@\Œ\') FROM DUAL; +SELECT HEX('²“‘@Œ\') FROM DUAL; # End of 4.1 tests diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index a38771db233..efce0cdf3b5 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -15,9 +15,9 @@ explain select * from t1 ignore key (str) where str="foo"; explain select * from t1 use key (str,str) where str="foo"; #The following should give errors ---error 1072 +--error 1176 explain select * from t1 use key (str,str,foo) where str="foo"; ---error 1072 +--error 1176 explain select * from t1 ignore key (str,str,foo) where str="foo"; drop table t1; diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test index f5fd9fcadf2..95ba633fefd 100644 --- a/mysql-test/t/flush.test +++ b/mysql-test/t/flush.test @@ -102,3 +102,43 @@ unlock tables; drop table t1, t2, t3; # End of 4.1 tests + +# +# Test of deadlock problem when doing FLUSH TABLE with read lock +# (Bug was in NTPL threads in Linux when using different mutex while +# waiting for a condtion variable) + +create table t1 (c1 int); +create table t2 (c1 int); + +connect (con1,localhost,root,,); +connect (con3,localhost,root,,); + +connection con1; +lock table t1 write; + +connection con2; +send flush tables with read lock; +--sleep 1 + +connection con3; +send insert into t2 values(1); +--sleep 1 + +connection con1; +unlock tables; +disconnect con1; + +connection con2; +reap; +disconnect con2; + +connection con3; +# It hangs here (insert into t2 does not end). +reap; +disconnect con3; + +connection default; +drop table t1, t2; + +# End of 5.0 tests diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index c667f90940c..fb9470c16dd 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -539,77 +539,6 @@ 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 -# - ---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; - # End of 4.1 tests # diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 351d1fc2c92..8ddf1fbe314 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -220,3 +220,15 @@ set @str=NULL; drop table t2; drop table t1; +# BUG#19618: Crash in range optimizer for +# "unsigned_keypart NOT IN(negative_number,...)" +# (introduced in fix BUG#15872) +create table t1 ( + some_id smallint(5) unsigned, + key (some_id) +); +insert into t1 values (1),(2); +select some_id from t1 where some_id not in(2,-1); +select some_id from t1 where some_id not in(-4,-1,-4); +select some_id from t1 where some_id not in(-4,-1,3423534,2342342); +drop table t1; diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index dee06231deb..7f809dbc4a1 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -698,4 +698,10 @@ select cast(rtrim(' 20.06 ') as decimal(19,2)); select cast(ltrim(' 20.06 ') as decimal(19,2)); select cast(rtrim(ltrim(' 20.06 ')) as decimal(19,2)); +# +# Bug #13975: "same string" + 0 has 2 different results +# +select conv("18383815659218730760",10,10) + 0; +select "18383815659218730760" + 0; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 1c7f387e354..dc2e74c9365 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -367,6 +367,13 @@ select last_day('2005-01-00'); select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m')); +# +# Bug #16546 +# + +select now() - now() + 0, curtime() - curtime() + 0, + sec_to_time(1) + 0, from_unixtime(1) + 0; + # End of 4.1 tests explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1, diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index 97f13381557..a9d52f559ca 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -302,7 +302,7 @@ DROP DATABASE testdb10; create table t1(a int, b int, c int, d int); grant insert(b), insert(c), insert(d), insert(a) on t1 to grant_user@localhost; show grants for grant_user@localhost; -select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv; +select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name; revoke ALL PRIVILEGES on t1 from grant_user@localhost; show grants for grant_user@localhost; select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv; @@ -326,7 +326,18 @@ grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost; grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost; connect (conn1,localhost,mysqltest_3,,); connection conn1; -show grants for mysqltest_3@localhost; +SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES + WHERE GRANTEE = '''mysqltest_3''@''localhost''' + ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE; +SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES + WHERE GRANTEE = '''mysqltest_3''@''localhost''' + ORDER BY TABLE_NAME,PRIVILEGE_TYPE; +SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES + WHERE GRANTEE = '''mysqltest_3''@''localhost''' + ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE; +SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES + WHERE GRANTEE = '''mysqltest_3''@''localhost''' + ORDER BY TABLE_CATALOG,PRIVILEGE_TYPE; --error 1143 update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1; --error 1143 @@ -479,6 +490,7 @@ insert into tables_priv values ('','test_db','mysqltest_1','test_table','test_gr flush privileges; delete from tables_priv where host = '' and user = 'mysqltest_1'; flush privileges; +use test; # # Bug #10892 user variables not auto cast for comparisons @@ -507,7 +519,6 @@ create user mysqltest_7@; set password for mysqltest_7@ = password('systpass'); show grants for mysqltest_7@; drop user mysqltest_7@; -flush privileges; # BUG#16297(flush should be removed when that bug is fixed) --error 1141 show grants for mysqltest_7@; @@ -528,3 +539,145 @@ flush privileges; drop database mysqltest; # End of 4.1 tests + +# +# Bug #16297 In memory grant tables not flushed when users's hostname is "" +# +use test; +create table t1 (a int); + +# Backup anonymous users and remove them. (They get in the way of +# the one we test with here otherwise.) +create table t2 as select * from mysql.user where user=''; +delete from mysql.user where user=''; +flush privileges; + +# Create some users with different hostnames +create user mysqltest_8@''; +create user mysqltest_8; +create user mysqltest_8@host8; + +# Try to create them again +--error 1396 +create user mysqltest_8@''; +--error 1396 +create user mysqltest_8; +--error 1396 +create user mysqltest_8@host8; + +select user, QUOTE(host) from mysql.user where user="mysqltest_8"; + +--echo Schema privileges +grant select on mysqltest.* to mysqltest_8@''; +show grants for mysqltest_8@''; +grant select on mysqltest.* to mysqltest_8@; +show grants for mysqltest_8@; +grant select on mysqltest.* to mysqltest_8; +show grants for mysqltest_8; +select * from information_schema.schema_privileges +where grantee like "'mysqltest_8'%"; +connect (conn3,localhost,mysqltest_8,,); +select * from t1; +disconnect conn3; +connection master; +revoke select on mysqltest.* from mysqltest_8@''; +revoke select on mysqltest.* from mysqltest_8; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +select * from information_schema.schema_privileges +where grantee like "'mysqltest_8'%"; +flush privileges; +show grants for mysqltest_8@''; +show grants for mysqltest_8@; +grant select on mysqltest.* to mysqltest_8@''; +flush privileges; +show grants for mysqltest_8@; +revoke select on mysqltest.* from mysqltest_8@''; +flush privileges; + +--echo Column privileges +grant update (a) on t1 to mysqltest_8@''; +grant update (a) on t1 to mysqltest_8; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +flush privileges; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +select * from information_schema.column_privileges; +connect (conn4,localhost,mysqltest_8,,); +select * from t1; +disconnect conn4; +connection master; +revoke update (a) on t1 from mysqltest_8@''; +revoke update (a) on t1 from mysqltest_8; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +select * from information_schema.column_privileges; +flush privileges; +show grants for mysqltest_8@''; +show grants for mysqltest_8; + +--echo Table privileges +grant update on t1 to mysqltest_8@''; +grant update on t1 to mysqltest_8; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +flush privileges; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +select * from information_schema.table_privileges; +connect (conn5,localhost,mysqltest_8,,); +select * from t1; +disconnect conn5; +connection master; +revoke update on t1 from mysqltest_8@''; +revoke update on t1 from mysqltest_8; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +select * from information_schema.table_privileges; +flush privileges; +show grants for mysqltest_8@''; +show grants for mysqltest_8; + +--echo "DROP USER" should clear privileges +grant all privileges on mysqltest.* to mysqltest_8@''; +grant select on mysqltest.* to mysqltest_8@''; +grant update on t1 to mysqltest_8@''; +grant update (a) on t1 to mysqltest_8@''; +grant all privileges on mysqltest.* to mysqltest_8; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +select * from information_schema.user_privileges +where grantee like "'mysqltest_8'%"; +connect (conn5,localhost,mysqltest_8,,); +select * from t1; +disconnect conn5; +connection master; +flush privileges; +show grants for mysqltest_8@''; +show grants for mysqltest_8; +drop user mysqltest_8@''; +--error 1141 +show grants for mysqltest_8@''; +show grants for mysqltest_8; +select * from information_schema.user_privileges +where grantee like "'mysqltest_8'%"; +drop user mysqltest_8; +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error 1045 +connect (conn6,localhost,mysqltest_8,,); +connection master; +--error 1141 +show grants for mysqltest_8; +drop user mysqltest_8@host8; +--error 1141 +show grants for mysqltest_8@host8; + +# Restore the anonymous users. +insert into mysql.user select * from t2; +flush privileges; +drop table t2; + +drop table t1; + + diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index d9836ccc8df..874f3cd1a80 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -659,59 +659,7 @@ 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 -# - ---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, dummy) select * from t1; - -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 t1; - -select distinct a1 from t4 where pk_col not in (1,2,3,4); - -drop table t1,t2,t3,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; - +drop table t1,t2,t3; # # Bug #14920 Ordering aggregated result sets with composite primary keys # corrupts resultset diff --git a/mysql-test/t/heap.test b/mysql-test/t/heap.test index 82294db336d..e501fce1eeb 100644 --- a/mysql-test/t/heap.test +++ b/mysql-test/t/heap.test @@ -458,4 +458,16 @@ create table t2 (c varchar(10)) engine=memory; show table status like 't_'; drop table t1, t2; +# +# BUG#18233 - Memory tables INDEX USING HASH (a,b) returns 1 row on +# SELECT WHERE a= AND b= +# +CREATE TABLE t1(a VARCHAR(1), b VARCHAR(2), c VARCHAR(256), + KEY(a), KEY(b), KEY(c)) ENGINE=MEMORY; +INSERT INTO t1 VALUES('a','aa',REPEAT('a', 256)),('a','aa',REPEAT('a',256)); +SELECT COUNT(*) FROM t1 WHERE a='a'; +SELECT COUNT(*) FROM t1 WHERE b='aa'; +SELECT COUNT(*) FROM t1 WHERE c=REPEAT('a',256); +DROP TABLE t1; + # End of 5.0 tests diff --git a/mysql-test/t/heap_btree.test b/mysql-test/t/heap_btree.test index f1b9d290885..fb715fccefe 100644 --- a/mysql-test/t/heap_btree.test +++ b/mysql-test/t/heap_btree.test @@ -176,4 +176,12 @@ UPDATE t1 SET val=1; SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='t1'; DROP TABLE t1; +# +# BUG#12873 - BTREE index on MEMORY table with multiple NULL values doesn't +# work properly +# +CREATE TABLE t1 (a INT, UNIQUE USING BTREE(a)) ENGINE=MEMORY; +INSERT INTO t1 VALUES(NULL),(NULL); +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index e96f1ef4bbd..0bcd9ef8c0b 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1,9 +1,6 @@ # This test uses grants, which can't get tested for embedded server -- source include/not_embedded.inc -# This test uses chmod, can't be run with root permissions --- source include/not_as_root.inc - # Test for information_schema.schemata & # show databases @@ -579,28 +576,32 @@ connect (con2,localhost,user2,,mysqltest); connect (con3,localhost,user3,,mysqltest); connect (con4,localhost,user4,,); connection con1; -select * from information_schema.column_privileges; -select * from information_schema.table_privileges; -select * from information_schema.schema_privileges; -select * from information_schema.user_privileges; +select * from information_schema.column_privileges order by grantee; +select * from information_schema.table_privileges order by grantee; +select * from information_schema.schema_privileges order by grantee; +select * from information_schema.user_privileges order by grantee; show grants; connection con2; -select * from information_schema.column_privileges; -select * from information_schema.table_privileges; -select * from information_schema.schema_privileges; -select * from information_schema.user_privileges; +select * from information_schema.column_privileges order by grantee; +select * from information_schema.table_privileges order by grantee; +select * from information_schema.schema_privileges order by grantee; +select * from information_schema.user_privileges order by grantee; show grants; connection con3; -select * from information_schema.column_privileges; -select * from information_schema.table_privileges; -select * from information_schema.schema_privileges; -select * from information_schema.user_privileges; +select * from information_schema.column_privileges order by grantee; +select * from information_schema.table_privileges order by grantee; +select * from information_schema.schema_privileges order by grantee; +select * from information_schema.user_privileges order by grantee; show grants; connection con4; -select * from information_schema.column_privileges where grantee like '%user%'; -select * from information_schema.table_privileges where grantee like '%user%'; -select * from information_schema.schema_privileges where grantee like '%user%'; -select * from information_schema.user_privileges where grantee like '%user%'; +select * from information_schema.column_privileges where grantee like '%user%' +order by grantee; +select * from information_schema.table_privileges where grantee like '%user%' +order by grantee; +select * from information_schema.schema_privileges where grantee like '%user%' +order by grantee; +select * from information_schema.user_privileges where grantee like '%user%' +order by grantee; show grants; connection default; drop user user1@localhost, user2@localhost, user3@localhost, user4@localhost; @@ -822,3 +823,32 @@ INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL', select routine_name from information_schema.routines; delete from proc where name=''; use test; + +# +# Bug#16681 information_schema shows forbidden VIEW details +# +grant select on test.* to mysqltest_1@localhost; +create table t1 (id int); +create view v1 as select * from t1; +create definer = mysqltest_1@localhost +sql security definer view v2 as select 1; + +connect (con16681,localhost,mysqltest_1,,test); +connection con16681; + +select * from information_schema.views +where table_name='v1' or table_name='v2'; +connection default; +drop view v1, v2; +drop table t1; +drop user mysqltest_1@localhost; + +# +# Bug#19599 duplication of information_schema column value in a CONCAT expr with user var +# +set @a:= '.'; +create table t1(f1 char(5)); +create table t2(f1 char(5)); +select concat(@a, table_name), @a, table_name +from information_schema.tables where table_schema = 'test'; +drop table t1,t2; diff --git a/mysql-test/t/information_schema_chmod.test b/mysql-test/t/information_schema_chmod.test index fb850b8e38d..c7ea2b03890 100644 --- a/mysql-test/t/information_schema_chmod.test +++ b/mysql-test/t/information_schema_chmod.test @@ -8,6 +8,9 @@ # --source include/not_windows.inc +# This test uses chmod, can't be run with root permissions +-- source include/not_as_root.inc + # # Bug #15851 Unlistable directories yield no info from information_schema diff --git a/mysql-test/t/information_schema_db.test b/mysql-test/t/information_schema_db.test index b65135a621d..2cfa766d799 100644 --- a/mysql-test/t/information_schema_db.test +++ b/mysql-test/t/information_schema_db.test @@ -1,16 +1,25 @@ -- source include/testdb_only.inc +--disable_warnings +drop table if exists t1,t2; +drop view if exists v1,v2; +drop function if exists f1; +drop function if exists f2; +--enable_warnings + use INFORMATION_SCHEMA; --replace_result Tables_in_INFORMATION_SCHEMA Tables_in_information_schema show tables; --replace_result 'Tables_in_INFORMATION_SCHEMA (T%)' 'Tables_in_information_schema (T%)' show tables from INFORMATION_SCHEMA like 'T%'; create database `inf%`; +create database mbase; use `inf%`; show tables; # # Bug#18113 SELECT * FROM information_schema.xxx crashes server +# Bug#17204 second CALL to procedure crashes Server # Crash happened when one selected data from one of INFORMATION_SCHEMA # tables and in order to build its contents server had to open view which # used stored function and table or view on which one had not global or @@ -18,6 +27,7 @@ show tables; # privileges at all). # grant all privileges on `inf%`.* to 'mysqltest_1'@'localhost'; +grant all privileges on `mbase`.* to 'mysqltest_1'@'localhost'; create table t1 (f1 int); delimiter |; create function func1(curr_int int) returns int @@ -28,15 +38,63 @@ begin end| delimiter ;| create view v1 as select f1 from t1 where f1 = func1(f1); +create function func2() returns int return 1; + +use mbase; +delimiter |; +create procedure p1 () +begin +select table_name from information_schema.key_column_usage +order by table_name; +end| +delimiter ;| + +create table t1 +(f1 int(10) unsigned not null, + f2 varchar(100) not null, + primary key (f1), unique key (f2)); + connect (user1,localhost,mysqltest_1,,); connection user1; --disable_result_log select * from information_schema.tables; +call mbase.p1(); +call mbase.p1(); +call mbase.p1(); --enable_result_log + connection default; +use `inf%`; drop user mysqltest_1@localhost; +drop table t1; +select table_name, table_type, table_comment from information_schema.tables +where table_schema='inf%' and func2(); +select table_name, table_type, table_comment from information_schema.tables +where table_schema='inf%' and func2(); drop view v1; drop function func1; -drop table t1; +drop function func2; drop database `inf%`; +drop procedure mbase.p1; +drop database mbase; + +# +# Bug#18282 INFORMATION_SCHEMA.TABLES provides inconsistent info about invalid views +# +use test; +create table t1 (i int); +create function f1 () returns int return (select max(i) from t1); +create view v1 as select f1(); +create table t2 (id int); +create function f2 () returns int return (select max(i) from t2); +create view v2 as select f2(); +drop table t2; +select table_name, table_type, table_comment from information_schema.tables +where table_schema='test'; +drop table t1; +select table_name, table_type, table_comment from information_schema.tables +where table_schema='test'; +drop function f1; +drop function f2; +drop view v1, v2; diff --git a/mysql-test/t/init_connect.test b/mysql-test/t/init_connect.test index 2e3c67a7d38..0ee6387d985 100644 --- a/mysql-test/t/init_connect.test +++ b/mysql-test/t/init_connect.test @@ -2,6 +2,8 @@ # Test of init_connect variable # +# should work with embedded server after mysqltest is fixed +-- source include/not_embedded.inc connect (con0,localhost,root,,); connection con0; select hex(@a); diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index f31e4d64789..c7d2e2c0acd 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -57,3 +57,169 @@ where c.c_id = 218 and expiredate is null; drop table t1, t2; + +# +# 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; + +# +# Bug #12672: primary key implcitly included in every innodb index +# (was part of group_min_max.test) +# + +create table t1 ( + a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' +); + +insert into t1 (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'); +--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, dummy) select * from t1; + +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 t1; + +select distinct a1 from t4 where pk_col not in (1,2,3,4); + +drop table t1,t4; + +# +# Bug #6142: a problem with the empty innodb table +# (was part of group_min_max.test) +# + +--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 +# (was part of group_min_max.test) +# + +--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. +# (was part of group_min_max.test) +# +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/join_outer.test b/mysql-test/t/join_outer.test index 7134137a430..dc4e240750c 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -805,3 +805,21 @@ SELECT v1.a, v2. b DROP VIEW v1,v2; DROP TABLE t1,t2; + +# +# Bug 19816: LEFT OUTER JOIN with constant ORed predicates in WHERE clause +# + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); +INSERT INTO t1 VALUES (1), (2), (3), (4); +INSERT INTO t2 VALUES (2), (3); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1); +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1); +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2); +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0); + +DROP TABLE t1,t2; diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 0d2266fc2ae..ee03088b8c3 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -107,3 +107,68 @@ show columns from t1; connection locker; unlock tables; drop table t1; + +# +# Bug#19815 - CREATE/RENAME/DROP DATABASE can deadlock on a global read lock +# +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +# +connection con1; +CREATE DATABASE mysqltest_1; +FLUSH TABLES WITH READ LOCK; +# +# With bug in place: acquire LOCK_mysql_create_table and +# wait in wait_if_global_read_lock(). +connection con2; +send DROP DATABASE mysqltest_1; +--sleep 1 +# +# With bug in place: try to acquire LOCK_mysql_create_table... +# When fixed: Reject dropping db because of the read lock. +connection con1; +--error ER_CANT_UPDATE_WITH_READLOCK +DROP DATABASE mysqltest_1; +UNLOCK TABLES; +# +connection con2; +reap; +# +connection default; +disconnect con1; +disconnect con2; +# This must have been dropped by connection 2 already, +# which waited until the global read lock was released. +--error ER_DB_DROP_EXISTS +DROP DATABASE mysqltest_1; + +# Bug#16986 - Deadlock condition with MyISAM tables +# +connection locker; +use mysql; +LOCK TABLES columns_priv WRITE, db WRITE, host WRITE, user WRITE; +FLUSH TABLES; +--sleep 1 +# +connection reader; +use mysql; +#NOTE: This must be a multi-table select, otherwise the deadlock will not occur +send SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1; +--sleep 1 +# +connection locker; +# Make test case independent from earlier grants. +--replace_result "Table is already up to date" "OK" +OPTIMIZE TABLES columns_priv, db, host, user; +UNLOCK TABLES; +# +connection reader; +reap; +use test; +# +connection locker; +use test; +# +connection default; + +# End of 5.0 tests diff --git a/mysql-test/t/lowercase_table2.test b/mysql-test/t/lowercase_table2.test index c02ae8f5073..521df01cc9b 100644 --- a/mysql-test/t/lowercase_table2.test +++ b/mysql-test/t/lowercase_table2.test @@ -139,3 +139,14 @@ select t1Aa.col1 from t1aA,t2Aa where t1Aa.col1 = t2aA.col1; drop table t2aA, t1Aa; # End of 4.1 tests + +# +# Bug#17661 information_schema.SCHEMATA returns uppercase with lower_case_table_names = 1 +# +create database mysqltest_LC2; +use mysqltest_LC2; +create table myUC (i int); +select TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES +where TABLE_SCHEMA ='mysqltest_LC2'; +use test; +drop database mysqltest_LC2; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 7ea14a811ed..400279a826b 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -390,4 +390,13 @@ insert into t1 values ("Monty"),("WAX"),("Walrus"); alter table t1 engine=MERGE; drop table t1; +# +# BUG#19648 - Merge table does not work with bit types +# +create table t1 (b bit(1)); +create table t2 (b bit(1)); +create table tm (b bit(1)) engine = merge union = (t1,t2); +select * from tm; +drop table tm, t1, t2; + # End of 5.0 tests diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index d74bb1c3a80..c2cd15c3f0f 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -123,21 +123,18 @@ select HEX(f) from t04; select HEX(f) from t4; # -# BUG#14157: utf8 encoding in binlog without set character_set_client -# -# BUG: -# This test only works on the MySQL-internal rpl machines. -# Needs to be fixed. Problem is that koi8r is not installed -# on many machines. +#BUG#14157: utf8 encoding in binlog without set character_set_client # flush logs; -# --exec $MYSQL --character-sets-dir=../sql/share/charsets/ --default-character-set=koi8r test -e 'create table if not exists t5 (a int); set names koi8r; create temporary table `ÑÝÉË` (a int); insert into `ÑÝÉË` values (1); insert into t5 select * from `ÑÝÉË`' +--exec $MYSQL test -e 'create table if not exists t5 (a int); set names latin1; create temporary table `äöüÄÖÜ` (a int); insert into `äöüÄÖÜ` values (1); insert into t5 select * from `äöüÄÖÜ`' -# resulted log is client charset insensitive (latin1 not koi8r) as it must be -# --exec $MYSQL_BINLOG --short-form $MYSQL_TEST_DIR/var/log/master-bin.000006 | $MYSQL --default-character-set=latin1 -#select * from t5 /* must be (1),(1) */; +# resulted binlog, parly consisting of multi-byte utf8 chars, +# must be digestable for both client and server. In 4.1 the client +# should use default-character-set same as the server. +--exec $MYSQL_BINLOG --short-form $MYSQLTEST_VARDIR/log/master-bin.000006 | $MYSQL +select * from t5 /* must be (1),(1) */; # clean up -drop table t1, t2, t03, t04, t3, t4; +drop table t1, t2, t03, t04, t3, t4, t5; # End of 5.0 tests diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 4113c136e17..9fb8b572f76 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -2,7 +2,7 @@ --source include/not_embedded.inc --disable_warnings -DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa; +DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa, t3; drop database if exists mysqldump_test_db; drop database if exists db1; drop database if exists db2; @@ -607,6 +607,13 @@ drop table t1, t2; drop database db1; # +# BUG#15328 Segmentation fault occured if my.cnf is invalid for escape sequence +# + +--exec $MYSQL_MY_PRINT_DEFAULTS --defaults-extra-file=$MYSQL_TEST_DIR/std_data/bug15328.cnf mysqldump + + +# # Bug #9558 mysqldump --no-data db t1 t2 format still dumps data # @@ -1065,7 +1072,18 @@ insert into t1 values ('',''); --exec $MYSQL_DUMP --skip-comments --hex-blob test t1 drop table t1; -# End of 4.1 tests +# +# Bug #18536: wrong table order +# + +create table t1(a int); +create table t2(a int); +create table t3(a int); +--error 6 +--exec $MYSQL_DUMP --skip-comments --force --no-data test t3 t1 non_existing t2 +drop table t1, t2, t3; + +--echo End of 4.1 tests # # Bug 14871 Invalid view dump output diff --git a/mysql-test/t/ndb_alter_table.test b/mysql-test/t/ndb_alter_table.test index 0a0211c8c83..957b95c6fd9 100644 --- a/mysql-test/t/ndb_alter_table.test +++ b/mysql-test/t/ndb_alter_table.test @@ -3,7 +3,7 @@ -- source include/not_embedded.inc --disable_warnings -DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t1, t2; drop database if exists mysqltest; --enable_warnings @@ -326,5 +326,20 @@ on t1 (c010, c011, c012, c013); drop table t1; +# simple test that auto incr is not lost at rename or alter +create table t1 (a int primary key auto_increment, b int) engine=ndb; +insert into t1 (b) values (101),(102),(103); +select * from t1 where a = 3; +alter table t1 rename t2; +insert into t2 (b) values (201),(202),(203); +select * from t2 where a = 6; +alter table t2 add c int; +insert into t2 (b) values (301),(302),(303); +select * from t2 where a = 9; +alter table t2 rename t1; +insert into t1 (b) values (401),(402),(403); +select * from t1 where a = 12; +drop table t1; + # End of 4.1 tests diff --git a/mysql-test/t/ndb_blob.test b/mysql-test/t/ndb_blob.test index bf82a793049..d6e0edc89f0 100644 --- a/mysql-test/t/ndb_blob.test +++ b/mysql-test/t/ndb_blob.test @@ -428,4 +428,60 @@ truncate t1; select count(*) from t1; drop table t1; +# -- bug#19956 - var* key, complex key + +create table t1 ( + a varchar(40) not null, + b mediumint not null, + t text, + c varchar(2) not null, + d bigint not null, + primary key (a,b,c), + key (c,a), + unique key (d) +) engine=ndb; + +--disable_query_log +set @s1 = 'rggurloniukyehuxdbfkkyzlceixzrehqhvxvxbpwizzvjzpucqmzrhzxzfau'; +set @s2 = 'ykyymbzqgqlcjhlhmyqelfoaaohvtbekvifukdtnvcrrjveevfakxarxexomz'; +set @s3 = 'dbnfqyzgtqxalcrwtfsqabknvtfcbpoonxsjiqvmhnfikxxhcgoexlkoezvah'; +set @v1 = repeat(@s1,123); +set @v2 = repeat(@s2,234); +set @v3 = repeat(@s3,345); +set @v4 = NULL; +--enable_query_log + +insert into t1 (a,b,c,d,t) values ('a',1110,'a',1,@v1); +insert into t1 (a,b,c,d,t) values ('b',1110,'a',2,@v2); +insert into t1 (a,b,c,d,t) values ('a',1110,'b',3,@v3); +insert into t1 (a,b,c,d,t) values ('b',1110,'b',4,@v4); +select a,b,c,d,sha1(t) from t1 order by c,a; + +select a,b,c,d,sha1(t) from t1 where a='a' and b=1110 and c='a'; +select a,b,c,d,sha1(t) from t1 where a='a' and b=1110 and c='b'; + +update t1 set t=@v4 where a='b' and b=1110 and c='a'; +update t1 set t=@v2 where a='b' and b=1110 and c='b'; +select a,b,c,d,sha1(t) from t1 order by c,a; + +update t1 set t=@v2 where d=2; +update t1 set t=@v4 where d=4; +select a,b,c,d,sha1(t) from t1 order by c,a; + +update t1 set t=@v4 where a='b' and c='a'; +update t1 set t=@v2 where a='b' and c='b'; +select a,b,c,d,sha1(t) from t1 order by c,a; + +update t1 set t=@v2 where b+d=1112; +update t1 set t=@v4 where b+d=1114; +select a,b,c,d,sha1(t) from t1 order by c,a; + +delete from t1 where a='a' and b=1110 and c='a'; +delete from t1 where a='b' and c='a'; +delete from t1 where d=3; +delete from t1 where b+d=1114; +select count(*) from t1; + +drop table t1; + # End of 4.1 tests diff --git a/mysql-test/t/ndb_lock.test b/mysql-test/t/ndb_lock.test index 6945f91ee39..54214ee72ec 100644 --- a/mysql-test/t/ndb_lock.test +++ b/mysql-test/t/ndb_lock.test @@ -69,4 +69,117 @@ insert into t1 values (1,1,1); drop table t1; +# Lock for update + +create table t1 (x integer not null primary key, y varchar(32), z integer, key(z)) engine = ndb; + +insert into t1 values (1,'one',1), (2,'two',2),(3,"three",3); + +# PK access +connection con1; +begin; +select * from t1 where x = 1 for update; + +connection con2; +begin; +select * from t1 where x = 2 for update; +--error 1205 +select * from t1 where x = 1 for update; +rollback; + +connection con1; +commit; + +# table scan +connection con1; +begin; +select * from t1 where y = 'one' or y = 'three' order by x for update; + +connection con2; +begin; +# Have to check with pk access here since scans take locks on +# all rows and then release them in chunks +# Bug #20390 SELECT FOR UPDATE does not release locks of untouched rows in full table scans +#select * from t1 where x = 2 for update; +--error 1205 +select * from t1 where x = 1 for update; +rollback; + +connection con1; +commit; + +# index scan +connection con1; +begin; +select * from t1 where z > 1 and z < 3 for update; + +connection con2; +begin; +# Have to check with pk access here since scans take locks on +# all rows and then release them in chunks +select * from t1 where x = 1 for update; +--error 1205 +select * from t1 where x = 2 for update; +rollback; + +connection con1; +commit; + +# share locking + +# PK access +connection con1; +begin; +select * from t1 where x = 1 lock in share mode; + +connection con2; +begin; +select * from t1 where x = 1 lock in share mode; +select * from t1 where x = 2 for update; +--error 1205 +select * from t1 where x = 1 for update; +rollback; + +connection con1; +commit; + +# table scan +connection con1; +begin; +select * from t1 where y = 'one' or y = 'three' order by x lock in share mode; + +connection con2; +begin; +select * from t1 where y = 'one' lock in share mode; +# Have to check with pk access here since scans take locks on +# all rows and then release them in chunks +# Bug #20390 SELECT FOR UPDATE does not release locks of untouched rows in full table scans +#select * from t1 where x = 2 for update; +--error 1205 +select * from t1 where x = 1 for update; +rollback; + +connection con1; +commit; + +# index scan +connection con1; +begin; +select * from t1 where z > 1 and z < 3 lock in share mode; + +connection con2; +begin; +select * from t1 where z = 1 lock in share mode; +# Have to check with pk access here since scans take locks on +# all rows and then release them in chunks +select * from t1 where x = 1 for update; +--error 1205 +select * from t1 where x = 2 for update; +rollback; + +connection con1; +commit; + +drop table t1; + # End of 4.1 tests diff --git a/mysql-test/t/ndb_rename.test b/mysql-test/t/ndb_rename.test new file mode 100644 index 00000000000..99c344bc29e --- /dev/null +++ b/mysql-test/t/ndb_rename.test @@ -0,0 +1,32 @@ +-- source include/have_ndb.inc +-- source include/not_embedded.inc + +--disable_warnings +DROP TABLE IF EXISTS t1,t2; +drop database if exists mysqltest; +--enable_warnings + +# +# Table rename tests +# + +# +# Create a normal table with primary key +# +CREATE TABLE t1 ( + pk1 INT NOT NULL PRIMARY KEY, + attr1 INT NOT NULL, + attr2 INT, + attr3 VARCHAR(10), + INDEX i1(attr1) +) ENGINE=ndbcluster; + +alter table t1 rename t2; + +create database ndbtest; +alter table t2 rename ndbtest.t2; + +drop table ndbtest.t2; +drop database ndbtest; + +# End of 4.1 tests diff --git a/mysql-test/t/ndb_truncate.test b/mysql-test/t/ndb_truncate.test index 73af70d0d0f..a1ef4be0d48 100644 --- a/mysql-test/t/ndb_truncate.test +++ b/mysql-test/t/ndb_truncate.test @@ -2,12 +2,11 @@ -- source include/not_embedded.inc --disable_warnings -DROP TABLE IF EXISTS t2; +DROP TABLE IF EXISTS t1, t2; --enable_warnings - -CREATE TABLE t2 ( - a bigint unsigned NOT NULL PRIMARY KEY, +CREATE TABLE t1 ( + a bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, b int unsigned not null, c int unsigned ) engine=ndbcluster; @@ -20,17 +19,23 @@ let $1=500; disable_query_log; while ($1) { - eval insert into t2 values($1*10, $1+9, 5*$1), ($1*10+1, $1+10, 7),($1*10+2, $1+10, 7*$1), ($1*10+3, $1+10, 10+$1), ($1*10+4, $1+10, 70*$1), ($1*10+5, $1+10, 7), ($1*10+6, $1+10, 9), ($1*10+7, $1+299, 899), ($1*10+8, $1+10, 12), ($1*10+9, $1+10, 14*$1); + eval insert into t1 values(NULL, $1+9, 5*$1), (NULL, $1+10, 7),(NULL, $1+10, 7*$1), (NULL, $1+10, 10+$1), (NULL, $1+10, 70*$1), (NULL, $1+10, 7), (NULL, $1+10, 9), (NULL, $1+299, 899), (NULL, $1+10, 12), (NULL, $1+10, 14*$1); dec $1; } enable_query_log; -select count(*) from t2; +select count(*) from t1; + +select * from t1 order by a limit 2; + +truncate table t1; + +select count(*) from t1; -truncate table t2; +insert into t1 values(NULL,1,1),(NULL,2,2); -select count(*) from t2; +select * from t1 order by a; -drop table t2; +drop table t1; # End of 4.1 tests diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index cc7d7ef2d82..e3f3e37cd4c 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1145,5 +1145,5 @@ prepare stmt from "analyze table t4, t1"; execute stmt; execute stmt; deallocate prepare stmt; - +drop table t1, t2, t3; # End of 5.0 tests diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index ccd80dce388..e038829760d 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -148,3 +148,5 @@ drop function bug15728_insert; drop table t1, t2; # End of 5.0 tests + +sync_slave_with_master; diff --git a/mysql-test/t/rpl_temporary.test b/mysql-test/t/rpl_temporary.test index 51a195e3d0e..81f0e8a0af7 100644 --- a/mysql-test/t/rpl_temporary.test +++ b/mysql-test/t/rpl_temporary.test @@ -192,7 +192,7 @@ drop table t1; # #14157: utf8 encoding in binlog without set character_set_client # ---exec $MYSQL --character-sets-dir=../sql/share/charsets/ --default-character-set=koi8r test -e 'create table t1 (a int); set names koi8r; create temporary table `ÑÝÉË` (a int); insert into `ÑÝÉË` values (1); insert into t1 select * from `ÑÝÉË`' +--exec $MYSQL --character-sets-dir=../sql/share/charsets/ --default-character-set=latin1 test -e 'create table t1 (a int); set names latin1; create temporary table `äöüÄÖÜ` (a int); insert into `äöüÄÖÜ` values (1); insert into t1 select * from `äöüÄÖÜ`' sync_slave_with_master; #connection slave; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 4b6ae921b9b..8cd15463c62 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1296,9 +1296,9 @@ explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; # The next should give an error # --- error 1072 +-- error 1176 explain select fld3 from t2 ignore index (fld3,not_used); --- error 1072 +-- error 1176 explain select fld3 from t2 use index (not_used); # @@ -2264,6 +2264,21 @@ insert into t2 values(1,1); select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2)); drop table t1,t2; +# +# Bug #17873: confusing error message when IGNORE INDEX refers a column name +# + +CREATE TABLE t1 (a int, INDEX idx(a)); +INSERT INTO t1 VALUES (2), (3), (1); + +EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx); +--error 1176 +EXPLAIN SELECT * FROM t1 IGNORE INDEX (a); +--error 1176 +EXPLAIN SELECT * FROM t1 FORCE INDEX (a); + +DROP TABLE t1; + # End of 4.1 tests # diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 9995ff5a9ad..1d21a5da187 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -5844,6 +5844,52 @@ DROP PROCEDURE bug18589_p2| # +# BUG#18037: Server crash when returning system variable in stored procedures +# BUG#19633: Stack corruption in fix_fields()/THD::rollback_item_tree_changes() +# + +# Prepare. + +--disable_warnings +DROP FUNCTION IF EXISTS bug18037_f1| +DROP PROCEDURE IF EXISTS bug18037_p1| +DROP PROCEDURE IF EXISTS bug18037_p2| +--enable_warnings + +# Test case. + +CREATE FUNCTION bug18037_f1() RETURNS INT +BEGIN + RETURN @@server_id; +END| + +CREATE PROCEDURE bug18037_p1() +BEGIN + DECLARE v INT DEFAULT @@server_id; +END| + +CREATE PROCEDURE bug18037_p2() +BEGIN + CASE @@server_id + WHEN -1 THEN + SELECT 0; + ELSE + SELECT 1; + END CASE; +END| + +SELECT bug18037_f1()| +CALL bug18037_p1()| +CALL bug18037_p2()| + +# Cleanup. + +DROP FUNCTION bug18037_f1| +DROP PROCEDURE bug18037_p1| +DROP PROCEDURE bug18037_p2| + + +# # BUG#NNNN: New bug synopsis # #--disable_warnings diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test index d3b36cbc2a8..5044a20ae9f 100644 --- a/mysql-test/t/strict.test +++ b/mysql-test/t/strict.test @@ -1145,3 +1145,13 @@ create table t1(a bit(2)); insert into t1 values(b'101'); select * from t1; drop table t1; + +# +# Bug#17626 CREATE TABLE ... SELECT failure with TRADITIONAL SQL mode +# +set sql_mode='traditional'; +create table t1 (date date not null); +create table t2 select date from t1; +show create table t2; +drop table t2,t1; +set @@sql_mode= @org_mode; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 9f4d89a7e50..8916a5cec6d 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2100,3 +2100,34 @@ CREATE VIEW v2 AS SELECT * FROM t1 WHERE no_such_column = (SELECT 1); SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1); DROP TABLE t1; + +# +# Bug#19077: A nested materialized derived table is used before being populated. +# +create table t1 (i int, j bigint); +insert into t1 values (1, 2), (2, 2), (3, 2); +select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2)) t3; +drop table t1; + +# +# Bug#19700: subselect returning BIGINT always returned it as SIGNED +# +CREATE TABLE t1 (i BIGINT UNSIGNED); +INSERT INTO t1 VALUES (10000000000000000000); -- > MAX SIGNED BIGINT 9323372036854775807 +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 (i BIGINT UNSIGNED); +INSERT INTO t2 VALUES (10000000000000000000); -- same as first table +INSERT INTO t2 VALUES (1); + +/* simple test */ +SELECT t1.i FROM t1 JOIN t2 ON t1.i = t2.i; + +/* subquery test */ +SELECT t1.i FROM t1 WHERE t1.i = (SELECT MAX(i) FROM t2); + +/* subquery test with cast*/ +SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED); + +DROP TABLE t1; +DROP TABLE t2; diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 20928c7b6c1..be1731e7493 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -540,7 +540,42 @@ select @@version, @@version_comment, @@version_compile_machine, @@version_compile_os; # -# Bug #19263: variables.test doesn't clean up after itself (II/II -- restore) +# Bug #1039: make tmpdir and datadir available as @@variables (also included +# basedir) +# +# Don't actually output, since it depends on the system +--replace_column 1 # 2 # 3 # +select @@basedir, @@datadir, @@tmpdir; +--replace_column 2 # +show variables like 'basedir'; +--replace_column 2 # +show variables like 'datadir'; +--replace_column 2 # +show variables like 'tmpdir'; + +# +# Bug #19606: make ssl settings available via SHOW VARIABLES and @@variables +# +# Don't actually output, since it depends on the system +--replace_column 1 # 2 # 3 # 4 # 5 # +select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_key; +--replace_column 2 # +show variables like 'ssl%'; + +# +# Bug #19616: make log_queries_not_using_indexes available in SHOW VARIABLES +# and as @@log_queries_not_using_indexes +# +select @@log_queries_not_using_indexes; +show variables like 'log_queries_not_using_indexes'; + +--echo End of 5.0 tests + +# This is at the very after the versioned tests, since it involves doing +# cleanup +# +# Bug #19263: variables.test doesn't clean up after itself (II/II -- +# restore) # set global binlog_cache_size =@my_binlog_cache_size; set global connect_timeout =@my_connect_timeout; @@ -569,5 +604,3 @@ set global server_id =@my_server_id; set global slow_launch_time =@my_slow_launch_time; set global storage_engine =@my_storage_engine; set global thread_cache_size =@my_thread_cache_size; - ---echo End of 5.0 tests diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index ea22ada900a..a1c1e9b2ad1 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -516,7 +516,7 @@ drop table t1; # create table t1 (a int, b int); create view v1 as select a, sum(b) from t1 group by a; --- error 1072 +-- error 1176 select b from v1 use index (some_index) where b=1; drop view v1; drop table t1; @@ -2528,3 +2528,71 @@ SELECT * FROM v1; DROP VIEW v1; DROP TABLE t1; + +# +# Bug#19077: A nested materialized view is used before being populated. +# +CREATE TABLE t1 (i INT, j BIGINT); +INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2); +CREATE VIEW v1 AS SELECT MIN(j) AS j FROM t1; +CREATE VIEW v2 AS SELECT MIN(i) FROM t1 WHERE j = ( SELECT * FROM v1 ); +SELECT * FROM v2; +DROP VIEW v2, v1; +DROP TABLE t1; + +# +# Bug #19573: VIEW with HAVING that refers an alias name +# + +CREATE TABLE t1( + fName varchar(25) NOT NULL, + lName varchar(25) NOT NULL, + DOB date NOT NULL, + uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY); + +INSERT INTO t1(fName, lName, DOB) VALUES + ('Hank', 'Hill', '1964-09-29'), + ('Tom', 'Adams', '1908-02-14'), + ('Homer', 'Simpson', '1968-03-05'); + +CREATE VIEW v1 AS + SELECT (year(now())-year(DOB)) AS Age + FROM t1 HAVING Age < 75; +SHOW CREATE VIEW v1; + +SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75; +SELECT * FROM v1; + +DROP VIEW v1; +DROP TABLE t1; + +# +# Bug #19089: wrong inherited dafault values in temp table views +# + +CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a char(6) DEFAULT 'xxx'); +INSERT INTO t1(id) VALUES (1), (2), (3), (4); +INSERT INTO t1 VALUES (5,'yyy'), (6,'yyy'); +SELECT * FROM t1; + +CREATE VIEW v1(a, m) AS SELECT a, MIN(id) FROM t1 GROUP BY a; +SELECT * FROM v1; + +CREATE TABLE t2 SELECT * FROM v1; +INSERT INTO t2(m) VALUES (0); +SELECT * FROM t2; + +DROP VIEW v1; +DROP TABLE t1,t2; + +CREATE TABLE t1 (id int PRIMARY KEY, e ENUM('a','b') NOT NULL DEFAULT 'b'); +INSERT INTO t1(id) VALUES (1), (2), (3); +INSERT INTO t1 VALUES (4,'a'); +SELECT * FROM t1; + +CREATE VIEW v1(m, e) AS SELECT MIN(id), e FROM t1 GROUP BY e; +CREATE TABLE t2 SELECT * FROM v1; +SELECT * FROM t2; + +DROP VIEW v1; +DROP TABLE IF EXISTS t1,t2; diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index e80e1770ba2..f160de2d798 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -712,3 +712,137 @@ show create view v1; show create view v2; drop view v1; drop view v2; + +# +# Bug#18681: View privileges are broken +# +CREATE DATABASE mysqltest1; +CREATE USER readonly@localhost; +CREATE TABLE mysqltest1.t1 (x INT); +INSERT INTO mysqltest1.t1 VALUES (1), (2); +CREATE SQL SECURITY INVOKER VIEW mysqltest1.v_t1 AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ts AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ti AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tu AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tus AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_td AS SELECT * FROM mysqltest1.t1; +CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tds AS SELECT * FROM mysqltest1.t1; +GRANT SELECT, INSERT, UPDATE, DELETE ON mysqltest1.v_t1 TO readonly; +GRANT SELECT ON mysqltest1.v_ts TO readonly; +GRANT INSERT ON mysqltest1.v_ti TO readonly; +GRANT UPDATE ON mysqltest1.v_tu TO readonly; +GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly; +GRANT DELETE ON mysqltest1.v_td TO readonly; +GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly; + +CONNECT (n1,localhost,readonly,,); +CONNECTION n1; + +--error 1356 +SELECT * FROM mysqltest1.v_t1; +--error 1356 +INSERT INTO mysqltest1.v_t1 VALUES(4); +--error 1356 +DELETE FROM mysqltest1.v_t1 WHERE x = 1; +--error 1356 +UPDATE mysqltest1.v_t1 SET x = 3 WHERE x = 2; +--error 1356 +UPDATE mysqltest1.v_t1 SET x = 3; +--error 1356 +DELETE FROM mysqltest1.v_t1; +--error 1356 +SELECT 1 FROM mysqltest1.v_t1; +--error 1142 +SELECT * FROM mysqltest1.t1; + +SELECT * FROM mysqltest1.v_ts; +--error 1142 +SELECT * FROM mysqltest1.v_ts, mysqltest1.t1 WHERE mysqltest1.t1.x = mysqltest1.v_ts.x; +--error 1142 +SELECT * FROM mysqltest1.v_ti; + +--error 1142 +INSERT INTO mysqltest1.v_ts VALUES (100); +INSERT INTO mysqltest1.v_ti VALUES (100); + +--error 1142 +UPDATE mysqltest1.v_ts SET x= 200 WHERE x = 100; +--error 1142 +UPDATE mysqltest1.v_ts SET x= 200; +UPDATE mysqltest1.v_tu SET x= 200 WHERE x = 100; +UPDATE mysqltest1.v_tus SET x= 200 WHERE x = 100; +UPDATE mysqltest1.v_tu SET x= 200; + +--error 1142 +DELETE FROM mysqltest1.v_ts WHERE x= 200; +--error 1142 +DELETE FROM mysqltest1.v_ts; +--error 1143 +DELETE FROM mysqltest1.v_td WHERE x= 200; +DELETE FROM mysqltest1.v_tds WHERE x= 200; +DELETE FROM mysqltest1.v_td; + +CONNECTION default; +DROP VIEW mysqltest1.v_tds; +DROP VIEW mysqltest1.v_td; +DROP VIEW mysqltest1.v_tus; +DROP VIEW mysqltest1.v_tu; +DROP VIEW mysqltest1.v_ti; +DROP VIEW mysqltest1.v_ts; +DROP VIEW mysqltest1.v_t1; +DROP TABLE mysqltest1.t1; +DROP USER readonly@localhost; +DROP DATABASE mysqltest1; + +# +# BUG#14875: Bad view DEFINER makes SHOW CREATE VIEW fail +# +CREATE TABLE t1 (a INT PRIMARY KEY); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE DEFINER = 'no-such-user'@localhost VIEW v AS SELECT a from t1; +--warning 1448 +SHOW CREATE VIEW v; +--error 1449 +SELECT * FROM v; +DROP VIEW v; +DROP TABLE t1; +USE test; + +# +# Bug#20363: Create view on just created view is now denied +# +eval CREATE USER mysqltest_db1@localhost identified by 'PWD'; +eval GRANT ALL ON mysqltest_db1.* TO mysqltest_db1@localhost WITH GRANT OPTION; + +# The session with the non root user is needed. +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (session1,localhost,mysqltest_db1,PWD,test); + +CREATE SCHEMA mysqltest_db1 ; +USE mysqltest_db1 ; + +CREATE TABLE t1 (f1 INTEGER); + +CREATE VIEW view1 AS +SELECT * FROM t1; +SHOW CREATE VIEW view1; + +CREATE VIEW view2 AS +SELECT * FROM view1; +--echo # Here comes a suspicious warning +SHOW CREATE VIEW view2; +--echo # But the view view2 is usable +SELECT * FROM view2; + +CREATE VIEW view3 AS +SELECT * FROM view2; + +SELECT * from view3; + +connection default; +DROP VIEW mysqltest_db1.view3; +DROP VIEW mysqltest_db1.view2; +DROP VIEW mysqltest_db1.view1; +DROP TABLE mysqltest_db1.t1; +DROP SCHEMA mysqltest_db1; +DROP USER mysqltest_db1@localhost; diff --git a/mysql-test/t/wait_timeout.test b/mysql-test/t/wait_timeout.test index 1fef3deea3c..9310c3502b9 100644 --- a/mysql-test/t/wait_timeout.test +++ b/mysql-test/t/wait_timeout.test @@ -4,10 +4,41 @@ # # Bug #8731: wait_timeout does not work on Mac OS X # + + +# Connect with another connection and reset counters +--disable_query_log +connect (wait_con,localhost,root,,test,,); +flush status; # Reset counters +connection wait_con; +let $retries=300; +let $aborted_clients = `SHOW STATUS LIKE 'aborted_clients'`; +set @aborted_clients= 0; +--enable_query_log + +# Disable reconnect and do the query +connection default; --disable_reconnect select 1; -# wait_timeout is 1, so we should get disconnected now ---sleep 2 + +# Switch to wait_con and wait until server has aborted the connection +--disable_query_log +connection wait_con; +while (!`select @aborted_clients`) +{ + sleep 0.1; + let $aborted_clients = `SHOW STATUS LIKE 'aborted_clients'`; + eval set @aborted_clients= SUBSTRING('$aborted_clients', 16)+0; + + dec $retries; + if (!$retries) + { + Failed to detect that client has been aborted; + } +} +--enable_query_log + +connection default; # When the connection is closed in this way, the error code should # be consistent see bug#2845 for an explanation --error 2006 @@ -15,12 +46,41 @@ select 2; --enable_reconnect select 3; +# # Do the same test as above on a TCP connection +# (which we get by specifying a ip adress) + +# Connect with another connection and reset counters +--disable_query_log +connection wait_con; +flush status; # Reset counters +let $retries=300; +let $aborted_clients = `SHOW STATUS LIKE 'aborted_clients'`; +set @aborted_clients= 0; +--enable_query_log + connect (con1,127.0.0.1,root,,test,$MASTER_MYPORT,); --disable_reconnect select 1; -# wait_timeout is 1, so we should get disconnected now ---sleep 2 + +# Switch to wait_con and wait until server has aborted the connection +--disable_query_log +connection wait_con; +while (!`select @aborted_clients`) +{ + sleep 0.1; + let $aborted_clients = `SHOW STATUS LIKE 'aborted_clients'`; + eval set @aborted_clients= SUBSTRING('$aborted_clients', 16)+0; + + dec $retries; + if (!$retries) + { + Failed to detect that client has been aborted; + } +} +--enable_query_log + +connection con1; # When the connection is closed in this way, the error code should # be consistent see bug#2845 for an explanation --error 2006 diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index 7737810653d..3c2ac1a7ea5 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -11,7 +11,7 @@ fun:calloc fun:_dl_allocate_tls fun:allocate_stack - fun:pthread_create@@GLIBC_2.1 + fun:pthread_create* } { @@ -34,15 +34,6 @@ } { - pthread allocate_dtv memory loss second - Memcheck:Leak - fun:calloc - fun:allocate_dtv - fun:_dl_allocate_tls - fun:pthread_create* -} - -{ pthread memalign memory loss Memcheck:Leak fun:memalign @@ -73,17 +64,6 @@ } { - pthread strstr uninit - Memcheck:Cond - fun:strstr - obj:/lib/tls/libpthread.so.* - obj:/lib/tls/libpthread.so.* - fun:call_init - fun:_dl_init - obj:/lib/ld-*.so -} - -{ pthread errno Memcheck:Leak fun:calloc @@ -152,3 +132,163 @@ obj:*/libz.so.* fun:gzflush } + +# +# Leaks reported in _dl_* internal functions on Linux amd64 / glibc2.3.2. +# + +{ + _dl_start invalid write8 + Memcheck:Addr8 + fun:_dl_start +} + +{ + _dl_start invalid write4 + Memcheck:Addr4 + fun:_dl_start +} + +{ + _dl_start/_dl_setup_hash invalid read8 + Memcheck:Addr8 + fun:_dl_setup_hash + fun:_dl_start +} + +{ + _dl_sysdep_start invalid write8 + Memcheck:Addr8 + fun:_dl_sysdep_start +} + +{ + _dl_init invalid write8 + Memcheck:Addr8 + fun:_dl_init +} + +{ + _dl_init invalid write4 + Memcheck:Addr4 + fun:_dl_init +} + +{ + _dl_init/_dl_init invalid read8 + Memcheck:Addr8 + fun:_dl_debug_initialize + fun:_dl_init +} + +{ + _dl_init/_dl_debug_state invalid read8 + Memcheck:Addr8 + fun:_dl_debug_state + fun:_dl_init +} + +{ + init invalid write8 + Memcheck:Addr8 + fun:init +} + +{ + fixup invalid write8 + Memcheck:Addr8 + fun:fixup +} + +{ + fixup/_dl_lookup_versioned_symbol invalid read8 + Memcheck:Addr8 + fun:_dl_lookup_versioned_symbol + fun:fixup +} + +{ + _dl_runtime_resolve invalid read8 + Memcheck:Addr8 + fun:_dl_runtime_resolve +} + +{ + __libc_start_main invalid write8 + Memcheck:Addr8 + fun:__libc_start_main +} + +{ + __libc_start_main/__sigjmp_save invalid write4 + Memcheck:Addr4 + fun:__sigjmp_save + fun:__libc_start_main +} + +# +# These seem to be libc threading stuff, not related to MySQL code (allocations +# during pthread_exit()). Googling shows other projects also using these +# suppressions. +# +# Note that these all stem from pthread_exit() deeper in the call stack, but +# Valgrind only allows the top four calls in the suppressions. +# + +{ + libc pthread_exit 1 + Memcheck:Leak + fun:malloc + fun:_dl_new_object + fun:_dl_map_object_from_fd + fun:_dl_map_object +} + +{ + libc pthread_exit 2 + Memcheck:Leak + fun:malloc + fun:_dl_map_object + fun:dl_open_worker + fun:_dl_catch_error +} + +{ + libc pthread_exit 3 + Memcheck:Leak + fun:malloc + fun:_dl_map_object_deps + fun:dl_open_worker + fun:_dl_catch_error +} + +{ + libc pthread_exit 4 + Memcheck:Leak + fun:calloc + fun:_dl_check_map_versions + fun:dl_open_worker + fun:_dl_catch_error +} + +{ + libc pthread_exit 5 + Memcheck:Leak + fun:calloc + fun:_dl_new_object + fun:_dl_map_object_from_fd + fun:_dl_map_object +} + + + +# +# This is seen internally in the system libraries on 64-bit RHAS3. +# + +{ + __lll_mutex_unlock_wake uninitialized + Memcheck:Param + futex(utime) + fun:__lll_mutex_unlock_wake +} |