=== modified file 'mysql-test/r/federated.result' --- a/mysql-test/r/federated.result 2008-08-15 06:40:05 +0000 +++ b/mysql-test/r/federated.result 2008-09-04 17:01:57 +0000 @@ -9,6 +9,10 @@ CREATE DATABASE federated; DROP DATABASE IF EXISTS federated; CREATE DATABASE federated; +SET @OLD_MASTER_CONCURRENT_INSERT= @@GLOBAL.CONCURRENT_INSERT; +SET @@GLOBAL.CONCURRENT_INSERT= 0; +SET @OLD_SLAVE_CONCURRENT_INSERT= @@GLOBAL.CONCURRENT_INSERT; +SET @@GLOBAL.CONCURRENT_INSERT= 0; DROP TABLE IF EXISTS federated.t1; Warnings: Note 1051 Unknown table 't1' @@ -83,7 +87,7 @@ ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1' INSERT INTO federated.t2 (id, name) VALUES (1, 'foo'); INSERT INTO federated.t2 (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.t2; +SELECT * FROM federated.t2 ORDER BY id, name; id name 1 foo 2 fee @@ -108,7 +112,7 @@ CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1%'; INSERT INTO federated.t1 (id, name) VALUES (1, 'foo'); INSERT INTO federated.t1 (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.t1; +SELECT * FROM federated.t1 ORDER BY id,name; id name 1 foo 2 fee @@ -122,7 +126,7 @@ CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1%'; INSERT INTO federated.`t1%` (id, name) VALUES (1, 'foo'); INSERT INTO federated.`t1%` (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.`t1%`; +SELECT * FROM federated.`t1%` ORDER BY id, name; id name 1 foo 2 fee @@ -160,6 +164,7 @@ SELECT * FROM federated.t1; id name other created 1 First Name 11111 2004-04-04 04:04:04 +10 Tenth Name 101010 2004-04-04 04:04:04 2 Second Name 22222 2004-04-04 04:04:04 3 Third Name 33333 2004-04-04 04:04:04 4 Fourth Name 44444 2004-04-04 04:04:04 @@ -168,7 +173,6 @@ 7 Seventh Name 77777 2004-04-04 04:04:04 8 Eigth Name 88888 2004-04-04 04:04:04 9 Ninth Name 99999 2004-04-04 04:04:04 -10 Tenth Name 101010 2004-04-04 04:04:04 SELECT * FROM federated.t1 WHERE id = 5; id name other created 5 Fifth Name 55555 2004-04-04 04:04:04 @@ -182,6 +186,7 @@ id name other created SELECT * FROM federated.t1 WHERE name like '%th%'; id name other created +10 Tenth Name 101010 2004-04-04 04:04:04 3 Third Name 33333 2004-04-04 04:04:04 4 Fourth Name 44444 2004-04-04 04:04:04 5 Fifth Name 55555 2004-04-04 04:04:04 @@ -189,7 +194,6 @@ 7 Seventh Name 77777 2004-04-04 04:04:04 8 Eigth Name 88888 2004-04-04 04:04:04 9 Ninth Name 99999 2004-04-04 04:04:04 -10 Tenth Name 101010 2004-04-04 04:04:04 UPDATE federated.t1 SET name = '3rd name' WHERE id = 3; SELECT * FROM federated.t1 WHERE name = '3rd name'; id name other created @@ -310,6 +314,7 @@ SELECT * FROM federated.t1; id name other created 1 First Name 11111 2004-01-01 01:01:01 +10 Tenth Name 101010 2005-03-12 12:00:01 2 Second Name 22222 2004-01-23 02:43:00 3 Third Name 33333 2004-02-14 02:14:00 4 Fourth Name 44444 2003-04-05 00:00:00 @@ -318,7 +323,6 @@ 7 Seventh Name 77777 2003-12-12 18:32:00 8 Eigth Name 88888 2005-03-12 11:00:00 9 Ninth Name 99999 2005-03-12 11:00:01 -10 Tenth Name 101010 2005-03-12 12:00:01 SELECT * FROM federated.t1 WHERE id = 5; id name other created 5 Fifth Name 55555 2001-02-02 02:02:02 @@ -330,6 +334,7 @@ 4 Fourth Name 44444 2003-04-05 00:00:00 SELECT * FROM federated.t1 WHERE name like '%th%'; id name other created +10 Tenth Name 101010 2005-03-12 12:00:01 3 Third Name 33333 2004-02-14 02:14:00 4 Fourth Name 44444 2003-04-05 00:00:00 5 Fifth Name 55555 2001-02-02 02:02:02 @@ -337,7 +342,6 @@ 7 Seventh Name 77777 2003-12-12 18:32:00 8 Eigth Name 88888 2005-03-12 11:00:00 9 Ninth Name 99999 2005-03-12 11:00:01 -10 Tenth Name 101010 2005-03-12 12:00:01 UPDATE federated.t1 SET name = '3rd name' WHERE id = 3; SELECT * FROM federated.t1 WHERE name = '3rd name'; id name other created @@ -444,17 +448,17 @@ 7 Seventh Name NULL SELECT * FROM federated.t1 WHERE name IS NULL; id name other +10 NULL fee fie foe fum 4 NULL NULL -10 NULL fee fie foe fum SELECT * FROM federated.t1 WHERE name IS NULL and other IS NULL; id name other 4 NULL NULL SELECT * FROM federated.t1 WHERE name IS NULL or other IS NULL; id name other +10 NULL fee fie foe fum 2 Second Name NULL 4 NULL NULL 7 Seventh Name NULL -10 NULL fee fie foe fum UPDATE federated.t1 SET name = 'Fourth Name', other = 'four four four' WHERE name IS NULL AND other IS NULL; @@ -466,6 +470,7 @@ SELECT * FROM federated.t1; id name other 1 First Name 11111 +10 Tenth Name fee fie foe fum 2 Second Name two two two two 3 Third Name 33333 4 Fourth Name four four four @@ -474,7 +479,6 @@ 7 Seventh Name seven seven 8 Eigth Name 88888 9 Ninth Name 99999 -10 Tenth Name fee fie foe fum DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( `id` int(20) NOT NULL auto_increment, @@ -655,8 +659,8 @@ SELECT * FROM federated.t1 WHERE (id = 5 AND col2 = 'five 5 five five 5') OR (col2 = 'three Three' AND col3 = 33); id col1 col2 col3 col4 +3 3 three Three 33 33333 5 5 five 5 five five 5 5 55555 -3 3 three Three 33 33333 SELECT * FROM federated.t1 WHERE (id = 5 AND col2 = 'Two two') OR (col2 = 444 AND col3 = 4444444); id col1 col2 col3 col4 @@ -667,25 +671,25 @@ OR col4 = 4444444; id col1 col2 col3 col4 1 1 one One 11 1111 +10 10 Tenth ten TEN 1010101 1010 2 2 Two two 22 2222 3 3 three Three 33 33333 4 4 fourfourfour 444 4444444 -10 10 Tenth ten TEN 1010101 1010 SELECT * FROM federated.t1 WHERE id > 5; id col1 col2 col3 col4 +10 10 Tenth ten TEN 1010101 1010 6 6 six six Sixsix 6666 6 7 7 seven Sevenseven 77777 7777 8 8 eight eight eight 88888 88 9 9 nine Nine 999999 999999 -10 10 Tenth ten TEN 1010101 1010 SELECT * FROM federated.t1 WHERE id >= 5; id col1 col2 col3 col4 +10 10 Tenth ten TEN 1010101 1010 5 5 five 5 five five 5 5 55555 6 6 six six Sixsix 6666 6 7 7 seven Sevenseven 77777 7777 8 8 eight eight eight 88888 88 9 9 nine Nine 999999 999999 -10 10 Tenth ten TEN 1010101 1010 SELECT * FROM federated.t1 WHERE id < 5; id col1 col2 col3 col4 1 1 one One 11 1111 @@ -702,6 +706,7 @@ SELECT * FROM federated.t1 WHERE id != 5; id col1 col2 col3 col4 1 1 one One 11 1111 +10 10 Tenth ten TEN 1010101 1010 2 2 Two two 22 2222 3 3 three Three 33 33333 4 4 fourfourfour 444 4444444 @@ -709,7 +714,6 @@ 7 7 seven Sevenseven 77777 7777 8 8 eight eight eight 88888 88 9 9 nine Nine 999999 999999 -10 10 Tenth ten TEN 1010101 1010 SELECT * FROM federated.t1 WHERE id > 3 AND id < 7; id col1 col2 col3 col4 4 4 fourfourfour 444 4444444 @@ -737,25 +741,25 @@ SELECT * FROM federated.t1 WHERE id < 3 OR id > 7; id col1 col2 col3 col4 1 1 one One 11 1111 +10 10 Tenth ten TEN 1010101 1010 2 2 Two two 22 2222 8 8 eight eight eight 88888 88 9 9 nine Nine 999999 999999 -10 10 Tenth ten TEN 1010101 1010 SELECT * FROM federated.t1 WHERE col2 = 'three Three'; id col1 col2 col3 col4 3 3 three Three 33 33333 SELECT * FROM federated.t1 WHERE col2 > 'one'; id col1 col2 col3 col4 1 1 one One 11 1111 +10 10 Tenth ten TEN 1010101 1010 2 2 Two two 22 2222 3 3 three Three 33 33333 6 6 six six Sixsix 6666 6 7 7 seven Sevenseven 77777 7777 -10 10 Tenth ten TEN 1010101 1010 SELECT * FROM federated.t1 WHERE col2 LIKE 's%'; id col1 col2 col3 col4 +6 6 six six Sixsix 6666 6 7 7 seven Sevenseven 77777 7777 -6 6 six six Sixsix 6666 6 SELECT * FROM federated.t1 WHERE col2 LIKE 'si%'; id col1 col2 col3 col4 6 6 six six Sixsix 6666 6 @@ -765,6 +769,7 @@ SELECT * FROM federated.t1 WHERE col2 NOT LIKE 'e%'; id col1 col2 col3 col4 1 1 one One 11 1111 +10 10 Tenth ten TEN 1010101 1010 2 2 Two two 22 2222 3 3 three Three 33 33333 4 4 fourfourfour 444 4444444 @@ -772,18 +777,17 @@ 6 6 six six Sixsix 6666 6 7 7 seven Sevenseven 77777 7777 9 9 nine Nine 999999 999999 -10 10 Tenth ten TEN 1010101 1010 SELECT * FROM federated.t1 WHERE col2 <> 'one One'; id col1 col2 col3 col4 +10 10 Tenth ten TEN 1010101 1010 +2 2 Two two 22 2222 +3 3 three Three 33 33333 4 4 fourfourfour 444 4444444 5 5 five 5 five five 5 5 55555 +6 6 six six Sixsix 6666 6 +7 7 seven Sevenseven 77777 7777 8 8 eight eight eight 88888 88 9 9 nine Nine 999999 999999 -2 2 Two two 22 2222 -3 3 three Three 33 33333 -6 6 six six Sixsix 6666 6 -7 7 seven Sevenseven 77777 7777 -10 10 Tenth ten TEN 1010101 1010 DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( `col1` varchar(8) NOT NULL DEFAULT '', @@ -950,11 +954,11 @@ VALUES (0, 00.3333, NULL); SELECT * FROM federated.t1; id name floatval other -NULL NULL NULL NULL -NULL NULL NULL NULL 1 NULL NULL NULL +NULL 0 0.3333 NULL +NULL NULL NULL NULL +NULL NULL NULL NULL NULL foo 33.3333 NULL -NULL 0 0.3333 NULL SELECT count(*) FROM federated.t1 WHERE id IS NULL AND name IS NULL @@ -983,13 +987,13 @@ INSERT INTO federated.t1 VALUES (1, " MySQL supports a number of column types in several categories: numeric types, date and time types, and string (character) types. This chapter first gives an overview of these column types, and then provides a more detailed description of the properties of the types in each category, and a summary of the column type storage requirements. The overview is intentionally brief. The more detailed descriptions should be consulted for additional information about particular column types, such as the allowable formats in which you can specify values."); INSERT INTO federated.t1 VALUES (2, "All arithmetic is done using signed BIGINT or DOUBLE values, so you should not use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting a BIGINT value to a DOUBLE."); INSERT INTO federated.t1 VALUES (3, " A floating-point number. p represents the precision. It can be from 0 to 24 for a single-precision floating-point number and from 25 to 53 for a double-precision floating-point number. These types are like the FLOAT and DOUBLE types described immediately following. FLOAT(p) has the same range as the corresponding FLOAT and DOUBLE types, but the display size and number of decimals are undefined. "); -INSERT INTO federated.t1 VALUES(4, "Die bersetzung einer so umfangreichen technischen Dokumentation wie des MySQL-Referenzhandbuchs ist schon eine besondere Herausforderung. Zumindest fr jemanden, der seine Zielsprache ernst nimmt:"); +INSERT INTO federated.t1 VALUES(4, "Die Übersetzung einer so umfangreichen technischen Dokumentation wie des MySQL-Referenzhandbuchs ist schon eine besondere Herausforderung. Zumindest für jemanden, der seine Zielsprache ernst nimmt:"); SELECT * FROM federated.t1; blurb_id blurb 1 MySQL supports a number of column types in several categories: numeric types, date and time types, and string (character) types. This chapter first gives an overview of these column types, and then provides a more detailed description of the properties of the types in each category, and a summary of the column type storage requirements. The overview is intentionally brief. The more detailed descriptions should be consulted for additional information about particular column types, such as the allowable formats in which you can specify values. 2 All arithmetic is done using signed BIGINT or DOUBLE values, so you should not use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting a BIGINT value to a DOUBLE. 3 A floating-point number. p represents the precision. It can be from 0 to 24 for a single-precision floating-point number and from 25 to 53 for a double-precision floating-point number. These types are like the FLOAT and DOUBLE types described immediately following. FLOAT(p) has the same range as the corresponding FLOAT and DOUBLE types, but the display size and number of decimals are undefined. -4 Die bersetzung einer so umfangreichen technischen Dokumentation wie des MySQL-Referenzhandbuchs ist schon eine besondere Herausforderung. Zumindest fr jemanden, der seine Zielsprache ernst nimmt: +4 Die Übersetzung einer so umfangreichen technischen Dokumentation wie des MySQL-Referenzhandbuchs ist schon eine besondere Herausforderung. Zumindest für jemanden, der seine Zielsprache ernst nimmt: DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( `a` int NOT NULL, @@ -1741,20 +1745,20 @@ create table federated.t2 (a int, b int); insert into federated.t2 values (13, 17), (19, 23); insert into federated.t1 (a, b) values (1, 2), (3, 5), (7, 11); -select * from federated.t1; +select * from federated.t1 order by a; a b c 1 2 2 3 5 15 7 11 77 delete from federated.t1; insert into federated.t1 (a, b) select * from federated.t2; -select * from federated.t1; +select * from federated.t1 order by a; a b c 13 17 221 19 23 437 delete from federated.t1; load data infile '../std_data_ln/loaddata5.dat' into table federated.t1 fields terminated by '' enclosed by '' ignore 1 lines (a, b); -select * from federated.t1; +select * from federated.t1 order by a; a b c 3 4 12 5 6 30 @@ -2081,8 +2085,10 @@ test.t1 2465757603 DROP TABLE t1; DROP TABLE t1; -DROP TABLE IF EXISTS federated.t1; -DROP DATABASE IF EXISTS federated; -DROP TABLE IF EXISTS federated.t1; -DROP DATABASE IF EXISTS federated; End of 5.0 tests +SET @@GLOBAL.CONCURRENT_INSERT= @OLD_MASTER_CONCURRENT_INSERT; +SET @@GLOBAL.CONCURRENT_INSERT= @OLD_SLAVE_CONCURRENT_INSERT; +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE IF EXISTS federated; +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE IF EXISTS federated; === modified file 'mysql-test/t/federated.test' --- a/mysql-test/t/federated.test 2008-08-15 06:40:05 +0000 +++ b/mysql-test/t/federated.test 2008-09-04 17:01:57 +0000 @@ -1,6 +1,24 @@ -source include/federated.inc; +# Note: This test is tricky. It reuses the prerequisites generated for +# replication tests (master+slave server and connections) for its +# own purposes. But the replication feature itself is stopped. +# + + +# should work with embedded server after mysqltest is fixed +# MLML --source include/not_embedded.inc +--source include/federated.inc + +connection default; + +# Disable concurrent inserts to avoid test failures when reading +# data from concurrent connections (insert might return before +# the data is actually in the table). +SET @OLD_MASTER_CONCURRENT_INSERT= @@GLOBAL.CONCURRENT_INSERT; +SET @@GLOBAL.CONCURRENT_INSERT= 0; connection slave; +SET @OLD_SLAVE_CONCURRENT_INSERT= @@GLOBAL.CONCURRENT_INSERT; +SET @@GLOBAL.CONCURRENT_INSERT= 0; DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, @@ -11,7 +29,7 @@ connection master; DROP TABLE IF EXISTS federated.t1; # test too many items (malformed) in the comment string url ---error 1432 +--error ER_FOREIGN_DATA_STRING_INVALID_CANT_CREATE CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' @@ -20,7 +38,7 @@ CONNECTION='mysql://root@127.0.0.1:@/too/many/items/federated/t1'; # test not enough items (malformed) in the comment string url ---error 1432 +--error ER_FOREIGN_DATA_STRING_INVALID_CANT_CREATE CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' @@ -36,7 +54,7 @@ ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t3'; ---error 1431 +--error ER_FOREIGN_DATA_SOURCE_DOESNT_EXIST SELECT * FROM federated.t1; DROP TABLE federated.t1; @@ -48,7 +66,7 @@ ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql://user:pass@127.0.0.1:$SLAVE_MYPORT/federated/t1'; ---error 1429 +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE SELECT * FROM federated.t1; DROP TABLE federated.t1; @@ -64,6 +82,7 @@ INSERT INTO federated.t1 (id, name) VALUES (1, 'foo'); INSERT INTO federated.t1 (id, name) VALUES (2, 'fee'); +--sorted_result SELECT * FROM federated.t1; DELETE FROM federated.t1; DROP TABLE federated.t1; @@ -84,7 +103,7 @@ INSERT INTO federated.t2 (id, name) VALUES (1, 'foo'); INSERT INTO federated.t2 (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.t2; +SELECT * FROM federated.t2 ORDER BY id, name; DROP TABLE federated.t2; connection slave; @@ -111,7 +130,7 @@ INSERT INTO federated.t1 (id, name) VALUES (1, 'foo'); INSERT INTO federated.t1 (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.t1; +SELECT * FROM federated.t1 ORDER BY id,name; DELETE FROM federated.t1; DROP TABLE IF EXISTS federated.t1; @@ -126,7 +145,7 @@ INSERT INTO federated.`t1%` (id, name) VALUES (1, 'foo'); INSERT INTO federated.`t1%` (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.`t1%`; +SELECT * FROM federated.`t1%` ORDER BY id, name; DELETE FROM federated.`t1%`; DROP TABLE IF EXISTS federated.`t1%`; @@ -166,12 +185,14 @@ INSERT INTO federated.t1 (name, other) VALUES ('Tenth Name', 101010); # basic select +--sorted_result SELECT * FROM federated.t1; # with PRIMARY KEY index_read_idx SELECT * FROM federated.t1 WHERE id = 5; SELECT * FROM federated.t1 WHERE name = 'Sixth Name'; SELECT * FROM federated.t1 WHERE id = 6 and name = 'Sixth Name'; SELECT * FROM federated.t1 WHERE name = 'Sixth Name' AND other = 44444; +--sorted_result SELECT * FROM federated.t1 WHERE name like '%th%'; UPDATE federated.t1 SET name = '3rd name' WHERE id = 3; SELECT * FROM federated.t1 WHERE name = '3rd name'; @@ -243,6 +264,7 @@ VALUES ('Tenth Name', 101010, '2005-03-12 12:00:01'); # basic select +--sorted_result SELECT * FROM federated.t1; # with PRIMARY KEY index_read_idx SELECT * FROM federated.t1 WHERE id = 5; @@ -251,6 +273,7 @@ SELECT * FROM federated.t1 WHERE id = 6 and name = 'Sixth Name'; # with regular key index_read -> index_read_idx SELECT * FROM federated.t1 WHERE other = 44444; +--sorted_result SELECT * FROM federated.t1 WHERE name like '%th%'; # update - update_row, index_read_idx UPDATE federated.t1 SET name = '3rd name' WHERE id = 3; @@ -303,9 +326,12 @@ INSERT INTO federated.t1 (name, other) VALUES ('Ninth Name', 99999); INSERT INTO federated.t1 (other) VALUES ('fee fie foe fum'); +--sorted_result SELECT * FROM federated.t1 WHERE other IS NULL; +--sorted_result SELECT * FROM federated.t1 WHERE name IS NULL; SELECT * FROM federated.t1 WHERE name IS NULL and other IS NULL; +--sorted_result SELECT * FROM federated.t1 WHERE name IS NULL or other IS NULL; UPDATE federated.t1 @@ -316,6 +342,7 @@ UPDATE federated.t1 SET other = 'seven seven' WHERE name like 'Sev%'; UPDATE federated.t1 SET name = 'Tenth Name' WHERE other like 'fee fie%'; SELECT * FROM federated.t1 WHERE name IS NULL OR other IS NULL ; +--sorted_result SELECT * FROM federated.t1; # test multi-keys @@ -386,6 +413,7 @@ VALUES ('second', 0x66, 22.22, 2222); INSERT INTO federated.t1 (name, bincol, floatval, other) VALUES ('third', 'g', 22.22, 2222); +--sorted_result SELECT * FROM federated.t1; SELECT * FROM federated.t1 WHERE name = 'second'; SELECT * FROM federated.t1 WHERE bincol= 'f'; @@ -394,6 +422,7 @@ SELECT * FROM federated.t1 WHERE bincol= 'g'; SELECT * FROM federated.t1 WHERE floatval=11.11; SELECT * FROM federated.t1 WHERE name='third'; +--sorted_result SELECT * FROM federated.t1 WHERE other=2222; SELECT * FROM federated.t1 WHERE name='third' and other=2222; @@ -467,32 +496,47 @@ SELECT * FROM federated.t1 WHERE id = 5 AND col2 = 'five 5 five five 5' AND col3 = 5 AND col4 = 55555; +--sorted_result SELECT * FROM federated.t1 WHERE (id = 5 AND col2 = 'five 5 five five 5') OR (col2 = 'three Three' AND col3 = 33); SELECT * FROM federated.t1 WHERE (id = 5 AND col2 = 'Two two') OR (col2 = 444 AND col3 = 4444444); +--sorted_result SELECT * FROM federated.t1 WHERE id = 1 OR col1 = 10 OR col2 = 'Two two' OR col3 = 33 OR col4 = 4444444; +--sorted_result SELECT * FROM federated.t1 WHERE id > 5; +--sorted_result SELECT * FROM federated.t1 WHERE id >= 5; +--sorted_result SELECT * FROM federated.t1 WHERE id < 5; +--sorted_result SELECT * FROM federated.t1 WHERE id <= 5; +--sorted_result SELECT * FROM federated.t1 WHERE id != 5; +--sorted_result SELECT * FROM federated.t1 WHERE id > 3 AND id < 7; +--sorted_result SELECT * FROM federated.t1 WHERE id > 3 AND id <= 7; +--sorted_result SELECT * FROM federated.t1 WHERE id >= 3 AND id <= 7; SELECT * FROM federated.t1 WHERE id < 3 AND id <= 7; SELECT * FROM federated.t1 WHERE id < 3 AND id > 7; +--sorted_result SELECT * FROM federated.t1 WHERE id < 3 OR id > 7; SELECT * FROM federated.t1 WHERE col2 = 'three Three'; +--sorted_result SELECT * FROM federated.t1 WHERE col2 > 'one'; +--sorted_result SELECT * FROM federated.t1 WHERE col2 LIKE 's%'; SELECT * FROM federated.t1 WHERE col2 LIKE 'si%'; SELECT * FROM federated.t1 WHERE col2 LIKE 'se%'; +--sorted_result SELECT * FROM federated.t1 WHERE col2 NOT LIKE 'e%'; +--sorted_result SELECT * FROM federated.t1 WHERE col2 <> 'one One'; # more multi-column indexes, in the primary key @@ -546,13 +590,19 @@ SELECT * FROM federated.t1 WHERE col1 = 'gggg' AND col2 = 'ggggggggggggggggggg'; SELECT * FROM federated.t1 WHERE col1 = 'gggg' AND col3 = 'gagagagaga'; SELECT * FROM federated.t1 WHERE col1 = 'ffff' AND col4 = 'fcfcfcfcfcfcfcfc'; +--sorted_result SELECT * FROM federated.t1 WHERE col1 > 'bbbb'; +--sorted_result SELECT * FROM federated.t1 WHERE col1 >= 'bbbb'; SELECT * FROM federated.t1 WHERE col1 < 'bbbb'; +--sorted_result SELECT * FROM federated.t1 WHERE col1 <= 'bbbb'; +--sorted_result SELECT * FROM federated.t1 WHERE col1 <> 'bbbb'; SELECT * FROM federated.t1 WHERE col1 LIKE 'b%'; +--sorted_result SELECT * FROM federated.t1 WHERE col4 LIKE '%b%'; +--sorted_result SELECT * FROM federated.t1 WHERE col1 NOT LIKE 'c%'; SELECT * FROM federated.t1 WHERE col4 NOT LIKE '%c%'; connection slave; @@ -583,11 +633,13 @@ # let's see what the foreign database says connection slave; +--sorted_result SELECT col3 FROM federated.t1 WHERE ( (col1 = 'aaa' AND col2 >= '111') OR col1 > 'aaa') AND (col1 < 'ccc' OR ( col1 = 'ccc' AND col2 <= '111')); connection master; +--sorted_result SELECT col3 FROM federated.t1 WHERE ( (col1 = 'aaa' AND col2 >= '111') OR col1 > 'aaa') AND (col1 < 'ccc' OR ( col1 = 'ccc' AND col2 <= '111')); @@ -622,6 +674,7 @@ VALUES ('foo', 33.33333332, NULL); INSERT INTO federated.t1 (name, floatval, other) VALUES (0, 00.3333, NULL); +--sorted_result SELECT * FROM federated.t1; SELECT count(*) FROM federated.t1 WHERE id IS NULL @@ -651,7 +704,8 @@ INSERT INTO federated.t1 VALUES (1, " MySQL supports a number of column types in several categories: numeric types, date and time types, and string (character) types. This chapter first gives an overview of these column types, and then provides a more detailed description of the properties of the types in each category, and a summary of the column type storage requirements. The overview is intentionally brief. The more detailed descriptions should be consulted for additional information about particular column types, such as the allowable formats in which you can specify values."); INSERT INTO federated.t1 VALUES (2, "All arithmetic is done using signed BIGINT or DOUBLE values, so you should not use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting a BIGINT value to a DOUBLE."); INSERT INTO federated.t1 VALUES (3, " A floating-point number. p represents the precision. It can be from 0 to 24 for a single-precision floating-point number and from 25 to 53 for a double-precision floating-point number. These types are like the FLOAT and DOUBLE types described immediately following. FLOAT(p) has the same range as the corresponding FLOAT and DOUBLE types, but the display size and number of decimals are undefined. "); -INSERT INTO federated.t1 VALUES(4, "Die bersetzung einer so umfangreichen technischen Dokumentation wie des MySQL-Referenzhandbuchs ist schon eine besondere Herausforderung. Zumindest fr jemanden, der seine Zielsprache ernst nimmt:"); +INSERT INTO federated.t1 VALUES(4, "Die Übersetzung einer so umfangreichen technischen Dokumentation wie des MySQL-Referenzhandbuchs ist schon eine besondere Herausforderung. Zumindest für jemanden, der seine Zielsprache ernst nimmt:"); +--sorted_result SELECT * FROM federated.t1; connection slave; @@ -1010,6 +1064,7 @@ INSERT INTO federated.t1 (code, fileguts, creation_date) VALUES ('ASDFWERQWETWETAWETA', '*()w*09*$()*#)(*09*^90*d)(*s()d8g)(s*ned)(*)(s*d)(*hn(d*)(*sbn)D((#$*(#*%%&#&^$#&#&#&#&^&#*&*#$*&^*(&#(&Q*&&(*!&!(*&*(#&*(%&#*###[[', '2003-03-03 03:03:03'); INSERT INTO federated.t1 (code, fileguts, creation_date) VALUES ('DEUEUEUEUEUEUEUEUEU', '*()w*09*$()*#)(*09*^90*d)(*s()d8g)(s*ned)(*)(s*d)(*hn(d*)(*sbn)D((#$*(#*%%&#&^$#&#&#&#&^&#*&*#$*&^*(&#(&Q*&&(*!&!(*&*(#&*(%&#*###[[', '2004-04-04 04:04:04'); INSERT INTO federated.t1 (code, fileguts, creation_date) VALUES ('DEUEUEUEUEUEUEUEUEU', 'jimbob', '2004-04-04 04:04:04'); +--sorted_result SELECT * FROM federated.t1; # test blob indexes SELECT * FROM federated.t1 WHERE fileguts = 'jimbob'; @@ -1030,6 +1085,7 @@ INSERT INTO federated.t1 VALUES (0x00); INSERT INTO federated.t1 VALUES (0x0001); INSERT INTO federated.t1 VALUES (0x0100); +--sorted_result SELECT HEX(a) FROM federated.t1; # # simple tests for cyrillic, given to me by @@ -1044,20 +1100,20 @@ # CREATE TABLE federated.t1 (a char(20)) charset=cp1251; # # # connection master; -# INSERT INTO federated.t1 values (_cp1251'--1'); -# INSERT INTO federated.t1 values (_cp1251'--2'); +# INSERT INTO federated.t1 values (_cp1251'À-ÁÂÃ-1'); +# INSERT INTO federated.t1 values (_cp1251'Á-ÂÃÄ-2'); # SELECT * FROM federated.t1; # SET names cp1251; -# INSERT INTO federated.t1 values ('--3'); -# INSERT INTO federated.t1 values ('-Ũ-4'); +# INSERT INTO federated.t1 values ('Â-ÃÄÅ-3'); +# INSERT INTO federated.t1 values ('Ã-ŨÆ-4'); # SELECT * FROM federated.t1; # SELECT hex(a) from federated.t1; # SELECT hex(a) from federated.t1 ORDER BY a desc; -# UPDATE federated.t1 SET a='--1' WHERE a='--1'; -# SELECT * FROM federated.t1; -# DELETE FROM federated.t1 WHERE a='-Ũ-4'; -# SELECT * FROM federated.t1; -# DELETE FROM federated.t1 WHERE a>'-'; +# UPDATE federated.t1 SET a='À-ÁÂÃ-1íîâûé' WHERE a='À-ÁÂÃ-1'; +# SELECT * FROM federated.t1; +# DELETE FROM federated.t1 WHERE a='Ã-ŨÆ-4'; +# SELECT * FROM federated.t1; +# DELETE FROM federated.t1 WHERE a>'Â-'; # SELECT * FROM federated.t1; # SET names default; # DROP TABLE IF EXISTS federated.t1; @@ -1108,6 +1164,7 @@ INSERT INTO federated.t1 (name, country_id, other) VALUES ('Sanja', 5, 33333); #inner join +--sorted_result SELECT federated.t1.name AS name, federated.t1.country_id AS country_id, federated.t1.other AS other, federated.countries.country AS country FROM federated.t1, federated.countries WHERE @@ -1181,7 +1238,7 @@ SELECT * FROM federated.alter_me; ---error 1031 +--error ER_ILLEGAL_HA ALTER TABLE federated.alter_me MODIFY COLUMN id int(16) NOT NULL; SELECT * FROM federated.alter_me; @@ -1452,15 +1509,15 @@ # Each of three statements should correctly set values for all three fields # insert insert into federated.t1 (a, b) values (1, 2), (3, 5), (7, 11); -select * from federated.t1; +select * from federated.t1 order by a; delete from federated.t1; # insert ... select insert into federated.t1 (a, b) select * from federated.t2; -select * from federated.t1; +select * from federated.t1 order by a; delete from federated.t1; # load load data infile '../std_data_ln/loaddata5.dat' into table federated.t1 fields terminated by '' enclosed by '' ignore 1 lines (a, b); -select * from federated.t1; +select * from federated.t1 order by a; drop tables federated.t1, federated.t2; connection slave; @@ -1769,7 +1826,13 @@ connection master; DROP TABLE t1; - +connection default; + +--echo End of 5.0 tests + +SET @@GLOBAL.CONCURRENT_INSERT= @OLD_MASTER_CONCURRENT_INSERT; +connection slave; +SET @@GLOBAL.CONCURRENT_INSERT= @OLD_SLAVE_CONCURRENT_INSERT; + +connection default; source include/federated_cleanup.inc; - ---echo End of 5.0 tests