diff options
| author | monty@mysql.com <> | 2004-02-16 10:31:05 +0200 |
|---|---|---|
| committer | monty@mysql.com <> | 2004-02-16 10:31:05 +0200 |
| commit | 050af89dd8034046df593c1ac7e060ee37bfdf27 (patch) | |
| tree | e792af64f95eba198633387e3b36f50f096c3001 /mysql-test | |
| parent | 25aff697d17d023b1fe0c085f3215a1358832e5e (diff) | |
| parent | d1d48a9dd5e3a6ff3d5aadf9234d66777216b682 (diff) | |
| download | mariadb-git-050af89dd8034046df593c1ac7e060ee37bfdf27.tar.gz | |
Merge with public tree
Diffstat (limited to 'mysql-test')
106 files changed, 1459 insertions, 438 deletions
diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh index 3f7efd3d6bc..a1e5322f35d 100644 --- a/mysql-test/mysql-test-run.sh +++ b/mysql-test/mysql-test-run.sh @@ -183,7 +183,8 @@ MY_LOG_DIR="$MYSQL_TEST_DIR/var/log" # Set LD_LIBRARY_PATH if we are using shared libraries # LD_LIBRARY_PATH="$BASEDIR/lib:$BASEDIR/libmysql/.libs:$LD_LIBRARY_PATH" -export LD_LIBRARY_PATH +DYLD_LIBRARY_PATH="$BASEDIR/lib:$BASEDIR/libmysql/.libs:$DYLD_LIBRARY_PATH" +export LD_LIBRARY_PATH DYLD_LIBRARY_PATH MASTER_RUNNING=0 MASTER_MYPORT=9306 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..c2c2f548de7 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 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..95dc2ca3a2a 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 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..743d4dac254 --- /dev/null +++ b/mysql-test/r/myisam-blob.result @@ -0,0 +1,27 @@ +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 +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..96853465385 100644 --- a/mysql-test/r/raid.result +++ b/mysql-test/r/raid.result @@ -2,6 +2,8 @@ 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 TABLE IF EXISTS t1,t2; 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/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_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/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/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 diff --git a/mysql-test/std_data/init_file.dat b/mysql-test/std_data/init_file.dat new file mode 100644 index 00000000000..4236ada1142 --- /dev/null +++ b/mysql-test/std_data/init_file.dat @@ -0,0 +1 @@ +select * from mysql.user as t1, mysql.user as t2, mysql.user as t3, mysql.user as t4, mysql.user as t5, mysql.user as t6, mysql.user as t7, mysql.user as t8;
\ No newline at end of file diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index c013b2251a9..8da017e8acb 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -244,6 +244,23 @@ LOCK TABLES t1 WRITE; ALTER TABLE t1 DISABLE KEYS; SHOW INDEX FROM t1; DROP TABLE t1; +# +# Bug #2628: 'alter table t1 rename mysqltest.t1' silently drops mysqltest.t1 +# if it exists +# +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; +select * from mysqltest.t1; +--error 1050 +alter table t1 rename mysqltest.t1; +select * from t1; +select * from mysqltest.t1; +drop table t1; +drop database mysqltest; # # Bug 2361 diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test index 12b81f27147..6823dd23b73 100644 --- a/mysql-test/t/bdb.test +++ b/mysql-test/t/bdb.test @@ -808,3 +808,17 @@ 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; + +# +# Test index only read (Bug #2509) +# +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; +select a from t1; +explain select b from t1; +select b from t1; +alter table t1 modify a char(10) binary; +explain select a from t1; +select a from t1; +drop table t1; diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index 5c977983087..9bbe10eae8c 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -68,3 +68,6 @@ insert into t1 values ('10000000000000000000'); select * from t1; drop table t1; +# atof() behaviour is different of different systems. to be fixed in 4.1 +#SELECT '0x8000000000000001'+0; + diff --git a/mysql-test/t/case.test b/mysql-test/t/case.test index 9377c6c4aef..87e456baba7 100644 --- a/mysql-test/t/case.test +++ b/mysql-test/t/case.test @@ -63,14 +63,14 @@ CREATE TABLE t1 SELECT SHOW CREATE TABLE t1; DROP TABLE t1; ---error 1266 +--error 1267 SELECT CASE WHEN 1 THEN _latin1'a' COLLATE latin1_danish_ci ELSE _latin1'a' COLLATE latin1_swedish_ci END; ---error 1269 +--error 1270 SELECT CASE _latin1'a' COLLATE latin1_general_ci WHEN _latin1'a' COLLATE latin1_danish_ci THEN 1 WHEN _latin1'a' COLLATE latin1_swedish_ci THEN 2 @@ -93,9 +93,9 @@ CASE _latin1'a' WHEN _latin1'A' COLLATE latin1_bin THEN '1' ELSE 2 END # # Check COALESCE argument types aggregation ---error 1266 +--error 1267 CREATE TABLE t1 SELECT COALESCE(_latin1'a',_latin2'a'); ---error 1266 +--error 1267 CREATE TABLE t1 SELECT COALESCE('a' COLLATE latin1_swedish_ci,'b' COLLATE latin1_bin); CREATE TABLE t1 SELECT COALESCE(1), COALESCE(1.0),COALESCE('a'), diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 64134fe9990..70fa4173c76 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -74,7 +74,7 @@ create table `` (a int); drop table if exists ``; --error 1166 create table t1 (`` int); ---error 1279 +--error 1280 create table t1 (i int, index `` (i)); # @@ -157,7 +157,7 @@ SELECT @@storage_engine; CREATE TABLE t1 (a int not null); show create table t1; drop table t1; ---error 1285 +--error 1286 SET SESSION storage_engine="gemini"; SELECT @@storage_engine; CREATE TABLE t1 (a int not null); @@ -277,7 +277,7 @@ SELECT @@storage_engine; CREATE TABLE t1 (a int not null); show create table t1; drop table t1; ---error 1285 +--error 1286 SET SESSION storage_engine="gemini"; SELECT @@storage_engine; CREATE TABLE t1 (a int not null); diff --git a/mysql-test/t/ctype_collate.test b/mysql-test/t/ctype_collate.test index 5916e3da241..3599beeacc4 100644 --- a/mysql-test/t/ctype_collate.test +++ b/mysql-test/t/ctype_collate.test @@ -7,12 +7,12 @@ CREATE TABLE t1 ( latin1_f CHAR(32) CHARACTER SET latin1 NOT NULL ); ---error 1252 +--error 1253 CREATE TABLE t2 ( latin1_f CHAR(32) CHARACTER SET latin1 COLLATE koi8r_general_ci NOT NULL ); ---error 1272 +--error 1273 CREATE TABLE t2 ( latin1_f CHAR(32) CHARACTER SET latin1 COLLATE some_non_existing_col NOT NULL ); @@ -66,7 +66,7 @@ SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_swedish_ci; SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_german2_ci; SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_general_ci; SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_bin; ---error 1252 +--error 1253 SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE koi8r_general_ci; --SELECT latin1_f COLLATE koi8r FROM t1 ; @@ -76,7 +76,7 @@ SELECT latin1_f COLLATE latin1_swedish_ci AS latin1_f_as FROM t1 ORDER BY latin1 SELECT latin1_f COLLATE latin1_german2_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; SELECT latin1_f COLLATE latin1_general_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; SELECT latin1_f COLLATE latin1_bin AS latin1_f_as FROM t1 ORDER BY latin1_f_as; ---error 1252 +--error 1253 SELECT latin1_f COLLATE koi8r_general_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; @@ -87,7 +87,7 @@ SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_swedish_ci; SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_german2_ci; SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_general_ci; SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_bin; ---error 1252 +--error 1253 SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE koi8r_general_ci; @@ -98,7 +98,7 @@ SELECT DISTINCT latin1_f COLLATE latin1_swedish_ci FROM t1; SELECT DISTINCT latin1_f COLLATE latin1_german2_ci FROM t1; SELECT DISTINCT latin1_f COLLATE latin1_general_ci FROM t1; SELECT DISTINCT latin1_f COLLATE latin1_bin FROM t1; ---error 1272 +--error 1273 SELECT DISTINCT latin1_f COLLATE koi8r FROM t1; @@ -153,7 +153,7 @@ DROP TABLE t1; CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci, s2 CHAR(5) COLLATE latin1_swedish_ci); ---error 1266 +--error 1267 SELECT * FROM t1 WHERE s1 = s2; DROP TABLE t1; diff --git a/mysql-test/t/ctype_tis620-master.opt b/mysql-test/t/ctype_tis620-master.opt new file mode 100644 index 00000000000..69d47c06e42 --- /dev/null +++ b/mysql-test/t/ctype_tis620-master.opt @@ -0,0 +1 @@ +--default-character-set=tis620 diff --git a/mysql-test/t/ctype_tis620.test b/mysql-test/t/ctype_tis620.test index 82c660dfea0..b30a5fbc95a 100644 --- a/mysql-test/t/ctype_tis620.test +++ b/mysql-test/t/ctype_tis620.test @@ -62,3 +62,27 @@ SELECT hex(convert(@uC USING tis620)); SELECT hex(convert(@uD USING tis620)); SELECT hex(convert(@uE USING tis620)); SELECT hex(convert(@uF USING tis620)); + +SET NAMES tis620; + +CREATE TABLE t1 ( + recid int(11) NOT NULL auto_increment, + dyninfo text, + PRIMARY KEY (recid) +) ENGINE=MyISAM; + +show create table t1; + +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; + +DROP TABLE t1; diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index bd5d9e5c0d9..e370b545eff 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -84,7 +84,7 @@ insert into t2 values (1, 21),(2, 12),(3, 23); select * from t11; select * from t12; select * from t2; --- error 1241 +-- error 1242 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); select * from t11; select * from t12; @@ -92,7 +92,7 @@ delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select select * from t11; select * from t12; insert into t11 values (2, 12); --- error 1241 +-- error 1242 delete from t11 where t11.b <> (select b from t2 where t11.a < t2.a); select * from t11; delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a); diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index a9341ada416..0ef56191b7c 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -116,7 +116,7 @@ select mail_id, if(folder.f_description!='', folder.f_description, folder.f_nam # create table t1 (a int); insert into t1 values (1),(2),(3); --- error 1287 +-- error 1288 update (select * from t1) as t1 set a = 5; -- error 1064 delete from (select * from t1); @@ -151,13 +151,13 @@ CREATE TABLE `t1` ( INSERT INTO `t1` (N, M) VALUES (1, 0),(1, 0),(1, 0),(2, 0),(2, 0),(3, 0); UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2; select * from t1; --- error 1287 +-- error 1288 UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2, P2.N = 2; -- error 1054 UPDATE `t1` AS P1 INNER JOIN (SELECT aaaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2; delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; select * from t1; --- error 1287 +-- error 1288 delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; -- error 1054 delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index e24eb353cb3..48a3bc54240 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -12,6 +12,7 @@ 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; # nl search @@ -148,9 +149,9 @@ select * from t2 having MATCH inhalt AGAINST ('foobar'); # check of fulltext errors # ---error 1282 +--error 1283 CREATE TABLE t3 (t int(11),i text,fulltext tix (t,i)); ---error 1282 +--error 1283 CREATE TABLE t3 (t int(11),i text, j varchar(200) CHARACTER SET latin2, fulltext tix (i,j)); diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 9ff768ec76b..855a7cbd28f 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -61,11 +61,11 @@ c char(1) character set latin1 collate latin1_danish_ci ); insert into t1 values ('A','B','C'); insert into t1 values ('a','c','c'); ---error 1266 +--error 1267 select * from t1 where a in (b); ---error 1269 -select * from t1 where a in (b,c); --error 1270 +select * from t1 where a in (b,c); +--error 1271 select * from t1 where 'a' in (a,b,c); select * from t1 where 'a' in (a); select * from t1 where a in ('a'); diff --git a/mysql-test/t/func_like.test b/mysql-test/t/func_like.test index a05a2a3bdaa..8b719b077f2 100644 --- a/mysql-test/t/func_like.test +++ b/mysql-test/t/func_like.test @@ -25,6 +25,16 @@ select * from t1 where a like "%abc\d%"; drop table t1; +# +# Bug #2231 +# + +create table t1 (a varchar(10), key(a)); +insert into t1 values ('a'), ('a\\b'); +select * from t1 where a like 'a\\%' escape '#'; +select * from t1 where a like 'a\\%' escape '#' and a like 'a\\\\b'; +drop table t1; + # # Test like with non-default character set diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index b6e2d3bd725..0f473abb96d 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -29,4 +29,15 @@ explain extended select rand(999999),rand(); select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1); explain extended select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1); select degrees(pi()),radians(360); + +# +# Bug #2338 Trignometric arithmatic problems +# + +SELECT ACOS(1.0); +SELECT ASIN(1.0); +SELECT ACOS(0.2*5.0); +SELECT ACOS(0.5*2.0); +SELECT ASIN(0.8+0.2); +SELECT ASIN(1.2-0.2); explain extended select degrees(pi()),radians(360); diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 155ed459d1f..a9c2895206d 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1,5 +1,3 @@ -# Version: 3.23.29 -# # Description # ----------- # Testing string functions @@ -141,6 +139,36 @@ INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); 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; +DROP TABLE t1; + +# +# test for #739 + +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; +DROP TABLE t1; + +# +# test for Bug #2290 "output truncated with ELT when using DISTINCT" +# + +CREATE TABLE t1 (i int, j int); +INSERT INTO t1 VALUES (1,1),(2,2); +SELECT DISTINCT i, ELT(j, '345', '34') FROM t1; +DROP TABLE t1; + # # Test collation and coercibility # @@ -149,23 +177,22 @@ select 1=_latin1'1'; select _latin1'1'=1; select _latin2'1'=1; select 1=_latin2'1'; ---error 1266 +--error 1267 select _latin1'1'=_latin2'1'; select row('a','b','c') = row('a','b','c'); select row('A','b','c') = row('a','b','c'); select row('A' COLLATE latin1_bin,'b','c') = row('a','b','c'); select row('A','b','c') = row('a' COLLATE latin1_bin,'b','c'); ---error 1266 +--error 1267 select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c'); ---error 1266 +--error 1267 select concat(_latin1'a',_latin2'a'); ---error 1269 -select concat(_latin1'a',_latin2'a',_latin5'a'); --error 1270 +select concat(_latin1'a',_latin2'a',_latin5'a'); +--error 1271 select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a'); - # # Test FIELD() and collations # @@ -173,65 +200,64 @@ select FIELD('b','A','B'); select FIELD('B','A','B'); select FIELD('b' COLLATE latin1_bin,'A','B'); select FIELD('b','A' COLLATE latin1_bin,'B'); ---error 1269 +--error 1270 select FIELD(_latin2'b','A','B'); ---error 1269 +--error 1270 select FIELD('b',_latin2'A','B'); select FIELD('b',_latin2'A','B',1); - select POSITION(_latin1'B' IN _latin1'abcd'); select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin); select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd'); ---error 1266 +--error 1267 select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin); ---error 1266 +--error 1267 select POSITION(_latin1'B' IN _latin2'abcd'); select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'); --fix this: --select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d' COLLATE latin1_bin); --select FIND_IN_SET(_latin1'B' COLLATE latin1_bin,_latin1'a,b,c,d'); ---error 1266 +--error 1267 select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin); ---error 1266 +--error 1267 select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d'); select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2); --fix this: --select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_bin,_latin1'd',2); --select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd' COLLATE latin1_bin,2); ---error 1266 +--error 1267 select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2); ---error 1266 +--error 1267 select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2); select _latin1'B' between _latin1'a' and _latin1'c'; select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c'; select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c'; select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin; ---error 1269 +--error 1270 select _latin2'B' between _latin1'a' and _latin1'b'; ---error 1269 +--error 1270 select _latin1'B' between _latin2'a' and _latin1'b'; ---error 1269 +--error 1270 select _latin1'B' between _latin1'a' and _latin2'b'; ---error 1269 +--error 1270 select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b'; select _latin1'B' in (_latin1'a',_latin1'b'); select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b'); select _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b'); select _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin); ---error 1269 +--error 1270 select _latin2'B' in (_latin1'a',_latin1'b'); ---error 1269 +--error 1270 select _latin1'B' in (_latin2'a',_latin1'b'); ---error 1269 +--error 1270 select _latin1'B' in (_latin1'a',_latin2'b'); ---error 1269 +--error 1270 select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b'); ---error 1269 +--error 1270 select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin); select collation(bin(130)), coercibility(bin(130)); @@ -317,32 +343,11 @@ select SUBSTR('abcdefg',1,-1) FROM DUAL; # when an error on a lower level (in concat) has accured: # create table t7 (s1 char); ---error 1266 +--error 1267 select * from t7 where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA'; 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; -DROP TABLE t1; - -# -# test for #739 - -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; -DROP TABLE t1; - select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2); explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'mood' sounds like 'mud', aes_decrypt(aes_encrypt('abc','1'),'1'),concat('*',space(5),'*'), reverse('abc'), rpad('a',4,'1'), lpad('a',4,'1'), concat_ws(',','',NULL,'a'),make_set(255,_latin2'a',_latin2'b',_latin2'c'),elt(2,1),locate("a","b",2),format(130,10),char(0),conv(130,16,10),hex(130),binary 'HE', export_set(255,_latin2'y',_latin2'n',_latin2' '),FIELD('b' COLLATE latin1_bin,'A','B'),FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'),collation(conv(130,16,10)), coercibility(conv(130,16,10)),length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h'),quote(1/0),crc32("123"),replace('aaaa','a','b'),insert('txs',2,1,'hi'),left(_latin2'a',1),right(_latin2'a',1),lcase(_latin2'a'),ucase(_latin2'a'),SUBSTR('abcdefg',3,2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),trim(_latin2' a '),ltrim(_latin2' a '),rtrim(_latin2' a '), decode(encode(repeat("a",100000),"monty"),"monty"); diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index 0d055549866..44047491862 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -51,9 +51,9 @@ explain extended select _koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci; select _koi8r'a' = _koi8r'A' COLLATE koi8r_bin; select _koi8r'a' COLLATE koi8r_general_ci = _koi8r'A'; select _koi8r'a' COLLATE koi8r_bin = _koi8r'A'; ---error 1266 +--error 1267 select _koi8r'a' COLLATE koi8r_bin = _koi8r'A' COLLATE koi8r_general_ci; ---error 1266 +--error 1267 select _koi8r'a' = _latin1'A'; select strcmp(_koi8r'a', _koi8r'A'); @@ -61,9 +61,9 @@ select strcmp(_koi8r'a', _koi8r'A' COLLATE koi8r_general_ci); select strcmp(_koi8r'a', _koi8r'A' COLLATE koi8r_bin); select strcmp(_koi8r'a' COLLATE koi8r_general_ci, _koi8r'A'); select strcmp(_koi8r'a' COLLATE koi8r_bin, _koi8r'A'); ---error 1266 +--error 1267 select strcmp(_koi8r'a' COLLATE koi8r_general_ci, _koi8r'A' COLLATE koi8r_bin); ---error 1266 +--error 1267 select strcmp(_koi8r'a', _latin1'A'); select _koi8r'a' LIKE _koi8r'A'; @@ -71,9 +71,9 @@ select _koi8r'a' LIKE _koi8r'A' COLLATE koi8r_general_ci; select _koi8r'a' LIKE _koi8r'A' COLLATE koi8r_bin; select _koi8r'a' COLLATE koi8r_general_ci LIKE _koi8r'A'; select _koi8r'a' COLLATE koi8r_bin LIKE _koi8r'A'; ---error 1266 +--error 1267 select _koi8r'a' COLLATE koi8r_general_ci LIKE _koi8r'A' COLLATE koi8r_bin; ---error 1266 +--error 1267 select _koi8r'a' LIKE _latin1'A'; # diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index 7255f872bb7..ea7d7fa44af 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -110,14 +110,14 @@ show grants for drop_user@localhost; revoke all privileges, grant option from drop_user@localhost; show grants for drop_user@localhost; drop user drop_user@localhost; ---error 1268 +--error 1269 revoke all privileges, grant option from drop_user@localhost; grant select(a) on test.t1 to drop_user1@localhost; grant select on test.t1 to drop_user2@localhost; grant select on test.* to drop_user3@localhost; grant select on *.* to drop_user4@localhost; ---error 1267 +--error 1268 drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, drop_user4@localhost; revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost, diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test index d734882591a..72b6624509e 100644 --- a/mysql-test/t/handler.test +++ b/mysql-test/t/handler.test @@ -110,3 +110,19 @@ handler t1 read a=(W); --error 1210 handler t1 read a=(a); drop table t1; +# +# BUG#2304 +# +create table t1 (a char(5)); +insert into t1 values ("Ok"); +handler t1 open as t; +handler t read first; +use mysql; +handler t read first; +handler t close; +handler test.t1 open as t; +handler t read first; +handler t close; +use test; +drop table t1; + diff --git a/mysql-test/t/init_file-master.opt b/mysql-test/t/init_file-master.opt new file mode 100644 index 00000000000..825311dabf2 --- /dev/null +++ b/mysql-test/t/init_file-master.opt @@ -0,0 +1 @@ +--init-file=$MYSQL_TEST_DIR/std_data/init_file.dat diff --git a/mysql-test/t/init_file.test b/mysql-test/t/init_file.test new file mode 100644 index 00000000000..604d0a01794 --- /dev/null +++ b/mysql-test/t/init_file.test @@ -0,0 +1,7 @@ +# +# This is a regression test for bug #2526 "--init-file crashes MySQL if it +# contains a large select" +# +# See mysql-test/std_data/init_file.dat and +# mysql-test/t/init_file-master.opt for the actual test +# diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index b227614e7ae..ada2771fdc2 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -753,8 +753,7 @@ insert into t2 set id=1, t1_id=1; delete t1,t2 from t1,t2 where t1.id=t2.t1_id; select * from t1; select * from t2; -drop table t1,t2; -DROP TABLE IF EXISTS t1,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); @@ -934,6 +933,15 @@ SELECT * from t1; drop table t1; # +# Test dictionary handling with spaceand quoting +# + +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; +#show create table t2; +drop table t2,t1; + +# # Test timestamps # diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index e39e470cf31..234a5ec0eef 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -135,3 +135,34 @@ 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; + +# +# Another problem from Bug #2012 +# + +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; + +Select null, Field, Count From t1 Where Month=20030901 and Type=2; + +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; + +select * from t2; + +drop table t1, t2; diff --git a/mysql-test/t/key_cache.test b/mysql-test/t/key_cache.test index d9a2200a636..2bf5cdbcf6f 100644 --- a/mysql-test/t/key_cache.test +++ b/mysql-test/t/key_cache.test @@ -41,7 +41,7 @@ SET @@global.key_buffer_size=@save_key_buffer; --error 1064 SELECT @@default.key_buffer_size; ---error 1271 +--error 1272 SELECT @@skr.storage_engine="test"; select @@keycache1.key_cache_block_size; @@ -99,7 +99,7 @@ explain select a from t2; select a from t2; # Test some error conditions ---error 1283 +--error 1284 cache index t1 in unknown_key_cache; cache index t1 key (unknown_key) in keycache1; diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test index 69c453bf60a..26fc4e32bda 100644 --- a/mysql-test/t/lock.test +++ b/mysql-test/t/lock.test @@ -53,5 +53,9 @@ check table t1; # Check error message lock tables t1 write; check table t2; +--error 1100 +insert into t1 select nr from t1; 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/t/lowercase_table2-master.opt b/mysql-test/t/lowercase_table2-master.opt new file mode 100644 index 00000000000..9b27aef9bf8 --- /dev/null +++ b/mysql-test/t/lowercase_table2-master.opt @@ -0,0 +1 @@ +--lower_case_table_names=0 diff --git a/mysql-test/t/lowercase_table2.test b/mysql-test/t/lowercase_table2.test new file mode 100644 index 00000000000..86bb26f0cf9 --- /dev/null +++ b/mysql-test/t/lowercase_table2.test @@ -0,0 +1,80 @@ +# +# Test of --lower-case-table-names=2 +# (User has case insensitive file system and want's to preserve case of +# table names) +# +--source include/have_innodb.inc +--require r/lowercase2.require +disable_query_log; +show variables like "lower_case_table_names"; +enable_query_log; + +--disable_warnings +DROP TABLE IF EXISTS t1,t2,T1,T2,t3,T3; +DROP DATABASE IF EXISTS `TEST_$1`; +DROP DATABASE IF EXISTS `test_$1`; +--enable_warnings + +CREATE TABLE T1 (a int); +INSERT INTO T1 VALUES (1); +SHOW TABLES LIKE "T1"; +SHOW TABLES LIKE "t1"; +SHOW CREATE TABLE T1; +RENAME TABLE T1 TO T2; +SHOW TABLES LIKE "T2"; +SELECT * FROM t2; +RENAME TABLE T2 TO t3; +SHOW TABLES LIKE "T3"; +RENAME TABLE T3 TO T1; +SHOW TABLES LIKE "T1"; +ALTER TABLE T1 add b int; +SHOW TABLES LIKE "T1"; +ALTER TABLE T1 RENAME T2; +SHOW TABLES LIKE "T2"; + +LOCK TABLE T2 WRITE; +ALTER TABLE T2 drop b; +SHOW TABLES LIKE "T2"; +UNLOCK TABLES; +RENAME TABLE T2 TO T1; +SHOW TABLES LIKE "T1"; +SELECT * from T1; +DROP TABLE T1; + +# +# Test database level +# + +CREATE DATABASE `TEST_$1`; +SHOW DATABASES LIKE "TEST%"; +DROP DATABASE `test_$1`; + +# +# Test of innodb tables with lower_case_table_names=2 +# + +CREATE TABLE T1 (a int) engine=innodb; +INSERT INTO T1 VALUES (1); +SHOW TABLES LIKE "T1"; +SHOW TABLES LIKE "t1"; +SHOW CREATE TABLE T1; +RENAME TABLE T1 TO T2; +SHOW TABLES LIKE "T2"; +SELECT * FROM t2; +RENAME TABLE T2 TO t3; +SHOW TABLES LIKE "T3"; +RENAME TABLE T3 TO T1; +SHOW TABLES LIKE "T1"; +ALTER TABLE T1 add b int; +SHOW TABLES LIKE "T1"; +ALTER TABLE T1 RENAME T2; +SHOW TABLES LIKE "T2"; + +LOCK TABLE T2 WRITE; +ALTER TABLE T2 drop b; +SHOW TABLES LIKE "T2"; +UNLOCK TABLES; +RENAME TABLE T2 TO T1; +SHOW TABLES LIKE "T1"; +SELECT * from T1; +DROP TABLE T1; diff --git a/mysql-test/t/myisam-blob-master.opt b/mysql-test/t/myisam-blob-master.opt new file mode 100644 index 00000000000..1a1076c7bad --- /dev/null +++ b/mysql-test/t/myisam-blob-master.opt @@ -0,0 +1 @@ +--max-allowed-packet=24M --skip-innodb --key-buffer-size=1M diff --git a/mysql-test/t/myisam-blob.test b/mysql-test/t/myisam-blob.test new file mode 100644 index 00000000000..d58222ec8bf --- /dev/null +++ b/mysql-test/t/myisam-blob.test @@ -0,0 +1,30 @@ +# +# Test bugs in the MyISAM code with blobs +# + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# Bug #2159 (Problem with update of blob to > 16M) + +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; +delete from t1 where left(data,1)='a'; +check table t1; +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; + +# now we have two blocks in the table, first is a 1M record and second is +# a 16M delete block. + +UPDATE t1 set data=repeat('c',17*1024*1024); +check table t1; +delete from t1 where left(data,1)='c'; +check table t1; +drop table t1; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 85e5c7a7751..c407cba4800 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -399,6 +399,55 @@ check table t1; drop table t1; # +# two bugs in myisam-space-stripping feature +# +create table t1 ( a text not null, key a (a(20))); +insert into t1 values ('aaa '),('aaa'),('aa'); +check table t1; +repair table t1; +select concat(a,'.') from t1 where a='aaa'; +select concat(a,'.') from t1 where binary a='aaa'; +update t1 set a='bbb' where a='aaa'; +select concat(a,'.') from t1; +drop table t1; + +# +# Third bug in the same code (BUG#2295) +# + +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'; +drop table t1; + +# +# Verify blob handling +# +create table t1 (a blob); +insert into t1 values('a '),('a'); +select concat(a,'.') from t1 where a='a'; +select concat(a,'.') from t1 where a='a '; +alter table t1 add key(a(2)); +select concat(a,'.') from t1 where a='a'; +select concat(a,'.') from t1 where a='a '; +drop table t1; + +# +# Test text and unique +# +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; +update t1 set b='b ' where a=2; +--error 1062 +update t1 set b='b ' where a > 1; +delete from t1 where b='b'; +select a,concat(b,'.') from t1; +drop table t1; + +# # Test RTREE index # --error 1235 diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index 2d1277a1cbc..07adf2732dc 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -64,10 +64,6 @@ select "--- --position --" as ""; # These are tests for remote binlog. # They should return the same as previous test. -# But now they are not. V. Vagin should fix this. -# We test all the same options second time since code for remote case is -# essentially different. If code for both cases will be unified we'll be -# able to throw out most of this. --disable_query_log select "--- Remote --" as ""; diff --git a/mysql-test/t/raid.test b/mysql-test/t/raid.test index 0d6e851a153..457262a8d75 100644 --- a/mysql-test/t/raid.test +++ b/mysql-test/t/raid.test @@ -18,6 +18,7 @@ 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 ( diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index f01d8d4eacf..642dac471da 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -229,7 +229,7 @@ SELECT * FROM t1 WHERE a IN(1,2) AND b=5; DROP TABLE t1; # -# Test error with +# Test problem with range optimzer and sub ranges # CREATE TABLE t1 (a int, b int, c int, INDEX (c,a,b)); @@ -240,6 +240,22 @@ SELECT COUNT(*) FROM t1 WHERE (c=0 and a=1) or (c=0 and b=1); SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1); DROP TABLE t1; +# +# Test problem with range optimization over overlapping ranges (#2448) +# + +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) +); +DROP TABLE t1; + # test for a bug with in() and unique key create table t1 (id int(10) primary key); diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test index ecb0c6adfc1..62e8eb7991c 100644 --- a/mysql-test/t/row.test +++ b/mysql-test/t/row.test @@ -15,7 +15,7 @@ select row('b',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3)); select row('b',1.5,3) IN (row('b',NULL,3), row('a',1.5,3), row(1,3,3)); select row('b',1.5,3) IN (row('b',NULL,4), row('a',1.5,3), row(1,3,3)); select (1,2,(3,4)) IN ((3,2,(3,4)), (1,2,(3,4))); --- error 1240 +-- error 1241 select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,4)); select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL))); explain extended select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL))); @@ -37,12 +37,12 @@ SELECT ROW(NULL,2,3)=ROW(NULL,2,3); SELECT ROW(NULL,2,3)<=>ROW(NULL,2,3); SELECT ROW(1,2,ROW(3,4,5))=ROW(1,2,ROW(3,4,5)); SELECT ROW('test',2,3.33)=ROW('test',2,3.33); --- error 1240 +-- error 1241 SELECT ROW('test',2,3.33)=ROW('test',2,3.33,4); SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,33)); SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,3)); SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,NULL)); --- error 1240 +-- error 1241 SELECT ROW('test',2,ROW(3,33))=ROW('test',2,4); create table t1 ( a int, b int, c int); @@ -56,15 +56,15 @@ select ROW(a,b,c) IN(row(1,2,3), row(3,2,1)) from t1; select ROW(1,2,3) IN(row(a,b,c), row(1,2,3)) from t1; drop table t1; --- error 1240 +-- error 1241 select ROW(1,1); create table t1 (i int); --- error 1240 +-- error 1241 select 1 from t1 where ROW(1,1); --- error 1240 +-- error 1241 select count(*) from t1 order by ROW(1,1); --- error 1240 +-- error 1241 select count(*) from t1 having (1,1) order by i; drop table t1; diff --git a/mysql-test/t/rpl_heap.test b/mysql-test/t/rpl_heap.test new file mode 100644 index 00000000000..15f61918034 --- /dev/null +++ b/mysql-test/t/rpl_heap.test @@ -0,0 +1,47 @@ +# You must run this test with --manager. + +require_manager; + +# Don't know why, but using TCP/IP connections makes this test fail +# with "Lost connection to MySQL server during query" when we +# issue a query after the server restart. +# Maybe this is something awkward in mysqltest or in the manager? +# So we use sockets. +connect (master,localhost,root,,test,0,master.sock); +connect (slave,localhost,root,,test,0,slave.sock); + +connection master; +reset master; +drop table if exists t1; +create table t1 (a int) type=HEAP; +insert into t1 values(10); +save_master_pos; +show binlog events from 79; +connection slave; +reset slave; +start slave; +sync_with_master; +show create table t1; +select * from t1; # should be one row + +server_stop master; +server_start master; + +connection master; +select * from t1; +# to check that DELETE is not written twice +# (the LIMIT is to not use the query cache) +select * from t1 limit 10; +save_master_pos; +show binlog events in 'master-bin.002' from 79; + +connection slave; +sync_with_master; +select * from t1; # should be empty + +# clean up +connection master; +drop table t1; +save_master_pos; +connection slave; +sync_with_master; diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index 8244c323a0d..4c27c03af60 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -41,8 +41,8 @@ connection master; # check if INSERT SELECT in auto_increment is well replicated (bug #490) -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/t/rpl_multi_delete-slave.opt b/mysql-test/t/rpl_multi_delete-slave.opt new file mode 100644 index 00000000000..c98fe0b0a46 --- /dev/null +++ b/mysql-test/t/rpl_multi_delete-slave.opt @@ -0,0 +1 @@ +--replicate-wild-do-table=test.% diff --git a/mysql-test/t/rpl_multi_delete.test b/mysql-test/t/rpl_multi_delete.test new file mode 100644 index 00000000000..299cb720b62 --- /dev/null +++ b/mysql-test/t/rpl_multi_delete.test @@ -0,0 +1,23 @@ +source include/master-slave.inc; +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; + +save_master_pos; +select * from t1; +select * from t2; + +connection slave; +sync_with_master; +select * from t1; +select * from t2; + +connection master; +drop table t1,t2; +save_master_pos; +connection slave; +sync_with_master; diff --git a/mysql-test/t/rpl_until.test b/mysql-test/t/rpl_until.test index 40d36110296..5eaec0727b6 100644 --- a/mysql-test/t/rpl_until.test +++ b/mysql-test/t/rpl_until.test @@ -67,15 +67,15 @@ real_sleep 4 show slave status; #testing various error conditions ---error 1276 +--error 1277 start slave until master_log_file='master-bin', master_log_pos=561; ---error 1276 +--error 1277 start slave until master_log_file='master-bin.000001', master_log_pos=561, relay_log_pos=12; ---error 1276 +--error 1277 start slave until master_log_file='master-bin.000001'; ---error 1276 +--error 1277 start slave until relay_log_file='slave-relay-bin.000002'; ---error 1276 +--error 1277 start slave until relay_log_file='slave-relay-bin.000002', master_log_pos=561; start slave sql_thread; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 446c8768146..0a3de178456 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1862,3 +1862,19 @@ select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 left join t4 on id3 = id4 where id2 = 1 or id4 = 1; drop table t1,t2,t3,t4; +# +# Bug #2298 +# + +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'; +select * from t2 where s = 'one'; +select * from t3 where s = 'one'; +select * from t1,t2 where t1.s = t2.s; +select * from t2,t3 where t2.s = t3.s; +drop table t1, t2, t3; diff --git a/mysql-test/t/select_found.test b/mysql-test/t/select_found.test index 3cf736cafb0..e63e894c1cf 100644 --- a/mysql-test/t/select_found.test +++ b/mysql-test/t/select_found.test @@ -109,3 +109,31 @@ select FOUND_ROWS(); select SQL_CALC_FOUND_ROWS * from t1 where id > 6 limit 0, 1; select FOUND_ROWS(); drop table t1; + +# +# Other bugs with range optimization +# + +# bug #2448 + +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 ) +); +DROP TABLE t1; + +# bug #2698 + +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/t/subselect.test b/mysql-test/t/subselect.test index 419033d26b6..42fb2afbe89 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -9,12 +9,12 @@ SELECT (SELECT 1) UNION SELECT (SELECT 2); explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2); SELECT (SELECT (SELECT 0 UNION SELECT 0)); explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0)); --- error 1246 +-- error 1247 SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a; --- error 1246 +-- error 1247 SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b; SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a; --- error 1246 +-- error 1247 SELECT (SELECT a) as a; EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; @@ -24,7 +24,7 @@ SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1; -- error 1054 SELECT 1 FROM (SELECT (SELECT a) b) c; SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id); --- error 1240 +-- error 1241 SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1); SELECT 1 IN (SELECT 1); SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); @@ -51,7 +51,7 @@ SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a'); SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a'); SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a'); --- error 1240 +-- error 1241 SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a); SELECT 1 as a,(SELECT a+a) b,(SELECT b); @@ -63,7 +63,7 @@ create table t4 (a int not null, b int not null); insert into t1 values (2); insert into t2 values (1,7),(2,7); insert into t4 values (4,8),(3,8),(5,9); --- error 1246 +-- error 1247 select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1; select (select a from t1 where t1.a=t2.a), a from t2; select (select a from t1 where t1.a=t2.b), a from t2; @@ -103,9 +103,9 @@ select * from t3 where a >= any (select b from t2); explain extended select * from t3 where a >= any (select b from t2); select * from t3 where a >= all (select b from t2); delete from t2 where a=100; --- error 1240 +-- error 1241 select * from t3 where a in (select a,b from t2); --- error 1240 +-- error 1241 select * from t3 where a in (select * from t2); insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10); -- empty set @@ -121,7 +121,7 @@ select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a) insert into t5 values (2); select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; explain extended select (select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a), a from t2; --- error 1241 +-- error 1242 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)); create table t7( uq int primary key, name char(25)); @@ -158,14 +158,14 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce','test'); INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); --- error 1240 +-- error 1241 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); --- error 1240 +-- error 1241 SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE pseudo='joce'); SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); --- error 1241 +-- error 1242 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%'); drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; @@ -185,7 +185,7 @@ EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'); SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1; --- error 1241 +-- error 1242 SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1; EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1); drop table t1; @@ -242,9 +242,9 @@ CREATE TABLE `t1` ( ) ENGINE=MyISAM ROW_FORMAT=FIXED; INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1); --- error 1241 +-- error 1242 select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1); --- error 1241 +-- error 1242 select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1); drop table t1; @@ -256,7 +256,7 @@ drop table t1; #iftest CREATE TABLE t1 (field char(1) NOT NULL DEFAULT 'b'); INSERT INTO t1 VALUES (); --- error 1241 +-- error 1242 SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b'); drop table t1; @@ -269,13 +269,13 @@ CREATE TABLE `t1` ( UNIQUE KEY `numreponse` (`numreponse`), KEY `pseudo` (`pseudo`,`numeropost`) ) ENGINE=MyISAM; --- error 1246 +-- error 1247 SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a; -- error 1054 SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a; SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); --- error 1241 +-- error 1242 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); @@ -294,7 +294,7 @@ insert into t2 values (1, 21),(2, 22),(3, 23); select * from t1; -- error 1093 update t1 set b= (select b from t1); --- error 1241 +-- error 1242 update t1 set b= (select b from t2); update t1 set b= (select b from t2 where t1.a = t2.a); select * from t1; @@ -309,7 +309,7 @@ select * from t1; select * from t1 where b = (select b from t2 where t1.a = t2.a); -- error 1093 delete from t1 where b = (select b from t1); --- error 1241 +-- error 1242 delete from t1 where b = (select b from t2); delete from t1 where b = (select b from t2 where t1.a = t2.a); select * from t1; @@ -327,7 +327,7 @@ select * from t11; select * from t12; -- error 1093 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); --- error 1241 +-- error 1242 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); select * from t11; @@ -342,7 +342,7 @@ insert into t2 values (1); insert into t3 values (1),(2); -- error 1093 INSERT INTO t1 (x) VALUES ((SELECT x FROM t1)); --- error 1241 +-- error 1242 INSERT INTO t1 (x) VALUES ((SELECT b FROM t3)); INSERT INTO t1 (x) VALUES ((SELECT a FROM t2)); select * from t1; @@ -373,7 +373,7 @@ insert into t3 values (1),(2); select * from t1; -- error 1093 replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2)); --- error 1241 +-- error 1242 replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2)); replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2)); select * from t1; @@ -409,7 +409,7 @@ INSERT INTO t2 VALUES ((SELECT id FROM t2)); SELECT * FROM t2; CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 values (1),(1); --- error 1241 +-- error 1242 UPDATE t2 SET id=(SELECT * FROM t1); drop table t2, t1; @@ -867,9 +867,9 @@ DROP TABLE t1, t2; CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci, s2 CHAR(5) COLLATE latin1_swedish_ci); INSERT INTO t1 VALUES ('z','?'); --- error 1266 +-- error 1267 select * from t1 where s1 > (select max(s2) from t1); --- error 1266 +-- error 1267 select * from t1 where s1 > any (select max(s2) from t1); drop table t1; @@ -887,7 +887,7 @@ drop table t1, t2; # row union # create table t1 (s1 char(5)); --- error 1240 +-- error 1241 select (select 'a','b' from t1 union select 'a','b' from t1) from t1; insert into t1 values ('tttt'); select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1); @@ -952,7 +952,7 @@ CREATE TABLE t1 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES (1),(5); CREATE TABLE t2 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t2 VALUES (2),(6); --- error 1240 +-- error 1241 select * from t1 where (1,2,6) in (select * from t2); DROP TABLE t1,t2; @@ -962,7 +962,7 @@ DROP TABLE t1,t2; create table t1 (s1 int); insert into t1 values (1); insert into t1 values (2); --- error 1241 +-- error 1242 set sort_buffer_size = (select s1 from t1); do (select * from t1); drop table t1; diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index 8e8d41f7653..47642832158 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -67,7 +67,7 @@ INSERT INTO t1 VALUES (1),(2),(3); INSERT INTO t3 VALUES (1,1),(2,2),(3,3); INSERT INTO t2 VALUES (1,1),(2,2),(3,3); SELECT distinct p1.processor_id, (SELECT y.yod_id FROM t1 p2, t2 y WHERE p2.processor_id = p1.processor_id and p2.processor_id = y.processor_id) FROM t1 p1; -drop table t1,t2,t3; +drop table t2,t1,t3; # # innodb locking diff --git a/mysql-test/t/timezone.test b/mysql-test/t/timezone.test index ab732c11a34..ba65eb72fe6 100644 --- a/mysql-test/t/timezone.test +++ b/mysql-test/t/timezone.test @@ -38,3 +38,10 @@ INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 04:00:01')); SELECT ts,from_unixtime(ts) FROM t1; DROP TABLE t1; +# +# Test for fix for Bug#2523 +# +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'); diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test index 70fa2f9b172..c826e59b29d 100644 --- a/mysql-test/t/type_blob.test +++ b/mysql-test/t/type_blob.test @@ -357,3 +357,9 @@ select * from t1 where txt <= 'Chevy'; select * from t1 where txt > 'Chevy'; select * from t1 where txt >= 'Chevy'; 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 = ''; +drop table t1; + + diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test index fc7cd019171..425e038956e 100644 --- a/mysql-test/t/type_timestamp.test +++ b/mysql-test/t/type_timestamp.test @@ -76,8 +76,35 @@ select * from t1; drop table t1; # -# Bug #1885 +# Bug #1885, bug #2539. +# Not perfect but still sensible attitude towards defaults for TIMESTAMP +# We will ignore default value for first TIMESTAMP column. # +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; +show create table t1; +show columns from t1; +show columns from t1 like 't2'; +create table t2 (select * from t1); +show create table t2; + +# Ugly, but we can't do anything about this in 4.0 +alter table t1 add column t0 timestamp first; +show create table t1; ---error 1067 -create table t1 (a timestamp default 1); +drop table t1,t2; + +# +# Test for bug 2464, DEFAULT keyword in INSERT statement should return +# default value for column. +# + +create table t1 (ts1 timestamp, ts2 timestamp); +set TIMESTAMP=1000000000; +insert into t1 values (); +insert into t1 values (DEFAULT, DEFAULT); +select * from t1; +drop table t1; diff --git a/mysql-test/t/type_year.test b/mysql-test/t/type_year.test index 9922df447fc..c67b8447494 100644 --- a/mysql-test/t/type_year.test +++ b/mysql-test/t/type_year.test @@ -11,3 +11,12 @@ select * from t1; select * from t1 order by y; select * from t1 order by y2; drop table t1; + +# +# Bug 2335 +# + +create table t1 (y year); +insert into t1 values (now()); +select if(y = now(), 1, 0) from t1; +drop table t1; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 6c3a22a33cf..c88ac4e70d8 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -23,7 +23,7 @@ select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 g (select a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 4; (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1); (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; ---error 1249 +--error 1250 (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by t1.b; explain extended (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; (select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; @@ -420,7 +420,7 @@ create table t1 select 1 union select -1; select * from t1; show create table t1; drop table t1; --- error 1266 +-- error 1267 create table t1 select _latin1"test" union select _latin2"testt" ; create table t1 select _latin2"test" union select _latin2"testt" ; show create table t1; diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 32ed6fe33db..a28b327cf58 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -84,7 +84,7 @@ select @a=_latin2'TEST' collate latin2_bin; set @a=_latin2'test' collate latin2_general_ci; select charset(@a),collation(@a),coercibility(@a); select @a=_latin2'TEST'; ---error 1266 +--error 1267 select @a=_latin2'TEST' collate latin2_bin; # @@ -97,5 +97,5 @@ select collation(@a:=_latin2'test' collate latin2_bin); select coercibility(@a:=_latin2'test' collate latin2_bin); select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST'; select charset(@a),collation(@a),coercibility(@a); ---error 1266 +--error 1267 select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci; diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index e2537532eb4..05ac0427a92 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -124,7 +124,7 @@ set big_tables="OFFF"; set unknown_variable=1; --error 1232 set max_join_size="hello"; ---error 1285 +--error 1286 set storage_engine=UNKNOWN_TABLE_TYPE; --error 1231 set storage_engine=INNODB, big_tables=2; @@ -135,11 +135,11 @@ set SESSION query_cache_size=10000; set GLOBAL storage_engine=DEFAULT; --error 1115 set character_set_client=UNKNOWN_CHARACTER_SET; ---error 1272 +--error 1273 set collation_connection=UNKNOWN_COLLATION; --error 1228 set global autocommit=1; ---error 1228 +--error 1238 select @@global.timestamp; --error 1193 set @@version=''; @@ -147,7 +147,7 @@ set @@version=''; set @@concurrent_insert=1; --error 1228 set @@global.sql_auto_is_null=1; ---error 1228 +--error 1238 select @@global.sql_auto_is_null; --error 1229 set myisam_max_sort_file_size=100; @@ -267,6 +267,9 @@ drop table t1,t2; select @@xxxxxxxxxx; select 1; +--error 1238 +select @@session.key_buffer_size; + # # swap # @@ -282,4 +285,3 @@ select @@global.max_user_connections,@@local.max_join_size; set @a=1, @b=2; set @a=@b, @b=@a; select @a, @b; - |