diff options
Diffstat (limited to 'mysql-test')
33 files changed, 885 insertions, 32 deletions
diff --git a/mysql-test/ndb/ndb_range_bounds.pl b/mysql-test/ndb/ndb_range_bounds.pl new file mode 100644 index 00000000000..75b7f8a33e1 --- /dev/null +++ b/mysql-test/ndb/ndb_range_bounds.pl @@ -0,0 +1,138 @@ +# +# test range scan bounds +# output to mysql-test/t/ndb_range_bounds.test +# +# give option --all to generate all cases +# + +use strict; +use integer; +use Getopt::Long; + +my $opt_all = 0; +my $opt_cnt = 5; +GetOptions("all" => \$opt_all, "cnt=i" => \$opt_cnt) + or die "options are: --all --cnt=N"; + +my $table = 't'; + +print <<EOF; +--source include/have_ndb.inc + +--disable_warnings +drop table if exists $table; +--enable_warnings + +# test range scan bounds +# generated by mysql-test/ndb/ndb_range_bounds.pl +# all selects must return 0 + +EOF + +sub cut ($$@) { + my($op, $key, @v) = @_; + $op = '==' if $op eq '='; + my(@w); + eval "\@w = grep(\$_ $op $key, \@v)"; + $@ and die $@; + return @w; +} + +sub mkdummy (\@) { + my ($val) = @_; + return { + 'dummy' => 1, + 'exp' => '9 = 9', + 'cnt' => scalar @$val, + }; +} + +sub mkone ($$$\@) { + my($col, $op, $key, $val) = @_; + my $cnt = scalar cut($op, $key, @$val); + return { + 'exp' => "$col $op $key", + 'cnt' => $cnt, + }; +} + +sub mktwo ($$$$$\@) { + my($col, $op1, $key1, $op2, $key2, $val) = @_; + my $cnt = scalar cut($op2, $key2, cut($op1, $key1, @$val)); + return { + 'exp' => "$col $op1 $key1 and $col $op2 $key2", + 'cnt' => $cnt, + }; +} + +sub mkall ($$$\@) { + my($col, $key1, $key2, $val) = @_; + my @a = (); + my $p = mkdummy(@$val); + push(@a, $p) if $opt_all; + my @ops = qw(< <= = >= >); + for my $op (@ops) { + my $p = mkone($col, $op, $key1, @$val); + push(@a, $p) if $opt_all || $p->{cnt} != 0; + } + my @ops1 = $opt_all ? @ops : qw(= >= >); + my @ops2 = $opt_all ? @ops : qw(<= <); + for my $op1 (@ops1) { + for my $op2 (@ops2) { + my $p = mktwo($col, $op1, $key1, $op2, $key2, @$val); + push(@a, $p) if $opt_all || $p->{cnt} != 0; + } + } + return \@a; +} + +for my $nn ("bcd", "") { + my %nn; + for my $x (qw(b c d)) { + $nn{$x} = $nn =~ /$x/ ? "not null" : "null"; + } + print <<EOF; +create table $table ( + a int primary key, + b int $nn{b}, + c int $nn{c}, + d int $nn{d}, + index (b, c, d) +) engine=ndb; +EOF + my @val = (0..($opt_cnt-1)); + my $v0 = 0; + for my $v1 (@val) { + for my $v2 (@val) { + for my $v3 (@val) { + print "insert into $table values($v0, $v1, $v2, $v3);\n"; + $v0++; + } + } + } + my $key1 = 1; + my $key2 = 3; + my $a1 = mkall('b', $key1, $key2, @val); + my $a2 = mkall('c', $key1, $key2, @val); + my $a3 = mkall('d', $key1, $key2, @val); + for my $p1 (@$a1) { + my $cnt1 = $p1->{cnt} * @val * @val; + print "select count(*) - $cnt1 from $table"; + print " where $p1->{exp};\n"; + for my $p2 (@$a2) { + my $cnt2 = $p1->{cnt} * $p2->{cnt} * @val; + print "select count(*) - $cnt2 from $table"; + print " where $p1->{exp} and $p2->{exp};\n"; + for my $p3 (@$a3) { + my $cnt3 = $p1->{cnt} * $p2->{cnt} * $p3->{cnt}; + print "select count(*) - $cnt3 from $table"; + print " where $p1->{exp} and $p2->{exp} and $p3->{exp};\n"; + } + } + } + print <<EOF; +drop table $table; +EOF +} + +# vim: set sw=2: diff --git a/mysql-test/ndb/ndbcluster.sh b/mysql-test/ndb/ndbcluster.sh index 7485e42923e..294d32ac4be 100644 --- a/mysql-test/ndb/ndbcluster.sh +++ b/mysql-test/ndb/ndbcluster.sh @@ -205,7 +205,7 @@ if [ -f "$fs_ndb/$pidfile" ] ; then fi done kill_pids=$new_kill_pid - if [ "$kill_pids" == "" ] ; then + if [ -z "$kill_pids" ] ; then break fi sleep 1 diff --git a/mysql-test/r/ctype_sjis.result b/mysql-test/r/ctype_sjis.result index 1e3e28784a5..26a45dd28e8 100644 --- a/mysql-test/r/ctype_sjis.result +++ b/mysql-test/r/ctype_sjis.result @@ -41,3 +41,9 @@ C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF select hex(CONVERT(@utf84 USING sjis)); hex(CONVERT(@utf84 USING sjis)) D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF +create table t1 (a char(10) character set sjis); +insert into t1 values (0x878A); +select hex(a) from t1; +hex(a) +878A +drop table t1; diff --git a/mysql-test/r/ctype_uca.result b/mysql-test/r/ctype_uca.result index 7e4a03e96cc..90681795513 100644 --- a/mysql-test/r/ctype_uca.result +++ b/mysql-test/r/ctype_uca.result @@ -1,5 +1,35 @@ DROP TABLE IF EXISTS t1; set names utf8; +set collation_connection=utf8_unicode_ci; +select 'a' = 'a', 'a' = 'a ', 'a ' = 'a'; +'a' = 'a' 'a' = 'a ' 'a ' = 'a' +1 1 1 +select 'a\t' = 'a' , 'a\t' < 'a' , 'a\t' > 'a'; +'a\t' = 'a' 'a\t' < 'a' 'a\t' > 'a' +0 1 0 +select 'a\t' = 'a ', 'a\t' < 'a ', 'a\t' > 'a '; +'a\t' = 'a ' 'a\t' < 'a ' 'a\t' > 'a ' +0 1 0 +select 'a' = 'a\t', 'a' < 'a\t', 'a' > 'a\t'; +'a' = 'a\t' 'a' < 'a\t' 'a' > 'a\t' +0 0 1 +select 'a ' = 'a\t', 'a ' < 'a\t', 'a ' > 'a\t'; +'a ' = 'a\t' 'a ' < 'a\t' 'a ' > 'a\t' +0 0 1 +select 'a a' > 'a', 'a \t' < 'a'; +'a a' > 'a' 'a \t' < 'a' +1 1 +CREATE TABLE t ( +c char(20) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +INSERT INTO t VALUES ('a'),('ab'),('aba'); +ALTER TABLE t ADD INDEX (c); +SELECT c FROM t WHERE c LIKE 'a%'; +c +a +ab +aba +DROP TABLE t; create table t1 (c1 char(10) character set utf8 collate utf8_bin); insert into t1 values ('A'),('a'); insert into t1 values ('B'),('b'); diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 2e8bbc8fa92..e65eb96cb68 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -63,6 +63,15 @@ select 'A' like 'a' collate utf8_bin; select _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD0B1,_utf8 '%'); _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD0B1,_utf8 '%') 1 +select convert(_latin1'Günter André' using utf8) like CONVERT(_latin1'GÜNTER%' USING utf8); +convert(_latin1'Günter André' using utf8) like CONVERT(_latin1'GÜNTER%' USING utf8) +1 +select CONVERT(_koi8r'×ÁÓÑ' USING utf8) LIKE CONVERT(_koi8r'÷áóñ' USING utf8); +CONVERT(_koi8r'×ÁÓÑ' USING utf8) LIKE CONVERT(_koi8r'÷áóñ' USING utf8) +1 +select CONVERT(_koi8r'÷áóñ' USING utf8) LIKE CONVERT(_koi8r'×ÁÓÑ' USING utf8); +CONVERT(_koi8r'÷áóñ' USING utf8) LIKE CONVERT(_koi8r'×ÁÓÑ' USING utf8) +1 SELECT 'a' = 'a '; 'a' = 'a ' 1 diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index c3fe1de15db..8a28312b348 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -174,3 +174,12 @@ SELECT GREATEST(d,d) FROM t1 WHERE k=2; GREATEST(d,d) NULL DROP TABLE t1; +select 1197.90 mod 50; +1197.90 mod 50 +47.90 +select 5.1 mod 3, 5.1 mod -3, -5.1 mod 3, -5.1 mod -3; +5.1 mod 3 5.1 mod -3 -5.1 mod 3 -5.1 mod -3 +2.1 2.1 -2.1 -2.1 +select 5 mod 3, 5 mod -3, -5 mod 3, -5 mod -3; +5 mod 3 5 mod -3 -5 mod 3 -5 mod -3 +2 2 -2 -2 diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result index 31e506d2679..a31fa2ac3dc 100644 --- a/mysql-test/r/grant2.result +++ b/mysql-test/r/grant2.result @@ -1,6 +1,9 @@ SET NAMES binary; +drop database if exists mysqltest; delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; +delete from mysql.tables_priv where user like 'mysqltest\_%'; +delete from mysql.columns_priv where user like 'mysqltest\_%'; flush privileges; grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option; select current_user(); @@ -25,3 +28,27 @@ ERROR 42000: There is no such grant defined for user 'mysqltest_3' on host 'loca delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; flush privileges; +create database mysqltest; +grant INSERT, SELECT on mysqltest.* to mysqltest_1@localhost; +flush privileges; +use mysqltest; +create table t1 (id int primary key, data varchar(255)); +show grants for current_user(); +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT SELECT, INSERT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +use mysqltest; +insert into t1 values (1, 'I can''t change it!'); +update t1 set data='I can change it!' where id = 1; +ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest' +insert into t1 values (1, 'XXX') on duplicate key update data= 'I can change it!'; +ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest' +select * from t1; +id data +1 I can't change it! +drop table t1; +drop database mysqltest; +use test; +delete from mysql.user where user like 'mysqltest\_%'; +delete from mysql.db where user like 'mysqltest\_%'; +flush privileges; diff --git a/mysql-test/r/innodb-lock.result b/mysql-test/r/innodb-lock.result index cf00adb30ae..407a85ed038 100644 --- a/mysql-test/r/innodb-lock.result +++ b/mysql-test/r/innodb-lock.result @@ -1,4 +1,8 @@ +select @@innodb_table_locks; +@@innodb_table_locks +1 drop table if exists t1; +set @@innodb_table_locks=1; create table t1 (id integer, x integer) engine=INNODB; insert into t1 values(0, 0); set autocommit=0; @@ -20,3 +24,33 @@ id x 0 2 commit; drop table t1; +set @@innodb_table_locks=0; +create table t1 (id integer primary key, x integer) engine=INNODB; +insert into t1 values(0, 0),(1,1),(2,2); +commit; +SELECT * from t1 where id = 0 FOR UPDATE; +id x +0 0 +set autocommit=0; +set @@innodb_table_locks=0; +lock table t1 write; +update t1 set x=10 where id = 2; +SELECT * from t1 where id = 2; +id x +2 2 +UPDATE t1 set x=3 where id = 2; +commit; +SELECT * from t1; +id x +0 0 +1 1 +2 3 +commit; +unlock tables; +commit; +select * from t1; +id x +0 0 +1 1 +2 10 +drop table t1; diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result new file mode 100644 index 00000000000..4e30d5bc110 --- /dev/null +++ b/mysql-test/r/mysqltest.result @@ -0,0 +1,23 @@ +select otto from (select 1 as otto) as t1; +otto +1 +select otto from (select 1 as otto) as t1; +otto +1 +select otto from (select 1 as otto) as t1; +otto +1 +select friedrich from (select 1 as otto) as t1; +ERROR 42S22: Unknown column 'friedrich' in 'field list' +select friedrich from (select 1 as otto) as t1; +ERROR 42S22: Unknown column 'friedrich' in 'field list' +select otto from (select 1 as otto) as t1; +otto +1 +select otto from (select 1 as otto) as t1; +otto +1 +select friedrich from (select 1 as otto) as t1; +ERROR 42S22: Unknown column 'friedrich' in 'field list' +select friedrich from (select 1 as otto) as t1; +ERROR 42S22: Unknown column 'friedrich' in 'field list' diff --git a/mysql-test/r/ndb_basic.result b/mysql-test/r/ndb_basic.result index 9f8dcf3610a..abe1b98b536 100644 --- a/mysql-test/r/ndb_basic.result +++ b/mysql-test/r/ndb_basic.result @@ -395,3 +395,22 @@ b attr1 9413 9412 drop table test.t1, t2; drop database mysqltest; +use test; +create table t1 (a int primary key, b char(0)); +insert into t1 values (1,""); +insert into t1 values (2,NULL); +select * from t1 order by a; +a b +1 +2 NULL +select * from t1 order by b; +a b +2 NULL +1 +select * from t1 where b IS NULL; +a b +2 NULL +select * from t1 where b IS NOT NULL; +a b +1 +drop table t1; diff --git a/mysql-test/r/ndb_index_ordered.result b/mysql-test/r/ndb_index_ordered.result index 2a3050e5dea..2dc260ec43d 100644 --- a/mysql-test/r/ndb_index_ordered.result +++ b/mysql-test/r/ndb_index_ordered.result @@ -37,6 +37,24 @@ a b c 1 2 3 2 3 5 3 4 6 +select tt1.* from t1 as tt1, t1 as tt2 use index(b) where tt1.b = tt2.b order by tt1.b; +a b c +1 2 3 +2 3 5 +3 4 6 +4 5 8 +5 6 2 +6 7 2 +select a, b, c from t1 where a!=2 and c=6; +a b c +3 4 6 +select a, b, c from t1 where a!=2 order by a; +a b c +1 2 3 +3 4 6 +4 5 8 +5 6 2 +6 7 2 update t1 set c = 3 where b = 3; select * from t1 order by a; a b c diff --git a/mysql-test/r/ndb_lock.result b/mysql-test/r/ndb_lock.result index 56661913e22..b8c2c58aac4 100644 --- a/mysql-test/r/ndb_lock.result +++ b/mysql-test/r/ndb_lock.result @@ -28,3 +28,38 @@ x y 2 two 3 three commit; +drop table t1; +create table t1 (pk integer not null primary key, u int not null, o int not null, +unique(u), key(o)) engine = ndb; +insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); +lock tables t1 write; +delete from t1 where pk = 1; +unlock tables; +select * from t1 order by pk; +pk u o +2 2 2 +3 3 3 +4 4 4 +5 5 5 +insert into t1 values (1,1,1); +lock tables t1 write; +delete from t1 where u = 1; +unlock tables; +select * from t1 order by pk; +pk u o +2 2 2 +3 3 3 +4 4 4 +5 5 5 +insert into t1 values (1,1,1); +lock tables t1 write; +delete from t1 where o = 1; +unlock tables; +select * from t1 order by pk; +pk u o +2 2 2 +3 3 3 +4 4 4 +5 5 5 +insert into t1 values (1,1,1); +drop table t1; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 0aba0c4672e..0950a066e64 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -375,3 +375,38 @@ rand() cast(rand(10)*@precision as unsigned integer) cast(rand(a)*@precision as - 9647622201 3845601374 6211931236 drop table t1; deallocate prepare stmt; +create database mysqltest1; +create table t1 (a int); +create table mysqltest1.t1 (a int); +select * from t1, mysqltest1.t1; +a a +prepare stmt from "select * from t1, mysqltest1.t1"; +execute stmt; +a a +execute stmt; +a a +execute stmt; +a a +drop table t1; +drop table mysqltest1.t1; +drop database mysqltest1; +deallocate prepare stmt; +select '1.1' as a, '1.2' as a UNION SELECT '2.1', '2.2'; +a a +1.1 1.2 +2.1 2.2 +prepare stmt from +"select '1.1' as a, '1.2' as a UNION SELECT '2.1', '2.2'"; +execute stmt; +a a +1.1 1.2 +2.1 2.2 +execute stmt; +a a +1.1 1.2 +2.1 2.2 +execute stmt; +a a +1.1 1.2 +2.1 2.2 +deallocate prepare stmt; diff --git a/mysql-test/r/ps_10nestset.result b/mysql-test/r/ps_10nestset.result index 10f0a741b54..68f58a03674 100644 --- a/mysql-test/r/ps_10nestset.result +++ b/mysql-test/r/ps_10nestset.result @@ -1,14 +1,11 @@ -use test; -drop table if exists personnel; -Warnings: -Note 1051 Unknown table 'personnel' -create table personnel ( +drop table if exists t1; +create table t1 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, emp CHAR(10) NOT NULL, salary DECIMAL(6,2) NOT NULL, l INTEGER NOT NULL, r INTEGER NOT NULL); -prepare st_ins from 'insert into personnel set emp = ?, salary = ?, l = ?, r = ?'; +prepare st_ins from 'insert into t1 set emp = ?, salary = ?, l = ?, r = ?'; set @arg_nam= 'Jerry'; set @arg_sal= 1000; set @arg_l= 1; @@ -39,7 +36,7 @@ set @arg_sal= 600; set @arg_l= 9; set @arg_r= 10; execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ; -select * from personnel; +select * from t1; id emp salary l r 1 Jerry 1000.00 1 12 2 Bert 900.00 2 3 @@ -47,8 +44,8 @@ id emp salary l r 4 Donna 800.00 5 6 5 Eddie 700.00 7 8 6 Fred 600.00 9 10 -prepare st_raise_base from 'update personnel set salary = salary * ( 1 + ? ) where r - l = 1'; -prepare st_raise_mgr from 'update personnel set salary = salary + ? where r - l > 1'; +prepare st_raise_base from 'update t1 set salary = salary * ( 1 + ? ) where r - l = 1'; +prepare st_raise_mgr from 'update t1 set salary = salary + ? where r - l > 1'; set @arg_percent= .10; set @arg_amount= 100; execute st_raise_base using @arg_percent; @@ -57,7 +54,7 @@ execute st_raise_base using @arg_percent; execute st_raise_mgr using @arg_amount; execute st_raise_base using @arg_percent; execute st_raise_mgr using @arg_amount; -select * from personnel; +select * from t1; id emp salary l r 1 Jerry 1300.00 1 12 2 Bert 1197.90 2 3 @@ -65,4 +62,4 @@ id emp salary l r 4 Donna 1064.80 5 6 5 Eddie 931.70 7 8 6 Fred 798.60 9 10 -drop table personnel; +drop table t1; diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 6dcdb0feab1..ccf2945d488 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -184,7 +184,7 @@ f3 int ); insert into t5( f1, f2, f3) values( 9, 'recreated table', 9); execute stmt2 ; -ERROR 42S22: Unknown column 't5.a' in 'field list' +ERROR 42S22: Unknown column 'test.t5.a' in 'field list' drop table t5 ; prepare stmt1 from ' select * from t1 where a <= 2 ' ; execute stmt1 ; diff --git a/mysql-test/r/rpl_commit_after_flush.result b/mysql-test/r/rpl_commit_after_flush.result new file mode 100644 index 00000000000..d3aba779219 --- /dev/null +++ b/mysql-test/r/rpl_commit_after_flush.result @@ -0,0 +1,13 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +create table t1 (a int) engine=innodb; +begin; +insert into t1 values(1); +flush tables with read lock; +commit; +unlock tables; +drop table t1; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index fbd4f8e11dc..7820cd1d6ff 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1033,3 +1033,81 @@ a No aaa,bbb drop table t1,t2,t3,t4; +create table t1 as +(select _latin1'test') union +(select _latin1'TEST') union +(select _latin1'TeST'); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `test` char(4) NOT NULL default '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select count(*) from t1; +count(*) +1 +drop table t1; +create table t1 as +(select _latin1'test' collate latin1_bin) union +(select _latin1'TEST') union +(select _latin1'TeST'); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_latin1'test' collate latin1_bin` char(4) character set latin1 collate latin1_bin NOT NULL default '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select count(*) from t1; +count(*) +3 +drop table t1; +create table t1 as +(select _latin1'test') union +(select _latin1'TEST' collate latin1_bin) union +(select _latin1'TeST'); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `test` char(4) character set latin1 collate latin1_bin NOT NULL default '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select count(*) from t1; +count(*) +3 +drop table t1; +create table t1 as +(select _latin1'test') union +(select _latin1'TEST') union +(select _latin1'TeST' collate latin1_bin); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `test` char(4) character set latin1 collate latin1_bin NOT NULL default '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select count(*) from t1; +count(*) +3 +drop table t1; +create table t2 ( +a char character set latin1 collate latin1_swedish_ci, +b char character set latin1 collate latin1_bin); +create table t1 as +(select a from t2) union +(select b from t2); +ERROR HY000: Illegal mix of collations for operation 'UNION' +create table t1 as +(select a collate latin1_german1_ci from t2) union +(select b from t2); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a collate latin1_german1_ci` char(1) character set latin1 collate latin1_german1_ci default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 as +(select a from t2) union +(select b collate latin1_german1_ci from t2); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(1) character set latin1 collate latin1_german1_ci default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +drop table t2; diff --git a/mysql-test/t/ctype_sjis.test b/mysql-test/t/ctype_sjis.test index 1dd363c4910..68f4f7010e0 100644 --- a/mysql-test/t/ctype_sjis.test +++ b/mysql-test/t/ctype_sjis.test @@ -32,3 +32,12 @@ select hex(CONVERT(@utf81 USING sjis)); select hex(CONVERT(@utf82 USING sjis)); select hex(CONVERT(@utf83 USING sjis)); select hex(CONVERT(@utf84 USING sjis)); + +# +# Allow to insert extra CP932 characters +# into a SJIS column +# +create table t1 (a char(10) character set sjis); +insert into t1 values (0x878A); +select hex(a) from t1; +drop table t1; diff --git a/mysql-test/t/ctype_uca.test b/mysql-test/t/ctype_uca.test index cbb2bd7ba4b..708a31d637e 100644 --- a/mysql-test/t/ctype_uca.test +++ b/mysql-test/t/ctype_uca.test @@ -7,8 +7,35 @@ DROP TABLE IF EXISTS t1; # # Test Unicode collations. # - set names utf8; + +# +# Check trailing spaces +# +set collation_connection=utf8_unicode_ci; + +select 'a' = 'a', 'a' = 'a ', 'a ' = 'a'; + +select 'a\t' = 'a' , 'a\t' < 'a' , 'a\t' > 'a'; +select 'a\t' = 'a ', 'a\t' < 'a ', 'a\t' > 'a '; + +select 'a' = 'a\t', 'a' < 'a\t', 'a' > 'a\t'; +select 'a ' = 'a\t', 'a ' < 'a\t', 'a ' > 'a\t'; + +select 'a a' > 'a', 'a \t' < 'a'; + +# +# Bug #5679 utf8_unicode_ci LIKE--trailing % doesn't equal zero characters +# +CREATE TABLE t ( + c char(20) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +INSERT INTO t VALUES ('a'),('ab'),('aba'); +ALTER TABLE t ADD INDEX (c); +SELECT c FROM t WHERE c LIKE 'a%'; +#should find 3 rows but only found 2 +DROP TABLE t; + create table t1 (c1 char(10) character set utf8 collate utf8_bin); # diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index c74bb59ae6b..238cd6daef3 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -33,6 +33,14 @@ select 'A' like 'a'; select 'A' like 'a' collate utf8_bin; select _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD0B1,_utf8 '%'); +# Bug #6040: can't retrieve records with umlaut +# characters in case insensitive manner. +# Case insensitive search LIKE comparison +# was broken for multibyte characters: +select convert(_latin1'Günter André' using utf8) like CONVERT(_latin1'GÜNTER%' USING utf8); +select CONVERT(_koi8r'×ÁÓÑ' USING utf8) LIKE CONVERT(_koi8r'÷áóñ' USING utf8); +select CONVERT(_koi8r'÷áóñ' USING utf8) LIKE CONVERT(_koi8r'×ÁÓÑ' USING utf8); + # # Check the following: # "a" == "a " diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index e7bcd81a15e..eb506a58870 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -94,3 +94,16 @@ CREATE TABLE t1 (d varchar(6), k int); INSERT INTO t1 VALUES (NULL, 2); SELECT GREATEST(d,d) FROM t1 WHERE k=2; DROP TABLE t1; + +# +# Bug #6138: mod and doubles +# + +select 1197.90 mod 50; +select 5.1 mod 3, 5.1 mod -3, -5.1 mod 3, -5.1 mod -3; + +# +# Test for mod and signed integers +# + +select 5 mod 3, 5 mod -3, -5 mod 3, -5 mod -3; diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test index 3a9afa7453b..f86be0c95b9 100644 --- a/mysql-test/t/grant2.test +++ b/mysql-test/t/grant2.test @@ -6,13 +6,21 @@ SET NAMES binary; # +# prepare playground before tests +--disable_warnings +drop database if exists mysqltest; +--enable_warnings +delete from mysql.user where user like 'mysqltest\_%'; +delete from mysql.db where user like 'mysqltest\_%'; +delete from mysql.tables_priv where user like 'mysqltest\_%'; +delete from mysql.columns_priv where user like 'mysqltest\_%'; +flush privileges; + + # # wild_compare fun # -delete from mysql.user where user like 'mysqltest\_%'; -delete from mysql.db where user like 'mysqltest\_%'; -flush privileges; grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option; connect (user1,localhost,mysqltest_1,,); connection user1; @@ -31,3 +39,33 @@ delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; flush privileges; + +# +# Bug #6173: One can circumvent missing UPDATE privilege if he has SELECT +# and INSERT privilege for table with primary key +# +create database mysqltest; +grant INSERT, SELECT on mysqltest.* to mysqltest_1@localhost; +flush privileges; +use mysqltest; +create table t1 (id int primary key, data varchar(255)); + +connect (mrbad, localhost, mysqltest_1,,); +connection mrbad; +show grants for current_user(); +use mysqltest; +insert into t1 values (1, 'I can''t change it!'); +--error 1044 +update t1 set data='I can change it!' where id = 1; +# This should not be allowed since it too require UPDATE privilege. +--error 1044 +insert into t1 values (1, 'XXX') on duplicate key update data= 'I can change it!'; +select * from t1; + +connection default; +drop table t1; +drop database mysqltest; +use test; +delete from mysql.user where user like 'mysqltest\_%'; +delete from mysql.db where user like 'mysqltest\_%'; +flush privileges; diff --git a/mysql-test/t/innodb-lock-master.opt b/mysql-test/t/innodb-lock-master.opt new file mode 100644 index 00000000000..403fcde87ed --- /dev/null +++ b/mysql-test/t/innodb-lock-master.opt @@ -0,0 +1 @@ +--innodb-table-lock=1 diff --git a/mysql-test/t/innodb-lock.test b/mysql-test/t/innodb-lock.test index 33baec32549..2bbbac82ad5 100644 --- a/mysql-test/t/innodb-lock.test +++ b/mysql-test/t/innodb-lock.test @@ -1,5 +1,15 @@ -- source include/have_innodb.inc +# +# Check and select innodb lock type +# + +select @@innodb_table_locks; + +# +# Testing of explicit table locks with enforced table locks +# + connect (con1,localhost,root,,); connect (con2,localhost,root,,); @@ -8,9 +18,11 @@ drop table if exists t1; --enable_warnings # -# Testing of explicit table locks +# Testing of explicit table locks with enforced table locks # +set @@innodb_table_locks=1; + connection con1; create table t1 (id integer, x integer) engine=INNODB; insert into t1 values(0, 0); @@ -41,3 +53,46 @@ select * from t1; commit; drop table t1; + +# +# Try with old lock method (where LOCK TABLE is ignored by InnoDB) +# + +set @@innodb_table_locks=0; + +create table t1 (id integer primary key, x integer) engine=INNODB; +insert into t1 values(0, 0),(1,1),(2,2); +commit; +SELECT * from t1 where id = 0 FOR UPDATE; + +connection con2; +set autocommit=0; +set @@innodb_table_locks=0; + +# The following statement should work becase innodb doesn't check table locks +lock table t1 write; + +connection con1; + +# This will be locked by MySQL +--send +update t1 set x=10 where id = 2; +--sleep 2 + +connection con2; + +# Note that we will get a deadlock if we try to select any rows marked +# for update by con1 ! + +SELECT * from t1 where id = 2; +UPDATE t1 set x=3 where id = 2; +commit; +SELECT * from t1; +commit; +unlock tables; + +connection con1; +reap; +commit; +select * from t1; +drop table t1; diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test new file mode 100644 index 00000000000..c18dfe1e25c --- /dev/null +++ b/mysql-test/t/mysqltest.test @@ -0,0 +1,78 @@ + +# ============================================================================ +# +# Test of mysqltest itself +# +# ============================================================================ + +# ---------------------------------------------------------------------------- +# Positive case(statement) +# ---------------------------------------------------------------------------- + +select otto from (select 1 as otto) as t1; +# expectation = response +!$0 select otto from (select 1 as otto) as t1; +--error 0 +select otto from (select 1 as otto) as t1; + +# expectation <> response +-- // !$1054 select otto from (select 1 as otto) as t1; +-- // --error 1054 +-- // select otto from (select 1 as otto) as t1; + + +# ---------------------------------------------------------------------------- +# Negative case(statement): +# The dervied table t1 does not contain a column named 'friedrich' . +# --> ERROR 42S22: Unknown column 'friedrich' in 'field list and +# --> 1054: Unknown column 'friedrich' in 'field list' +# ---------------------------------------------------------------------------- + +# expectation <> response +#!$0 select friedrich from (select 1 as otto) as t1; +#--error 0 +#select friedrich from (select 1 as otto) as t1; + +# expectation = response +!$1054 select friedrich from (select 1 as otto) as t1; +--error 1054 +select friedrich from (select 1 as otto) as t1; + +# The following unmasked unsuccessful statement must give +# 1. mysqltest gives a 'failed' +# 2. does not produce a r/<test case>.reject file !!! +# PLEASE uncomment it and check it's effect +#select friedrich from (select 1 as otto) as t1; + + +# ---------------------------------------------------------------------------- +# Tests for the new feature - SQLSTATE error code matching +# Positive case(statement) +# ---------------------------------------------------------------------------- + +# expectation = response +!S00000 select otto from (select 1 as otto) as t1; + +--error S00000 +select otto from (select 1 as otto) as t1; + +# expectation <> response +#!S42S22 select otto from (select 1 as otto) as t1; +#--error S42S22 +#select otto from (select 1 as otto) as t1; + + +# ---------------------------------------------------------------------------- +# Negative case(statement) +# ---------------------------------------------------------------------------- + +# expectation = response +!S42S22 select friedrich from (select 1 as otto) as t1; +--error S42S22 +select friedrich from (select 1 as otto) as t1; + +# expectation !=response +#!S00000 select friedrich from (select 1 as otto) as t1; +#--error S00000 +#select friedrich from (select 1 as otto) as t1; + diff --git a/mysql-test/t/ndb_basic.test b/mysql-test/t/ndb_basic.test index 5f32a8016bf..e79815bbeb1 100644 --- a/mysql-test/t/ndb_basic.test +++ b/mysql-test/t/ndb_basic.test @@ -358,3 +358,16 @@ select b,test.t1.attr1 from test.t1, t2 where test.t1.pk1 < a; drop table test.t1, t2; drop database mysqltest; +# +# test support of char(0) +# + +use test; +create table t1 (a int primary key, b char(0)); +insert into t1 values (1,""); +insert into t1 values (2,NULL); +select * from t1 order by a; +select * from t1 order by b; +select * from t1 where b IS NULL; +select * from t1 where b IS NOT NULL; +drop table t1; diff --git a/mysql-test/t/ndb_index_ordered.test b/mysql-test/t/ndb_index_ordered.test index 2a94475df13..64291c8ab97 100644 --- a/mysql-test/t/ndb_index_ordered.test +++ b/mysql-test/t/ndb_index_ordered.test @@ -23,6 +23,11 @@ select * from t1 where b > 4 order by b; select * from t1 where b < 4 order by b; select * from t1 where b <= 4 order by b; +# Test of reset_bounds +select tt1.* from t1 as tt1, t1 as tt2 use index(b) where tt1.b = tt2.b order by tt1.b; +select a, b, c from t1 where a!=2 and c=6; +select a, b, c from t1 where a!=2 order by a; + # # Here we should add some "explain select" to verify that the ordered index is # used for these queries. diff --git a/mysql-test/t/ndb_lock.test b/mysql-test/t/ndb_lock.test index c0389dced44..39a8655b972 100644 --- a/mysql-test/t/ndb_lock.test +++ b/mysql-test/t/ndb_lock.test @@ -39,3 +39,32 @@ commit; connection con2; select * from t1 order by x; commit; + +drop table t1; + +### +# Bug#6020 +create table t1 (pk integer not null primary key, u int not null, o int not null, + unique(u), key(o)) engine = ndb; +insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); + +lock tables t1 write; +delete from t1 where pk = 1; +unlock tables; +select * from t1 order by pk; +insert into t1 values (1,1,1); + +lock tables t1 write; +delete from t1 where u = 1; +unlock tables; +select * from t1 order by pk; +insert into t1 values (1,1,1); + +lock tables t1 write; +delete from t1 where o = 1; +unlock tables; +select * from t1 order by pk; +insert into t1 values (1,1,1); + +drop table t1; + diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 7cbcd50245f..04ab8aa62a8 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -390,3 +390,28 @@ set @var=3; execute stmt using @var; drop table t1; deallocate prepare stmt; + +# +# A test case for Bug#6050 "EXECUTE stmt reports ambiguous fieldnames with +# identical tables from different schemata" +# Check that field name resolving in prepared statements works OK. +# +create database mysqltest1; +create table t1 (a int); +create table mysqltest1.t1 (a int); +select * from t1, mysqltest1.t1; +prepare stmt from "select * from t1, mysqltest1.t1"; +execute stmt; +execute stmt; +execute stmt; +drop table t1; +drop table mysqltest1.t1; +drop database mysqltest1; +deallocate prepare stmt; +select '1.1' as a, '1.2' as a UNION SELECT '2.1', '2.2'; +prepare stmt from +"select '1.1' as a, '1.2' as a UNION SELECT '2.1', '2.2'"; +execute stmt; +execute stmt; +execute stmt; +deallocate prepare stmt; diff --git a/mysql-test/t/ps_10nestset.test b/mysql-test/t/ps_10nestset.test index 2c6009af9de..d2adaca689e 100644 --- a/mysql-test/t/ps_10nestset.test +++ b/mysql-test/t/ps_10nestset.test @@ -8,28 +8,29 @@ # Source: http://kris.koehntopp.de/artikel/sql-self-references (dated 1999) # Source: http://dbmsmag.com/9603d06.html (dated 1996) -use test; - -drop table if exists personnel; +--disable_warnings +drop table if exists t1; +--enable_warnings # "Nested Set": This table represents an employee list with a hierarchy tree. # The tree is not modeled by "parent" links but rather by showing the "left" # and "right" border of any person's "region". By convention, "l" < "r". # As it is a tree, these "regions" of two persons A and B are either disjoint, -# or A's region is completely contained in B's (B is A's boss), or vice versa. -# See the references for more info. +# or A's region is completely contained in B's (B.l < A.l < A.r < B.r: +# B is A's boss), or vice versa. +# Any other overlaps violate the model. See the references for more info. -create table personnel ( +create table t1 ( id INTEGER AUTO_INCREMENT PRIMARY KEY, emp CHAR(10) NOT NULL, salary DECIMAL(6,2) NOT NULL, l INTEGER NOT NULL, r INTEGER NOT NULL); -prepare st_ins from 'insert into personnel set emp = ?, salary = ?, l = ?, r = ?'; +prepare st_ins from 'insert into t1 set emp = ?, salary = ?, l = ?, r = ?'; # Initial employee list: -# Jerry ( Bert ( ) Chuck ( Donna ( ) Eddie ( ) Fred ( ) ) ) +# Jerry ( Bert () Chuck ( Donna () Eddie () Fred () ) ) set @arg_nam= 'Jerry'; set @arg_sal= 1000; set @arg_l= 1; set @arg_r= 12; execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ; set @arg_nam= 'Bert'; set @arg_sal= 900; set @arg_l= 2; set @arg_r= 3; @@ -43,11 +44,11 @@ execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ; set @arg_nam= 'Fred'; set @arg_sal= 600; set @arg_l= 9; set @arg_r= 10; execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ; -select * from personnel; +select * from t1; # Three successive raises, each one is 100 units for managers, 10 percent for others. -prepare st_raise_base from 'update personnel set salary = salary * ( 1 + ? ) where r - l = 1'; -prepare st_raise_mgr from 'update personnel set salary = salary + ? where r - l > 1'; +prepare st_raise_base from 'update t1 set salary = salary * ( 1 + ? ) where r - l = 1'; +prepare st_raise_mgr from 'update t1 set salary = salary + ? where r - l > 1'; let $1= 3; set @arg_percent= .10; set @arg_amount= 100; @@ -58,6 +59,14 @@ while ($1) dec $1; } -select * from personnel; +select * from t1; + +# Waiting for the resolution of bug#6138 +# # Now, increase salary to a multiple of 50 +# prepare st_round from 'update t1 set salary = salary + ? - ( salary MOD ? )'; +# set @arg_round= 50; +# execute st_round using @arg_round, @arg_round; +# +# select * from t1; -drop table personnel; +drop table t1; diff --git a/mysql-test/t/ps_11bugs.test b/mysql-test/t/ps_11bugs.test index d0aeaf265bb..5945b140645 100644 --- a/mysql-test/t/ps_11bugs.test +++ b/mysql-test/t/ps_11bugs.test @@ -97,7 +97,7 @@ drop table t1; # end of bug#1644 -# bug#1677: Prepared statement two-table join returns no rows when one is expected +# bug#1676: Prepared statement two-table join returns no rows when one is expected create table t1( cola varchar(50) not null, diff --git a/mysql-test/t/rpl_commit_after_flush.test b/mysql-test/t/rpl_commit_after_flush.test new file mode 100644 index 00000000000..62c89b3aae6 --- /dev/null +++ b/mysql-test/t/rpl_commit_after_flush.test @@ -0,0 +1,17 @@ +source include/master-slave.inc; +source include/have_innodb.inc; +create table t1 (a int) engine=innodb; +begin; +insert into t1 values(1); +flush tables with read lock; +commit; +save_master_pos; +connection slave; +sync_with_master; +# cleanup +connection master; +unlock tables; +drop table t1; +save_master_pos; +connection slave; +sync_with_master; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index c5e72e85835..6e16a2b02aa 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -595,3 +595,58 @@ select a as a from t3 union select "1"; select a as a from t4 union select a from t3; select a as a from t1 union select a from t4; drop table t1,t2,t3,t4; + +# +# Bug #6139 UNION doesn't understand collate in the column of second select +# +create table t1 as +(select _latin1'test') union +(select _latin1'TEST') union +(select _latin1'TeST'); +show create table t1; +select count(*) from t1; +drop table t1; + +create table t1 as +(select _latin1'test' collate latin1_bin) union +(select _latin1'TEST') union +(select _latin1'TeST'); +show create table t1; +select count(*) from t1; +drop table t1; + +create table t1 as +(select _latin1'test') union +(select _latin1'TEST' collate latin1_bin) union +(select _latin1'TeST'); +show create table t1; +select count(*) from t1; +drop table t1; + +create table t1 as +(select _latin1'test') union +(select _latin1'TEST') union +(select _latin1'TeST' collate latin1_bin); +show create table t1; +select count(*) from t1; +drop table t1; + +create table t2 ( +a char character set latin1 collate latin1_swedish_ci, +b char character set latin1 collate latin1_bin); +--error 1271 +create table t1 as +(select a from t2) union +(select b from t2); +create table t1 as +(select a collate latin1_german1_ci from t2) union +(select b from t2); +show create table t1; +drop table t1; +create table t1 as +(select a from t2) union +(select b collate latin1_german1_ci from t2); +show create table t1; +drop table t1; +drop table t2; + |