diff options
Diffstat (limited to 'mysql-test/r')
59 files changed, 919 insertions, 343 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index ce324dc1fa8..703cc59d9a4 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -412,6 +412,27 @@ t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE t1 0 PRIMARY 2 User A 0 NULL NULL BTREE t1 1 Host 1 Host A NULL NULL NULL BTREE disabled DROP TABLE t1; +create table t1 (name char(15)); +insert into t1 (name) values ("current"); +create database mysqltest; +create table mysqltest.t1 (name char(15)); +insert into mysqltest.t1 (name) values ("mysqltest"); +select * from t1; +name +current +select * from mysqltest.t1; +name +mysqltest +alter table t1 rename mysqltest.t1; +ERROR 42S01: Table 't1' already exists +select * from t1; +name +current +select * from mysqltest.t1; +name +mysqltest +drop table t1; +drop database mysqltest; CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE); ALTER TABLE t1 DROP PRIMARY KEY; SHOW CREATE TABLE t1; diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index 7a7faf0db9a..8f77b306d3e 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -162,7 +162,7 @@ last_insert_id() 255 insert into t1 set i = null; Warnings: -Warning 1263 Data truncated, out of range for column 'i' at row 1 +Warning 1264 Data truncated, out of range for column 'i' at row 1 select last_insert_id(); last_insert_id() 255 @@ -213,7 +213,7 @@ a b delete from t1 where a=0; update t1 set a=NULL where b=6; Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'a' at row 4 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'a' at row 4 update t1 set a=300 where b=7; SET SQL_MODE=''; insert into t1(a,b)values(NULL,8); @@ -255,7 +255,7 @@ a b delete from t1 where a=0; update t1 set a=NULL where b=13; Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'a' at row 9 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'a' at row 9 update t1 set a=500 where b=14; select * from t1 order by b; a b diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result index b02574cf8a3..bee40eac30d 100644 --- a/mysql-test/r/bdb.result +++ b/mysql-test/r/bdb.result @@ -1156,3 +1156,28 @@ create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, pri insert into t2 select * from t1; delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; drop table t1,t2; +create table t1 (a char(10), key(a), b int not null, key(b)) engine=bdb; +insert into t1 values ('a',1),('A',2); +explain select a from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +select a from t1; +a +a +A +explain select b from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL b 4 NULL 2 Using index +select b from t1; +b +1 +2 +alter table t1 modify a char(10) binary; +explain select a from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 11 NULL 2 Using index +select a from t1; +a +A +a +drop table t1; diff --git a/mysql-test/r/binary.result b/mysql-test/r/binary.result index d4576791aed..9c3174ec606 100644 --- a/mysql-test/r/binary.result +++ b/mysql-test/r/binary.result @@ -59,10 +59,8 @@ concat("-",a,"-",b,"-") -hello-hello- select concat("-",a,"-",b,"-") from t1 where b="hello "; concat("-",a,"-",b,"-") --hello-hello- select concat("-",a,"-",b,"-") from t1 ignore index (b) where b="hello "; concat("-",a,"-",b,"-") --hello-hello- alter table t1 modify b tinytext not null, drop key b, add key (b(100)); select concat("-",a,"-",b,"-") from t1 where b="hello "; concat("-",a,"-",b,"-") diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 940c9e9a50b..e6a010a7542 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -11,7 +11,7 @@ create table t1 (b char(0) not null); create table if not exists t1 (b char(0) not null); insert into t1 values (""),(null); Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'b' at row 2 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'b' at row 2 select * from t1; b diff --git a/mysql-test/r/ctype_mb.result b/mysql-test/r/ctype_mb.result index edccb047c85..5e273b3c800 100644 --- a/mysql-test/r/ctype_mb.result +++ b/mysql-test/r/ctype_mb.result @@ -17,9 +17,9 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES ('aaaabbbbccccdddd','aaaabbbbccccdddd','aaaabbbbccccdddd'); Warnings: -Warning 1264 Data truncated for column 'c1' at row 1 -Warning 1264 Data truncated for column 'c2' at row 1 -Warning 1264 Data truncated for column 'c3' at row 1 +Warning 1265 Data truncated for column 'c1' at row 1 +Warning 1265 Data truncated for column 'c2' at row 1 +Warning 1265 Data truncated for column 'c3' at row 1 SELECT * FROM t1; c1 c2 c3 aaaa aaaa aaaa diff --git a/mysql-test/r/ctype_tis620.result b/mysql-test/r/ctype_tis620.result index 10164cd07ef..77799717d44 100644 --- a/mysql-test/r/ctype_tis620.result +++ b/mysql-test/r/ctype_tis620.result @@ -111,3 +111,28 @@ E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF SELECT hex(convert(@uF USING tis620)); hex(convert(@uF USING tis620)) F0F1F2F3F4F5F6F7F8F9FAFBFFFFFFFF +SET NAMES tis620; +CREATE TABLE t1 ( +recid int(11) NOT NULL auto_increment, +dyninfo text, +PRIMARY KEY (recid) +) ENGINE=MyISAM; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `recid` int(11) NOT NULL auto_increment, + `dyninfo` text, + PRIMARY KEY (`recid`) +) ENGINE=MyISAM DEFAULT CHARSET=tis620 +INSERT INTO t1 VALUES (1,'color=\"STB,NPG\"\r\nengine=\"J30A13\"\r\nframe=\"MRHCG1640YP4\"\r\ngrade=\"V6\"\r\nmodel=\"ACCORD\"\r\nmodelcode=\"CG164YEN\"\r\ntype=\"VT6\"\r\n'); +INSERT INTO t1 VALUES (2,'color=\"HTM,NPG,DEG,RGS\"\r\nengine=\"F23A5YP1\"\r\nframe=\"MRHCF8640YP3\"\r\ngrade=\"EXi AT\"\r\nmodel=\"ACCORD\"\r\nmodelcode=\"CF864YE\"\r\ntype=\"EXA\"\r\n'); +SELECT DISTINCT +(IF( LOCATE( 'year=\"', dyninfo ) = 1, +SUBSTRING( dyninfo, 6+1, LOCATE('\"\r',dyninfo) - 6 -1), +IF( LOCATE( '\nyear=\"', dyninfo ), +SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) + 7, +LOCATE( '\"\r', SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) +7 )) - 1), '' ))) AS year +FROM t1 +HAVING year != '' ORDER BY year; +year +DROP TABLE t1; diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 8b7178993a7..b7498ab2bc7 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -83,7 +83,7 @@ set names koi8r; create table t1 (s1 char(1) character set utf8); insert into t1 values (_koi8r'ÁÂ'); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 select s1,hex(s1),char_length(s1),octet_length(s1) from t1; s1 hex(s1) char_length(s1) octet_length(s1) Á D0B0 1 2 @@ -91,19 +91,19 @@ drop table t1; create table t1 (s1 tinytext character set utf8); insert into t1 select repeat('a',300); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 insert into t1 select repeat('Ñ',300); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 insert into t1 select repeat('aÑ',300); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 insert into t1 select repeat('Ña',300); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 insert into t1 select repeat('ÑÑ',300); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 select hex(s1) from t1; hex(s1) 616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161 @@ -122,19 +122,19 @@ drop table t1; create table t1 (s1 text character set utf8); insert into t1 select repeat('a',66000); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 insert into t1 select repeat('Ñ',66000); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 insert into t1 select repeat('aÑ',66000); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 insert into t1 select repeat('Ña',66000); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 insert into t1 select repeat('ÑÑ',66000); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 select length(s1),char_length(s1) from t1; length(s1) char_length(s1) 65535 65535 @@ -146,7 +146,7 @@ drop table t1; create table t1 (s1 char(10) character set utf8); insert into t1 values (0x41FF); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 select hex(s1) from t1; hex(s1) 41 @@ -154,7 +154,7 @@ drop table t1; create table t1 (s1 varchar(10) character set utf8); insert into t1 values (0x41FF); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 select hex(s1) from t1; hex(s1) 41 @@ -162,7 +162,7 @@ drop table t1; create table t1 (s1 text character set utf8); insert into t1 values (0x41FF); Warnings: -Warning 1264 Data truncated for column 's1' at row 1 +Warning 1265 Data truncated for column 's1' at row 1 select hex(s1) from t1; hex(s1) 41 diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index 10f2c069429..f1acc5d2dfa 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -93,8 +93,8 @@ a b 2 12 delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); Warnings: -Error 1241 Subquery returns more than 1 row -Error 1241 Subquery returns more than 1 row +Error 1242 Subquery returns more than 1 row +Error 1242 Subquery returns more than 1 row select * from t11; a b 0 10 @@ -113,8 +113,8 @@ a b 2 12 delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a); Warnings: -Error 1241 Subquery returns more than 1 row -Error 1241 Subquery returns more than 1 row +Error 1242 Subquery returns more than 1 row +Error 1242 Subquery returns more than 1 row select * from t11; a b 0 10 diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 16aae989d2b..4207eb4f9a8 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -5,6 +5,10 @@ INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), ('Only MyISAM tables','support collections'), ('Function MATCH ... AGAINST()','is used to do a search'), ('Full-text search in MySQL', 'implements vector space model'); +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES FULLTEXT +t1 1 a 2 b A NULL NULL NULL YES FULLTEXT select * from t1 where MATCH(a,b) AGAINST ("collections"); a b Only MyISAM tables support collections @@ -213,7 +217,7 @@ id show keys from t2; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t2 1 tig 1 ticket A NULL NULL NULL YES BTREE -t2 1 tix 1 inhalt A NULL 1 NULL YES FULLTEXT +t2 1 tix 1 inhalt A NULL NULL NULL YES FULLTEXT show create table t2; Table Create Table t2 CREATE TABLE `t2` ( diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result index 1fcda7748b3..35c1b6ae93f 100644 --- a/mysql-test/r/func_compress.result +++ b/mysql-test/r/func_compress.result @@ -65,6 +65,6 @@ NULL 50000 NULL Warnings: -Error 1258 ZLIB: Input data was corrupted for zlib -Error 1255 Too big size of uncompressed data. The maximum size is 1048576. (probably, length of uncompressed data was corrupted) +Error 1259 ZLIB: Input data was corrupted for zlib +Error 1256 Too big size of uncompressed data. The maximum size is 1048576. (probably, length of uncompressed data was corrupted) drop table t1; diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index af6f7956fe3..5807bae6a06 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -163,10 +163,10 @@ grp group_concat(c) 4 5 NULL Warnings: -Warning 1259 1 line(s) was(were) cut by group_concat() +Warning 1260 1 line(s) was(were) cut by group_concat() show warnings; Level Code Message -Warning 1259 1 line(s) was(were) cut by group_concat() +Warning 1260 1 line(s) was(were) cut by group_concat() set group_concat_max_len = 1024; select group_concat(sum(a)) from t1 group by grp; ERROR HY000: Invalid use of group function diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 6a704f2847d..147d7776e4d 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -551,7 +551,7 @@ id select_type table type possible_keys key key_len ref rows Extra explain select min(a1) from t1 where a1 between a3 and 'KKK'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 14 Using where +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 14 Using where explain select min(a4) from t1 where (a4 + 0.01) between 0.07 and 0.08; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/func_like.result b/mysql-test/r/func_like.result index 085e2f2f37e..7f722622be1 100644 --- a/mysql-test/r/func_like.result +++ b/mysql-test/r/func_like.result @@ -37,6 +37,15 @@ select * from t1 where a like "%abc\d%"; a abcd drop table t1; +create table t1 (a varchar(10), key(a)); +insert into t1 values ('a'), ('a\\b'); +select * from t1 where a like 'a\\%' escape '#'; +a +a\b +select * from t1 where a like 'a\\%' escape '#' and a like 'a\\\\b'; +a +a\b +drop table t1; SET NAMES koi8r; CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET koi8r); INSERT INTO t1 VALUES ('ÆÙ×Á'),('æÙ×Á'),('Æù×Á'),('ÆÙ÷Á'),('ÆÙ×á'),('æù÷á'); diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index 151a2902423..9085849e582 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -101,6 +101,24 @@ Note 1003 select high_priority pi() AS `pi()`,sin((pi() / 2)) AS `sin(pi()/2)`,c select degrees(pi()),radians(360); degrees(pi()) radians(360) 180 6.2831853071796 +SELECT ACOS(1.0); +ACOS(1.0) +0.000000 +SELECT ASIN(1.0); +ASIN(1.0) +1.570796 +SELECT ACOS(0.2*5.0); +ACOS(0.2*5.0) +0.000000 +SELECT ACOS(0.5*2.0); +ACOS(0.5*2.0) +0.000000 +SELECT ASIN(0.8+0.2); +ASIN(0.8+0.2) +1.570796 +SELECT ASIN(1.2-0.2); +ASIN(1.2-0.2) +1.570796 explain extended select degrees(pi()),radians(360); 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 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index f08ae1b1efd..c9eba2f2505 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -268,6 +268,34 @@ INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); 1 DROP TABLE t1; +CREATE TABLE t1 ( +wid int(10) unsigned NOT NULL auto_increment, +data_podp date default NULL, +status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy', +PRIMARY KEY(wid), +); +INSERT INTO t1 VALUES (8,NULL,'real'); +INSERT INTO t1 VALUES (9,NULL,'nowy'); +SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid; +elt(status_wnio,data_podp) +NULL +NULL +DROP TABLE t1; +CREATE TABLE t1 (title text) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education'); +INSERT INTO t1 VALUES ('House passes the CAREERS bill'); +SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1; +CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) +NULL +</a>.......................... +DROP TABLE t1; +CREATE TABLE t1 (i int, j int); +INSERT INTO t1 VALUES (1,1),(2,2); +SELECT DISTINCT i, ELT(j, '345', '34') FROM t1; +i ELT(j, '345', '34') +1 345 +2 34 +DROP TABLE t1; select 1=_latin1'1'; 1=_latin1'1' 1 @@ -514,7 +542,7 @@ insert(_latin2'abcd',2,3,_latin2'ef'), replace(_latin2'abcd',_latin2'b',_latin2'B') ; Warnings: -Warning 1264 Data truncated for column 'format(130,10)' at row 1 +Warning 1265 Data truncated for column 'format(130,10)' at row 1 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -576,27 +604,6 @@ select * from t7 where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA'; ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'concat' drop table t7; -CREATE TABLE t1 ( -wid int(10) unsigned NOT NULL auto_increment, -data_podp date default NULL, -status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy', -PRIMARY KEY(wid), -); -INSERT INTO t1 VALUES (8,NULL,'real'); -INSERT INTO t1 VALUES (9,NULL,'nowy'); -SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid; -elt(status_wnio,data_podp) -NULL -NULL -DROP TABLE t1; -CREATE TABLE t1 (title text) ENGINE=MyISAM; -INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education'); -INSERT INTO t1 VALUES ('House passes the CAREERS bill'); -SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1; -CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) -NULL -</a>.......................... -DROP TABLE t1; select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2); substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2) substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2) 1abcd;2abcd 3abcd;4abcd diff --git a/mysql-test/r/handler.result b/mysql-test/r/handler.result index d0ea16c5699..b25150919d6 100644 --- a/mysql-test/r/handler.result +++ b/mysql-test/r/handler.result @@ -175,3 +175,21 @@ ERROR 42S22: Unknown column 'W' in 'field list' handler t1 read a=(a); ERROR HY000: Wrong arguments to HANDLER ... READ drop table t1; +create table t1 (a char(5)); +insert into t1 values ("Ok"); +handler t1 open as t; +handler t read first; +a +Ok +use mysql; +handler t read first; +a +Ok +handler t close; +handler test.t1 open as t; +handler t read first; +a +Ok +handler t close; +use test; +drop table t1; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index eb0b9af8e38..57ad5ef79a2 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1084,11 +1084,7 @@ select * from t1; id select * from t2; id t1_id -drop table t1,t2; -DROP TABLE IF EXISTS t1,t2; -Warnings: -Note 1051 Unknown table 't1' -Note 1051 Unknown table 't2' +drop table t2,t1; CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB; INSERT INTO t1 VALUES(1); @@ -1333,13 +1329,16 @@ a 3 4 drop table t1; +CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB; +CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`) ON DELETE CASCADE ) ENGINE=INNODB; +drop table t2,t1; CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB; CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx (stamp))ENGINE=InnoDB; insert into t1 values (1),(2),(3); insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000); Warnings: -Warning 1264 Data truncated for column 'stamp' at row 3 +Warning 1265 Data truncated for column 'stamp' at row 3 SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp < '20020204120000' GROUP BY col1; col1 @@ -1414,3 +1413,17 @@ test.t2 968604391 test.t3 968604391 test.t4 NULL drop table t1,t2,t3; +create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb; +insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt'); +select name2 from t1 union all select name from t1 union all select id from t1; +name2 +fff +sss +ttt +first +second +third +1 +2 +3 +drop table t1; diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index ff5111c552e..38e08d7fbc5 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -63,7 +63,7 @@ insert into t1 values(NULL); ERROR 23000: Column 'id' cannot be null insert into t1 values (1), (NULL), (2); Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'id' at row 2 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'id' at row 2 select * from t1; id 1 diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index a77d67b5f64..2e90f8b2d81 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -595,3 +595,37 @@ CREATE TABLE t2 ( USID INTEGER UNSIGNED AUTO_INCREMENT, ServerID TINYINT UNSIGNE INSERT INTO t1 VALUES (39,42,'Access-Granted','46','491721000045',2130706433,17690,NULL,NULL,'Localnet','491721000045','49172200000',754974766,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-07-18 00:11:21',NULL,NULL,20030718001121); INSERT INTO t2 SELECT USID, ServerID, State, SessionID, User, NASAddr, NASPort, NASPortType, ConnectSpeed, CarrierType, CallingStationID, CalledStationID, AssignedAddr, SessionTime, PacketsIn, OctetsIn, PacketsOut, OctetsOut, TerminateCause, UnauthTime, AccessRequestTime, AcctStartTime, AcctLastTime, LastModification from t1 LIMIT 1; drop table t1,t2; +CREATE TABLE t1( +Month date NOT NULL, +Type tinyint(3) unsigned NOT NULL auto_increment, +Field int(10) unsigned NOT NULL, +Count int(10) unsigned NOT NULL, +UNIQUE KEY Month (Month,Type,Field) +); +insert into t1 Values +(20030901, 1, 1, 100), +(20030901, 1, 2, 100), +(20030901, 2, 1, 100), +(20030901, 2, 2, 100), +(20030901, 3, 1, 100); +select * from t1; +Month Type Field Count +2003-09-01 1 1 100 +2003-09-01 1 2 100 +2003-09-01 2 1 100 +2003-09-01 2 2 100 +2003-09-01 3 1 100 +Select null, Field, Count From t1 Where Month=20030901 and Type=2; +NULL Field Count +NULL 1 100 +NULL 2 100 +create table t2(No int not null, Field int not null, Count int not null); +insert into t2 Select null, Field, Count From t1 Where Month=20030901 and Type=2; +Warnings: +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'No' at row 1 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'No' at row 2 +select * from t2; +No Field Count +0 1 100 +0 2 100 +drop table t1, t2; diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index d8e10fd20c7..dca7b580378 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -34,10 +34,10 @@ INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N'); INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N'); INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','','','',''); Warnings: -Warning 1264 Data truncated for column 'transityes' at row 1 -Warning 1264 Data truncated for column 'shopsyes' at row 1 -Warning 1264 Data truncated for column 'schoolsyes' at row 1 -Warning 1264 Data truncated for column 'petsyes' at row 1 +Warning 1265 Data truncated for column 'transityes' at row 1 +Warning 1265 Data truncated for column 'shopsyes' at row 1 +Warning 1265 Data truncated for column 'schoolsyes' at row 1 +Warning 1265 Data truncated for column 'petsyes' at row 1 INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y'); @@ -156,8 +156,8 @@ CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, UNIQUE (c,i)); INSERT INTO t1 (c) VALUES (NULL),(NULL); Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'c' at row 1 -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'c' at row 2 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'c' at row 1 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'c' at row 2 SELECT * FROM t1; c i 1 diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 0547596dfbd..368055fed99 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -2,12 +2,12 @@ drop table if exists t1; create table t1 (a date, b date, c date not null, d date); load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ','; Warnings: -Warning 1264 Data truncated for column 'a' at row 1 -Warning 1264 Data truncated for column 'c' at row 1 -Warning 1264 Data truncated for column 'd' at row 1 -Warning 1264 Data truncated for column 'a' at row 2 -Warning 1264 Data truncated for column 'b' at row 2 -Warning 1264 Data truncated for column 'd' at row 2 +Warning 1265 Data truncated for column 'a' at row 1 +Warning 1265 Data truncated for column 'c' at row 1 +Warning 1265 Data truncated for column 'd' at row 1 +Warning 1265 Data truncated for column 'a' at row 2 +Warning 1265 Data truncated for column 'b' at row 2 +Warning 1265 Data truncated for column 'd' at row 2 load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES; SELECT * from t1; a b c d @@ -18,10 +18,10 @@ a b c d truncate table t1; load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d); Warnings: -Warning 1264 Data truncated for column 'c' at row 1 -Warning 1264 Data truncated for column 'd' at row 1 -Warning 1264 Data truncated for column 'b' at row 2 -Warning 1264 Data truncated for column 'd' at row 2 +Warning 1265 Data truncated for column 'c' at row 1 +Warning 1265 Data truncated for column 'd' at row 1 +Warning 1265 Data truncated for column 'b' at row 2 +Warning 1265 Data truncated for column 'd' at row 2 SELECT * from t1; a b c d NULL NULL 0000-00-00 0000-00-00 @@ -31,7 +31,7 @@ drop table t1; create table t1 (a text, b text); load data infile '../../std_data/loaddata2.dat' into table t1 fields terminated by ',' enclosed by ''''; Warnings: -Warning 1260 Row 3 doesn't contain data for all columns +Warning 1261 Row 3 doesn't contain data for all columns select concat('|',a,'|'), concat('|',b,'|') from t1; concat('|',a,'|') concat('|',b,'|') |Field A| |Field B| @@ -43,10 +43,10 @@ drop table t1; create table t1 (a int, b char(10)); load data infile '../../std_data/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines; Warnings: -Warning 1264 Data truncated for column 'a' at row 3 -Warning 1261 Row 3 was truncated; It contained more data than there where input columns -Warning 1264 Data truncated for column 'a' at row 5 -Warning 1261 Row 5 was truncated; It contained more data than there where input columns +Warning 1265 Data truncated for column 'a' at row 3 +Warning 1262 Row 3 was truncated; It contained more data than there where input columns +Warning 1265 Data truncated for column 'a' at row 5 +Warning 1262 Row 5 was truncated; It contained more data than there where input columns select * from t1; a b 1 row 1 @@ -57,8 +57,8 @@ a b truncate table t1; load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines; Warnings: -Warning 1264 Data truncated for column 'a' at row 4 -Warning 1260 Row 4 doesn't contain data for all columns +Warning 1265 Data truncated for column 'a' at row 4 +Warning 1261 Row 4 doesn't contain data for all columns select * from t1; a b 1 row 1 diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result index 2cb9527b94b..429bc5ed352 100644 --- a/mysql-test/r/lock.result +++ b/mysql-test/r/lock.result @@ -41,5 +41,9 @@ lock tables t1 write; check table t2; Table Op Msg_type Msg_text test.t2 check error Table 't2' was not locked with LOCK TABLES +insert into t1 select nr from t1; +ERROR HY000: Table 't1' was not locked with LOCK TABLES unlock tables; +lock tables t1 write, t1 as t1_alias read; +insert into t1 select index1,nr from t1 as t1_alias; drop table t1,t2; diff --git a/mysql-test/r/lowercase2.require b/mysql-test/r/lowercase2.require new file mode 100644 index 00000000000..522eac63e81 --- /dev/null +++ b/mysql-test/r/lowercase2.require @@ -0,0 +1,2 @@ +Variable_name Value +lower_case_table_names 2 diff --git a/mysql-test/r/lowercase_table2.result b/mysql-test/r/lowercase_table2.result new file mode 100644 index 00000000000..e582978c126 --- /dev/null +++ b/mysql-test/r/lowercase_table2.result @@ -0,0 +1,108 @@ +DROP TABLE IF EXISTS t1,t2,T1,T2,t3,T3; +DROP DATABASE IF EXISTS `TEST_$1`; +DROP DATABASE IF EXISTS `test_$1`; +CREATE TABLE T1 (a int); +INSERT INTO T1 VALUES (1); +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +T1 +SHOW TABLES LIKE "t1"; +Tables_in_test (t1) +T1 +SHOW CREATE TABLE T1; +Table Create Table +T1 CREATE TABLE `T1` ( + `a` int(11) default NULL +) TYPE=MyISAM +RENAME TABLE T1 TO T2; +SHOW TABLES LIKE "T2"; +Tables_in_test (T2) +T2 +SELECT * FROM t2; +a +1 +RENAME TABLE T2 TO t3; +SHOW TABLES LIKE "T3"; +Tables_in_test (T3) +t3 +RENAME TABLE T3 TO T1; +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +T1 +ALTER TABLE T1 add b int; +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +t1 +ALTER TABLE T1 RENAME T2; +SHOW TABLES LIKE "T2"; +Tables_in_test (T2) +T2 +LOCK TABLE T2 WRITE; +ALTER TABLE T2 drop b; +SHOW TABLES LIKE "T2"; +Tables_in_test (T2) +t2 +UNLOCK TABLES; +RENAME TABLE T2 TO T1; +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +T1 +SELECT * from T1; +a +1 +DROP TABLE T1; +CREATE DATABASE `TEST_$1`; +SHOW DATABASES LIKE "TEST%"; +Database (TEST%) +TEST_$1 +DROP DATABASE `test_$1`; +CREATE TABLE T1 (a int) engine=innodb; +INSERT INTO T1 VALUES (1); +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +T1 +SHOW TABLES LIKE "t1"; +Tables_in_test (t1) +T1 +SHOW CREATE TABLE T1; +Table Create Table +T1 CREATE TABLE `T1` ( + `a` int(11) default NULL +) TYPE=InnoDB +RENAME TABLE T1 TO T2; +SHOW TABLES LIKE "T2"; +Tables_in_test (T2) +t2 +SELECT * FROM t2; +a +1 +RENAME TABLE T2 TO t3; +SHOW TABLES LIKE "T3"; +Tables_in_test (T3) +t3 +RENAME TABLE T3 TO T1; +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +t1 +ALTER TABLE T1 add b int; +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +t1 +ALTER TABLE T1 RENAME T2; +SHOW TABLES LIKE "T2"; +Tables_in_test (T2) +t2 +LOCK TABLE T2 WRITE; +ALTER TABLE T2 drop b; +SHOW TABLES LIKE "T2"; +Tables_in_test (T2) +t2 +UNLOCK TABLES; +RENAME TABLE T2 TO T1; +SHOW TABLES LIKE "T1"; +Tables_in_test (T1) +t1 +SELECT * from T1; +a +1 +DROP TABLE T1; diff --git a/mysql-test/r/myisam-blob.result b/mysql-test/r/myisam-blob.result new file mode 100644 index 00000000000..43db7c8badd --- /dev/null +++ b/mysql-test/r/myisam-blob.result @@ -0,0 +1,44 @@ +drop table if exists t1; +CREATE TABLE t1 (data LONGBLOB) ENGINE=myisam; +INSERT INTO t1 (data) VALUES (NULL); +UPDATE t1 set data=repeat('a',18*1024*1024); +select length(data) from t1; +length(data) +18874368 +delete from t1 where left(data,1)='a'; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +truncate table t1; +INSERT INTO t1 (data) VALUES (repeat('a',1*1024*1024)); +INSERT INTO t1 (data) VALUES (repeat('b',16*1024*1024-1024)); +delete from t1 where left(data,1)='b'; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +UPDATE t1 set data=repeat('c',17*1024*1024); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +delete from t1 where left(data,1)='c'; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 set data=repeat('a',18*1024*1024); +select length(data) from t1; +length(data) +18874368 +alter table t1 modify data blob; +select length(data) from t1; +length(data) +0 +drop table t1; +CREATE TABLE t1 (data BLOB) ENGINE=myisam; +INSERT INTO t1 (data) VALUES (NULL); +UPDATE t1 set data=repeat('a',18*1024*1024); +Warnings: +Warning 1265 Data truncated for column 'data' at row 1 +select length(data) from t1; +length(data) +65535 +drop table t1; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index ec70cefc63f..278b9f41480 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -398,6 +398,68 @@ check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; +create table t1 ( a text not null, key a (a(20))); +insert into t1 values ('aaa '),('aaa'),('aa'); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +select concat(a,'.') from t1 where a='aaa'; +concat(a,'.') +aaa. +aaa . +select concat(a,'.') from t1 where binary a='aaa'; +concat(a,'.') +aaa. +update t1 set a='bbb' where a='aaa'; +select concat(a,'.') from t1; +concat(a,'.') +bbb. +bbb. +aa. +drop table t1; +create table t1(a text not null, b text not null, c text not null, index (a(10),b(10),c(10))); +insert into t1 values('807780', '477', '165'); +insert into t1 values('807780', '477', '162'); +insert into t1 values('807780', '472', '162'); +select * from t1 where a='807780' and b='477' and c='165'; +a b c +807780 477 165 +drop table t1; +create table t1 (a blob); +insert into t1 values('a '),('a'); +select concat(a,'.') from t1 where a='a'; +concat(a,'.') +a. +select concat(a,'.') from t1 where a='a '; +concat(a,'.') +a . +alter table t1 add key(a(2)); +select concat(a,'.') from t1 where a='a'; +concat(a,'.') +a. +select concat(a,'.') from t1 where a='a '; +concat(a,'.') +a . +drop table t1; +create table t1 (a int not null auto_increment primary key, b text not null, unique b (b(20))); +insert into t1 (b) values ('a'),('a '),('a '); +select concat(b,'.') from t1; +concat(b,'.') +a. +a . +a . +update t1 set b='b ' where a=2; +update t1 set b='b ' where a > 1; +ERROR 23000: Duplicate entry 'b ' for key 2 +delete from t1 where b='b'; +select a,concat(b,'.') from t1; +a concat(b,'.') +1 a. +3 a . +drop table t1; CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)) ENGINE=MyISAM; ERROR 42000: This version of MySQL doesn't yet support 'RTREE INDEX' create table t1 (a int, b varchar(200), c text not null) checksum=1; diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 92453b3e35b..f70eaeae214 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -24,11 +24,11 @@ insert into t1 values ("abirvalg"); SET INSERT_ID=1; SET TIMESTAMP=1000000000; insert into t2 values (); -LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-1-0' INTO TABLE t1 FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); -LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-2-0' INTO TABLE t1 FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); -LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-3-0' INTO TABLE t1 FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); -LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-4-0' INTO TABLE t1 FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); -LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-5-0' INTO TABLE t1 FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); +LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-1-0' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); +LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-2-0' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); +LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-3-0' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); +LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-4-0' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); +LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-5-0' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); --- Broken LOAD DATA -- use test; @@ -54,6 +54,12 @@ insert into t1 values ("abirvalg"); SET INSERT_ID=1; SET TIMESTAMP=1000000000; insert into t2 values (); +LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-1-1' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); +LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-2-1' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); +LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-3-1' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); +LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-4-1' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); +LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-5-1' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); +LOAD DATA LOCAL INFILE 'MYSQL_TEST_DIR/var/tmp/words.dat-6-1' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (word); SET TIMESTAMP=1000000000; insert into t1 values ("Alas"); @@ -63,18 +69,7 @@ SET TIMESTAMP=1000000000; insert into t1 values ("Alas"); --- --database -- -use test; -SET TIMESTAMP=1000000000; -create table t1 (word varchar(20)); -SET TIMESTAMP=1000000000; -create table t2 (id int auto_increment not null primary key); -SET TIMESTAMP=1000000000; -insert into t1 values ("abirvalg"); SET INSERT_ID=1; -SET TIMESTAMP=1000000000; -insert into t2 values (); -SET TIMESTAMP=1000000000; -insert into t1 values ("Alas"); --- --position -- use test; diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index aa56bce6453..cbd949d6e72 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -91,45 +91,45 @@ drop table t1; CREATE TABLE t1 (a varchar(16) NOT NULL, b smallint(6) NOT NULL, c datetime NOT NULL, d smallint(6) NOT NULL); INSERT INTO t1 SET a = "", d= "2003-01-14 03:54:55"; Warnings: -Warning 1264 Data truncated for column 'd' at row 1 +Warning 1265 Data truncated for column 'd' at row 1 UPDATE t1 SET d=1/NULL; Warnings: -Warning 1264 Data truncated for column 'd' at row 1 +Warning 1265 Data truncated for column 'd' at row 1 UPDATE t1 SET d=NULL; Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'd' at row 1 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'd' at row 1 INSERT INTO t1 (a) values (null); ERROR 23000: Column 'a' cannot be null INSERT INTO t1 (a) values (1/null); ERROR 23000: Column 'a' cannot be null INSERT INTO t1 (a) values (null),(null); Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'a' at row 1 -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'a' at row 2 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'a' at row 1 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'a' at row 2 INSERT INTO t1 (b) values (null); ERROR 23000: Column 'b' cannot be null INSERT INTO t1 (b) values (1/null); ERROR 23000: Column 'b' cannot be null INSERT INTO t1 (b) values (null),(null); Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'b' at row 1 -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'b' at row 2 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'b' at row 1 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'b' at row 2 INSERT INTO t1 (c) values (null); ERROR 23000: Column 'c' cannot be null INSERT INTO t1 (c) values (1/null); ERROR 23000: Column 'c' cannot be null INSERT INTO t1 (c) values (null),(null); Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'c' at row 1 -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'c' at row 2 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'c' at row 1 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'c' at row 2 INSERT INTO t1 (d) values (null); ERROR 23000: Column 'd' cannot be null INSERT INTO t1 (d) values (1/null); ERROR 23000: Column 'd' cannot be null INSERT INTO t1 (d) values (null),(null); Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'd' at row 1 -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'd' at row 2 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'd' at row 1 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'd' at row 2 select * from t1; a b c d 0 0000-00-00 00:00:00 0 diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 385108216bf..4dfd17991cc 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -337,7 +337,7 @@ index (id2) ); insert into t1 values(null,null),(1,1); Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'id2' at row 1 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'id2' at row 1 select * from t1; id id2 NULL 0 diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index c1053087603..4033996b239 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -333,10 +333,10 @@ a b c 1 NULL NULL alter table t1 modify b int not null, modify c varchar(10) not null; Warnings: -Warning 1264 Data truncated for column 'b' at row 1 -Warning 1264 Data truncated for column 'c' at row 1 -Warning 1264 Data truncated for column 'b' at row 2 -Warning 1264 Data truncated for column 'c' at row 3 +Warning 1265 Data truncated for column 'b' at row 1 +Warning 1265 Data truncated for column 'c' at row 1 +Warning 1265 Data truncated for column 'b' at row 2 +Warning 1265 Data truncated for column 'c' at row 3 explain select * from t1 order by a, b, c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 18 NULL 11 Using index diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 04bcb56d48d..5bbf0bad445 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -593,7 +593,7 @@ select * from t1; a set GLOBAL query_cache_size=1024; Warnings: -Warning 1281 Query cache failed to set size 1024, new query cache size is 0 +Warning 1282 Query cache failed to set size 1024, new query cache size is 0 show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 @@ -601,7 +601,7 @@ select * from t1; a set GLOBAL query_cache_size=10240; Warnings: -Warning 1281 Query cache failed to set size 10240, new query cache size is 0 +Warning 1282 Query cache failed to set size 10240, new query cache size is 0 show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 @@ -609,7 +609,7 @@ select * from t1; a set GLOBAL query_cache_size=20480; Warnings: -Warning 1281 Query cache failed to set size 20480, new query cache size is 0 +Warning 1282 Query cache failed to set size 20480, new query cache size is 0 show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 @@ -617,7 +617,7 @@ select * from t1; a set GLOBAL query_cache_size=40960; Warnings: -Warning 1281 Query cache failed to set size 40960, new query cache size is 0 +Warning 1282 Query cache failed to set size 40960, new query cache size is 0 show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 diff --git a/mysql-test/r/raid.result b/mysql-test/r/raid.result index 6e16757a43d..690ef24d809 100644 --- a/mysql-test/r/raid.result +++ b/mysql-test/r/raid.result @@ -2,6 +2,7 @@ DROP TABLE IF EXISTS t1,t2; DROP DATABASE IF EXISTS test_$1; create database test_$1; create table test_$1.r1 (i int) raid_type=1; +create table test_$1.r2 (i int) raid_type=1 raid_chunks=32; drop database test_$1; CREATE TABLE t1 ( id int unsigned not null auto_increment primary key, diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 5e61b1c58c3..ab66d786833 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -289,6 +289,20 @@ SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1); COUNT(*) 6 DROP TABLE t1; +CREATE TABLE t1 ( a int not null, b int not null, INDEX ab(a,b) ); +INSERT INTO t1 VALUES (47,1), (70,1), (15,1), (15, 4); +SELECT * FROM t1 +WHERE +( +( b =1 AND a BETWEEN 14 AND 21 ) OR +( b =2 AND a BETWEEN 16 AND 18 ) OR +( b =3 AND a BETWEEN 15 AND 19 ) OR +(a BETWEEN 19 AND 47) +); +a b +15 1 +47 1 +DROP TABLE t1; create table t1 (id int(10) primary key); insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); select id from t1 where id in (2,5,9) ; diff --git a/mysql-test/r/rpl000002.result b/mysql-test/r/rpl000002.result index 56e34b4874f..e5e661795fe 100644 --- a/mysql-test/r/rpl000002.result +++ b/mysql-test/r/rpl000002.result @@ -23,8 +23,24 @@ insert into t2 set created=now(); select * from t2; id created 1 1970-01-01 06:25:45 +create table t3 like t2; +create temporary table t4 like t2; +create table t5 select * from t4; start slave; select * from t2; id created 1 1970-01-01 06:25:45 -drop table t2; +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `id` int(11) NOT NULL auto_increment, + `created` datetime default NULL, + PRIMARY KEY (`id`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table t5; +Table Create Table +t5 CREATE TABLE `t5` ( + `id` int(11) NOT NULL default '0', + `created` datetime default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t2,t3,t5; diff --git a/mysql-test/r/rpl_heap.result b/mysql-test/r/rpl_heap.result new file mode 100644 index 00000000000..1556bcd5f25 --- /dev/null +++ b/mysql-test/r/rpl_heap.result @@ -0,0 +1,29 @@ +reset master; +drop table if exists t1; +create table t1 (a int) type=HEAP; +insert into t1 values(10); +show binlog events from 79; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.001 79 Query 1 79 use `test`; create table t1 (a int) type=HEAP +master-bin.001 147 Query 1 147 use `test`; DELETE FROM `test`.`t1` +master-bin.001 205 Query 1 205 use `test`; insert into t1 values(10) +reset slave; +start slave; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) default NULL +) TYPE=HEAP +select * from t1; +a +10 +select * from t1; +a +select * from t1 limit 10; +a +show binlog events in 'master-bin.002' from 79; +Log_name Pos Event_type Server_id Orig_log_pos Info +master-bin.002 79 Query 1 79 use `test`; DELETE FROM `test`.`t1` +select * from t1; +a +drop table t1; diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result index 6e331abf102..8001cb59a35 100644 --- a/mysql-test/r/rpl_insert_id.result +++ b/mysql-test/r/rpl_insert_id.result @@ -38,8 +38,8 @@ select * from t2; b c 5 0 6 11 -drop table t1; drop table t2; +drop table t1; create table t1(a int auto_increment, key(a)); create table t2(b int auto_increment, c int, key(b)); insert into t1 values (10); diff --git a/mysql-test/r/rpl_log.result b/mysql-test/r/rpl_log.result index 34d41c5dfac..2f8a54369c9 100644 --- a/mysql-test/r/rpl_log.result +++ b/mysql-test/r/rpl_log.result @@ -16,7 +16,6 @@ load data infile '../../std_data/words.dat' into table t1 ignore 1 lines; select count(*) from t1; count(*) 69 -create table t2 like t1; drop table t1; show binlog events; Log_name Pos Event_type Server_id Orig_log_pos Info @@ -28,8 +27,7 @@ master-bin.000001 263 Query 1 263 use `test`; drop table t1 master-bin.000001 311 Query 1 311 use `test`; create table t1 (word char(20) not null) master-bin.000001 386 Create_file 1 386 db=test;table=t1;file_id=1;block_len=581 master-bin.000001 1056 Exec_load 1 1056 ;file_id=1 -master-bin.000001 1079 Query 1 1079 use `test`; create table t2 like t1 -master-bin.000001 1137 Query 1 1137 use `test`; drop table t1 +master-bin.000001 1079 Query 1 1079 use `test`; drop table t1 show binlog events from 79 limit 1; Log_name Pos Event_type Server_id Orig_log_pos Info master-bin.000001 79 Query 1 79 use `test`; create table t1(n int not null auto_increment primary key) @@ -40,10 +38,6 @@ master-bin.000001 172 Intvar 1 172 INSERT_ID=1 show binlog events from 79 limit 2,1; Log_name Pos Event_type Server_id Orig_log_pos Info master-bin.000001 200 Query 1 200 use `test`; insert into t1 values (NULL) -show binlog events from 79 limit 2,2; -Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.000001 200 Query 1 200 use `test`; insert into t1 values (NULL) -master-bin.000001 263 Query 1 263 use `test`; drop table t1 flush logs; create table t5 (a int); drop table t5; @@ -63,9 +57,8 @@ master-bin.000001 263 Query 1 263 use `test`; drop table t1 master-bin.000001 311 Query 1 311 use `test`; create table t1 (word char(20) not null) master-bin.000001 386 Create_file 1 386 db=test;table=t1;file_id=1;block_len=581 master-bin.000001 1056 Exec_load 1 1056 ;file_id=1 -master-bin.000001 1079 Query 1 1079 use `test`; create table t2 like t1 -master-bin.000001 1137 Query 1 1137 use `test`; drop table t1 -master-bin.000001 1185 Rotate 1 1185 master-bin.000002;pos=4 +master-bin.000001 1079 Query 1 1079 use `test`; drop table t1 +master-bin.000001 1127 Rotate 1 1127 master-bin.000002;pos=4 show binlog events in 'master-bin.000002'; Log_name Pos Event_type Server_id Orig_log_pos Info master-bin.000002 4 Query 1 4 use `test`; create table t5 (a int) @@ -92,11 +85,10 @@ slave-bin.000001 263 Query 1 263 use `test`; drop table t1 slave-bin.000001 311 Query 1 311 use `test`; create table t1 (word char(20) not null) slave-bin.000001 386 Create_file 1 386 db=test;table=t1;file_id=1;block_len=581 slave-bin.000001 1065 Exec_load 1 1065 ;file_id=1 -slave-bin.000001 1088 Query 1 1088 use `test`; create table t2 like t1 -slave-bin.000001 1146 Query 1 1146 use `test`; drop table t1 -slave-bin.000001 1194 Query 1 1194 use `test`; create table t5 (a int) -slave-bin.000001 1252 Query 1 1252 use `test`; drop table t5 -slave-bin.000001 1300 Rotate 2 1300 slave-bin.000002;pos=4 +slave-bin.000001 1088 Query 1 1088 use `test`; drop table t1 +slave-bin.000001 1136 Query 1 1136 use `test`; create table t5 (a int) +slave-bin.000001 1194 Query 1 1194 use `test`; drop table t5 +slave-bin.000001 1242 Rotate 2 1242 slave-bin.000002;pos=4 show binlog events in 'slave-bin.000002' from 4; Log_name Pos Event_type Server_id Orig_log_pos Info slave-bin.000002 4 Query 1 4 use `test`; create table t1 (n int) diff --git a/mysql-test/r/rpl_multi_delete.result b/mysql-test/r/rpl_multi_delete.result new file mode 100644 index 00000000000..e94a4e7947e --- /dev/null +++ b/mysql-test/r/rpl_multi_delete.result @@ -0,0 +1,22 @@ +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); +create table t2 (a int); +insert into t1 values (1); +insert into t2 values (1); +delete t1.* from t1, t2 where t1.a = t2.a; +select * from t1; +a +select * from t2; +a +1 +select * from t1; +a +select * from t2; +a +1 +drop table t1,t2; diff --git a/mysql-test/r/rpl_until.result b/mysql-test/r/rpl_until.result index 82b1ed233ec..120c3d7a57f 100644 --- a/mysql-test/r/rpl_until.result +++ b/mysql-test/r/rpl_until.result @@ -69,4 +69,4 @@ ERROR HY000: Wrong parameter or combination of parameters for START SLAVE UNTIL start slave sql_thread; start slave until master_log_file='master-bin.000001', master_log_pos=561; Warnings: -Note 1253 Slave is already running +Note 1254 Slave is already running diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 30b391dd947..a940e0f2e95 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2321,3 +2321,23 @@ left join t4 on id3 = id4 where id2 = 1 or id4 = 1; id1 id2 id3 id4 id44 1 1 NULL NULL NULL drop table t1,t2,t3,t4; +create table t1(s varchar(10) not null); +create table t2(s varchar(10) not null primary key); +create table t3(s varchar(10) not null primary key); +insert into t1 values ('one\t'), ('two\t'); +insert into t2 values ('one\r'), ('two\t'); +insert into t3 values ('one '), ('two\t'); +select * from t1 where s = 'one'; +s +select * from t2 where s = 'one'; +s +select * from t3 where s = 'one'; +s +one +select * from t1,t2 where t1.s = t2.s; +s s +two two +select * from t2,t3 where t2.s = t3.s; +s s +two two +drop table t1, t2, t3; diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result index e302a0cefeb..23a39a242a1 100644 --- a/mysql-test/r/select_found.result +++ b/mysql-test/r/select_found.result @@ -208,3 +208,21 @@ select FOUND_ROWS(); FOUND_ROWS() 0 drop table t1; +CREATE TABLE t1 ( a int not null, b int not null, KEY ab(a,b) ); +INSERT INTO t1 VALUES ( 47, 1 ); +INSERT INTO t1 VALUES ( 70, 1 ); +SELECT * FROM t1 +WHERE +( +( b =1 AND a BETWEEN 14 AND 21 ) OR +( b =2 AND a BETWEEN 16 AND 18 ) OR +( b =3 AND a BETWEEN 15 AND 19 ) +); +a b +DROP TABLE t1; +CREATE TABLE t1 ( a integer, u varchar(15), r integer, key uao_idx( r, a, u)); +DELETE FROM t1 +WHERE ( r = 1 AND a IN ( 1, 2 ) AND ( u = 'w' OR u LIKE 'w/%' ) ) +OR ( r = 1 AND a IN ( 3 ) AND ( u = 'w/U' OR u LIKE 'w/U/%' ) ) +OR ( r = 1 AND a IN ( 1, 2, 3 ) AND ( u = 'w' ) ); +drop table t1; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 55620afb17d..ee3492c8111 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -6,7 +6,7 @@ explain extended select (select 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1248 Select 2 was reduced during optimisation +Note 1249 Select 2 was reduced during optimisation Note 1003 select high_priority 2 AS `(select 2)` SELECT (SELECT 1) UNION SELECT (SELECT 2); (SELECT 1) @@ -17,8 +17,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1248 Select 2 was reduced during optimisation -Note 1248 Select 4 was reduced during optimisation +Note 1249 Select 2 was reduced during optimisation +Note 1249 Select 4 was reduced during optimisation Note 1003 select high_priority 1 AS `(SELECT 1)` union select 2 AS `(SELECT 2)` SELECT (SELECT (SELECT 0 UNION SELECT 0)); (SELECT (SELECT 0 UNION SELECT 0)) @@ -29,7 +29,7 @@ id select_type table type possible_keys key key_len ref rows Extra 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1248 Select 2 was reduced during optimisation +Note 1249 Select 2 was reduced during optimisation Note 1003 select high_priority (select 0 AS `0` union select 0 AS `0`) AS `(SELECT (SELECT 0 UNION SELECT 0))` SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a; ERROR 42S22: Reference 'a' not supported (forward reference in item list) @@ -46,8 +46,8 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1275 Field or reference 'a' of SELECT #3 was resolved in SELECT #1 -Note 1275 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 Note 1003 select high_priority 1 AS `1` from (select 1 AS `a`) b having ((select b.a AS `a`) = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 @@ -219,7 +219,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where Warnings: -Note 1275 Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1 Note 1003 select high_priority test.t4.b AS `b`,(select avg((test.t2.a + (select min(test.t3.a) AS `min(t3.a)` from test.t3 where (test.t3.a >= test.t4.a)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from test.t2) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from test.t4 select * from t3 where exists (select * from t2 where t2.b=t3.a); a @@ -307,8 +307,8 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 Using where Warnings: -Note 1275 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1 -Note 1275 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1 Note 1003 select high_priority (select test.t1.a AS `a` from test.t1 where (test.t1.a = test.t2.a) union select test.t5.a AS `a` from test.t5 where (test.t5.a = test.t2.a)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,test.t2.a AS `a` from test.t2 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; ERROR 21000: Subquery returns more than 1 row @@ -326,7 +326,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t6 ALL NULL NULL NULL NULL 4 Using where 2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 Warnings: -Note 1275 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1 Note 1003 select high_priority test.t6.patient_uq AS `patient_uq`,test.t6.clinic_uq AS `clinic_uq` from test.t6 where exists(select test.t7.uq AS `uq`,test.t7.name AS `name` from test.t7 where (test.t7.uq = test.t6.clinic_uq) limit 1) select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); ERROR 23000: Column: 'a' in field list is ambiguous @@ -707,7 +707,7 @@ EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ref id id 5 const 1 Using where; Using index Warnings: -Note 1248 Select 2 was reduced during optimisation +Note 1249 Select 2 was reduced during optimisation Note 1003 select high_priority test.t2.id AS `id` from test.t2 where (test.t2.id = 1) SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); id @@ -719,8 +719,8 @@ EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ref id id 5 const 1 Using where; Using index Warnings: -Note 1248 Select 3 was reduced during optimisation -Note 1248 Select 2 was reduced during optimisation +Note 1249 Select 3 was reduced during optimisation +Note 1249 Select 2 was reduced during optimisation Note 1003 select high_priority test.t2.id AS `id` from test.t2 where (test.t2.id = (1 + 1)) EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); id select_type table type possible_keys key key_len ref rows Extra @@ -852,8 +852,8 @@ explain extended select (select a+1) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Warnings: -Note 1275 Field or reference 'a' of SELECT #2 was resolved in SELECT #1 -Note 1248 Select 2 was reduced during optimisation +Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1 +Note 1249 Select 2 was reduced during optimisation Note 1003 select high_priority (test.t1.a + 1) AS `(select a+1)` from test.t1 select (select a+1) from t1; (select a+1) @@ -1041,12 +1041,12 @@ UNIQUE KEY `maxnumrep` (`maxnumrep`) ) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test'); Warnings: -Warning 1264 Data truncated for column 'date' at row 1 -Warning 1264 Data truncated for column 'date' at row 2 +Warning 1265 Data truncated for column 'date' at row 1 +Warning 1265 Data truncated for column 'date' at row 2 INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test'); Warnings: -Warning 1264 Data truncated for column 'date' at row 1 -Warning 1264 Data truncated for column 'date' at row 2 +Warning 1265 Data truncated for column 'date' at row 1 +Warning 1265 Data truncated for column 'date' at row 2 INSERT INTO t3 VALUES (1,1); SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE numeropost=topic); @@ -1238,7 +1238,7 @@ insert into t1 values (1,0), (2,0), (3,0); insert into t2 values (1,1), (2,1), (3,1), (2,2); update ignore t1 set b=(select b from t2 where t1.a=t2.a); Warnings: -Error 1241 Subquery returns more than 1 row +Error 1242 Subquery returns more than 1 row select * from t1; a b 1 1 diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index b2a055fa72c..9c1816e295c 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -62,7 +62,7 @@ processor_id (SELECT y.yod_id FROM t1 p2, t2 y WHERE p2.processor_id = p1.proces 1 1 2 2 3 3 -drop table t1,t2,t3; +drop table t2,t1,t3; CREATE TABLE t1 ( id int(11) NOT NULL default '0', b int(11) default NULL, diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 6dc48a0a77e..08d75d8b562 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -64,9 +64,7 @@ t9 CREATE TABLE `t9` ( PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DATA DIRECTORY='TEST_DIR/var/tmp/' INDEX DIRECTORY='TEST_DIR/var/run/' drop database mysqltest; -create table t1 (a int not null) type=myisam; -Warnings: -Warning 1286 'TYPE=storage_engine' is deprecated. Use 'ENGINE=storage_engine' instead. +create table t1 (a int not null) engine=myisam; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/r/timezone.result b/mysql-test/r/timezone.result index 20706408075..15f0d4121c7 100644 --- a/mysql-test/r/timezone.result +++ b/mysql-test/r/timezone.result @@ -32,3 +32,9 @@ ts from_unixtime(ts) 1048989599 2003-03-30 03:59:59 1048989601 2003-03-30 04:00:01 DROP TABLE t1; +select unix_timestamp('1970-01-01 01:00:00'), +unix_timestamp('1970-01-01 01:00:01'), +unix_timestamp('2038-01-01 00:59:59'), +unix_timestamp('2038-01-01 01:00:00'); +unix_timestamp('1970-01-01 01:00:00') unix_timestamp('1970-01-01 01:00:01') unix_timestamp('2038-01-01 00:59:59') unix_timestamp('2038-01-01 01:00:00') +0 1 2145916799 0 diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index 255b7f0d942..330464fe669 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -9,9 +9,9 @@ d mediumtext YES NULL e longtext YES NULL CREATE TABLE t2 (a char(257), b varchar(70000) binary, c varchar(70000000)); Warnings: -Warning 1245 Converting column 'a' from CHAR to TEXT -Warning 1245 Converting column 'b' from CHAR to BLOB -Warning 1245 Converting column 'c' from CHAR to TEXT +Warning 1246 Converting column 'a' from CHAR to TEXT +Warning 1246 Converting column 'b' from CHAR to BLOB +Warning 1246 Converting column 'c' from CHAR to TEXT show columns from t2; Field Type Null Key Default Extra a text YES NULL @@ -656,3 +656,9 @@ id txt 2 Chevy 4 Ford drop table t1; +CREATE TABLE t1 ( i int(11) NOT NULL default '0', c text NOT NULL, PRIMARY KEY (i), KEY (c(1),c(1))); +INSERT t1 VALUES (1,''),(2,''),(3,'asdfh'),(4,''); +select max(i) from t1 where c = ''; +max(i) +4 +drop table t1; diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index c1ee621697d..cc29c676283 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -2,11 +2,11 @@ drop table if exists t1; create table t1 (t datetime); insert into t1 values(101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959),(20030102030460),(20030102036301),(20030102240401),(20030132030401),(20031302030460); Warnings: -Warning 1264 Data truncated for column 't' at row 13 -Warning 1264 Data truncated for column 't' at row 14 -Warning 1264 Data truncated for column 't' at row 15 -Warning 1264 Data truncated for column 't' at row 16 -Warning 1264 Data truncated for column 't' at row 17 +Warning 1265 Data truncated for column 't' at row 13 +Warning 1265 Data truncated for column 't' at row 14 +Warning 1265 Data truncated for column 't' at row 15 +Warning 1265 Data truncated for column 't' at row 16 +Warning 1265 Data truncated for column 't' at row 17 select * from t1; t 2000-01-01 00:00:00 diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index fa4cd231129..dc78369f583 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -158,17 +158,17 @@ insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 3 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 3 insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1264 Data truncated for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 3 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 3 insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); Warnings: -Warning 1264 Data truncated for column 'a' at row 3 +Warning 1265 Data truncated for column 'a' at row 3 select * from t1; a 0.00 @@ -201,32 +201,32 @@ drop table t1; create table t1 (a decimal(10,2) unsigned); insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 6 +Warning 1264 Data truncated, out of range for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 6 insert into t1 values ("-.1"),("+.1"),(".1"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 1 insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 3 +Warning 1264 Data truncated, out of range for column 'a' at row 3 insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 3 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 3 insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 3 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 3 insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1264 Data truncated for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 3 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 3 insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); Warnings: -Warning 1264 Data truncated for column 'a' at row 3 +Warning 1265 Data truncated for column 'a' at row 3 select * from t1; a 0.00 @@ -259,32 +259,32 @@ drop table t1; create table t1 (a decimal(10,2) zerofill); insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 6 +Warning 1264 Data truncated, out of range for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 6 insert into t1 values ("-.1"),("+.1"),(".1"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 1 insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 3 +Warning 1264 Data truncated, out of range for column 'a' at row 3 insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 3 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 3 insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 3 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 3 insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1264 Data truncated for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 3 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 3 insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); Warnings: -Warning 1264 Data truncated for column 'a' at row 3 +Warning 1265 Data truncated for column 'a' at row 3 select * from t1; a 00000000.00 @@ -321,13 +321,13 @@ insert into t1 values (00000000000001),(+0000000000001),(-0000000000001); insert into t1 values (+111111111.11),(111111111.11),(-11111111.11); insert into t1 values (-111111111.11),(+1111111111.11),(1111111111.11); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 3 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 3 insert into t1 values (1e+100),(1e-100),(-1e+100); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'a' at row 3 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 3 insert into t1 values (123.4e0),(123.4e+2),(123.4e-2),(123e1),(123e+0); select * from t1; a @@ -361,8 +361,8 @@ drop table t1; create table t1 (a decimal); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+12345678901'),(99999999999999); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'a' at row 7 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 7 select * from t1; a -9999999999 @@ -376,9 +376,9 @@ drop table t1; create table t1 (a decimal unsigned); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 7 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 7 select * from t1; a 0 @@ -392,9 +392,9 @@ drop table t1; create table t1 (a decimal zerofill); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 7 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 7 select * from t1; a 0000000000 @@ -408,9 +408,9 @@ drop table t1; create table t1 (a decimal unsigned zerofill); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999); Warnings: -Warning 1263 Data truncated, out of range for column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'a' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 7 +Warning 1264 Data truncated, out of range for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 7 select * from t1; a 0000000000 @@ -424,16 +424,16 @@ drop table t1; create table t1(a decimal(10,0)); insert into t1 values ("1e4294967295"); Warnings: -Warning 1264 Data truncated for column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 1 select * from t1; a 99999999999 delete from t1; insert into t1 values("1e4294967297"); Warnings: -Warning 1264 Data truncated for column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'a' at row 1 select * from t1; a 99999999999 diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result index fe8f78453b7..976c484dabf 100644 --- a/mysql-test/r/type_enum.result +++ b/mysql-test/r/type_enum.result @@ -1639,13 +1639,13 @@ drop table t1; create table t1 (a enum ('0','1')); insert into t1 set a='foobar'; Warnings: -Warning 1264 Data truncated for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 1 select * from t1; a update t1 set a = replace(a,'x','y'); Warnings: -Warning 1264 Data truncated for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 1 select * from t1; a diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index 2a461942853..a0c0e0f5503 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -15,8 +15,8 @@ f1 float NULL YES NULL select,insert,update,references f2 double NULL YES NULL select,insert,update,references insert into t1 values(10,10),(1e+5,1e+5),(1234567890,1234567890),(1e+10,1e+10),(1e+15,1e+15),(1e+20,1e+20),(1e+50,1e+50),(1e+150,1e+150); Warnings: -Warning 1263 Data truncated, out of range for column 'f1' at row 7 -Warning 1263 Data truncated, out of range for column 'f1' at row 8 +Warning 1264 Data truncated, out of range for column 'f1' at row 7 +Warning 1264 Data truncated, out of range for column 'f1' at row 8 insert into t1 values(-10,-10),(1e-5,1e-5),(1e-10,1e-10),(1e-15,1e-15),(1e-20,1e-20),(1e-50,1e-50),(1e-150,1e-150); select * from t1; f1 f2 diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 512df8fbaa9..4a3206005dd 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -89,34 +89,34 @@ insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,N insert into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3); insert into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1); Warnings: -Warning 1263 Data truncated, out of range for column 'utiny' at row 1 -Warning 1263 Data truncated, out of range for column 'ushort' at row 1 -Warning 1263 Data truncated, out of range for column 'umedium' at row 1 -Warning 1263 Data truncated, out of range for column 'ulong' at row 1 -Warning 1264 Data truncated for column 'options' at row 1 -Warning 1264 Data truncated for column 'flags' at row 1 +Warning 1264 Data truncated, out of range for column 'utiny' at row 1 +Warning 1264 Data truncated, out of range for column 'ushort' at row 1 +Warning 1264 Data truncated, out of range for column 'umedium' at row 1 +Warning 1264 Data truncated, out of range for column 'ulong' at row 1 +Warning 1265 Data truncated for column 'options' at row 1 +Warning 1265 Data truncated for column 'flags' at row 1 insert into t1 values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,NULL,0,0,0,-4294967295,-4294967295,-4294967295,'-4294967295',0,"one,two,tree"); Warnings: -Warning 1264 Data truncated for column 'string' at row 1 -Warning 1263 Data truncated, out of range for column 'tiny' at row 1 -Warning 1263 Data truncated, out of range for column 'short' at row 1 -Warning 1263 Data truncated, out of range for column 'medium' at row 1 -Warning 1263 Data truncated, out of range for column 'long_int' at row 1 -Warning 1263 Data truncated, out of range for column 'utiny' at row 1 -Warning 1263 Data truncated, out of range for column 'ushort' at row 1 -Warning 1263 Data truncated, out of range for column 'umedium' at row 1 -Warning 1263 Data truncated, out of range for column 'ulong' at row 1 -Warning 1264 Data truncated for column 'options' at row 1 +Warning 1265 Data truncated for column 'string' at row 1 +Warning 1264 Data truncated, out of range for column 'tiny' at row 1 +Warning 1264 Data truncated, out of range for column 'short' at row 1 +Warning 1264 Data truncated, out of range for column 'medium' at row 1 +Warning 1264 Data truncated, out of range for column 'long_int' at row 1 +Warning 1264 Data truncated, out of range for column 'utiny' at row 1 +Warning 1264 Data truncated, out of range for column 'ushort' at row 1 +Warning 1264 Data truncated, out of range for column 'umedium' at row 1 +Warning 1264 Data truncated, out of range for column 'ulong' at row 1 +Warning 1265 Data truncated for column 'options' at row 1 insert into t1 values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,NULL,0,0,0,4294967295,4294967295,4294967295,'4294967295',0,0); Warnings: -Warning 1263 Data truncated, out of range for column 'tiny' at row 1 -Warning 1263 Data truncated, out of range for column 'short' at row 1 -Warning 1263 Data truncated, out of range for column 'medium' at row 1 -Warning 1263 Data truncated, out of range for column 'long_int' at row 1 -Warning 1263 Data truncated, out of range for column 'utiny' at row 1 -Warning 1263 Data truncated, out of range for column 'ushort' at row 1 -Warning 1263 Data truncated, out of range for column 'umedium' at row 1 -Warning 1264 Data truncated for column 'options' at row 1 +Warning 1264 Data truncated, out of range for column 'tiny' at row 1 +Warning 1264 Data truncated, out of range for column 'short' at row 1 +Warning 1264 Data truncated, out of range for column 'medium' at row 1 +Warning 1264 Data truncated, out of range for column 'long_int' at row 1 +Warning 1264 Data truncated, out of range for column 'utiny' at row 1 +Warning 1264 Data truncated, out of range for column 'ushort' at row 1 +Warning 1264 Data truncated, out of range for column 'umedium' at row 1 +Warning 1265 Data truncated for column 'options' at row 1 insert into t1 (tiny) values (1); select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000),date_field,time_field,date_time,blob_col,tinyblob_col,mediumblob_col,longblob_col from t1; auto string tiny short medium long_int longlong real_float real_double utiny ushort umedium ulong ulonglong mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000) date_field time_field date_time blob_col tinyblob_col mediumblob_col longblob_col @@ -165,12 +165,12 @@ PRIMARY KEY (auto) ); INSERT INTO t2 (string,mediumblob_col,new_field) SELECT string,mediumblob_col,new_field from t1 where auto > 10; Warnings: -Warning 1264 Data truncated for column 'new_field' at row 2 -Warning 1264 Data truncated for column 'new_field' at row 3 -Warning 1264 Data truncated for column 'new_field' at row 4 -Warning 1264 Data truncated for column 'new_field' at row 5 -Warning 1264 Data truncated for column 'new_field' at row 6 -Warning 1264 Data truncated for column 'new_field' at row 7 +Warning 1265 Data truncated for column 'new_field' at row 2 +Warning 1265 Data truncated for column 'new_field' at row 3 +Warning 1265 Data truncated for column 'new_field' at row 4 +Warning 1265 Data truncated for column 'new_field' at row 5 +Warning 1265 Data truncated for column 'new_field' at row 6 +Warning 1265 Data truncated for column 'new_field' at row 7 select * from t2; auto string mediumblob_col new_field 1 2 2 ne @@ -202,9 +202,9 @@ one one drop table t2; create table t2 select * from t1; Warnings: -Warning 1264 Data truncated for column 'options' at row 4 -Warning 1264 Data truncated for column 'options' at row 5 -Warning 1264 Data truncated for column 'options' at row 6 +Warning 1265 Data truncated for column 'options' at row 4 +Warning 1265 Data truncated for column 'options' at row 5 +Warning 1265 Data truncated for column 'options' at row 6 update t2 set string="changed" where auto=16; show full columns from t1; Field Type Collation Null Key Default Extra Privileges Comment diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index 5666e865518..0830179902d 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -25,9 +25,9 @@ t 36:30:31 insert into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a"); Warnings: -Warning 1263 Data truncated, out of range for column 't' at row 2 -Warning 1263 Data truncated, out of range for column 't' at row 3 -Warning 1263 Data truncated, out of range for column 't' at row 4 +Warning 1264 Data truncated, out of range for column 't' at row 2 +Warning 1264 Data truncated, out of range for column 't' at row 3 +Warning 1264 Data truncated, out of range for column 't' at row 4 select * from t1; t 10:22:33 diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 172ccc440f4..72640a4a802 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -45,11 +45,11 @@ drop table t1; create table t1 (ix timestamp); insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101); Warnings: -Warning 1264 Data truncated for column 'ix' at row 10 -Warning 1264 Data truncated for column 'ix' at row 11 -Warning 1264 Data truncated for column 'ix' at row 12 -Warning 1264 Data truncated for column 'ix' at row 13 -Warning 1264 Data truncated for column 'ix' at row 14 +Warning 1265 Data truncated for column 'ix' at row 10 +Warning 1265 Data truncated for column 'ix' at row 11 +Warning 1265 Data truncated for column 'ix' at row 12 +Warning 1265 Data truncated for column 'ix' at row 13 +Warning 1265 Data truncated for column 'ix' at row 14 select ix+0 from t1; ix+0 19991101000000 @@ -128,5 +128,48 @@ t2 t4 t6 t8 t10 t12 t14 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 1997-12-31 23:47:59 drop table t1; -create table t1 (a timestamp default 1); -ERROR 42000: Invalid default value for 'a' +create table t1 (t1 timestamp default '2003-01-01 00:00:00', +t2 timestamp default '2003-01-01 00:00:00'); +set TIMESTAMP=1000000000; +insert into t1 values(); +select * from t1; +t1 t2 +2001-09-09 04:46:40 2003-01-01 00:00:00 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t1` timestamp NOT NULL, + `t2` timestamp NOT NULL default '2003-01-01 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show columns from t1; +Field Type Null Key Default Extra +t1 timestamp YES NULL +t2 timestamp YES 2003-01-01 00:00:00 +show columns from t1 like 't2'; +Field Type Null Key Default Extra +t2 timestamp YES 2003-01-01 00:00:00 +create table t2 (select * from t1); +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `t1` timestamp NOT NULL, + `t2` timestamp NOT NULL default '2003-01-01 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 add column t0 timestamp first; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t0` timestamp NOT NULL, + `t1` timestamp NOT NULL default '2003-01-01 00:00:00', + `t2` timestamp NOT NULL default '2003-01-01 00:00:00' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1,t2; +create table t1 (ts1 timestamp, ts2 timestamp); +set TIMESTAMP=1000000000; +insert into t1 values (); +insert into t1 values (DEFAULT, DEFAULT); +select * from t1; +ts1 ts2 +2001-09-09 04:46:40 0000-00-00 00:00:00 +2001-09-09 04:46:40 0000-00-00 00:00:00 +drop table t1; diff --git a/mysql-test/r/type_uint.result b/mysql-test/r/type_uint.result index d948ca47972..f312e9b7f64 100644 --- a/mysql-test/r/type_uint.result +++ b/mysql-test/r/type_uint.result @@ -4,7 +4,7 @@ create table t1 (this int unsigned); insert into t1 values (1); insert into t1 values (-1); Warnings: -Warning 1263 Data truncated, out of range for column 'this' at row 1 +Warning 1264 Data truncated, out of range for column 'this' at row 1 select * from t1; this 1 diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result index 25ddda88f48..84b688429db 100644 --- a/mysql-test/r/type_year.result +++ b/mysql-test/r/type_year.result @@ -26,3 +26,11 @@ y y2 2001 01 2069 69 drop table t1; +create table t1 (y year); +insert into t1 values (now()); +Warnings: +Warning 1265 Data truncated for column 'y' at row 1 +select if(y = now(), 1, 0) from t1; +if(y = now(), 1, 0) +1 +drop table t1; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 23ed06dee6a..f89d41aa982 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -539,7 +539,7 @@ aa show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` char(2) default NULL + `a` char(2) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT 12 as a UNION select "aa" as a; @@ -550,7 +550,7 @@ aa show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` char(2) default NULL + `a` char(2) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT 12 as a UNION select 12.2 as a; @@ -561,7 +561,7 @@ a show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` double(4,1) default NULL + `a` double(4,1) NOT NULL default '0.0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t2 (it1 tinyint, it2 tinyint not null, i int not null, ib bigint, f float, d double, y year, da date, dt datetime, sc char(10), sv varchar(10), b blob, tx text); @@ -585,7 +585,7 @@ it2 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `it2` int(11) default NULL + `it2` int(11) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 SELECT i from t2 UNION select f from t2; @@ -799,7 +799,7 @@ select * from t1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `1` bigint(1) default NULL + `1` bigint(1) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 select _latin1"test" union select _latin2"testt" ; @@ -808,7 +808,7 @@ create table t1 select _latin2"test" union select _latin2"testt" ; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `test` char(5) character set latin2 default NULL + `test` char(5) character set latin2 NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (s char(200)); @@ -856,7 +856,7 @@ Variable_name Value Slow_queries 1 select count(*) from t1 where b=13 union select count(*) from t1 where a=7; count(*) -0 +10 26 show status like 'Slow_queries'; Variable_name Value @@ -879,17 +879,31 @@ d 444 d 454 NULL NULL f 666 NULL NULL g 777 drop table t1; -create table t1 ( id int, name char(10) not null, name2 char(10) not null ) engine=innodb; -insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt'); -select name2 from t1 union all select name from t1 union all select id from t1; -name2 -fff -sss -ttt -first -second -third +create table t1 (col1 tinyint unsigned, col2 tinyint unsigned); +insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10); +select col1 n from t1 union select col2 n from t1 order by n; +n 1 2 3 -drop table t1; +4 +5 +6 +7 +8 +9 +10 +alter table t1 add index myindex (col2); +select col1 n from t1 union select col2 n from t1 order by n; +n +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +drop table t1; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index c0f1bc5df02..5355e7ffadc 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -236,7 +236,7 @@ ERROR HY000: Unknown collation: 'UNKNOWN_COLLATION' set global autocommit=1; ERROR HY000: Variable 'autocommit' is a LOCAL variable and can't be used with SET GLOBAL select @@global.timestamp; -ERROR HY000: Variable 'timestamp' is a LOCAL variable and can't be used with SET GLOBAL +ERROR HY000: Variable 'timestamp' is a LOCAL variable set @@version=''; ERROR HY000: Unknown system variable 'version' set @@concurrent_insert=1; @@ -244,7 +244,7 @@ ERROR HY000: Variable 'concurrent_insert' is a GLOBAL variable and should be set set @@global.sql_auto_is_null=1; ERROR HY000: Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL select @@global.sql_auto_is_null; -ERROR HY000: Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL +ERROR HY000: Variable 'sql_auto_is_null' is a LOCAL variable set myisam_max_sort_file_size=100; ERROR HY000: Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL set myisam_max_extra_sort_file_size=100; @@ -380,6 +380,8 @@ ERROR HY000: Unknown system variable 'xxxxxxxxxx' select 1; 1 1 +select @@session.key_buffer_size; +ERROR HY000: Variable 'key_buffer_size' is a GLOBAL variable select @@global.max_user_connections,@@local.max_join_size; @@global.max_user_connections @@session.max_join_size 100 200 diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index a4620701d91..ae1605c99f4 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -4,19 +4,19 @@ create table t1 (a int); insert into t1 values (1); insert into t1 values ("hej"); Warnings: -Warning 1264 Data truncated for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 1 insert into t1 values ("hej"),("då"); Warnings: -Warning 1264 Data truncated for column 'a' at row 1 -Warning 1264 Data truncated for column 'a' at row 2 +Warning 1265 Data truncated for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 2 set SQL_WARNINGS=1; insert into t1 values ("hej"); Warnings: -Warning 1264 Data truncated for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 1 insert into t1 values ("hej"),("då"); Warnings: -Warning 1264 Data truncated for column 'a' at row 1 -Warning 1264 Data truncated for column 'a' at row 2 +Warning 1265 Data truncated for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 2 drop table t1; set SQL_WARNINGS=0; drop temporary table if exists not_exists; @@ -43,13 +43,13 @@ drop table t1; create table t1(a tinyint, b int not null, c date, d char(5)); load data infile '../../std_data/warnings_loaddata.dat' into table t1 fields terminated by ','; Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'b' at row 2 -Warning 1264 Data truncated for column 'd' at row 3 -Warning 1264 Data truncated for column 'c' at row 4 -Warning 1260 Row 5 doesn't contain data for all columns -Warning 1264 Data truncated for column 'b' at row 6 -Warning 1261 Row 7 was truncated; It contained more data than there where input columns -Warning 1263 Data truncated, out of range for column 'a' at row 8 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'b' at row 2 +Warning 1265 Data truncated for column 'd' at row 3 +Warning 1265 Data truncated for column 'c' at row 4 +Warning 1261 Row 5 doesn't contain data for all columns +Warning 1265 Data truncated for column 'b' at row 6 +Warning 1262 Row 7 was truncated; It contained more data than there where input columns +Warning 1264 Data truncated, out of range for column 'a' at row 8 select @@warning_count; @@warning_count 7 @@ -57,44 +57,44 @@ drop table t1; create table t1(a tinyint NOT NULL, b tinyint unsigned, c char(5)); insert into t1 values(NULL,100,'mysql'),(10,-1,'mysql ab'),(500,256,'open source'),(20,NULL,'test'); Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'a' at row 1 -Warning 1263 Data truncated, out of range for column 'b' at row 2 -Warning 1264 Data truncated for column 'c' at row 2 -Warning 1263 Data truncated, out of range for column 'a' at row 3 -Warning 1263 Data truncated, out of range for column 'b' at row 3 -Warning 1264 Data truncated for column 'c' at row 3 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'a' at row 1 +Warning 1264 Data truncated, out of range for column 'b' at row 2 +Warning 1265 Data truncated for column 'c' at row 2 +Warning 1264 Data truncated, out of range for column 'a' at row 3 +Warning 1264 Data truncated, out of range for column 'b' at row 3 +Warning 1265 Data truncated for column 'c' at row 3 alter table t1 modify c char(4); Warnings: -Warning 1264 Data truncated for column 'c' at row 1 -Warning 1264 Data truncated for column 'c' at row 2 +Warning 1265 Data truncated for column 'c' at row 1 +Warning 1265 Data truncated for column 'c' at row 2 alter table t1 add d char(2); update t1 set a=NULL where a=10; Warnings: -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'a' at row 2 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'a' at row 2 update t1 set c='mysql ab' where c='test'; Warnings: -Warning 1264 Data truncated for column 'c' at row 4 +Warning 1265 Data truncated for column 'c' at row 4 update t1 set d=c; Warnings: -Warning 1264 Data truncated for column 'd' at row 1 -Warning 1264 Data truncated for column 'd' at row 2 -Warning 1264 Data truncated for column 'd' at row 3 -Warning 1264 Data truncated for column 'd' at row 4 +Warning 1265 Data truncated for column 'd' at row 1 +Warning 1265 Data truncated for column 'd' at row 2 +Warning 1265 Data truncated for column 'd' at row 3 +Warning 1265 Data truncated for column 'd' at row 4 create table t2(a tinyint NOT NULL, b char(3)); insert into t2 select b,c from t1; Warnings: -Warning 1264 Data truncated for column 'b' at row 1 -Warning 1264 Data truncated for column 'b' at row 2 -Warning 1264 Data truncated for column 'b' at row 3 -Warning 1262 Data truncated, NULL supplied to NOT NULL column 'a' at row 4 -Warning 1264 Data truncated for column 'b' at row 4 +Warning 1265 Data truncated for column 'b' at row 1 +Warning 1265 Data truncated for column 'b' at row 2 +Warning 1265 Data truncated for column 'b' at row 3 +Warning 1263 Data truncated, NULL supplied to NOT NULL column 'a' at row 4 +Warning 1265 Data truncated for column 'b' at row 4 insert into t2(b) values('mysqlab'); Warnings: -Warning 1264 Data truncated for column 'b' at row 1 +Warning 1265 Data truncated for column 'b' at row 1 set sql_warnings=1; insert into t2(b) values('mysqlab'); Warnings: -Warning 1264 Data truncated for column 'b' at row 1 +Warning 1265 Data truncated for column 'b' at row 1 set sql_warnings=0; drop table t1, t2; create table t1(a char(10)); @@ -102,34 +102,34 @@ alter table t1 add b char; set max_error_count=10; update t1 set b=a; Warnings: -Warning 1264 Data truncated for column 'b' at row 1 -Warning 1264 Data truncated for column 'b' at row 2 -Warning 1264 Data truncated for column 'b' at row 3 -Warning 1264 Data truncated for column 'b' at row 4 -Warning 1264 Data truncated for column 'b' at row 5 -Warning 1264 Data truncated for column 'b' at row 6 -Warning 1264 Data truncated for column 'b' at row 7 -Warning 1264 Data truncated for column 'b' at row 8 -Warning 1264 Data truncated for column 'b' at row 9 -Warning 1264 Data truncated for column 'b' at row 10 +Warning 1265 Data truncated for column 'b' at row 1 +Warning 1265 Data truncated for column 'b' at row 2 +Warning 1265 Data truncated for column 'b' at row 3 +Warning 1265 Data truncated for column 'b' at row 4 +Warning 1265 Data truncated for column 'b' at row 5 +Warning 1265 Data truncated for column 'b' at row 6 +Warning 1265 Data truncated for column 'b' at row 7 +Warning 1265 Data truncated for column 'b' at row 8 +Warning 1265 Data truncated for column 'b' at row 9 +Warning 1265 Data truncated for column 'b' at row 10 select @@warning_count; @@warning_count 50 drop table t1; create table t1 (id int) engine=isam; Warnings: -Warning 1265 Using storage engine MyISAM for table 't1' +Warning 1266 Using storage engine MyISAM for table 't1' alter table t1 engine=isam; Warnings: -Warning 1265 Using storage engine MyISAM for table 't1' +Warning 1266 Using storage engine MyISAM for table 't1' drop table t1; create table t1 (id int) type=heap; Warnings: -Warning 1286 'TYPE=storage_engine' is deprecated, use 'ENGINE=storage_engine' instead +Warning 1287 'TYPE=storage_engine' is deprecated, use 'ENGINE=storage_engine' instead alter table t1 type=myisam; Warnings: -Warning 1286 'TYPE=storage_engine' is deprecated, use 'ENGINE=storage_engine' instead +Warning 1287 'TYPE=storage_engine' is deprecated, use 'ENGINE=storage_engine' instead drop table t1; set table_type=MYISAM; Warnings: -Warning 1286 'table_type' is deprecated, use 'storage_engine' instead +Warning 1287 'table_type' is deprecated, use 'storage_engine' instead |