diff options
Diffstat (limited to 'mysql-test')
102 files changed, 2370 insertions, 231 deletions
diff --git a/mysql-test/Makefile.am b/mysql-test/Makefile.am index 9426b20d09c..b2e86d8c00e 100644 --- a/mysql-test/Makefile.am +++ b/mysql-test/Makefile.am @@ -20,12 +20,14 @@ if HAVE_NDBCLUSTER_DB SUBDIRS = ndb DIST_SUBDIRS=ndb +USE_NDBCLUSTER=\"--ndbcluster\" else # If one uses automake conditionals, automake will automatically # include all possible branches to DIST_SUBDIRS goal. # Reset DIST_SUBDIRS if we don't use NDB SUBDIRS= DIST_SUBDIRS= +USE_NDBCLUSTER=\"\" endif benchdir_root= $(prefix) @@ -98,6 +100,7 @@ SUFFIXES = .sh -e 's!@''MYSQL_TCP_PORT''@!@MYSQL_TCP_PORT@!' \ -e 's!@''MYSQL_NO_DASH_VERSION''@!@MYSQL_NO_DASH_VERSION@!' \ -e 's!@''MYSQL_SERVER_SUFFIX''@!@MYSQL_SERVER_SUFFIX@!' \ + -e 's!@''USE_NDBCLUSTER''@!$(USE_NDBCLUSTER)!g' \ $< > $@-t @CHMOD@ +x $@-t @MV@ $@-t $@ diff --git a/mysql-test/include/have_cp1250_ch.inc b/mysql-test/include/have_cp1250_ch.inc new file mode 100644 index 00000000000..eec5d69fbd6 --- /dev/null +++ b/mysql-test/include/have_cp1250_ch.inc @@ -0,0 +1,4 @@ +-- require r/have_cp1250_ch.require +disable_query_log; +show collation like "cp1250_czech_cs"; +enable_query_log; diff --git a/mysql-test/include/have_multi_ndb.inc b/mysql-test/include/have_multi_ndb.inc new file mode 100644 index 00000000000..ec1a93311fb --- /dev/null +++ b/mysql-test/include/have_multi_ndb.inc @@ -0,0 +1,28 @@ +# Setup connections to both MySQL Servers connected to the cluster +connect (server1,127.0.0.1,root,,test,$MASTER_MYPORT,); +connect (server2,127.0.0.1,root,,test,$MASTER_MYPORT1,); + +# Check that server1 has NDB support +connection server1; +disable_query_log; +--disable_warnings +drop table if exists t1, t2; +--enable_warnings +flush tables; +@r/have_ndb.require show variables like "have_ndbcluster"; +# @r/server_id.require show variables like "server_id"; +enable_query_log; + +# Check that server2 has NDB support +connection server2; +disable_query_log; +--disable_warnings +drop table if exists t1, t2; +--enable_warnings +flush tables; +@r/have_ndb.require show variables like "have_ndbcluster"; +# @r/server_id1.require show variables like "server_id"; +enable_query_log; + +# Set the default connection to 'server1' +connection server1; diff --git a/mysql-test/lib/init_db.sql b/mysql-test/lib/init_db.sql index f42f7ca6b5f..902af0b0842 100644 --- a/mysql-test/lib/init_db.sql +++ b/mysql-test/lib/init_db.sql @@ -1,54 +1,470 @@ USE mysql; -CREATE TABLE db (Host char(60) binary DEFAULT '' NOT NULL,Db char(64) binary DEFAULT '' NOT NULL,User char(16) binary DEFAULT '' NOT NULL,Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,Grant_priv enum('N','Y') DEFAULT 'N' NOT NULL,References_priv enum('N','Y') DEFAULT 'N' NOT NULL,Index_priv enum('N','Y') DEFAULT 'N' NOT NULL,Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL,Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL,PRIMARY KEY Host (Host,Db,User),KEY User (User)) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; +CREATE TABLE db ( + Host char(60) binary DEFAULT '' NOT NULL, + Db char(64) binary DEFAULT '' NOT NULL, + User char(16) binary DEFAULT '' NOT NULL, + Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + PRIMARY KEY Host (Host,Db,User), + KEY User (User) +) engine=MyISAM +CHARACTER SET utf8 COLLATE utf8_bin +comment='Database privileges'; + -INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y'); +INSERT INTO db VALUES ('%','test' ,'','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y'); INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y'); -CREATE TABLE host (Host char(60) binary DEFAULT '' NOT NULL,Db char(64) binary DEFAULT '' NOT NULL,Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,Grant_priv enum('N','Y') DEFAULT 'N' NOT NULL,References_priv enum('N','Y') DEFAULT 'N' NOT NULL,Index_priv enum('N','Y') DEFAULT 'N' NOT NULL,Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL,Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL,PRIMARY KEY Host (Host,Db)) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; +CREATE TABLE host ( + Host char(60) binary DEFAULT '' NOT NULL, + Db char(64) binary DEFAULT '' NOT NULL, + Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + PRIMARY KEY Host (Host,Db) +) engine=MyISAM +CHARACTER SET utf8 COLLATE utf8_bin +comment='Host privileges; Merged with database privileges'; -CREATE TABLE user (Host char(60) binary DEFAULT '' NOT NULL,User char(16) binary DEFAULT '' NOT NULL,Password char(41) binary DEFAULT '' NOT NULL,Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,Reload_priv enum('N','Y') DEFAULT 'N' NOT NULL,Shutdown_priv enum('N','Y') DEFAULT 'N' NOT NULL,Process_priv enum('N','Y') DEFAULT 'N' NOT NULL,File_priv enum('N','Y') DEFAULT 'N' NOT NULL,Grant_priv enum('N','Y') DEFAULT 'N' NOT NULL,References_priv enum('N','Y') DEFAULT 'N' NOT NULL,Index_priv enum('N','Y') DEFAULT 'N' NOT NULL,Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL,Show_db_priv enum('N','Y') DEFAULT 'N' NOT NULL,Super_priv enum('N','Y') DEFAULT 'N' NOT NULL,Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL,Execute_priv enum('N','Y') DEFAULT 'N' NOT NULL,Repl_slave_priv enum('N','Y') DEFAULT 'N' NOT NULL,Repl_client_priv enum('N','Y') DEFAULT 'N' NOT NULL,ssl_type enum('','ANY','X509', 'SPECIFIED') DEFAULT '' NOT NULL,ssl_cipher BLOB NOT NULL,x509_issuer BLOB NOT NULL,x509_subject BLOB NOT NULL,max_questions int(11) unsigned DEFAULT 0 NOT NULL,max_updates int(11) unsigned DEFAULT 0 NOT NULL,max_connections int(11) unsigned DEFAULT 0 NOT NULL,PRIMARY KEY Host (Host,User)) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; -INSERT INTO user VALUES ('%','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); -INSERT INTO user VALUES ('localhost','','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); -INSERT INTO user VALUES ('%','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0); +CREATE TABLE user ( + Host char(60) binary DEFAULT '' NOT NULL, + User char(16) binary DEFAULT '' NOT NULL, + Password char(41) binary DEFAULT '' NOT NULL, + Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, + ssl_cipher BLOB NOT NULL, + x509_issuer BLOB NOT NULL, + x509_subject BLOB NOT NULL, + max_questions int(11) unsigned DEFAULT 0 NOT NULL, + max_updates int(11) unsigned DEFAULT 0 NOT NULL, + max_connections int(11) unsigned DEFAULT 0 NOT NULL, + PRIMARY KEY Host (Host,User) +) engine=MyISAM +CHARACTER SET utf8 COLLATE utf8_bin +comment='Users and global privileges'; -CREATE TABLE func (name char(64) binary DEFAULT '' NOT NULL,ret tinyint(1) DEFAULT '0' NOT NULL,dl char(128) DEFAULT '' NOT NULL,type enum ('function','aggregate') NOT NULL,PRIMARY KEY (name)) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='User defined functions'; -CREATE TABLE tables_priv (Host char(60) binary DEFAULT '' NOT NULL,Db char(64) binary DEFAULT '' NOT NULL,User char(16) binary DEFAULT '' NOT NULL,Table_name char(64) binary DEFAULT '' NOT NULL,Grantor char(77) DEFAULT '' NOT NULL,Timestamp timestamp(14),Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') DEFAULT '' NOT NULL,Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL,PRIMARY KEY (Host,Db,User,Table_name),KEY Grantor (Grantor)) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Table privileges'; +INSERT INTO user VALUES ('localhost' ,'root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); +INSERT INTO user VALUES ('@HOSTNAME@%','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); +REPLACE INTO user VALUES ('127.0.0.1' ,'root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); +INSERT INTO user (host,user) VALUES ('localhost',''); +INSERT INTO user (host,user) VALUES ('@HOSTNAME@%',''); -CREATE TABLE columns_priv (Host char(60) binary DEFAULT '' NOT NULL,Db char(64) binary DEFAULT '' NOT NULL,User char(16) binary DEFAULT '' NOT NULL,Table_name char(64) binary DEFAULT '' NOT NULL,Column_name char(64) binary DEFAULT '' NOT NULL,Timestamp timestamp(14),Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL,PRIMARY KEY (Host,Db,User,Table_name,Column_name)) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Column privileges'; -CREATE TABLE help_topic (help_topic_id int unsigned not null,name varchar(64) not null,help_category_id smallint unsigned not null,description text not null,example text not null,url varchar(128) not null,primary key (help_topic_id),unique index (name)) engine=MyISAM CHARACTER SET utf8 comment='help topics'; - -CREATE TABLE help_category (help_category_id smallint unsigned not null,name varchar(64) not null,parent_category_id smallint unsigned null,url varchar(128) not null,primary key (help_category_id),unique index (name)) engine=MyISAM CHARACTER SET utf8 comment='help categories'; +CREATE TABLE func ( + name char(64) binary DEFAULT '' NOT NULL, + ret tinyint(1) DEFAULT '0' NOT NULL, + dl char(128) DEFAULT '' NOT NULL, + type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL, + PRIMARY KEY (name) +) engine=MyISAM +CHARACTER SET utf8 COLLATE utf8_bin +comment='User defined functions'; + + +CREATE TABLE tables_priv ( + Host char(60) binary DEFAULT '' NOT NULL, + Db char(64) binary DEFAULT '' NOT NULL, + User char(16) binary DEFAULT '' NOT NULL, + Table_name char(64) binary DEFAULT '' NOT NULL, + Grantor char(77) DEFAULT '' NOT NULL, + Timestamp timestamp(14), + Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') COLLATE utf8_general_ci DEFAULT '' NOT NULL, + Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, + PRIMARY KEY (Host,Db,User,Table_name),KEY Grantor (Grantor) +) engine=MyISAM +CHARACTER SET utf8 COLLATE utf8_bin +comment='Table privileges'; + -CREATE TABLE help_keyword (help_keyword_id int unsigned not null,name varchar(64) not null,primary key (help_keyword_id),unique index (name)) engine=MyISAM CHARACTER SET utf8 comment='help keywords'; +CREATE TABLE columns_priv ( + Host char(60) binary DEFAULT '' NOT NULL, + Db char(64) binary DEFAULT '' NOT NULL, + User char(16) binary DEFAULT '' NOT NULL, + Table_name char(64) binary DEFAULT '' NOT NULL, + Column_name char(64) binary DEFAULT '' NOT NULL, + Timestamp timestamp(14), + Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, + PRIMARY KEY (Host,Db,User,Table_name,Column_name) +) engine=MyISAM +CHARACTER SET utf8 COLLATE utf8_bin +comment='Column privileges'; -CREATE TABLE help_relation (help_topic_id int unsigned not null references help_topic,help_keyword_id int unsigned not null references help_keyword,primary key (help_keyword_id, help_topic_id)) engine=MyISAM CHARACTER SET utf8 comment='keyword-topic relation'; -CREATE TABLE time_zone_name (Name char(64) NOT NULL,Time_zone_id int unsigned NOT NULL,PRIMARY KEY Name (Name)) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; +CREATE TABLE help_topic ( + help_topic_id int unsigned not null, + name varchar(64) not null, + help_category_id smallint unsigned not null, + description text not null, + example text not null, + url varchar(128) not null, + primary key (help_topic_id), + unique index (name) +) engine=MyISAM +CHARACTER SET utf8 +comment='help topics'; + -INSERT INTO time_zone_name (Name, Time_Zone_id) VALUES ('MET', 1), ('UTC', 2), ('Universal', 2), ('Europe/Moscow',3), ('leap/Europe/Moscow',4), ('Japan', 5); +CREATE TABLE help_category ( + help_category_id smallint unsigned not null, + name varchar(64) not null, + parent_category_id smallint unsigned null, + url varchar(128) not null, + primary key (help_category_id),unique index (name) +) engine=MyISAM +CHARACTER SET utf8 +comment='help categories'; + + +CREATE TABLE help_keyword ( + help_keyword_id int unsigned not null, + name varchar(64) not null, + primary key (help_keyword_id),unique index (name) +) engine=MyISAM +CHARACTER SET utf8 +comment='help keywords'; + +CREATE TABLE help_relation ( + help_topic_id int unsigned not null references help_topic, + help_keyword_id int unsigned not null references help_keyword, + primary key (help_keyword_id, help_topic_id) +) engine=MyISAM +CHARACTER SET utf8 +comment='keyword-topic relation'; + + +CREATE TABLE time_zone_name ( + Name char(64) NOT NULL, + Time_zone_id int unsigned NOT NULL, + PRIMARY KEY Name (Name) +) engine=MyISAM +CHARACTER SET utf8 +comment='Time zone names'; -CREATE TABLE time_zone (Time_zone_id int unsigned NOT NULL auto_increment,Use_leap_seconds enum('Y','N') DEFAULT 'N' NOT NULL,PRIMARY KEY TzId (Time_zone_id)) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; -INSERT INTO time_zone (Time_zone_id, Use_leap_seconds) VALUES (1,'N'), (2,'N'), (3,'N'), (4,'Y'), (5,'N'); +INSERT INTO time_zone_name (Name, Time_Zone_id) VALUES + ('MET', 1), ('UTC', 2), ('Universal', 2), + ('Europe/Moscow',3), ('leap/Europe/Moscow',4), + ('Japan', 5); -CREATE TABLE time_zone_transition (Time_zone_id int unsigned NOT NULL,Transition_time bigint signed NOT NULL,Transition_type_id int unsigned NOT NULL,PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time)) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; +CREATE TABLE time_zone ( + Time_zone_id int unsigned NOT NULL auto_increment, + Use_leap_seconds enum('Y','N') DEFAULT 'N' NOT NULL, + PRIMARY KEY TzId (Time_zone_id) +) engine=MyISAM +CHARACTER SET utf8 +comment='Time zones'; + -INSERT INTO time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) VALUES (1, -1693706400, 0) ,(1, -1680483600, 1),(1, -1663455600, 2) ,(1, -1650150000, 3),(1, -1632006000, 2) ,(1, -1618700400, 3),(1, -938905200, 2) ,(1, -857257200, 3),(1, -844556400, 2) ,(1, -828226800, 3),(1, -812502000, 2) ,(1, -796777200, 3),(1, 228877200, 2) ,(1, 243997200, 3),(1, 260326800, 2) ,(1, 276051600, 3),(1, 291776400, 2) ,(1, 307501200, 3),(1, 323830800, 2) ,(1, 338950800, 3),(1, 354675600, 2) ,(1, 370400400, 3),(1, 386125200, 2) ,(1, 401850000, 3),(1, 417574800, 2) ,(1, 433299600, 3),(1, 449024400, 2) ,(1, 465354000, 3),(1, 481078800, 2) ,(1, 496803600, 3),(1, 512528400, 2) ,(1, 528253200, 3),(1, 543978000, 2) ,(1, 559702800, 3),(1, 575427600, 2) ,(1, 591152400, 3),(1, 606877200, 2) ,(1, 622602000, 3),(1, 638326800, 2) ,(1, 654656400, 3),(1, 670381200, 2) ,(1, 686106000, 3),(1, 701830800, 2) ,(1, 717555600, 3),(1, 733280400, 2) ,(1, 749005200, 3),(1, 764730000, 2) ,(1, 780454800, 3),(1, 796179600, 2) ,(1, 811904400, 3),(1, 828234000, 2) ,(1, 846378000, 3),(1, 859683600, 2) ,(1, 877827600, 3),(1, 891133200, 2) ,(1, 909277200, 3),(1, 922582800, 2) ,(1, 941331600, 3),(1, 954032400, 2) ,(1, 972781200, 3),(1, 985482000, 2) ,(1, 1004230800, 3),(1, 1017536400, 2) ,(1, 1035680400, 3),(1, 1048986000, 2) ,(1, 1067130000, 3),(1, 1080435600, 2) ,(1, 1099184400, 3),(1, 1111885200, 2) ,(1, 1130634000, 3),(1, 1143334800, 2) ,(1, 1162083600, 3),(1, 1174784400, 2) ,(1, 1193533200, 3),(1, 1206838800, 2) ,(1, 1224982800, 3),(1, 1238288400, 2) ,(1, 1256432400, 3),(1, 1269738000, 2) ,(1, 1288486800, 3),(1, 1301187600, 2) ,(1, 1319936400, 3),(1, 1332637200, 2) ,(1, 1351386000, 3),(1, 1364691600, 2) ,(1, 1382835600, 3),(1, 1396141200, 2) ,(1, 1414285200, 3),(1, 1427590800, 2) ,(1, 1445734800, 3),(1, 1459040400, 2) ,(1, 1477789200, 3),(1, 1490490000, 2) ,(1, 1509238800, 3),(1, 1521939600, 2) ,(1, 1540688400, 3),(1, 1553994000, 2) ,(1, 1572138000, 3),(1, 1585443600, 2) ,(1, 1603587600, 3),(1, 1616893200, 2) ,(1, 1635642000, 3),(1, 1648342800, 2) ,(1, 1667091600, 3),(1, 1679792400, 2) ,(1, 1698541200, 3),(1, 1711846800, 2) ,(1, 1729990800, 3),(1, 1743296400, 2) ,(1, 1761440400, 3),(1, 1774746000, 2) ,(1, 1792890000, 3),(1, 1806195600, 2) ,(1, 1824944400, 3),(1, 1837645200, 2) ,(1, 1856394000, 3),(1, 1869094800, 2) ,(1, 1887843600, 3),(1, 1901149200, 2) ,(1, 1919293200, 3),(1, 1932598800, 2) ,(1, 1950742800, 3),(1, 1964048400, 2) ,(1, 1982797200, 3),(1, 1995498000, 2) ,(1, 2014246800, 3),(1, 2026947600, 2) ,(1, 2045696400, 3),(1, 2058397200, 2) ,(1, 2077146000, 3),(1, 2090451600, 2) ,(1, 2108595600, 3),(1, 2121901200, 2) ,(1, 2140045200, 3),(3, -1688265000, 2) ,(3, -1656819048, 1),(3, -1641353448, 2) ,(3, -1627965048, 3),(3, -1618716648, 1) ,(3, -1596429048, 3),(3, -1593829848, 5) ,(3, -1589860800, 4),(3, -1542427200, 5) ,(3, -1539493200, 6),(3, -1525323600, 5) ,(3, -1522728000, 4),(3, -1491188400, 7) ,(3, -1247536800, 4),(3, 354920400, 5) ,(3, 370728000, 4),(3, 386456400, 5) ,(3, 402264000, 4),(3, 417992400, 5) ,(3, 433800000, 4),(3, 449614800, 5) ,(3, 465346800, 8),(3, 481071600, 9) ,(3, 496796400, 8),(3, 512521200, 9) ,(3, 528246000, 8),(3, 543970800, 9) ,(3, 559695600, 8),(3, 575420400, 9) ,(3, 591145200, 8),(3, 606870000, 9) ,(3, 622594800, 8),(3, 638319600, 9) ,(3, 654649200, 8),(3, 670374000, 10) ,(3, 686102400, 11),(3, 695779200, 8) ,(3, 701812800, 5),(3, 717534000, 4) ,(3, 733273200, 9),(3, 748998000, 8) ,(3, 764722800, 9),(3, 780447600, 8) ,(3, 796172400, 9),(3, 811897200, 8) ,(3, 828226800, 9),(3, 846370800, 8) ,(3, 859676400, 9),(3, 877820400, 8) ,(3, 891126000, 9),(3, 909270000, 8) ,(3, 922575600, 9),(3, 941324400, 8) ,(3, 954025200, 9),(3, 972774000, 8) ,(3, 985474800, 9),(3, 1004223600, 8) ,(3, 1017529200, 9),(3, 1035673200, 8) ,(3, 1048978800, 9),(3, 1067122800, 8) ,(3, 1080428400, 9),(3, 1099177200, 8) ,(3, 1111878000, 9),(3, 1130626800, 8) ,(3, 1143327600, 9),(3, 1162076400, 8) ,(3, 1174777200, 9),(3, 1193526000, 8) ,(3, 1206831600, 9),(3, 1224975600, 8) ,(3, 1238281200, 9),(3, 1256425200, 8) ,(3, 1269730800, 9),(3, 1288479600, 8) ,(3, 1301180400, 9),(3, 1319929200, 8) ,(3, 1332630000, 9),(3, 1351378800, 8) ,(3, 1364684400, 9),(3, 1382828400, 8) ,(3, 1396134000, 9),(3, 1414278000, 8) ,(3, 1427583600, 9),(3, 1445727600, 8) ,(3, 1459033200, 9),(3, 1477782000, 8) ,(3, 1490482800, 9),(3, 1509231600, 8) ,(3, 1521932400, 9),(3, 1540681200, 8) ,(3, 1553986800, 9),(3, 1572130800, 8) ,(3, 1585436400, 9),(3, 1603580400, 8) ,(3, 1616886000, 9),(3, 1635634800, 8) ,(3, 1648335600, 9),(3, 1667084400, 8) ,(3, 1679785200, 9),(3, 1698534000, 8) ,(3, 1711839600, 9),(3, 1729983600, 8) ,(3, 1743289200, 9),(3, 1761433200, 8) ,(3, 1774738800, 9),(3, 1792882800, 8) ,(3, 1806188400, 9),(3, 1824937200, 8) ,(3, 1837638000, 9),(3, 1856386800, 8) ,(3, 1869087600, 9),(3, 1887836400, 8) ,(3, 1901142000, 9),(3, 1919286000, 8) ,(3, 1932591600, 9),(3, 1950735600, 8) ,(3, 1964041200, 9),(3, 1982790000, 8) ,(3, 1995490800, 9),(3, 2014239600, 8) ,(3, 2026940400, 9),(3, 2045689200, 8) ,(3, 2058390000, 9),(3, 2077138800, 8) ,(3, 2090444400, 9),(3, 2108588400, 8) ,(3, 2121894000, 9),(3, 2140038000, 8),(4, -1688265000, 2) ,(4, -1656819048, 1),(4, -1641353448, 2) ,(4, -1627965048, 3),(4, -1618716648, 1) ,(4, -1596429048, 3),(4, -1593829848, 5) ,(4, -1589860800, 4),(4, -1542427200, 5) ,(4, -1539493200, 6),(4, -1525323600, 5) ,(4, -1522728000, 4),(4, -1491188400, 7) ,(4, -1247536800, 4),(4, 354920409, 5) ,(4, 370728010, 4),(4, 386456410, 5) ,(4, 402264011, 4),(4, 417992411, 5) ,(4, 433800012, 4),(4, 449614812, 5) ,(4, 465346812, 8),(4, 481071612, 9) ,(4, 496796413, 8),(4, 512521213, 9) ,(4, 528246013, 8),(4, 543970813, 9) ,(4, 559695613, 8),(4, 575420414, 9) ,(4, 591145214, 8),(4, 606870014, 9) ,(4, 622594814, 8),(4, 638319615, 9) ,(4, 654649215, 8),(4, 670374016, 10) ,(4, 686102416, 11),(4, 695779216, 8) ,(4, 701812816, 5),(4, 717534017, 4) ,(4, 733273217, 9),(4, 748998018, 8) ,(4, 764722818, 9),(4, 780447619, 8) ,(4, 796172419, 9),(4, 811897219, 8) ,(4, 828226820, 9),(4, 846370820, 8) ,(4, 859676420, 9),(4, 877820421, 8) ,(4, 891126021, 9),(4, 909270021, 8) ,(4, 922575622, 9),(4, 941324422, 8) ,(4, 954025222, 9),(4, 972774022, 8) ,(4, 985474822, 9),(4, 1004223622, 8) ,(4, 1017529222, 9),(4, 1035673222, 8) ,(4, 1048978822, 9),(4, 1067122822, 8) ,(4, 1080428422, 9),(4, 1099177222, 8) ,(4, 1111878022, 9),(4, 1130626822, 8) ,(4, 1143327622, 9),(4, 1162076422, 8) ,(4, 1174777222, 9),(4, 1193526022, 8) ,(4, 1206831622, 9),(4, 1224975622, 8) ,(4, 1238281222, 9),(4, 1256425222, 8) ,(4, 1269730822, 9),(4, 1288479622, 8) ,(4, 1301180422, 9),(4, 1319929222, 8) ,(4, 1332630022, 9),(4, 1351378822, 8) ,(4, 1364684422, 9),(4, 1382828422, 8) ,(4, 1396134022, 9),(4, 1414278022, 8) ,(4, 1427583622, 9),(4, 1445727622, 8) ,(4, 1459033222, 9),(4, 1477782022, 8) ,(4, 1490482822, 9),(4, 1509231622, 8) ,(4, 1521932422, 9),(4, 1540681222, 8) ,(4, 1553986822, 9),(4, 1572130822, 8) ,(4, 1585436422, 9),(4, 1603580422, 8) ,(4, 1616886022, 9),(4, 1635634822, 8) ,(4, 1648335622, 9),(4, 1667084422, 8) ,(4, 1679785222, 9),(4, 1698534022, 8) ,(4, 1711839622, 9),(4, 1729983622, 8) ,(4, 1743289222, 9),(4, 1761433222, 8) ,(4, 1774738822, 9),(4, 1792882822, 8) ,(4, 1806188422, 9),(4, 1824937222, 8) ,(4, 1837638022, 9),(4, 1856386822, 8) ,(4, 1869087622, 9),(4, 1887836422, 8) ,(4, 1901142022, 9),(4, 1919286022, 8) ,(4, 1932591622, 9),(4, 1950735622, 8) ,(4, 1964041222, 9),(4, 1982790022, 8) ,(4, 1995490822, 9),(4, 2014239622, 8) ,(4, 2026940422, 9),(4, 2045689222, 8) ,(4, 2058390022, 9),(4, 2077138822, 8) ,(4, 2090444422, 9),(4, 2108588422, 8) ,(4, 2121894022, 9),(4, 2140038022, 8); +INSERT INTO time_zone (Time_zone_id, Use_leap_seconds) + VALUES (1,'N'), (2,'N'), (3,'N'), (4,'Y'), (5,'N'); + +CREATE TABLE time_zone_transition ( + Time_zone_id int unsigned NOT NULL, + Transition_time bigint signed NOT NULL, + Transition_type_id int unsigned NOT NULL, + PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) +) engine=MyISAM +CHARACTER SET utf8 +comment='Time zone transitions'; -CREATE TABLE time_zone_transition_type (Time_zone_id int unsigned NOT NULL,Transition_type_id int unsigned NOT NULL,Offset int signed DEFAULT 0 NOT NULL,Is_DST tinyint unsigned DEFAULT 0 NOT NULL,Abbreviation char(8) DEFAULT '' NOT NULL,PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id)) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; -INSERT INTO time_zone_transition_type (Time_zone_id,Transition_type_id, Offset, Is_DST, Abbreviation) VALUES (1, 0, 7200, 1, 'MEST') ,(1, 1, 3600, 0, 'MET') ,(1, 2, 7200, 1, 'MEST') ,(1, 3, 3600, 0, 'MET') ,(2, 0, 0, 0, 'UTC') ,(3, 0, 9000, 0, 'MMT') ,(3, 1, 12648, 1, 'MST') ,(3, 2, 9048, 0, 'MMT') ,(3, 3, 16248, 1, 'MDST') ,(3, 4, 10800, 0, 'MSK') ,(3, 5, 14400, 1, 'MSD') ,(3, 6, 18000, 1, 'MSD') ,(3, 7, 7200, 0, 'EET') ,(3, 8, 10800, 0, 'MSK') ,(3, 9, 14400, 1, 'MSD') ,(3, 10, 10800, 1, 'EEST') ,(3, 11, 7200, 0, 'EET') ,(4, 0, 9000, 0, 'MMT') ,(4, 1, 12648, 1, 'MST') ,(4, 2, 9048, 0, 'MMT') ,(4, 3, 16248, 1, 'MDST') ,(4, 4, 10800, 0, 'MSK') ,(4, 5, 14400, 1, 'MSD') ,(4, 6, 18000, 1, 'MSD') ,(4, 7, 7200, 0, 'EET') ,(4, 8, 10800, 0, 'MSK') ,(4, 9, 14400, 1, 'MSD') ,(4, 10, 10800, 1, 'EEST') ,(4, 11, 7200, 0, 'EET') ,(5, 0, 32400, 0, 'CJT') ,(5, 1, 32400, 0, 'JST'); +INSERT INTO time_zone_transition + (Time_zone_id, Transition_time, Transition_type_id) +VALUES + (1, -1693706400, 0) ,(1, -1680483600, 1) + ,(1, -1663455600, 2) ,(1, -1650150000, 3) + ,(1, -1632006000, 2) ,(1, -1618700400, 3) + ,(1, -938905200, 2) ,(1, -857257200, 3) + ,(1, -844556400, 2) ,(1, -828226800, 3) + ,(1, -812502000, 2) ,(1, -796777200, 3) + ,(1, 228877200, 2) ,(1, 243997200, 3) + ,(1, 260326800, 2) ,(1, 276051600, 3) + ,(1, 291776400, 2) ,(1, 307501200, 3) + ,(1, 323830800, 2) ,(1, 338950800, 3) + ,(1, 354675600, 2) ,(1, 370400400, 3) + ,(1, 386125200, 2) ,(1, 401850000, 3) + ,(1, 417574800, 2) ,(1, 433299600, 3) + ,(1, 449024400, 2) ,(1, 465354000, 3) + ,(1, 481078800, 2) ,(1, 496803600, 3) + ,(1, 512528400, 2) ,(1, 528253200, 3) + ,(1, 543978000, 2) ,(1, 559702800, 3) + ,(1, 575427600, 2) ,(1, 591152400, 3) + ,(1, 606877200, 2) ,(1, 622602000, 3) + ,(1, 638326800, 2) ,(1, 654656400, 3) + ,(1, 670381200, 2) ,(1, 686106000, 3) + ,(1, 701830800, 2) ,(1, 717555600, 3) + ,(1, 733280400, 2) ,(1, 749005200, 3) + ,(1, 764730000, 2) ,(1, 780454800, 3) + ,(1, 796179600, 2) ,(1, 811904400, 3) + ,(1, 828234000, 2) ,(1, 846378000, 3) + ,(1, 859683600, 2) ,(1, 877827600, 3) + ,(1, 891133200, 2) ,(1, 909277200, 3) + ,(1, 922582800, 2) ,(1, 941331600, 3) + ,(1, 954032400, 2) ,(1, 972781200, 3) + ,(1, 985482000, 2) ,(1, 1004230800, 3) + ,(1, 1017536400, 2) ,(1, 1035680400, 3) + ,(1, 1048986000, 2) ,(1, 1067130000, 3) + ,(1, 1080435600, 2) ,(1, 1099184400, 3) + ,(1, 1111885200, 2) ,(1, 1130634000, 3) + ,(1, 1143334800, 2) ,(1, 1162083600, 3) + ,(1, 1174784400, 2) ,(1, 1193533200, 3) + ,(1, 1206838800, 2) ,(1, 1224982800, 3) + ,(1, 1238288400, 2) ,(1, 1256432400, 3) + ,(1, 1269738000, 2) ,(1, 1288486800, 3) + ,(1, 1301187600, 2) ,(1, 1319936400, 3) + ,(1, 1332637200, 2) ,(1, 1351386000, 3) + ,(1, 1364691600, 2) ,(1, 1382835600, 3) + ,(1, 1396141200, 2) ,(1, 1414285200, 3) + ,(1, 1427590800, 2) ,(1, 1445734800, 3) + ,(1, 1459040400, 2) ,(1, 1477789200, 3) + ,(1, 1490490000, 2) ,(1, 1509238800, 3) + ,(1, 1521939600, 2) ,(1, 1540688400, 3) + ,(1, 1553994000, 2) ,(1, 1572138000, 3) + ,(1, 1585443600, 2) ,(1, 1603587600, 3) + ,(1, 1616893200, 2) ,(1, 1635642000, 3) + ,(1, 1648342800, 2) ,(1, 1667091600, 3) + ,(1, 1679792400, 2) ,(1, 1698541200, 3) + ,(1, 1711846800, 2) ,(1, 1729990800, 3) + ,(1, 1743296400, 2) ,(1, 1761440400, 3) + ,(1, 1774746000, 2) ,(1, 1792890000, 3) + ,(1, 1806195600, 2) ,(1, 1824944400, 3) + ,(1, 1837645200, 2) ,(1, 1856394000, 3) + ,(1, 1869094800, 2) ,(1, 1887843600, 3) + ,(1, 1901149200, 2) ,(1, 1919293200, 3) + ,(1, 1932598800, 2) ,(1, 1950742800, 3) + ,(1, 1964048400, 2) ,(1, 1982797200, 3) + ,(1, 1995498000, 2) ,(1, 2014246800, 3) + ,(1, 2026947600, 2) ,(1, 2045696400, 3) + ,(1, 2058397200, 2) ,(1, 2077146000, 3) + ,(1, 2090451600, 2) ,(1, 2108595600, 3) + ,(1, 2121901200, 2) ,(1, 2140045200, 3) + ,(3, -1688265000, 2) ,(3, -1656819048, 1) + ,(3, -1641353448, 2) ,(3, -1627965048, 3) + ,(3, -1618716648, 1) ,(3, -1596429048, 3) + ,(3, -1593829848, 5) ,(3, -1589860800, 4) + ,(3, -1542427200, 5) ,(3, -1539493200, 6) + ,(3, -1525323600, 5) ,(3, -1522728000, 4) + ,(3, -1491188400, 7) ,(3, -1247536800, 4) + ,(3, 354920400, 5) ,(3, 370728000, 4) + ,(3, 386456400, 5) ,(3, 402264000, 4) + ,(3, 417992400, 5) ,(3, 433800000, 4) + ,(3, 449614800, 5) ,(3, 465346800, 8) + ,(3, 481071600, 9) ,(3, 496796400, 8) + ,(3, 512521200, 9) ,(3, 528246000, 8) + ,(3, 543970800, 9) ,(3, 559695600, 8) + ,(3, 575420400, 9) ,(3, 591145200, 8) + ,(3, 606870000, 9) ,(3, 622594800, 8) + ,(3, 638319600, 9) ,(3, 654649200, 8) + ,(3, 670374000, 10) ,(3, 686102400, 11) + ,(3, 695779200, 8) ,(3, 701812800, 5) + ,(3, 717534000, 4) ,(3, 733273200, 9) + ,(3, 748998000, 8) ,(3, 764722800, 9) + ,(3, 780447600, 8) ,(3, 796172400, 9) + ,(3, 811897200, 8) ,(3, 828226800, 9) + ,(3, 846370800, 8) ,(3, 859676400, 9) + ,(3, 877820400, 8) ,(3, 891126000, 9) + ,(3, 909270000, 8) ,(3, 922575600, 9) + ,(3, 941324400, 8) ,(3, 954025200, 9) + ,(3, 972774000, 8) ,(3, 985474800, 9) + ,(3, 1004223600, 8) ,(3, 1017529200, 9) + ,(3, 1035673200, 8) ,(3, 1048978800, 9) + ,(3, 1067122800, 8) ,(3, 1080428400, 9) + ,(3, 1099177200, 8) ,(3, 1111878000, 9) + ,(3, 1130626800, 8) ,(3, 1143327600, 9) + ,(3, 1162076400, 8) ,(3, 1174777200, 9) + ,(3, 1193526000, 8) ,(3, 1206831600, 9) + ,(3, 1224975600, 8) ,(3, 1238281200, 9) + ,(3, 1256425200, 8) ,(3, 1269730800, 9) + ,(3, 1288479600, 8) ,(3, 1301180400, 9) + ,(3, 1319929200, 8) ,(3, 1332630000, 9) + ,(3, 1351378800, 8) ,(3, 1364684400, 9) + ,(3, 1382828400, 8) ,(3, 1396134000, 9) + ,(3, 1414278000, 8) ,(3, 1427583600, 9) + ,(3, 1445727600, 8) ,(3, 1459033200, 9) + ,(3, 1477782000, 8) ,(3, 1490482800, 9) + ,(3, 1509231600, 8) ,(3, 1521932400, 9) + ,(3, 1540681200, 8) ,(3, 1553986800, 9) + ,(3, 1572130800, 8) ,(3, 1585436400, 9) + ,(3, 1603580400, 8) ,(3, 1616886000, 9) + ,(3, 1635634800, 8) ,(3, 1648335600, 9) + ,(3, 1667084400, 8) ,(3, 1679785200, 9) + ,(3, 1698534000, 8) ,(3, 1711839600, 9) + ,(3, 1729983600, 8) ,(3, 1743289200, 9) + ,(3, 1761433200, 8) ,(3, 1774738800, 9) + ,(3, 1792882800, 8) ,(3, 1806188400, 9) + ,(3, 1824937200, 8) ,(3, 1837638000, 9) + ,(3, 1856386800, 8) ,(3, 1869087600, 9) + ,(3, 1887836400, 8) ,(3, 1901142000, 9) + ,(3, 1919286000, 8) ,(3, 1932591600, 9) + ,(3, 1950735600, 8) ,(3, 1964041200, 9) + ,(3, 1982790000, 8) ,(3, 1995490800, 9) + ,(3, 2014239600, 8) ,(3, 2026940400, 9) + ,(3, 2045689200, 8) ,(3, 2058390000, 9) + ,(3, 2077138800, 8) ,(3, 2090444400, 9) + ,(3, 2108588400, 8) ,(3, 2121894000, 9) + ,(3, 2140038000, 8) + ,(4, -1688265000, 2) ,(4, -1656819048, 1) + ,(4, -1641353448, 2) ,(4, -1627965048, 3) + ,(4, -1618716648, 1) ,(4, -1596429048, 3) + ,(4, -1593829848, 5) ,(4, -1589860800, 4) + ,(4, -1542427200, 5) ,(4, -1539493200, 6) + ,(4, -1525323600, 5) ,(4, -1522728000, 4) + ,(4, -1491188400, 7) ,(4, -1247536800, 4) + ,(4, 354920409, 5) ,(4, 370728010, 4) + ,(4, 386456410, 5) ,(4, 402264011, 4) + ,(4, 417992411, 5) ,(4, 433800012, 4) + ,(4, 449614812, 5) ,(4, 465346812, 8) + ,(4, 481071612, 9) ,(4, 496796413, 8) + ,(4, 512521213, 9) ,(4, 528246013, 8) + ,(4, 543970813, 9) ,(4, 559695613, 8) + ,(4, 575420414, 9) ,(4, 591145214, 8) + ,(4, 606870014, 9) ,(4, 622594814, 8) + ,(4, 638319615, 9) ,(4, 654649215, 8) + ,(4, 670374016, 10) ,(4, 686102416, 11) + ,(4, 695779216, 8) ,(4, 701812816, 5) + ,(4, 717534017, 4) ,(4, 733273217, 9) + ,(4, 748998018, 8) ,(4, 764722818, 9) + ,(4, 780447619, 8) ,(4, 796172419, 9) + ,(4, 811897219, 8) ,(4, 828226820, 9) + ,(4, 846370820, 8) ,(4, 859676420, 9) + ,(4, 877820421, 8) ,(4, 891126021, 9) + ,(4, 909270021, 8) ,(4, 922575622, 9) + ,(4, 941324422, 8) ,(4, 954025222, 9) + ,(4, 972774022, 8) ,(4, 985474822, 9) + ,(4, 1004223622, 8) ,(4, 1017529222, 9) + ,(4, 1035673222, 8) ,(4, 1048978822, 9) + ,(4, 1067122822, 8) ,(4, 1080428422, 9) + ,(4, 1099177222, 8) ,(4, 1111878022, 9) + ,(4, 1130626822, 8) ,(4, 1143327622, 9) + ,(4, 1162076422, 8) ,(4, 1174777222, 9) + ,(4, 1193526022, 8) ,(4, 1206831622, 9) + ,(4, 1224975622, 8) ,(4, 1238281222, 9) + ,(4, 1256425222, 8) ,(4, 1269730822, 9) + ,(4, 1288479622, 8) ,(4, 1301180422, 9) + ,(4, 1319929222, 8) ,(4, 1332630022, 9) + ,(4, 1351378822, 8) ,(4, 1364684422, 9) + ,(4, 1382828422, 8) ,(4, 1396134022, 9) + ,(4, 1414278022, 8) ,(4, 1427583622, 9) + ,(4, 1445727622, 8) ,(4, 1459033222, 9) + ,(4, 1477782022, 8) ,(4, 1490482822, 9) + ,(4, 1509231622, 8) ,(4, 1521932422, 9) + ,(4, 1540681222, 8) ,(4, 1553986822, 9) + ,(4, 1572130822, 8) ,(4, 1585436422, 9) + ,(4, 1603580422, 8) ,(4, 1616886022, 9) + ,(4, 1635634822, 8) ,(4, 1648335622, 9) + ,(4, 1667084422, 8) ,(4, 1679785222, 9) + ,(4, 1698534022, 8) ,(4, 1711839622, 9) + ,(4, 1729983622, 8) ,(4, 1743289222, 9) + ,(4, 1761433222, 8) ,(4, 1774738822, 9) + ,(4, 1792882822, 8) ,(4, 1806188422, 9) + ,(4, 1824937222, 8) ,(4, 1837638022, 9) + ,(4, 1856386822, 8) ,(4, 1869087622, 9) + ,(4, 1887836422, 8) ,(4, 1901142022, 9) + ,(4, 1919286022, 8) ,(4, 1932591622, 9) + ,(4, 1950735622, 8) ,(4, 1964041222, 9) + ,(4, 1982790022, 8) ,(4, 1995490822, 9) + ,(4, 2014239622, 8) ,(4, 2026940422, 9) + ,(4, 2045689222, 8) ,(4, 2058390022, 9) + ,(4, 2077138822, 8) ,(4, 2090444422, 9) + ,(4, 2108588422, 8) ,(4, 2121894022, 9) + ,(4, 2140038022, 8) + ,(5, -1009875600, 1); + + +CREATE TABLE time_zone_transition_type ( + Time_zone_id int unsigned NOT NULL, + Transition_type_id int unsigned NOT NULL, + Offset int signed DEFAULT 0 NOT NULL, + Is_DST tinyint unsigned DEFAULT 0 NOT NULL, + Abbreviation char(8) DEFAULT '' NOT NULL, + PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) +) engine=MyISAM +CHARACTER SET utf8 +comment='Time zone transition types'; -CREATE TABLE time_zone_leap_second (Transition_time bigint signed NOT NULL,Correction int signed NOT NULL,PRIMARY KEY TranTime (Transition_time)) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; -INSERT INTO time_zone_leap_second (Transition_time, Correction) VALUES (78796800, 1) ,(94694401, 2) ,(126230402, 3) ,(157766403, 4) ,(189302404, 5) ,(220924805, 6) ,(252460806, 7) ,(283996807, 8) ,(315532808, 9) ,(362793609, 10) ,(394329610, 11) ,(425865611, 12) ,(489024012, 13) ,(567993613, 14) ,(631152014, 15) ,(662688015, 16) ,(709948816, 17) ,(741484817, 18) ,(773020818, 19) ,(820454419, 20) ,(867715220, 21) ,(915148821, 22); +INSERT INTO time_zone_transition_type ( + Time_zone_id,Transition_type_id, Offset, Is_DST, Abbreviation) VALUES + (1, 0, 7200, 1, 'MEST') ,(1, 1, 3600, 0, 'MET') + ,(1, 2, 7200, 1, 'MEST') ,(1, 3, 3600, 0, 'MET') + ,(2, 0, 0, 0, 'UTC') + ,(3, 0, 9000, 0, 'MMT') ,(3, 1, 12648, 1, 'MST') + ,(3, 2, 9048, 0, 'MMT') ,(3, 3, 16248, 1, 'MDST') + ,(3, 4, 10800, 0, 'MSK') ,(3, 5, 14400, 1, 'MSD') + ,(3, 6, 18000, 1, 'MSD') ,(3, 7, 7200, 0, 'EET') + ,(3, 8, 10800, 0, 'MSK') ,(3, 9, 14400, 1, 'MSD') + ,(3, 10, 10800, 1, 'EEST') ,(3, 11, 7200, 0, 'EET') + ,(4, 0, 9000, 0, 'MMT') ,(4, 1, 12648, 1, 'MST') + ,(4, 2, 9048, 0, 'MMT') ,(4, 3, 16248, 1, 'MDST') + ,(4, 4, 10800, 0, 'MSK') ,(4, 5, 14400, 1, 'MSD') + ,(4, 6, 18000, 1, 'MSD') ,(4, 7, 7200, 0, 'EET') + ,(4, 8, 10800, 0, 'MSK') ,(4, 9, 14400, 1, 'MSD') + ,(4, 10, 10800, 1, 'EEST') ,(4, 11, 7200, 0, 'EET') + ,(5, 0, 32400, 0, 'CJT') ,(5, 1, 32400, 0, 'JST'); +CREATE TABLE time_zone_leap_second ( + Transition_time bigint signed NOT NULL, + Correction int signed NOT NULL, + PRIMARY KEY TranTime (Transition_time) +) engine=MyISAM +CHARACTER SET utf8 +comment='Leap seconds information for time zones'; + + +INSERT INTO time_zone_leap_second ( + Transition_time, Correction) VALUES + (78796800, 1) ,(94694401, 2) ,(126230402, 3) + ,(157766403, 4) ,(189302404, 5) ,(220924805, 6) + ,(252460806, 7) ,(283996807, 8) ,(315532808, 9) + ,(362793609, 10) ,(394329610, 11) ,(425865611, 12) + ,(489024012, 13) ,(567993613, 14) ,(631152014, 15) + ,(662688015, 16) ,(709948816, 17) ,(741484817, 18) + ,(773020818, 19) ,(820454419, 20) ,(867715220, 21) + ,(915148821, 22); diff --git a/mysql-test/lib/mtr_process.pl b/mysql-test/lib/mtr_process.pl index e1461a9730c..8aefc235d72 100644 --- a/mysql-test/lib/mtr_process.pl +++ b/mysql-test/lib/mtr_process.pl @@ -123,6 +123,17 @@ sub spawn_impl ($$$$$$$) { $SIG{INT}= 'DEFAULT'; # Parent do some stuff, we don't + if ( $::glob_cygwin_shell and $mode eq 'test' ) + { + # Programs started from mysqltest under Cygwin, are to + # execute them within Cygwin. Else simple things in test + # files like + # --system "echo 1 > file" + # will fail. + # FIXME not working :-( +# $ENV{'COMSPEC'}= "$::glob_cygwin_shell -c"; + } + if ( $output ) { if ( ! open(STDOUT,">",$output) ) @@ -130,6 +141,7 @@ sub spawn_impl ($$$$$$$) { mtr_error("can't redirect STDOUT to \"$output\": $!"); } } + if ( $error ) { if ( $output eq $error ) @@ -147,6 +159,7 @@ sub spawn_impl ($$$$$$$) { } } } + if ( $input ) { if ( ! open(STDIN,"<",$input) ) @@ -154,7 +167,11 @@ sub spawn_impl ($$$$$$$) { mtr_error("can't redirect STDIN to \"$input\": $!"); } } - exec($path,@$arg_list_t); + + if ( ! exec($path,@$arg_list_t) ) + { + mtr_error("failed to execute \"$path\": $!"); + } } } } @@ -569,7 +586,7 @@ sub mtr_stop_mysqld_servers ($) { sub mtr_mysqladmin_shutdown () { my $spec= shift; - my @mysql_admin_pids; + my %mysql_admin_pids; my @to_kill_specs; foreach my $srv ( @$spec ) @@ -611,13 +628,19 @@ sub mtr_mysqladmin_shutdown () { # We don't wait for termination of mysqladmin my $pid= mtr_spawn($::exe_mysqladmin, $args, "", $::path_manager_log, $::path_manager_log, ""); - push(@mysql_admin_pids, $pid); + $mysql_admin_pids{$pid}= 1; } # We wait blocking, we wait for the last one anyway - foreach my $pid (@mysql_admin_pids) + while (keys %mysql_admin_pids) { - waitpid($pid,0); # FIXME no need to check -1 or 0? + foreach my $pid (keys %mysql_admin_pids) + { + if ( waitpid($pid,0) > 0 ) + { + delete $mysql_admin_pids{$pid}; + } + } } # If we trusted "mysqladmin --shutdown_timeout= ..." we could just diff --git a/mysql-test/lib/mtr_report.pl b/mysql-test/lib/mtr_report.pl index c45bb1601ce..9947fed9d27 100644 --- a/mysql-test/lib/mtr_report.pl +++ b/mysql-test/lib/mtr_report.pl @@ -50,12 +50,14 @@ sub mtr_show_failed_diff ($) { $result_file= "$result_file$::opt_result_ext"; } + my $diffopts= $::opt_udiff ? "-u" : "-c"; + if ( -f $reject_file ) { print "Below are the diffs between actual and expected results:\n"; print "-------------------------------------------------------\n"; # FIXME check result code?! - mtr_run("diff",["-c",$result_file,$reject_file], "", "", "", ""); + mtr_run("diff",[$diffopts,$result_file,$reject_file], "", "", "", ""); print "-------------------------------------------------------\n"; print "Please follow the instructions outlined at\n"; print "http://www.mysql.com/doc/en/Reporting_mysqltest_bugs.html\n"; @@ -159,7 +161,7 @@ sub mtr_report_stats ($) { { my $ratio= $tot_passed * 100 / $tot_tests; printf "Failed $tot_failed/$tot_tests tests, " . - "%.2f\% successful.\n\n", $ratio; + "%.2f\% were successful.\n\n", $ratio; print "The log files in var/log may give you some hint\n", "of what when wrong.\n", diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index f204fee50ed..f8adf372585 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -156,13 +156,13 @@ our @mysqld_src_dirs= our $glob_win32= 0; # OS and native Win32 executables our $glob_win32_perl= 0; # ActiveState Win32 Perl our $glob_cygwin_perl= 0; # Cygwin Perl +our $glob_cygwin_shell= undef; our $glob_mysql_test_dir= undef; our $glob_mysql_bench_dir= undef; our $glob_hostname= undef; our $glob_scriptname= undef; our $glob_use_running_server= 0; our $glob_use_running_ndbcluster= 0; -our $glob_user= 'test'; our $glob_use_embedded_server= 0; our $glob_basedir; @@ -281,6 +281,8 @@ our $opt_wait_timeout= 10; our $opt_warnings; +our $opt_udiff; + our $opt_with_ndbcluster; our $opt_with_openssl; @@ -358,6 +360,10 @@ sub main () { if ( $opt_start_and_exit ) { + if ( ndbcluster_start() ) + { + mtr_error("Can't start ndbcluster"); + } if ( mysqld_start('master',0,[],[]) ) { mtr_report("Servers started, exiting"); @@ -422,7 +428,9 @@ sub initial_setup () { { # Windows programs like 'mysqld' needs Windows paths $glob_mysql_test_dir= `cygpath -m $glob_mysql_test_dir`; + $glob_cygwin_shell= `cygpath -w $ENV{'SHELL'}`; # The Windows path c:\... chomp($glob_mysql_test_dir); + chomp($glob_cygwin_shell); } $glob_basedir= dirname($glob_mysql_test_dir); $glob_mysql_bench_dir= "$glob_basedir/mysql-bench"; # FIXME make configurable @@ -524,6 +532,7 @@ sub command_line_setup () { 'start-from=s' => \$opt_start_from, 'timer' => \$opt_timer, 'tmpdir=s' => \$opt_tmpdir, + 'unified-diff|udiff' => \$opt_udiff, 'user-test=s' => \$opt_user_test, 'user=s' => \$opt_user, 'verbose' => \$opt_verbose, @@ -712,17 +721,16 @@ sub command_line_setup () { # } #} - if ( $opt_user ) - { - $glob_user= $opt_user; - } - elsif ( $glob_use_running_server ) + if ( ! $opt_user ) { - $glob_user= "test"; - } - else - { - $glob_user= "root"; # We want to do FLUSH xxx commands + if ( $glob_use_running_server ) + { + $opt_user= "test"; + } + else + { + $opt_user= "root"; # We want to do FLUSH xxx commands + } } } @@ -837,7 +845,7 @@ sub executable_setup () { } else { - error("Cannot find embedded server 'mysqltest_embedded'"); + mtr_error("Cannot find embedded server 'mysqltest_embedded'"); } if ( -d "$path_tests_bindir/mysql_client_test_embedded" ) { @@ -860,9 +868,6 @@ sub executable_setup () { $exe_ndb_mgm= "$glob_basedir/bin/ndb_mgm"; } - # FIXME special $exe_master_mysqld and $exe_slave_mysqld - # are not used that much.... - if ( ! $exe_master_mysqld ) { $exe_master_mysqld= $exe_mysqld; @@ -1054,7 +1059,7 @@ sub ndbcluster_start () { "--data-dir=$glob_mysql_test_dir/var"], "", "/dev/null", "", "") ) { - mtr_error("Error ndbcluster_install"); + mtr_error("Error ndbcluster_start"); return 1; } @@ -1102,7 +1107,7 @@ sub run_benchmarks ($) { mtr_init_args(\$args); mtr_add_arg($args, "--socket=%s", $master->[0]->{'path_mysock'}); - mtr_add_arg($args, "--user=root"); + mtr_add_arg($args, "--user=%s", $opt_user); if ( $opt_small_bench ) { @@ -1226,15 +1231,41 @@ sub install_db ($$) { my $type= shift; my $data_dir= shift; - my $init_db_sql= "lib/init_db.sql"; # FIXME this is too simple maybe + my $init_db_sql= "lib/init_db.sql"; + my $init_db_sql_tmp= "/tmp/init_db.sql$$"; my $args; mtr_report("Installing \u$type Databases"); + open(IN, $init_db_sql) + or error("Can't open $init_db_sql: $!"); + open(OUT, ">", $init_db_sql_tmp) + or error("Can't write to $init_db_sql_tmp: $!"); + while (<IN>) + { + chomp; + s/\@HOSTNAME\@/$glob_hostname/; + if ( /^\s*$/ ) + { + print OUT "\n"; + } + elsif (/;$/) + { + print OUT "$_\n"; + } + else + { + print OUT "$_ "; + } + } + close OUT; + close IN; + mtr_init_args(\$args); mtr_add_arg($args, "--no-defaults"); mtr_add_arg($args, "--bootstrap"); + mtr_add_arg($args, "--console"); mtr_add_arg($args, "--skip-grant-tables"); mtr_add_arg($args, "--basedir=%s", $path_my_basedir); mtr_add_arg($args, "--datadir=%s", $data_dir); @@ -1248,12 +1279,14 @@ sub install_db ($$) { mtr_add_arg($args, "--character-sets-dir=%s", $path_charsetsdir); } - if ( mtr_run($exe_mysqld, $args, $init_db_sql, + if ( mtr_run($exe_mysqld, $args, $init_db_sql_tmp, $path_manager_log, $path_manager_log, "") != 0 ) { + unlink($init_db_sql_tmp); mtr_error("Error executing mysqld --bootstrap\n" . "Could not install $type test DBs"); } + unlink($init_db_sql_tmp); } @@ -1491,8 +1524,8 @@ sub report_failure_and_restart ($) { # but stop before actually running mysqld or anything. sub do_before_start_master ($$) { - my $tname= shift; - my $master_init_script= shift; + my $tname= shift; + my $init_script= shift; # FIXME what about second master..... @@ -1502,8 +1535,9 @@ sub do_before_start_master ($$) { $tname ne "rpl_crash_binlog_ib_3b") { # FIXME we really want separate dir for binlogs - `rm -fr $glob_mysql_test_dir/var/log/master-bin.*`; -# unlink("$glob_mysql_test_dir/var/log/master-bin.*"); + # FIXME replace 'rm' in backticks with portable Perl function + `rm -f $glob_mysql_test_dir/var/log/master-bin*`; +# unlink("$glob_mysql_test_dir/var/log/master-bin*"); } # Remove old master.info and relay-log.info files @@ -1512,23 +1546,23 @@ sub do_before_start_master ($$) { unlink("$glob_mysql_test_dir/var/master1-data/master.info"); unlink("$glob_mysql_test_dir/var/master1-data/relay-log.info"); - #run master initialization shell script if one exists - - if ( $master_init_script and - mtr_run($master_init_script, [], "", "", "", "") != 0 ) + # Run master initialization shell script if one exists + if ( $init_script ) { - mtr_error("Can't run $master_init_script"); + my $ret= mtr_run("/bin/sh", [$init_script], "", "", "", ""); + if ( $ret != 0 ) + { + # FIXME rewrite those scripts to return 0 if successful +# mtr_warning("$init_script exited with code $ret"); + } } # for gcov FIXME needed? If so we need more absolute paths # chdir($glob_basedir); } sub do_before_start_slave ($$) { - my $tname= shift; - my $slave_init_script= shift; - - # When testing fail-safe replication, we will have more than one slave - # in this case, we start secondary slaves with an argument + my $tname= shift; + my $init_script= shift; # Remove stale binary logs and old master.info files # except for too tests which need them @@ -1537,6 +1571,7 @@ sub do_before_start_slave ($$) { $tname ne "rpl_crash_binlog_ib_3b" ) { # FIXME we really want separate dir for binlogs + # FIXME replace 'rm' in backticks with portable Perl function `rm -fr $glob_mysql_test_dir/var/log/slave*-bin.*`; # unlink("$glob_mysql_test_dir/var/log/slave*-bin.*"); # FIXME idx??? # FIXME really master?! @@ -1544,14 +1579,19 @@ sub do_before_start_slave ($$) { unlink("$glob_mysql_test_dir/var/slave-data/relay-log.info"); } - #run slave initialization shell script if one exists - if ( $slave_init_script and - mtr_run($slave_init_script, [], "", "", "", "") != 0 ) + # Run slave initialization shell script if one exists + if ( $init_script ) { - mtr_error("Can't run $slave_init_script"); + my $ret= mtr_run("/bin/sh", [$init_script], "", "", "", ""); + if ( $ret != 0 ) + { + # FIXME rewrite those scripts to return 0 if successful +# mtr_warning("$init_script exited with code $ret"); + } } - unlink("$glob_mysql_test_dir/var/slave-data/log.*"); + `rm -f $glob_mysql_test_dir/var/slave-data/log.*`; +# unlink("$glob_mysql_test_dir/var/slave-data/log.*"); } sub mysqld_arguments ($$$$$) { @@ -1579,6 +1619,7 @@ sub mysqld_arguments ($$$$$) { mtr_add_arg($args, "%s--no-defaults", $prefix); } + mtr_add_arg($args, "%s--console", $prefix); mtr_add_arg($args, "%s--basedir=%s", $prefix, $path_my_basedir); mtr_add_arg($args, "%s--character-sets-dir=%s", $prefix, $path_charsetsdir); mtr_add_arg($args, "%s--core", $prefix); @@ -1669,12 +1710,12 @@ sub mysqld_arguments ($$$$$) { { if ( $type eq 'master' ) { - mtr_add_arg($args, "--debug=d:t:i:A,%s/var/log/master%s.trace", + mtr_add_arg($args, "%s--debug=d:t:i:A,%s/var/log/master%s.trace", $prefix, $glob_mysql_test_dir, $sidx); } if ( $type eq 'slave' ) { - mtr_add_arg($args, "--debug=d:t:i:A,%s/var/log/slave%s.trace", + mtr_add_arg($args, "%s--debug=d:t:i:A,%s/var/log/slave%s.trace", $prefix, $glob_mysql_test_dir, $sidx); } } @@ -1789,18 +1830,17 @@ sub mysqld_start ($$$$) { my $exe; my $pid; - # FIXME code duplication, make up your mind.... - if ( $opt_source_dist ) + if ( $type eq 'master' ) { - $exe= "$glob_basedir/sql/mysqld"; + $exe= $exe_master_mysqld; + } + elsif ( $type eq 'slave' ) + { + $exe= $exe_slave_mysqld; } else { - $exe ="$glob_basedir/libexec/mysqld"; - if ( ! -x $exe ) - { - $exe ="$glob_basedir/bin/mysqld"; - } + $exe= $exe_mysqld; } mtr_init_args(\$args); @@ -1983,7 +2023,7 @@ sub run_mysqltest ($$) { mtr_add_arg($args, "--no-defaults"); mtr_add_arg($args, "--socket=%s", $master->[0]->{'path_mysock'}); mtr_add_arg($args, "--database=test"); - mtr_add_arg($args, "--user=%s", $glob_user); + mtr_add_arg($args, "--user=%s", $opt_user); mtr_add_arg($args, "--password="); mtr_add_arg($args, "--silent"); mtr_add_arg($args, "-v"); @@ -2054,7 +2094,7 @@ sub run_mysqltest ($$) { mysqld_arguments($args,'master',0,$tinfo->{'master_opt'},[]); } - return mtr_run($exe_mysqltest,$args,$tinfo->{'path'},"",$path_timefile,""); + return mtr_run_test($exe_mysqltest,$args,$tinfo->{'path'},"",$path_timefile,""); } ############################################################################## @@ -2136,6 +2176,7 @@ Misc options start-and-exit Only initiate and start the "mysqld" servers fast Don't try to cleanup from earlier runs help Get this help text + unified-diff | udiff When presenting differences, use unified diff Options not yet described, or that I want to look into more diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh index af432f37868..90846a2711a 100644 --- a/mysql-test/mysql-test-run.sh +++ b/mysql-test/mysql-test-run.sh @@ -218,7 +218,7 @@ EXTRA_MYSQL_TEST_OPT="" EXTRA_MYSQLDUMP_OPT="" EXTRA_MYSQLBINLOG_OPT="" USE_RUNNING_SERVER="" -USE_NDBCLUSTER="" +USE_NDBCLUSTER=@USE_NDBCLUSTER@ USE_RUNNING_NDBCLUSTER="" USE_PURIFY="" PURIFY_LOGS="" @@ -426,6 +426,11 @@ while test $# -gt 0; do TMP=`$ECHO "$1" | $SED -e "s;--valgrind-options=;;"` VALGRIND="$VALGRIND $TMP" ;; + --skip-ndbcluster) + USE_NDBCLUSTER="" + EXTRA_MASTER_MYSQLD_OPT="$EXTRA_MASTER_MYSQLD_OPT $1" + EXTRA_SLAVE_MYSQLD_OPT="$EXTRA_SLAVE_MYSQLD_OPT $1" + ;; --skip-*) EXTRA_MASTER_MYSQLD_OPT="$EXTRA_MASTER_MYSQLD_OPT $1" EXTRA_SLAVE_MYSQLD_OPT="$EXTRA_SLAVE_MYSQLD_OPT $1" @@ -444,6 +449,7 @@ while test $# -gt 0; do --debug=d:t:A,$MYSQL_TEST_DIR/var/log/mysqldump.trace" EXTRA_MYSQLBINLOG_OPT="$EXTRA_MYSQLBINLOG_OPT \ --debug=d:t:A,$MYSQL_TEST_DIR/var/log/mysqlbinlog.trace" + EXTRA_MYSQL_CLIENT_TEST_OPT="--debug=d:t:A,$MYSQL_TEST_DIR/var/log/mysql_client_test.trace" ;; --fast) FAST_START=1 @@ -681,7 +687,7 @@ then EXTRA_SLAVE_MYSQLD_OPT="$EXTRA_SLAVE_MYSQLD_OPT --user=root" fi -MYSQL_CLIENT_TEST="$MYSQL_CLIENT_TEST --no-defaults --testcase --user=root --socket=$MASTER_MYSOCK --port=$MYSQL_TCP_PORT --silent" +MYSQL_CLIENT_TEST="$MYSQL_CLIENT_TEST --no-defaults --testcase --user=root --socket=$MASTER_MYSOCK --port=$MYSQL_TCP_PORT --silent $EXTRA_MYSQL_CLIENT_TEST_OPT" MYSQL_DUMP="$MYSQL_DUMP --no-defaults -uroot --socket=$MASTER_MYSOCK --password=$DBPASSWD $EXTRA_MYSQLDUMP_OPT" MYSQL_BINLOG="$MYSQL_BINLOG --no-defaults --local-load=$MYSQL_TMP_DIR $EXTRA_MYSQLBINLOG_OPT" MYSQL_FIX_SYSTEM_TABLES="$MYSQL_FIX_SYSTEM_TABLES --no-defaults --host=localhost --port=$MASTER_MYPORT --socket=$MASTER_MYSOCK --user=root --password=$DBPASSWD --basedir=$BASEDIR --bindir=$CLIENT_BINDIR --verbose" @@ -811,6 +817,18 @@ skip_test() { $ECHO "$RES$RES_SPACE [ skipped ]" } + +disable_test() { + USERT=" ...." + SYST=" ...." + REALT=" ...." + pname=`$ECHO "$1 "|$CUT -c 1-24` + RES="$pname" + skip_inc + $ECHO "$RES$RES_SPACE [ disabled ] $2" +} + + report_current_test () { tname=$1 echo "CURRENT_TEST: $tname" >> $MASTER_MYERR @@ -832,7 +850,7 @@ report_stats () { whole=`$PRINTF %.2s $raw` xwhole=`$EXPR $whole \* 100` deci=`$EXPR $raw - $xwhole` - $ECHO "Failed ${TOT_FAIL}/${TOT_TEST} tests, ${whole}.${deci}% successful." + $ECHO "Failed ${TOT_FAIL}/${TOT_TEST} tests, ${whole}.${deci}% were successful." $ECHO "" $ECHO "The log files in $MY_LOG_DIR may give you some hint" $ECHO "of what when wrong." @@ -1465,6 +1483,12 @@ run_testcase () if [ -n "$RESULT_EXT" -a \( x$RECORD = x1 -o -f "$result_file$RESULT_EXT" \) ] ; then result_file="$result_file$RESULT_EXT" fi + if [ -f "$TESTDIR/$tname.disabled" ] + then + comment=`$CAT $TESTDIR/$tname.disabled`; + disable_test $tname "$comment" + return + fi if [ "$USE_MANAGER" = 1 ] ; then many_slaves=`$EXPR \( \( $tname : rpl_failsafe \) != 0 \) \| \( \( $tname : rpl_chain_temp_table \) != 0 \)` fi diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index 4c70e72bdfb..eb3d0da3f23 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -87,3 +87,42 @@ drop table t1; SELECT '0x8000000000000001'+0; '0x8000000000000001'+0 0 +create table t1 ( +value64 bigint unsigned not null, +value32 integer not null, +primary key(value64, value32) +); +create table t2 ( +value64 bigint unsigned not null, +value32 integer not null, +primary key(value64, value32) +); +insert into t1 values(17156792991891826145, 1); +insert into t1 values( 9223372036854775807, 2); +insert into t2 values(17156792991891826145, 3); +insert into t2 values( 9223372036854775807, 4); +select * from t1; +value64 value32 +9223372036854775807 2 +17156792991891826145 1 +select * from t2; +value64 value32 +9223372036854775807 4 +17156792991891826145 3 +select * from t1, t2 where t1.value64=17156792991891826145 and +t2.value64=17156792991891826145; +value64 value32 value64 value32 +17156792991891826145 1 17156792991891826145 3 +select * from t1, t2 where t1.value64=17156792991891826145 and +t2.value64=t1.value64; +value64 value32 value64 value32 +17156792991891826145 1 17156792991891826145 3 +select * from t1, t2 where t1.value64= 9223372036854775807 and +t2.value64=9223372036854775807; +value64 value32 value64 value32 +9223372036854775807 2 9223372036854775807 4 +select * from t1, t2 where t1.value64= 9223372036854775807 and +t2.value64=t1.value64; +value64 value32 value64 value32 +9223372036854775807 2 9223372036854775807 4 +drop table t1, t2; diff --git a/mysql-test/r/ctype_collate.result b/mysql-test/r/ctype_collate.result index d4a8beda185..bc77e9c2362 100644 --- a/mysql-test/r/ctype_collate.result +++ b/mysql-test/r/ctype_collate.result @@ -514,7 +514,7 @@ Variable_name Value character_set_client latin1 SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; charset('a') collation('a') coercibility('a') 'a'='A' -latin1 latin1_swedish_ci 3 1 +latin1 latin1_swedish_ci 4 1 explain extended SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used @@ -525,7 +525,7 @@ SHOW VARIABLES LIKE 'collation_client'; Variable_name Value SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; charset('a') collation('a') coercibility('a') 'a'='A' -latin1 latin1_swedish_ci 3 1 +latin1 latin1_swedish_ci 4 1 SET CHARACTER SET 'DEFAULT'; ERROR 42000: Unknown character set: 'DEFAULT' DROP TABLE t1; diff --git a/mysql-test/r/ctype_cp1250_ch.result b/mysql-test/r/ctype_cp1250_ch.result new file mode 100644 index 00000000000..62936b84caf --- /dev/null +++ b/mysql-test/r/ctype_cp1250_ch.result @@ -0,0 +1,9 @@ +SHOW COLLATION LIKE 'cp1250_czech_cs'; +Collation Charset Id Default Compiled Sortlen +cp1250_czech_cs cp1250 34 Yes 2 +CREATE TABLE t1 (a char(16)) character set cp1250 collate cp1250_czech_cs; +INSERT INTO t1 VALUES (''); +SELECT a, length(a), a='', a=' ', a=' ' FROM t1; +a length(a) a='' a=' ' a=' ' + 0 1 1 1 +DROP TABLE t1; diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result index 3793e962d40..647f8c6236c 100644 --- a/mysql-test/r/ctype_cp1251.result +++ b/mysql-test/r/ctype_cp1251.result @@ -54,3 +54,19 @@ select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1; collation(a) collation(b) collation(binary 'ccc') cp1251_bin binary binary drop table t1; +create table t1 ( +a varchar(16) character set cp1251 collate cp1251_bin not null, +b int(10) default null, +primary key(a) +) charset=cp1251; +insert into t1 (a) values ('air'), +('we'),('g'),('we_toshko'), ('s0urce'),('we_ivo'),('we_iliyan'), +('we_martin'),('vw_grado'),('vw_vasko'),('tn_vili'),('tn_kalina'), +('tn_fakira'),('vw_silvia'),('vw_starshi'),('vw_geo'),('vw_b0x1'); +select * from t1 where a like 'we_%'; +a b +we_iliyan NULL +we_ivo NULL +we_martin NULL +we_toshko NULL +drop table t1; diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result index cd804939a75..21c40e24fe2 100644 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@ -325,3 +325,8 @@ latin1_bin 6109 latin1_bin 61 latin1_bin 6120 drop table t1; +CREATE TABLE „a (a int); +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 '„a (a int)' at line 1 +SELECT '„a' as str; +str +„a diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result index 6df9d963498..f57d8c191bf 100644 --- a/mysql-test/r/ctype_latin1_de.result +++ b/mysql-test/r/ctype_latin1_de.result @@ -326,3 +326,15 @@ latin1_german2_ci 6109 latin1_german2_ci 61 latin1_german2_ci 6120 drop table t1; +SET NAMES latin1; +CREATE TABLE t1 ( +col1 varchar(255) NOT NULL default '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 collate latin1_german2_ci; +INSERT INTO t1 VALUES ('ß'),('ss'),('ss'); +ALTER TABLE t1 ADD KEY ifword(col1); +SELECT * FROM t1 WHERE col1='ß' ORDER BY col1, BINARY col1; +col1 +ss +ss +ß +DROP TABLE t1; diff --git a/mysql-test/r/ctype_sjis.result b/mysql-test/r/ctype_sjis.result index 1f414f89e20..98e5992f374 100644 --- a/mysql-test/r/ctype_sjis.result +++ b/mysql-test/r/ctype_sjis.result @@ -91,3 +91,7 @@ sjis_bin 6109 sjis_bin 61 sjis_bin 6120 drop table t1; +SET NAMES sjis; +SELECT HEX('²“‘@\Œ\') FROM DUAL; +HEX('²“‘@_Œ\') +8DB2939181408C5C diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 415ed33ad40..13105e2276c 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -861,3 +861,6 @@ user c one <one> two <two> DROP TABLE t1; +select convert(_koi8r'É' using utf8) < convert(_koi8r'Ê' using utf8); +convert(_koi8r'É' using utf8) < convert(_koi8r'Ê' using utf8) +1 diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 61d745d0236..b4d9a921178 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -339,3 +339,22 @@ select distinct sum(b) from (select a,b from t1) y group by a; sum(b) 4 drop table t1; +create table t1(a int); +create table t2(a int); +create table t3(a int); +insert into t1 values(1),(1); +insert into t2 values(2),(2); +insert into t3 values(3),(3); +select * from t1 union distinct select * from t2 union all select * from t3; +a +1 +2 +3 +3 +select * from (select * from t1 union distinct select * from t2 union all select * from t3) X; +a +1 +2 +3 +3 +drop table t1, t2, t3; diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 67644a68598..dd5b0407eb3 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -402,6 +402,12 @@ select count(*) from t1; count(*) 1 drop table t1; +CREATE TABLE t1 ( a TEXT, FULLTEXT (a) ); +INSERT INTO t1 VALUES ('testing ft_nlq_find_relevance'); +SELECT MATCH(a) AGAINST ('nosuchword') FROM t1; +MATCH(a) AGAINST ('nosuchword') +0 +DROP TABLE t1; create table t1 (a int primary key, b text, fulltext(b)); create table t2 (a int, b text); insert t1 values (1, "aaaa"), (2, "bbbb"); diff --git a/mysql-test/r/fulltext_cache.result b/mysql-test/r/fulltext_cache.result index 6a94189d8b4..80c101357c5 100644 --- a/mysql-test/r/fulltext_cache.result +++ b/mysql-test/r/fulltext_cache.result @@ -21,17 +21,17 @@ INSERT INTO t2 VALUES (5,2,'um copo de Vodka'); INSERT INTO t2 VALUES (6,2,'um chocolate Snickers'); INSERT INTO t2 VALUES (7,1,'Bife'); INSERT INTO t2 VALUES (8,1,'Pizza de Salmao'); -SELECT t1.q, t2.item, t2.id, round(MATCH t2.item AGAINST ('sushi'),8) +SELECT t1.q, t2.item, t2.id, round(MATCH t2.item AGAINST ('sushi'),6) as x FROM t1, t2 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; q item id x -aaaaaaaaa dsaass de sushi 1 1.92378664 -aaaaaaaaa dsaass de Bolo de Chocolate 2 0.00000000 -aaaaaaaaa dsaass de Feijoada 3 0.00000000 -aaaaaaaaa dsaass de Mousse de Chocolate 4 0.00000000 -ssde df s fsda sad er um copo de Vodka 5 0.00000000 -ssde df s fsda sad er um chocolate Snickers 6 0.00000000 -aaaaaaaaa dsaass de Bife 7 0.00000000 -aaaaaaaaa dsaass de Pizza de Salmao 8 0.00000000 +aaaaaaaaa dsaass de sushi 1 1.923787 +aaaaaaaaa dsaass de Bolo de Chocolate 2 0.000000 +aaaaaaaaa dsaass de Feijoada 3 0.000000 +aaaaaaaaa dsaass de Mousse de Chocolate 4 0.000000 +ssde df s fsda sad er um copo de Vodka 5 0.000000 +ssde df s fsda sad er um chocolate Snickers 6 0.000000 +aaaaaaaaa dsaass de Bife 7 0.000000 +aaaaaaaaa dsaass de Pizza de Salmao 8 0.000000 SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi' IN BOOLEAN MODE) as x FROM t1, t2 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; q item id x @@ -43,17 +43,17 @@ ssde df s fsda sad er um copo de Vodka 5 0 ssde df s fsda sad er um chocolate Snickers 6 0 aaaaaaaaa dsaass de Bife 7 0 aaaaaaaaa dsaass de Pizza de Salmao 8 0 -SELECT t1.q, t2.item, t2.id, round(MATCH t2.item AGAINST ('sushi'),8) +SELECT t1.q, t2.item, t2.id, round(MATCH t2.item AGAINST ('sushi'),6) as x FROM t2, t1 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; q item id x -aaaaaaaaa dsaass de sushi 1 1.92378664 -aaaaaaaaa dsaass de Bolo de Chocolate 2 0.00000000 -aaaaaaaaa dsaass de Feijoada 3 0.00000000 -aaaaaaaaa dsaass de Mousse de Chocolate 4 0.00000000 -ssde df s fsda sad er um copo de Vodka 5 0.00000000 -ssde df s fsda sad er um chocolate Snickers 6 0.00000000 -aaaaaaaaa dsaass de Bife 7 0.00000000 -aaaaaaaaa dsaass de Pizza de Salmao 8 0.00000000 +aaaaaaaaa dsaass de sushi 1 1.923787 +aaaaaaaaa dsaass de Bolo de Chocolate 2 0.000000 +aaaaaaaaa dsaass de Feijoada 3 0.000000 +aaaaaaaaa dsaass de Mousse de Chocolate 4 0.000000 +ssde df s fsda sad er um copo de Vodka 5 0.000000 +ssde df s fsda sad er um chocolate Snickers 6 0.000000 +aaaaaaaaa dsaass de Bife 7 0.000000 +aaaaaaaaa dsaass de Pizza de Salmao 8 0.000000 SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi' IN BOOLEAN MODE) as x FROM t2, t1 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; q item id x diff --git a/mysql-test/r/fulltext_order_by.result b/mysql-test/r/fulltext_order_by.result index c6c42fa2e8b..dc51454f1d5 100644 --- a/mysql-test/r/fulltext_order_by.result +++ b/mysql-test/r/fulltext_order_by.result @@ -62,6 +62,14 @@ a rel 6 0 4 1 7 1 +alter table t1 add key m (message); +explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 fulltext message message 0 1 Using where; Using filesort +SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc; +message +steve is cool +steve drop table t1; CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY, diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index fa645700875..c22d0d3bec8 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -733,6 +733,15 @@ one 2 two 2 three 1 drop table t1; +create table t1(a int, b datetime); +insert into t1 values (1, NOW()), (2, NOW()); +create table t2 select MAX(b) from t1 group by a; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `MAX(b)` datetime default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1, t2; create table t1(f1 datetime); insert into t1 values (now()); create table t2 select f2 from (select max(now()) f2 from t1) a; @@ -745,3 +754,29 @@ show columns from t2; Field Type Null Key Default Extra f2 datetime 0000-00-00 00:00:00 drop table t2, t1; +CREATE TABLE t1( +id int PRIMARY KEY, +a int, +b int, +INDEX i_b_id(a,b,id), +INDEX i_id(a,id) +); +INSERT INTO t1 VALUES +(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1); +SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6; +MAX(id) +NULL +DROP TABLE t1; +CREATE TABLE t1( +id int PRIMARY KEY, +a int, +b int, +INDEX i_id(a,id), +INDEX i_b_id(a,b,id) +); +INSERT INTO t1 VALUES +(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1); +SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6; +MAX(id) +NULL +DROP TABLE t1; diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 88b1a5ea743..855473bdc08 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -464,97 +464,97 @@ select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE la ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_bin,EXPLICIT) for operation ' IN ' select collation(bin(130)), coercibility(bin(130)); collation(bin(130)) coercibility(bin(130)) -latin1_swedish_ci 3 +latin1_swedish_ci 4 select collation(oct(130)), coercibility(oct(130)); collation(oct(130)) coercibility(oct(130)) -latin1_swedish_ci 3 +latin1_swedish_ci 4 select collation(conv(130,16,10)), coercibility(conv(130,16,10)); collation(conv(130,16,10)) coercibility(conv(130,16,10)) -latin1_swedish_ci 3 +latin1_swedish_ci 4 select collation(hex(130)), coercibility(hex(130)); collation(hex(130)) coercibility(hex(130)) -latin1_swedish_ci 3 +latin1_swedish_ci 4 select collation(char(130)), coercibility(hex(130)); collation(char(130)) coercibility(hex(130)) -latin1_swedish_ci 3 +latin1_swedish_ci 4 select collation(format(130,10)), coercibility(format(130,10)); collation(format(130,10)) coercibility(format(130,10)) -latin1_swedish_ci 3 +latin1_swedish_ci 4 select collation(lcase(_latin2'a')), coercibility(lcase(_latin2'a')); collation(lcase(_latin2'a')) coercibility(lcase(_latin2'a')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(ucase(_latin2'a')), coercibility(ucase(_latin2'a')); collation(ucase(_latin2'a')) coercibility(ucase(_latin2'a')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(left(_latin2'a',1)), coercibility(left(_latin2'a',1)); collation(left(_latin2'a',1)) coercibility(left(_latin2'a',1)) -latin2_general_ci 3 +latin2_general_ci 4 select collation(right(_latin2'a',1)), coercibility(right(_latin2'a',1)); collation(right(_latin2'a',1)) coercibility(right(_latin2'a',1)) -latin2_general_ci 3 +latin2_general_ci 4 select collation(substring(_latin2'a',1,1)), coercibility(substring(_latin2'a',1,1)); collation(substring(_latin2'a',1,1)) coercibility(substring(_latin2'a',1,1)) -latin2_general_ci 3 +latin2_general_ci 4 select collation(concat(_latin2'a',_latin2'b')), coercibility(concat(_latin2'a',_latin2'b')); collation(concat(_latin2'a',_latin2'b')) coercibility(concat(_latin2'a',_latin2'b')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(lpad(_latin2'a',4,_latin2'b')), coercibility(lpad(_latin2'a',4,_latin2'b')); collation(lpad(_latin2'a',4,_latin2'b')) coercibility(lpad(_latin2'a',4,_latin2'b')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(rpad(_latin2'a',4,_latin2'b')), coercibility(rpad(_latin2'a',4,_latin2'b')); collation(rpad(_latin2'a',4,_latin2'b')) coercibility(rpad(_latin2'a',4,_latin2'b')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b')); collation(concat_ws(_latin2'a',_latin2'b')) coercibility(concat_ws(_latin2'a',_latin2'b')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')), coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c')); collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')) coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')), coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' ')); collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')) coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' ')) -binary 3 +binary 4 select collation(trim(_latin2' a ')), coercibility(trim(_latin2' a ')); collation(trim(_latin2' a ')) coercibility(trim(_latin2' a ')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(ltrim(_latin2' a ')), coercibility(ltrim(_latin2' a ')); collation(ltrim(_latin2' a ')) coercibility(ltrim(_latin2' a ')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(rtrim(_latin2' a ')), coercibility(rtrim(_latin2' a ')); collation(rtrim(_latin2' a ')) coercibility(rtrim(_latin2' a ')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(trim(LEADING _latin2' ' FROM _latin2'a')), coercibility(trim(LEADING _latin2'a' FROM _latin2'a')); collation(trim(LEADING _latin2' ' FROM _latin2'a')) coercibility(trim(LEADING _latin2'a' FROM _latin2'a')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(trim(TRAILING _latin2' ' FROM _latin2'a')), coercibility(trim(TRAILING _latin2'a' FROM _latin2'a')); collation(trim(TRAILING _latin2' ' FROM _latin2'a')) coercibility(trim(TRAILING _latin2'a' FROM _latin2'a')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(trim(BOTH _latin2' ' FROM _latin2'a')), coercibility(trim(BOTH _latin2'a' FROM _latin2'a')); collation(trim(BOTH _latin2' ' FROM _latin2'a')) coercibility(trim(BOTH _latin2'a' FROM _latin2'a')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(repeat(_latin2'a',10)), coercibility(repeat(_latin2'a',10)); collation(repeat(_latin2'a',10)) coercibility(repeat(_latin2'a',10)) -latin2_general_ci 3 +latin2_general_ci 4 select collation(reverse(_latin2'ab')), coercibility(reverse(_latin2'ab')); collation(reverse(_latin2'ab')) coercibility(reverse(_latin2'ab')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(quote(_latin2'ab')), coercibility(quote(_latin2'ab')); collation(quote(_latin2'ab')) coercibility(quote(_latin2'ab')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(soundex(_latin2'ab')), coercibility(soundex(_latin2'ab')); collation(soundex(_latin2'ab')) coercibility(soundex(_latin2'ab')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(substring(_latin2'ab',1)), coercibility(substring(_latin2'ab',1)); collation(substring(_latin2'ab',1)) coercibility(substring(_latin2'ab',1)) -latin2_general_ci 3 +latin2_general_ci 4 select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef')); collation(insert(_latin2'abcd',2,3,_latin2'ef')) coercibility(insert(_latin2'abcd',2,3,_latin2'ef')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B')); collation(replace(_latin2'abcd',_latin2'b',_latin2'B')) coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B')) -latin2_general_ci 3 +latin2_general_ci 4 select collation(encode('abcd','ab')), coercibility(encode('abcd','ab')); collation(encode('abcd','ab')) coercibility(encode('abcd','ab')) -binary 3 +binary 4 create table t1 select bin(130), @@ -733,3 +733,24 @@ WHERE a = CONV('e251273eb74a8ee3', 16, 10); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 DROP TABLE t1; +SELECT CHAR(NULL,121,83,81,'76') as my_column; +my_column +ySQL +SELECT CHAR_LENGTH(CHAR(NULL,121,83,81,'76')) as my_column; +my_column +4 +CREATE TABLE t1 (id int PRIMARY KEY, str char(255) NOT NULL); +CREATE TABLE t2 (id int NOT NULL UNIQUE); +INSERT INTO t2 VALUES (1),(2); +INSERT INTO t1 VALUES (1, aes_encrypt('foo', 'bar')); +INSERT INTO t1 VALUES (2, 'not valid'); +SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id; +id aes_decrypt(str, 'bar') +1 foo +2 NULL +SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id +ORDER BY t1.id; +id aes_decrypt(str, 'bar') +1 foo +2 NULL +DROP TABLE t1, t2; diff --git a/mysql-test/r/func_system.result b/mysql-test/r/func_system.result index d3db2cc5151..d0ac9d781a4 100644 --- a/mysql-test/r/func_system.result +++ b/mysql-test/r/func_system.result @@ -68,3 +68,11 @@ drop table t1; select TRUE,FALSE,NULL; TRUE FALSE NULL 1 0 NULL +create table t1 (a char(10)) character set latin1; +select * from t1 where a=version(); +a +select * from t1 where a=database(); +a +select * from t1 where a=user(); +a +drop table t1; diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result index 6d8bdbaf8f9..8200d112709 100644 --- a/mysql-test/r/grant2.result +++ b/mysql-test/r/grant2.result @@ -63,3 +63,19 @@ delete from mysql.columns_priv where user like 'mysqltest\_%'; flush privileges; drop database mysqltest; use test; +create database mysqltest_1; +create table mysqltest_1.t1 (i int); +insert into mysqltest_1.t1 values (1),(2),(3); +GRANT ALL ON mysqltest_1.t1 TO mysqltest_1@'127.0.0.0/255.0.0.0'; +show grants for current_user(); +Grants for mysqltest_1@127.0.0.0/255.0.0.0 +GRANT USAGE ON *.* TO 'mysqltest_1'@'127.0.0.0/255.0.0.0' +GRANT ALL PRIVILEGES ON `mysqltest_1`.`t1` TO 'mysqltest_1'@'127.0.0.0/255.0.0.0' +select * from t1; +i +1 +2 +3 +REVOKE ALL ON mysqltest_1.t1 FROM mysqltest_1@'127.0.0.0/255.0.0.0'; +drop table mysqltest_1.t1; +drop database mysqltest_1; diff --git a/mysql-test/r/have_cp1250_ch.require b/mysql-test/r/have_cp1250_ch.require new file mode 100644 index 00000000000..2eb834d97e2 --- /dev/null +++ b/mysql-test/r/have_cp1250_ch.require @@ -0,0 +1,2 @@ +Collation Charset Id Default Compiled Sortlen +cp1250_czech_cs cp1250 34 Yes 2 diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index 753dc2cd749..ff7ec1ba73f 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -140,10 +140,10 @@ a b c 5 6 130 TRUNCATE TABLE t1; INSERT t1 VALUES (1,2,10), (3,4,20); -CREATE TABLE t2 (x INT, y INT, z INT, d INT); +CREATE TABLE t2 (a INT, b INT, c INT, d INT); INSERT t2 VALUES (5,6,30,1), (7,4,40,1), (8,9,60,1); INSERT t2 VALUES (2,1,11,2), (7,4,40,2); -INSERT t1 SELECT x,y,z FROM t2 WHERE d=1 ON DUPLICATE KEY UPDATE c=c+100; +INSERT t1 SELECT a,b,c FROM t2 WHERE d=1 ON DUPLICATE KEY UPDATE c=c+100; SELECT * FROM t1; a b c 1 2 10 @@ -157,7 +157,7 @@ a b c 3 4 120 5 0 30 8 9 60 -INSERT t1 SELECT x,y,z FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a); +INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a); SELECT *, VALUES(a) FROM t1; a b c VALUES(a) 1 2 10 NULL diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index c045aa0d00a..b035f88da41 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -683,6 +683,26 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 drop table t1, t2, t3; create table t1 ( +a int(11), +b char(10), +key (a) +); +insert into t1 (a) values (1),(2),(3),(4); +create table t2 (a int); +select * from t1 left join t2 on t1.a=t2.a where not (t2.a <=> t1.a); +a b a +1 NULL NULL +2 NULL NULL +3 NULL NULL +4 NULL NULL +select * from t1 left join t2 on t1.a=t2.a having not (t2.a <=> t1.a); +a b a +1 NULL NULL +2 NULL NULL +3 NULL NULL +4 NULL NULL +drop table t1,t2; +create table t1 ( match_id tinyint(3) unsigned not null auto_increment, home tinyint(3) unsigned default '0', unique key match_id (match_id), @@ -816,3 +836,31 @@ id text_id text_data 1 0 0-SV 2 10 10-SV DROP TABLE invoice, text_table; +CREATE TABLE t1 (a int PRIMARY KEY, b int); +CREATE TABLE t2 (a int PRIMARY KEY, b int); +INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2); +INSERT INTO t2 VALUES (1,2), (2,2); +SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +a b a b +1 1 1 2 +2 1 2 2 +3 1 NULL NULL +4 2 NULL NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1; +a b a b +1 1 1 2 +2 1 2 2 +3 1 NULL NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a +WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1); +a b a b +1 1 1 2 +2 1 2 2 +3 1 NULL NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1)); +a b a b +1 1 1 2 +2 1 2 2 +3 1 NULL NULL +4 2 NULL NULL +DROP TABLE t1,t2; diff --git a/mysql-test/r/lowercase_table2.result b/mysql-test/r/lowercase_table2.result index a79b6b04063..8361b66817a 100644 --- a/mysql-test/r/lowercase_table2.result +++ b/mysql-test/r/lowercase_table2.result @@ -141,3 +141,21 @@ select * from T1; a b 1 abc drop table T1; +create database mysqltest_LC2; +use mysqltest_LC2; +create table myUC (i int); +insert into myUC values (1),(2),(3); +select * from myUC; +i +1 +2 +3 +use test; +drop database mysqltest_LC2; +create database mysqltest_LC2; +use mysqltest_LC2; +create table myUC (i int); +select * from myUC; +i +use test; +drop database mysqltest_LC2; diff --git a/mysql-test/r/lowercase_table_grant.result b/mysql-test/r/lowercase_table_grant.result new file mode 100644 index 00000000000..3889bd418db --- /dev/null +++ b/mysql-test/r/lowercase_table_grant.result @@ -0,0 +1,23 @@ +use mysql; +create database MYSQLtest; +grant all on MySQLtest.* to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +select * from db where user = 'mysqltest_1'; +Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv +localhost mysqltest mysqltest_1 Y Y Y Y Y Y N Y Y Y Y Y +update db set db = 'MYSQLtest' where db = 'mysqltest' and user = 'mysqltest_1' and host = 'localhost'; +flush privileges; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +select * from db where user = 'mysqltest_1'; +Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv +localhost MYSQLtest mysqltest_1 Y Y Y Y Y Y N Y Y Y Y Y +delete from db where db = 'MYSQLtest' and user = 'mysqltest_1' and host = 'localhost'; +flush privileges; +drop user mysqltest_1@localhost; +drop database MYSQLtest; diff --git a/mysql-test/r/multi_statement.result b/mysql-test/r/multi_statement.result index 4451b0a355e..3a8d86bf349 100644 --- a/mysql-test/r/multi_statement.result +++ b/mysql-test/r/multi_statement.result @@ -31,3 +31,20 @@ select 5'abcd' select 'finish'; finish finish +flush status; +create table t1 (i int); +insert into t1 values (1); +select * from t1 where i = 1; +insert into t1 values (2),(3),(4); +select * from t1 where i = 2; +select * from t1 where i = 3|||| +i +1 +i +2 +i +3 +show status like 'Slow_queries'|||| +Variable_name Value +Slow_queries 2 +drop table t1|||| diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index d155a14bb60..eaed7c620e3 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -555,3 +555,21 @@ select count(*) from t1 where a is null; count(*) 2 drop table t1; +create table t1 (c1 int, index(c1)); +create table t2 (c1 int, index(c1)) engine=merge union=(t1); +insert into t1 values (1); +flush tables; +select * from t2; +c1 +1 +flush tables; +truncate table t1; +insert into t1 values (1); +flush tables; +select * from t2; +c1 +1 +truncate table t1; +ERROR HY000: MyISAM table 't1' is in use (most likely by a MERGE table). Try FLUSH TABLES. +insert into t1 values (1); +drop table t1,t2; diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 8f2294caa48..69ca9486d2f 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -61,6 +61,7 @@ INSERT INTO `t1` VALUES ('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456) /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `a` decimal(10,5) default NULL, @@ -80,10 +81,12 @@ UNLOCK TABLES; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE TABLE `t1` ( `a` decimal(10,5) default NULL, `b` float default NULL @@ -94,6 +97,7 @@ INSERT INTO `t1` VALUES ('1.23450',2.3456),('1.23450',2.3456),('1.23450',2.3456) /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; CREATE TABLE t1(a int, b text, c varchar(3)); @@ -150,6 +154,7 @@ INSERT INTO t1 VALUES (_koi8r x'C1C2C3C4C5'), (NULL); /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `a` varchar(255) default NULL @@ -169,6 +174,7 @@ UNLOCK TABLES; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; CREATE TABLE t1 (a int) ENGINE=MYISAM; @@ -176,6 +182,7 @@ INSERT INTO t1 VALUES (1), (2); /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,MYSQL40' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `a` int(11) default NULL @@ -191,10 +198,12 @@ UNLOCK TABLES; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,MYSQL323' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `a` int(11) default NULL @@ -210,6 +219,7 @@ UNLOCK TABLES; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; create table ```a` (i int); @@ -226,6 +236,7 @@ create table t1(a int); /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `a` int(11) default NULL @@ -243,10 +254,12 @@ UNLOCK TABLES; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ANSI' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS "t1"; CREATE TABLE "t1" ( "a" int(11) default NULL @@ -261,6 +274,7 @@ UNLOCK TABLES; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; set global sql_mode='ANSI_QUOTES'; @@ -271,6 +285,7 @@ set global sql_mode='ANSI_QUOTES'; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `a` int(11) default NULL @@ -288,10 +303,12 @@ UNLOCK TABLES; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ANSI' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS "t1"; CREATE TABLE "t1" ( "a" int(11) default NULL @@ -306,6 +323,7 @@ UNLOCK TABLES; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; set global sql_mode=''; drop table t1; @@ -317,6 +335,7 @@ insert into t1 values (1),(2),(3); /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `a` int(11) default NULL @@ -327,6 +346,7 @@ CREATE TABLE `t1` ( /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; 1 2 @@ -340,6 +360,7 @@ drop table t1; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */; @@ -351,6 +372,7 @@ USE `test`; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; create database mysqldump_test_db character set latin2 collate latin2_bin; @@ -361,6 +383,7 @@ create database mysqldump_test_db character set latin2 collate latin2_bin; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqldump_test_db` /*!40100 DEFAULT CHARACTER SET latin2 COLLATE latin2_bin */; @@ -372,6 +395,7 @@ USE `mysqldump_test_db`; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; drop database mysqldump_test_db; CREATE TABLE t1 (a CHAR(10)); @@ -384,6 +408,7 @@ INSERT INTO t1 VALUES (_latin1 'ÄÖÜß'); /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `a` char(10) default NULL @@ -402,29 +427,12 @@ UNLOCK TABLES; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,MYSQL323' */; -DROP TABLE IF EXISTS `t1`; -CREATE TABLE `t1` ( - `a` char(10) default NULL -) TYPE=MyISAM; - - -/*!40000 ALTER TABLE `t1` DISABLE KEYS */; -LOCK TABLES `t1` WRITE; -INSERT INTO `t1` VALUES ('ÄÖÜß'); -UNLOCK TABLES; -/*!40000 ALTER TABLE `t1` ENABLE KEYS */; - -/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; -/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; -/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; - -/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; -/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; -/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,MYSQL323' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `a` char(10) default NULL @@ -440,10 +448,12 @@ UNLOCK TABLES; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,MYSQL323' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `a` char(10) default NULL @@ -459,10 +469,12 @@ UNLOCK TABLES; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,MYSQL323' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `a` char(10) default NULL @@ -478,6 +490,7 @@ UNLOCK TABLES; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; CREATE TABLE t1 (a int); @@ -492,6 +505,7 @@ INSERT INTO t2 VALUES (4),(5),(6); /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS `t2`; CREATE TABLE `t2` ( `a` int(11) default NULL @@ -510,6 +524,39 @@ UNLOCK TABLES; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; DROP TABLE t2; +CREATE TABLE t1 (`b` blob); +INSERT INTO `t1` VALUES (0x602010000280100005E71A); + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS `t1`; +CREATE TABLE `t1` ( + `b` blob +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + + +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; +LOCK TABLES `t1` WRITE; +INSERT INTO `t1` VALUES (0x602010000280100005E71A); +UNLOCK TABLES; +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +DROP TABLE t1; diff --git a/mysql-test/r/ndb_alter_table.result b/mysql-test/r/ndb_alter_table.result index f899d254243..88ac04db111 100644 --- a/mysql-test/r/ndb_alter_table.result +++ b/mysql-test/r/ndb_alter_table.result @@ -170,3 +170,17 @@ c 4 5 drop table t1; +create table t1 ( a int primary key, b varchar(10), c varchar(10), index (b) ) +engine=ndb; +insert into t1 values (1,'one','one'), (2,'two','two'), (3,'three','three'); +create index c on t1(c); +select * from t1 where b = 'two'; +a b c +2 two two +alter table t1 drop index c; +select * from t1 where b = 'two'; +ERROR 42S02: Table 'test.t1' doesn't exist +select * from t1 where b = 'two'; +a b c +2 two two +drop table t1; diff --git a/mysql-test/r/ndb_index_ordered.result b/mysql-test/r/ndb_index_ordered.result index 12438f247c3..f5794d477f3 100644 --- a/mysql-test/r/ndb_index_ordered.result +++ b/mysql-test/r/ndb_index_ordered.result @@ -445,6 +445,89 @@ select count(*)-9 from t1 use index (ts) where ts <= '2001-01-01 23:59:59'; count(*)-9 0 drop table t1; +create table t1 ( +a int primary key, +s decimal(12), +t decimal(12, 5), +u decimal(12) unsigned, +v decimal(12, 5) unsigned, +key (s), +key (t), +key (u), +key (v) +) engine=ndb; +insert into t1 values +( 0, -000000000007, -0000061.00003, 000000000061, 0000965.00042), +( 1, -000000000007, -0000061.00042, 000000000061, 0000965.00003), +( 2, -071006035767, 4210253.00024, 000000000001, 0000001.84488), +( 3, 000000007115, 0000000.77607, 000077350625, 0000018.00013), +( 4, -000000068391, -0346486.00000, 000000005071, 0005334.00002), +( 5, -521579890459, -1936874.00001, 000000000154, 0000003.00018), +( 6, -521579890459, -1936874.00018, 000000000154, 0000003.00001), +( 7, 000000000333, 0000051.39140, 000000907958, 0788643.08374), +( 8, 000042731229, 0000009.00000, 000000000009, 6428667.00000), +( 9, -000008159769, 0000918.00004, 000096951421, 7607730.00008); +select count(*)- 5 from t1 use index (s) where s < -000000000007; +count(*)- 5 +0 +select count(*)- 7 from t1 use index (s) where s <= -000000000007; +count(*)- 7 +0 +select count(*)- 2 from t1 use index (s) where s = -000000000007; +count(*)- 2 +0 +select count(*)- 5 from t1 use index (s) where s >= -000000000007; +count(*)- 5 +0 +select count(*)- 3 from t1 use index (s) where s > -000000000007; +count(*)- 3 +0 +select count(*)- 4 from t1 use index (t) where t < -0000061.00003; +count(*)- 4 +0 +select count(*)- 5 from t1 use index (t) where t <= -0000061.00003; +count(*)- 5 +0 +select count(*)- 1 from t1 use index (t) where t = -0000061.00003; +count(*)- 1 +0 +select count(*)- 6 from t1 use index (t) where t >= -0000061.00003; +count(*)- 6 +0 +select count(*)- 5 from t1 use index (t) where t > -0000061.00003; +count(*)- 5 +0 +select count(*)- 2 from t1 use index (u) where u < 000000000061; +count(*)- 2 +0 +select count(*)- 4 from t1 use index (u) where u <= 000000000061; +count(*)- 4 +0 +select count(*)- 2 from t1 use index (u) where u = 000000000061; +count(*)- 2 +0 +select count(*)- 8 from t1 use index (u) where u >= 000000000061; +count(*)- 8 +0 +select count(*)- 6 from t1 use index (u) where u > 000000000061; +count(*)- 6 +0 +select count(*)- 5 from t1 use index (v) where v < 0000965.00042; +count(*)- 5 +0 +select count(*)- 6 from t1 use index (v) where v <= 0000965.00042; +count(*)- 6 +0 +select count(*)- 1 from t1 use index (v) where v = 0000965.00042; +count(*)- 1 +0 +select count(*)- 5 from t1 use index (v) where v >= 0000965.00042; +count(*)- 5 +0 +select count(*)- 4 from t1 use index (v) where v > 0000965.00042; +count(*)- 4 +0 +drop table t1; create table t1(a int primary key, b int not null, index(b)); insert into t1 values (1,1), (2,2); set autocommit=0; diff --git a/mysql-test/r/ndb_multi.result b/mysql-test/r/ndb_multi.result new file mode 100644 index 00000000000..4a2389cd1ff --- /dev/null +++ b/mysql-test/r/ndb_multi.result @@ -0,0 +1,49 @@ +drop table if exists t1, t2, t3, t4; +flush status; +create table t1 (a int) engine=ndbcluster; +create table t2 (a int) engine=ndbcluster; +insert into t1 value (2); +insert into t2 value (3); +select * from t1; +a +2 +select * from t2; +a +3 +show status like 'handler_discover%'; +Variable_name Value +Handler_discover 0 +flush status; +select * from t1; +a +2 +update t1 set a=3 where a=2; +show status like 'handler_discover%'; +Variable_name Value +Handler_discover 1 +create table t3 (a int not null primary key, b varchar(22), +c int, last_col text) engine=ndb; +insert into t3 values(1, 'Hi!', 89, 'Longtext column'); +create table t4 (pk int primary key, b int) engine=ndb; +select * from t1; +a +3 +select * from t3; +a b c last_col +1 Hi! 89 Longtext column +show status like 'handler_discover%'; +Variable_name Value +Handler_discover 1 +show tables like 't4'; +Tables_in_test (t4) +t4 +show status like 'handler_discover%'; +Variable_name Value +Handler_discover 2 +show tables; +Tables_in_test +t1 +t2 +t3 +t4 +drop table t1, t2, t3, t4; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 4cb32fa4644..89c369a51e8 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -129,7 +129,7 @@ FOUND_ROWS() 1 execute stmt1; FOUND_ROWS() -0 +1 deallocate prepare stmt1; drop table t1; create table t1 @@ -482,3 +482,15 @@ execute stmt; pnum deallocate prepare stmt; drop table t1, t2; +prepare stmt from "SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0"; +execute stmt; +foo +SELECT FOUND_ROWS(); +FOUND_ROWS() +2 +execute stmt; +foo +SELECT FOUND_ROWS(); +FOUND_ROWS() +2 +deallocate prepare stmt; diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 2356989eaf6..ec4aa528a7f 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -1,5 +1,6 @@ drop table if exists t5, t6, t7, t8; drop database if exists mysqltest ; +drop database if exists client_test_db; test_sequence ------ basic tests ------ drop table if exists t1, t9 ; diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 9f61b029391..5fe00e70d3d 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -922,4 +922,24 @@ group_concat(a) 12345678901234567890 set group_concat_max_len=default; drop table t1; -SET GLOBAL query_cache_size=0; +set global query_cache_size=1024*1024; +flush query cache; +create table t1 ( a int ); +insert into t1 values (1); +select a from t1; +a +1 +select a from t1; +a +1 +show status like 'qcache_queries_in_cache'; +Variable_name Value +Qcache_queries_in_cache 1 +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +show status like 'qcache_queries_in_cache'; +Variable_name Value +Qcache_queries_in_cache 0 +drop table t1; +set GLOBAL query_cache_size=0; diff --git a/mysql-test/r/rpl_charset.result b/mysql-test/r/rpl_charset.result index cab41344238..292cfb19175 100644 --- a/mysql-test/r/rpl_charset.result +++ b/mysql-test/r/rpl_charset.result @@ -207,3 +207,10 @@ select hex(c1), hex(c2) from t1; hex(c1) hex(c2) CDF32C20E7E020F0FBE1E0EBEAF3 CDF32C20E7E020F0FBE1E0EBEAF3 drop table t1; +create table `t1` ( +`pk` varchar(10) not null default '', +primary key (`pk`) +) engine=myisam default charset=latin1; +set @p=_latin1 'test'; +update t1 set pk='test' where pk=@p; +drop table t1; diff --git a/mysql-test/r/rpl_deadlock.result b/mysql-test/r/rpl_deadlock.result new file mode 100644 index 00000000000..366d18f3e05 --- /dev/null +++ b/mysql-test/r/rpl_deadlock.result @@ -0,0 +1,81 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +create table t1 (a int not null, key(a)) engine=innodb; +create table t2 (a int not null, key(a)) engine=innodb; +create table t3 (a int) engine=innodb; +create table t4 (a int) engine=innodb; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL default '0', + KEY `a` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) NOT NULL default '0', + KEY `a` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +stop slave; +begin; +insert into t3 select * from t2 for update; +insert into t1 values(1); +commit; +begin; +select * from t1 for update; +a +start slave; +insert into t2 values(22); +commit; +select * from t1; +a +1 +select * from t2; +a +22 +show slave status; +Slave_IO_State 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 Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master +# 127.0.0.1 root MASTER_MYPORT 1 master-bin.000001 13110 # # master-bin.000001 Yes Yes 0 0 13110 # None 0 No # +stop slave; +change master to master_log_pos=401; +begin; +select * from t2 for update; +a +22 +start slave; +commit; +select * from t1; +a +1 +1 +select * from t2; +a +22 +show slave status; +Slave_IO_State 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 Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master +# 127.0.0.1 root MASTER_MYPORT 1 master-bin.000001 13110 # # master-bin.000001 Yes Yes 0 0 13110 # None 0 No # +set global max_relay_log_size=0; +stop slave; +change master to master_log_pos=401; +begin; +select * from t2 for update; +a +22 +start slave; +commit; +select * from t1; +a +1 +1 +1 +select * from t2; +a +22 +show slave status; +Slave_IO_State 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 Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master +# 127.0.0.1 root MASTER_MYPORT 1 master-bin.000001 13110 # # master-bin.000001 Yes Yes 0 0 13110 # None 0 No # +drop table t1,t2; diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result index 8001cb59a35..8482f631553 100644 --- a/mysql-test/r/rpl_insert_id.result +++ b/mysql-test/r/rpl_insert_id.result @@ -68,3 +68,8 @@ b c 9 13 drop table t1; drop table t2; +SET TIMESTAMP=1000000000; +CREATE TABLE t1 ( a INT UNIQUE ); +SET FOREIGN_KEY_CHECKS=0; +INSERT INTO t1 VALUES (1),(1); +ERROR 23000: Duplicate entry '1' for key 1 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 70ac33964ad..9252b3dc689 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2400,3 +2400,48 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 1 SIMPLE t2 ref a a 23 test.t1.a 2 DROP TABLE t1, t2; +CREATE TABLE t1 ( city char(30) ); +INSERT INTO t1 VALUES ('London'); +INSERT INTO t1 VALUES ('Paris'); +SELECT * FROM t1 WHERE city='London'; +city +London +SELECT * FROM t1 WHERE city='london'; +city +London +EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE city='London' AND city='london'; +city +London +EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; +city +London +DROP TABLE t1; +create table t1 (a int(11) unsigned, b int(11) unsigned); +insert into t1 values (1,0), (1,1), (1,2); +select a-b from t1 order by 1; +a-b +0 +1 +18446744073709551615 +select a-b , (a-b < 0) from t1 order by 1; +a-b (a-b < 0) +0 0 +1 0 +18446744073709551615 0 +select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0; +d (a-b >= 0) b +1 1 0 +0 1 1 +18446744073709551615 1 2 +select cast((a - b) as unsigned) from t1 order by 1; +cast((a - b) as unsigned) +0 +1 +18446744073709551615 +drop table t1; diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result index 665277f68f2..b8178bbeb52 100644 --- a/mysql-test/r/select_found.result +++ b/mysql-test/r/select_found.result @@ -246,6 +246,31 @@ SELECT FOUND_ROWS(); FOUND_ROWS() 0 DROP TABLE t1; +SELECT 'foo'; +foo +foo +SELECT FOUND_ROWS(); +FOUND_ROWS() +1 +SELECT SQL_CALC_FOUND_ROWS 'foo'; +foo +foo +SELECT FOUND_ROWS(); +FOUND_ROWS() +1 +SELECT SQL_CALC_FOUND_ROWS 'foo' limit 0; +foo +SELECT FOUND_ROWS(); +FOUND_ROWS() +1 +SELECT FOUND_ROWS(); +FOUND_ROWS() +1 +SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0; +foo +SELECT FOUND_ROWS(); +FOUND_ROWS() +2 CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,2), (1,3), (1,4), (1,5); SELECT SQL_CALC_FOUND_ROWS DISTINCT 'a' FROM t1 GROUP BY b LIMIT 2; @@ -254,3 +279,4 @@ a SELECT FOUND_ROWS(); FOUND_ROWS() 1 +DROP TABLE t1; diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index 766ee8c0e14..7a29db42dd9 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -78,4 +78,19 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref b b 21 test.t1.b 6 Using where SET MAX_SEEKS_FOR_KEY=DEFAULT; drop table t1; +create table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +set local max_join_size=8; +select * from (select * from t1) x; +ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay +set local max_join_size=1; +select * from (select * from t1 a, t1 b) x; +ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay +set local max_join_size=1; +select * from (select 1 union select 2 union select 3) x; +ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay +drop table t1; SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT; diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 37531f05f43..0d580ae2505 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -250,9 +250,11 @@ type_bool type_tiny type_short type_mediumint type_bigint type_decimal type_nume drop table t1; create table t1 (a int not null); create table t2 select max(a) from t1; +Warnings: +Warning 1263 Data truncated; NULL supplied to NOT NULL column 'max(a)' at row 1 show columns from t2; Field Type Null Key Default Extra -max(a) bigint(20) YES NULL +max(a) int(11) 0 drop table t1,t2; create table t1 (c decimal, d double, f float, r real); show columns from t1; diff --git a/mysql-test/r/skip_name_resolve.result b/mysql-test/r/skip_name_resolve.result new file mode 100644 index 00000000000..d8d873699a5 --- /dev/null +++ b/mysql-test/r/skip_name_resolve.result @@ -0,0 +1,7 @@ +GRANT ALL ON test.* TO mysqltest_1@'127.0.0.1/255.255.255.255'; +SHOW GRANTS FOR mysqltest_1@'127.0.0.1/255.255.255.255'; +Grants for mysqltest_1@127.0.0.1/255.255.255.255 +GRANT USAGE ON *.* TO 'mysqltest_1'@'127.0.0.1/255.255.255.255' +GRANT ALL PRIVILEGES ON `test`.* TO 'mysqltest_1'@'127.0.0.1/255.255.255.255' +REVOKE ALL ON test.* FROM mysqltest_1@'127.0.0.1/255.255.255.255'; +DROP USER mysqltest_1@'127.0.0.1/255.255.255.255'; diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 08d75d8b562..26612ec81fd 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -84,3 +84,15 @@ t1 CREATE TABLE `t1` ( `b` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index ebb24159373..141878a7bf6 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -21,18 +21,18 @@ db CREATE TABLE `db` ( `Host` char(60) collate utf8_bin NOT NULL default '', `Db` char(64) collate utf8_bin NOT NULL default '', `User` char(16) collate utf8_bin NOT NULL default '', - `Select_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Insert_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Update_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Delete_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Create_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Drop_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Grant_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `References_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Index_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Alter_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Create_tmp_table_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Lock_tables_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Delete_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Create_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Drop_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Grant_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `References_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Index_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Alter_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Create_tmp_table_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Lock_tables_priv` enum('N','Y') character set utf8 NOT NULL default 'N', PRIMARY KEY (`Host`,`Db`,`User`), KEY `User` (`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges' @@ -41,18 +41,18 @@ Table Create Table host CREATE TABLE `host` ( `Host` char(60) collate utf8_bin NOT NULL default '', `Db` char(64) collate utf8_bin NOT NULL default '', - `Select_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Insert_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Update_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Delete_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Create_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Drop_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Grant_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `References_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Index_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Alter_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Create_tmp_table_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Lock_tables_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Delete_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Create_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Drop_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Grant_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `References_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Index_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Alter_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Create_tmp_table_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Lock_tables_priv` enum('N','Y') character set utf8 NOT NULL default 'N', PRIMARY KEY (`Host`,`Db`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Host privileges; Merged with database privileges' show create table user; @@ -61,28 +61,28 @@ user CREATE TABLE `user` ( `Host` varchar(60) collate utf8_bin NOT NULL default '', `User` varchar(16) collate utf8_bin NOT NULL default '', `Password` varchar(41) collate utf8_bin NOT NULL default '', - `Select_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Insert_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Update_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Delete_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Create_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Drop_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Reload_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Shutdown_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Process_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `File_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Grant_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `References_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Index_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Alter_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Show_db_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Super_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Create_tmp_table_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Lock_tables_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Execute_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Repl_slave_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `Repl_client_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', - `ssl_type` enum('','ANY','X509','SPECIFIED') collate utf8_bin NOT NULL default '', + `Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Delete_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Create_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Drop_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Reload_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Shutdown_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Process_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `File_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Grant_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `References_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Index_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Alter_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Show_db_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Super_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Create_tmp_table_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Lock_tables_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Execute_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Repl_slave_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `Repl_client_priv` enum('N','Y') character set utf8 NOT NULL default 'N', + `ssl_type` enum('','ANY','X509','SPECIFIED') character set utf8 NOT NULL default '', `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, @@ -97,7 +97,7 @@ func CREATE TABLE `func` ( `name` char(64) collate utf8_bin NOT NULL default '', `ret` tinyint(1) NOT NULL default '0', `dl` char(128) collate utf8_bin NOT NULL default '', - `type` enum('function','aggregate') collate utf8_bin NOT NULL default 'function', + `type` enum('function','aggregate') character set utf8 NOT NULL default 'function', PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User defined functions' show create table tables_priv; @@ -109,8 +109,8 @@ tables_priv CREATE TABLE `tables_priv` ( `Table_name` char(64) collate utf8_bin NOT NULL default '', `Grantor` char(77) collate utf8_bin NOT NULL default '', `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') collate utf8_bin NOT NULL default '', - `Column_priv` set('Select','Insert','Update','References') collate utf8_bin NOT NULL default '', + `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') character set utf8 NOT NULL default '', + `Column_priv` set('Select','Insert','Update','References') character set utf8 NOT NULL default '', PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`), KEY `Grantor` (`Grantor`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges' @@ -123,7 +123,7 @@ columns_priv CREATE TABLE `columns_priv` ( `Table_name` char(64) collate utf8_bin NOT NULL default '', `Column_name` char(64) collate utf8_bin NOT NULL default '', `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `Column_priv` set('Select','Insert','Update','References') collate utf8_bin NOT NULL default '', + `Column_priv` set('Select','Insert','Update','References') character set utf8 NOT NULL default '', PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges' show tables; diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index 8a0c74b3ae5..12eb2902966 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -499,7 +499,7 @@ charset(load_file('../../std_data/words.dat')), collation(load_file('../../std_data/words.dat')), coercibility(load_file('../../std_data/words.dat')); charset(load_file('../../std_data/words.dat')) collation(load_file('../../std_data/words.dat')) coercibility(load_file('../../std_data/words.dat')) -binary binary 3 +binary binary 4 explain extended select charset(load_file('../../std_data/words.dat')), collation(load_file('../../std_data/words.dat')), diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index 1f5a34917d7..c1cefe4b35d 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1,t2; SELECT 10,10.0,10.,.1e+2,100.0e-1; 10 10.0 10. .1e+2 100.0e-1 10 10.0 10 10 10 @@ -8,6 +8,15 @@ SELECT 6e-05, -6e-05, --6e-05, -6e-05+1.000000; SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1; 1e1 1.e1 1.0e1 1e+1 1.e+1 1.0e+1 1e-1 1.e-1 1.0e-1 10 10 10 10 10 10 0.1 0.1 0.1 +SELECT 0.001e+1,0.001e-1, -0.001e+01,-0.001e-01; +0.001e+1 0.001e-1 -0.001e+01 -0.001e-01 +0.01 0.0001 -0.01 -0.0001 +SELECT 123.23E+02,-123.23E-02,"123.23E+02"+0.0,"-123.23E-02"+0.0; +123.23E+02 -123.23E-02 "123.23E+02"+0.0 "-123.23E-02"+0.0 +12323 -1.2323 12323 -1.2323 +SELECT 2147483647E+02,21474836.47E+06; +2147483647E+02 21474836.47E+06 +214748364700 21474836470000 create table t1 (f1 float(24),f2 float(52)); show full columns from t1; Field Type Collation Null Key Default Extra Privileges Comment @@ -139,6 +148,9 @@ create table t1 (c20 char); insert into t1 values (5000.0); Warnings: Warning 1265 Data truncated for column 'c20' at row 1 +insert into t1 values (0.5e4); +Warnings: +Warning 1265 Data truncated for column 'c20' at row 1 drop table t1; create table t1 (f float(54)); ERROR 42000: Incorrect column specifier for column 'f' @@ -203,3 +215,23 @@ c 0.0002 2e-05 drop table t1; +CREATE TABLE t1 ( +reckey int unsigned NOT NULL, +recdesc varchar(50) NOT NULL, +PRIMARY KEY (reckey) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (108, 'Has 108 as key'); +INSERT INTO t1 VALUES (109, 'Has 109 as key'); +select * from t1 where reckey=108; +reckey recdesc +108 Has 108 as key +select * from t1 where reckey=1.08E2; +reckey recdesc +108 Has 108 as key +select * from t1 where reckey=109; +reckey recdesc +109 Has 109 as key +select * from t1 where reckey=1.09E2; +reckey recdesc +109 Has 109 as key +drop table t1; diff --git a/mysql-test/r/type_set.result b/mysql-test/r/type_set.result index 9c82f59fc69..5aedefda283 100644 --- a/mysql-test/r/type_set.result +++ b/mysql-test/r/type_set.result @@ -29,6 +29,12 @@ a A a,A a,A +select s from t1 order by concat(s); +s +A +a +a,A +a,A drop table t1; CREATE TABLE t1 (c set('ae','oe','ue','ss') collate latin1_german2_ci); INSERT INTO t1 VALUES ('ä'),('ö'),('ü'),('ß'); @@ -47,4 +53,16 @@ ss ss ae,oe,ue,ss ae,oe,ue,ss +SELECT c FROM t1 ORDER BY concat(c); +c +ae +ae +ae,oe,ue,ss +ae,oe,ue,ss +oe +oe +ss +ss +ue +ue DROP TABLE t1; diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 81846391795..d82c17b0fe0 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -123,7 +123,7 @@ drop table t1; set @a=_latin2'test'; select charset(@a),collation(@a),coercibility(@a); charset(@a) collation(@a) coercibility(@a) -latin2 latin2_general_ci 3 +latin2 latin2_general_ci 2 select @a=_latin2'TEST'; @a=_latin2'TEST' 1 @@ -133,12 +133,13 @@ select @a=_latin2'TEST' collate latin2_bin; set @a=_latin2'test' collate latin2_general_ci; select charset(@a),collation(@a),coercibility(@a); charset(@a) collation(@a) coercibility(@a) -latin2 latin2_general_ci 0 +latin2 latin2_general_ci 2 select @a=_latin2'TEST'; @a=_latin2'TEST' 1 select @a=_latin2'TEST' collate latin2_bin; -ERROR HY000: Illegal mix of collations (latin2_general_ci,EXPLICIT) and (latin2_bin,EXPLICIT) for operation '=' +@a=_latin2'TEST' collate latin2_bin +0 select charset(@a:=_latin2'test'); charset(@a:=_latin2'test') latin2 @@ -147,21 +148,22 @@ collation(@a:=_latin2'test') latin2_general_ci select coercibility(@a:=_latin2'test'); coercibility(@a:=_latin2'test') -3 +2 select collation(@a:=_latin2'test' collate latin2_bin); collation(@a:=_latin2'test' collate latin2_bin) latin2_bin select coercibility(@a:=_latin2'test' collate latin2_bin); coercibility(@a:=_latin2'test' collate latin2_bin) -0 +2 select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST'; (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' 0 select charset(@a),collation(@a),coercibility(@a); charset(@a) collation(@a) coercibility(@a) -latin2 latin2_bin 0 +latin2 latin2_bin 2 select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci; -ERROR HY000: Illegal mix of collations (latin2_bin,EXPLICIT) and (latin2_general_ci,EXPLICIT) for operation '=' +(@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci +1 create table t1 (a varchar(50)); reset master; SET TIMESTAMP=10000; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 6b700f7f6a2..633f7c139fd 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -482,3 +482,8 @@ SET GLOBAL MYISAM_DATA_POINTER_SIZE= 8; SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE'; Variable_name Value myisam_data_pointer_size 8 +SET GLOBAL table_cache=-1; +SHOW VARIABLES LIKE 'table_cache'; +Variable_name Value +table_cache 1 +SET GLOBAL table_cache=DEFAULT; diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index d883feb7ce2..e03d72351d1 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -1,6 +1,33 @@ drop table if exists t1, t2; SET SQL_WARNINGS=1; create table t1 (a int); +create table t1 (a int); +ERROR 42S01: Table 't1' already exists +show count(*) errors; +@@session.error_count +1 +show errors; +Level Code Message +Error 1050 Table 't1' already exists +show warnings; +Level Code Message +Error 1050 Table 't1' already exists +create table t2(a int) default charset qwerty; +ERROR 42000: Unknown character set: 'qwerty' +show count(*) errors; +@@session.error_count +1 +show errors; +Level Code Message +Error 1115 Unknown character set: 'qwerty' +create table t (i); +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 ')' at line 1 +show count(*) errors; +@@session.error_count +1 +show errors; +Level Code Message +Error 1064 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 insert into t1 values (1); insert into t1 values ("hej"); Warnings: diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index c509a4113f4..a26b78254e7 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -71,3 +71,36 @@ drop table t1; # atof() behaviour is different of different systems. to be fixed in 4.1 SELECT '0x8000000000000001'+0; +# Test for BUG#8562: joins over BIGINT UNSIGNED value + constant propagation +create table t1 ( + value64 bigint unsigned not null, + value32 integer not null, + primary key(value64, value32) +); + +create table t2 ( + value64 bigint unsigned not null, + value32 integer not null, + primary key(value64, value32) +); + +insert into t1 values(17156792991891826145, 1); +insert into t1 values( 9223372036854775807, 2); +insert into t2 values(17156792991891826145, 3); +insert into t2 values( 9223372036854775807, 4); + +select * from t1; +select * from t2; + +select * from t1, t2 where t1.value64=17156792991891826145 and +t2.value64=17156792991891826145; +select * from t1, t2 where t1.value64=17156792991891826145 and +t2.value64=t1.value64; + +select * from t1, t2 where t1.value64= 9223372036854775807 and +t2.value64=9223372036854775807; +select * from t1, t2 where t1.value64= 9223372036854775807 and +t2.value64=t1.value64; + +drop table t1, t2; + diff --git a/mysql-test/t/ctype_cp1250_ch.test b/mysql-test/t/ctype_cp1250_ch.test new file mode 100644 index 00000000000..06aea7b9979 --- /dev/null +++ b/mysql-test/t/ctype_cp1250_ch.test @@ -0,0 +1,12 @@ +-- source include/have_cp1250_ch.inc + +SHOW COLLATION LIKE 'cp1250_czech_cs'; + +# +# Bugs: #8840: Empty string comparison and character set 'cp1250' +# + +CREATE TABLE t1 (a char(16)) character set cp1250 collate cp1250_czech_cs; +INSERT INTO t1 VALUES (''); +SELECT a, length(a), a='', a=' ', a=' ' FROM t1; +DROP TABLE t1; diff --git a/mysql-test/t/ctype_cp1251.test b/mysql-test/t/ctype_cp1251.test index 66a8a5aa909..2d670ec3607 100644 --- a/mysql-test/t/ctype_cp1251.test +++ b/mysql-test/t/ctype_cp1251.test @@ -32,3 +32,17 @@ select * from t1 where lower(b)='bbb'; select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1; select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1; drop table t1; + +# Test for BUG#8560 +create table t1 ( + a varchar(16) character set cp1251 collate cp1251_bin not null, + b int(10) default null, + primary key(a) +) charset=cp1251; +insert into t1 (a) values ('air'), + ('we'),('g'),('we_toshko'), ('s0urce'),('we_ivo'),('we_iliyan'), + ('we_martin'),('vw_grado'),('vw_vasko'),('tn_vili'),('tn_kalina'), + ('tn_fakira'),('vw_silvia'),('vw_starshi'),('vw_geo'),('vw_b0x1'); + +select * from t1 where a like 'we_%'; +drop table t1; diff --git a/mysql-test/t/ctype_latin1.test b/mysql-test/t/ctype_latin1.test index cee0324d12f..6006ee4c527 100644 --- a/mysql-test/t/ctype_latin1.test +++ b/mysql-test/t/ctype_latin1.test @@ -66,3 +66,12 @@ SET collation_connection='latin1_swedish_ci'; -- source include/ctype_filesort.inc SET collation_connection='latin1_bin'; -- source include/ctype_filesort.inc + +# +# Bug#8041 +# An unknown character (e.g. 0x84) should result in ERROR, +# It was treated like a space character earlier. +# Howerver, it should still work fine as a string part. +--error 1064 +CREATE TABLE „a (a int); +SELECT '„a' as str; diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test index ce4fdc4e3c9..512ae88a445 100644 --- a/mysql-test/t/ctype_latin1_de.test +++ b/mysql-test/t/ctype_latin1_de.test @@ -116,3 +116,19 @@ SELECT FIELD('ue',s1), FIELD('Ü',s1), s1='ue', s1='Ü' FROM t1; DROP TABLE t1; -- source include/ctype_filesort.inc + +# +# Bug#7878 with utf8_general_ci, equals (=) has problem with +# accent insensitivity. +# Although originally this problem was found with UTF8 character set, +# '=' behaved wrong for latin1_german2_ci as well. +# Let's check it does not work incorrect anymore. +# +SET NAMES latin1; +CREATE TABLE t1 ( + col1 varchar(255) NOT NULL default '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 collate latin1_german2_ci; +INSERT INTO t1 VALUES ('ß'),('ss'),('ss'); +ALTER TABLE t1 ADD KEY ifword(col1); +SELECT * FROM t1 WHERE col1='ß' ORDER BY col1, BINARY col1; +DROP TABLE t1; diff --git a/mysql-test/t/ctype_sjis.test b/mysql-test/t/ctype_sjis.test index 58ca3c6a997..50d286f28b9 100644 --- a/mysql-test/t/ctype_sjis.test +++ b/mysql-test/t/ctype_sjis.test @@ -68,3 +68,10 @@ SET collation_connection='sjis_japanese_ci'; -- source include/ctype_filesort.inc SET collation_connection='sjis_bin'; -- source include/ctype_filesort.inc + +# Check parsing of string literals in SJIS with multibyte characters that +# have an embedded \ in them. (Bug #8303) + +--character_set sjis +SET NAMES sjis; +SELECT HEX('²“‘@\Œ\') FROM DUAL; diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 8e3eb71c3e5..35f2b2642be 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -693,3 +693,8 @@ INSERT INTO t1 VALUES ('one'),('two'); SELECT CHARSET('a'); SELECT user, CONCAT('<', user, '>') AS c FROM t1; DROP TABLE t1; + +# +# Bug#8385: utf8_general_ci treats Cyrillic letters I and SHORT I as the same +# +select convert(_koi8r'É' using utf8) < convert(_koi8r'Ê' using utf8); diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 8b322746ed6..bd27e0654e0 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -224,3 +224,15 @@ select distinct sum(b) from t1 group by a; select distinct sum(b) from (select a,b from t1) y group by a; drop table t1; +# +# test of union subquery in the FROM clause with complex distinct/all (BUG#6565) +# +create table t1(a int); +create table t2(a int); +create table t3(a int); +insert into t1 values(1),(1); +insert into t2 values(2),(2); +insert into t3 values(3),(3); +select * from t1 union distinct select * from t2 union all select * from t3; +select * from (select * from t1 union distinct select * from t2 union all select * from t3) X; +drop table t1, t2, t3; diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 50d01da080f..4809f6f0357 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -310,6 +310,14 @@ select count(*) from t1; drop table t1; # +# testing out of bounds memory access in ft_nlq_find_relevance() +# (bug#8522); visible in valgrind. +# +CREATE TABLE t1 ( a TEXT, FULLTEXT (a) ); +INSERT INTO t1 VALUES ('testing ft_nlq_find_relevance'); +SELECT MATCH(a) AGAINST ('nosuchword') FROM t1; +DROP TABLE t1; +# # bug#6784 # mi_flush_bulk_insert (on dup key error in mi_write) # was mangling info->dupp_key_pos diff --git a/mysql-test/t/fulltext_cache.test b/mysql-test/t/fulltext_cache.test index 234deab91e6..b01f0d18b76 100644 --- a/mysql-test/t/fulltext_cache.test +++ b/mysql-test/t/fulltext_cache.test @@ -29,13 +29,13 @@ INSERT INTO t2 VALUES (6,2,'um chocolate Snickers'); INSERT INTO t2 VALUES (7,1,'Bife'); INSERT INTO t2 VALUES (8,1,'Pizza de Salmao'); -SELECT t1.q, t2.item, t2.id, round(MATCH t2.item AGAINST ('sushi'),8) +SELECT t1.q, t2.item, t2.id, round(MATCH t2.item AGAINST ('sushi'),6) as x FROM t1, t2 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi' IN BOOLEAN MODE) as x FROM t1, t2 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; -SELECT t1.q, t2.item, t2.id, round(MATCH t2.item AGAINST ('sushi'),8) +SELECT t1.q, t2.item, t2.id, round(MATCH t2.item AGAINST ('sushi'),6) as x FROM t2, t1 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi' IN BOOLEAN MODE) diff --git a/mysql-test/t/fulltext_order_by.test b/mysql-test/t/fulltext_order_by.test index 5856f68ec9e..5c1b4127d8c 100644 --- a/mysql-test/t/fulltext_order_by.test +++ b/mysql-test/t/fulltext_order_by.test @@ -31,6 +31,14 @@ SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE a=7 an SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel; SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel; +# +# BUG#6635 - test_if_skip_sort_order() thought it can skip filesort +# for fulltext searches too +# +alter table t1 add key m (message); +explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message; +SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc; + drop table t1; # diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 465611a5ebb..78e7d3c5ed6 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -474,6 +474,15 @@ INSERT INTO t1 VALUES select val, count(*) from t1 group by val; drop table t1; +# +# Bug #5615: type of aggregate function column wrong when using group by +# + +create table t1(a int, b datetime); +insert into t1 values (1, NOW()), (2, NOW()); +create table t2 select MAX(b) from t1 group by a; +show create table t2; +drop table t1, t2; # # Bug 7833: Wrong datatype of aggregate column is returned @@ -487,3 +496,33 @@ drop table t2; create table t2 select f2 from (select now() f2 from t1) a; show columns from t2; drop table t2, t1; + +# +# Bug 8893: wrong result for min/max optimization with 2 indexes +# + +CREATE TABLE t1( + id int PRIMARY KEY, + a int, + b int, + INDEX i_b_id(a,b,id), + INDEX i_id(a,id) +); +INSERT INTO t1 VALUES + (1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1); +SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6; +DROP TABLE t1; + +# change the order of the last two index definitions + +CREATE TABLE t1( + id int PRIMARY KEY, + a int, + b int, + INDEX i_id(a,id), + INDEX i_b_id(a,b,id) +); +INSERT INTO t1 VALUES + (1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1); +SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6; +DROP TABLE t1; diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 6d5974ca5ed..4e504797a48 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -476,3 +476,25 @@ EXPLAIN WHERE a = CONV('e251273eb74a8ee3', 16, 10); DROP TABLE t1; + +# +# Bug #6317: string function CHAR, parameter is NULL, wrong result +# +SELECT CHAR(NULL,121,83,81,'76') as my_column; +SELECT CHAR_LENGTH(CHAR(NULL,121,83,81,'76')) as my_column; +# +# Test case for bug #8669: null aes_decrypt result in order by query +# + +CREATE TABLE t1 (id int PRIMARY KEY, str char(255) NOT NULL); +CREATE TABLE t2 (id int NOT NULL UNIQUE); +INSERT INTO t2 VALUES (1),(2); +INSERT INTO t1 VALUES (1, aes_encrypt('foo', 'bar')); +INSERT INTO t1 VALUES (2, 'not valid'); + +SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id; +SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id + ORDER BY t1.id; + +DROP TABLE t1, t2; + diff --git a/mysql-test/t/func_system.test b/mysql-test/t/func_system.test index a05b80ca56b..7fff165e057 100644 --- a/mysql-test/t/func_system.test +++ b/mysql-test/t/func_system.test @@ -30,3 +30,12 @@ show create table t1; drop table t1; select TRUE,FALSE,NULL; + +# +# Bug#8291 Illegal collation mix with USER() function +# +create table t1 (a char(10)) character set latin1; +select * from t1 where a=version(); +select * from t1 where a=database(); +select * from t1 where a=user(); +drop table t1; diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test index 7060d35e9a4..8e1314aeace 100644 --- a/mysql-test/t/grant2.test +++ b/mysql-test/t/grant2.test @@ -89,3 +89,17 @@ flush privileges; drop database mysqltest; use test; +# Bug #3309: Test IP addresses with netmask +create database mysqltest_1; +create table mysqltest_1.t1 (i int); +insert into mysqltest_1.t1 values (1),(2),(3); +GRANT ALL ON mysqltest_1.t1 TO mysqltest_1@'127.0.0.0/255.0.0.0'; +connect (n1,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,$MASTER_MYSOCK); +connection n1; +show grants for current_user(); +select * from t1; +disconnect n1; +connection default; +REVOKE ALL ON mysqltest_1.t1 FROM mysqltest_1@'127.0.0.0/255.0.0.0'; +drop table mysqltest_1.t1; +drop database mysqltest_1; diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test index 182baa641da..188de8a5379 100644 --- a/mysql-test/t/insert_update.test +++ b/mysql-test/t/insert_update.test @@ -68,14 +68,15 @@ INSERT t1 SELECT 1,9,70 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100000, b=4; SELECT * FROM t1; TRUNCATE TABLE t1; INSERT t1 VALUES (1,2,10), (3,4,20); -CREATE TABLE t2 (x INT, y INT, z INT, d INT); +CREATE TABLE t2 (a INT, b INT, c INT, d INT); +# column names deliberately clash with columns in t1 (Bug#8147) INSERT t2 VALUES (5,6,30,1), (7,4,40,1), (8,9,60,1); INSERT t2 VALUES (2,1,11,2), (7,4,40,2); -INSERT t1 SELECT x,y,z FROM t2 WHERE d=1 ON DUPLICATE KEY UPDATE c=c+100; +INSERT t1 SELECT a,b,c FROM t2 WHERE d=1 ON DUPLICATE KEY UPDATE c=c+100; SELECT * FROM t1; INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0; SELECT * FROM t1; -INSERT t1 SELECT x,y,z FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a); +INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a); SELECT *, VALUES(a) FROM t1; DROP TABLE t1; DROP TABLE t2; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index d177a68e685..fe9ec1e0963 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -457,6 +457,19 @@ explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 an drop table t1, t2, t3; +# Test for BUG#8711 '<=>' was considered to be a NULL-rejecting predicate. +create table t1 ( + a int(11), + b char(10), + key (a) +); +insert into t1 (a) values (1),(2),(3),(4); +create table t2 (a int); + +select * from t1 left join t2 on t1.a=t2.a where not (t2.a <=> t1.a); +select * from t1 left join t2 on t1.a=t2.a having not (t2.a <=> t1.a); +drop table t1,t2; + # Test for BUG#5088 create table t1 ( @@ -582,3 +595,23 @@ SELECT invoice.id, invoice.text_id, text_table.text_data WHERE (invoice.id LIKE '%' OR text_table.text_data LIKE '%'); DROP TABLE invoice, text_table; + +# +# Test for bug #9017: left join mistakingly converted to inner join +# + +CREATE TABLE t1 (a int PRIMARY KEY, b int); +CREATE TABLE t2 (a int PRIMARY KEY, b int); + +INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2); +INSERT INTO t2 VALUES (1,2), (2,2); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a + WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1); +SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1)); + +DROP TABLE t1,t2; + + diff --git a/mysql-test/t/lowercase_table2.test b/mysql-test/t/lowercase_table2.test index a766e39abab..eff5f2a99ec 100644 --- a/mysql-test/t/lowercase_table2.test +++ b/mysql-test/t/lowercase_table2.test @@ -111,3 +111,20 @@ select * from T1; alter table T1 add index (a); select * from T1; drop table T1; + +# +# Bug #8355: Tables not dropped from table cache on drop db +# +create database mysqltest_LC2; +use mysqltest_LC2; +create table myUC (i int); +insert into myUC values (1),(2),(3); +select * from myUC; +use test; +drop database mysqltest_LC2; +create database mysqltest_LC2; +use mysqltest_LC2; +create table myUC (i int); +select * from myUC; +use test; +drop database mysqltest_LC2; diff --git a/mysql-test/t/lowercase_table_grant-master.opt b/mysql-test/t/lowercase_table_grant-master.opt new file mode 100644 index 00000000000..c718e2feb1b --- /dev/null +++ b/mysql-test/t/lowercase_table_grant-master.opt @@ -0,0 +1 @@ +--lower_case_table_names diff --git a/mysql-test/t/lowercase_table_grant.test b/mysql-test/t/lowercase_table_grant.test new file mode 100644 index 00000000000..5ac35c81c21 --- /dev/null +++ b/mysql-test/t/lowercase_table_grant.test @@ -0,0 +1,25 @@ +# Test of grants when lower_case_table_names is on +use mysql; + +# mixed-case database name for testing +create database MYSQLtest; + +# check that database name gets forced to lowercase +grant all on MySQLtest.* to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; + +# now force it to mixed case, but see that it is lowercased in the acl cache +select * from db where user = 'mysqltest_1'; +update db set db = 'MYSQLtest' where db = 'mysqltest' and user = 'mysqltest_1' and host = 'localhost'; +flush privileges; +show grants for mysqltest_1@localhost; +select * from db where user = 'mysqltest_1'; + +# clear out the user we created +# +# can't use REVOKE because of the mixed-case database name +delete from db where db = 'MYSQLtest' and user = 'mysqltest_1' and host = 'localhost'; +flush privileges; +drop user mysqltest_1@localhost; + +drop database MYSQLtest; diff --git a/mysql-test/t/multi_statement-master.opt b/mysql-test/t/multi_statement-master.opt new file mode 100644 index 00000000000..b30df037531 --- /dev/null +++ b/mysql-test/t/multi_statement-master.opt @@ -0,0 +1,2 @@ +--log-slow-queries=slow.log +--log-queries-not-using-indexes diff --git a/mysql-test/t/multi_statement.test b/mysql-test/t/multi_statement.test index 862f2294641..2abec332878 100644 --- a/mysql-test/t/multi_statement.test +++ b/mysql-test/t/multi_statement.test @@ -14,3 +14,18 @@ select "abcd'";'abcd'select "'abcd";'abcd' select 5'abcd' delimiter ;'abcd' select 'finish'; + +# Bug #8475: Make sure every statement that is a slow query in +# a multi-statement query gets logged as a slow query. +flush status; +delimiter ||||; +create table t1 (i int); +insert into t1 values (1); +select * from t1 where i = 1; +insert into t1 values (2),(3),(4); +select * from t1 where i = 2; +select * from t1 where i = 3|||| +show status like 'Slow_queries'|||| +drop table t1|||| + +delimiter ;|||| diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index c8ed7910b76..0babd1f9401 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -524,3 +524,29 @@ explain select count(*) from t1 where a is null; select count(*) from t1 where a is null; drop table t1; +# +# Bug #8306: TRUNCATE leads to index corruption +# +create table t1 (c1 int, index(c1)); +create table t2 (c1 int, index(c1)) engine=merge union=(t1); +insert into t1 values (1); +# Close all tables. +flush tables; +# Open t2 and (implicitly) t1. +select * from t2; +# Truncate after flush works (unless another threads reopens t2 in between). +flush tables; +truncate table t1; +insert into t1 values (1); +# Close all tables. +flush tables; +# Open t2 and (implicitly) t1. +select * from t2; +# Truncate t1, wich was not recognized as open without the bugfix. +# Now, it should fail with a table-in-use error message. +--error 1105 +truncate table t1; +# The insert used to fail on the crashed table. +insert into t1 values (1); +drop table t1,t2; + diff --git a/mysql-test/t/mysql_client_test.test b/mysql-test/t/mysql_client_test.test index 86aecf43cbd..3639fc2e262 100644 --- a/mysql-test/t/mysql_client_test.test +++ b/mysql-test/t/mysql_client_test.test @@ -1,3 +1,10 @@ # We run with different binaries for normal and --embedded-server +# +# If this test fails with "command "$MYSQL_CLIENT_TEST" failed", +# you should either run mysql_client_test separartely against a running +# server or run mysql-test-run --debug mysql_client_test and check +# var/log/mysql_client_test.trace + --disable_result_log +--exec echo $MYSQL_CLIENT_TEST --exec $MYSQL_CLIENT_TEST diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 255ae50a8ca..f753e9dcf29 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -148,7 +148,14 @@ drop database mysqldump_test_db; CREATE TABLE t1 (a CHAR(10)); INSERT INTO t1 VALUES (_latin1 'ÄÖÜß'); --exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --skip-comments test t1 ---exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --skip-comments --compatible=mysql323 test t1 +# +# Bug#8063: make test mysqldump [ fail ] +# We cannot tes this command because its output depends +# on --default-character-set incompiled into "mysqldump" program. +# If the future we can move this command into a separate test with +# checking that "mysqldump" is compiled with "latin1" +# +#--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --skip-comments --compatible=mysql323 test t1 --exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --skip-comments --compatible=mysql323 --default-character-set=cp850 test t1 --exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --skip-comments --default-character-set=cp850 --compatible=mysql323 test t1 --exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --skip-comments --default-character-set=utf8 --compatible=mysql323 test t1 @@ -165,3 +172,13 @@ INSERT INTO t2 VALUES (4),(5),(6); --exec $MYSQL_DUMP --skip-comments --ignore-table=test.t1 test DROP TABLE t1; DROP TABLE t2; + +# +# Bug #8830 +# + +CREATE TABLE t1 (`b` blob); +INSERT INTO `t1` VALUES (0x602010000280100005E71A); +--exec $MYSQL_DUMP --skip-extended-insert --hex-blob test --skip-comments t1 +DROP TABLE t1; + diff --git a/mysql-test/t/ndb_alter_table.test b/mysql-test/t/ndb_alter_table.test index 892443a1407..f39edc0ee65 100644 --- a/mysql-test/t/ndb_alter_table.test +++ b/mysql-test/t/ndb_alter_table.test @@ -1,4 +1,5 @@ -- source include/have_ndb.inc +-- source include/have_multi_ndb.inc --disable_warnings DROP TABLE IF EXISTS t1; @@ -133,6 +134,21 @@ INSERT INTO t1 VALUES (1,2,0),(18,19,4),(20,21,0); select c from t1 order by c; drop table t1; +create table t1 ( a int primary key, b varchar(10), c varchar(10), index (b) ) +engine=ndb; +insert into t1 values (1,'one','one'), (2,'two','two'), (3,'three','three'); +create index c on t1(c); +connection server2; +select * from t1 where b = 'two'; +connection server1; +alter table t1 drop index c; +connection server2; +--error 1146 +select * from t1 where b = 'two'; +select * from t1 where b = 'two'; +connection server1; +drop table t1; + #--disable_warnings #DROP TABLE IF EXISTS t2; #--enable_warnings diff --git a/mysql-test/t/ndb_index_ordered.test b/mysql-test/t/ndb_index_ordered.test index 47e6b93eb81..010060a694d 100644 --- a/mysql-test/t/ndb_index_ordered.test +++ b/mysql-test/t/ndb_index_ordered.test @@ -249,6 +249,58 @@ select count(*)-9 from t1 use index (ts) where ts <= '2001-01-01 23:59:59'; drop table t1; +# decimal (not the new 5.0 thing) + +create table t1 ( + a int primary key, + s decimal(12), + t decimal(12, 5), + u decimal(12) unsigned, + v decimal(12, 5) unsigned, + key (s), + key (t), + key (u), + key (v) +) engine=ndb; +# +insert into t1 values + ( 0, -000000000007, -0000061.00003, 000000000061, 0000965.00042), + ( 1, -000000000007, -0000061.00042, 000000000061, 0000965.00003), + ( 2, -071006035767, 4210253.00024, 000000000001, 0000001.84488), + ( 3, 000000007115, 0000000.77607, 000077350625, 0000018.00013), + ( 4, -000000068391, -0346486.00000, 000000005071, 0005334.00002), + ( 5, -521579890459, -1936874.00001, 000000000154, 0000003.00018), + ( 6, -521579890459, -1936874.00018, 000000000154, 0000003.00001), + ( 7, 000000000333, 0000051.39140, 000000907958, 0788643.08374), + ( 8, 000042731229, 0000009.00000, 000000000009, 6428667.00000), + ( 9, -000008159769, 0000918.00004, 000096951421, 7607730.00008); +# +select count(*)- 5 from t1 use index (s) where s < -000000000007; +select count(*)- 7 from t1 use index (s) where s <= -000000000007; +select count(*)- 2 from t1 use index (s) where s = -000000000007; +select count(*)- 5 from t1 use index (s) where s >= -000000000007; +select count(*)- 3 from t1 use index (s) where s > -000000000007; +# +select count(*)- 4 from t1 use index (t) where t < -0000061.00003; +select count(*)- 5 from t1 use index (t) where t <= -0000061.00003; +select count(*)- 1 from t1 use index (t) where t = -0000061.00003; +select count(*)- 6 from t1 use index (t) where t >= -0000061.00003; +select count(*)- 5 from t1 use index (t) where t > -0000061.00003; +# +select count(*)- 2 from t1 use index (u) where u < 000000000061; +select count(*)- 4 from t1 use index (u) where u <= 000000000061; +select count(*)- 2 from t1 use index (u) where u = 000000000061; +select count(*)- 8 from t1 use index (u) where u >= 000000000061; +select count(*)- 6 from t1 use index (u) where u > 000000000061; +# +select count(*)- 5 from t1 use index (v) where v < 0000965.00042; +select count(*)- 6 from t1 use index (v) where v <= 0000965.00042; +select count(*)- 1 from t1 use index (v) where v = 0000965.00042; +select count(*)- 5 from t1 use index (v) where v >= 0000965.00042; +select count(*)- 4 from t1 use index (v) where v > 0000965.00042; + +drop table t1; + # bug#7798 create table t1(a int primary key, b int not null, index(b)); insert into t1 values (1,1), (2,2); diff --git a/mysql-test/t/ndb_multi.test b/mysql-test/t/ndb_multi.test new file mode 100644 index 00000000000..9286721b677 --- /dev/null +++ b/mysql-test/t/ndb_multi.test @@ -0,0 +1,44 @@ +-- source include/have_ndb.inc +-- source include/have_multi_ndb.inc + + +--disable_warnings +drop table if exists t1, t2, t3, t4; +--enable_warnings + +flush status; + +# Create test tables on server1 +create table t1 (a int) engine=ndbcluster; +create table t2 (a int) engine=ndbcluster; +insert into t1 value (2); +insert into t2 value (3); +select * from t1; +select * from t2; +show status like 'handler_discover%'; + +# Connect to server2 and use the tables from there +connection server2; +flush status; +select * from t1; +update t1 set a=3 where a=2; +show status like 'handler_discover%'; + +# Create a new table on server2 +create table t3 (a int not null primary key, b varchar(22), +c int, last_col text) engine=ndb; +insert into t3 values(1, 'Hi!', 89, 'Longtext column'); +create table t4 (pk int primary key, b int) engine=ndb; + +# Check that the tables are accessible from server1 +connection server1; +select * from t1; +select * from t3; +show status like 'handler_discover%'; +show tables like 't4'; +show status like 'handler_discover%'; +show tables; + +drop table t1, t2, t3, t4; + + diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 92bf4ece4e3..b204e59267e 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -485,3 +485,14 @@ execute stmt; deallocate prepare stmt; drop table t1, t2; + +# +# Bug #6089: FOUND_ROWS returns wrong values when no table/view is used +# + +prepare stmt from "SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0"; +execute stmt; +SELECT FOUND_ROWS(); +execute stmt; +SELECT FOUND_ROWS(); +deallocate prepare stmt; diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test index 5450512b959..b3ce6d7fd82 100644 --- a/mysql-test/t/ps_1general.test +++ b/mysql-test/t/ps_1general.test @@ -11,6 +11,7 @@ --disable_warnings drop table if exists t5, t6, t7, t8; drop database if exists mysqltest ; +drop database if exists client_test_db; --enable_warnings --disable_query_log @@ -599,6 +600,7 @@ drop table t2; prepare stmt1 from ' rename table t5 to t6, t7 to t8 ' ; create table t5 (a int) ; # rename must fail, t7 does not exist +--replace_result \\ / --error 1017 execute stmt1 ; create table t7 (a int) ; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index d570073b1d2..b0148a689af 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -672,4 +672,16 @@ select group_concat(a) FROM t1 group by b; set group_concat_max_len=default; drop table t1; -SET GLOBAL query_cache_size=0; +# Bug #8480: REPAIR TABLE needs to flush the table from the query cache +set global query_cache_size=1024*1024; +flush query cache; +create table t1 ( a int ); +insert into t1 values (1); +select a from t1; +select a from t1; +show status like 'qcache_queries_in_cache'; +repair table t1; +show status like 'qcache_queries_in_cache'; +drop table t1; + +set GLOBAL query_cache_size=0; diff --git a/mysql-test/t/rpl_charset.test b/mysql-test/t/rpl_charset.test index 68036ae49f1..3f7eabfa434 100644 --- a/mysql-test/t/rpl_charset.test +++ b/mysql-test/t/rpl_charset.test @@ -169,3 +169,16 @@ select hex(c1), hex(c2) from t1; connection master; drop table t1; sync_slave_with_master; + +# +# BUG#6676: Derivation of variables must be correct on slave +# +connection master; +create table `t1` ( + `pk` varchar(10) not null default '', + primary key (`pk`) +) engine=myisam default charset=latin1; +set @p=_latin1 'test'; +update t1 set pk='test' where pk=@p; +drop table t1; +sync_slave_with_master; diff --git a/mysql-test/t/rpl_deadlock-slave.opt b/mysql-test/t/rpl_deadlock-slave.opt new file mode 100644 index 00000000000..f4a8c640458 --- /dev/null +++ b/mysql-test/t/rpl_deadlock-slave.opt @@ -0,0 +1 @@ +--innodb --loose-innodb_lock_wait_timeout=4 --slave-transaction-retries=2 --max-relay-log-size=4096 diff --git a/mysql-test/t/rpl_deadlock.test b/mysql-test/t/rpl_deadlock.test new file mode 100644 index 00000000000..82470e8ebd0 --- /dev/null +++ b/mysql-test/t/rpl_deadlock.test @@ -0,0 +1,107 @@ +# See if slave restarts the transaction after failing on an InnoDB deadlock error. + +# Note: testing what happens when too many retries is possible, but +# needs large waits when running with --debug, so we don't do it. +# The same way, this test may not test what is expected when run +# under Valgrind, timings are too short then (with --valgrind I +# (Guilhem) have seen the test manage to provoke lock wait timeout +# error but not deadlock error; that is ok as code deals with the two +# errors in exactly the same way. + +source include/have_innodb.inc; +source include/master-slave.inc; + +connection master; +create table t1 (a int not null, key(a)) engine=innodb; +create table t2 (a int not null, key(a)) engine=innodb; +create table t3 (a int) engine=innodb; +create table t4 (a int) engine=innodb; +sync_slave_with_master; + +show create table t1; +show create table t2; +stop slave; + +# 1) Test deadlock + +connection master; +begin; +# Let's keep BEGIN and the locked statement in two different relay logs. +let $1=200; +disable_query_log; +while ($1) +{ + eval insert into t3 values( $1 ); + dec $1; +} +enable_query_log; +insert into t3 select * from t2 for update; +insert into t1 values(1); +commit; +save_master_pos; + +connection slave; +begin; +# Let's make our transaction large so that it's slave who is chosen as +# victim +let $1=1000; +disable_query_log; +while ($1) +{ + eval insert into t4 values( $1 ); + dec $1; +} +enable_query_log; +select * from t1 for update; +start slave; +--sleep 3; # hope that slave is blocked now +insert into t2 values(22); # provoke deadlock, slave should be victim +commit; +sync_with_master; +select * from t1; # check that slave succeeded finally +select * from t2; +# check that no error is reported +--replace_column 1 # 8 # 9 # 23 # 33 # +--replace_result $MASTER_MYPORT MASTER_MYPORT +show slave status; + +# 2) Test lock wait timeout + +stop slave; +change master to master_log_pos=401; # the BEGIN log event +begin; +select * from t2 for update; # hold lock +start slave; +--sleep 10; # slave should have blocked, and be retrying +commit; +sync_with_master; +select * from t1; # check that slave succeeded finally +select * from t2; +# check that no error is reported +--replace_column 1 # 8 # 9 # 23 # 33 # +--replace_result $MASTER_MYPORT MASTER_MYPORT +show slave status; + +# Now we repeat 2), but with BEGIN in the same relay log as +# COMMIT (to see if seeking into hot log is ok). + +set global max_relay_log_size=0; + +# This is really copy-paste of 2) of above +stop slave; +change master to master_log_pos=401; +begin; +select * from t2 for update; +start slave; +--sleep 10; +commit; +sync_with_master; +select * from t1; +select * from t2; +--replace_column 1 # 8 # 9 # 23 # 33 # +--replace_result $MASTER_MYPORT MASTER_MYPORT +show slave status; + +connection master; +drop table t1,t2; +sync_slave_with_master; diff --git a/mysql-test/t/rpl_delete_all.test b/mysql-test/t/rpl_delete_all.test index 23848720107..ad2ce29c610 100644 --- a/mysql-test/t/rpl_delete_all.test +++ b/mysql-test/t/rpl_delete_all.test @@ -6,7 +6,7 @@ connection master; drop database if exists mysqltest; sync_slave_with_master; # can't read dir ---replace_result "Errcode: 1" "Errcode: X" "Errcode: 2" "Errcode: X" +--replace_result "Errcode: 1" "Errcode: X" "Errcode: 2" "Errcode: X" \\ / --error 12 show tables from mysqltest; diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index 4c27c03af60..a4506e32963 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -61,3 +61,19 @@ drop table t2; save_master_pos; connection slave; sync_with_master; + +# +# Bug#8412: Error codes reported in binary log for CHARACTER SET, +# FOREIGN_KEY_CHECKS +# +connection master; +SET TIMESTAMP=1000000000; +CREATE TABLE t1 ( a INT UNIQUE ); +SET FOREIGN_KEY_CHECKS=0; +--error 1062 +INSERT INTO t1 VALUES (1),(1); +sync_slave_with_master; + + + + diff --git a/mysql-test/t/rpl_multi_query.test b/mysql-test/t/rpl_multi_query.test index 15f5d288759..482a2679e7a 100644 --- a/mysql-test/t/rpl_multi_query.test +++ b/mysql-test/t/rpl_multi_query.test @@ -3,6 +3,9 @@ # CLIENT_MULTI_STATEMENTS) will be binlogged ONE-query-per-event (not # one binlog event containing all queries) +# PS doesn't support multi-statements +--disable_ps_protocol + source include/master-slave.inc; --disable_warnings drop database if exists mysqltest; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index c00395d95e7..f7144f436df 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1945,3 +1945,33 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; DROP TABLE t1, t2; + + +# +# Test case for bug 7098: substitution of a constant for a string field +# + +CREATE TABLE t1 ( city char(30) ); +INSERT INTO t1 VALUES ('London'); +INSERT INTO t1 VALUES ('Paris'); + +SELECT * FROM t1 WHERE city='London'; +SELECT * FROM t1 WHERE city='london'; +EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london'; +SELECT * FROM t1 WHERE city='London' AND city='london'; +EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; +SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; + +DROP TABLE t1; + +# +# Bug#7425 inconsistent sort order on unsigned columns result of substraction +# + +create table t1 (a int(11) unsigned, b int(11) unsigned); +insert into t1 values (1,0), (1,1), (1,2); +select a-b from t1 order by 1; +select a-b , (a-b < 0) from t1 order by 1; +select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0; +select cast((a - b) as unsigned) from t1 order by 1; +drop table t1; diff --git a/mysql-test/t/select_found.test b/mysql-test/t/select_found.test index d31d7d0b02e..91879015b51 100644 --- a/mysql-test/t/select_found.test +++ b/mysql-test/t/select_found.test @@ -168,6 +168,21 @@ SELECT FOUND_ROWS(); DROP TABLE t1; # +# Bug #6089: queries which don't use any tables +# + +SELECT 'foo'; +SELECT FOUND_ROWS(); +SELECT SQL_CALC_FOUND_ROWS 'foo'; +SELECT FOUND_ROWS(); +SELECT SQL_CALC_FOUND_ROWS 'foo' limit 0; +SELECT FOUND_ROWS(); +SELECT FOUND_ROWS(); + +SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0; +SELECT FOUND_ROWS(); + +# # Bug #7945: group by + distinct with constant expression + limit # @@ -175,3 +190,4 @@ CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,2), (1,3), (1,4), (1,5); SELECT SQL_CALC_FOUND_ROWS DISTINCT 'a' FROM t1 GROUP BY b LIMIT 2; SELECT FOUND_ROWS(); +DROP TABLE t1; diff --git a/mysql-test/t/select_safe.test b/mysql-test/t/select_safe.test index 3cafd31a879..5b2dfb00bb7 100644 --- a/mysql-test/t/select_safe.test +++ b/mysql-test/t/select_safe.test @@ -66,4 +66,24 @@ SET MAX_SEEKS_FOR_KEY=DEFAULT; drop table t1; +# BUG#8726 +create table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; + +set local max_join_size=8; +--error 1104 +select * from (select * from t1) x; + +set local max_join_size=1; +--error 1104 +select * from (select * from t1 a, t1 b) x; + +set local max_join_size=1; +--error 1104 +select * from (select 1 union select 2 union select 3) x; +drop table t1; + SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT; diff --git a/mysql-test/t/skip_name_resolve-master.opt b/mysql-test/t/skip_name_resolve-master.opt new file mode 100644 index 00000000000..ab6ca1731f5 --- /dev/null +++ b/mysql-test/t/skip_name_resolve-master.opt @@ -0,0 +1 @@ +--skip-name-resolve diff --git a/mysql-test/t/skip_name_resolve.test b/mysql-test/t/skip_name_resolve.test new file mode 100644 index 00000000000..68dcf329427 --- /dev/null +++ b/mysql-test/t/skip_name_resolve.test @@ -0,0 +1,5 @@ +# Bug #8471: IP address with mask fail when skip-name-resolve is on +GRANT ALL ON test.* TO mysqltest_1@'127.0.0.1/255.255.255.255'; +SHOW GRANTS FOR mysqltest_1@'127.0.0.1/255.255.255.255'; +REVOKE ALL ON test.* FROM mysqltest_1@'127.0.0.1/255.255.255.255'; +DROP USER mysqltest_1@'127.0.0.1/255.255.255.255'; diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index 78c9b68fde5..ddde5171200 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -115,3 +115,18 @@ eval alter table t1 index directory="$MYSQL_TEST_DIR/var/log"; enable_query_log; show create table t1; drop table t1; + +# +# Test specifying DATA DIRECTORY that is the same as what would normally +# have been chosen. (Bug #8707) +# +disable_query_log; +eval create table t1 (i int) data directory = "$MYSQL_TEST_DIR/var/master-data/test/"; +enable_query_log; +show create table t1; +drop table t1; +disable_query_log; +eval create table t1 (i int) index directory = "$MYSQL_TEST_DIR/var/master-data/test/"; +enable_query_log; +show create table t1; +drop table t1; diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test index 5b106d242de..6e991dc53d4 100644 --- a/mysql-test/t/type_float.test +++ b/mysql-test/t/type_float.test @@ -3,7 +3,7 @@ # Numeric floating point. --disable_warnings -drop table if exists t1; +drop table if exists t1,t2; --enable_warnings --replace_result e-0 e- e+0 e+ @@ -11,6 +11,9 @@ SELECT 10,10.0,10.,.1e+2,100.0e-1; --replace_result e-00 e-0 SELECT 6e-05, -6e-05, --6e-05, -6e-05+1.000000; SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1; +SELECT 0.001e+1,0.001e-1, -0.001e+01,-0.001e-01; +SELECT 123.23E+02,-123.23E-02,"123.23E+02"+0.0,"-123.23E-02"+0.0; +SELECT 2147483647E+02,21474836.47E+06; create table t1 (f1 float(24),f2 float(52)); show full columns from t1; @@ -83,6 +86,7 @@ drop table t1; # create table t1 (c20 char); insert into t1 values (5000.0); +insert into t1 values (0.5e4); drop table t1; # Errors @@ -120,3 +124,23 @@ create table t1 (c char(6)); insert into t1 values (2e5),(2e6),(2e-4),(2e-5); select * from t1; drop table t1; + +# +# Test of comparison of integer with float-in-range (Bug #7840) +# This is needed because some ODBC applications (like Foxpro) uses +# floats for everything. +# + +CREATE TABLE t1 ( + reckey int unsigned NOT NULL, + recdesc varchar(50) NOT NULL, + PRIMARY KEY (reckey) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +INSERT INTO t1 VALUES (108, 'Has 108 as key'); +INSERT INTO t1 VALUES (109, 'Has 109 as key'); +select * from t1 where reckey=108; +select * from t1 where reckey=1.08E2; +select * from t1 where reckey=109; +select * from t1 where reckey=1.09E2; +drop table t1; diff --git a/mysql-test/t/type_set.test b/mysql-test/t/type_set.test index e4aeecb2c79..b6410a9ea3d 100644 --- a/mysql-test/t/type_set.test +++ b/mysql-test/t/type_set.test @@ -23,6 +23,7 @@ create table t1 (s set ('a','A') character set latin1 collate latin1_bin); show create table t1; insert into t1 values ('a'),('a,A'),('A,a'),('A'); select s from t1 order by s; +select s from t1 order by concat(s); drop table t1; # @@ -34,4 +35,5 @@ INSERT INTO t1 VALUES ('ae'),('oe'),('ue'),('ss'); INSERT INTO t1 VALUES ('ä,ö,ü,ß'); INSERT INTO t1 VALUES ('ae,oe,ue,ss'); SELECT c FROM t1 ORDER BY c; +SELECT c FROM t1 ORDER BY concat(c); DROP TABLE t1; diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 81788ce8d73..2f526dc9a46 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -84,7 +84,6 @@ select @a=_latin2'TEST' collate latin2_bin; set @a=_latin2'test' collate latin2_general_ci; select charset(@a),collation(@a),coercibility(@a); select @a=_latin2'TEST'; ---error 1267 select @a=_latin2'TEST' collate latin2_bin; # @@ -97,7 +96,6 @@ select collation(@a:=_latin2'test' collate latin2_bin); select coercibility(@a:=_latin2'test' collate latin2_bin); select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST'; select charset(@a),collation(@a),coercibility(@a); ---error 1267 select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci; # Check that user variables are binlogged correctly (BUG#3875) diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 3a76ae5136e..f80ca6378a1 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -362,3 +362,11 @@ drop table t1; SET GLOBAL MYISAM_DATA_POINTER_SIZE= 8; SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE'; + +# +# Bug #6958: negative arguments to integer options wrap around +# + +SET GLOBAL table_cache=-1; +SHOW VARIABLES LIKE 'table_cache'; +SET GLOBAL table_cache=DEFAULT; diff --git a/mysql-test/t/warnings.test b/mysql-test/t/warnings.test index 4bd659606f6..23ab5aa1f05 100644 --- a/mysql-test/t/warnings.test +++ b/mysql-test/t/warnings.test @@ -7,6 +7,19 @@ drop table if exists t1, t2; SET SQL_WARNINGS=1; create table t1 (a int); +--error 1050 +create table t1 (a int); +show count(*) errors; +show errors; +show warnings; +--error 1115 +create table t2(a int) default charset qwerty; +show count(*) errors; +show errors; +--error 1064 +create table t (i); +show count(*) errors; +show errors; insert into t1 values (1); insert into t1 values ("hej"); insert into t1 values ("hej"),("då"); |