diff options
Diffstat (limited to 'mysql-test')
35 files changed, 1561 insertions, 245 deletions
diff --git a/mysql-test/include/federated.inc b/mysql-test/include/federated.inc new file mode 100644 index 00000000000..1c53b9ed2c5 --- /dev/null +++ b/mysql-test/include/federated.inc @@ -0,0 +1,21 @@ +--source ./include/have_federated_db.inc + +source ./include/master-slave.inc; + +# remote table creation + +connection slave; +--replicate-ignore-db=federated +stop slave; + +--disable_warnings +# at this point, we are connected to master +DROP DATABASE IF EXISTS federated; +--enable_warnings +CREATE DATABASE federated; + +connection master; +--disable_warnings +DROP DATABASE IF EXISTS federated; +--enable_warnings +CREATE DATABASE federated; diff --git a/mysql-test/include/federated_cleanup.inc b/mysql-test/include/federated_cleanup.inc new file mode 100644 index 00000000000..17a6e1e5100 --- /dev/null +++ b/mysql-test/include/federated_cleanup.inc @@ -0,0 +1,11 @@ +connection master; +--disable_warnings +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE IF EXISTS federated; +--enable_warnings + +connection slave; +--disable_warnings +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE IF EXISTS federated; +--enable_warnings diff --git a/mysql-test/r/ctype_eucjpms.result b/mysql-test/r/ctype_eucjpms.result index a07ff9b28be..cdb28cd0fdd 100755 --- a/mysql-test/r/ctype_eucjpms.result +++ b/mysql-test/r/ctype_eucjpms.result @@ -9785,6 +9785,20 @@ DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; +CREATE TABLE t1(c1 varchar(10)) default character set = eucjpms; +insert into t1 values(_ucs2 0x00F7); +insert into t1 values(_eucjpms 0xA1E0); +insert into t1 values(_ujis 0xA1E0); +insert into t1 values(_sjis 0x8180); +insert into t1 values(_cp932 0x8180); +SELECT HEX(c1) FROM t1; +HEX(c1) +A1E0 +A1E0 +A1E0 +A1E0 +A1E0 +DROP TABLE t1; SET collation_connection='eucjpms_japanese_ci'; create table t1 select repeat('a',4000) a; delete from t1; diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result index d9c86a89c75..7692991c112 100644 --- a/mysql-test/r/federated.result +++ b/mysql-test/r/federated.result @@ -7,41 +7,47 @@ start slave; stop slave; DROP DATABASE IF EXISTS federated; CREATE DATABASE federated; +DROP DATABASE IF EXISTS federated; +CREATE DATABASE federated; +DROP TABLE IF EXISTS federated.t1; +Warnings: +Note 1051 Unknown table 't1' CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' ) DEFAULT CHARSET=latin1; -DROP DATABASE IF EXISTS federated; -CREATE DATABASE federated; +DROP TABLE IF EXISTS federated.t1; +Warnings: +Note 1051 Unknown table 't1' CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 COMMENT='mysql://root@127.0.0.1:@/too/many/items/federated/t1'; -ERROR HY000: Can't create table 'connection string is not in the correct format' (errno: 0) +ERROR HY000: Can't create federated table. The data source connection string 'mysql://root@127.0.0.1:@/too/many/items/federated/t1' is not in the correct format CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 COMMENT='mysql://root@127.0.0.1'; -ERROR HY000: Can't create table 'connection string is not in the correct format' (errno: 0) +ERROR HY000: Can't create federated table. The data source connection string 'mysql://root@127.0.0.1' is not in the correct format CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 COMMENT='mysql://root@127.0.0.1:SLAVE_PORT/federated/t3'; -ERROR HY000: Error running query on master: foreign table 't3' does not exist! +ERROR HY000: Can't create federated table. Foreign data src error : ': 1146 : Table 'federated.t3' doesn't exist' CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 COMMENT='mysql://user:pass@127.0.0.1:SLAVE_PORT/federated/t1'; -ERROR 08S01: Error connecting to master: unable to connect to database 'federated' on host '127.0.0.1 as user 'user' ! +ERROR HY000: Unable to connect to foreign data source - database ' database federated username user hostname 127.0.0.1'! DROP TABLE IF EXISTS federated.t1; Warnings: Note 1051 Unknown table 't1' @@ -124,18 +130,14 @@ CREATE TABLE federated.t1 ( `name` varchar(32) NOT NULL default '', `other` int(20) NOT NULL default '0', `created` datetime default '2004-04-04 04:04:04', -PRIMARY KEY (`id`), -KEY `name` (`name`), -KEY `other_key` (`other`)) +PRIMARY KEY (`id`)) DEFAULT CHARSET=latin1; CREATE TABLE federated.t1 ( `id` int(20) NOT NULL auto_increment, `name` varchar(32) NOT NULL default '', `other` int(20) NOT NULL default '0', `created` datetime default '2004-04-04 04:04:04', -PRIMARY KEY (`id`), -KEY `name` (`name`), -KEY `other_key` (`other`)) +PRIMARY KEY (`id`)) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 COMMENT='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1'; INSERT INTO federated.t1 (name, other) VALUES ('First Name', 11111); @@ -169,9 +171,8 @@ id name other created SELECT * FROM federated.t1 WHERE id = 6 and name = 'Sixth Name'; id name other created 6 Sixth Name 66666 2004-04-04 04:04:04 -SELECT * FROM federated.t1 WHERE other = 44444; +SELECT * FROM federated.t1 WHERE name = 'Sixth Name' AND other = 44444; id name other created -4 Fourth Name 44444 2004-04-04 04:04:04 SELECT * FROM federated.t1 WHERE name like '%th%'; id name other created 3 Third Name 33333 2004-04-04 04:04:04 @@ -257,6 +258,154 @@ DELETE FROM federated.t1; SELECT * FROM federated.t1 WHERE id = 5; id name other created DROP TABLE IF EXISTS federated.t1; +CREATE TABLE federated.t1 ( +`id` int(20) NOT NULL auto_increment, +`name` varchar(32) NOT NULL default '', +`other` int(20) NOT NULL default '0', +`created` datetime NOT NULL, +PRIMARY KEY (`id`), +key name(`name`), +key other(`other`), +key created(`created`)) +DEFAULT CHARSET=latin1; +DROP TABLE IF EXISTS federated.t1; +CREATE TABLE federated.t1 ( +`id` int(20) NOT NULL auto_increment, +`name` varchar(32) NOT NULL default '', +`other` int(20) NOT NULL default '0', +`created` datetime NOT NULL, +PRIMARY KEY (`id`), +key name(`name`), +key other(`other`), +key created(`created`)) +ENGINE="FEDERATED" DEFAULT CHARSET=latin1 +COMMENT='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1'; +INSERT INTO federated.t1 (name, other, created) +VALUES ('First Name', 11111, '2004-01-01 01:01:01'); +INSERT INTO federated.t1 (name, other, created) +VALUES ('Second Name', 22222, '2004-01-23 02:43:00'); +INSERT INTO federated.t1 (name, other, created) +VALUES ('Third Name', 33333, '2004-02-14 02:14:00'); +INSERT INTO federated.t1 (name, other, created) +VALUES ('Fourth Name', 44444, '2003-04-05 00:00:00'); +INSERT INTO federated.t1 (name, other, created) +VALUES ('Fifth Name', 55555, '2001-02-02 02:02:02'); +INSERT INTO federated.t1 (name, other, created) +VALUES ('Sixth Name', 66666, '2005-06-06 15:30:00'); +INSERT INTO federated.t1 (name, other, created) +VALUES ('Seventh Name', 77777, '2003-12-12 18:32:00'); +INSERT INTO federated.t1 (name, other, created) +VALUES ('Eigth Name', 88888, '2005-03-12 11:00:00'); +INSERT INTO federated.t1 (name, other, created) +VALUES ('Ninth Name', 99999, '2005-03-12 11:00:01'); +INSERT INTO federated.t1 (name, other, created) +VALUES ('Tenth Name', 101010, '2005-03-12 12:00:01'); +SELECT * FROM federated.t1; +id name other created +1 First Name 11111 2004-01-01 01:01: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 +5 Fifth Name 55555 2001-02-02 02:02:02 +6 Sixth Name 66666 2005-06-06 15:30:00 +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 +SELECT * FROM federated.t1 WHERE id = 6 and name = 'Sixth Name'; +id name other created +6 Sixth Name 66666 2005-06-06 15:30:00 +SELECT * FROM federated.t1 WHERE other = 44444; +id name other created +4 Fourth Name 44444 2003-04-05 00:00:00 +SELECT * FROM federated.t1 WHERE name like '%th%'; +id name other created +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 +6 Sixth Name 66666 2005-06-06 15:30:00 +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 +3 3rd name 33333 2004-02-14 02:14:00 +UPDATE federated.t1 SET name = 'Third name' WHERE name = '3rd name'; +SELECT * FROM federated.t1 WHERE name = 'Third name'; +id name other created +3 Third name 33333 2004-02-14 02:14:00 +SELECT * FROM federated.t1 ORDER BY id DESC; +id name other created +10 Tenth Name 101010 2005-03-12 12:00:01 +9 Ninth Name 99999 2005-03-12 11:00:01 +8 Eigth Name 88888 2005-03-12 11:00:00 +7 Seventh Name 77777 2003-12-12 18:32:00 +6 Sixth Name 66666 2005-06-06 15:30:00 +5 Fifth Name 55555 2001-02-02 02:02:02 +4 Fourth Name 44444 2003-04-05 00:00:00 +3 Third name 33333 2004-02-14 02:14:00 +2 Second Name 22222 2004-01-23 02:43:00 +1 First Name 11111 2004-01-01 01:01:01 +SELECT * FROM federated.t1 ORDER BY name; +id name other created +8 Eigth Name 88888 2005-03-12 11:00:00 +5 Fifth Name 55555 2001-02-02 02:02:02 +1 First Name 11111 2004-01-01 01:01:01 +4 Fourth Name 44444 2003-04-05 00:00:00 +9 Ninth Name 99999 2005-03-12 11:00:01 +2 Second Name 22222 2004-01-23 02:43:00 +7 Seventh Name 77777 2003-12-12 18:32:00 +6 Sixth Name 66666 2005-06-06 15:30:00 +10 Tenth Name 101010 2005-03-12 12:00:01 +3 Third name 33333 2004-02-14 02:14:00 +SELECT * FROM federated.t1 ORDER BY name DESC; +id name other created +3 Third name 33333 2004-02-14 02:14:00 +10 Tenth Name 101010 2005-03-12 12:00:01 +6 Sixth Name 66666 2005-06-06 15:30:00 +7 Seventh Name 77777 2003-12-12 18:32:00 +2 Second Name 22222 2004-01-23 02:43:00 +9 Ninth Name 99999 2005-03-12 11:00:01 +4 Fourth Name 44444 2003-04-05 00:00:00 +1 First Name 11111 2004-01-01 01:01:01 +5 Fifth Name 55555 2001-02-02 02:02:02 +8 Eigth Name 88888 2005-03-12 11:00:00 +SELECT * FROM federated.t1 ORDER BY name ASC; +id name other created +8 Eigth Name 88888 2005-03-12 11:00:00 +5 Fifth Name 55555 2001-02-02 02:02:02 +1 First Name 11111 2004-01-01 01:01:01 +4 Fourth Name 44444 2003-04-05 00:00:00 +9 Ninth Name 99999 2005-03-12 11:00:01 +2 Second Name 22222 2004-01-23 02:43:00 +7 Seventh Name 77777 2003-12-12 18:32:00 +6 Sixth Name 66666 2005-06-06 15:30:00 +10 Tenth Name 101010 2005-03-12 12:00:01 +3 Third name 33333 2004-02-14 02:14:00 +SELECT * FROM federated.t1 GROUP BY other; +id name other created +1 First Name 11111 2004-01-01 01:01: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 +5 Fifth Name 55555 2001-02-02 02:02:02 +6 Sixth Name 66666 2005-06-06 15:30:00 +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 +DELETE FROM federated.t1 WHERE id = 5; +SELECT * FROM federated.t1 WHERE id = 5; +id name other created +DELETE FROM federated.t1; +SELECT * FROM federated.t1 WHERE id = 5; +id name other created +DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( `id` int(20) NOT NULL auto_increment, `name` varchar(32), @@ -303,15 +452,14 @@ UPDATE federated.t1 SET name = 'Fourth Name', other = 'four four four' WHERE name IS NULL AND other IS NULL; UPDATE federated.t1 SET other = 'two two two two' WHERE name = 'Second Name'; -UPDATE federated.t1 SET other = 'seven seven' WHERE name like 'Sec%'; -UPDATE federated.t1 SET other = 'seven seven' WHERE name = 'Seventh Name'; +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 ; id name other SELECT * FROM federated.t1; id name other 1 First Name 11111 -2 Second Name seven seven +2 Second Name two two two two 3 Third Name 33333 4 Fourth Name four four four 5 Fifth Name 55555 @@ -418,6 +566,360 @@ id name bincol floatval other 3 third g 22.22 2222 DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( +`id` int NOT NULL auto_increment, +`col1` int(10) NOT NULL DEFAULT 0, +`col2` varchar(64) NOT NULL DEFAULT '', +`col3` int(20) NOT NULL, +`col4` int(40) NOT NULL, +primary key (`id`, `col1`, `col2`, `col3`, `col4`), +key col1(col1), +key col2(col2), +key col3(col3), +key col4(col4)); +DROP TABLE IF EXISTS federated.t1; +CREATE TABLE federated.t1 ( +`id` int NOT NULL auto_increment, +`col1` int(10) NOT NULL DEFAULT 0, +`col2` varchar(64) NOT NULL DEFAULT '', +`col3` int(20) NOT NULL, +`col4` int(40) NOT NULL, +primary key (`id`, `col1`, `col2`, `col3`, `col4`), +key col1(col1), +key col2(col2), +key col3(col3), +key col4(col4)) +ENGINE="FEDERATED" + COMMENT='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1'; +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES (1, 'one One', 11, 1111); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES (2, 'Two two', 22, 2222); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES (3, 'three Three', 33, 33333); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES (4, 'fourfourfour', 444, 4444444); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES (5, 'five 5 five five 5', 5, 55555); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES (6, 'six six Sixsix', 6666, 6); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES (7, 'seven Sevenseven', 77777, 7777); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES (8, 'eight eight eight', 88888, 88); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES (9, 'nine Nine', 999999, 999999); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES (10, 'Tenth ten TEN', 1010101, 1010); +SELECT * FROM federated.t1 WHERE col2 = 'two two'; +id col1 col2 col3 col4 +2 2 Two two 22 2222 +SELECT * FROM federated.t1 WHERE col2 = 'two Two'; +id col1 col2 col3 col4 +2 2 Two two 22 2222 +SELECT * FROM federated.t1 WHERE id = 3; +id col1 col2 col3 col4 +3 3 three Three 33 33333 +SELECT * FROM federated.t1 WHERE id = 3 AND col1 = 3; +id col1 col2 col3 col4 +3 3 three Three 33 33333 +SELECT * FROM federated.t1 WHERE id = 4 AND col1 = 4 AND col2 = 'Two two'; +id col1 col2 col3 col4 +SELECT * FROM federated.t1 WHERE id = 4 AND col1 = 4 AND col2 = 'fourfourfour'; +id col1 col2 col3 col4 +4 4 fourfourfour 444 4444444 +SELECT * FROM federated.t1 WHERE id = 5 AND col2 = 'five 5 five five 5' +AND col3 = 5; +id col1 col2 col3 col4 +5 5 five 5 five five 5 5 55555 +SELECT * FROM federated.t1 WHERE id = 5 AND col2 = 'five 5 five five 5' +AND col3 = 5 +AND col4 = 55555; +id col1 col2 col3 col4 +5 5 five 5 five five 5 5 55555 +SELECT * FROM federated.t1 WHERE id = 5 +AND col2 = 'Two two' AND col3 = 22 +AND col4 = 33; +id col1 col2 col3 col4 +SELECT * FROM federated.t1 WHERE id = 5 +AND col2 = 'five 5 five five 5' AND col3 = 5 +AND col4 = 55555; +id col1 col2 col3 col4 +5 5 five 5 five five 5 5 55555 +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 +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 +SELECT * FROM federated.t1 WHERE id = 1 +OR col1 = 10 +OR col2 = 'Two two' +OR col3 = 33 +OR col4 = 4444444; +id col1 col2 col3 col4 +1 1 one One 11 1111 +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 +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 +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 +2 2 Two two 22 2222 +3 3 three Three 33 33333 +4 4 fourfourfour 444 4444444 +SELECT * FROM federated.t1 WHERE id <= 5; +id col1 col2 col3 col4 +1 1 one One 11 1111 +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 +SELECT * FROM federated.t1 WHERE id != 5; +id col1 col2 col3 col4 +1 1 one One 11 1111 +2 2 Two two 22 2222 +3 3 three Three 33 33333 +4 4 fourfourfour 444 4444444 +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 > 3 AND id < 7; +id col1 col2 col3 col4 +4 4 fourfourfour 444 4444444 +5 5 five 5 five five 5 5 55555 +6 6 six six Sixsix 6666 6 +SELECT * FROM federated.t1 WHERE id > 3 AND id <= 7; +id col1 col2 col3 col4 +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 +SELECT * FROM federated.t1 WHERE id >= 3 AND id <= 7; +id col1 col2 col3 col4 +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 +SELECT * FROM federated.t1 WHERE id < 3 AND id <= 7; +id col1 col2 col3 col4 +1 1 one One 11 1111 +2 2 Two two 22 2222 +SELECT * FROM federated.t1 WHERE id < 3 AND id > 7; +id col1 col2 col3 col4 +SELECT * FROM federated.t1 WHERE id < 3 OR id > 7; +id col1 col2 col3 col4 +1 1 one One 11 1111 +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 +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 +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 +SELECT * FROM federated.t1 WHERE col2 LIKE 'se%'; +id col1 col2 col3 col4 +7 7 seven Sevenseven 77777 7777 +SELECT * FROM federated.t1 WHERE col2 NOT LIKE 'e%'; +id col1 col2 col3 col4 +1 1 one One 11 1111 +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 +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 +4 4 fourfourfour 444 4444444 +5 5 five 5 five five 5 5 55555 +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 '', +`col2` varchar(128) NOT NULL DEFAULT '', +`col3` varchar(20) NOT NULL DEFAULT '', +`col4` varchar(40) NOT NULL DEFAULT '', +primary key (`col1`, `col2`, `col3`, `col4`), +key 3key(`col2`,`col3`,`col4`), +key 2key (`col3`,`col4`), +key col4(col4)); +DROP TABLE IF EXISTS federated.t1; +CREATE TABLE federated.t1 ( +`col1` varchar(8) NOT NULL DEFAULT '', +`col2` varchar(128) NOT NULL DEFAULT '', +`col3` varchar(20) NOT NULL DEFAULT '', +`col4` varchar(40) NOT NULL DEFAULT '', +primary key (`col1`, `col2`, `col3`, `col4`), +key 3key(`col2`,`col3`,`col4`), +key 2key (`col3`,`col4`), +key col4(col4)) +ENGINE="FEDERATED" + COMMENT='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1'; +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES ('aaaa', 'aaaaaaaaaaaaaaaaaaa', 'ababababab', 'acacacacacacacac'); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES ('bbbb', 'bbbbbbbbbbbbbbbbbbb', 'bababababa', 'bcbcbcbcbcbcbcbc'); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES ('cccc', 'ccccccccccccccccccc', 'cacacacaca', 'cbcbcbcbcbcbcbcb'); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES ('dddd', 'ddddddddddddddddddd', 'dadadadada', 'dcdcdcdcdcdcdcdc'); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES ('eeee', 'eeeeeeeeeeeeeeeeeee', 'eaeaeaeaea', 'ecececececececec'); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES ('ffff', 'fffffffffffffffffff', 'fafafafafa', 'fcfcfcfcfcfcfcfc'); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES ('gggg', 'ggggggggggggggggggg', 'gagagagaga', 'gcgcgcgcgcgcgcgc'); +INSERT INTO federated.t1 (col1, col2, col3, col4) +VALUES ('hhhh', 'hhhhhhhhhhhhhhhhhhh', 'hahahahaha', 'hchchchchchchchc'); +SELECT * FROM federated.t1 WHERE col1 = 'cccc'; +col1 col2 col3 col4 +cccc ccccccccccccccccccc cacacacaca cbcbcbcbcbcbcbcb +SELECT * FROM federated.t1 WHERE col2 = 'eeeeeeeeeeeeeeeeeee'; +col1 col2 col3 col4 +eeee eeeeeeeeeeeeeeeeeee eaeaeaeaea ecececececececec +SELECT * FROM federated.t1 WHERE col3 = 'bababababa'; +col1 col2 col3 col4 +bbbb bbbbbbbbbbbbbbbbbbb bababababa bcbcbcbcbcbcbcbc +SELECT * FROM federated.t1 WHERE col1 = 'gggg' AND col2 = 'ggggggggggggggggggg'; +col1 col2 col3 col4 +gggg ggggggggggggggggggg gagagagaga gcgcgcgcgcgcgcgc +SELECT * FROM federated.t1 WHERE col1 = 'gggg' AND col3 = 'gagagagaga'; +col1 col2 col3 col4 +gggg ggggggggggggggggggg gagagagaga gcgcgcgcgcgcgcgc +SELECT * FROM federated.t1 WHERE col1 = 'ffff' AND col4 = 'fcfcfcfcfcfcfcfc'; +col1 col2 col3 col4 +ffff fffffffffffffffffff fafafafafa fcfcfcfcfcfcfcfc +SELECT * FROM federated.t1 WHERE col1 > 'bbbb'; +col1 col2 col3 col4 +cccc ccccccccccccccccccc cacacacaca cbcbcbcbcbcbcbcb +dddd ddddddddddddddddddd dadadadada dcdcdcdcdcdcdcdc +eeee eeeeeeeeeeeeeeeeeee eaeaeaeaea ecececececececec +ffff fffffffffffffffffff fafafafafa fcfcfcfcfcfcfcfc +gggg ggggggggggggggggggg gagagagaga gcgcgcgcgcgcgcgc +hhhh hhhhhhhhhhhhhhhhhhh hahahahaha hchchchchchchchc +SELECT * FROM federated.t1 WHERE col1 >= 'bbbb'; +col1 col2 col3 col4 +bbbb bbbbbbbbbbbbbbbbbbb bababababa bcbcbcbcbcbcbcbc +cccc ccccccccccccccccccc cacacacaca cbcbcbcbcbcbcbcb +dddd ddddddddddddddddddd dadadadada dcdcdcdcdcdcdcdc +eeee eeeeeeeeeeeeeeeeeee eaeaeaeaea ecececececececec +ffff fffffffffffffffffff fafafafafa fcfcfcfcfcfcfcfc +gggg ggggggggggggggggggg gagagagaga gcgcgcgcgcgcgcgc +hhhh hhhhhhhhhhhhhhhhhhh hahahahaha hchchchchchchchc +SELECT * FROM federated.t1 WHERE col1 < 'bbbb'; +col1 col2 col3 col4 +aaaa aaaaaaaaaaaaaaaaaaa ababababab acacacacacacacac +SELECT * FROM federated.t1 WHERE col1 <= 'bbbb'; +col1 col2 col3 col4 +aaaa aaaaaaaaaaaaaaaaaaa ababababab acacacacacacacac +bbbb bbbbbbbbbbbbbbbbbbb bababababa bcbcbcbcbcbcbcbc +SELECT * FROM federated.t1 WHERE col1 <> 'bbbb'; +col1 col2 col3 col4 +aaaa aaaaaaaaaaaaaaaaaaa ababababab acacacacacacacac +cccc ccccccccccccccccccc cacacacaca cbcbcbcbcbcbcbcb +dddd ddddddddddddddddddd dadadadada dcdcdcdcdcdcdcdc +eeee eeeeeeeeeeeeeeeeeee eaeaeaeaea ecececececececec +ffff fffffffffffffffffff fafafafafa fcfcfcfcfcfcfcfc +gggg ggggggggggggggggggg gagagagaga gcgcgcgcgcgcgcgc +hhhh hhhhhhhhhhhhhhhhhhh hahahahaha hchchchchchchchc +SELECT * FROM federated.t1 WHERE col1 LIKE 'b%'; +col1 col2 col3 col4 +bbbb bbbbbbbbbbbbbbbbbbb bababababa bcbcbcbcbcbcbcbc +SELECT * FROM federated.t1 WHERE col4 LIKE '%b%'; +col1 col2 col3 col4 +bbbb bbbbbbbbbbbbbbbbbbb bababababa bcbcbcbcbcbcbcbc +cccc ccccccccccccccccccc cacacacaca cbcbcbcbcbcbcbcb +SELECT * FROM federated.t1 WHERE col1 NOT LIKE 'c%'; +col1 col2 col3 col4 +aaaa aaaaaaaaaaaaaaaaaaa ababababab acacacacacacacac +bbbb bbbbbbbbbbbbbbbbbbb bababababa bcbcbcbcbcbcbcbc +dddd ddddddddddddddddddd dadadadada dcdcdcdcdcdcdcdc +eeee eeeeeeeeeeeeeeeeeee eaeaeaeaea ecececececececec +ffff fffffffffffffffffff fafafafafa fcfcfcfcfcfcfcfc +gggg ggggggggggggggggggg gagagagaga gcgcgcgcgcgcgcgc +hhhh hhhhhhhhhhhhhhhhhhh hahahahaha hchchchchchchchc +SELECT * FROM federated.t1 WHERE col4 NOT LIKE '%c%'; +col1 col2 col3 col4 +DROP TABLE IF EXISTS federated.t1; +CREATE TABLE federated.t1 ( +`col1` varchar(8) NOT NULL DEFAULT '', +`col2` int(8) NOT NULL DEFAULT 0, +`col3` varchar(8) NOT NULL DEFAULT '', +primary key (`col1`, `col2`, `col3`)); +DROP TABLE IF EXISTS federated.t1; +CREATE TABLE federated.t1 ( +`col1` varchar(8) NOT NULL DEFAULT '', +`col2` varchar(8) NOT NULL DEFAULT '', +`col3` varchar(8) NOT NULL DEFAULT '', +primary key (`col1`, `col2`, `col3`)) +ENGINE="FEDERATED" + DEFAULT CHARSET=latin1 +COMMENT='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1'; +INSERT INTO federated.t1 VALUES ('a00', '110', 'cc0'); +INSERT INTO federated.t1 VALUES ('aaa', '111', 'ccc'); +INSERT INTO federated.t1 VALUES ('bbb', '222', 'yyy'); +INSERT INTO federated.t1 VALUES ('ccc', '111', 'zzz'); +INSERT INTO federated.t1 VALUES ('ccd', '112', 'zzzz'); +SELECT col3 FROM federated.t1 WHERE ( +(col1 = 'aaa' AND col2 >= '111') OR col1 > 'aaa') AND +(col1 < 'ccc' OR ( col1 = 'ccc' AND col2 <= '111')); +col3 +ccc +yyy +zzz +SELECT col3 FROM federated.t1 WHERE ( +(col1 = 'aaa' AND col2 >= '111') OR col1 > 'aaa') AND +(col1 < 'ccc' OR ( col1 = 'ccc' AND col2 <= '111')); +col3 +ccc +yyy +zzz +DROP TABLE IF EXISTS federated.t1; +CREATE TABLE federated.t1 ( `id` int, `name` varchar(32), `floatval` float, @@ -494,30 +996,6 @@ ENGINE="FEDERATED" DEFAULT CHARSET=latin1 COMMENT='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1'; INSERT INTO federated.t1 VALUES (3,3,3),(1,1,1),(2,2,2),(4,4,4); -EXPLAIN SELECT * FROM federated.t1 ORDER BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 Using filesort -EXPLAIN SELECT * FROM federated.t1 ORDER BY b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 Using filesort -EXPLAIN SELECT * FROM federated.t1 ORDER BY c; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 Using filesort -EXPLAIN SELECT a FROM federated.t1 ORDER BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 Using filesort -EXPLAIN SELECT b FROM federated.t1 ORDER BY b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 Using filesort -EXPLAIN SELECT a,b FROM federated.t1 ORDER BY b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 Using filesort -EXPLAIN SELECT a,b FROM federated.t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 -EXPLAIN SELECT a,b,c FROM federated.t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 (i1 int, i2 int, i3 int, i4 int, i5 int, i6 int, i7 int, i8 int, i9 int, i10 int, i11 int, i12 int, i13 int, i14 int, i15 int, i16 int, i17 @@ -906,13 +1384,6 @@ INSERT INTO federated.t1 (name, country_id, other) VALUES ('Lenz', 2, 22222); INSERT INTO federated.t1 (name, country_id, other) VALUES ('Marizio', 3, 33333); INSERT INTO federated.t1 (name, country_id, other) VALUES ('Monty', 4, 33333); INSERT INTO federated.t1 (name, country_id, other) VALUES ('Sanja', 5, 33333); -EXPLAIN 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 -federated.t1.country_id = federated.countries.id; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE countries ALL PRIMARY NULL NULL NULL 5 -1 SIMPLE t1 ref country_id country_id 4 federated.countries.id 120 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 @@ -923,13 +1394,6 @@ Lenz 2 22222 Germany Marizio 3 33333 Italy Monty 4 33333 Finland Sanja 5 33333 Ukraine -EXPLAIN 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 INNER JOIN federated.countries ON -federated.t1.country_id = federated.countries.id; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE countries ALL PRIMARY NULL NULL NULL 5 -1 SIMPLE t1 ref country_id country_id 4 federated.countries.id 120 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 INNER JOIN federated.countries ON @@ -940,14 +1404,6 @@ Lenz 2 22222 Germany Marizio 3 33333 Italy Monty 4 33333 Finland Sanja 5 33333 Ukraine -EXPLAIN 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 INNER JOIN federated.countries ON -federated.t1.country_id = federated.countries.id -WHERE federated.t1.name = 'Monty'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE countries ALL PRIMARY NULL NULL NULL 5 -1 SIMPLE t1 ref country_id country_id 4 federated.countries.id 120 Using where 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 INNER JOIN federated.countries ON @@ -955,13 +1411,6 @@ federated.t1.country_id = federated.countries.id WHERE federated.t1.name = 'Monty'; name country_id other country Monty 4 33333 Finland -EXPLAIN SELECT federated.t1.*, federated.countries.country -FROM federated.t1 LEFT JOIN federated.countries -ON federated.t1.country_id = federated.countries.id -ORDER BY federated.countries.id; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 Using temporary; Using filesort -1 SIMPLE countries eq_ref PRIMARY PRIMARY 4 federated.t1.country_id 1 SELECT federated.t1.*, federated.countries.country FROM federated.t1 LEFT JOIN federated.countries ON federated.t1.country_id = federated.countries.id @@ -972,13 +1421,6 @@ id country_id name other country 3 3 Marizio 33333 Italy 4 4 Monty 33333 Finland 5 5 Sanja 33333 Ukraine -EXPLAIN SELECT federated.t1.*, federated.countries.country -FROM federated.t1 LEFT JOIN federated.countries -ON federated.t1.country_id = federated.countries.id -ORDER BY federated.countries.country; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 Using temporary; Using filesort -1 SIMPLE countries eq_ref PRIMARY PRIMARY 4 federated.t1.country_id 1 SELECT federated.t1.*, federated.countries.country FROM federated.t1 LEFT JOIN federated.countries ON federated.t1.country_id = federated.countries.id @@ -989,13 +1431,6 @@ id country_id name other country 1 1 Kumar 11111 India 3 3 Marizio 33333 Italy 5 5 Sanja 33333 Ukraine -EXPLAIN SELECT federated.t1.*, federated.countries.country -FROM federated.t1 RIGHT JOIN federated.countries -ON federated.t1.country_id = federated.countries.id -ORDER BY federated.t1.country_id; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE countries ALL NULL NULL NULL NULL 5 Using temporary; Using filesort -1 SIMPLE t1 ref country_id country_id 4 federated.countries.id 120 SELECT federated.t1.*, federated.countries.country FROM federated.t1 RIGHT JOIN federated.countries ON federated.t1.country_id = federated.countries.id @@ -1007,6 +1442,21 @@ id country_id name other country 4 4 Monty 33333 Finland 5 5 Sanja 33333 Ukraine DROP TABLE federated.countries; +OPTIMIZE TABLE federated.t1; +Table Op Msg_type Msg_text +federated.t1 optimize status OK +REPAIR TABLE federated.t1; +Table Op Msg_type Msg_text +federated.t1 repair status OK +REPAIR TABLE federated.t1 QUICK; +Table Op Msg_type Msg_text +federated.t1 repair status OK +REPAIR TABLE federated.t1 EXTENDED; +Table Op Msg_type Msg_text +federated.t1 repair status OK +REPAIR TABLE federated.t1 USE_FRM; +Table Op Msg_type Msg_text +federated.t1 repair status OK DROP TABLE IF EXISTS federated.t1; DROP DATABASE IF EXISTS federated; DROP TABLE IF EXISTS federated.t1; diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index f75fe0d1627..b0c0178328e 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -119,7 +119,7 @@ c char(1) character set latin1 collate latin1_danish_ci insert into t1 values ('A','B','C'); insert into t1 values ('a','c','c'); select * from t1 where a in (b); -ERROR HY000: Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation ' IN ' +ERROR HY000: Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' select * from t1 where a in (b,c); ERROR HY000: Illegal mix of collations (latin1_general_ci,IMPLICIT), (latin1_swedish_ci,IMPLICIT), (latin1_danish_ci,IMPLICIT) for operation ' IN ' select * from t1 where 'a' in (a,b,c); @@ -193,3 +193,26 @@ select * from t1 where a in (NULL, 'aa'); a aa drop table t1; +CREATE TABLE t1 (a int PRIMARY KEY); +INSERT INTO t1 VALUES (44), (45), (46); +SELECT * FROM t1 WHERE a IN (45); +a +45 +SELECT * FROM t1 WHERE a NOT IN (0, 45); +a +44 +46 +SELECT * FROM t1 WHERE a NOT IN (45); +a +44 +46 +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a NOT IN (45); +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` <> 45) +SELECT * FROM v1; +a +44 +46 +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 98f3d59485f..01abd791881 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -48,6 +48,7 @@ TABLE_PRIVILEGES COLUMN_PRIVILEGES TABLE_CONSTRAINTS KEY_COLUMN_USAGE +TRIGGERS columns_priv db func @@ -77,6 +78,7 @@ c table_name TABLES TABLES TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_CONSTRAINTS TABLE_CONSTRAINTS +TRIGGERS TRIGGERS tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second @@ -94,6 +96,7 @@ c table_name TABLES TABLES TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_CONSTRAINTS TABLE_CONSTRAINTS +TRIGGERS TRIGGERS tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second @@ -111,6 +114,7 @@ c table_name TABLES TABLES TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_CONSTRAINTS TABLE_CONSTRAINTS +TRIGGERS TRIGGERS tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second @@ -153,7 +157,7 @@ c varchar(64) utf8_general_ci NO select,insert,update,references select * from information_schema.COLUMNS where table_name="t1" and column_name= "a"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT -NULL mysqltest t1 a 1 NULL YES int NULL NULL 11 0 NULL NULL int(11) select,insert,update,references +NULL mysqltest t1 a 1 NULL YES int NULL NULL 10 NULL NULL NULL int(11) select,insert,update,references show columns from mysqltest.t1 where field like "%a%"; Field Type Null Key Default Extra a int(11) YES NULL @@ -298,6 +302,9 @@ show create function sub2; Function sql_mode Create Function sub2 CREATE FUNCTION `test`.`sub2`(i int) RETURNS int(11) return i+1 +show function status like "sub2"; +Db Name Type Definer Modified Created Security_type Comment +test sub2 FUNCTION mysqltest_1@localhost # # DEFINER drop function sub2; show create procedure sel2; Procedure sql_mode Create Procedure @@ -520,7 +527,7 @@ c float(5,2) NULL NULL 5 2 d decimal(6,4) NULL NULL 6 4 e float NULL NULL 12 NULL f decimal(6,3) NULL NULL 6 3 -g int(11) NULL NULL 11 0 +g int(11) NULL NULL 10 NULL h double(10,3) NULL NULL 10 3 i double NULL NULL 22 NULL drop table t1; @@ -577,6 +584,7 @@ Tables_in_information_schema (T%) TABLES TABLE_PRIVILEGES TABLE_CONSTRAINTS +TRIGGERS create database information_schema; ERROR HY000: Can't create database 'information_schema'; database exists use information_schema; @@ -585,6 +593,7 @@ Tables_in_information_schema (T%) Table_type TABLES TEMPORARY TABLE_PRIVILEGES TEMPORARY TABLE_CONSTRAINTS TEMPORARY +TRIGGERS TEMPORARY create table t1(a int); ERROR 42S02: Unknown table 't1' in information_schema use test; @@ -596,6 +605,7 @@ Tables_in_information_schema (T%) TABLES TABLE_PRIVILEGES TABLE_CONSTRAINTS +TRIGGERS select table_name from tables where table_name='user'; table_name user @@ -690,7 +700,7 @@ CREATE TABLE t_crashme ( f1 BIGINT); CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1; CREATE VIEW a2 AS SELECT t_CRASHME FROM a1; count(*) -100 +101 drop view a2, a1; drop table t_crashme; select table_schema,table_name, column_name from @@ -701,6 +711,8 @@ information_schema COLUMNS COLUMN_TYPE information_schema ROUTINES ROUTINE_DEFINITION information_schema ROUTINES SQL_MODE information_schema VIEWS VIEW_DEFINITION +information_schema TRIGGERS ACTION_CONDITION +information_schema TRIGGERS ACTION_STATEMENT select table_name, column_name, data_type from information_schema.columns where data_type = 'datetime'; table_name column_name data_type @@ -709,6 +721,7 @@ TABLES UPDATE_TIME datetime TABLES CHECK_TIME datetime ROUTINES CREATED datetime ROUTINES LAST_ALTERED datetime +TRIGGERS CREATED datetime SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS B @@ -755,8 +768,71 @@ delete from mysql.db where user='mysqltest_4'; flush privileges; SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA; table_schema count(*) -information_schema 15 +information_schema 16 mysql 17 +create table t1 (i int, j int); +create trigger trg1 before insert on t1 for each row +begin +if new.j > 10 then +set new.j := 10; +end if; +end| +create trigger trg2 before update on t1 for each row +begin +if old.i % 2 = 0 then +set new.j := -1; +end if; +end| +create trigger trg3 after update on t1 for each row +begin +if new.j = -1 then +set @fired:= "Yes"; +end if; +end| +show triggers; +Trigger Event Table Statement Timing Created +trg1 INSERT t1 +begin +if new.j > 10 then +set new.j := 10; +end if; +end BEFORE NULL +trg2 UPDATE t1 +begin +if old.i % 2 = 0 then +set new.j := -1; +end if; +end BEFORE NULL +trg3 UPDATE t1 +begin +if new.j = -1 then +set @fired:= "Yes"; +end if; +end AFTER NULL +select * from information_schema.triggers; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED +NULL test trg1 INSERT NULL test t1 0 NULL +begin +if new.j > 10 then +set new.j := 10; +end if; +end ROW BEFORE NULL NULL OLD NEW NULL +NULL test trg2 UPDATE NULL test t1 0 NULL +begin +if old.i % 2 = 0 then +set new.j := -1; +end if; +end ROW BEFORE NULL NULL OLD NEW NULL +NULL test trg3 UPDATE NULL test t1 0 NULL +begin +if new.j = -1 then +set @fired:= "Yes"; +end if; +end ROW AFTER NULL NULL OLD NEW NULL +drop trigger trg1; +drop trigger trg2; +drop trigger trg3; +drop table t1; create database mysqltest; create table mysqltest.t1 (f1 int, f2 int); create table mysqltest.t2 (f1 int); @@ -841,3 +917,26 @@ drop procedure p2; show create database information_schema; Database Create Database information_schema CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ +create table t1(f1 LONGBLOB, f2 LONGTEXT); +select column_name,data_type,CHARACTER_OCTET_LENGTH, +CHARACTER_MAXIMUM_LENGTH +from information_schema.columns +where table_name='t1'; +column_name data_type CHARACTER_OCTET_LENGTH CHARACTER_MAXIMUM_LENGTH +f1 longblob 4294967295 4294967295 +f2 longtext 4294967295 4294967295 +drop table t1; +create table t1(f1 tinyint, f2 SMALLINT, f3 mediumint, f4 int, +f5 BIGINT, f6 BIT, f7 bit(64)); +select column_name, NUMERIC_PRECISION, NUMERIC_SCALE +from information_schema.columns +where table_name='t1'; +column_name NUMERIC_PRECISION NUMERIC_SCALE +f1 3 NULL +f2 5 NULL +f3 7 NULL +f4 10 NULL +f5 19 NULL +f6 1 NULL +f7 64 NULL +drop table t1; diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 3da5cc7bd11..ece30924055 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -16,11 +16,13 @@ TABLE_PRIVILEGES COLUMN_PRIVILEGES TABLE_CONSTRAINTS KEY_COLUMN_USAGE +TRIGGERS show tables from INFORMATION_SCHEMA like 'T%'; Tables_in_information_schema (T%) TABLES TABLE_PRIVILEGES TABLE_CONSTRAINTS +TRIGGERS create database `inf%`; use `inf%`; show tables; diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index a19734d55b5..69871b2110b 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -577,3 +577,15 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using filesort DROP VIEW v1; DROP TABLE t1; +CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL); +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t1 VALUES (1, 2); +SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP; +a b c count +1 1 1 1 +1 1 NULL 1 +1 2 1 1 +1 2 NULL 1 +1 NULL NULL 2 +NULL NULL NULL 2 +DROP TABLE t1; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 81d85306e93..0050dfc0841 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -775,3 +775,28 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp select ? from t1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? from t1' at line 1 drop table t1; +prepare stmt from "select @@time_zone"; +execute stmt; +@@time_zone +SYSTEM +set @@time_zone:='Japan'; +execute stmt; +@@time_zone +Japan +prepare stmt from "select @@tx_isolation"; +execute stmt; +@@tx_isolation +REPEATABLE-READ +set transaction isolation level read committed; +execute stmt; +@@tx_isolation +READ-COMMITTED +set transaction isolation level serializable; +execute stmt; +@@tx_isolation +SERIALIZABLE +set @@tx_isolation=default; +execute stmt; +@@tx_isolation +REPEATABLE-READ +deallocate prepare stmt; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 6f9b4e41b22..e8e30b3653a 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -744,3 +744,27 @@ a b 1 3 DROP VIEW v1; DROP TABLE t1; +CREATE TABLE t1 (name varchar(15) NOT NULL, KEY idx(name)); +INSERT INTO t1 VALUES ('Betty'), ('Anna'); +SELECT * FROM t1; +name +Anna +Betty +DELETE FROM t1 WHERE name NOT LIKE 'A%a'; +SELECT * FROM t1; +name +Anna +DROP TABLE t1; +CREATE TABLE t1 (a int, KEY idx(a)); +INSERT INTO t1 VALUES (NULL), (1), (2), (3); +SELECT * FROM t1; +a +NULL +1 +2 +3 +DELETE FROM t1 WHERE NOT(a <=> 2); +SELECT * FROM t1; +a +2 +DROP TABLE t1; diff --git a/mysql-test/r/rpl_log.result b/mysql-test/r/rpl_log.result index 5e0eec6305d..be8d5291ad9 100644 --- a/mysql-test/r/rpl_log.result +++ b/mysql-test/r/rpl_log.result @@ -69,12 +69,12 @@ master-bin.000002 346 Query 1 434 use `test`; insert into t1 values (1) master-bin.000002 434 Query 1 510 use `test`; drop table t1 show binary logs; Log_name File_size -master-bin.000001 0 +master-bin.000001 1389 master-bin.000002 510 start slave; show binary logs; Log_name File_size -slave-bin.000001 0 +slave-bin.000001 1559 slave-bin.000002 348 show binlog events in 'slave-bin.000001' from 4; Log_name Pos Event_type Server_id End_log_pos Info diff --git a/mysql-test/r/rpl_rotate_logs.result b/mysql-test/r/rpl_rotate_logs.result index a6d3697987a..bf2ef98c87f 100644 --- a/mysql-test/r/rpl_rotate_logs.result +++ b/mysql-test/r/rpl_rotate_logs.result @@ -27,8 +27,8 @@ insert into t2 values (34),(67),(123); flush logs; show binary logs; Log_name File_size -master-bin.000001 0 -master-bin.000002 0 +master-bin.000001 592 +master-bin.000002 363 master-bin.000003 98 create table t3 select * from temp_table; select * from t3; @@ -43,12 +43,12 @@ start slave; purge master logs to 'master-bin.000002'; show master logs; Log_name File_size -master-bin.000002 0 +master-bin.000002 363 master-bin.000003 407 purge binary logs to 'master-bin.000002'; show binary logs; Log_name File_size -master-bin.000002 0 +master-bin.000002 363 master-bin.000003 407 purge master logs before now(); show binary logs; @@ -74,8 +74,8 @@ count(*) create table t4 select * from temp_table; show binary logs; Log_name File_size -master-bin.000003 0 -master-bin.000004 0 +master-bin.000003 4185 +master-bin.000004 4190 master-bin.000005 2032 show master status; File Position Binlog_Do_DB Binlog_Ignore_DB diff --git a/mysql-test/r/rpl_sp.result b/mysql-test/r/rpl_sp.result index 64e09839f1b..15180abe8fd 100644 --- a/mysql-test/r/rpl_sp.result +++ b/mysql-test/r/rpl_sp.result @@ -237,7 +237,7 @@ select * from t1; a 10 delete from t1; -drop trigger t1.trg; +drop trigger trg; insert into t1 values (1); select * from t1; a @@ -248,7 +248,7 @@ master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1 master-bin.000002 # Query 1 # use `mysqltest1`; create trigger trg before insert on t1 for each row set new.a= 10 master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1) master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1 -master-bin.000002 # Query 1 # use `mysqltest1`; drop trigger t1.trg +master-bin.000002 # Query 1 # use `mysqltest1`; drop trigger trg master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1) select * from t1; a diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index d0503d1b83b..2a38c5fc86f 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2746,3 +2746,42 @@ WHERE COUNT(*) 4 drop table t1,t2,t3; +create table t1 (f1 int); +insert into t1 values (1),(NULL); +create table t2 (f2 int, f3 int, f4 int); +create index idx1 on t2 (f4); +insert into t2 values (1,2,3),(2,4,6); +select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3) +from t2 C where A.f4 = C.f4) or A.f3 IS NULL; +f2 +1 +NULL +drop table t1,t2; +create table t2 (a tinyint unsigned); +create index t2i on t2(a); +insert into t2 values (0), (254), (255); +explain select * from t2 where a > -1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index +select * from t2 where a > -1; +a +0 +254 +255 +drop table t2; +CREATE TABLE t1 (a int, b int, c int); +INSERT INTO t1 +SELECT 50, 3, 3 FROM DUAL +WHERE NOT EXISTS +(SELECT * FROM t1 WHERE a = 50 AND b = 3); +SELECT * FROM t1; +a b c +50 3 3 +INSERT INTO t1 +SELECT 50, 3, 3 FROM DUAL +WHERE NOT EXISTS +(SELECT * FROM t1 WHERE a = 50 AND b = 3); +SELECT * FROM t1; +a b c +50 3 3 +DROP TABLE t1; diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result index 3a0d2aa5f36..184978e4a0d 100644 --- a/mysql-test/r/sp-security.result +++ b/mysql-test/r/sp-security.result @@ -212,3 +212,27 @@ REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; drop function bug_9503; use test; drop database mysqltest; +use test; +select current_user(); +current_user() +root@localhost +select user(); +user() +root@localhost +create procedure bug7291_0 () sql security invoker select current_user(), user(); +create procedure bug7291_1 () sql security definer call bug7291_0(); +create procedure bug7291_2 () sql security invoker call bug7291_0(); +grant execute on procedure bug7291_0 to user1@localhost; +grant execute on procedure bug7291_1 to user1@localhost; +grant execute on procedure bug7291_2 to user1@localhost; +call bug7291_2(); +current_user() user() +user1@localhost user1@localhost +call bug7291_1(); +current_user() user() +root@localhost user1@localhost +drop procedure bug7291_1; +drop procedure bug7291_2; +drop procedure bug7291_0; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; +drop user user1@localhost; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 345de49c21d..fd63204e32f 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -2657,16 +2657,22 @@ end| call avg ()| drop procedure avg| drop procedure if exists bug6129| -set @@sql_mode = 'traditional'| -create procedure bug6129(mode text) -select @@sql_mode = mode| -call bug6129(@@sql_mode)| -@@sql_mode = mode -1 -set @@sql_mode = ''| -call bug6129(@@sql_mode)| -@@sql_mode = mode -0 +set @old_mode= @@sql_mode; +set @@sql_mode= ""; +create procedure bug6129() +select @@sql_mode| +call bug6129()| +@@sql_mode + +set @@sql_mode= "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"| +call bug6129()| +@@sql_mode +NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO +set @@sql_mode= "NO_ZERO_IN_DATE"| +call bug6129()| +@@sql_mode +NO_ZERO_IN_DATE +set @@sql_mode=@old_mode; drop procedure bug6129| drop procedure if exists bug9856| create procedure bug9856() @@ -3079,4 +3085,18 @@ one 1 delete from t1| drop procedure bug9565_sub| drop procedure bug9565| +drop procedure if exists bug9538| +create procedure bug9538() +set @@sort_buffer_size = 1000000| +set @x = @@sort_buffer_size| +set @@sort_buffer_size = 2000000| +select @@sort_buffer_size| +@@sort_buffer_size +2000000 +call bug9538()| +select @@sort_buffer_size| +@@sort_buffer_size +1000000 +set @@sort_buffer_size = @x| +drop procedure bug9538| drop table t1,t2; diff --git a/mysql-test/r/timezone_grant.result b/mysql-test/r/timezone_grant.result index dfe0b75ee43..3758f3c2645 100644 --- a/mysql-test/r/timezone_grant.result +++ b/mysql-test/r/timezone_grant.result @@ -47,6 +47,13 @@ select * from mysql.time_zone_name; ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'time_zone_name' select Name, convert_tz('2004-11-30 12:00:00', Name, 'UTC') from mysql.time_zone_name; ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'time_zone_name' +drop table t1, t2; +create table t1 (a int, b datetime); +create table t2 (a int, b varchar(40)); +update t1 set b = '2005-01-01 10:00'; +update t1 set b = convert_tz(b, 'UTC', 'UTC'); +update t1 join t2 on (t1.a = t2.a) set t1.b = '2005-01-01 10:00' where t2.b = 'foo'; +update t1 join t2 on (t1.a = t2.a) set t1.b = convert_tz('2005-01-01 10:00','UTC','UTC') where t2.b = 'foo'; 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\_%'; diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index efd09ba08fc..7e3a6fa65d4 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -12,13 +12,13 @@ insert into t1 values (1); select @a; @a 1 -drop trigger t1.trg; +drop trigger trg; create trigger trg before insert on t1 for each row set @a:=new.i; insert into t1 values (123); select @a; @a 123 -drop trigger t1.trg; +drop trigger trg; drop table t1; create table t1 (i int not null, j int); create trigger trg before insert on t1 for each row @@ -33,7 +33,7 @@ select * from t1| i j 1 10 2 3 -drop trigger t1.trg| +drop trigger trg| drop table t1| create table t1 (i int not null primary key); create trigger trg after insert on t1 for each row @@ -43,7 +43,7 @@ insert into t1 values (2),(3),(4),(5); select @a; @a 2:3:4:5 -drop trigger t1.trg; +drop trigger trg; drop table t1; create table t1 (aid int not null primary key, balance int not null default 0); insert into t1 values (1, 1000), (2,3000); @@ -65,7 +65,7 @@ Too big change for aid = 2 aid balance 1 1500 2 3000 -drop trigger t1.trg| +drop trigger trg| drop table t1| create table t1 (i int); insert into t1 values (1),(2),(3),(4); @@ -76,7 +76,7 @@ update t1 set i=3; select @total_change; @total_change 2 -drop trigger t1.trg; +drop trigger trg; drop table t1; create table t1 (i int); insert into t1 values (1),(2),(3),(4); @@ -87,7 +87,7 @@ delete from t1 where i <= 3; select @del_sum; @del_sum 6 -drop trigger t1.trg; +drop trigger trg; drop table t1; create table t1 (i int); insert into t1 values (1),(2),(3),(4); @@ -97,7 +97,7 @@ delete from t1 where i <> 0; select @del; @del 1 -drop trigger t1.trg; +drop trigger trg; drop table t1; create table t1 (i int, j int); create trigger trg1 before insert on t1 for each row @@ -137,9 +137,9 @@ i j 1 20 2 -1 3 20 -drop trigger t1.trg1; -drop trigger t1.trg2; -drop trigger t1.trg3; +drop trigger trg1; +drop trigger trg2; +drop trigger trg3; drop table t1; create table t1 (id int not null primary key, data int); create trigger t1_bi before insert on t1 for each row @@ -197,7 +197,7 @@ select * from t2; event INSERT INTO t1 id=1 data='one' INSERT INTO t1 id=2 data='two' -drop trigger t1.t1_ai; +drop trigger t1_ai; create trigger t1_bi before insert on t1 for each row begin if exists (select id from t3 where id=new.fk) then @@ -271,6 +271,7 @@ id copy 3 NULL drop table t1, t2; create table t1 (i int); +create table t3 (i int); create trigger trg before insert on t1 for each row set @a:= old.i; ERROR HY000: There is no OLD row in on INSERT trigger create trigger trg before delete on t1 for each row set @a:= new.i; @@ -292,14 +293,19 @@ create trigger trg after insert on t1 for each row set @a:=1; ERROR HY000: Trigger already exists create trigger trg2 before insert on t1 for each row set @a:=1; ERROR HY000: Trigger already exists -drop trigger t1.trg; -drop trigger t1.trg; +create trigger trg before insert on t3 for each row set @a:=1; +ERROR HY000: Trigger already exists +create trigger trg2 before insert on t3 for each row set @a:=1; +drop trigger trg2; +drop trigger trg; +drop trigger trg; ERROR HY000: Trigger does not exist create view v1 as select * from t1; create trigger trg before insert on v1 for each row set @a:=1; ERROR HY000: 'test.v1' is not BASE TABLE drop view v1; drop table t1; +drop table t3; create temporary table t1 (i int); create trigger trg before insert on t1 for each row set @a:=1; ERROR HY000: Trigger's 't1' is view or temporary table @@ -307,7 +313,7 @@ drop table t1; create table t1 (x1col char); create trigger tx1 before insert on t1 for each row set new.x1col = 'x'; insert into t1 values ('y'); -drop trigger t1.tx1; +drop trigger tx1; drop table t1; create table t1 (i int) engine=myisam; insert into t1 values (1), (2); @@ -318,8 +324,8 @@ delete from t1; select @del_before, @del_after; @del_before @del_after 3 3 -drop trigger t1.trg1; -drop trigger t1.trg2; +drop trigger trg1; +drop trigger trg2; drop table t1; create table t1 (a int); create trigger trg1 before insert on t1 for each row set new.a= 10; @@ -336,6 +342,15 @@ create table t1 (i int); create trigger trg1 before insert on t1 for each row set @a:= 1; drop database mysqltest; use test; +create database mysqltest; +create table mysqltest.t1 (i int); +create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1; +ERROR HY000: Trigger in wrong schema +use mysqltest; +create trigger test.trg1 before insert on t1 for each row set @a:= 1; +ERROR HY000: Trigger in wrong schema +drop database mysqltest; +use test; create table t1 (i int, j int default 10, k int not null, key (k)); create table t2 (i int); insert into t1 (i, k) values (1, 1); @@ -549,7 +564,7 @@ i k 1 1 2 2 alter table t1 add primary key (i); -drop trigger t1.bi; +drop trigger bi; insert into t1 values (2, 4) on duplicate key update k= k + 10; ERROR 42S22: Unknown column 'bt' in 'NEW' select * from t1; @@ -578,5 +593,5 @@ create trigger t1_bu before update on t1 for each row set new.col1= bug5893(); drop function bug5893; update t1 set col2 = 4; ERROR 42000: FUNCTION test.bug5893 does not exist -drop trigger t1.t1_bu; +drop trigger t1_bu; drop table t1; diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index b0055346d61..42fb8064044 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -240,3 +240,14 @@ update t1, t2 set t1.a = t2.a where t2.b = t1.b; show warnings; Level Code Message drop table t1, t2; +create table t1(f1 int, f2 int); +create table t2(f3 int, f4 int); +create index idx on t2(f3); +insert into t1 values(1,0),(2,0); +insert into t2 values(1,1),(2,2); +UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1); +select * from t1; +f1 f2 +1 1 +2 2 +drop table t1,t2; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 684c4950acd..624fae4d728 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1245,7 +1245,7 @@ select * from v1; s1 select * from t1; s1 -drop trigger t1.t1_bi; +drop trigger t1_bi; drop view v1; drop table t1; create table t1 (s1 tinyint); @@ -1831,6 +1831,31 @@ select * from v1; t 01:00 drop view v1; +create table t1 (a timestamp default now()); +create table t2 (b timestamp default now()); +create view v1 as select a,b,t1.a < now() from t1,t2 where t1.a < now(); +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select sql_no_cache `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,(`test`.`t1`.`a` < now()) AS `t1.a < now()` from (`test`.`t1` join `test`.`t2`) where (`test`.`t1`.`a` < now()) +drop view v1; +drop table t1, t2; +CREATE TABLE t1 ( a varchar(50) ); +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = CURRENT_USER(); +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select sql_no_cache `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = current_user()) +DROP VIEW v1; +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = VERSION(); +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = version()) +DROP VIEW v1; +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = DATABASE(); +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select sql_no_cache `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = database()) +DROP VIEW v1; +DROP TABLE t1; CREATE TABLE t1 (col1 time); CREATE TABLE t2 (col1 time); CREATE VIEW v1 AS SELECT CONVERT_TZ(col1,'GMT','MET') FROM t1; diff --git a/mysql-test/t/ctype_eucjpms.test b/mysql-test/t/ctype_eucjpms.test index cec1e2a9861..3609407fe96 100644 --- a/mysql-test/t/ctype_eucjpms.test +++ b/mysql-test/t/ctype_eucjpms.test @@ -346,6 +346,18 @@ DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; +#Test bug#11717 +CREATE TABLE t1(c1 varchar(10)) default character set = eucjpms; + +insert into t1 values(_ucs2 0x00F7); +insert into t1 values(_eucjpms 0xA1E0); +insert into t1 values(_ujis 0xA1E0); +insert into t1 values(_sjis 0x8180); +insert into t1 values(_cp932 0x8180); + +SELECT HEX(c1) FROM t1; + +DROP TABLE t1; SET collation_connection='eucjpms_japanese_ci'; -- source include/ctype_filesort.inc diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index 1e33efe1c0e..255b9dc22d7 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -1,19 +1,7 @@ ---source include/have_federated_db.inc - -source include/master-slave.inc; - -# remote table creation +source include/federated.inc; connection slave; ---replicate-ignore-db=federated -stop slave; - ---disable_warnings -# at this point, we are connected to master -DROP DATABASE IF EXISTS federated; ---enable_warnings -CREATE DATABASE federated; - +DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' @@ -21,14 +9,10 @@ CREATE TABLE federated.t1 ( DEFAULT CHARSET=latin1; connection master; ---disable_warnings -DROP DATABASE IF EXISTS federated; ---enable_warnings -CREATE DATABASE federated; - +DROP TABLE IF EXISTS federated.t1; # test too many items (malformed) in the comment string url ---error 1005 -eval CREATE TABLE federated.t1 ( +--error 1432 +CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' ) @@ -36,8 +20,8 @@ eval CREATE TABLE federated.t1 ( COMMENT='mysql://root@127.0.0.1:@/too/many/items/federated/t1'; # test not enough items (malformed) in the comment string url ---error 1005 -eval CREATE TABLE federated.t1 ( +--error 1432 +CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' ) @@ -46,7 +30,7 @@ eval CREATE TABLE federated.t1 ( # test non-existant table --replace_result $SLAVE_MYPORT SLAVE_PORT ---error 1219 +--error 1434 eval CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' @@ -56,7 +40,7 @@ eval CREATE TABLE federated.t1 ( # test bad user/password --replace_result $SLAVE_MYPORT SLAVE_PORT ---error 1218 +--error 1429 eval CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(32) NOT NULL default '' @@ -150,12 +134,9 @@ CREATE TABLE federated.t1 ( `name` varchar(32) NOT NULL default '', `other` int(20) NOT NULL default '0', `created` datetime default '2004-04-04 04:04:04', - PRIMARY KEY (`id`), - KEY `name` (`name`), - KEY `other_key` (`other`)) + PRIMARY KEY (`id`)) DEFAULT CHARSET=latin1; - connection master; --replace_result $SLAVE_MYPORT SLAVE_PORT eval CREATE TABLE federated.t1 ( @@ -163,9 +144,7 @@ eval CREATE TABLE federated.t1 ( `name` varchar(32) NOT NULL default '', `other` int(20) NOT NULL default '0', `created` datetime default '2004-04-04 04:04:04', - PRIMARY KEY (`id`), - KEY `name` (`name`), - KEY `other_key` (`other`)) + PRIMARY KEY (`id`)) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 COMMENT='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; @@ -184,8 +163,84 @@ INSERT INTO federated.t1 (name, other) VALUES ('Tenth Name', 101010); SELECT * FROM federated.t1; # with PRIMARY KEY index_read_idx SELECT * FROM federated.t1 WHERE id = 5; -# with regular key index_read -> index_read_idx 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; +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'; +UPDATE federated.t1 SET name = 'Third name' WHERE name = '3rd name'; +SELECT * FROM federated.t1 WHERE name = 'Third name'; +# rnd_post, ::position +SELECT * FROM federated.t1 ORDER BY id DESC; +SELECT * FROM federated.t1 ORDER BY name; +SELECT * FROM federated.t1 ORDER BY name DESC; +SELECT * FROM federated.t1 ORDER BY name ASC; +SELECT * FROM federated.t1 GROUP BY other; + +# ::delete_row +DELETE FROM federated.t1 WHERE id = 5; +SELECT * FROM federated.t1 WHERE id = 5; + +# ::delete_all_rows +DELETE FROM federated.t1; +SELECT * FROM federated.t1 WHERE id = 5; + +# previous test, but this time with indexes +connection slave; +DROP TABLE IF EXISTS federated.t1; +CREATE TABLE federated.t1 ( + `id` int(20) NOT NULL auto_increment, + `name` varchar(32) NOT NULL default '', + `other` int(20) NOT NULL default '0', + `created` datetime NOT NULL, + PRIMARY KEY (`id`), + key name(`name`), + key other(`other`), + key created(`created`)) + DEFAULT CHARSET=latin1; + +connection master; +DROP TABLE IF EXISTS federated.t1; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval CREATE TABLE federated.t1 ( + `id` int(20) NOT NULL auto_increment, + `name` varchar(32) NOT NULL default '', + `other` int(20) NOT NULL default '0', + `created` datetime NOT NULL, + PRIMARY KEY (`id`), + key name(`name`), + key other(`other`), + key created(`created`)) + ENGINE="FEDERATED" DEFAULT CHARSET=latin1 + COMMENT='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; + +INSERT INTO federated.t1 (name, other, created) + VALUES ('First Name', 11111, '2004-01-01 01:01:01'); +INSERT INTO federated.t1 (name, other, created) + VALUES ('Second Name', 22222, '2004-01-23 02:43:00'); +INSERT INTO federated.t1 (name, other, created) + VALUES ('Third Name', 33333, '2004-02-14 02:14:00'); +INSERT INTO federated.t1 (name, other, created) + VALUES ('Fourth Name', 44444, '2003-04-05 00:00:00'); +INSERT INTO federated.t1 (name, other, created) + VALUES ('Fifth Name', 55555, '2001-02-02 02:02:02'); +INSERT INTO federated.t1 (name, other, created) + VALUES ('Sixth Name', 66666, '2005-06-06 15:30:00'); +INSERT INTO federated.t1 (name, other, created) + VALUES ('Seventh Name', 77777, '2003-12-12 18:32:00'); +INSERT INTO federated.t1 (name, other, created) + VALUES ('Eigth Name', 88888, '2005-03-12 11:00:00'); +INSERT INTO federated.t1 (name, other, created) + VALUES ('Ninth Name', 99999, '2005-03-12 11:00:01'); +INSERT INTO federated.t1 (name, other, created) + VALUES ('Tenth Name', 101010, '2005-03-12 12:00:01'); + +# basic select +SELECT * FROM federated.t1; +# with PRIMARY KEY index_read_idx +SELECT * FROM federated.t1 WHERE id = 5; +# with regular key index_read -> index_read_idx # regular and PRIMARY KEY index_read_idx SELECT * FROM federated.t1 WHERE id = 6 and name = 'Sixth Name'; # with regular key index_read -> index_read_idx @@ -211,7 +266,6 @@ SELECT * FROM federated.t1 WHERE id = 5; # ::delete_all_rows DELETE FROM federated.t1; SELECT * FROM federated.t1 WHERE id = 5; - connection slave; DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( @@ -253,8 +307,7 @@ SET name = 'Fourth Name', other = 'four four four' WHERE name IS NULL AND other IS NULL; UPDATE federated.t1 SET other = 'two two two two' WHERE name = 'Second Name'; -UPDATE federated.t1 SET other = 'seven seven' WHERE name like 'Sec%'; -UPDATE federated.t1 SET other = 'seven seven' WHERE name = 'Seventh Name'; +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 ; SELECT * FROM federated.t1; @@ -338,6 +391,201 @@ SELECT * FROM federated.t1 WHERE name='third'; SELECT * FROM federated.t1 WHERE other=2222; SELECT * FROM federated.t1 WHERE name='third' and other=2222; +# more multi-column indexes, in the primary key +connection slave; +DROP TABLE IF EXISTS federated.t1; +CREATE TABLE federated.t1 ( + `id` int NOT NULL auto_increment, + `col1` int(10) NOT NULL DEFAULT 0, + `col2` varchar(64) NOT NULL DEFAULT '', + `col3` int(20) NOT NULL, + `col4` int(40) NOT NULL, + primary key (`id`, `col1`, `col2`, `col3`, `col4`), + key col1(col1), + key col2(col2), + key col3(col3), + key col4(col4)); + +connection master; +DROP TABLE IF EXISTS federated.t1; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval CREATE TABLE federated.t1 ( + `id` int NOT NULL auto_increment, + `col1` int(10) NOT NULL DEFAULT 0, + `col2` varchar(64) NOT NULL DEFAULT '', + `col3` int(20) NOT NULL, + `col4` int(40) NOT NULL, + primary key (`id`, `col1`, `col2`, `col3`, `col4`), + key col1(col1), + key col2(col2), + key col3(col3), + key col4(col4)) + ENGINE="FEDERATED" + COMMENT='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; + +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES (1, 'one One', 11, 1111); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES (2, 'Two two', 22, 2222); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES (3, 'three Three', 33, 33333); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES (4, 'fourfourfour', 444, 4444444); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES (5, 'five 5 five five 5', 5, 55555); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES (6, 'six six Sixsix', 6666, 6); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES (7, 'seven Sevenseven', 77777, 7777); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES (8, 'eight eight eight', 88888, 88); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES (9, 'nine Nine', 999999, 999999); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES (10, 'Tenth ten TEN', 1010101, 1010); + +SELECT * FROM federated.t1 WHERE col2 = 'two two'; +SELECT * FROM federated.t1 WHERE col2 = 'two Two'; +SELECT * FROM federated.t1 WHERE id = 3; +SELECT * FROM federated.t1 WHERE id = 3 AND col1 = 3; +SELECT * FROM federated.t1 WHERE id = 4 AND col1 = 4 AND col2 = 'Two two'; +SELECT * FROM federated.t1 WHERE id = 4 AND col1 = 4 AND col2 = 'fourfourfour'; +SELECT * FROM federated.t1 WHERE id = 5 AND col2 = 'five 5 five five 5' + AND col3 = 5; +SELECT * FROM federated.t1 WHERE id = 5 AND col2 = 'five 5 five five 5' + AND col3 = 5 + AND col4 = 55555; +SELECT * FROM federated.t1 WHERE id = 5 + AND col2 = 'Two two' AND col3 = 22 + AND col4 = 33; +SELECT * FROM federated.t1 WHERE id = 5 + AND col2 = 'five 5 five five 5' AND col3 = 5 + AND col4 = 55555; +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); +SELECT * FROM federated.t1 WHERE id = 1 + OR col1 = 10 + OR col2 = 'Two two' + OR col3 = 33 + OR col4 = 4444444; +SELECT * FROM federated.t1 WHERE id > 5; +SELECT * FROM federated.t1 WHERE id >= 5; +SELECT * FROM federated.t1 WHERE id < 5; +SELECT * FROM federated.t1 WHERE id <= 5; +SELECT * FROM federated.t1 WHERE id != 5; +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; +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 OR id > 7; +SELECT * FROM federated.t1 WHERE col2 = 'three Three'; +SELECT * FROM federated.t1 WHERE col2 > 'one'; +SELECT * FROM federated.t1 WHERE col2 LIKE 's%'; +SELECT * FROM federated.t1 WHERE col2 LIKE 'si%'; +SELECT * FROM federated.t1 WHERE col2 LIKE 'se%'; +SELECT * FROM federated.t1 WHERE col2 NOT LIKE 'e%'; +SELECT * FROM federated.t1 WHERE col2 <> 'one One'; + +# more multi-column indexes, in the primary key +connection slave; +DROP TABLE IF EXISTS federated.t1; +CREATE TABLE federated.t1 ( + `col1` varchar(8) NOT NULL DEFAULT '', + `col2` varchar(128) NOT NULL DEFAULT '', + `col3` varchar(20) NOT NULL DEFAULT '', + `col4` varchar(40) NOT NULL DEFAULT '', + primary key (`col1`, `col2`, `col3`, `col4`), + key 3key(`col2`,`col3`,`col4`), + key 2key (`col3`,`col4`), + key col4(col4)); + +connection master; +DROP TABLE IF EXISTS federated.t1; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval CREATE TABLE federated.t1 ( + `col1` varchar(8) NOT NULL DEFAULT '', + `col2` varchar(128) NOT NULL DEFAULT '', + `col3` varchar(20) NOT NULL DEFAULT '', + `col4` varchar(40) NOT NULL DEFAULT '', + primary key (`col1`, `col2`, `col3`, `col4`), + key 3key(`col2`,`col3`,`col4`), + key 2key (`col3`,`col4`), + key col4(col4)) + ENGINE="FEDERATED" + COMMENT='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; + +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES ('aaaa', 'aaaaaaaaaaaaaaaaaaa', 'ababababab', 'acacacacacacacac'); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES ('bbbb', 'bbbbbbbbbbbbbbbbbbb', 'bababababa', 'bcbcbcbcbcbcbcbc'); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES ('cccc', 'ccccccccccccccccccc', 'cacacacaca', 'cbcbcbcbcbcbcbcb'); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES ('dddd', 'ddddddddddddddddddd', 'dadadadada', 'dcdcdcdcdcdcdcdc'); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES ('eeee', 'eeeeeeeeeeeeeeeeeee', 'eaeaeaeaea', 'ecececececececec'); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES ('ffff', 'fffffffffffffffffff', 'fafafafafa', 'fcfcfcfcfcfcfcfc'); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES ('gggg', 'ggggggggggggggggggg', 'gagagagaga', 'gcgcgcgcgcgcgcgc'); +INSERT INTO federated.t1 (col1, col2, col3, col4) + VALUES ('hhhh', 'hhhhhhhhhhhhhhhhhhh', 'hahahahaha', 'hchchchchchchchc'); + +SELECT * FROM federated.t1 WHERE col1 = 'cccc'; +SELECT * FROM federated.t1 WHERE col2 = 'eeeeeeeeeeeeeeeeeee'; +SELECT * FROM federated.t1 WHERE col3 = 'bababababa'; +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'; +SELECT * FROM federated.t1 WHERE col1 > 'bbbb'; +SELECT * FROM federated.t1 WHERE col1 >= 'bbbb'; +SELECT * FROM federated.t1 WHERE col1 < 'bbbb'; +SELECT * FROM federated.t1 WHERE col1 <= 'bbbb'; +SELECT * FROM federated.t1 WHERE col1 <> 'bbbb'; +SELECT * FROM federated.t1 WHERE col1 LIKE 'b%'; +SELECT * FROM federated.t1 WHERE col4 LIKE '%b%'; +SELECT * FROM federated.t1 WHERE col1 NOT LIKE 'c%'; +SELECT * FROM federated.t1 WHERE col4 NOT LIKE '%c%'; +connection slave; +DROP TABLE IF EXISTS federated.t1; +CREATE TABLE federated.t1 ( + `col1` varchar(8) NOT NULL DEFAULT '', + `col2` int(8) NOT NULL DEFAULT 0, + `col3` varchar(8) NOT NULL DEFAULT '', + primary key (`col1`, `col2`, `col3`)); + +connection master; +DROP TABLE IF EXISTS federated.t1; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval CREATE TABLE federated.t1 ( + `col1` varchar(8) NOT NULL DEFAULT '', + `col2` varchar(8) NOT NULL DEFAULT '', + `col3` varchar(8) NOT NULL DEFAULT '', + primary key (`col1`, `col2`, `col3`)) + ENGINE="FEDERATED" + DEFAULT CHARSET=latin1 + COMMENT='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; + +INSERT INTO federated.t1 VALUES ('a00', '110', 'cc0'); +INSERT INTO federated.t1 VALUES ('aaa', '111', 'ccc'); +INSERT INTO federated.t1 VALUES ('bbb', '222', 'yyy'); +INSERT INTO federated.t1 VALUES ('ccc', '111', 'zzz'); +INSERT INTO federated.t1 VALUES ('ccd', '112', 'zzzz'); + +# let's see what the foreign database says +connection slave; +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; +SELECT col3 FROM federated.t1 WHERE ( +(col1 = 'aaa' AND col2 >= '111') OR col1 > 'aaa') AND +(col1 < 'ccc' OR ( col1 = 'ccc' AND col2 <= '111')); + # test NULLs connection slave; DROP TABLE IF EXISTS federated.t1; @@ -422,14 +670,6 @@ eval CREATE TABLE federated.t1 ( COMMENT='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; INSERT INTO federated.t1 VALUES (3,3,3),(1,1,1),(2,2,2),(4,4,4); -EXPLAIN SELECT * FROM federated.t1 ORDER BY a; -EXPLAIN SELECT * FROM federated.t1 ORDER BY b; -EXPLAIN SELECT * FROM federated.t1 ORDER BY c; -EXPLAIN SELECT a FROM federated.t1 ORDER BY a; -EXPLAIN SELECT b FROM federated.t1 ORDER BY b; -EXPLAIN SELECT a,b FROM federated.t1 ORDER BY b; -EXPLAIN SELECT a,b FROM federated.t1; -EXPLAIN SELECT a,b,c FROM federated.t1; connection slave; DROP TABLE IF EXISTS federated.t1; @@ -862,32 +1102,16 @@ INSERT INTO federated.t1 (name, country_id, other) VALUES ('Monty', 4, 33333); INSERT INTO federated.t1 (name, country_id, other) VALUES ('Sanja', 5, 33333); #inner join -EXPLAIN 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 -federated.t1.country_id = federated.countries.id; - 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 federated.t1.country_id = federated.countries.id; -EXPLAIN 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 INNER JOIN federated.countries ON -federated.t1.country_id = federated.countries.id; - 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 INNER JOIN federated.countries ON federated.t1.country_id = federated.countries.id; -EXPLAIN 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 INNER JOIN federated.countries ON -federated.t1.country_id = federated.countries.id -WHERE federated.t1.name = 'Monty'; - 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 INNER JOIN federated.countries ON @@ -895,32 +1119,17 @@ federated.t1.country_id = federated.countries.id WHERE federated.t1.name = 'Monty'; #left join -EXPLAIN SELECT federated.t1.*, federated.countries.country -FROM federated.t1 LEFT JOIN federated.countries -ON federated.t1.country_id = federated.countries.id -ORDER BY federated.countries.id; - SELECT federated.t1.*, federated.countries.country FROM federated.t1 LEFT JOIN federated.countries ON federated.t1.country_id = federated.countries.id ORDER BY federated.countries.id; -EXPLAIN SELECT federated.t1.*, federated.countries.country -FROM federated.t1 LEFT JOIN federated.countries -ON federated.t1.country_id = federated.countries.id -ORDER BY federated.countries.country; - SELECT federated.t1.*, federated.countries.country FROM federated.t1 LEFT JOIN federated.countries ON federated.t1.country_id = federated.countries.id ORDER BY federated.countries.country; #right join -EXPLAIN SELECT federated.t1.*, federated.countries.country -FROM federated.t1 RIGHT JOIN federated.countries -ON federated.t1.country_id = federated.countries.id -ORDER BY federated.t1.country_id; - SELECT federated.t1.*, federated.countries.country FROM federated.t1 RIGHT JOIN federated.countries ON federated.t1.country_id = federated.countries.id @@ -928,14 +1137,11 @@ ORDER BY federated.t1.country_id; DROP TABLE federated.countries; -connection master; ---disable_warnings -DROP TABLE IF EXISTS federated.t1; -DROP DATABASE IF EXISTS federated; ---enable_warnings +# optimize and repair tests +OPTIMIZE TABLE federated.t1; +REPAIR TABLE federated.t1; +REPAIR TABLE federated.t1 QUICK; +REPAIR TABLE federated.t1 EXTENDED; +REPAIR TABLE federated.t1 USE_FRM; -connection slave; ---disable_warnings -DROP TABLE IF EXISTS federated.t1; -DROP DATABASE IF EXISTS federated; ---enable_warnings +source include/federated_cleanup.inc; diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 6e0883b821f..3a6b6653098 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -101,3 +101,21 @@ create table t1 (a char(20) character set binary); insert into t1 values ('aa'), ('bb'); select * from t1 where a in (NULL, 'aa'); drop table t1; + +# +# Bug #11885: WHERE condition with NOT IN (one element) +# + +CREATE TABLE t1 (a int PRIMARY KEY); +INSERT INTO t1 VALUES (44), (45), (46); + +SELECT * FROM t1 WHERE a IN (45); +SELECT * FROM t1 WHERE a NOT IN (0, 45); +SELECT * FROM t1 WHERE a NOT IN (45); + +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a NOT IN (45); +SHOW CREATE VIEW v1; +SELECT * FROM v1; + +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 7c0624b67fd..f13a29f07ad 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -157,6 +157,8 @@ select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; show create procedure sel2; show create function sub1; show create function sub2; +--replace_column 5 # 6 # +show function status like "sub2"; connection default; disconnect user1; drop function sub2; @@ -505,6 +507,41 @@ flush privileges; # SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA; + +# +# TRIGGERS table test +# +create table t1 (i int, j int); + +delimiter |; +create trigger trg1 before insert on t1 for each row +begin + if new.j > 10 then + set new.j := 10; + end if; +end| +create trigger trg2 before update on t1 for each row +begin + if old.i % 2 = 0 then + set new.j := -1; + end if; +end| +create trigger trg3 after update on t1 for each row +begin + if new.j = -1 then + set @fired:= "Yes"; + end if; +end| +delimiter ;| +show triggers; +select * from information_schema.triggers; + +drop trigger trg1; +drop trigger trg2; +drop trigger trg3; +drop table t1; + + # # Bug #10964 Information Schema:Authorization check on privilege tables is improper # @@ -569,3 +606,19 @@ drop procedure p2; # Bug #9434 SHOW CREATE DATABASE information_schema; # show create database information_schema; + +# +# Bug #11057 information_schema: columns table has some questionable contents +# +create table t1(f1 LONGBLOB, f2 LONGTEXT); +select column_name,data_type,CHARACTER_OCTET_LENGTH, + CHARACTER_MAXIMUM_LENGTH +from information_schema.columns +where table_name='t1'; +drop table t1; +create table t1(f1 tinyint, f2 SMALLINT, f3 mediumint, f4 int, + f5 BIGINT, f6 BIT, f7 bit(64)); +select column_name, NUMERIC_PRECISION, NUMERIC_SCALE +from information_schema.columns +where table_name='t1'; +drop table t1; diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test index 26fcc7463d6..f3017978588 100644 --- a/mysql-test/t/olap.test +++ b/mysql-test/t/olap.test @@ -266,3 +266,13 @@ EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP; DROP VIEW v1; DROP TABLE t1; +# Test for bug #11543: ROLLUP query with a repeated column in GROUP BY +# + +CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL); +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t1 VALUES (1, 2); + +SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP; + +DROP TABLE t1; diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index e8ea1dc373b..de92c67518e 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -811,3 +811,20 @@ select ??; select ? from t1; --enable_ps_protocol drop table t1; +# +# Bug#9359 "Prepared statements take snapshot of system vars at PREPARE +# time" +# +prepare stmt from "select @@time_zone"; +execute stmt; +set @@time_zone:='Japan'; +execute stmt; +prepare stmt from "select @@tx_isolation"; +execute stmt; +set transaction isolation level read committed; +execute stmt; +set transaction isolation level serializable; +execute stmt; +set @@tx_isolation=default; +execute stmt; +deallocate prepare stmt; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 3d2285b1633..065cef659b8 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -553,3 +553,26 @@ SELECT a,b FROM v1 WHERE a < 2 and b=3; DROP VIEW v1; DROP TABLE t1; + +# +# Bug #11853: DELETE statement with a NOT (LIKE/<=>) where condition +# for an indexed attribute +# + +CREATE TABLE t1 (name varchar(15) NOT NULL, KEY idx(name)); +INSERT INTO t1 VALUES ('Betty'), ('Anna'); + +SELECT * FROM t1; +DELETE FROM t1 WHERE name NOT LIKE 'A%a'; +SELECT * FROM t1; + +DROP TABLE t1; + +CREATE TABLE t1 (a int, KEY idx(a)); +INSERT INTO t1 VALUES (NULL), (1), (2), (3); + +SELECT * FROM t1; +DELETE FROM t1 WHERE NOT(a <=> 2); +SELECT * FROM t1; + +DROP TABLE t1; diff --git a/mysql-test/t/rpl_sp.test b/mysql-test/t/rpl_sp.test index e2a8982ebaa..184ac4edea1 100644 --- a/mysql-test/t/rpl_sp.test +++ b/mysql-test/t/rpl_sp.test @@ -249,7 +249,7 @@ select * from t1; connection master; delete from t1; -drop trigger t1.trg; +drop trigger trg; insert into t1 values (1); select * from t1; --replace_column 2 # 5 # diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 712658a19a6..90f42f3896e 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2330,3 +2330,42 @@ WHERE drop table t1,t2,t3; +# +# Bug #11482 Wrongly applied optimization was erroneously rejecting valid +# rows +create table t1 (f1 int); +insert into t1 values (1),(NULL); +create table t2 (f2 int, f3 int, f4 int); +create index idx1 on t2 (f4); +insert into t2 values (1,2,3),(2,4,6); +select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3) +from t2 C where A.f4 = C.f4) or A.f3 IS NULL; +drop table t1,t2; +# +# Bug #11521 Negative integer keys incorrectly substituted for 0 during +# range analysis. + +create table t2 (a tinyint unsigned); +create index t2i on t2(a); +insert into t2 values (0), (254), (255); +explain select * from t2 where a > -1; +select * from t2 where a > -1; +drop table t2; + +# +# Bug #11745: SELECT ... FROM DUAL with WHERE condition +# + +CREATE TABLE t1 (a int, b int, c int); +INSERT INTO t1 + SELECT 50, 3, 3 FROM DUAL + WHERE NOT EXISTS + (SELECT * FROM t1 WHERE a = 50 AND b = 3); +SELECT * FROM t1; +INSERT INTO t1 + SELECT 50, 3, 3 FROM DUAL + WHERE NOT EXISTS + (SELECT * FROM t1 WHERE a = 50 AND b = 3); +SELECT * FROM t1; + +DROP TABLE t1; diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index 3dc6b9d07ab..15fcba5ebe9 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -341,3 +341,33 @@ REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; drop function bug_9503; use test; drop database mysqltest; + +# +# correct value from current_user() in function run from "security definer" +# (BUG#7291) +# +connection con1root; +use test; + +select current_user(); +select user(); +create procedure bug7291_0 () sql security invoker select current_user(), user(); +create procedure bug7291_1 () sql security definer call bug7291_0(); +create procedure bug7291_2 () sql security invoker call bug7291_0(); +grant execute on procedure bug7291_0 to user1@localhost; +grant execute on procedure bug7291_1 to user1@localhost; +grant execute on procedure bug7291_2 to user1@localhost; + +connect (user1,localhost,user1,,); +connection user1; + +call bug7291_2(); +call bug7291_1(); + +connection con1root; +drop procedure bug7291_1; +drop procedure bug7291_2; +drop procedure bug7291_0; +disconnect user1; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; +drop user user1@localhost; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 13de2090a84..d9e6163cbc7 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -3377,15 +3377,16 @@ drop procedure avg| --disable_warnings drop procedure if exists bug6129| --enable_warnings -set @@sql_mode = 'traditional'| -create procedure bug6129(mode text) - select @@sql_mode = mode| - -# 1 -call bug6129(@@sql_mode)| -set @@sql_mode = ''| -# 0 -call bug6129(@@sql_mode)| +set @old_mode= @@sql_mode; +set @@sql_mode= ""; +create procedure bug6129() + select @@sql_mode| +call bug6129()| +set @@sql_mode= "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"| +call bug6129()| +set @@sql_mode= "NO_ZERO_IN_DATE"| +call bug6129()| +set @@sql_mode=@old_mode; drop procedure bug6129| @@ -3856,6 +3857,26 @@ drop procedure bug9565| # +# BUG#9538: SProc: Creation fails if we try to SET system variable +# using @@var_name in proc +# +--disable_warnings +drop procedure if exists bug9538| +--enable_warnings +create procedure bug9538() + set @@sort_buffer_size = 1000000| + +set @x = @@sort_buffer_size| +set @@sort_buffer_size = 2000000| +select @@sort_buffer_size| +call bug9538()| +select @@sort_buffer_size| +set @@sort_buffer_size = @x| + +drop procedure bug9538| + + +# # BUG#NNNN: New bug synopsis # #--disable_warnings diff --git a/mysql-test/t/timezone_grant.test b/mysql-test/t/timezone_grant.test index f586ba0c5fe..5dbf457e1f1 100644 --- a/mysql-test/t/timezone_grant.test +++ b/mysql-test/t/timezone_grant.test @@ -61,6 +61,18 @@ select * from mysql.time_zone_name; --error 1142 select Name, convert_tz('2004-11-30 12:00:00', Name, 'UTC') from mysql.time_zone_name; +# +# Bug #9979: Use of CONVERT_TZ in multiple-table UPDATE causes bogus +# privilege error +# +drop table t1, t2; +create table t1 (a int, b datetime); +create table t2 (a int, b varchar(40)); +update t1 set b = '2005-01-01 10:00'; +update t1 set b = convert_tz(b, 'UTC', 'UTC'); +update t1 join t2 on (t1.a = t2.a) set t1.b = '2005-01-01 10:00' where t2.b = 'foo'; +update t1 join t2 on (t1.a = t2.a) set t1.b = convert_tz('2005-01-01 10:00','UTC','UTC') where t2.b = 'foo'; + # Clean-up connection default; delete from mysql.user where user like 'mysqltest\_%'; diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 8a27636ed84..f6b3c714d28 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -17,13 +17,13 @@ set @a:=0; select @a; insert into t1 values (1); select @a; -drop trigger t1.trg; +drop trigger trg; # let us test simple trigger reading some values create trigger trg before insert on t1 for each row set @a:=new.i; insert into t1 values (123); select @a; -drop trigger t1.trg; +drop trigger trg; drop table t1; @@ -40,7 +40,7 @@ end| insert into t1 (i) values (1)| insert into t1 (i,j) values (2, 3)| select * from t1| -drop trigger t1.trg| +drop trigger trg| drop table t1| delimiter ;| @@ -52,7 +52,7 @@ create trigger trg after insert on t1 for each row set @a:=""; insert into t1 values (2),(3),(4),(5); select @a; -drop trigger t1.trg; +drop trigger trg; drop table t1; # PS doesn't work with multi-row statements @@ -75,7 +75,7 @@ set @update_failed:=""| update t1 set balance=1500| select @update_failed; select * from t1| -drop trigger t1.trg| +drop trigger trg| drop table t1| delimiter ;| --enable_ps_protocol @@ -88,7 +88,7 @@ create trigger trg after update on t1 for each row set @total_change:=0; update t1 set i=3; select @total_change; -drop trigger t1.trg; +drop trigger trg; drop table t1; # Before delete trigger @@ -100,7 +100,7 @@ create trigger trg before delete on t1 for each row set @del_sum:= 0; delete from t1 where i <= 3; select @del_sum; -drop trigger t1.trg; +drop trigger trg; drop table t1; # After delete trigger. @@ -111,7 +111,7 @@ create trigger trg after delete on t1 for each row set @del:= 1; set @del:= 0; delete from t1 where i <> 0; select @del; -drop trigger t1.trg; +drop trigger trg; drop table t1; # Several triggers on one table @@ -145,9 +145,9 @@ update t1 set j= 20; select @fired; select * from t1; -drop trigger t1.trg1; -drop trigger t1.trg2; -drop trigger t1.trg3; +drop trigger trg1; +drop trigger trg2; +drop trigger trg3; drop table t1; @@ -212,7 +212,7 @@ create trigger t1_ai after insert on t1 for each row insert into t1 (id, data) values (1, "one"), (2, "two"); select * from t1; select * from t2; -drop trigger t1.t1_ai; +drop trigger t1_ai; # Trigger which uses couple of tables (and partially emulates FK constraint) delimiter |; create trigger t1_bi before insert on t1 for each row @@ -282,6 +282,7 @@ drop table t1, t2; # Test of wrong column specifiers in triggers # create table t1 (i int); +create table t3 (i int); --error 1363 create trigger trg before insert on t1 for each row set @a:= old.i; @@ -301,7 +302,7 @@ create trigger trg before update on t1 for each row set @a:=old.j; # # Let us test various trigger creation errors -# +# Also quickly test table namespace (bug#5892/6182) # --error 1146 create trigger trg before insert on t2 for each row set @a:=1; @@ -311,10 +312,14 @@ create trigger trg before insert on t1 for each row set @a:=1; create trigger trg after insert on t1 for each row set @a:=1; --error 1359 create trigger trg2 before insert on t1 for each row set @a:=1; -drop trigger t1.trg; +--error 1359 +create trigger trg before insert on t3 for each row set @a:=1; +create trigger trg2 before insert on t3 for each row set @a:=1; +drop trigger trg2; +drop trigger trg; --error 1360 -drop trigger t1.trg; +drop trigger trg; create view v1 as select * from t1; --error 1347 @@ -322,6 +327,7 @@ create trigger trg before insert on v1 for each row set @a:=1; drop view v1; drop table t1; +drop table t3; create temporary table t1 (i int); --error 1361 @@ -339,7 +345,7 @@ drop table t1; create table t1 (x1col char); create trigger tx1 before insert on t1 for each row set new.x1col = 'x'; insert into t1 values ('y'); -drop trigger t1.tx1; +drop trigger tx1; drop table t1; # @@ -355,8 +361,8 @@ create trigger trg2 after delete on t1 for each row set @del_after:= @del_after set @del_before:=0, @del_after:= 0; delete from t1; select @del_before, @del_after; -drop trigger t1.trg1; -drop trigger t1.trg2; +drop trigger trg1; +drop trigger trg2; drop table t1; # Test for bug #5859 "DROP TABLE does not drop triggers". Trigger should not @@ -378,6 +384,19 @@ create trigger trg1 before insert on t1 for each row set @a:= 1; drop database mysqltest; use test; +# Test for bug #8791 +# "Triggers: Allowed to create triggers on a subject table in a different DB". +create database mysqltest; +create table mysqltest.t1 (i int); +--error ER_TRG_IN_WRONG_SCHEMA +create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1; +use mysqltest; +--error ER_TRG_IN_WRONG_SCHEMA +create trigger test.trg1 before insert on t1 for each row set @a:= 1; +drop database mysqltest; +use test; + + # Test for bug #5860 "Multi-table UPDATE does not activate update triggers" # We will also test how delete triggers wor for multi-table DELETE. create table t1 (i int, j int default 10, k int not null, key (k)); @@ -559,7 +578,7 @@ select * from t1; # To test properly code-paths different from those that are used # in ordinary INSERT we need to drop "before insert" trigger. alter table t1 add primary key (i); -drop trigger t1.bi; +drop trigger bi; --error 1054 insert into t1 values (2, 4) on duplicate key update k= k + 10; select * from t1; @@ -589,5 +608,5 @@ drop function bug5893; --error 1305 update t1 set col2 = 4; # This should not crash server too. -drop trigger t1.t1_bu; +drop trigger t1_bu; drop table t1; diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 21789a550b9..66b3a2886b6 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -201,3 +201,16 @@ analyze table t1,t2; update t1, t2 set t1.a = t2.a where t2.b = t1.b; show warnings; drop table t1, t2; + +# +# Bug #11868 Update with subquery with ref built with a key from the updated +# table crashes server +# +create table t1(f1 int, f2 int); +create table t2(f3 int, f4 int); +create index idx on t2(f3); +insert into t1 values(1,0),(2,0); +insert into t2 values(1,1),(2,2); +UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1); +select * from t1; +drop table t1,t2; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 8eeac41efcd..449bca63f0d 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -1182,7 +1182,7 @@ create view v1 as select * from t1 where s1 <> 127 with check option; insert into v1 values (0); select * from v1; select * from t1; -drop trigger t1.t1_bi; +drop trigger t1_bi; drop view v1; drop table t1; @@ -1674,6 +1674,27 @@ select * from v1; drop view v1; # +# evaluation constant functions in WHERE (BUG#4663) +# +create table t1 (a timestamp default now()); +create table t2 (b timestamp default now()); +create view v1 as select a,b,t1.a < now() from t1,t2 where t1.a < now(); +SHOW CREATE VIEW v1; +drop view v1; +drop table t1, t2; +CREATE TABLE t1 ( a varchar(50) ); +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = CURRENT_USER(); +SHOW CREATE VIEW v1; +DROP VIEW v1; +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = VERSION(); +SHOW CREATE VIEW v1; +DROP VIEW v1; +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = DATABASE(); +SHOW CREATE VIEW v1; +DROP VIEW v1; +DROP TABLE t1; + +# # checking views after some view with error (BUG#11337) # CREATE TABLE t1 (col1 time); |