diff options
| author | monty@mysql.com <> | 2004-10-29 19:26:52 +0300 |
|---|---|---|
| committer | monty@mysql.com <> | 2004-10-29 19:26:52 +0300 |
| commit | afbe601302fc59c498437321b296ed6c8d360564 (patch) | |
| tree | 23bcc9a71fe7237887a111b158e30f5a6bb665d3 /mysql-test/t | |
| parent | 67456bb970cc949ceb5779b230592e455843c35c (diff) | |
| parent | 541883f9d89a8d38affba60bf9506289a6232da1 (diff) | |
| download | mariadb-git-afbe601302fc59c498437321b296ed6c8d360564.tar.gz | |
merge with 4.1
Diffstat (limited to 'mysql-test/t')
95 files changed, 3905 insertions, 224 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index eb35aa90fe2..e46027ae8d9 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -207,6 +207,7 @@ alter table t1 change a a text character set cp1251; select a,hex(a) from t1; alter table t1 change a a char(10) character set koi8r; select a,hex(a) from t1; +delete from t1; # # Test ALTER TABLE .. CHARACTER SET .. diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index 5c2e73e5af7..f55aea6e104 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1297,4 +1297,6 @@ INSERT INTO t2 VALUES (2,011401,37,'breaking','dreaded','Steinberg','W'); INSERT INTO t2 VALUES (3,011402,37,'Romans','scholastics','jarring',''); INSERT INTO t2 VALUES (4,011403,37,'intercepted','audiology','tinily',''); SELECT * FROM t2; +OPTIMIZE TABLE t2; +SELECT * FROM t2; drop table t1, t2; diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test index e5986e6755d..8e11a3d68a5 100644 --- a/mysql-test/t/auto_increment.test +++ b/mysql-test/t/auto_increment.test @@ -168,3 +168,41 @@ update t1 set a=NULL where b=13; update t1 set a=500 where b=14; select * from t1 order by b; drop table t1; + +# +# Test of behavior of ALTER TABLE when coulmn containing NULL or zeroes is +# converted to AUTO_INCREMENT column +# +create table t1 (a bigint); +insert into t1 values (1), (2), (3), (NULL), (NULL); +alter table t1 modify a bigint not null auto_increment primary key; +select * from t1; +drop table t1; + +create table t1 (a bigint); +insert into t1 values (1), (2), (3), (0), (0); +alter table t1 modify a bigint not null auto_increment primary key; +select * from t1; +drop table t1; + +# We still should be able to preserve zero in NO_AUTO_VALUE_ON_ZERO mode +create table t1 (a bigint); +insert into t1 values (0), (1), (2), (3); +set sql_mode=NO_AUTO_VALUE_ON_ZERO; +alter table t1 modify a bigint not null auto_increment primary key; +set sql_mode= ''; +select * from t1; +drop table t1; + +# It also sensible to preserve zeroes if we are converting auto_increment +# column to auto_increment column (or not touching it at all, which is more +# common case probably) +create table t1 (a int auto_increment primary key , b int null); +set sql_mode=NO_AUTO_VALUE_ON_ZERO; +insert into t1 values (0,1),(1,2),(2,3); +select * from t1; +set sql_mode= ''; +alter table t1 modify b varchar(255); +insert into t1 values (0,4); +select * from t1; +drop table t1; diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test index 7ee7d18439d..069ec758ba2 100644 --- a/mysql-test/t/bdb.test +++ b/mysql-test/t/bdb.test @@ -897,3 +897,35 @@ commit; truncate t1; select * from t1; drop table t1; + +# +# Check that BDB works fine with a string which is +# longer than 255 bytes for multibyte characters. +# +SET NAMES utf8; +create table t1 (a varchar(255) character set utf8) engine=bdb; +set @a:= convert(repeat(_latin1 0xFF, 255) using utf8); +insert into t1 values (@a); +select a, length(a), char_length(a) from t1; +drop table t1; +SET NAMES latin1; + +# +# Bug #5832 SELECT doesn't return records in some cases +# +CREATE TABLE t1 ( + id int unsigned NOT NULL auto_increment, + list_id smallint unsigned NOT NULL, + term TEXT NOT NULL, + PRIMARY KEY(id), + INDEX(list_id, term(4)) +) ENGINE=BDB CHARSET=utf8; +INSERT INTO t1 SET list_id = 1, term = "letterc"; +INSERT INTO t1 SET list_id = 1, term = "letterb"; +INSERT INTO t1 SET list_id = 1, term = "lettera"; +INSERT INTO t1 SET list_id = 1, term = "letterd"; +SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc"); +SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb"); +SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera"); +SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd"); +DROP TABLE t1; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index e2deb792d47..e5681dedbac 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -95,3 +95,16 @@ select cast("2001-1-1" as datetime) = "2001-01-01 00:00:00"; select cast("1:2:3" as TIME) = "1:02:03"; select cast(NULL as DATE); select cast(NULL as BINARY); + +# +# Bug #5228 ORDER BY CAST(enumcol) sorts incorrectly under certain conditions +# +CREATE TABLE t1 (a enum ('aac','aab','aaa') not null); +INSERT INTO t1 VALUES ('aaa'),('aab'),('aac'); +# these two should be in enum order +SELECT a, CAST(a AS CHAR) FROM t1 ORDER BY CAST(a AS UNSIGNED) ; +SELECT a, CAST(a AS CHAR(3)) FROM t1 ORDER BY CAST(a AS CHAR(2)), a; +# these two should be in alphabetic order +SELECT a, CAST(a AS UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR) ; +SELECT a, CAST(a AS CHAR(2)) FROM t1 ORDER BY CAST(a AS CHAR(3)), a; +DROP TABLE t1; diff --git a/mysql-test/t/client_test.test b/mysql-test/t/client_test.test new file mode 100644 index 00000000000..b56e8038d9b --- /dev/null +++ b/mysql-test/t/client_test.test @@ -0,0 +1,2 @@ +--disable_result_log +--exec $TESTS_BINDIR/client_test --testcase --user=root --socket=$MASTER_MYSOCK --port=$MYSQL_TCP_PORT diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 26c527ca7cb..6f222eedec1 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -272,8 +272,11 @@ insert into t2 values(10),(20); create table t3 like t1; show create table t3; select * from t3; +# Disable PS becasue of @@warning_count +--disable_ps_protocol create table if not exists t3 like t1; select @@warning_count; +--enable_ps_protocol create temporary table t3 like t2; show create table t3; select * from t3; diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test index e29e43496af..1c9576c1c56 100644 --- a/mysql-test/t/ctype_latin1_de.test +++ b/mysql-test/t/ctype_latin1_de.test @@ -86,6 +86,23 @@ select * from t1 where word like binary 0xDF; select * from t1 where word like CAST(0xDF as CHAR); drop table t1; +# +# Bug #5447 Select does not find records +# +CREATE TABLE t1 ( + autor varchar(80) NOT NULL default '', + PRIMARY KEY (autor) +); +INSERT INTO t1 VALUES ('Powell, B.'),('Powell, Bud.'),('Powell, L. H.'),('Power, H.'), +('Poynter, M. A. L. Lane'),('Poynting, J. H. und J. J. Thomson.'),('Pozzi, S(amuel-Jean).'), +('Pozzi, Samuel-Jean.'),('Pozzo, A.'),('Pozzoli, Serge.'); +SELECT * FROM t1 WHERE autor LIKE 'Poz%' ORDER BY autor; +DROP TABLE t1; + +# +# Test of special character in german collation +# + CREATE TABLE t1 ( s1 CHAR(5) CHARACTER SET latin1 COLLATE latin1_german2_ci ); diff --git a/mysql-test/t/ctype_recoding.test b/mysql-test/t/ctype_recoding.test index 82d0643b577..5f417352d95 100644 --- a/mysql-test/t/ctype_recoding.test +++ b/mysql-test/t/ctype_recoding.test @@ -121,3 +121,13 @@ CREATE TABLE `good` (a int); SET NAMES utf8; --error 1300 CREATE TABLE `good` (a int); + + +# +# Test that we produce a warnign when conversion loses data. +# +set names latin1; +create table t1 (a char(10) character set koi8r, b text character set koi8r); +insert into t1 values ('test','test'); +insert into t1 values ('',''); +drop table t1; diff --git a/mysql-test/t/ctype_sjis.test b/mysql-test/t/ctype_sjis.test index 1dd363c4910..c910812ef8a 100644 --- a/mysql-test/t/ctype_sjis.test +++ b/mysql-test/t/ctype_sjis.test @@ -32,3 +32,22 @@ 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; + +# +# Bug #6206 ENUMs are not case sensitive even if declared BINARY +# +create table t1(c enum(0x9353,0x9373) character set sjis); +show create table t1; +insert into t1 values (0x9353); +insert into t1 values (0x9373); +select hex(c) from t1; +drop table t1; diff --git a/mysql-test/t/ctype_tis620-master.opt b/mysql-test/t/ctype_tis620-master.opt deleted file mode 100644 index 69d47c06e42..00000000000 --- a/mysql-test/t/ctype_tis620-master.opt +++ /dev/null @@ -1 +0,0 @@ ---default-character-set=tis620 diff --git a/mysql-test/t/ctype_tis620.test b/mysql-test/t/ctype_tis620.test index 9bffc2b7ab8..92a9eada05f 100644 --- a/mysql-test/t/ctype_tis620.test +++ b/mysql-test/t/ctype_tis620.test @@ -69,7 +69,7 @@ CREATE TABLE t1 ( recid int(11) NOT NULL auto_increment, dyninfo text, PRIMARY KEY (recid) -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET tis620; show create table t1; @@ -112,7 +112,7 @@ SELECT 'a\0' < 'a '; SELECT 'a\t' < 'a'; SELECT 'a\t' < 'a '; -CREATE TABLE t1 (a char(10) not null); +CREATE TABLE t1 (a char(10) not null) CHARACTER SET tis620; INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a '); SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1; DROP TABLE t1; diff --git a/mysql-test/t/ctype_uca.test b/mysql-test/t/ctype_uca.test index 187d21f9ab7..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); # @@ -180,3 +207,231 @@ select group_concat(c1 order by c1) from t1 group by c1 collate utf8_slovak_ci; select group_concat(c1 order by c1) from t1 group by c1 collate utf8_spanish2_ci; select group_concat(c1 order by c1) from t1 group by c1 collate utf8_roman_ci; +drop table t1; + +# +# Bug#5324 +# +SET NAMES utf8; +#test1 +CREATE TABLE t1 (c varchar(255) NOT NULL COLLATE utf8_general_ci, INDEX (c)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B403B11F770308 USING utf8)); +#Check one row +SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8) +COLLATE utf8_general_ci; +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B4 USING utf8)); +#Check two rows +SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8) +COLLATE utf8_general_ci ORDER BY c; +DROP TABLE t1; +#test2 +CREATE TABLE t1 (c varchar(255) NOT NULL COLLATE ucs2_unicode_ci, INDEX (c)); +INSERT INTO t1 VALUES (_ucs2 0x039C03C903B403B11F770308); +#Check one row +SELECT * FROM t1 WHERE c LIKE _ucs2 0x039C0025 COLLATE ucs2_unicode_ci; +INSERT INTO t1 VALUES (_ucs2 0x039C03C903B4); +#Check two rows +SELECT * FROM t1 WHERE c LIKE _ucs2 0x039C0025 +COLLATE ucs2_unicode_ci ORDER BY c; +DROP TABLE t1; +#test 3 +CREATE TABLE t1 (c varchar(255) NOT NULL COLLATE utf8_unicode_ci, INDEX (c)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B403B11F770308 USING utf8)); +#Check one row row +SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8) COLLATE utf8_unicode_ci; +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B4 USING utf8)); +#Check two rows +SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8) +COLLATE utf8_unicode_ci ORDER BY c; +DROP TABLE t1; + +CREATE TABLE t1 ( + col1 CHAR(32) CHARACTER SET utf8 NOT NULL +); + +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0041004100410627 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0041004100410628 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0041004100410647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0041004100410648 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0633064A0651062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062D06330646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0642064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06320627062F0647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062806310627064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064706450647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F062706460634062C0648064A06270646064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A90647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A06270631064A062E USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062706460642064406270628 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064A0631062706460650 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627062F064806270631062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280631062706480646200C06310627 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062E064806270646062F0647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0648 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A062D062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0623062B064A0631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06220646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0642063106270631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06AF06310641062A0647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06270646062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0634062E0635064A0651062A064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0628062706310632 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06270633062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063906A90633 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06270648060C USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F0631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062D062F0648062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0628064A0633062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0648067E0646062C USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06330627064406AF064A060C USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063306270644 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064606450627064A0646062F0647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A06280631064A0632 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645062C06440633 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280648062F060C USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0628064A0646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06350641062D0627062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064A0646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A9062A06270628 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x068606340645 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062E06480631062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0686064706310647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06420648064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06450635064506510645 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06310627 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0646063406270646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645064A200C062F0647062F060C USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0647063106860646062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06390645064400BB USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A9064806340634 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0628 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064706500646064A064606AF USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627062D063306270646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064A062706310634062706370631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06450646062A06340631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0634062F0647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F0633062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A064806270646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0647064506270646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064806510644 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0634062E064A0635 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F0627062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280627 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A064106270648062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062D06270644062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A064106A906510631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063A064406280647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F06270631062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064A06A9064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06270632 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063106470628063106270646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064606470636062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064506340631064806370647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064A063106270646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0646064A0632 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064A06A9 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645062D064206510642 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0637063106270632 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064106310647064606AF USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0645062F06510646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064A063106270646064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280648062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A90627063106470627064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06270648 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0639063106350647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064506480631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0633064A06270633064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064A063106270646060C USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062D064806320647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063906440645 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F062706460634 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06450642062706440627062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F064A06AF0631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0648064A06980647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0646062706450647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064506480631062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0628062D062B USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0628063106310633064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064606480634062A0647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06450646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A064606470627 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0622064606860647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F064806310647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064206270645062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x067E0631062F062706320645 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0698062706460648064A0647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0648064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F06390648062A0650 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063306500631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F0646064A0633064F0646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063106270633 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0647064A0626062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0639064406480645200C063406310642064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280639062F0627064B USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645062F063106330647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062206410631064A06420627064A064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F06270646063406AF06270647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06440646062F0646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x067E064A06480633062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0647064606AF06270645064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x067E0633 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0622063A06270632 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062C064606AF USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062C064706270646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F064806510645 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063406470631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A9064506280631064A062C USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06450646062A06420644 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A90631062F0646062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06470645 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06310641062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06220646062C0627 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064506270646062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0627 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062706A9062A06280631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064606380631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F06480644062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F06480628062706310647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064606330628062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645063306270639062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0634062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06480632064A0631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645062E062A06270631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06330641064A0631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064606AF0644064A0633 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0642064A200C06320627062F0647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280627063206AF0634062A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0647064506330631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06220644064506270646064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06270634 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06220645062F0647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A906270631064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x067E0631062F0627062E062A0647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063906440645064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627062F0628064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062D062F0651 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064606280648062F060C USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06480644064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063906480636060C USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06340627064A062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064506470645 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06220646060C USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06470645063306310634 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A90627064606480646 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062E0627064606480627062F06AF064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06AF06310645064A USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280648062C0648062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062206480631062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F0648 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A90627064506440627064B USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064A06A9062F064A06AF0631 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06AF USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F064406280633062A0647 USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280648062F0646062F USING utf8)); +INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06450647064506270646 USING utf8)); +SELECT HEX(CONVERT(col1 USING ucs2)) FROM t1 ORDER BY col1 COLLATE utf8_persian_ci, col1 COLLATE utf8_bin; +DROP TABLE t1; diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 2c531d4e5d2..0c8bdd6a94d 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'Gnter Andr' using utf8) like CONVERT(_latin1'GNTER%' 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 " @@ -219,6 +227,29 @@ drop table t1; # # Bug 4521: unique key prefix interacts poorly with utf8 +# InnoDB: keys with prefix compression, case insensitive collation. +# +--disable_warnings +create table t1 (c varchar(30) character set utf8, unique(c(10))) engine=innodb; +--enable_warnings +insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); +insert into t1 values ('aaaaaaaaaa'); +--error 1062 +insert into t1 values ('aaaaaaaaaaa'); +--error 1062 +insert into t1 values ('aaaaaaaaaaaa'); +insert into t1 values (repeat('b',20)); +select c c1 from t1 where c='1'; +select c c2 from t1 where c='2'; +select c c3 from t1 where c='3'; +select c cx from t1 where c='x'; +select c cy from t1 where c='y'; +select c cz from t1 where c='z'; +select c ca10 from t1 where c='aaaaaaaaaa'; +select c cb20 from t1 where c=repeat('b',20); +drop table t1; +# +# Bug 4521: unique key prefix interacts poorly with utf8 # MYISAM: fixed length keys, case insensitive collation # create table t1 (c char(3) character set utf8, unique (c(2))); @@ -244,7 +275,35 @@ insert into t1 values ('ꪪꪪ'); --error 1062 insert into t1 values ('ꪪꪪꪪ'); drop table t1; - +# +# Bug 4521: unique key prefix interacts poorly with utf8 +# InnoDB: fixed length keys, case insensitive collation +# +--disable_warnings +create table t1 (c char(3) character set utf8, unique (c(2))) engine=innodb; +--enable_warnings +insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); +insert into t1 values ('a'); +insert into t1 values ('aa'); +--error 1062 +insert into t1 values ('aaa'); +insert into t1 values ('b'); +insert into t1 values ('bb'); +--error 1062 +insert into t1 values ('bbb'); +insert into t1 values ('а'); +insert into t1 values ('аа'); +--error 1062 +insert into t1 values ('ааа'); +insert into t1 values ('б'); +insert into t1 values ('бб'); +--error 1062 +insert into t1 values ('ббб'); +insert into t1 values ('ꪪ'); +insert into t1 values ('ꪪꪪ'); +--error 1062 +insert into t1 values ('ꪪꪪꪪ'); +drop table t1; # # Bug 4531: unique key prefix interacts poorly with utf8 # Check HEAP+HASH, case insensitive collation @@ -454,6 +513,20 @@ INSERT INTO t1 VALUES ('str2'); select * from t1 where str='str'; drop table t1; +# Bug#4594: column index make = failed for gbk, but like works +# Check InnoDB +# +--disable_warnings +create table t1 ( + str varchar(255) character set utf8 not null, + key str (str(2)) +) engine=innodb; +--enable_warnings +INSERT INTO t1 VALUES ('str'); +INSERT INTO t1 VALUES ('str2'); +select * from t1 where str='str'; +drop table t1; + # the same for HEAP+BTREE # @@ -492,3 +565,97 @@ INSERT INTO t1 VALUES ('str2'); select * from t1 where str='str'; drop table t1; +# +# Bug #5397: Crash with varchar binary and LIKE +# +CREATE TABLE t1 (a varchar(32) BINARY) CHARACTER SET utf8; +INSERT INTO t1 VALUES ('test'); +SELECT a FROM t1 WHERE a LIKE '%te'; +DROP TABLE t1; + +# +# Bug #5723: length(<varchar utf8 field>) returns varying results +# +--disable_warnings +SET NAMES utf8; +--disable_warnings +CREATE TABLE t1 ( + subject varchar(255) character set utf8 collate utf8_unicode_ci, + p varchar(15) character set utf8 +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +--enable_warnings +INSERT INTO t1 VALUES ('谷川俊二と申しますが、インターネット予約の会員登録をしましたところ、メールアドレスを間違えてしまい会員ＩＤが受け取ることが出来ませんでした。間違えアドレスはtani-shun@n.vodafone.ne.jpを書き込みました。どうすればよいですか？ その他、住所等は間違えありません。連絡ください。よろしくお願いします。m(__)m','040312-000057'); +INSERT INTO t1 VALUES ('aaa','bbb'); +SELECT length(subject) FROM t1; +SELECT length(subject) FROM t1 ORDER BY 1; +DROP TABLE t1; + +# +# Bug #5832 SELECT doesn't return records in some cases +# +CREATE TABLE t1 ( + id int unsigned NOT NULL auto_increment, + list_id smallint unsigned NOT NULL, + term TEXT NOT NULL, + PRIMARY KEY(id), + INDEX(list_id, term(4)) +) ENGINE=MYISAM CHARSET=utf8; +INSERT INTO t1 SET list_id = 1, term = "letterc"; +INSERT INTO t1 SET list_id = 1, term = "letterb"; +INSERT INTO t1 SET list_id = 1, term = "lettera"; +INSERT INTO t1 SET list_id = 1, term = "letterd"; +SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc"); +SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb"); +SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera"); +SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd"); +DROP TABLE t1; + + +# +# Bug #6043 erratic searching for diacriticals in indexed MyISAM UTF-8 table +# +SET NAMES latin1; +CREATE TABLE t1 ( + id int unsigned NOT NULL auto_increment, + list_id smallint unsigned NOT NULL, + term text NOT NULL, + PRIMARY KEY(id), + INDEX(list_id, term(19)) +) ENGINE=MyISAM CHARSET=utf8; +INSERT INTO t1 set list_id = 1, term = "testtest"; +INSERT INTO t1 set list_id = 1, term = "testetest"; +INSERT INTO t1 set list_id = 1, term = "testtest"; +SELECT id, term FROM t1 where (list_id = 1) AND (term = "testtest"); +SELECT id, term FROM t1 where (list_id = 1) AND (term = "testetest"); +SELECT id, term FROM t1 where (list_id = 1) AND (term = "testtest"); +DROP TABLE t1; + +# +# Bug #6019 SELECT tries to use too short prefix index on utf8 data +# +set names utf8; +--disable_warnings +create table t1 ( + a int primary key, + b varchar(6), + index b3(b(3)) +) engine=innodb character set=utf8; +--enable_warnings +insert into t1 values(1,'foo'),(2,'foobar'); +select * from t1 where b like 'foob%'; +--disable_warnings +alter table t1 engine=bdb; +select * from t1 where b like 'foob%'; +drop table t1; + +# +# Test for calculate_interval_lengths() function +# +create table t1 ( + a enum('петя','вася','анюта') character set utf8 not null default 'анюта', + b set('петя','вася','анюта') character set utf8 not null default 'анюта' +); +create table t2 select concat(a,_utf8'') as a, concat(b,_utf8'')as b from t1; +show create table t2; +drop table t2; +drop table t1; diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test index f769fe7af04..c369a9c85d5 100644 --- a/mysql-test/t/date_formats.test +++ b/mysql-test/t/date_formats.test @@ -118,8 +118,11 @@ SET datetime_format=default; # Test of str_to_date # +# PS doesn't support fraction of a seconds +--disable_ps_protocol select str_to_date(concat('15-01-2001',' 2:59:58.999'), concat('%d-%m-%Y',' ','%H:%i:%s.%f')); +--enable_ps_protocol create table t1 (date char(30), format char(30) not null); insert into t1 values @@ -153,7 +156,8 @@ insert into t1 values ('15-01-20', '%d-%m-%y'), ('15-2001-1', '%d-%Y-%c'); -# Use through protocol functions +# PS doesn't support fractional seconds +--disable_ps_protocol select date,format,str_to_date(date, format) as str_to_date from t1; # Use as a string select date,format,concat('',str_to_date(date, format)) as con from t1; @@ -198,6 +202,7 @@ select date,format,str_to_date(date, format) as str_to_date from t1; select date,format,concat(str_to_date(date, format),'') as con from t1; drop table t1; +--enable_ps_protocol # # Test of get_format @@ -221,6 +226,8 @@ insert into t1 values ('2004-07-14'),('2005-07-14'); select date_format(d,"%d") from t1 order by 1; drop table t1; +# PS doesn't support fractional seconds +--disable_ps_protocol select str_to_date("2003-....01ABCD-02 10:11:12.0012", "%Y-%.%m%@-%d %H:%i:%S.%f") as a; @@ -249,3 +256,4 @@ select str_to_date("2003-01-02 10:11:12.0012ABCD", "%Y-%m-%d %H:%i:%S.%f") as f1 select str_to_date("2003-04-05 g", "%Y-%m-%d") as f1, str_to_date("2003-04-05 10:11:12.101010234567", "%Y-%m-%d %H:%i:%S.%f") as f2; +--enable_ps_protocol diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 56c8ce77627..5f60445d765 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -71,6 +71,16 @@ delete from t1 where 3 > 2; select count(*) from t1; drop table t1; +# +# Bug #5733: Table handler error with self-join multi-table DELETE +# + +create table t1 (a int not null auto_increment primary key, b char(32)); +insert into t1 (b) values ('apple'), ('apple'); +select * from t1; +delete t1 from t1, t1 as t2 where t1.b = t2.b and t1.a > t2.a; +select * from t1; +drop table t1; # # IGNORE option diff --git a/mysql-test/t/flush_table.test b/mysql-test/t/flush_table.test index 8bee94cf21f..afb30d21da7 100644 --- a/mysql-test/t/flush_table.test +++ b/mysql-test/t/flush_table.test @@ -16,7 +16,8 @@ drop table t1; # # In the following test FLUSH TABLES produces a deadlock -# (hang forever) if the fix for bug#3565 is missing. +# (hang forever) if the fix for BUG #3565 is missing. +# And it shows that handler tables are re-opened after flush (BUG #4286). # create table t1(table_id char(20) primary key); create table t2(table_id char(20) primary key); @@ -31,28 +32,43 @@ handler a1 read first limit 9; handler a2 read first limit 9; handler t2 read first limit 9; flush tables; ---error 1109; handler a1 read first limit 9; ---error 1109; handler a2 read first limit 9; ---error 1109; handler t2 read first limit 9; # +--error 1066 handler t1 open as a1; +--error 1066 handler t1 open as a2; +--error 1066 handler t2 open; handler a1 read first limit 9; handler a2 read first limit 9; handler t2 read first limit 9; flush table t1; ---error 1109; handler a1 read first limit 9; ---error 1109; handler a2 read first limit 9; handler t2 read first limit 9; flush table t2; ---error 1109; handler t2 close; drop table t1; drop table t2; +# +# The fix for BUG #4286 cannot restore the position after a flush. +# +create table t1(table_id char(20) primary key); +insert into t1 values ('Record-01'); +insert into t1 values ('Record-02'); +insert into t1 values ('Record-03'); +insert into t1 values ('Record-04'); +insert into t1 values ('Record-05'); +handler t1 open; +handler t1 read first limit 1; +handler t1 read next limit 1; +handler t1 read next limit 1; +flush table t1; +handler t1 read next limit 1; +handler t1 read next limit 1; +handler t1 close; +drop table t1; diff --git a/mysql-test/t/fulltext_cache.test b/mysql-test/t/fulltext_cache.test index 15f32fdb5a0..234deab91e6 100644 --- a/mysql-test/t/fulltext_cache.test +++ b/mysql-test/t/fulltext_cache.test @@ -29,13 +29,13 @@ INSERT INTO t2 VALUES (6,2,'um chocolate Snickers'); INSERT INTO t2 VALUES (7,1,'Bife'); INSERT INTO t2 VALUES (8,1,'Pizza de Salmao'); -SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi') +SELECT t1.q, t2.item, t2.id, round(MATCH t2.item AGAINST ('sushi'),8) as x FROM t1, t2 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi' IN BOOLEAN MODE) as x FROM t1, t2 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; -SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi') +SELECT t1.q, t2.item, t2.id, round(MATCH t2.item AGAINST ('sushi'),8) as x FROM t2, t1 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi' IN BOOLEAN MODE) diff --git a/mysql-test/t/fulltext_left_join.test b/mysql-test/t/fulltext_left_join.test index f8dcfb2aad6..96751ef8678 100644 --- a/mysql-test/t/fulltext_left_join.test +++ b/mysql-test/t/fulltext_left_join.test @@ -24,7 +24,7 @@ INSERT INTO t2 VALUES('123', 'moi'); INSERT INTO t2 VALUES('123', 'lui'); INSERT INTO t2 VALUES('456', 'lui'); -select match(t1.texte,t1.sujet,t1.motsclefs) against('droit') +select round(match(t1.texte,t1.sujet,t1.motsclefs) against('droit'),5) from t1 left join t2 on t2.id=t1.id; select match(t1.texte,t1.sujet,t1.motsclefs) against('droit' IN BOOLEAN MODE) from t1 left join t2 on t2.id=t1.id; diff --git a/mysql-test/t/fulltext_multi.test b/mysql-test/t/fulltext_multi.test index c312a5938b2..3a622a551bc 100644 --- a/mysql-test/t/fulltext_multi.test +++ b/mysql-test/t/fulltext_multi.test @@ -17,7 +17,7 @@ INSERT INTO t1 VALUES (1,'lala lolo lili','oooo aaaa pppp'); INSERT INTO t1 VALUES (2,'asdf fdsa','lkjh fghj'); INSERT INTO t1 VALUES (3,'qpwoei','zmxnvb'); -SELECT a, MATCH b AGAINST ('lala lkjh') FROM t1; -SELECT a, MATCH c AGAINST ('lala lkjh') FROM t1; -SELECT a, MATCH b,c AGAINST ('lala lkjh') FROM t1; +SELECT a, round(MATCH b AGAINST ('lala lkjh'),5) FROM t1; +SELECT a, round(MATCH c AGAINST ('lala lkjh'),5) FROM t1; +SELECT a, round(MATCH b,c AGAINST ('lala lkjh'),5) FROM t1; drop table t1; diff --git a/mysql-test/t/func_compress.test b/mysql-test/t/func_compress.test index 79de99276a6..7b70289d2c0 100644 --- a/mysql-test/t/func_compress.test +++ b/mysql-test/t/func_compress.test @@ -35,3 +35,10 @@ select length(a) from t1; select length(uncompress(a)) from t1; drop table t1; + +# +# Bug #5497: a problem with large strings +# + +set @@max_allowed_packet=1048576*100; +select compress(repeat('aaaaaaaaaa', 10000000)) is null; diff --git a/mysql-test/t/func_concat.test b/mysql-test/t/func_concat.test index 147367a6d27..0cf1502b10e 100644 --- a/mysql-test/t/func_concat.test +++ b/mysql-test/t/func_concat.test @@ -25,3 +25,12 @@ FROM t1 GROUP BY new LIMIT 1; SELECT number, alpha, CONCAT_WS('<------------------>',number,alpha) AS new FROM t1 GROUP BY new LIMIT 1; drop table t1; + +# +# Bug #5540: a problem with double type +# + +create table t1 (a char(4), b double, c date, d tinyint(4)); +insert into t1 values ('AAAA', 105, '2003-03-01', 1); +select * from t1 where concat(A,C,B,D) = 'AAAA2003-03-011051'; +drop table t1; diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 3f671377c4e..e0737a42221 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -240,3 +240,40 @@ SELECT t1.A_ID, GROUP_CONCAT(t2.B_DESC) AS B_DESC FROM t1 LEFT JOIN t2 ON t1.A_I DROP TABLE t1; DROP TABLE t2; +# +# blobs +# + +create table t1 (a int, b text); +insert into t1 values (1, 'bb'), (1, 'ccc'), (1, 'a'), (1, 'bb'), (1, 'ccc'); +insert into t1 values (2, 'BB'), (2, 'CCC'), (2, 'A'), (2, 'BB'), (2, 'CCC'); +select group_concat(b) from t1 group by a; +select group_concat(distinct b) from t1 group by a; +select group_concat(b order by b) from t1 group by a; +select group_concat(distinct b order by b) from t1 group by a; +set local group_concat_max_len=4; +select group_concat(b) from t1 group by a; +select group_concat(distinct b) from t1 group by a; +select group_concat(b order by b) from t1 group by a; +select group_concat(distinct b order by b) from t1 group by a; + +# +# long blobs +# + +insert into t1 values (1, concat(repeat('1', 300), '2')), +(1, concat(repeat('1', 300), '2')), (1, concat(repeat('0', 300), '1')), +(2, concat(repeat('1', 300), '2')), (2, concat(repeat('1', 300), '2')), +(2, concat(repeat('0', 300), '1')); +set local group_concat_max_len=1024; +select group_concat(b) from t1 group by a; +select group_concat(distinct b) from t1 group by a; +select group_concat(b order by b) from t1 group by a; +select group_concat(distinct b order by b) from t1 group by a; +set local group_concat_max_len=400; +select group_concat(b) from t1 group by a; +select group_concat(distinct b) from t1 group by a; +select group_concat(b order by b) from t1 group by a; +select group_concat(distinct b order by b) from t1 group by a; + +drop table t1; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 1416d5f0f9a..e67d4fa3757 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -30,7 +30,11 @@ select grp, sum(a)+count(a)+avg(a)+std(a)+variance(a)+bit_or(a)+bit_and(a)+min(a create table t2 (grp int, a bigint unsigned, c char(10)); insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp; + +# REPLACE ... SELECT doesn't yet work with PS +--disable_ps_protocol replace into t2 select grp, a, c from t1 limit 2,1; +--enable_ps_protocol select * from t2; drop table t1,t2; @@ -419,3 +423,43 @@ execute stmt1; execute stmt1; deallocate prepare stmt1; drop table t1; + +# +# Bug #5406 min/max optimization for empty set +# + +CREATE TABLE t1 (a int primary key); +INSERT INTO t1 VALUES (1),(2),(3),(4); + +SELECT MAX(a) FROM t1 WHERE a > 5; +SELECT MIN(a) FROM t1 WHERE a < 0; + +DROP TABLE t1; + +# +# Bug #5555 GROUP BY enum_field" returns incorrect results +# + +CREATE TABLE t1 ( + id int(10) unsigned NOT NULL auto_increment, + val enum('one','two','three') NOT NULL default 'one', + PRIMARY KEY (id) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + +INSERT INTO t1 VALUES +(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two'); + +select val, count(*) from t1 group by val; +drop table t1; + +CREATE TABLE t1 ( + id int(10) unsigned NOT NULL auto_increment, + val set('one','two','three') NOT NULL default 'one', + PRIMARY KEY (id) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + +INSERT INTO t1 VALUES +(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two'); + +select val, count(*) from t1 group by val; +drop table t1; diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test index 7048d188604..693773b18c8 100644 --- a/mysql-test/t/func_if.test +++ b/mysql-test/t/func_if.test @@ -56,3 +56,7 @@ insert t1 values (1),(2); select if(1>2,a,avg(a)) from t1; drop table t1; +# +# Bug #5595 NULLIF() IS NULL returns false if NULLIF() returns NULL +# +SELECT NULLIF(5,5) IS NULL, NULLIF(5,5) IS NOT NULL; diff --git a/mysql-test/t/func_sapdb.test b/mysql-test/t/func_sapdb.test index ea845d836ec..3f547739679 100644 --- a/mysql-test/t/func_sapdb.test +++ b/mysql-test/t/func_sapdb.test @@ -53,17 +53,23 @@ select subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999"); select subtime("01:00:00.999999", "02:00:00.999998"); select subtime("02:01:01.999999", "01:01:01.999999"); +# PS doesn't support fractional seconds +--disable_ps_protocol select timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002"); select timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002"); select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002"); select timediff("1997-12-31 23:59:59.000001","23:59:59.000001"); select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001"); +--enable_ps_protocol select maketime(10,11,12); select maketime(25,11,12); select maketime(-25,11,12); -#Extraction functions +# Extraction functions + +# PS doesn't support fractional seconds +--disable_ps_protocol select timestamp("2001-12-01", "01:01:01.999999"); select timestamp("2001-13-01", "01:01:01.000001"); select timestamp("2001-12-01", "25:01:01"); @@ -75,6 +81,7 @@ select date("1997-13-31 23:59:59.000001"); select time("1997-12-31 23:59:59.000001"); select time("1997-12-31 25:59:59.000001"); select microsecond("1997-12-31 23:59:59.000001"); +--enable_ps_protocol create table t1 select makedate(1997,1) as f1, @@ -87,7 +94,10 @@ select makedate(1997,1) as f1, date("1997-12-31 23:59:59.000001") as f8, time("1997-12-31 23:59:59.000001") as f9; describe t1; +# PS doesn't support fractional seconds +--disable_ps_protocol select * from t1; +--enable_ps_protocol create table test(t1 datetime, t2 time, t3 time, t4 datetime); insert into test values @@ -100,8 +110,11 @@ insert into test values ('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01'); SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test; +# PS doesn't support fractional seconds +--disable_ps_protocol SELECT TIMEDIFF(t1, t4) As ttt, TIMEDIFF(t2, t3) As qqq, TIMEDIFF(t3, t2) As eee, TIMEDIFF(t2, t4) As rrr from test; +--enable_ps_protocol drop table t1, test; @@ -109,4 +122,7 @@ select addtime("-01:01:01.01", "-23:59:59.1") as a; select microsecond("1997-12-31 23:59:59.01") as a; select microsecond(19971231235959.01) as a; select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a; +# PS doesn't support fractional seconds +--disable_ps_protocol select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f"); +--enable_ps_protocol diff --git a/mysql-test/t/func_set.test b/mysql-test/t/func_set.test index d669739bcb4..98ef1e07bfe 100644 --- a/mysql-test/t/func_set.test +++ b/mysql-test/t/func_set.test @@ -47,3 +47,8 @@ select find_in_set(binary 'a',binary 'A,B,C'); select find_in_set('a',binary 'A,B,C'); select find_in_set(binary 'a', 'A,B,C'); +# +# Bug5513:FIND_IN_SET fails if set ends with a comma +# +select find_in_set('1','3,1,'); + diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index e7852df40b3..3eab694ee05 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -182,6 +182,13 @@ select a, quote(a), isnull(quote(a)), quote(a) is null, ifnull(quote(a), 'n') fr drop table t1; # +# Bug #5498: TRIM fails with LEADING or TRAILING if remstr = str +# + +select trim(trailing 'foo' from 'foo'); +select trim(leading 'foo' from 'foo'); + +# # Test collation and coercibility # 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/gis.test b/mysql-test/t/gis.test index e35b9996a44..739fced1f29 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -190,3 +190,97 @@ select AsText(a) from t1 where and MBRContains(GeomFromText('Polygon((0 0, 0 7, 7 7, 7 0, 0 0))'), a); drop table t1; + +CREATE TABLE t1 (Coordinates POINT NOT NULL, SPATIAL INDEX(Coordinates)); +INSERT INTO t1 VALUES(GeomFromText('POINT(383293632 1754448)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(564952612 157516260)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(903994614 180726515)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(98128178 141127631)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(862547902 799334546)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(341989013 850270906)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(803302376 93039099)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(857439153 817431356)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(319757546 343162742)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(826341972 717484432)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(305066789 201736238)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(626068992 616241497)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(55789424 755830108)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(802874458 312435220)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(153795660 551723671)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(242207428 537089292)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(553478119 807160039)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(694605552 457472733)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(987886554 792733729)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(598600363 850434457)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(592068275 940589376)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(700705362 395370650)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(33628474 558144514)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(212802006 353386020)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(901307256 39143977)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(70870451 206374045)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(240880214 696939443)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(822615542 296669638)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(452769551 625489999)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(609104858 606565210)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(177213669 851312285)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(143654501 730691787)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(658472325 838260052)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(188164520 646358878)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(630993781 786764883)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(496793334 223062055)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(727354258 197498696)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(618432704 760982731)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(755643210 831234710)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(114368751 656950466)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(870378686 185239202)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(863324511 111258900)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(882178645 685940052)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(407928538 334948195)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(311430051 17033395)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(941513405 488643719)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(868345680 85167906)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(219335507 526818004)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(923427958 407500026)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(173176882 554421738)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(194264908 669970217)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(777483793 921619165)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(867468912 395916497)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(682601897 623112122)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(227151206 796970647)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(280062588 97529892)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(982209849 143387099)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(208788792 864388493)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(829327151 616717329)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(199336688 140757201)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(633750724 140850093)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(629400920 502096404)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(226017998 848736426)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(28914408 149445955)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(256236452 202091290)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(703867693 450501360)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(872061506 481351486)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(372120524 739530418)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(877267982 54722420)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(362642540 104419188)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(851693067 642705127)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(201949080 833902916)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(786092225 410737872)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(698291409 615419376)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(27455201 897628096)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(756176576 661205925)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(38478189 385577496)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(163302328 264496186)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(234313922 192216735)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(413942141 490550373)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(394308025 117809834)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(941051732 266369530)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(599161319 313172256)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(5899948 476429301)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(367894677 368542487)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(580848489 219587743)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(11247614 782797569)')); +drop table t1; + +create table t1 select POINT(1,3); +show create table t1; +drop table t1; diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index a892b761964..4b1601c245f 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -189,6 +189,75 @@ show grants for test11@localhost; delete from mysql.user where user='test11'; delete from mysql.db where user='test11'; +# +# Bug #5831: REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke everything +# +USE test; +CREATE TABLE t1 (a int ); +CREATE TABLE t2 LIKE t1; +CREATE TABLE t3 LIKE t1; +CREATE TABLE t4 LIKE t1; +CREATE TABLE t5 LIKE t1; +CREATE TABLE t6 LIKE t1; +CREATE TABLE t7 LIKE t1; +CREATE TABLE t8 LIKE t1; +CREATE TABLE t9 LIKE t1; +CREATE TABLE t10 LIKE t1; +CREATE DATABASE testdb1; +CREATE DATABASE testdb2; +CREATE DATABASE testdb3; +CREATE DATABASE testdb4; +CREATE DATABASE testdb5; +CREATE DATABASE testdb6; +CREATE DATABASE testdb7; +CREATE DATABASE testdb8; +CREATE DATABASE testdb9; +CREATE DATABASE testdb10; +GRANT ALL ON testdb1.* TO testuser@localhost; +GRANT ALL ON testdb2.* TO testuser@localhost; +GRANT ALL ON testdb3.* TO testuser@localhost; +GRANT ALL ON testdb4.* TO testuser@localhost; +GRANT ALL ON testdb5.* TO testuser@localhost; +GRANT ALL ON testdb6.* TO testuser@localhost; +GRANT ALL ON testdb7.* TO testuser@localhost; +GRANT ALL ON testdb8.* TO testuser@localhost; +GRANT ALL ON testdb9.* TO testuser@localhost; +GRANT ALL ON testdb10.* TO testuser@localhost; +GRANT SELECT ON test.t1 TO testuser@localhost; +GRANT SELECT ON test.t2 TO testuser@localhost; +GRANT SELECT ON test.t3 TO testuser@localhost; +GRANT SELECT ON test.t4 TO testuser@localhost; +GRANT SELECT ON test.t5 TO testuser@localhost; +GRANT SELECT ON test.t6 TO testuser@localhost; +GRANT SELECT ON test.t7 TO testuser@localhost; +GRANT SELECT ON test.t8 TO testuser@localhost; +GRANT SELECT ON test.t9 TO testuser@localhost; +GRANT SELECT ON test.t10 TO testuser@localhost; +GRANT SELECT (a) ON test.t1 TO testuser@localhost; +GRANT SELECT (a) ON test.t2 TO testuser@localhost; +GRANT SELECT (a) ON test.t3 TO testuser@localhost; +GRANT SELECT (a) ON test.t4 TO testuser@localhost; +GRANT SELECT (a) ON test.t5 TO testuser@localhost; +GRANT SELECT (a) ON test.t6 TO testuser@localhost; +GRANT SELECT (a) ON test.t7 TO testuser@localhost; +GRANT SELECT (a) ON test.t8 TO testuser@localhost; +GRANT SELECT (a) ON test.t9 TO testuser@localhost; +GRANT SELECT (a) ON test.t10 TO testuser@localhost; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser@localhost; +SHOW GRANTS FOR testuser@localhost; +DROP USER testuser@localhost; +DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; +DROP DATABASE testdb1; +DROP DATABASE testdb2; +DROP DATABASE testdb3; +DROP DATABASE testdb4; +DROP DATABASE testdb5; +DROP DATABASE testdb6; +DROP DATABASE testdb7; +DROP DATABASE testdb8; +DROP DATABASE testdb9; +DROP DATABASE testdb10; + # # just SHOW PRIVILEGES test 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/group_by.test b/mysql-test/t/group_by.test index d6d1922c10f..59983594c32 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -456,3 +456,12 @@ INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5); SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL; explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL; DROP TABLE t1; + +# Test for BUG#5400: GROUP_CONCAT returns everything twice. +create table t1 ( col1 int, col2 int ); +insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2); +select group_concat( distinct col1 ) as alias from t1 + group by col2 having alias like '%'; + +drop table t1; + diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test index 685f438712c..3de8e8ae784 100644 --- a/mysql-test/t/handler.test +++ b/mysql-test/t/handler.test @@ -138,3 +138,211 @@ handler t1 read a=(1) where b=1; handler t1 close; drop table t1; +# +# Check if two database names beginning the same are seen as different. +# +# This database begins like the usual 'test' database. +# +--disable_warnings +drop database if exists test_test; +--enable_warnings +create database test_test; +use test_test; +create table t1(table_id char(20) primary key); +insert into t1 values ('test_test.t1'); +insert into t1 values (''); +handler t1 open; +handler t1 read first limit 9; +create table t2(table_id char(20) primary key); +insert into t2 values ('test_test.t2'); +insert into t2 values (''); +handler t2 open; +handler t2 read first limit 9; +# +# This is the usual 'test' database. +# +use test; +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1(table_id char(20) primary key); +insert into t1 values ('test.t1'); +insert into t1 values (''); +--error 1066 +handler t1 open; +# +# Check accesibility of all the tables. +# +use test; +--error 1064 +handler test.t1 read first limit 9; +--error 1064 +handler test_test.t1 read first limit 9; +handler t1 read first limit 9; +--error 1064 +handler test_test.t2 read first limit 9; +handler t2 read first limit 9; + +# +# Cleanup. +# + +--error 1064 +handler test_test.t1 close; +handler t1 close; +drop table test_test.t1; +--error 1064 +handler test_test.t2 close; +handler t2 close; +drop table test_test.t2; +drop database test_test; + +# +use test; +--error 1064 +handler test.t1 close; +--error 1109 +handler t1 close; +drop table test.t1; + +# +# BUG#4335 +# +--disable_warnings +drop database if exists test_test; +drop table if exists t1; +drop table if exists t2; +drop table if exists t3; +--enable_warnings +create database test_test; +use test_test; +create table t1 (c1 char(20)); +insert into t1 values ('test_test.t1'); +create table t3 (c1 char(20)); +insert into t3 values ('test_test.t3'); +handler t1 open; +handler t1 read first limit 9; +handler t1 open h1; +handler h1 read first limit 9; +use test; +create table t1 (c1 char(20)); +create table t2 (c1 char(20)); +create table t3 (c1 char(20)); +insert into t1 values ('t1'); +insert into t2 values ('t2'); +insert into t3 values ('t3'); +--error 1066 +handler t1 open; +--error 1066 +handler t2 open t1; +--error 1066 +handler t3 open t1; +handler t1 read first limit 9; +--error 1064 +handler test.t1 close; +--error 1066 +handler test.t1 open h1; +--error 1066 +handler test_test.t1 open h1; +handler test_test.t3 open h3; +handler test.t1 open h2; +handler t1 read first limit 9; +handler h1 read first limit 9; +handler h2 read first limit 9; +handler h3 read first limit 9; +handler h2 read first limit 9; +--error 1064 +handler test.h1 close; +handler t1 close; +handler h1 close; +handler h2 close; +--error 1109 +handler t1 read first limit 9; +--error 1109 +handler h1 read first limit 9; +--error 1109 +handler h2 read first limit 9; +handler h3 read first limit 9; +handler h3 read first limit 9; +use test_test; +handler h3 read first limit 9; +--error 1064 +handler test.h3 read first limit 9; +handler h3 close; +use test; +drop table t3; +drop table t2; +drop table t1; +drop database test_test; + +# +# Test if fix for BUG#4286 correctly closes handler tables. +# +create table t1 (c1 char(20)); +insert into t1 values ("t1"); +handler t1 open as h1; +handler h1 read first limit 9; +create table t2 (c1 char(20)); +insert into t2 values ("t2"); +handler t2 open as h2; +handler h2 read first limit 9; +create table t3 (c1 char(20)); +insert into t3 values ("t3"); +handler t3 open as h3; +handler h3 read first limit 9; +create table t4 (c1 char(20)); +insert into t4 values ("t4"); +handler t4 open as h4; +handler h4 read first limit 9; +create table t5 (c1 char(20)); +insert into t5 values ("t5"); +handler t5 open as h5; +handler h5 read first limit 9; +# close first +alter table t1 engine=MyISAM; +--error 1109; +handler h1 read first limit 9; +handler h2 read first limit 9; +handler h3 read first limit 9; +handler h4 read first limit 9; +handler h5 read first limit 9; +# close last +alter table t5 engine=MyISAM; +--error 1109; +handler h1 read first limit 9; +handler h2 read first limit 9; +handler h3 read first limit 9; +handler h4 read first limit 9; +--error 1109; +handler h5 read first limit 9; +# close middle +alter table t3 engine=MyISAM; +--error 1109; +handler h1 read first limit 9; +handler h2 read first limit 9; +--error 1109; +handler h3 read first limit 9; +handler h4 read first limit 9; +--error 1109; +handler h5 read first limit 9; +handler h2 close; +handler h4 close; +# close all depending handler tables +handler t1 open as h1_1; +handler t1 open as h1_2; +handler t1 open as h1_3; +handler h1_1 read first limit 9; +handler h1_2 read first limit 9; +handler h1_3 read first limit 9; +alter table t1 engine=MyISAM; +--error 1109; +handler h1_1 read first limit 9; +--error 1109; +handler h1_2 read first limit 9; +--error 1109; +handler h1_3 read first limit 9; +drop table t1; +drop table t2; +drop table t3; +drop table t4; +drop table t5; diff --git a/mysql-test/t/heap.test b/mysql-test/t/heap.test index 37fc5a43227..e1776245d9e 100644 --- a/mysql-test/t/heap.test +++ b/mysql-test/t/heap.test @@ -164,3 +164,13 @@ CREATE TABLE `job_titles` ( SELECT MAX(job_title_id) FROM job_titles; DROP TABLE job_titles; + +# +# Test of delete with NOT NULL +# (Bug #6082) +# + +CREATE TABLE t1 (a INT NOT NULL, B INT, KEY(B)) ENGINE=HEAP; +INSERT INTO t1 VALUES(1,1), (1,NULL); +SELECT * FROM t1 WHERE B is not null; +DROP TABLE t1; diff --git a/mysql-test/t/innodb-lock.test b/mysql-test/t/innodb-lock.test index 33baec32549..a3b6f8993f2 100644 --- a/mysql-test/t/innodb-lock.test +++ b/mysql-test/t/innodb-lock.test @@ -1,5 +1,17 @@ -- source include/have_innodb.inc +# +# Check and select innodb lock type +# + +set global innodb_table_locks=1; + +select @@innodb_table_locks; + +# +# Testing of explicit table locks with enforced table locks +# + connect (con1,localhost,root,,); connect (con2,localhost,root,,); @@ -8,9 +20,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 +55,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/insert.test b/mysql-test/t/insert.test index c01be1b5992..c56bc74877e 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -3,7 +3,7 @@ # --disable_warnings -drop table if exists t1,t2; +drop table if exists t1,t2,t3; --enable_warnings create table t1 (a int not null); @@ -87,12 +87,14 @@ use mysqltest; create table t1 (c int); insert into mysqltest.t1 set mysqltest.t1.c = '1'; drop database mysqltest; +use test; # # Test of wrong values for float data (bug #2082) # -use test; +# PS gives sligthly different numbers for max-float/max-double +--disable_ps_protocol create table t1(number int auto_increment primary key, original_value varchar(50), f_double double, f_float float, f_double_7_2 double(7,2), f_float_4_3 float (4,3), f_double_u double unsigned, f_float_u float unsigned, f_double_15_1_u double(15,1) unsigned, f_float_3_1_u float (3,1) unsigned); set @value= "aa"; @@ -136,10 +138,9 @@ set @value= -1; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); --query_vertical select * from t1 where number =last_insert_id() -use test; ---disable_warnings -drop table if exists t1,t2,t3; ---enable_warnings +drop table t1; +--enable_ps_protocol + create table t1(id1 int not null auto_increment primary key, t char(12)); create table t2(id2 int not null, t char(12)); create table t3(id3 int not null, t char(12), index(id3)); @@ -166,4 +167,4 @@ enable_query_log; select count(*) from t2; insert into t2 select t1.* from t1, t2 t, t3 where t1.id1 = t.id2 and t.id2 = t3.id3; select count(*) from t2; -drop table if exists t1,t2,t3; +drop table t1,t2,t3; diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 234a5ec0eef..db5dc8d91da 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -122,7 +122,10 @@ insert into t2 values (2,"t2:2"), (3,"t2:3"); --error 1062 insert into t1 select * from t2; select * from t1; +# REPLACE .. SELECT is not yet supported by PS +--disable_ps_protocol replace into t1 select * from t2; +--enable_ps_protocol select * from t1; drop table t1,t2; diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test index d2a70208022..fc54ce88f8a 100644 --- a/mysql-test/t/insert_update.test +++ b/mysql-test/t/insert_update.test @@ -35,10 +35,10 @@ create table t1(a int primary key, b int); insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5); select * from t1; -enable_info; +--enable_info insert into t1 values(4,14),(5,15),(6,16),(7,17),(8,18) on duplicate key update b=b+10; -disable_info; +--disable_info select * from t1; diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test index 4e4f6ddef8b..4a3631d3918 100644 --- a/mysql-test/t/key.test +++ b/mysql-test/t/key.test @@ -256,3 +256,47 @@ select c from t1 where c=0xD0B1212223D0B1D0B1D0B1D0B1D0B1; select t from t1 where t=0xD0B1D0B1212223D0B1D0B1D0B1D0B1; drop table t1; +# +# BUG#6151 - myisam index corruption +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( + c1 int, + c2 varbinary(240), + UNIQUE KEY (c1), + KEY (c2) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'\Z\Z\Z\Z'); +INSERT INTO t1 VALUES (2,'\Z\Z\Z\Z\Z\Z'); +INSERT INTO t1 VALUES (3,'\Z\Z\Z\Z'); +select c1 from t1 where c2='\Z\Z\Z\Z'; +DELETE FROM t1 WHERE (c1 = 1); +check table t1; +select c1 from t1 where c2='\Z\Z\Z\Z'; +DELETE FROM t1 WHERE (c1 = 3); +check table t1; +select c1 from t1 where c2='\Z\Z\Z\Z'; + +# +# test delete of keys in a different order +# +truncate table t1; +insert into t1 values(1,"aaaa"),(2,"aaab"),(3,"aaac"),(4,"aaccc"); +delete from t1 where c1=3; +delete from t1 where c1=1; +delete from t1 where c1=4; +check table t1; + +drop table t1; + +# +# Bug 6166: index prefix length of 0 not rejected +# +# this test should fail in 5.0 +# to fix it, remove #ifdef in +# file sql_yacc.yy(key_part) +# create dedicated error code for this and +# and change my_printf_error() to my_error + +--error 1105 +create table t1 (c char(10), index (c(0))); diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index fa095275182..cbda47ac864 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -53,6 +53,30 @@ reap; drop table t1; # +# Test problem when using locks with multi-updates +# It should not block when multi-update is reading on a read-locked table +# + +connection locker; +create table t1 (a int, b int); +create table t2 (c int, d int); +insert into t1 values(1,1); +insert into t1 values(2,2); +insert into t2 values(1,2); +lock table t1 read; +connection writer; +--sleep 2 +send update t1,t2 set c=a where b=d; +connection reader; +--sleep 2 +select c from t2; +connection writer; +reap; +connection locker; +drop table t1; +drop table t2; + +# # Test problem when using locks on many tables and droping a table that # is to-be-locked by another thread # diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test index c426ab9864b..d11cb62b04e 100644 --- a/mysql-test/t/metadata.test +++ b/mysql-test/t/metadata.test @@ -6,6 +6,8 @@ drop table if exists t1,t2; --enable_warnings --enable_metadata +# PS protocol gives slightly different metadata +--disable_ps_protocol # # First some simple tests diff --git a/mysql-test/t/multi_statement.test b/mysql-test/t/multi_statement.test index bd90275c9f5..862f2294641 100644 --- a/mysql-test/t/multi_statement.test +++ b/mysql-test/t/multi_statement.test @@ -1,3 +1,6 @@ +# PS doesn't support multi-statements +--disable_ps_protocol + select 1; delimiter ||||; select 2; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index d28619f0313..1e1490cd3f1 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -5,6 +5,11 @@ --disable_warnings drop table if exists t1,t2,t3; drop database if exists mysqltest; +--error 0,1141 +revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; +--error 0,1141 +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +delete from mysql.user where user=_binary'mysqltest_1'; --enable_warnings create table t1(id1 int not null auto_increment primary key, t char(12)); @@ -154,8 +159,6 @@ LOCK TABLES t1 write, t2 read; DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n; --error 1099 UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; -# The following should be fixed to not give an error ---error 1099 UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; unlock tables; LOCK TABLES t1 write, t2 write; @@ -385,7 +388,7 @@ select * from t2; drop table t1, t2; # -# prevelege chexk for multiupdate with other tables +# privilege check for multiupdate with other tables # connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); @@ -419,6 +422,17 @@ delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a drop table t1, t2, t3; # +# multi* unique updating table check +# +create table t1 (col1 int); +create table t2 (col1 int); +-- error 1093 +update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; +-- error 1093 +delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1; +drop table t1,t2; + +# # Test for bug #1980. # set @ttype_save=@@storage_engine; diff --git a/mysql-test/t/mysql_protocols.test b/mysql-test/t/mysql_protocols.test index 942ba2722d8..e5158586124 100644 --- a/mysql-test/t/mysql_protocols.test +++ b/mysql-test/t/mysql_protocols.test @@ -4,7 +4,7 @@ --exec echo "select ' ok' as '<default>'" | $MYSQL --exec echo "select ' ok' as 'TCP'" | $MYSQL --protocol=TCP --exec echo "select ' ok' as 'SOCKET'" | $MYSQL --protocol=SOCKET ---exec echo "select ' ok' as 'PIPE'" | $MYSQL --protocol=PIPE 2>&1 ---exec echo "select ' ok' as 'MEMORY'" | $MYSQL --protocol=MEMORY 2>&1 ---exec echo "select ' ok' as 'NullS'" | $MYSQL --protocol=NullS 2>&1 +--exec echo "select ' ok' as 'PIPE'" | $MYSQL --protocol=PIPE 2>&1 || true +--exec echo "select ' ok' as 'MEMORY'" | $MYSQL --protocol=MEMORY 2>&1 || true +--exec echo "select ' ok' as 'NullS'" | $MYSQL --protocol=NullS 2>&1 || true 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_alter_table.test b/mysql-test/t/ndb_alter_table.test index ffcd0b99745..22b1a0e476d 100644 --- a/mysql-test/t/ndb_alter_table.test +++ b/mysql-test/t/ndb_alter_table.test @@ -31,6 +31,7 @@ col4 varchar(4) not null, col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null, col6 int not null, to_be_deleted int) ENGINE=ndbcluster; show table status; +SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; insert into t1 values (0,4,3,5,"PENDING",1,7),(NULL,4,3,5,"PENDING",1,7),(31,4,3,5,"PENDING",1,7), (7,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7), (100,4,3,5,"PENDING",1,7), (99,4,3,5,"PENDING",1,7), (8,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7); show table status; @@ -46,6 +47,94 @@ select * from t1 order by col1; insert into t1 values (2, NULL,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00'); show table status; select * from t1 order by col1; +delete from t1; +insert into t1 values (0,0,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00'); +SET SQL_MODE=''; +insert into t1 values (1,0,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00'); +select * from t1 order by col1; +alter table t1 drop column col4_5; +insert into t1 values (2,0,4,3,5,"PENDING","EXTRA",'2004-01-01 00:00:00'); +select * from t1 order by col1; +drop table t1; + + +# +# Check that invalidating dictionary cache works +# + +CREATE TABLE t1 ( + a INT NOT NULL, + b INT NOT NULL +) ENGINE=ndbcluster; + +INSERT INTO t1 VALUES (9410,9412); + +connect (con1,localhost,,,test); +connect (con2,localhost,,,test); + +connection con1; +ALTER TABLE t1 ADD COLUMN c int not null; +select * from t1 order by a; + +connection con2; +select * from t1 order by a; +alter table t1 drop c; + +connection con1; +select * from t1 order by a; +drop table t1; + +connection con2; +--error 1146 +select * from t1 order by a; + +CREATE TABLE t1 ( + a INT NOT NULL PRIMARY KEY, + b INT NOT NULL +) ENGINE=ndbcluster; + +INSERT INTO t1 VALUES (0,1),(17,18); +select * from t1 order by a; +SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; +alter table t1 modify column a int not null auto_increment; +SET SQL_MODE=''; +select * from t1 order by a; +INSERT INTO t1 VALUES (0,19),(20,21); +select * from t1 order by a; +drop table t1; + +CREATE TABLE t1 ( + a INT NOT NULL PRIMARY KEY, + b INT NOT NULL +) ENGINE=ndbcluster; + +INSERT INTO t1 VALUES (0,1),(17,18); +select * from t1 order by a; +alter table t1 add c int not null unique auto_increment; +select c from t1 order by c; +INSERT INTO t1 VALUES (1,2,0),(18,19,4),(20,21,0); +select c from t1 order by c; drop table t1; +#--disable_warnings +#DROP TABLE IF EXISTS t2; +#--enable_warnings +#create table t2 (a int NOT NULL PRIMARY KEY) engine=myisam; +#let $1=12001; +#disable_query_log; +#while ($1) +#{ +# eval insert into t2 values($1); +# dec $1; +#} +#enable_query_log; +#alter table t2 engine=ndbcluster; +#alter table t2 add c int; +#--error 1297 +#delete from t2; +#to make sure we do a full table scan +#select count(*) from t2 where a+0 > 0; +#truncate table t2; +#select count(*) from t2; +#drop table t2; diff --git a/mysql-test/t/ndb_autodiscover.test b/mysql-test/t/ndb_autodiscover.test index 371a130291b..95b616fc7b2 100644 --- a/mysql-test/t/ndb_autodiscover.test +++ b/mysql-test/t/ndb_autodiscover.test @@ -1,7 +1,7 @@ -- source include/have_ndb.inc --disable_warnings -drop table if exists t1,t2,t3,t4,t5,t6,t9; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; --enable_warnings ################################################ @@ -122,7 +122,6 @@ create table t3( # IF NOT EXISTS wasn't specified show status like 'handler_discover%'; -SHOW TABLES FROM test; # now it should be discovered create table IF NOT EXISTS t3( @@ -145,38 +144,226 @@ show status like 'handler_discover%'; drop table t3; +################################################## +# Test that a table that already exists in NDB +# is discovered when SHOW TABLES +# is used +# + +flush status; + +create table t7( + id int not null primary key, + name char(255) +) engine=ndb; +create table t6( + id int not null primary key, + name char(255) +) engine=MyISAM; +insert into t7 values (1, "Explorer"); +insert into t6 values (2, "MyISAM table"); +select * from t7; +show status like 'handler_discover%'; + +# Remove the frm file from disk +flush tables; +system rm var/master-data/test/t7.frm ; + +show tables from test; +show status like 'handler_discover%'; + +# Remove the frm file from disk again +flush tables; +system rm var/master-data/test/t7.frm ; + +--replace_column 7 # 8 # 9 # 12 # 13 # 15 # +show table status; +show status like 'handler_discover%'; + +drop table t6, t7; + + ####################################################### -# Test that a table that already exists as frm file -# but not in NDB can be deleted from disk. +# Test that a table that has been dropped from NDB +# but still exists on disk, get a consistent error message +# saying "No such table existed" # -# Manual test -#flush status; +flush status; + +create table t4( + id int not null primary key, + name char(27) +) engine=ndb; +insert into t4 values (1, "Automatic"); +select * from t4; + +# Remove the table from NDB +system exec $NDB_TOOLS_DIR/ndb_drop_table -d test t4 > /dev/null ; + # -#create table t4( -# id int not null primary key, -# name char(27) -#) engine=ndb; -#insert into t4 values (1, "Automatic"); -#select * from t4; +# Test that correct error is returned +--error 1146 +select * from t4; +--error 1146 +select * from t4; + +show status like 'handler_discover%'; +drop table t4; + + +####################################################### +# Test that a table that has been dropped from NDB +# but still exists on disk is deleted from disk +# when SHOW TABLES is called # + +flush status; + +create table t4( + id int not null primary key, + id2 int, + name char(27) +) engine=ndb; +insert into t4 values (1, 76, "Automatic2"); +select * from t4; +flush tables; + # Remove the table from NDB -#system drop_tab -c "$NDB_CONNECTSTRING2" -d test t4 > /dev/null ; -#system drop_tab -c "host=localhost:2200;nodeid=5" -d test t4 > /dev/null ; +system exec $NDB_TOOLS_DIR/ndb_drop_table -d test t4 > /dev/null ; + +SHOW TABLES; + +--error 1146 +select * from t4; + +####################################################### +# Test SHOW TABLES ability to detect new and delete old +# tables. Test all at once using many tables # -#--error 1296 -#select * from t4; + +flush status; + +# Create tables +create table t1(id int) engine=ndbcluster; +create table t2(id int, b char(255)) engine=myisam; +create table t3(id int, c char(255)) engine=ndbcluster; +create table t4(id int) engine=myisam; +create table t5(id int, d char(56)) engine=ndbcluster; +create table t6(id int) engine=ndbcluster; +create table t7(id int) engine=ndbcluster; +create table t8(id int, e char(34)) engine=myisam; +create table t9(id int) engine=myisam; + +# Populate tables +insert into t2 values (2, "myisam table 2"); +insert into t3 values (3, "ndb table 3"); +insert into t5 values (5, "ndb table 5"); +insert into t6 values (6); +insert into t8 values (8, "myisam table 8"); +insert into t9 values (9); + +# Remove t3, t5 from NDB +system exec $NDB_TOOLS_DIR/ndb_drop_table -d test t3 > /dev/null ; +system exec $NDB_TOOLS_DIR/ndb_drop_table -d test t5 > /dev/null ; +# Remove t6, t7 from disk +system rm var/master-data/test/t6.frm > /dev/null ; +system rm var/master-data/test/t7.frm > /dev/null ; + +SHOW TABLES; + +select * from t6; +select * from t7; + +show status like 'handler_discover%'; + +drop table t1, t2, t4, t6, t7, t8, t9; + +####################################################### +# Test SHOW TABLES LIKE ability to detect new and delete old +# tables. Test all at once using many tables. # -#flush table t4; -#--error 1016 -#select * from t4; + +flush status; + +# Create tables +create table t1(id int) engine=ndbcluster; +create table t2(id int, b char(255)) engine=myisam; +create table t3(id int, c char(255)) engine=ndbcluster; +create table t4(id int) engine=myisam; +create table t5(id int, d char(56)) engine=ndbcluster; +create table t6(id int) engine=ndbcluster; +create table t7(id int) engine=ndbcluster; +create table t8(id int, e char(34)) engine=myisam; +create table t9(id int) engine=myisam; + +# Populate tables +insert into t2 values (2, "myisam table 2"); +insert into t3 values (3, "ndb table 3"); +insert into t5 values (5, "ndb table 5"); +insert into t6 values (6); +insert into t8 values (8, "myisam table 8"); +insert into t9 values (9); + +# Remove t3, t5 from NDB +system exec $NDB_TOOLS_DIR/ndb_drop_table -d test t3 > /dev/null ; +system exec $NDB_TOOLS_DIR/ndb_drop_table -d test t5 > /dev/null ; +# Remove t6, t7 from disk +system rm var/master-data/test/t6.frm > /dev/null ; +system rm var/master-data/test/t7.frm > /dev/null ; + + +SHOW TABLES LIKE 't6'; + +show status like 'handler_discover%'; + +# Check that t3 or t5 can't be created +# frm files for these tables is stilll on disk +--error 1050 +create table t3(a int); +--error 1050 +create table t5(a int); + +SHOW TABLES LIKE 't%'; + +show status like 'handler_discover%'; + +drop table t1, t2, t4, t6, t7, t8, t9; + + + +###################################################### +# Test that several tables can be discovered when +# one statement access several table at once. # -#show status like 'handler_discover%'; -#drop table t4; -#flush tables; -#show tables; -#--error 1146 -#select * from t4; + +flush status; + +# Create tables +create table t1(id int) engine=ndbcluster; +create table t2(id int, b char(255)) engine=ndbcluster; +create table t3(id int, c char(255)) engine=ndbcluster; +create table t4(id int) engine=myisam; + +# Populate tables +insert into t1 values (1); +insert into t2 values (2, "table 2"); +insert into t3 values (3, "ndb table 3"); +insert into t4 values (4); + +# Remove t1, t2, t3 from disk +system rm var/master-data/test/t1.frm > /dev/null ; +system rm var/master-data/test/t2.frm > /dev/null ; +system rm var/master-data/test/t3.frm > /dev/null ; +flush tables; + +# Select from the table which only exists in NDB. +select * from t1, t2, t3, t4; + +# 3 table should have been discovered +show status like 'handler_discover%'; + +drop table t1, t2, t3, t4; ######################################################### @@ -240,8 +427,32 @@ show status like 'handler_discover%'; drop table t6; +##################################################### +# Test that only tables in the current database shows +# up in SHOW TABLES +# + +show tables; + +create table t1 (a int,b longblob) engine=ndb; +show tables; +create database test2; +use test2; +show tables; +--error 1146 +select * from t1; +create table t2 (b int,c longblob) engine=ndb; +use test; +select * from t1; +show tables; +drop table t1; +use test2; +drop table t2; +drop database test2; +show databases; +use test; + ###################################################### -# Simple test to show use of discover on startup # Note! This should always be the last step in this # file, the table t9 will be used and dropped # by ndb_autodiscover2 @@ -259,9 +470,7 @@ system rm var/master-data/test/t9.frm ; # Now leave test case, when ndb_autodiscover2 will run, this # MySQL Server will have been restarted because it has a -# ndb_autodiscover2-master.opt file. And thus the table should -# have been discovered by the "discover on startup" function. +# ndb_autodiscover2-master.opt file. + + -#TODO -#SLECT * FROM t1, t2, t4; -#handler discover 3; diff --git a/mysql-test/t/ndb_autodiscover2.test b/mysql-test/t/ndb_autodiscover2.test index 297795d909e..cce75d5ca4f 100644 --- a/mysql-test/t/ndb_autodiscover2.test +++ b/mysql-test/t/ndb_autodiscover2.test @@ -1,13 +1,16 @@ -- source include/have_ndb.inc # -# Simple test to show use of discover on startup +# Simple test to show use of discover when the server has been restarted # The previous step has simply removed the frm file # from disk, but left the table in NDB # +--sleep 3; select * from t9 order by a; -# handler_discover should be zero +# handler_discover should be 1 show status like 'handler_discover%'; drop table t9; + + diff --git a/mysql-test/t/ndb_basic.test b/mysql-test/t/ndb_basic.test index 0487e18cdb9..e79815bbeb1 100644 --- a/mysql-test/t/ndb_basic.test +++ b/mysql-test/t/ndb_basic.test @@ -20,8 +20,9 @@ CREATE TABLE t1 ( attr3 VARCHAR(10) ) ENGINE=ndbcluster; +SHOW INDEX FROM t1; INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9411,9413, 17, '9413'); - +SHOW INDEX FROM t1; SELECT pk1 FROM t1 ORDER BY pk1; SELECT * FROM t1 ORDER BY pk1; SELECT t1.* FROM t1 ORDER BY pk1; @@ -357,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_blob.test b/mysql-test/t/ndb_blob.test index 36c823bda41..5454dd91d26 100644 --- a/mysql-test/t/ndb_blob.test +++ b/mysql-test/t/ndb_blob.test @@ -12,6 +12,32 @@ drop database if exists mysqltest; # A prerequisite for this handler test is that "testBlobs" succeeds. # +# -- bug-5252 tinytext crashes -- + +create table t1 ( + a int not null primary key, + b tinytext +) engine=ndbcluster; + +insert into t1 values(1, 'x'); +update t1 set b = 'y'; +select * from t1; +delete from t1; +drop table t1; + +# -- bug-5013 insert empty string to text -- + +create table t1 ( + a int not null primary key, + b text not null +) engine=ndbcluster; + +insert into t1 values(1, ''); +select * from t1; +drop table t1; + +-- general test starts -- + # make test harder with autocommit off set autocommit=0; @@ -91,6 +117,7 @@ from t1 where a=2; # pk update to null update t1 set d=null where a=1; commit; +# FIXME now fails at random due to weird mixup between the 2 rows select a from t1 where d is null; # pk delete @@ -229,6 +256,14 @@ select * from test.t1,t2 where test.t1.a = t2.a order by test.t1.a; drop table t2; use test; +# alter table + +select * from t1 order by a; +alter table t1 add x int; +select * from t1 order by a; +alter table t1 drop x; +select * from t1 order by a; + # range scan delete delete from t1 where c >= 100; commit; @@ -273,3 +308,26 @@ rollback; select count(*) from t1; drop table t1; drop database mysqltest; + +# bug #5349 +set autocommit=1; +use test; +CREATE TABLE t1 ( + a int, + b text, + PRIMARY KEY (a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +INSERT INTO t1 VALUES +(1,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'); +INSERT INTO t1 VALUES +(2,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'); + +select * from t1 order by a; +alter table t1 engine=ndb; +select * from t1 order by a; + +# bug #5872 +alter table t1 engine=myisam; +select * from t1 order by a; +drop table t1; diff --git a/mysql-test/t/ndb_charset.test b/mysql-test/t/ndb_charset.test new file mode 100644 index 00000000000..b9f28ed0faf --- /dev/null +++ b/mysql-test/t/ndb_charset.test @@ -0,0 +1,159 @@ +--source include/have_ndb.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# Minimal NDB charset test. +# + +# pk - binary + +create table t1 ( + a char(3) character set latin1 collate latin1_bin primary key +) engine=ndb; +# ok +insert into t1 values('aAa'); +insert into t1 values('aaa'); +insert into t1 values('AAA'); +# 3 +select * from t1 order by a; +# 1 +select * from t1 where a = 'aAa'; +# 1 +select * from t1 where a = 'aaa'; +# 0 +select * from t1 where a = 'AaA'; +# 1 +select * from t1 where a = 'AAA'; +drop table t1; + +# pk - case insensitive + +create table t1 ( + a char(3) character set latin1 collate latin1_swedish_ci primary key +) engine=ndb; +# ok +insert into t1 values('aAa'); +# fail +--error 1062 +insert into t1 values('aaa'); +--error 1062 +insert into t1 values('AAA'); +# 1 +select * from t1 order by a; +# 1 +select * from t1 where a = 'aAa'; +# 1 +select * from t1 where a = 'aaa'; +# 1 +select * from t1 where a = 'AaA'; +# 1 +select * from t1 where a = 'AAA'; +drop table t1; + +# unique hash index - binary + +create table t1 ( + p int primary key, + a char(3) character set latin1 collate latin1_bin not null, + unique key(a) +) engine=ndb; +# ok +insert into t1 values(1, 'aAa'); +insert into t1 values(2, 'aaa'); +insert into t1 values(3, 'AAA'); +# 3 +select * from t1 order by p; +# 1 +select * from t1 where a = 'aAa'; +# 1 +select * from t1 where a = 'aaa'; +# 0 +select * from t1 where a = 'AaA'; +# 1 +select * from t1 where a = 'AAA'; +drop table t1; + +# unique hash index - case insensitive + +create table t1 ( + p int primary key, + a char(3) character set latin1 collate latin1_swedish_ci not null, + unique key(a) +) engine=ndb; +# ok +insert into t1 values(1, 'aAa'); +# fail +--error 1169 +insert into t1 values(2, 'aaa'); +--error 1169 +insert into t1 values(3, 'AAA'); +# 1 +select * from t1 order by p; +# 1 +select * from t1 where a = 'aAa'; +# 1 +select * from t1 where a = 'aaa'; +# 1 +select * from t1 where a = 'AaA'; +# 1 +select * from t1 where a = 'AAA'; +drop table t1; + +# ordered index - binary + +create table t1 ( + p int primary key, + a char(3) character set latin1 collate latin1_bin not null, + index(a) +) engine=ndb; +# ok +insert into t1 values(1, 'aAa'); +insert into t1 values(2, 'aaa'); +insert into t1 values(3, 'AAA'); +insert into t1 values(4, 'aAa'); +insert into t1 values(5, 'aaa'); +insert into t1 values(6, 'AAA'); +# 6 +select * from t1 order by p; +# plan +explain select * from t1 where a = 'zZz' order by p; +# 2 +select * from t1 where a = 'aAa' order by p; +# 2 +select * from t1 where a = 'aaa' order by p; +# 0 +select * from t1 where a = 'AaA' order by p; +# 2 +select * from t1 where a = 'AAA' order by p; +drop table t1; + +# ordered index - case insensitive + +create table t1 ( + p int primary key, + a char(3) character set latin1 collate latin1_swedish_ci not null, + index(a) +) engine=ndb; +# ok +insert into t1 values(1, 'aAa'); +insert into t1 values(2, 'aaa'); +insert into t1 values(3, 'AAA'); +insert into t1 values(4, 'aAa'); +insert into t1 values(5, 'aaa'); +insert into t1 values(6, 'AAA'); +# 6 +select * from t1 order by p; +# plan +explain select * from t1 where a = 'zZz' order by p; +# 6 +select * from t1 where a = 'aAa' order by p; +# 6 +select * from t1 where a = 'aaa' order by p; +# 6 +select * from t1 where a = 'AaA' order by p; +# 6 +select * from t1 where a = 'AAA' order by p; +drop table t1; diff --git a/mysql-test/t/ndb_index.test b/mysql-test/t/ndb_index.test index d3977dc3ea4..e65b24a9b20 100644 --- a/mysql-test/t/ndb_index.test +++ b/mysql-test/t/ndb_index.test @@ -9,7 +9,7 @@ CREATE TABLE t1 ( ACCESSNODE varchar(16) NOT NULL, POP varchar(48) NOT NULL, ACCESSTYPE int unsigned NOT NULL, - CUSTOMER_ID varchar(20) NOT NULL, + CUSTOMER_ID varchar(20) collate latin1_bin NOT NULL, PROVIDER varchar(16), TEXPIRE int unsigned, NUM_IP int unsigned, diff --git a/mysql-test/t/ndb_index_ordered.test b/mysql-test/t/ndb_index_ordered.test index 00807bfcb98..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. @@ -130,7 +135,7 @@ CREATE TABLE t1 ( ) engine = ndb; insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL); -select * from t1 use index (bc) where b IS NULL; +select * from t1 use index (bc) where b IS NULL order by a; select * from t1 use index (bc)order by a; select * from t1 use index (bc) order by a; diff --git a/mysql-test/t/ndb_insert.test b/mysql-test/t/ndb_insert.test index c55a925dca2..310c16de3d8 100644 --- a/mysql-test/t/ndb_insert.test +++ b/mysql-test/t/ndb_insert.test @@ -429,5 +429,157 @@ INSERT INTO t1 VALUES SELECT COUNT(*) FROM t1; +# +# Insert duplicate rows +# +--error 1062 +INSERT INTO t1 VALUES +(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5), +(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10); + +select count(*) from t1; + + +# +# Test that select count(*) can see inserts made in the same transaction +# +begin; +SELECT COUNT(*) FROM t1; +INSERT INTO t1 VALUES +(2001,2001,2001),(2002,2002,2002),(2003,2003,2003),(2004,2004,2004),(2005,2005,2005); +SELECT COUNT(*) FROM t1; +rollback; + +# +# Insert duplicate rows, inside transaction +# try to commit +# +begin; + +--error 1062 +INSERT INTO t1 VALUES +(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5), +(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10); + +--error 1296 +commit; + +select * from t1 where pk1=1; +select * from t1 where pk1=10; +select count(*) from t1 where pk1 <= 10 order by pk1; +select count(*) from t1; + + +# +# Insert duplicate rows, inside transaction +# rollback +# +begin; + +--error 1062 +INSERT INTO t1 VALUES +(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5), +(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10); + +rollback; + +select * from t1 where pk1=1; +select * from t1 where pk1=10; +select count(*) from t1 where pk1 <= 10 order by pk1; +select count(*) from t1; + + +# +# Insert duplicate rows, inside transaction +# then try to select, finally rollback +# +begin; + +--error 1062 +INSERT INTO t1 VALUES +(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5), +(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10); +--error 1296 +SELECT * FROM t1 WHERE pk1=10; + +rollback; + +select * from t1 where pk1=1; +select * from t1 where pk1=10; +select count(*) from t1 where pk1 <= 10 order by pk1; +select count(*) from t1; + + +# +# Insert duplicate rows, inside transaction +# then try to select, finally commit +# +begin; + +--error 1062 +INSERT INTO t1 VALUES +(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5), +(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10); + +--error 1296 +SELECT * FROM t1 WHERE pk1=10; + +--error 1296 +SELECT * FROM t1 WHERE pk1=10; + +--error 1296 +commit; + +select * from t1 where pk1=1; +select * from t1 where pk1=10; +select count(*) from t1 where pk1 <= 10 order by pk1; +select count(*) from t1; + + +# +# Insert duplicate rows, inside transaction +# then try to do another insert +# +begin; + +--error 1062 +INSERT INTO t1 VALUES +(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5), +(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10); + +--error 1296 +INSERT INTO t1 values (4000, 40, 44); + +rollback; + +select * from t1 where pk1=1; +select * from t1 where pk1=10; +select count(*) from t1 where pk1 <= 10 order by pk1; +select count(*) from t1; + +# +# Insert duplicate rows using "insert .. select" + +# +--error 1062 +insert into t1 select * from t1 where b < 10 order by pk1; + + +begin; +--error 1031 +INSERT IGNORE INTO t1 VALUES(1,2,3); +commit; +select * from t1 where pk1=1; + +--error 1031 +INSERT IGNORE INTO t1 VALUES(1,2,3); +select * from t1 where pk1=1; + +REPLACE INTO t1 values(1, 2, 3); +select * from t1 where pk1=1; + +--error 1031 +INSERT INTO t1 VALUES(1,1,1) ON DUPLICATE KEY UPDATE b=79; +select * from t1 where pk1=1; DROP TABLE t1; diff --git a/mysql-test/t/ndb_limit.test b/mysql-test/t/ndb_limit.test index b0b6f3c4f17..c2d7a0ecfec 100644 --- a/mysql-test/t/ndb_limit.test +++ b/mysql-test/t/ndb_limit.test @@ -42,3 +42,41 @@ select count(*) from t2 where c=12345678 limit 1000; select * from t2 limit 0; drop table t2; + +CREATE TABLE `t2` ( + `views` int(11) NOT NULL default '0', + `clicks` int(11) NOT NULL default '0', + `day` date NOT NULL default '0000-00-00', + `hour` tinyint(4) NOT NULL default '0', + `bannerid` smallint(6) NOT NULL default '0', + `zoneid` smallint(6) NOT NULL default '0', + `source` varchar(50) NOT NULL default '', + PRIMARY KEY (`day`,`hour`,`bannerid`,`zoneid`,`source`), + KEY `bannerid_day` (`bannerid`,`day`), + KEY `zoneid` (`zoneid`) +) ENGINE=ndbcluster DEFAULT CHARSET=latin1; + +INSERT INTO `t2` VALUES +( 1,0,'2004-09-17', 5,100,100,''), +( 1,0,'2004-09-18', 7,100,100,''), +( 17,0,'2004-09-27',20,132,100,''), +( 4,0,'2004-09-16',23,132,100,''), +( 86,0,'2004-09-18', 7,196,196,''), +( 11,0,'2004-09-16',16,132,100,''), +(140,0,'2004-09-18', 0,100,164,''), +( 2,0,'2004-09-17', 7,132,100,''), +(846,0,'2004-09-27',11,132,164,''), +( 1,0,'2004-09-18', 8,132,100,''), +( 22,0,'2004-09-27', 9,164,132,''), +(711,0,'2004-09-27', 9,100,164,''), +( 11,0,'2004-09-18', 0,196,132,''), +( 41,0,'2004-09-27',15,196,132,''), +( 57,0,'2004-09-18', 2,164,196,''); + +SELECT DATE_FORMAT(day, '%Y%m%d') as date, DATE_FORMAT(day, '%d-%m-%Y') +as date_formatted FROM t2 GROUP BY day ORDER BY day DESC; + +SELECT DATE_FORMAT(day, '%Y%m%d') as date, DATE_FORMAT(day, '%d-%m-%Y') +as date_formatted FROM t2 GROUP BY day ORDER BY day DESC LIMIT 2; + +drop table t2; 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/ndb_subquery.test b/mysql-test/t/ndb_subquery.test new file mode 100644 index 00000000000..cebc1920eaa --- /dev/null +++ b/mysql-test/t/ndb_subquery.test @@ -0,0 +1,38 @@ +-- source include/have_ndb.inc + +--disable_warnings +drop table if exists t1; +drop table if exists t2; +--enable_warnings + +########## +# bug#5367 +create table t1 (p int not null primary key, u int not null, o int not null, +unique (u), key(o)) engine=ndb; + +create table t2 (p int 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); +insert into t2 values (1,1,1),(2,2,2),(3,3,3), (4,4,4), (5,5,5); + +# Use pk +explain select * from t2 where p NOT IN (select p from t1); +select * from t2 where p NOT IN (select p from t1) order by p; + +# Use unique index +explain select * from t2 where p NOT IN (select u from t1); +select * from t2 where p NOT IN (select u from t1) order by p; + +# Use ordered index +explain select * from t2 where p NOT IN (select o from t1); +select * from t2 where p NOT IN (select o from t1) order by p; + +# Use scan +explain select * from t2 where p NOT IN (select p+0 from t1); +select * from t2 where p NOT IN (select p+0 from t1) order by p; + +drop table t1; +drop table t2; +# bug#5367 +########## diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 5131bb8c8b8..988c106bf21 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -472,13 +472,14 @@ select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2 order by col; --error 1052 select col1 from t1, t2 where t1.col1=t2.col2 order by col; +--error 1052 +select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2 + order by col; select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2 order by col; select col2 as c, col as c from t2 order by col; select col2 as col, col as col2 from t2 order by col; -select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2 - order by col; select t2.col2, t2.col, t2.col from t2 order by col; select t2.col2 as col from t2 order by t2.col; @@ -486,3 +487,16 @@ select t2.col2 as col, t2.col from t2 order by t2.col; select t2.col2, t2.col, t2.col from t2 order by t2.col; drop table t1, t2; + +# +# Bug #5428: a problem with small max_sort_length value +# + +create table t1 (a char(25)); +insert into t1 set a = repeat('x', 20); +insert into t1 set a = concat(repeat('x', 19), 'z'); +insert into t1 set a = concat(repeat('x', 19), 'ab'); +insert into t1 set a = concat(repeat('x', 19), 'aa'); +set max_sort_length=20; +select a from t1 order by a; +drop table t1; diff --git a/mysql-test/t/outfile.test b/mysql-test/t/outfile.test index a7b81d565e6..59ce6cc56fe 100644 --- a/mysql-test/t/outfile.test +++ b/mysql-test/t/outfile.test @@ -2,9 +2,12 @@ # test of into outfile|dumpfile # +--disable_warnings +drop table if exists t1; +--enable_warnings + # We need to check that we have 'file' privilege. -#drop table if exists t1; #create table t1 (`a` blob); #insert into t1 values("hello world"),("Hello mars"),(NULL); #select * into outfile "/tmp/select-test.1" from t1; @@ -26,3 +29,15 @@ #INSERT INTO t VALUES ('2002-12-20 12:01:20','',1,"aaa","bbb"); #select * from t into outfile "check"; #drop table if exists t; + +# +# Bug #5382: 'explain select into outfile' crashes the server +# + +CREATE TABLE t1 (a INT); +EXPLAIN + SELECT * + INTO OUTFILE '/tmp/t1.txt' + FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' + FROM t1; +DROP TABLE t1; diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index cbc76e02b42..978ce2bc2c3 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -200,9 +200,9 @@ drop table t1; # create table t1 ( a int primary key, b varchar(30)) engine = MYISAM ; prepare stmt1 from ' show table status from test like ''t1%'' '; ---replace_column 12 # 13 # 14 # +--replace_column 8 4294967295 12 # 13 # 14 # execute stmt1; ---replace_column 12 # 13 # 14 # +--replace_column 8 4294967295 12 # 13 # 14 # show table status from test like 't1%' ; deallocate prepare stmt1 ; drop table t1; @@ -278,3 +278,187 @@ execute stmt using @var; deallocate prepare stmt; drop table t1; +# +# BUG#5510 "inserting Null in AutoIncrement primary key Column Fails" +# (prepared statements) +# The cause: misuse of internal MySQL 'Field' API. +# + +create table t1 (a bigint(20) not null primary key auto_increment); +insert into t1 (a) values (null); +select * from t1; +prepare stmt from "insert into t1 (a) values (?)"; +set @var=null; +execute stmt using @var; +select * from t1; +drop table t1; +# +# check the same for timestamps +# +create table t1 (a timestamp not null); +prepare stmt from "insert into t1 (a) values (?)"; +execute stmt using @var; +--disable_result_log +select * from t1; +--enable_result_log +deallocate prepare stmt; +drop table t1; + +# +# BUG#5688 "Upgraded 4.1.5 Server seg faults" # (prepared statements) +# The test case speaks for itself. +# Just another place where we used wrong memory root for Items created +# during statement prepare. +# +prepare stmt from "select 'abc' like convert('abc' using utf8)"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +# +# BUG#5748 "Prepared statement with BETWEEN and bigint values crashes +# mysqld". Just another place where an item tree modification must be +# rolled back. +# +create table t1 ( a bigint ); +prepare stmt from 'select a from t1 where a between ? and ?'; +set @a=1; +execute stmt using @a, @a; +execute stmt using @a, @a; +execute stmt using @a, @a; +drop table t1; +deallocate prepare stmt; + +# +# Bug #5987 subselect in bool function crashes server (prepared statements): +# don't overwrite transformed subselects with old arguments of a bool +# function. +# +create table t1 (a int); +prepare stmt from "select * from t1 where 1 > (1 in (SELECT * FROM t1))"; +execute stmt; +execute stmt; +execute stmt; +drop table t1; +deallocate prepare stmt; + +# +# Test case for Bug#6042 "constants propogation works only once (prepared +# statements): check that the query plan changes whenever we change +# placeholder value. +# +create table t1 (a int, b int); +insert into t1 (a, b) values (1,1), (1,2), (2,1), (2,2); +prepare stmt from +"explain select * from t1 where t1.a=2 and t1.a=t1.b and t1.b > 1 + ?"; +--replace_column 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - +set @v=5; +execute stmt using @v; +--replace_column 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - +set @v=0; +execute stmt using @v; +--replace_column 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - +set @v=5; +execute stmt using @v; +drop table t1; +deallocate prepare stmt; + +# +# A test case for Bug#5985 prepare stmt from "select rand(?)" crashes +# server. Check that Item_func_rand is prepared-statements friendly. +# +create table t1 (a int); +insert into t1 (a) values (1), (2), (3), (4); +set @precision=10000000000; +--replace_column 1 - 3 - +select rand(), + cast(rand(10)*@precision as unsigned integer), + cast(rand(a)*@precision as unsigned integer) from t1; +prepare stmt from +"select rand(), + cast(rand(10)*@precision as unsigned integer), + cast(rand(a)*@precision as unsigned integer), + cast(rand(?)*@precision as unsigned integer) from t1"; +set @var=1; +--replace_column 1 - 3 - +execute stmt using @var; +set @var=2; +--replace_column 1 - +execute stmt using @var; +set @var=3; +--replace_column 1 - +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; + +# +# Test CREATE TABLE ... SELECT (Bug #6094) +# +create table t1 (a int); +insert into t1 values (1),(2),(3); +create table t2 select * from t1; +prepare stmt FROM 'create table t2 select * from t1'; +drop table t2; +execute stmt; +drop table t2; +execute stmt; +--error 1050 +execute stmt; +drop table t2; +execute stmt; +drop table t1,t2; +deallocate prepare stmt; + +# +# Bug#6088 "FOUND_ROWS returns wrong values for prepared statements when +# LIMIT is used" +# +create table t1 (a int); +insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +prepare stmt from "select sql_calc_found_rows * from t1 limit 2"; +execute stmt; +select found_rows(); +execute stmt; +select found_rows(); +execute stmt; +select found_rows(); +deallocate prepare stmt; +drop table t1; + +# +# Bug#6047 "permission problem when executing mysql_stmt_execute with derived +# table" +# + +CREATE TABLE t1 (N int, M tinyint); +INSERT INTO t1 VALUES (1,0),(1,0),(2,0),(2,0),(3,0); +PREPARE stmt FROM 'UPDATE t1 AS P1 INNER JOIN (SELECT N FROM t1 GROUP BY N HAVING COUNT(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2'; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +DROP TABLE t1; + diff --git a/mysql-test/t/ps_10nestset.test b/mysql-test/t/ps_10nestset.test new file mode 100644 index 00000000000..d2adaca689e --- /dev/null +++ b/mysql-test/t/ps_10nestset.test @@ -0,0 +1,72 @@ +############################################### +# # +# Prepared Statements test on # +# "nested sets" representing hierarchies # +# # +############################################### + +# Source: http://kris.koehntopp.de/artikel/sql-self-references (dated 1999) +# Source: http://dbmsmag.com/9603d06.html (dated 1996) + +--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.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 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 t1 set emp = ?, salary = ?, l = ?, r = ?'; + +# Initial employee list: +# 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; +execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ; +set @arg_nam= 'Chuck'; set @arg_sal= 900; set @arg_l= 4; set @arg_r= 11; +execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ; +set @arg_nam= 'Donna'; set @arg_sal= 800; set @arg_l= 5; set @arg_r= 6; +execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ; +set @arg_nam= 'Eddie'; set @arg_sal= 700; set @arg_l= 7; set @arg_r= 8; +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 t1; + +# Three successive raises, each one is 100 units for managers, 10 percent for others. +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; +while ($1) +{ + execute st_raise_base using @arg_percent; + execute st_raise_mgr using @arg_amount; + dec $1; +} + +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 t1; diff --git a/mysql-test/t/ps_11bugs.test b/mysql-test/t/ps_11bugs.test new file mode 100644 index 00000000000..5945b140645 --- /dev/null +++ b/mysql-test/t/ps_11bugs.test @@ -0,0 +1,131 @@ +############################################### +# # +# Prepared Statements # +# re-testing bug DB entries # +# # +# The bugs are reported as "closed". # +# Command sequences taken from bug report. # +# No other test contains the bug# as comment. # +# # +# Tests drop/create tables 't1', 't2', ... # +# # +############################################### + +--disable_warnings +drop table if exists t1, t2; +--enable_warnings + +# bug#1180: optimized away part of WHERE clause cause incorect prepared satatement results + +CREATE TABLE t1(session_id char(9) NOT NULL); +INSERT INTO t1 VALUES ("abc"); +SELECT * FROM t1; + +prepare st_1180 from 'SELECT * FROM t1 WHERE ?="1111" and session_id = "abc"'; + +# Must not find a row +set @arg1= 'abc'; +execute st_1180 using @arg1; + +# Now, it should find one row +set @arg1= '1111'; +execute st_1180 using @arg1; + +# Back to non-matching +set @arg1= 'abc'; +execute st_1180 using @arg1; + +drop table t1; + +# end of bug#1180 + + +# bug#1644: Insertion of more than 3 NULL columns with parameter binding fails + +# Using prepared statements, insertion of more than three columns with NULL +# values fails to insert additional NULLS. After the third column NULLS will +# be inserted into the database as zeros. +# First insert four columns of a value (i.e. 22) to verify binding is working +# correctly. Then Bind to each columns bind parameter an is_null value of 1. +# Then insert four more columns of integers, just for sanity. +# A subsequent select on the server will result in this: +# mysql> select * from foo_dfr; +# +------+------+------+------+ +# | col1 | col2 | col3 | col4 | +# +------+------+------+------+ +# | 22 | 22 | 22 | 22 | +# | NULL | NULL | NULL | 0 | +# | 88 | 88 | 88 | 88 | +# +------+------+------+------+ + +# Test is extended to more columns - code stores bit vector in bytes. + +create table t1 ( + c_01 char(6), c_02 integer, c_03 real, c_04 int(3), c_05 varchar(20), + c_06 date, c_07 char(1), c_08 real, c_09 int(11), c_10 time, + c_11 char(6), c_12 integer, c_13 real, c_14 int(3), c_15 varchar(20), + c_16 date, c_17 char(1), c_18 real, c_19 int(11), c_20 text); +# Do not use "timestamp" type, because it has a non-NULL default as of 4.1.2 + +prepare st_1644 from 'insert into t1 values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'; + +set @arg01= 'row_1'; set @arg02= 1; set @arg03= 1.1; set @arg04= 111; set @arg05= 'row_one'; +set @arg06= '2004-10-12'; set @arg07= '1'; set @arg08= 1.1; set @arg09= '100100100'; set @arg10= '12:34:56'; +set @arg11= 'row_1'; set @arg12= 1; set @arg13= 1.1; set @arg14= 111; set @arg15= 'row_one'; +set @arg16= '2004-10-12'; set @arg17= '1'; set @arg18= 1.1; set @arg19= '100100100'; set @arg20= '12:34:56'; +execute st_1644 using @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08, @arg09, @arg10, + @arg11, @arg12, @arg13, @arg14, @arg15, @arg16, @arg17, @arg18, @arg19, @arg20; + +set @arg01= NULL; set @arg02= NULL; set @arg03= NULL; set @arg04= NULL; set @arg05= NULL; +set @arg06= NULL; set @arg07= NULL; set @arg08= NULL; set @arg09= NULL; set @arg10= NULL; +set @arg11= NULL; set @arg12= NULL; set @arg13= NULL; set @arg14= NULL; set @arg15= NULL; +set @arg16= NULL; set @arg17= NULL; set @arg18= NULL; set @arg19= NULL; set @arg20= NULL; +execute st_1644 using @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08, @arg09, @arg10, + @arg11, @arg12, @arg13, @arg14, @arg15, @arg16, @arg17, @arg18, @arg19, @arg20; + +set @arg01= 'row_3'; set @arg02= 3; set @arg03= 3.3; set @arg04= 333; set @arg05= 'row_three'; +set @arg06= '2004-10-12'; set @arg07= '3'; set @arg08= 3.3; set @arg09= '300300300'; set @arg10= '12:34:56'; +set @arg11= 'row_3'; set @arg12= 3; set @arg13= 3.3; set @arg14= 333; set @arg15= 'row_three'; +set @arg16= '2004-10-12'; set @arg17= '3'; set @arg18= 3.3; set @arg19= '300300300'; set @arg20= '12:34:56'; +execute st_1644 using @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08, @arg09, @arg10, + @arg11, @arg12, @arg13, @arg14, @arg15, @arg16, @arg17, @arg18, @arg19, @arg20; + +select * from t1; + +drop table t1; + +# end of bug#1644 + + +# bug#1676: Prepared statement two-table join returns no rows when one is expected + +create table t1( + cola varchar(50) not null, + colb varchar(8) not null, + colc varchar(12) not null, + cold varchar(2) not null, + primary key (cola, colb, cold)); + +create table t2( + cola varchar(50) not null, + colb varchar(8) not null, + colc varchar(2) not null, + cold float, + primary key (cold)); + +insert into t1 values ('aaaa', 'yyyy', 'yyyy-dd-mm', 'R'); + +insert into t2 values ('aaaa', 'yyyy', 'R', 203), ('bbbb', 'zzzz', 'C', 201); + +prepare st_1676 from 'select a.cola, a.colb, a.cold from t1 a, t2 b where a.cola = ? and a.colb = ? and a.cold = ? and b.cola = a.cola and b.colb = a.colb and b.colc = a.cold'; + +set @arg0= "aaaa"; +set @arg1= "yyyy"; +set @arg2= "R"; + +execute st_1676 using @arg0, @arg1, @arg2; + +drop table t1, t2; + +# end of bug#1676 + diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test index cc8cb4c4ba0..89c49d087b7 100644 --- a/mysql-test/t/ps_1general.test +++ b/mysql-test/t/ps_1general.test @@ -14,13 +14,13 @@ select '------ basic tests ------' as test_sequence ; --enable_query_log let $type= 'MYISAM' ; -# create the tables (t1 and t_many_col_types) used in many tests +# create the tables (t1 and t9) used in many tests --source include/ps_create.inc # insert data into these tables --source include/ps_renew.inc -##### The basic functions #### +################ The basic functions ################ # 1. PREPARE stmt_name FROM <preparable statement>; # <preparable statement> ::= @@ -54,7 +54,7 @@ select * from t1 where a = @var ; # The server will reply with "Query Ok" or an error message. DEALLOCATE PREPARE stmt ; -## prepare +################ PREPARE ################ # prepare without parameter prepare stmt1 from ' select 1 as my_col ' ; # prepare with parameter @@ -91,9 +91,15 @@ set @arg00=NULL; prepare stmt1 from @arg01; prepare stmt1 from ' select * from t1 where a <= 2 ' ; -# prepare must fail (column does not exist) +# prepare must fail (column x does not exist) --error 1054 prepare stmt1 from ' select * from t1 where x <= 2 ' ; +# cases derived from client_test.c: test_null() +# prepare must fail (column x does not exist) +--error 1054 +prepare stmt1 from ' insert into t1(a,x) values(?,?) ' ; +--error 1054 +prepare stmt1 from ' insert into t1(x,a) values(?,?) ' ; --disable_warnings drop table if exists not_exist ; --enable_warnings @@ -109,7 +115,7 @@ prepare stmt1 from ' insert into t1 values(? ' ; prepare stmt1 from ' select a, b from t1 where a=? and where ' ; -## execute +################ EXECUTE ################ # execute must fail (statement never_prepared never prepared) --error 1243 execute never_prepared ; @@ -122,89 +128,89 @@ prepare stmt1 from ' select * from not_exist where a <= 2 ' ; execute stmt1 ; # drop the table between prepare and execute -create table to_be_dropped +create table t5 ( a int primary key, b char(30), c int ); -insert into to_be_dropped( a, b, c) values( 1, 'original table', 1); -prepare stmt2 from ' select * from to_be_dropped ' ; +insert into t5( a, b, c) values( 1, 'original table', 1); +prepare stmt2 from ' select * from t5 ' ; execute stmt2 ; -drop table to_be_dropped ; +drop table t5 ; # execute must fail (table was dropped after prepare) --error 1146 execute stmt2 ; # cases derived from client_test.c: test_select_prepare() # 1. drop + create table (same column names/types/order) # between prepare and execute -create table to_be_dropped +create table t5 ( a int primary key, b char(30), c int ); -insert into to_be_dropped( a, b, c) values( 9, 'recreated table', 9); +insert into t5( a, b, c) values( 9, 'recreated table', 9); execute stmt2 ; -drop table to_be_dropped ; +drop table t5 ; # 2. drop + create table (same column names/types but different order) # between prepare and execute -create table to_be_dropped +create table t5 ( a int primary key, c int, b char(30) ); -insert into to_be_dropped( a, b, c) values( 9, 'recreated table', 9); +insert into t5( a, b, c) values( 9, 'recreated table', 9); execute stmt2 ; -drop table to_be_dropped ; +drop table t5 ; # 3. drop + create table (same column names/types/order+extra column) # between prepare and execute -create table to_be_dropped +create table t5 ( a int primary key, b char(30), c int, d timestamp default current_timestamp ); -insert into to_be_dropped( a, b, c) values( 9, 'recreated table', 9); +insert into t5( a, b, c) values( 9, 'recreated table', 9); execute stmt2 ; -drop table to_be_dropped ; +drop table t5 ; # 4. drop + create table (same column names/types, different order + # additional column) between prepare and execute -create table to_be_dropped +create table t5 ( a int primary key, d timestamp default current_timestamp, b char(30), c int ); -insert into to_be_dropped( a, b, c) values( 9, 'recreated table', 9); +insert into t5( a, b, c) values( 9, 'recreated table', 9); execute stmt2 ; -drop table to_be_dropped ; +drop table t5 ; # 5. drop + create table (same column names/order, different types) # between prepare and execute -create table to_be_dropped +create table t5 ( a timestamp default '2004-02-29 18:01:59', b char(30), c int ); -insert into to_be_dropped( b, c) values( 'recreated table', 9); +insert into t5( b, c) values( 'recreated table', 9); execute stmt2 ; -drop table to_be_dropped ; +drop table t5 ; # 6. drop + create table (same column types/order, different names) # between prepare and execute -create table to_be_dropped +create table t5 ( f1 int primary key, f2 char(30), f3 int ); -insert into to_be_dropped( f1, f2, f3) values( 9, 'recreated table', 9); +insert into t5( f1, f2, f3) values( 9, 'recreated table', 9); --error 1054 execute stmt2 ; -drop table to_be_dropped ; +drop table t5 ; # execute without parameter prepare stmt1 from ' select * from t1 where a <= 2 ' ; @@ -223,8 +229,8 @@ execute stmt1 using @arg00, @arg01; # execute must fail (parameter is not set) execute stmt1 using @not_set; -## deallocate -# deallocate must fail (never_prepared was never prepared) +################ DEALLOCATE ################ +# deallocate must fail (the statement 'never_prepared' was never prepared) --error 1243 deallocate prepare never_prepared ; # deallocate must fail (prepare stmt1 just failed, @@ -234,13 +240,13 @@ prepare stmt1 from ' select * from t1 where a <= 2 ' ; prepare stmt1 from ' select * from not_exist where a <= 2 ' ; --error 1243 deallocate prepare stmt1; -create table to_be_dropped +create table t5 ( a int primary key, b char(10) ); -prepare stmt2 from ' select a,b from to_be_dropped where a <= 2 ' ; -drop table to_be_dropped ; +prepare stmt2 from ' select a,b from t5 where a <= 2 ' ; +drop table t5 ; # deallocate prepared statement where the table was dropped after prepare deallocate prepare stmt2; @@ -271,7 +277,7 @@ create table t2 a int primary key, b char(10) ); -###### SHOW COMMANDS +################ SHOW COMMANDS ################ prepare stmt4 from ' show databases '; execute stmt4; prepare stmt4 from ' show tables from test like ''t2%'' '; @@ -287,7 +293,7 @@ prepare stmt4 from ' show table status from test like ''t2%'' '; # Bug#4288 : prepared statement 'show table status ..', wrong output on execute execute stmt4; # try the same with the big table -prepare stmt4 from ' show table status from test like ''t_many_col_types%'' '; +prepare stmt4 from ' show table status from test like ''t9%'' '; # egalize date and time values --replace_column 12 # 13 # 14 # # Bug#4288 @@ -324,18 +330,75 @@ prepare stmt4 from ' show storage engines '; --replace_column 2 YES/NO execute stmt4; -###### MISC STUFF +################ MISC STUFF ################ ## get a warning and an error # cases derived from client_test.c: test_warnings(), test_errors() --disable_warnings -drop table if exists tx; +drop table if exists t5; --enable_warnings -prepare stmt1 from ' drop table if exists tx ' ; +prepare stmt1 from ' drop table if exists t5 ' ; execute stmt1 ; -prepare stmt1 from ' drop table tx ' ; +prepare stmt1 from ' drop table t5 ' ; --error 1051 execute stmt1 ; +## SELECT @@version +# cases derived from client_test.c: test_select_version() +# +# TODO: Metadata check is temporary disabled here, because metadata of +# this statement also depends on @@version contents and you can't apply +# replace_column and replace_result to it. It will be enabled again when +# support of replace_column and replace_result on metadata will be +# implemented. +# +#--enable_metadata +prepare stmt1 from ' SELECT @@version ' ; +# egalize the version +--replace_column 1 <version> +execute stmt1 ; +#--disable_metadata + +## do @var:= and set @var= +# cases derived from client_test.c: test_do_set() +prepare stmt_do from ' do @var:= (1 in (select a from t1)) ' ; +prepare stmt_set from ' set @var= (1 in (select a from t1)) ' ; +let $1= 3 ; +while ($1) +{ + execute stmt_do ; + --disable_query_log + select @var as 'content of @var is:' ; + --enable_query_log + execute stmt_set ; + --disable_query_log + select @var as 'content of @var is:' ; + --enable_query_log + dec $1 ; +} +# the same test with a table containing one column and 'select *' +--disable_warnings +drop table if exists t5 ; +--enable_warnings +create table t5 (a int) ; +prepare stmt_do from ' do @var:= (1 in (select a from t5)) ' ; +prepare stmt_set from ' set @var= (1 in (select a from t5)) ' ; +let $1= 3 ; +while ($1) +{ + execute stmt_do ; + --disable_query_log + select @var as 'content of @var is:' ; + --enable_query_log + execute stmt_set ; + --disable_query_log + select @var as 'content of @var is:' ; + --enable_query_log + dec $1 ; +} +drop table t5 ; +deallocate prepare stmt_do ; +deallocate prepare stmt_set ; + ## nonsense like prepare of prepare,execute or deallocate --error 1064 prepare stmt1 from ' prepare stmt2 from '' select 1 '' ' ; @@ -436,6 +499,8 @@ prepare stmt1 from ' KILL 0 '; ## simple explain # cases derived from client_test.c: test_explain_bug() prepare stmt1 from ' explain select a from t1 order by b '; +# PS protocol gives slightly different metadata +--disable_ps_protocol --enable_metadata execute stmt1; --disable_metadata @@ -444,7 +509,36 @@ prepare stmt1 from ' explain select a from t1 where a > ? order by b '; --enable_metadata execute stmt1 using @arg00; --disable_metadata +--enable_ps_protocol +## parameters with probably problematic characters (quote, double quote) +# cases derived from client_test.c: test_logs() +# try if +--disable_warnings +drop table if exists t2; +--enable_warnings +create table t2 (id smallint, name varchar(20)) ; +prepare stmt1 from ' insert into t2 values(?, ?) ' ; +set @id= 9876 ; +set @arg00= 'MySQL - Open Source Database' ; +set @arg01= "'" ; +set @arg02= '"' ; +set @arg03= "my'sql'" ; +set @arg04= 'my"sql"' ; +insert into t2 values ( @id , @arg00 ); +insert into t2 values ( @id , @arg01 ); +insert into t2 values ( @id , @arg02 ); +insert into t2 values ( @id , @arg03 ); +insert into t2 values ( @id , @arg04 ); +prepare stmt1 from ' select * from t2 where id= ? and name= ? '; +execute stmt1 using @id, @arg00 ; +execute stmt1 using @id, @arg01 ; +execute stmt1 using @id, @arg02 ; +execute stmt1 using @id, @arg03 ; +execute stmt1 using @id, @arg04 ; +drop table t2; + +################ CREATE/DROP/ALTER/RENAME TESTS ################ --disable_query_log select '------ create/drop/alter/rename tests ------' as test_sequence ; --enable_query_log @@ -453,11 +547,13 @@ select '------ create/drop/alter/rename tests ------' as test_sequence ; drop table if exists t2, t3; --enable_warnings +## DROP TABLE prepare stmt_drop from ' drop table if exists t2 ' ; --disable_warnings execute stmt_drop; --enable_warnings +## CREATE TABLE prepare stmt_create from ' create table t2 ( a int primary key, b char(10)) '; execute stmt_create; @@ -465,6 +561,7 @@ prepare stmt3 from ' create table t3 like t2 '; execute stmt3; drop table t3; +## CREATE TABLE .. SELECT set @arg00=1; prepare stmt3 from ' create table t3 (m int) select ? as m ' ; # Bug#4280 server hangs, prepared "create table .. as select ? .." @@ -475,6 +572,8 @@ drop table t3; prepare stmt3 from ' create index t2_idx on t2(b) '; prepare stmt3 from ' drop index t2_idx on t2 ' ; prepare stmt3 from ' alter table t2 drop primary key '; + +## RENAME TABLE --disable_warnings drop table if exists new_t2; --enable_warnings @@ -484,15 +583,41 @@ execute stmt3; execute stmt3; rename table new_t2 to t2; drop table t2; +## RENAME more than on TABLE within one statement +# cases derived from client_test.c: test_rename() +--disable_warnings +drop table if exists t5, t6, t7, t8 ; +--enable_warnings +prepare stmt1 from ' rename table t5 to t6, t7 to t8 ' ; +create table t5 (a int) ; +# rename must fail, tc does not exist +--error 1017 +execute stmt1 ; +create table t7 (a int) ; +# rename, t5 -> t6 and t7 -> t8 +execute stmt1 ; +# rename must fail, t5 and t7 does not exist t6 and t8 already exist +--error 1050 +execute stmt1 ; +rename table t6 to t5, t8 to t7 ; +# rename, t5 -> t6 and t7 -> t8 +execute stmt1 ; +drop table t6, t8 ; + +################ BIG STATEMENT TESTS ################ --disable_query_log select '------ big statement tests ------' as test_sequence ; --enable_query_log +# The following tests use huge numbers of lines, characters or parameters +# per prepared statement. +# I assume the server and also the client (mysqltest) are stressed. +# # Attention: The limits used are NOT derived from the manual # or other sources. ## many lines ( 50 ) -select 'ABC' as my_const_col from t1 where +let $my_stmt= select 'ABC' as my_const_col from t1 where 1 = 1 AND 1 = 1 AND 1 = 1 AND @@ -542,62 +667,14 @@ select 'ABC' as my_const_col from t1 where 1 = 1 AND 1 = 1 AND 1 = 1 ; -prepare stmt1 from ' select ''ABC'' as my_const_col FROM t1 WHERE -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 AND -1 = 1 ' ; +eval ($my_stmt) ; +eval prepare stmt1 from "$my_stmt" ; execute stmt1 ; execute stmt1 ; ## many characters ( about 1400 ) -select 'ABC' as my_const_col FROM t1 WHERE +let $my_stmt= select 'ABC' as my_const_col FROM t1 WHERE '1234567890123456789012345678901234567890123456789012345678901234567890' = '1234567890123456789012345678901234567890123456789012345678901234567890' AND '1234567890123456789012345678901234567890123456789012345678901234567890' @@ -616,30 +693,14 @@ select 'ABC' as my_const_col FROM t1 WHERE = '1234567890123456789012345678901234567890123456789012345678901234567890' AND '1234567890123456789012345678901234567890123456789012345678901234567890' = '1234567890123456789012345678901234567890123456789012345678901234567890' ; -prepare stmt1 from ' select ''ABC'' as my_const_col FROM t1 WHERE -''1234567890123456789012345678901234567890123456789012345678901234567890'' -= ''1234567890123456789012345678901234567890123456789012345678901234567890'' AND -''1234567890123456789012345678901234567890123456789012345678901234567890'' -= ''1234567890123456789012345678901234567890123456789012345678901234567890'' AND -''1234567890123456789012345678901234567890123456789012345678901234567890'' -= ''1234567890123456789012345678901234567890123456789012345678901234567890'' AND -''1234567890123456789012345678901234567890123456789012345678901234567890'' -= ''1234567890123456789012345678901234567890123456789012345678901234567890'' AND -''1234567890123456789012345678901234567890123456789012345678901234567890'' -= ''1234567890123456789012345678901234567890123456789012345678901234567890'' AND -''1234567890123456789012345678901234567890123456789012345678901234567890'' -= ''1234567890123456789012345678901234567890123456789012345678901234567890'' AND -''1234567890123456789012345678901234567890123456789012345678901234567890'' -= ''1234567890123456789012345678901234567890123456789012345678901234567890'' AND -''1234567890123456789012345678901234567890123456789012345678901234567890'' -= ''1234567890123456789012345678901234567890123456789012345678901234567890'' AND -''1234567890123456789012345678901234567890123456789012345678901234567890'' -= ''1234567890123456789012345678901234567890123456789012345678901234567890'' '; +eval ($my_stmt) ; +eval prepare stmt1 from "$my_stmt" ; execute stmt1 ; execute stmt1 ; ## many parameters ( 50 ) +--disable_query_log set @arg00= 1; set @arg01= 1; set @arg02= 1; @@ -690,6 +751,7 @@ set @arg56= 1; set @arg57= 1; set @arg60= 1; set @arg61= 1; +--enable_query_log select 'ABC' as my_const_col FROM t1 WHERE @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @@ -724,8 +786,156 @@ execute stmt1 using @arg50, @arg51, @arg52, @arg53, @arg54, @arg55, @arg56, @arg57, @arg60, @arg61 ; +# cases derived from client_test.c: test_mem_overun() +--disable_warnings +drop table if exists t5 ; +--enable_warnings + +set @col_num= 1000 ; + +--disable_query_log +set @string= 'create table t5( ' ; +let $1=`select @col_num - 1` ; +while ($1) +{ + eval set @string= concat(@string, 'c$1 int,') ; + dec $1 ; +} +set @string= concat(@string, 'c0 int)' ); +--enable_query_log +select @string as "" ; +prepare stmt1 from @string ; +execute stmt1 ; + +--disable_query_log +set @string= 'insert into t5 values(' ; +let $1=`select @col_num - 1` ; +while ($1) +{ + eval set @string= concat(@string, '1 ,') ; + dec $1 ; +} +eval set @string= concat(@string, '1 )') ; +--enable_query_log +select @string as "" ; +prepare stmt1 from @string ; +execute stmt1 ; + +prepare stmt1 from ' select * from t5 ' ; +--enable_metadata +# prevent too long lines +--vertical_results +--disable_result_log +execute stmt1 ; +--enable_result_log +--disable_metadata +--horizontal_results + +drop table t5 ; + + +################ GRANT/REVOKE/DROP affecting a parallel session ################ +--disable_query_log +select '------ grant/revoke/drop affects a parallel session test ------' + as test_sequence ; +--enable_query_log + +#---------------------------------------------------------------------# +# Here we test that: +# 1. A new GRANT will be visible within another sessions. # +# # +# Let's assume there is a parallel session with an already prepared # +# statement for a table. # +# A DROP TABLE will affect the EXECUTE properties. # +# A REVOKE will affect the EXECUTE properties. # +#---------------------------------------------------------------------# + +# Who am I ? +# this is different across different systems: +# select current_user(), user() ; + +#### create a new user account #### +## There should be no grants for that non existing user +--error 1141 +show grants for second_user@localhost ; +## create a new user account by using GRANT statements on t9 +grant usage on test.* to second_user@localhost +identified by 'looser' ; +grant select on test.t9 to second_user@localhost +identified by 'looser' ; +show grants for second_user@localhost ; + + +#### establish a second session to the new user account +connect (con3,localhost,second_user,looser,test); +## switch to the second session +connection con3; +# Who am I ? +select current_user(); +## check the access rights +show grants for current_user(); +prepare s_t9 from 'select c1 as my_col + from t9 where c1= 1' ; +execute s_t9 ; +# check that we cannot do a SELECT on the table t1; +--error 1142 +select a as my_col from t1; + + +#### give access rights to t1 and drop table t9 +## switch back to the first session +connection default; +grant select on test.t1 to second_user@localhost +identified by 'looser' ; +show grants for second_user@localhost ; +drop table t9 ; +show grants for second_user@localhost ; + + +#### check the access as new user +## switch to the second session +connection con3; +######## Question 1: The table t1 should be now accessible. ######## +show grants for second_user@localhost ; +prepare s_t1 from 'select a as my_col from t1' ; +execute s_t1 ; +######## Question 2: The table t9 does not exist. ######## +--error 1146 +execute s_t9 ; + + +#### revoke the access rights to t1 +## switch back to the first session +connection default; +revoke all privileges on test.t1 from second_user@localhost +identified by 'looser' ; +show grants for second_user@localhost ; + +#### check the access as new user +## switch to the second session +connection con3; +show grants for second_user@localhost ; +######## Question 2: The table t1 should be now not accessible. ######## +--error 1142 +execute s_t1 ; + +## cleanup +## switch back to the first session +connection default; +## disconnect the second session +disconnect con3 ; +## remove all rights of second_user@localhost +revoke all privileges, grant option from second_user@localhost ; +show grants for second_user@localhost ; +drop user second_user@localhost ; +commit ; +--error 1141 +show grants for second_user@localhost ; + + drop table t1 ; + ##### RULES OF THUMB TO PRESERVE THE SYSTEMATICS OF THE PS TEST CASES ##### # # 0. You don't have the time to @@ -744,7 +954,7 @@ drop table t1 ; # NO --> alter t/ps_1general.test (Example: Command with syntax error) # If you need a table, please try to use # t1 - very simple table -# t_many_col_types - table with nearly all available column types +# t9 - table with nearly all available column types # whenever possible. # # The structure and the content of these tables can be found in @@ -799,11 +1009,11 @@ drop table t1 ; # include/ps_query.inc test cases with SELECT/... # These test cases should not modify the content or # the structure (DROP/ALTER..) of the tables -# 't1' and 't_many_col_types'. +# 't1' and 't9'. # include/ps_modify.inc test cases with INSERT/UPDATE/... # These test cases should not modify the structure # (DROP/ALTER..) of the tables -# 't1' and 't_many_col_types'. +# 't1' and 't9'. # These two test sequences will be applied to all table types . # # include/ps_modify1.inc test cases with INSERT/UPDATE/... @@ -811,7 +1021,7 @@ drop table t1 ; # except MERGE tables. # # include/ps_create.inc DROP and CREATE of the tables -# 't1' and 't_many_col_types' . +# 't1' and 't9' . # include/ps_renew.inc DELETE all rows and INSERT some rows, that means # recreate the original content of these tables. # Please do not alter the commands concerning these two tables. diff --git a/mysql-test/t/ps_2myisam.test b/mysql-test/t/ps_2myisam.test index c7e4746762b..534703efc14 100644 --- a/mysql-test/t/ps_2myisam.test +++ b/mysql-test/t/ps_2myisam.test @@ -15,7 +15,28 @@ let $type= 'MYISAM' ; -- source include/ps_renew.inc -- source include/ps_query.inc + +# parameter in SELECT ... MATCH/AGAINST +# case derived from client_test.c: test_bug1500() +--disable_warnings +drop table if exists t2 ; +--enable_warnings +eval create table t2 (s varchar(25), fulltext(s)) +ENGINE = $type ; +insert into t2 values ('Gravedigger'), ('Greed'),('Hollow Dogs') ; +commit ; + +prepare stmt1 from ' select s from t2 where match (s) against (?) ' ; +set @arg00='Dogs' ; +execute stmt1 using @arg00 ; +prepare stmt1 from ' SELECT s FROM t2 +where match (s) against (concat(?,''digger'')) '; +set @arg00='Grave' ; +execute stmt1 using @arg00 ; +drop table t2 ; + -- source include/ps_modify.inc -- source include/ps_modify1.inc +-- source include/ps_conv.inc -drop table t1, t_many_col_types; +drop table t1, t9; diff --git a/mysql-test/t/ps_3innodb.test b/mysql-test/t/ps_3innodb.test index 055e1e127e5..f83b61914a2 100644 --- a/mysql-test/t/ps_3innodb.test +++ b/mysql-test/t/ps_3innodb.test @@ -19,5 +19,6 @@ let $type= 'InnoDB' ; -- source include/ps_query.inc -- source include/ps_modify.inc -- source include/ps_modify1.inc +-- source include/ps_conv.inc -drop table t1, t_many_col_types; +drop table t1, t9; diff --git a/mysql-test/t/ps_4heap.test b/mysql-test/t/ps_4heap.test index d1c81d95cd7..a7b2e332af4 100644 --- a/mysql-test/t/ps_4heap.test +++ b/mysql-test/t/ps_4heap.test @@ -12,7 +12,7 @@ use test; let $type= 'HEAP' ; --disable_warnings -drop table if exists t1, t_many_col_types ; +drop table if exists t1, t9 ; --enable_warnings eval create table t1 ( @@ -21,12 +21,12 @@ eval create table t1 ) engine = $type ; --disable_warnings -drop table if exists t_many_col_types; +drop table if exists t9; --enable_warnings # The used table type doesn't support BLOB/TEXT columns. # (The server would send error 1163 .) # So we use char(100) instead. -eval create table t_many_col_types +eval create table t9 ( c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, @@ -44,5 +44,6 @@ eval create table t_many_col_types -- source include/ps_query.inc -- source include/ps_modify.inc -- source include/ps_modify1.inc +-- source include/ps_conv.inc -drop table t1, t_many_col_types; +drop table t1, t9; diff --git a/mysql-test/t/ps_5merge.test b/mysql-test/t/ps_5merge.test index ff48a50f331..9a79842709c 100644 --- a/mysql-test/t/ps_5merge.test +++ b/mysql-test/t/ps_5merge.test @@ -12,13 +12,13 @@ use test; --disable_warnings drop table if exists t1, t1_1, t1_2, - t_many_col_types, t_many_col_types_1, t_many_col_types_2; + t9, t9_1, t9_2; --enable_warnings let $type= 'MYISAM' ; -- source include/ps_create.inc -rename table t1 to t1_1, t_many_col_types to t_many_col_types_1 ; +rename table t1 to t1_1, t9 to t9_1 ; -- source include/ps_create.inc -rename table t1 to t1_2, t_many_col_types to t_many_col_types_2 ; +rename table t1 to t1_2, t9 to t9_2 ; create table t1 ( @@ -26,7 +26,7 @@ create table t1 primary key(a) ) ENGINE = MERGE UNION=(t1_1,t1_2) INSERT_METHOD=FIRST; -create table t_many_col_types +create table t9 ( c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, @@ -38,7 +38,7 @@ create table t_many_col_types c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'), c32 set('monday', 'tuesday', 'wednesday'), primary key(c1) -) ENGINE = MERGE UNION=(t_many_col_types_1,t_many_col_types_2) +) ENGINE = MERGE UNION=(t9_1,t9_2) INSERT_METHOD=FIRST; -- source include/ps_renew.inc @@ -47,16 +47,17 @@ INSERT_METHOD=FIRST; # no test of ps_modify1, because insert .. select # is not allowed on MERGE tables # -- source include/ps_modify1.inc +-- source include/ps_conv.inc # Lets's try the same tests with INSERT_METHOD=LAST -drop table t1, t_many_col_types ; +drop table t1, t9 ; create table t1 ( a int, b varchar(30), primary key(a) ) ENGINE = MERGE UNION=(t1_1,t1_2) INSERT_METHOD=LAST; -create table t_many_col_types +create table t9 ( c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, @@ -68,7 +69,7 @@ create table t_many_col_types c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'), c32 set('monday', 'tuesday', 'wednesday'), primary key(c1) -) ENGINE = MERGE UNION=(t_many_col_types_1,t_many_col_types_2) +) ENGINE = MERGE UNION=(t9_1,t9_2) INSERT_METHOD=LAST; -- source include/ps_renew.inc @@ -77,6 +78,7 @@ INSERT_METHOD=LAST; # no test of ps_modify1, because insert .. select # is not allowed on MERGE tables # -- source include/ps_modify1.inc +-- source include/ps_conv.inc drop table t1, t1_1, t1_2, - t_many_col_types_1, t_many_col_types_2, t_many_col_types; + t9_1, t9_2, t9; diff --git a/mysql-test/t/ps_6bdb.test b/mysql-test/t/ps_6bdb.test index 7dbd08f5baa..5db3349279e 100644 --- a/mysql-test/t/ps_6bdb.test +++ b/mysql-test/t/ps_6bdb.test @@ -18,5 +18,6 @@ let $type= 'BDB' ; -- source include/ps_query.inc -- source include/ps_modify.inc -- source include/ps_modify1.inc +-- source include/ps_conv.inc -drop table t1, t_many_col_types; +drop table t1, t9; diff --git a/mysql-test/t/ps_7ndb.test b/mysql-test/t/ps_7ndb.test new file mode 100644 index 00000000000..af669a26400 --- /dev/null +++ b/mysql-test/t/ps_7ndb.test @@ -0,0 +1,377 @@ +############################################### +# # +# Prepared Statements test on NDB tables # +# # +############################################### + +# +# NOTE: PLEASE SEE ps_1general.test (bottom) +# BEFORE ADDING NEW TEST CASES HERE !!! + +use test; + +-- source include/have_ndb.inc +let $type= 'NDB' ; +--disable_warnings +drop table if exists t1, t9 ; +--enable_warnings +eval create table t1 +( + a int not null, b varchar(30), + primary key(a) +) engine = $type ; + +--disable_warnings +drop table if exists t9; +--enable_warnings +# The used table type doesn't support BLOB/TEXT columns. +# (The server would send error 1163 .) +# So we use char(100) instead. +eval create table t9 +( + c1 tinyint not null, c2 smallint, c3 mediumint, c4 int, + c5 integer, c6 bigint, c7 float, c8 double, + c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), + c13 date, c14 datetime, c15 timestamp(14), c16 time, + c17 year, c18 bit, c19 bool, c20 char, + c21 char(10), c22 varchar(30), c23 char(100), c24 char(100), + c25 char(100), c26 char(100), c27 char(100), c28 char(100), + c29 char(100), c30 char(100), c31 enum('one', 'two', 'three'), + c32 set('monday', 'tuesday', 'wednesday'), + primary key(c1) +) engine = $type ; +-- source include/ps_renew.inc + +-- source include/ps_query.inc +# The following line is deactivated, because the ndb storage engine is not able +# to do primary key column updates . +#-- source include/ps_modify.inc +# let's include all statements which will work +--disable_query_log +select '------ delete tests ------' as test_sequence ; +--enable_query_log +--source include/ps_renew.inc + +## delete without parameter +prepare stmt1 from 'delete from t1 where a=2' ; +execute stmt1; +select a,b from t1 where a=2 order by b; +# delete with row not found +execute stmt1; + +## delete with one parameter in the where clause +insert into t1 values(0,NULL); +set @arg00=NULL; +prepare stmt1 from 'delete from t1 where b=?' ; +execute stmt1 using @arg00; +select a,b from t1 where b is NULL ; +set @arg00='one'; +execute stmt1 using @arg00; +select a,b from t1 where b=@arg00; + +## truncate a table +--error 1295 +prepare stmt1 from 'truncate table t1' ; + + +--disable_query_log +select '------ update tests ------' as test_sequence ; +--enable_query_log +--source include/ps_renew.inc + +## update without parameter +prepare stmt1 from 'update t1 set b=''a=two'' where a=2' ; +execute stmt1; +select a,b from t1 where a=2; +# dummy update +execute stmt1; +select a,b from t1 where a=2; + +## update with one parameter in the set clause +set @arg00=NULL; +prepare stmt1 from 'update t1 set b=? where a=2' ; +execute stmt1 using @arg00; +select a,b from t1 where a=2; +set @arg00='two'; +execute stmt1 using @arg00; +select a,b from t1 where a=2; + +## update with one parameter in the where cause +set @arg00=2; +prepare stmt1 from 'update t1 set b=NULL where a=?' ; +execute stmt1 using @arg00; +select a,b from t1 where a=@arg00; +update t1 set b='two' where a=@arg00; +# row not found in update +set @arg00=2000; +execute stmt1 using @arg00; +select a,b from t1 where a=@arg00; + +## update on primary key column (two parameters) +set @arg00=2; +set @arg01=22; +prepare stmt1 from 'update t1 set a=? where a=?' ; +# dummy update +execute stmt1 using @arg00, @arg00; +select a,b from t1 where a=@arg00; +# deactivated primary key column update +# execute stmt1 using @arg01, @arg00; +select a,b from t1 where a=@arg01; +execute stmt1 using @arg00, @arg01; +select a,b from t1 where a=@arg00; +set @arg00=NULL; +set @arg01=2; +# deactivated primary key column update +# execute stmt1 using @arg00, @arg01; +select a,b from t1 order by a; +set @arg00=0; +execute stmt1 using @arg01, @arg00; +select a,b from t1 order by a; + +## update with subquery and several parameters +set @arg00=23; +set @arg01='two'; +set @arg02=2; +set @arg03='two'; +set @arg04=2; +--disable_warnings +drop table if exists t2; +--enable_warnings +# t2 will be of table type 'MYISAM' +create table t2 as select a,b from t1 ; +prepare stmt1 from 'update t1 set a=? where b=? + and a in (select ? from t2 + where b = ? or a = ?)'; +--enable_info +# deactivated primary key column update +# execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ; +--disable_info +select a,b from t1 where a = @arg00 ; +prepare stmt1 from 'update t1 set a=? where b=? + and a not in (select ? from t2 + where b = ? or a = ?)'; +--enable_info +execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ; +--disable_info +select a,b from t1 order by a; +drop table t2 ; +# t2 is now of table type '$type' +# The test battery for table type 'MERGE' gets here only a 'MYISAM' table +eval create table t2 +( + a int not null, b varchar(30), + primary key(a) +) engine = $type ; +insert into t2(a,b) select a, b from t1 ; +prepare stmt1 from 'update t1 set a=? where b=? + and a in (select ? from t2 + where b = ? or a = ?)'; +--enable_info +# deactivated primary key column update +# execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ; +--disable_info +select a,b from t1 where a = @arg00 ; +prepare stmt1 from 'update t1 set a=? where b=? + and a not in (select ? from t2 + where b = ? or a = ?)'; +--enable_info +execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ; +--disable_info +select a,b from t1 order by a ; +drop table t2 ; + +## update with parameters in limit +set @arg00=1; +prepare stmt1 from 'update t1 set b=''bla'' +where a=2 +limit 1'; +execute stmt1 ; +select a,b from t1 where b = 'bla' ; +# currently (May 2004, Version 4.1) it is impossible +-- error 1064 +prepare stmt1 from 'update t1 set b=''bla'' +where a=2 +limit ?'; + +--disable_query_log +select '------ insert tests ------' as test_sequence ; +--enable_query_log +--source include/ps_renew.inc + +## insert without parameter +prepare stmt1 from 'insert into t1 values(5, ''five'' )'; +execute stmt1; +select a,b from t1 where a = 5; + +## insert with one parameter in values part +set @arg00='six' ; +prepare stmt1 from 'insert into t1 values(6, ? )'; +execute stmt1 using @arg00; +select a,b from t1 where b = @arg00; +# the second insert fails, because the first column is primary key +--error 1062 +execute stmt1 using @arg00; +set @arg00=NULL ; +prepare stmt1 from 'insert into t1 values(0, ? )'; +execute stmt1 using @arg00; +select a,b from t1 where b is NULL; + +## insert with two parameter in values part +set @arg00=8 ; +set @arg01='eight' ; +prepare stmt1 from 'insert into t1 values(?, ? )'; +execute stmt1 using @arg00, @arg01 ; +select a,b from t1 where b = @arg01; +# cases derived from client_test.c: test_null() +set @NULL= null ; +set @arg00= 'abc' ; +# execute must fail, because first column is primary key (-> not null) +--error 1048 +execute stmt1 using @NULL, @NULL ; +--error 1048 +execute stmt1 using @NULL, @NULL ; +--error 1048 +execute stmt1 using @NULL, @arg00 ; +--error 1048 +execute stmt1 using @NULL, @arg00 ; +let $1 = 2; +while ($1) +{ + eval set @arg01= 10000 + $1 ; + execute stmt1 using @arg01, @arg00 ; + dec $1; +} +select * from t1 where a > 10000 order by a ; +delete from t1 where a > 10000 ; +let $1 = 2; +while ($1) +{ + eval set @arg01= 10000 + $1 ; + execute stmt1 using @arg01, @NULL ; + dec $1; +} +select * from t1 where a > 10000 order by a ; +delete from t1 where a > 10000 ; +let $1 = 10; +while ($1) +{ + eval set @arg01= 10000 + $1 ; + execute stmt1 using @arg01, @arg01 ; + dec $1; +} +select * from t1 where a > 10000 order by a ; +delete from t1 where a > 10000 ; + + +## insert with two rows in values part +set @arg00=81 ; +set @arg01='8-1' ; +set @arg02=82 ; +set @arg03='8-2' ; +prepare stmt1 from 'insert into t1 values(?,?),(?,?)'; +execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; +select a,b from t1 where a in (@arg00,@arg02) order by a ; + +## insert with two parameter in the set part +set @arg00=9 ; +set @arg01='nine' ; +prepare stmt1 from 'insert into t1 set a=?, b=? '; +execute stmt1 using @arg00, @arg01 ; +select a,b from t1 where a = @arg00 ; + +## insert with parameters in the ON DUPLICATE KEY part +set @arg00=6 ; +set @arg01=1 ; +prepare stmt1 from 'insert into t1 set a=?, b=''sechs'' + on duplicate key update a=a + ?, b=concat(b,''modified'') '; +# There is no primary key collision, so there will be no key column update +# If a key column update would be necessary occurs BUG#4312 +# deactivated, activate when BUG#4312: is solved +# execute stmt1 using @arg00, @arg01; +select * from t1 order by a; +set @arg00=81 ; +set @arg01=1 ; +# deactivated, activate when BUG#4312: is solved +# execute stmt1 using @arg00, @arg01; + +## insert, autoincrement column and ' SELECT LAST_INSERT_ID() ' +# cases derived from client_test.c: test_bug3117() +--disable_warnings +drop table if exists t2 ; +--enable_warnings +# The test battery for table type 'MERGE' gets here only a 'MYISAM' table +eval create table t2 (id int auto_increment primary key) +ENGINE= $type ; +prepare stmt1 from ' select last_insert_id() ' ; +insert into t2 values (NULL) ; +execute stmt1 ; +insert into t2 values (NULL) ; +execute stmt1 ; +drop table t2 ; + +## many parameters +set @1000=1000 ; +set @x1000_2="x1000_2" ; +set @x1000_3="x1000_3" ; + +set @x1000="x1000" ; +set @1100=1100 ; +set @x1100="x1100" ; +set @100=100 ; +set @updated="updated" ; +insert into t1 values(1000,'x1000_1') ; +# deactivated, activate when BUG#4312: is solved +# insert into t1 values(@1000,@x1000_2),(@1000,@x1000_3) +# on duplicate key update a = a + @100, b = concat(b,@updated) ; +select a,b from t1 where a >= 1000 order by a ; +delete from t1 where a >= 1000 ; +insert into t1 values(1000,'x1000_1') ; +prepare stmt1 from ' insert into t1 values(?,?),(?,?) + on duplicate key update a = a + ?, b = concat(b,?) '; +# deactivated, activate when BUG#4312: is solved +# execute stmt1 using @1000, @x1000_2, @1000, @x1000_3, @100, @updated ; +select a,b from t1 where a >= 1000 order by a ; +delete from t1 where a >= 1000 ; +insert into t1 values(1000,'x1000_1') ; +# deactivated, activate when BUG#4312: is solved +# execute stmt1 using @1000, @x1000_2, @1100, @x1000_3, @100, @updated ; +select a,b from t1 where a >= 1000 order by a ; +delete from t1 where a >= 1000 ; + +## replace +--error 1295 +prepare stmt1 from ' replace into t1 (a,b) select 100, ''hundred'' '; + +## multi table statements +--disable_query_log +select '------ multi table tests ------' as test_sequence ; +--enable_query_log +# cases derived from client_test.c: test_multi +delete from t1 ; +delete from t9 ; +insert into t1(a,b) values (1, 'one'), (2, 'two'), (3, 'three') ; +insert into t9 (c1,c21) + values (1, 'one'), (2, 'two'), (3, 'three') ; +prepare stmt_delete from " delete t1, t9 + from t1, t9 where t1.a=t9.c1 and t1.b='updated' "; +prepare stmt_update from " update t1, t9 + set t1.b='updated', t9.c21='updated' + where t1.a=t9.c1 and t1.a=? "; +prepare stmt_select1 from " select a, b from t1 order by a" ; +prepare stmt_select2 from " select c1, c21 from t9 order by c1" ; +set @arg00= 1 ; +let $1= 3 ; +while ($1) +{ + execute stmt_update using @arg00 ; + execute stmt_delete ; + execute stmt_select1 ; + execute stmt_select2 ; + set @arg00= @arg00 + 1 ; + dec $1 ; +} + +-- source include/ps_modify1.inc +-- source include/ps_conv.inc + +drop table t1, t9; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index e5dc69d21aa..ed89184a0bc 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -561,6 +561,17 @@ unlock table; drop table t1,t2; set query_cache_wlock_invalidate=default; +# +# hiding real table stored in query cache by temporary table +# +CREATE TABLE t1 (id INT PRIMARY KEY); +insert into t1 values (1),(2),(3); +select * from t1; +create temporary table t1 (a int not null auto_increment +primary key); +select * from t1; +drop table t1; +drop table t1; # # Test character set related variables: @@ -624,6 +635,40 @@ DROP TABLE t1; set character_set_results=null; select @@character_set_results; set character_set_results=default; +# +# query cache and environment variables +# +# max_sort_length +set GLOBAL query_cache_size=1355776; +create table t1 (id int auto_increment primary key, c char(25)); +insert into t1 set c = repeat('x',24); +insert into t1 set c = concat(repeat('x',24),'x'); +insert into t1 set c = concat(repeat('x',24),'w'); +insert into t1 set c = concat(repeat('x',24),'y'); +set max_sort_length=200; +select c from t1 order by c, id; +reset query cache; +set max_sort_length=20; +select c from t1 order by c, id; +set max_sort_length=200; +select c from t1 order by c, id; +set max_sort_length=default; +# sql_mode +select '1' || '3' from t1; +set SQL_MODE=oracle; +select '1' || '3' from t1; +set SQL_MODE=default; +drop table t1; +# group_concat_max_len +create table t1 (a varchar(20), b int); +insert into t1 values ('12345678901234567890', 1); +set group_concat_max_len=10; +select group_concat(a) FROM t1 group by b; +set group_concat_max_len=1024; +select group_concat(a) FROM t1 group by b; +set group_concat_max_len=default; +drop table t1; + # comments before command # create table t1 (a int); diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 300e9574c2d..d8794b2f394 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -416,6 +416,5 @@ select count(*) from t2 where x < -16; select count(*) from t2 where x = -16; select count(*) from t2 where x > -16; select count(*) from t2 where x = 18446744073709551601; - -drop table t1; +drop table t1,t2; diff --git a/mysql-test/t/rename.test b/mysql-test/t/rename.test index e6dc6ce9456..497ff721c99 100644 --- a/mysql-test/t/rename.test +++ b/mysql-test/t/rename.test @@ -5,7 +5,7 @@ --disable_warnings drop table if exists t0,t1,t2,t3,t4; # Clear up from other tests (to ensure that SHOW TABLES below is right) -drop table if exists t0,t5,t6,t7,t8,t9; +drop table if exists t0,t5,t6,t7,t8,t9,t1_1,t1_2,t9_1,t9_2; --enable_warnings create table t0 SELECT 1,"table 1"; diff --git a/mysql-test/t/rollback.test b/mysql-test/t/rollback.test index 87f59417d90..3cb1ea3024b 100644 --- a/mysql-test/t/rollback.test +++ b/mysql-test/t/rollback.test @@ -5,6 +5,8 @@ --disable_warnings drop table if exists t1; --enable_warnings +# PS doesn't work with BEGIN ... ROLLBACK +--disable_ps_protocol create table t1 (n int not null primary key) engine=myisam; begin work; diff --git a/mysql-test/t/rpl_charset.test b/mysql-test/t/rpl_charset.test index 74112ac44fe..ab2e0d30ec7 100644 --- a/mysql-test/t/rpl_charset.test +++ b/mysql-test/t/rpl_charset.test @@ -149,6 +149,24 @@ INSERT INTO t1 (c1, c2) VALUES (', ',', '); select hex(c1), hex(c2) from t1; sync_slave_with_master; select hex(c1), hex(c2) from t1; + +# Now test for BUG##5705: SET CHARATER_SET_SERVERetc will be lost if +# STOP SLAVE before following query + +stop slave; +delete from t1; +change master to master_log_pos=5801; +start slave until master_log_file='master-bin.000001', master_log_pos=5937; +# Slave is supposed to stop _after_ the INSERT, even though 5937 is +# the position of the beginning of the INSERT; after SET slave is not +# supposed to increment position. +wait_for_slave_to_stop; +# When you merge this into 5.0 you will have to adjust positions +# above; the first master_log_pos above should be the one of the SET, +# the second should be the one of the INSERT. +start slave; +sync_with_master; +select hex(c1), hex(c2) from t1; connection master; drop table t1; sync_slave_with_master; 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/rpl_redirect.test b/mysql-test/t/rpl_redirect.test index d6f37e7f7f6..d505351cc69 100644 --- a/mysql-test/t/rpl_redirect.test +++ b/mysql-test/t/rpl_redirect.test @@ -3,6 +3,8 @@ # source include/master-slave.inc; +# We disable this for now as PS doesn't handle redirection +--disable_ps_protocol #first, make sure the slave has had enough time to register save_master_pos; diff --git a/mysql-test/t/rpl_set_charset.test b/mysql-test/t/rpl_set_charset.test new file mode 100644 index 00000000000..269074b1c42 --- /dev/null +++ b/mysql-test/t/rpl_set_charset.test @@ -0,0 +1,33 @@ +source include/master-slave.inc; +--disable_warnings +drop database if exists mysqltest1; +# 4.1 bases its conversion on the db's charset, +# while 4.0 uses the part of "SET CHARACTER SET" after "_". +# So for 4.1 we add a clause to CREATE DATABASE. +create database mysqltest1 /*!40100 character set latin2 */; +use mysqltest1; +drop table if exists t1; +--enable_warnings +create table t1 (a varchar(255) character set latin2, b varchar(4)); +SET CHARACTER SET cp1250_latin2; +INSERT INTO t1 VALUES ('','80'); +INSERT INTO t1 VALUES ('','90'); +INSERT INTO t1 VALUES ('','A0'); +INSERT INTO t1 VALUES ('','B0'); +INSERT INTO t1 VALUES ('','C0'); +INSERT INTO t1 VALUES ('','D0'); +INSERT INTO t1 VALUES ('','E0'); +INSERT INTO t1 VALUES ('','F0'); +select "--- on master ---"; +select hex(a),b from t1 order by b; +save_master_pos; +connection slave; +sync_with_master; +use mysqltest1; +select "--- on slave ---"; +select hex(a),b from t1 order by b; +connection master; +drop database mysqltest1; +save_master_pos; +connection slave; +sync_with_master; diff --git a/mysql-test/t/rpl_user_variables.test b/mysql-test/t/rpl_user_variables.test index 7aab1c23c1a..01d4b0e033c 100644 --- a/mysql-test/t/rpl_user_variables.test +++ b/mysql-test/t/rpl_user_variables.test @@ -2,6 +2,9 @@ # Test of replicating user variables # source include/master-slave.inc; +# Disable PS as the log positions differs +--disable_ps_protocol + # Clean up old slave's binlogs. # The slave is started with --log-slave-updates diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index a1a97a17fd5..3619cf116d1 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -8,6 +8,8 @@ --disable_warnings drop table if exists t1,t2,t3,t4; +# The following may be left from older tests +drop table if exists t1_1,t1_2,t9_1,t9_2; drop view if exists v1; --enable_warnings @@ -1705,12 +1707,18 @@ select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.compan select count(*) from t2; select count(*) from t2 where fld1 < 098024; +# PS does correct pre-zero here. MySQL can't do it as it returns a number. +--disable_ps_protocol select min(fld1) from t2 where fld1>= 098024; +--enable_ps_protocol select max(fld1) from t2 where fld1>= 098024; select count(*) from t3 where price2=76234234; select count(*) from t3 where companynr=512 and price2=76234234; explain select min(fld1),max(fld1),count(*) from t2; +# PS does correct pre-zero here. MySQL can't do it as it returns a number. +--disable_ps_protocol select min(fld1),max(fld1),count(*) from t2; +--enable_ps_protocol select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742; select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78; select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20; @@ -1881,3 +1889,24 @@ select * from t3 where s = 'one'; select * from t1,t2 where t1.s = t2.s; select * from t2,t3 where t2.s = t3.s; drop table t1, t2, t3; + +# +# Covering index is mentioned in EXPLAIN output for const tables (bug #5333) +# + +CREATE TABLE t1 ( + i int(11) NOT NULL default '0', + c char(10) NOT NULL default '', + PRIMARY KEY (i), + UNIQUE KEY c (c) +) ENGINE=MyISAM; + +INSERT INTO t1 VALUES (1,'a'); +INSERT INTO t1 VALUES (2,'b'); +INSERT INTO t1 VALUES (3,'c'); + +EXPLAIN SELECT i FROM t1 WHERE i=1; + +EXPLAIN SELECT i FROM t1 WHERE i=1; + +DROP TABLE t1; diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 9ed1ac2d63e..9e18f1cd88c 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -5,6 +5,10 @@ --disable_warnings drop table if exists t1,t2; drop database if exists mysqltest; + +delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; +delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; +flush privileges; --enable_warnings create table t1 (a int not null primary key, b int not null,c int not null, key(b,c)); diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test index c7b6510db9d..26a5d07d951 100644 --- a/mysql-test/t/sql_mode.test +++ b/mysql-test/t/sql_mode.test @@ -30,6 +30,27 @@ show create table t1; drop table t1; # +# BUG#5318 - failure: 'IGNORE_SPACE' affects numeric values after DEFAULT +# +# Force the usage of the default +set session sql_mode = ''; +# statement for comparison, value starts with '.' +create table t1 ( min_num dec(6,6) default .000001); +show create table t1; +drop table t1 ; +# +set session sql_mode = 'IGNORE_SPACE'; +# statement for comparison, value starts with '0' +create table t1 ( min_num dec(6,6) default 0.000001); +show create table t1; +drop table t1 ; +# This statement fails, value starts with '.' +create table t1 ( min_num dec(6,6) default .000001); +show create table t1; +drop table t1 ; + + +# # test for # WL 1941 "NO_C_ESCAPES sql_mode" # diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 7be494bd0e7..31d9c8154d5 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -5,6 +5,8 @@ # connection in a separate thread. # --source include/not_embedded.inc +# PS causes different statistics +--disable_ps_protocol connect (con1,localhost,root,,); connect (con2,localhost,root,,); diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 3d10b88da5c..16556c4864c 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -548,8 +548,8 @@ drop table t1, t2; CREATE TABLE `t1` (`i` int(11) NOT NULL default '0',PRIMARY KEY (`i`)) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES (1); --- error 1111 UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i)); +select * from t1; drop table t1; #test of uncacheable subqueries @@ -694,12 +694,11 @@ CREATE TABLE `t1` ( ) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES (1); --- error 1111 UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i)); --- error 1111 UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i)); -- error 1109 UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t); +select * from t1; drop table t1; # @@ -1269,6 +1268,22 @@ SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.how CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues); SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues; SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues; --- error 1054 SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues; drop table t1; + +create table t1 (x int); +select (select b.x from t1 as b where b.x=a.x) from t1 as a where a.x=2 group by a.x; +drop table t1; + +# +# Test of correct maybe_null flag returning by subquwery for temporary table +# creation +# +CREATE TABLE `t1` ( `master` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `slave` int(10) unsigned NOT NULL default '0', `access` int(10) unsigned NOT NULL default '0', UNIQUE KEY `access_u` (`master`,`map`,`slave`)); +INSERT INTO `t1` VALUES (1,0,0,700),(1,1,1,400),(1,5,5,400),(1,12,12,400),(1,12,32,400),(4,12,32,400); +CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL default '0', `pid` int(10) unsigned NOT NULL default '0', `map` smallint(6) unsigned NOT NULL default '0', `level` tinyint(4) unsigned NOT NULL default '0', `title` varchar(255) default NULL, PRIMARY KEY (`id`,`pid`,`map`), KEY `level` (`level`), KEY `id` (`id`,`map`)) ; +INSERT INTO `t2` VALUES (6,5,12,7,'a'),(12,0,0,7,'a'),(12,1,0,7,'a'),(12,5,5,7,'a'),(12,5,12,7,'a'); +-- error 1054 +SELECT b.sc FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b; +SELECT b.ac FROM (SELECT (SELECT a.access FROM t1 a WHERE a.map = op.map AND a.slave = op.pid AND a.master = 1) ac FROM t2 op WHERE op.id = 12 AND op.map = 0) b; +drop tables t1,t2; diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index 8c13171d221..5f4badb3624 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -111,3 +111,17 @@ create table t2 (a int) engine=innodb; insert into t2 values (1),(2),(3),(4); select a, sum(b) as b from t1 group by a having b > (select max(a) from t2); drop table t1, t2; + +# +# bug #5220 test suite +# +CREATE TABLE `t1` ( `unit` varchar(50) NOT NULL default '', `ingredient` varchar(50) NOT NULL default '') ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE `t2` ( `ingredient` varchar(50) NOT NULL default '', `unit` varchar(50) NOT NULL default '', PRIMARY KEY (ingredient, unit)) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO `t1` VALUES ('xx','yy'); +INSERT INTO `t2` VALUES ('yy','xx'); + +SELECT R.unit, R.ingredient FROM t1 R WHERE R.ingredient IN (SELECT N.ingredient FROM t2 N WHERE N.unit = R.unit); + +drop table t1, t2; diff --git a/mysql-test/t/timezone2.test b/mysql-test/t/timezone2.test index 15ac3416b29..ad8089e1a37 100644 --- a/mysql-test/t/timezone2.test +++ b/mysql-test/t/timezone2.test @@ -199,3 +199,31 @@ insert into t1 (ts) values (now()); select convert_tz(ts, @@time_zone, 'Japan') from t1; drop table t1; +# +# Test for bug #6116 "SET time_zone := ... requires access to mysql.time_zone +# tables". We should allow implicit access to time zone description tables +# even for unprivileged users. +# + +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 usage on mysqltest.* to mysqltest_1@localhost; +connect (tzuser, localhost, mysqltest_1,,); +connection tzuser; +show grants for current_user(); +set time_zone= '+00:00'; +set time_zone= 'Europe/Moscow'; +select convert_tz('2004-10-21 19:00:00', 'Europe/Moscow', 'UTC'); +# But still these two statements should not work: +--error 1044 +select * from mysql.time_zone_name; +--error 1044 +select Name, convert_tz('2004-10-21 19:00:00', Name, 'UTC') from mysql.time_zone_name; + +connection default; +delete from mysql.user where user like 'mysqltest\_%'; +flush privileges; diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test index bd571deff49..b67fa7a552d 100644 --- a/mysql-test/t/type_blob.test +++ b/mysql-test/t/type_blob.test @@ -16,7 +16,10 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7; CREATE TABLE t1 (a blob, b text, c blob(250), d text(70000), e text(70000000)); show columns from t1; +# PS doesn't give errors on prepare yet +--disable_ps_protocol CREATE TABLE t2 (a char(257), b varbinary(70000), c varchar(70000000)); +--enable_ps_protocol show columns from t2; create table t3 (a long, b long byte); show create TABLE t3; diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test index b8f32107892..dc2e4d0f469 100644 --- a/mysql-test/t/type_enum.test +++ b/mysql-test/t/type_enum.test @@ -45,3 +45,30 @@ create table t1 (a enum(0xE4, '1', '2') not null default 0xE4); show columns from t1; show create table t1; drop table t1; + + +# +# Bug #5628 German characters in field-defs will be '?' +# with some table definitions +# +set names latin1; +CREATE TABLE t1 ( + a INT default 1, + b ENUM('value','_value','') character set latin1 NOT NULL +); +show create table t1; +show columns from t1; +drop table t1; + +# +# Bugs #6154, 6206: ENUMs are not case sensitive even if declared BINARY +# +CREATE TABLE t1 (c enum('a', 'A') BINARY); +INSERT INTO t1 VALUES ('a'),('A'); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c enum('ae','oe','ue','ss') collate latin1_german2_ci); +INSERT INTO t1 VALUES (''),(''),(''),(''); +SELECT * FROM t1; +DROP TABLE t1; diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test index 4b627ea9b99..216d5bbd286 100644 --- a/mysql-test/t/type_float.test +++ b/mysql-test/t/type_float.test @@ -39,7 +39,10 @@ create table t1 (c1 double, c2 varchar(20)); insert t1 values (121,"16"); select c1 + c1 * (c2 / 100) as col from t1; create table t2 select c1 + c1 * (c2 / 100) as col1, round(c1, 5) as col2, round(c1, 35) as col3, sqrt(c1*1e-15) col4 from t1; +# Floats are a bit different in PS +--disable_ps_protocol select * from t2; +--enable_ps_protocol show create table t2; drop table t1,t2; diff --git a/mysql-test/t/type_set.test b/mysql-test/t/type_set.test index 60aa8dcfcf2..e4aeecb2c79 100644 --- a/mysql-test/t/type_set.test +++ b/mysql-test/t/type_set.test @@ -14,3 +14,24 @@ show create table t1; drop table t1; CREATE TABLE t1 ( user varchar(64) NOT NULL default '', path varchar(255) NOT NULL default '', privilege set('select','RESERVED30','RESERVED29','RESERVED28','RESERVED27','RESERVED26', 'RESERVED25','RESERVED24','data.delete','RESERVED22','RESERVED21', 'RESERVED20','data.insert.none','data.insert.approve', 'data.insert.delete','data.insert.move','data.insert.propose', 'data.insert.reject','RESERVED13','RESERVED12','RESERVED11','RESERVED10', 'RESERVED09','data.update','RESERVED07','RESERVED06','RESERVED05', 'RESERVED04','metadata.delete','metadata.put','RESERVED01','RESERVED00') NOT NULL default '', KEY user (user) ) ENGINE=MyISAM CHARSET=utf8; DROP TABLE t1; + +# +# Check that SET is case sensitive with a binary collation +# +set names latin1; +create table t1 (s set ('a','A') character set latin1 collate latin1_bin); +show create table t1; +insert into t1 values ('a'),('a,A'),('A,a'),('A'); +select s from t1 order by s; +drop table t1; + +# +# Check that SET honors a more complex collation correctly +# +CREATE TABLE t1 (c set('ae','oe','ue','ss') collate latin1_german2_ci); +INSERT INTO t1 VALUES (''),(''),(''),(''); +INSERT INTO t1 VALUES ('ae'),('oe'),('ue'),('ss'); +INSERT INTO t1 VALUES (',,,'); +INSERT INTO t1 VALUES ('ae,oe,ue,ss'); +SELECT c FROM t1 ORDER BY c; +DROP TABLE t1; diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test index a644197f757..a8a0cf8703c 100644 --- a/mysql-test/t/type_timestamp.test +++ b/mysql-test/t/type_timestamp.test @@ -62,7 +62,6 @@ INSERT INTO t1 VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000); SELECT * FROM t1; drop table t1; -show variables like 'new'; create table t1 (t2 timestamp(2), t4 timestamp(4), t6 timestamp(6), t8 timestamp(8), t10 timestamp(10), t12 timestamp(12), t14 timestamp(14)); @@ -71,7 +70,6 @@ insert t1 values (0,0,0,0,0,0,0), "1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59", "1997-12-31 23:47:59"); select * from t1; -set new=1; select * from t1; drop table t1; @@ -234,13 +232,57 @@ alter table t1 add i int default 10; select * from t1; drop table t1; - -# Test for bug #4491, TIMESTAMP(19) should be possible to create and not -# only read in 4.0 # -create table t1 (ts timestamp(19)); +# Test for TIMESTAMP columns which are able to store NULLs +# + +# Unlike for default TIMESTAMP fields we don't interpret first field +# in this table as TIMESTAMP with DEFAULT NOW() ON UPDATE NOW() properties. +create table t1 (a timestamp null, b timestamp null); show create table t1; -set TIMESTAMP=1000000000; +insert into t1 values (NULL, NULL); +SET TIMESTAMP=1000000017; insert into t1 values (); select * from t1; drop table t1; + +# But explicit auto-set properties still should be OK. +create table t1 (a timestamp null default current_timestamp on update current_timestamp, b timestamp null); +show create table t1; +insert into t1 values (NULL, NULL); +SET TIMESTAMP=1000000018; +insert into t1 values (); +select * from t1; +drop table t1; + +# It is also OK to specify NULL as default explicitly for such fields. +# This is also a test for bug #2464, DEFAULT keyword in INSERT statement +# should return default value for column. + +create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00'); +show create table t1; +insert into t1 values (NULL, NULL); +insert into t1 values (DEFAULT, DEFAULT); +select * from t1; +drop table t1; + +# +# Let us test behavior of ALTER TABLE when it converts columns +# containing NULL to TIMESTAMP columns. +# +create table t1 (a bigint, b bigint); +insert into t1 values (NULL, NULL), (20030101000000, 20030102000000); +set timestamp=1000000019; +alter table t1 modify a timestamp, modify b timestamp; +select * from t1; +drop table t1; + +# +# Test for bug #4131, TIMESTAMP columns missing minutes and seconds when +# using GROUP BY in @@new=1 mode. +# +create table t1 (a char(2), t timestamp); +insert into t1 values ('a', '2004-01-01 00:00:00'), ('a', '2004-01-01 01:00:00'), + ('b', '2004-02-01 00:00:00'); +select max(t) from t1 group by a; +drop table t1; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 263f631a65f..36027e8c4cb 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -5,6 +5,9 @@ --disable_warnings drop table if exists t1,t2,t3,t4,t5,t6; --enable_warnings +# PS doesn't work correctly with found_rows: to be fixed +--disable_ps_protocol + CREATE TABLE t1 (a int not null, b char (10) not null); insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); @@ -73,6 +76,7 @@ select * from t1 union select SQL_BUFFER_RESULT * from t2; # Test CREATE, INSERT and REPLACE create table t3 select a,b from t1 union all select a,b from t2; insert into t3 select a,b from t1 union all select a,b from t2; +# PS can't handle REPLACE ... SELECT replace into t3 select a,b as c from t1 union all select a,b from t2; drop table t1,t2,t3; @@ -575,3 +579,78 @@ PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION (SELECT * FROM t1 AS PARTITIONED, t2 AS PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1); drop table t1,t2; + +# +# merging ENUM and SET fields in one UNION +# +create table t1 (a ENUM('Yes', 'No') NOT NULL); +create table t2 (a ENUM('aaa', 'bbb') NOT NULL); +insert into t1 values ('No'); +insert into t2 values ('bbb'); +create table t3 (a SET('Yes', 'No') NOT NULL); +create table t4 (a SET('aaa', 'bbb') NOT NULL); +insert into t3 values (1); +insert into t4 values (3); +select "1" as a union select a from t1; +select a as a from t1 union select "1"; +select a as a from t2 union select a from t1; +select "1" as a union select a from t3; +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; + diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 6ca75cf0c26..aeefa3c33f5 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -128,3 +128,36 @@ insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6), delete from m1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3); select * from t1; drop table t1; + +# +# Bug#5553 - Multi table UPDATE IGNORE fails on duplicate keys +# + +CREATE TABLE t1 ( + `colA` int(10) unsigned NOT NULL auto_increment, + `colB` int(11) NOT NULL default '0', + PRIMARY KEY (`colA`) +); +INSERT INTO t1 VALUES (4433,5424); +CREATE TABLE t2 ( + `colC` int(10) unsigned NOT NULL default '0', + `colA` int(10) unsigned NOT NULL default '0', + `colD` int(10) unsigned NOT NULL default '0', + `colE` int(10) unsigned NOT NULL default '0', + `colF` int(10) unsigned NOT NULL default '0', + PRIMARY KEY (`colC`,`colA`,`colD`,`colE`) +); +INSERT INTO t2 VALUES (3,4433,10005,495,500); +INSERT INTO t2 VALUES (3,4433,10005,496,500); +INSERT INTO t2 VALUES (3,4433,10009,494,500); +INSERT INTO t2 VALUES (3,4433,10011,494,500); +INSERT INTO t2 VALUES (3,4433,10005,497,500); +INSERT INTO t2 VALUES (3,4433,10013,489,500); +INSERT INTO t2 VALUES (3,4433,10005,494,500); +INSERT INTO t2 VALUES (3,4433,10005,493,500); +INSERT INTO t2 VALUES (3,4433,10005,492,500); +UPDATE IGNORE t2,t1 set t2.colE = t2.colE + 1,colF=0 WHERE t1.colA = t2.colA AND (t1.colB & 4096) > 0 AND (colE + 1) < colF; +SELECT * FROM t2; +DROP TABLE t1; +DROP TABLE t2; + diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 8c318497c22..67a05768595 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -347,3 +347,22 @@ show global variables like 'log_warnings'; set global log_warnings = @tstlw; show global variables like 'log_warnings'; +# +# BUG#4788 show create table provides incorrect statement +# +# What default width have numeric types? +create table t1 ( + c1 tinyint, + c2 smallint, + c3 mediumint, + c4 int, + c5 bigint); +show create table t1; +drop table t1; +# +# What types and widths have variables? +set @arg00= 8, @arg01= 8.8, @arg02= 'a string'; +create table t1 as select @arg00 as c1, @arg01 as c2, @arg02 as c3; +show create table t1; +drop table t1; + diff --git a/mysql-test/t/warnings.test b/mysql-test/t/warnings.test index b6042df51f1..4bd659606f6 100644 --- a/mysql-test/t/warnings.test +++ b/mysql-test/t/warnings.test @@ -26,8 +26,11 @@ show warnings limit 1; drop database if exists not_exists_db; show count(*) warnings; create table t1(id int); +# PS doesn't give warnings on prepare +--disable_ps_protocol create table if not exists t1(id int); select @@warning_count; +--enable_ps_protocol drop table t1; # @@ -36,7 +39,10 @@ drop table t1; create table t1(a tinyint, b int not null, c date, d char(5)); load data infile '../../std_data/warnings_loaddata.dat' into table t1 fields terminated by ','; +# PS doesn't work good with @@warning_count +--disable_ps_protocol select @@warning_count; +--enable_ps_protocol drop table t1; # @@ -74,7 +80,9 @@ enable_query_log; alter table t1 add b char; set max_error_count=10; update t1 set b=a; +--disable_ps_protocol select @@warning_count; +--enable_ps_protocol # # Test for handler type @@ -87,12 +95,15 @@ drop table t1; # # Test for deprecated TYPE= syntax # + +# PS doesn't give warnings on prepare +--disable_ps_protocol create table t1 (id int) type=heap; alter table t1 type=myisam; drop table t1; +--enable_ps_protocol # # Test for deprecated table_type variable # set table_type=MYISAM; - |