diff options
Diffstat (limited to 'mysql-test/r')
37 files changed, 327 insertions, 79 deletions
diff --git a/mysql-test/r/bdb-alter-table-1.result b/mysql-test/r/bdb-alter-table-1.result new file mode 100644 index 00000000000..095d89355ad --- /dev/null +++ b/mysql-test/r/bdb-alter-table-1.result @@ -0,0 +1,11 @@ +drop table if exists t1; +create table t1(objid BIGINT not null, tablename varchar(64), oid BIGINT not null, test BIGINT, PRIMARY KEY (objid), UNIQUE(tablename)) type=BDB; +insert into t1 values(1, 't1',4,9); +insert into t1 values(2, 'metatable',1,9); +insert into t1 values(3, 'metaindex',1,9 ); +select * from t1; +objid tablename oid test +1 t1 4 9 +2 metatable 1 9 +3 metaindex 1 9 +alter table t1 drop column test; diff --git a/mysql-test/r/bdb-alter-table-2.result b/mysql-test/r/bdb-alter-table-2.result new file mode 100644 index 00000000000..c23b5ba0031 --- /dev/null +++ b/mysql-test/r/bdb-alter-table-2.result @@ -0,0 +1,6 @@ +select * from t1; +objid tablename oid +1 t1 4 +2 metatable 1 +3 metaindex 1 +drop table t1; diff --git a/mysql-test/r/binary.result b/mysql-test/r/binary.result index 9f1c8ffac41..325accf00e7 100644 --- a/mysql-test/r/binary.result +++ b/mysql-test/r/binary.result @@ -1,3 +1,4 @@ +drop table if exists t1,t2; create table t1 (name char(20) not null, primary key (name)); create table t2 (name char(20) binary not null, primary key (name)); insert into t1 values ("å"); @@ -53,3 +54,13 @@ select * from t1 where b="hello"; a b hello hello drop table t1; +create table t1 (b char(8)); +insert into t1 values(NULL); +select b from t1 where binary b like ''; +b +select b from t1 group by binary b like ''; +b +NULL +select b from t1 having binary b like ''; +b +drop table t1; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 4b2cbab8d6e..9b56b2b5160 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -154,3 +154,8 @@ t1 CREATE TABLE `t1` ( drop table t1; create table t1 select if(1,'1','0'), month("2002-08-02"); drop table t1; +create table t1 select if('2002'='2002','Y','N'); +select * from t1; +if('2002'='2002','Y','N') +Y +drop table if exists t1; diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result index 44aef139ad2..9e375203e49 100644 --- a/mysql-test/r/delayed.result +++ b/mysql-test/r/delayed.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (a char(10), tmsp timestamp); insert into t1 set a = 1; insert delayed into t1 set a = 2; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 312e7893949..68fb8c39e6c 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -92,7 +92,8 @@ NULL NULL NULL 0 0 select id >= 0 and id <= 5 as grp,count(*) from t1 group by grp; grp count(*) -0 7 +NULL 1 +0 6 1 6 SELECT DISTINCT FACILITY FROM t1; FACILITY @@ -349,8 +350,7 @@ select distinct a from t1 where a >= '1' order by a desc; a 1 drop table t1; -CREATE TABLE t1 (email varchar(50), infoID BIGINT, dateentered -DATETIME); +CREATE TABLE t1 (email varchar(50), infoID BIGINT, dateentered DATETIME); CREATE TABLE t2 (infoID BIGINT, shipcode varchar(10)); INSERT INTO t1 (email, infoID, dateentered) VALUES ('test1@testdomain.com', 1, '2002-07-30 22:56:38'), @@ -379,3 +379,19 @@ test2@testdomain.com Z001 test2@testdomain.com R002 test3@testdomain.com Z001 drop table t1,t2; +CREATE TABLE t1 (privatemessageid int(10) unsigned NOT NULL auto_increment, folderid smallint(6) NOT NULL default '0', userid int(10) unsigned NOT NULL default '0', touserid int(10) unsigned NOT NULL default '0', fromuserid int(10) unsigned NOT NULL default '0', title varchar(250) NOT NULL default '', message mediumtext NOT NULL, dateline int(10) unsigned NOT NULL default '0', showsignature smallint(6) NOT NULL default '0', iconid smallint(5) unsigned NOT NULL default '0', messageread smallint(6) NOT NULL default '0', readtime int(10) unsigned NOT NULL default '0', receipt smallint(6) unsigned NOT NULL default '0', deleteprompt smallint(6) unsigned NOT NULL default '0', multiplerecipients smallint(6) unsigned NOT NULL default '0', PRIMARY KEY (privatemessageid), KEY userid (userid)) TYPE=MyISAM; +INSERT INTO t1 VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0); +CREATE TABLE t2 (userid int(10) unsigned NOT NULL auto_increment, usergroupid smallint(5) unsigned NOT NULL default '0', username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '', email varchar(50) NOT NULL default '', styleid smallint(5) unsigned NOT NULL default '0', parentemail varchar(50) NOT NULL default '', coppauser smallint(6) NOT NULL default '0', homepage varchar(100) NOT NULL default '', icq varchar(20) NOT NULL default '', aim varchar(20) NOT NULL default '', yahoo varchar(20) NOT NULL default '', signature mediumtext NOT NULL, adminemail smallint(6) NOT NULL default '0', showemail smallint(6) NOT NULL default '0', invisible smallint(6) NOT NULL default '0', usertitle varchar(250) NOT NULL default '', customtitle smallint(6) NOT NULL default '0', joindate int(10) unsigned NOT NULL default '0', cookieuser smallint(6) NOT NULL default '0', daysprune smallint(6) NOT NULL default '0', lastvisit int(10) unsigned NOT NULL default '0', lastactivity int(10) unsigned NOT NULL default '0', lastpost int(10) unsigned NOT NULL default '0', posts smallint(5) unsigned NOT NULL default '0', timezoneoffset varchar(4) NOT NULL default '', emailnotification smallint(6) NOT NULL default '0', buddylist mediumtext NOT NULL, ignorelist mediumtext NOT NULL, pmfolders mediumtext NOT NULL, receivepm smallint(6) NOT NULL default '0', emailonpm smallint(6) NOT NULL default '0', pmpopup smallint(6) NOT NULL default '0', avatarid smallint(6) NOT NULL default '0', avatarrevision int(6) unsigned NOT NULL default '0', options smallint(6) NOT NULL default '15', birthday date NOT NULL default '0000-00-00', maxposts smallint(6) NOT NULL default '-1', startofweek smallint(6) NOT NULL default '1', ipaddress varchar(20) NOT NULL default '', referrerid int(10) unsigned NOT NULL default '0', nosessionhash smallint(6) NOT NULL default '0', autorefresh smallint(6) NOT NULL default '-1', messagepopup tinyint(2) NOT NULL default '0', inforum smallint(5) unsigned NOT NULL default '0', ratenum smallint(5) unsigned NOT NULL default '0', ratetotal smallint(5) unsigned NOT NULL default '0', allowrate smallint(5) unsigned NOT NULL default '1', PRIMARY KEY (userid), KEY usergroupid (usergroupid), KEY username (username), KEY inforum (inforum)) TYPE=MyISAM; +INSERT INTO t2 VALUES (33,6,'Kevin','0','kevin@stileproject.com',1,'',0,'http://www.stileproject.com','','','','',1,1,0,'Administrator',0,996120694,1,-1,1030996168,1031027028,1030599436,36,'-6',0,'','','',1,0,1,0,0,15,'0000-00-00',-1,1,'64.0.0.0',0,1,-1,0,0,4,19,1); +SELECT DISTINCT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t2.userid = t1.touserid); +privatemessageid folderid userid touserid fromuserid title message dateline showsignature iconid messageread readtime receipt deleteprompt multiplerecipients userid usergroupid username password email styleid parentemail coppauser homepage icq aim yahoo signature adminemail showemail invisible usertitle customtitle joindate cookieuser daysprune lastvisit lastactivity lastpost posts timezoneoffset emailnotification buddylist ignorelist pmfolders receivepm emailonpm pmpopup avatarid avatarrevision options birthday maxposts startofweek ipaddress referrerid nosessionhash autorefresh messagepopup inforum ratenum ratetotal allowrate +128 0 33 33 8 :D 996121863 1 0 2 996122850 2 0 0 33 6 Kevin 0 kevin@stileproject.com 1 0 http://www.stileproject.com 1 1 0 Administrator 0 996120694 1 -1 1030996168 1031027028 1030599436 36 -6 0 1 0 1 0 0 15 0000-00-00 -1 1 64.0.0.0 0 1 -1 0 0 4 19 1 +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (a int primary key, b int, c int); +INSERT t1 VALUES (1,2,3); +CREATE TABLE t2 (a int primary key, b int, c int); +INSERT t2 VALUES (3,4,5); +SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c; +a b +1 4 +DROP TABLE IF EXISTS t1,t2; diff --git a/mysql-test/r/fulltext_left_join.result b/mysql-test/r/fulltext_left_join.result index 9f2ba6a5947..abc63358dbe 100644 --- a/mysql-test/r/fulltext_left_join.result +++ b/mysql-test/r/fulltext_left_join.result @@ -1,3 +1,4 @@ +drop table if exists t1, t2; CREATE TABLE t1 ( id VARCHAR(255) NOT NULL PRIMARY KEY, sujet VARCHAR(255), diff --git a/mysql-test/r/fulltext_order_by.result b/mysql-test/r/fulltext_order_by.result index 8d88b8d6ebf..c0e72ff789f 100644 --- a/mysql-test/r/fulltext_order_by.result +++ b/mysql-test/r/fulltext_order_by.result @@ -64,3 +64,18 @@ a rel 4 1 7 1 drop table t1; +CREATE TABLE t1 ( +a INT AUTO_INCREMENT PRIMARY KEY, +message CHAR(20), +FULLTEXT(message) +); +INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar"); +SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1; +a rel +1 1 +2 2 +SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a; +a rel +1 1 +2 2 +drop table t1; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 2d24b8ffd4e..c1f36283ab0 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1,3 +1,4 @@ +drop table if exists t1,t2; create table t1 (grp int, a bigint unsigned, c char(10) not null); insert into t1 values (1,1,"a"); insert into t1 values (2,2,"b"); diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 8c991dee8d8..ba33ee0831d 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -1,3 +1,4 @@ +drop table if exists t1; CREATE TABLE t1 (field char(1)); INSERT INTO t1 VALUES ('A'),(NULL); SELECT * from t1 WHERE field IN (NULL); diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index dd5e5191536..69e37d7b911 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -223,3 +223,8 @@ SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugs CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugstatus,submitter) Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4 drop table t1; +CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) TYPE=MyISAM; +INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); +SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); +1 +DROP TABLE t1; diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result new file mode 100644 index 00000000000..a1a0c91a575 --- /dev/null +++ b/mysql-test/r/grant.result @@ -0,0 +1,60 @@ +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +flush privileges; +grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA"; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +grant delete on mysqltest.* to mysqltest_1@localhost; +select * from mysql.user where user="mysqltest_1"; +Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections +localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N SPECIFIED EDH-RSA-DES-CBC3-SHA 0 0 0 +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT, DELETE ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +revoke delete on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +grant select on mysqltest.* to mysqltest_1@localhost require NONE; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "MySQL AB"; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA' +delete from mysql.user where user='mysqltest_1'; +flush privileges; +grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +flush privileges; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION; +flush privileges; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +flush privileges; diff --git a/mysql-test/r/handler.result b/mysql-test/r/handler.result index d495dde75bf..35765c48049 100644 --- a/mysql-test/r/handler.result +++ b/mysql-test/r/handler.result @@ -134,6 +134,6 @@ handler t2 read next; a b 19 fff handler t2 read last; -You have an error in your SQL syntax near '' at line 1 +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 '' at line 1 handler t2 close; drop table if exists t1; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 704e7a45e59..34c2eef3fea 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -455,7 +455,7 @@ Duplicate entry 'test2' for key 2 select * from t1; id ggid email passwd 1 this will work -4 test2 this will work +3 test2 this will work select * from t1 where id=1; id ggid email passwd 1 this will work @@ -987,7 +987,7 @@ create table t1 (id int unsigned not null auto_increment, code tinyint unsigned BEGIN; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT @@tx_isolation,@@global.tx_isolation; -@@tx_isolation @@tx_isolation +@@tx_isolation @@global.tx_isolation SERIALIZABLE READ-COMMITTED insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); select id, code, name from t1 order by id; diff --git a/mysql-test/r/innodb_handler.result b/mysql-test/r/innodb_handler.result index 73368c932af..a55114a0e3d 100644 --- a/mysql-test/r/innodb_handler.result +++ b/mysql-test/r/innodb_handler.result @@ -134,6 +134,6 @@ handler t2 read next; a b 19 fff handler t2 read last; -You have an error in your SQL syntax near '' at line 1 +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 '' at line 1 handler t2 close; drop table if exists t1; diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index b27cbb46210..2ffa9d88618 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -41,6 +41,14 @@ a t>0 c i 5 0 a NULL 6 1 hello NULL drop table t1; +create table t1 (sid char(20), id int(2) NOT NULL auto_increment, key(sid, id)); +insert into t1 values ('skr',NULL),('skr',NULL),('test',NULL); +select * from t1; +sid id +skr 1 +skr 2 +test 1 +drop table t1; drop database if exists foo; create database foo; use foo; diff --git a/mysql-test/r/key_primary.result b/mysql-test/r/key_primary.result index 10771a134fc..87289f1cf54 100644 --- a/mysql-test/r/key_primary.result +++ b/mysql-test/r/key_primary.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (t1 char(3) primary key); insert into t1 values("ABC"); insert into t1 values("ABA"); diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index 6fe8cc71185..20bc9b9572f 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1(n int); insert into t1 values (1); lock tables t1 write; diff --git a/mysql-test/r/lowercase_table.result b/mysql-test/r/lowercase_table.result new file mode 100644 index 00000000000..1caaf317c96 --- /dev/null +++ b/mysql-test/r/lowercase_table.result @@ -0,0 +1,15 @@ +drop table if exists t1,t2,t3; +create table T1 (id int primary key, Word varchar(40) not null, Index(Word)); +INSERT INTO T1 VALUES (1, 'a'), (2, 'b'), (3, 'c'); +SELECT * FROM t1; +id Word +1 a +2 b +3 c +RENAME TABLE T1 TO T2; +ALTER TABLE T2 ADD new_col int not null; +ALTER TABLE T2 RENAME T3; +show tables like 't_'; +Tables_in_test (t_) +t3 +drop table t3; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index d1de03712ba..f66d211a987 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -100,3 +100,17 @@ CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES (1), (2), (3); +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +DROP TABLE t1; +CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), KEY t1 (a, b, c)); +Specified key was too long. Max key length is 500 +CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255)); +ALTER TABLE t1 ADD INDEX t1 (a, b, c); +Specified key was too long. Max key length is 500 +DROP TABLE t1; diff --git a/mysql-test/r/odbc.result b/mysql-test/r/odbc.result index c696cf94e9d..5aa163a663e 100644 --- a/mysql-test/r/odbc.result +++ b/mysql-test/r/odbc.result @@ -1,6 +1,7 @@ select {fn length("hello")}, { date "1997-10-20" }; {fn length("hello")} 1997-10-20 5 1997-10-20 +drop table if exists t1; create table t1 (a int not null auto_increment,b int not null,primary key (a,b)); insert into t1 SET A=NULL,B=1; insert into t1 SET a=null,b=2; diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 9bc716ee8b9..9ac88b42436 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -471,3 +471,38 @@ table type possible_keys key key_len ref rows Extra t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort t3 eq_ref PRIMARY PRIMARY 2 t1.skr 1 where used drop table t1,t2,t3; +CREATE TABLE t1 ( +`titre` char(80) NOT NULL default '', +`numeropost` mediumint(8) unsigned NOT NULL auto_increment, +`date` datetime NOT NULL default '0000-00-00 00:00:00', +`auteur` char(35) NOT NULL default '', +`icone` tinyint(2) unsigned NOT NULL default '0', +`lastauteur` char(35) NOT NULL default '', +`nbrep` smallint(6) unsigned NOT NULL default '0', +`dest` char(35) NOT NULL default '', +`lu` tinyint(1) unsigned NOT NULL default '0', +`vue` mediumint(8) unsigned NOT NULL default '0', +`ludest` tinyint(1) unsigned NOT NULL default '0', +`ouvert` tinyint(1) unsigned NOT NULL default '1', +PRIMARY KEY (`numeropost`), +KEY `date` (`date`), +KEY `dest` (`dest`,`ludest`), +KEY `auteur` (`auteur`,`lu`), +KEY `auteur_2` (`auteur`,`date`), +KEY `dest_2` (`dest`,`date`) +) CHECKSUM=1; +CREATE TABLE t2 ( +`numeropost` mediumint(8) unsigned NOT NULL default '0', +`pseudo` char(35) NOT NULL default '', +PRIMARY KEY (`numeropost`,`pseudo`), +KEY `pseudo` (`pseudo`) +); +INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug'); +INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug'); +SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; +titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest +test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug +SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; +titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest +test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug +drop table t1,t2; diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result new file mode 100644 index 00000000000..8b50f9a92e8 --- /dev/null +++ b/mysql-test/r/repair.result @@ -0,0 +1,7 @@ +drop table if exists t1; +create table t1 SELECT 1,"table 1"; +repair table t1 use_frm; +Table Op Msg_type Msg_text +test.t1 repair warning Number of rows changed from 0 to 1 +test.t1 repair status OK +drop table if exists t1; diff --git a/mysql-test/r/rpl_flush_log_loop.result b/mysql-test/r/rpl_flush_log_loop.result new file mode 100644 index 00000000000..da2930f30c3 --- /dev/null +++ b/mysql-test/r/rpl_flush_log_loop.result @@ -0,0 +1,17 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +change master to master_host='127.0.0.1',master_user='root', +master_password='',master_port=MASTER_PORT; +slave start; +slave stop; +change master to master_host='127.0.0.1',master_user='root', +master_password='',master_port=SLAVE_PORT; +slave start; +flush logs; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root SLAVE_PORT 60 slave-bin.001 79 relay-log.001 119 slave-bin.001 Yes Yes 0 0 79 119 diff --git a/mysql-test/r/rpl_log.result b/mysql-test/r/rpl_log.result index b5719c920e6..01efce20e1d 100644 --- a/mysql-test/r/rpl_log.result +++ b/mysql-test/r/rpl_log.result @@ -8,7 +8,6 @@ slave stop; reset master; reset slave; reset master; -drop table if exists t1; create table t1(n int not null auto_increment primary key); insert into t1 values (NULL); drop table t1; @@ -37,6 +36,9 @@ show binlog events from 79 limit 2,1; Log_name Pos Event_type Server_id Orig_log_pos Info master-bin.001 200 Query 1 200 use `test`; insert into t1 values (NULL) flush logs; +slave start; +flush logs; +slave stop; create table t1 (n int); insert into t1 values (1); drop table t1; @@ -52,7 +54,6 @@ master-bin.001 386 Create_file 1 386 db=test;table=t1;file_id=1;block_len=81 master-bin.001 556 Exec_load 1 556 ;file_id=1 master-bin.001 579 Query 1 579 use `test`; drop table t1 master-bin.001 627 Rotate 1 627 master-bin.002;pos=4 -master-bin.001 668 Stop 1 668 show binlog events in 'master-bin.002'; Log_name Pos Event_type Server_id Orig_log_pos Info master-bin.002 4 Query 1 4 use `test`; create table t1 (n int) @@ -68,32 +69,24 @@ Log_name slave-bin.001 slave-bin.002 show binlog events in 'slave-bin.001' from 4; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.001 4 Start 2 4 Server ver: VERSION, Binlog ver: 3 +slave-bin.001 79 Query 1 79 use test; create table t1(n int not null auto_increment primary key) +slave-bin.001 172 Intvar 1 200 INSERT_ID=1 +slave-bin.001 200 Query 1 200 use test; insert into t1 values (NULL) +slave-bin.001 263 Query 1 263 use test; drop table t1 +slave-bin.001 311 Query 1 311 use test; create table t1 (word char(20) not null) +slave-bin.001 386 Create_file 1 386 db=test;table=t1;file_id=1;block_len=81 +slave-bin.001 565 Exec_load 1 556 ;file_id=1 +slave-bin.001 588 Query 1 579 use test; drop table t1 +slave-bin.001 636 Rotate 2 636 slave-bin.002;pos=4 show binlog events in 'slave-bin.002' from 4; Log_name Pos Event_type Server_id Orig_log_pos Info -slave-bin.002 4 Slave 2 627 host=127.0.0.1,port=MASTER_PORT,log=master-bin.002,pos=4 slave-bin.002 57 Query 1 4 use `test`; create table t1 (n int) slave-bin.002 115 Query 1 62 use `test`; insert into t1 values (1) slave-bin.002 175 Query 1 122 use `test`; drop table t1 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.002 170 slave-relay-bin.002 916 master-bin.002 Yes Yes 0 0 170 920 -show new master for slave with master_log_file='master-bin.001' and -master_log_pos=4 and master_server_id=1; -Log_name Log_pos -slave-bin.001 132 -show new master for slave with master_log_file='master-bin.001' and -master_log_pos=79 and master_server_id=1; -Log_name Log_pos -slave-bin.001 225 -show new master for slave with master_log_file='master-bin.001' and -master_log_pos=311 and master_server_id=1; -Log_name Log_pos -slave-bin.001 439 -show new master for slave with master_log_file='master-bin.002' and -master_log_pos=4 and master_server_id=1; -Log_name Log_pos -slave-bin.002 57 -show new master for slave with master_log_file='master-bin.002' and -master_log_pos=122 and master_server_id=1; -Log_name Log_pos -slave-bin.002 223 +127.0.0.1 root MASTER_PORT 1 master-bin.002 170 slave-relay-bin.002 957 master-bin.002 Yes Yes 0 0 170 961 +show binlog events in 'slave-bin.005' from 4; +Error when executing command SHOW BINLOG EVENTS: Could not find target log diff --git a/mysql-test/r/rpl_magic.result b/mysql-test/r/rpl_magic.result deleted file mode 100644 index 743bef4a053..00000000000 --- a/mysql-test/r/rpl_magic.result +++ /dev/null @@ -1,37 +0,0 @@ -slave stop; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -reset master; -reset slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -slave start; -drop table if exists t1; -create table t1 ( n int); -insert into t1 values (1),(2),(3),(4); -insert into t1 values(5); -select * from t1; -n -1 -2 -3 -4 -5 -select * from t1; -n -1 -2 -3 -4 -select * from t1; -n -1 -2 -3 -4 -select * from t1; -n -1 -2 -3 -4 -5 -drop table t1; diff --git a/mysql-test/r/rpl_redirect.result b/mysql-test/r/rpl_redirect.result new file mode 100644 index 00000000000..6103a075684 --- /dev/null +++ b/mysql-test/r/rpl_redirect.result @@ -0,0 +1,43 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +SHOW SLAVE STATUS; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +SHOW SLAVE HOSTS; +Server_id Host Port Rpl_recovery_rank Master_id +2 127.0.0.1 SLAVE_PORT 2 1 +drop table if exists t1; +create table t1 ( n int); +insert into t1 values (1),(2),(3),(4); +insert into t1 values(5); +select * from t1; +n +1 +2 +3 +4 +5 +select * from t1; +n +1 +2 +3 +4 +select * from t1; +n +1 +2 +3 +4 +select * from t1; +n +1 +2 +3 +4 +5 +drop table t1; +drop table t1; diff --git a/mysql-test/r/rpl_rotate_logs.result b/mysql-test/r/rpl_rotate_logs.result index d440e157ed4..63d5b0b63e1 100644 --- a/mysql-test/r/rpl_rotate_logs.result +++ b/mysql-test/r/rpl_rotate_logs.result @@ -2,12 +2,10 @@ slave start; Could not initialize master info structure, check permisions on master.info slave start; Could not initialize master info structure, check permisions on master.info -change master to master_host='127.0.0.1',master_port=MASTER_PORT, -master_user='root'; +change master to master_host='127.0.0.1',master_port=MASTER_PORT, master_user='root'; Could not initialize master info reset slave; -change master to master_host='127.0.0.1',master_port=MASTER_PORT, -master_user='root'; +change master to master_host='127.0.0.1',master_port=MASTER_PORT, master_user='root'; reset master; slave start; drop table if exists t1, t2, t3, t4; @@ -82,4 +80,4 @@ select count(*) from t3 where n >= 4; count(*) 100 unlock tables; -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index bdef42aa5a8..4ccc40d3bd6 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -1718,7 +1718,7 @@ insert into tmp select * from t3; insert into t3 select * from tmp; alter table t3 add t2nr int not null auto_increment primary key first; drop table tmp; -SET OPTION SQL_BIG_TABLES=1; +SET SQL_BIG_TABLES=1; select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; namn Abraham Abraham @@ -1731,7 +1731,7 @@ ammonium ammonium analyzable analyzable animals animals animized animized -SET OPTION SQL_BIG_TABLES=0; +SET SQL_BIG_TABLES=0; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; concat(fld3," ",fld3) Abraham Abraham @@ -1768,7 +1768,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET OPTION SQL_BIG_TABLES=1; +SET SQL_BIG_TABLES=1; select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; fld3 count(*) affixed 1 @@ -1781,7 +1781,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET OPTION SQL_BIG_TABLES=0; +SET SQL_BIG_TABLES=0; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; fld3 repeat("a",length(fld3)) count(*) circus aaaaaa 1 @@ -1809,6 +1809,18 @@ explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2 table type possible_keys key key_len ref rows Extra t2 ALL fld1 NULL NULL NULL 1199 where used; Using temporary; Using filesort t3 eq_ref PRIMARY PRIMARY 4 t2.fld1 1 where used; Using index +explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; +table type possible_keys key key_len ref rows Extra +t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort +t3 ref period period 4 t1.period 4181 +explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; +table type possible_keys key key_len ref rows Extra +t3 index period period 4 NULL 41810 +t1 ref period period 4 t3.period 4181 +explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; +table type possible_keys key key_len ref rows Extra +t1 index period period 4 NULL 41810 +t3 ref period period 4 t1.period 4181 select period from t1; period 9410 diff --git a/mysql-test/r/truncate.result b/mysql-test/r/truncate.result index 44dacdd9bf1..ad390c9fa92 100644 --- a/mysql-test/r/truncate.result +++ b/mysql-test/r/truncate.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (a integer, b integer,c1 CHAR(10)); insert into t1 (a) values (1),(2); truncate table t1; diff --git a/mysql-test/r/type_set.result b/mysql-test/r/type_set.result index 38c7b66dcb8..b0ea1b69e59 100644 --- a/mysql-test/r/type_set.result +++ b/mysql-test/r/type_set.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (a set (' ','a','b') not null); show create table t1; Table Create Table diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index ff700f456ef..bacc3ec0176 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (t time); insert into t1 values("10:22:33"),("12:34:56.78"),(10),(1234),(123456.78),(1234559.99),("1"),("1:23"),("1:23:45"), ("10.22"), ("-10 1:22:33.45"),("20 10:22:33"),("1999-02-03 20:33:34"); insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32"); diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index ab53d27e8f3..bd5e9f04992 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -1,3 +1,4 @@ +drop table if exists t1; CREATE TABLE t1 ( t timestamp); SET TIMESTAMP=1234; insert into t1 values(NULL); diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result index c892d575ca0..25ddda88f48 100644 --- a/mysql-test/r/type_year.result +++ b/mysql-test/r/type_year.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (y year,y2 year(2)); insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69); select * from t1; diff --git a/mysql-test/r/varbinary.result b/mysql-test/r/varbinary.result index a63db14b736..4eb34ebfd63 100644 --- a/mysql-test/r/varbinary.result +++ b/mysql-test/r/varbinary.result @@ -7,6 +7,7 @@ select 0x31+1,concat(0x31)+1,-0xf; select x'31',X'ffff'+0; x'31' X'ffff'+0 1 65535 +drop table if exists t1; create table t1 (ID int(8) unsigned zerofill not null auto_increment,UNIQ bigint(21) unsigned zerofill not null,primary key (ID),unique (UNIQ) ); insert into t1 set UNIQ=0x38afba1d73e6a18a; insert into t1 set UNIQ=123; @@ -15,7 +16,7 @@ table type possible_keys key key_len ref rows Extra t1 const UNIQ UNIQ 8 const 1 drop table t1; select x'hello'; -You have an error in your SQL syntax near 'x'hello'' at line 1 +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 'x'hello'' at line 1 select 0xfg; Unknown column '0xfg' in 'field list' create table t1 select 1 as x, 2 as xx; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 516204028fe..f708ddd2ee7 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -65,7 +65,7 @@ Variable_name Value max_join_size 4294967295 set @@max_join_size=1000, @@global.max_join_size=2000; select @@local.max_join_size, @@global.max_join_size; -@@max_join_size @@max_join_size +@@session.max_join_size @@global.max_join_size 1000 2000 select @@identity, length(@@version)>0; @@identity length(@@version)>0 diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index d77e98cc635..e5713718db0 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (a int); insert into t1 values (1); insert into t1 values ("hej"); |