diff options
Diffstat (limited to 'mysql-test')
28 files changed, 397 insertions, 35 deletions
diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh index 265ff036998..3f7efd3d6bc 100644 --- a/mysql-test/mysql-test-run.sh +++ b/mysql-test/mysql-test-run.sh @@ -603,7 +603,7 @@ error () { error_is () { $ECHO "Errors are (from $TIMEFILE) :" $CAT < $TIMEFILE - $ECHO "(the last line(s) may be the ones that caused the die() in mysqltest)" + $ECHO "(the last lines may be the most important ones)" } prefix_to_8() { @@ -1309,6 +1309,9 @@ run_testcase () skip_inc $ECHO "$RES$RES_SPACE [ skipped ]" else + if [ $res -gt 2 ]; then + $ECHO "mysqltest returned unexpected code $res, it has probably crashed" >> $TIMEFILE + fi total_inc fail_inc $ECHO "$RES$RES_SPACE [ fail ]" diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 7ec12c1b021..33af0b30d1c 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -412,3 +412,12 @@ 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 (a int UNIQUE); +ALTER TABLE t1 DROP PRIMARY KEY; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) default NULL, + UNIQUE KEY `a` (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/r/ctype_recoding.result b/mysql-test/r/ctype_recoding.result index 571c89ef467..7209c86bb31 100644 --- a/mysql-test/r/ctype_recoding.result +++ b/mysql-test/r/ctype_recoding.result @@ -19,6 +19,12 @@ SELECT HEX(a) FROM t2; HEX(a) D0BFD180D0BED0B1D0B0 DROP TABLE t1, t2; +CREATE TABLE t1 (description text character set cp1250 NOT NULL); +INSERT INTO t1 (description) VALUES (_latin2'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaasssssssssssaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddde'); +SELECT description FROM t1; +description +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaasssssssssssaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddde +DROP TABLE t1; CREATE TABLE t1 (a TEXT CHARACTER SET cp1251) SELECT _koi8r'ÐÒÏÂÁ' AS a; CREATE TABLE t2 (a TEXT CHARACTER SET utf8); SHOW CREATE TABLE t1; diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 2f6dc0c23ca..d6e9cc690a2 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -173,6 +173,12 @@ SELECT * FROM t1 WHERE word LIKE _ucs2 x'00630061005F'; word cat DROP TABLE t1; +select insert(_ucs2 0x006100620063,10,2,_ucs2 0x006400650066); +insert(_ucs2 0x006100620063,10,2,_ucs2 0x006400650066) +abc +select insert(_ucs2 0x006100620063,1,2,_ucs2 0x006400650066); +insert(_ucs2 0x006100620063,1,2,_ucs2 0x006400650066) +defc SET NAMES latin1; CREATE TABLE t1 ( word VARCHAR(64), @@ -270,3 +276,51 @@ aardvara aardvark aardvarz DROP TABLE t1; +SELECT HEX(_ucs2 0x0); +HEX(_ucs2 0x0) +0000 +SELECT HEX(_ucs2 0x01); +HEX(_ucs2 0x01) +0001 +SELECT HEX(_ucs2 0x012); +HEX(_ucs2 0x012) +0012 +SELECT HEX(_ucs2 0x0123); +HEX(_ucs2 0x0123) +0123 +SELECT HEX(_ucs2 0x01234); +HEX(_ucs2 0x01234) +00001234 +SELECT HEX(_ucs2 0x012345); +HEX(_ucs2 0x012345) +00012345 +SELECT HEX(_ucs2 0x0123456); +HEX(_ucs2 0x0123456) +00123456 +SELECT HEX(_ucs2 0x01234567); +HEX(_ucs2 0x01234567) +01234567 +SELECT HEX(_ucs2 0x012345678); +HEX(_ucs2 0x012345678) +000012345678 +SELECT HEX(_ucs2 0x0123456789); +HEX(_ucs2 0x0123456789) +000123456789 +SELECT HEX(_ucs2 0x0123456789A); +HEX(_ucs2 0x0123456789A) +00123456789A +SELECT HEX(_ucs2 0x0123456789AB); +HEX(_ucs2 0x0123456789AB) +0123456789AB +SELECT HEX(_ucs2 0x0123456789ABC); +HEX(_ucs2 0x0123456789ABC) +0000123456789ABC +SELECT HEX(_ucs2 0x0123456789ABCD); +HEX(_ucs2 0x0123456789ABCD) +000123456789ABCD +SELECT HEX(_ucs2 0x0123456789ABCDE); +HEX(_ucs2 0x0123456789ABCDE) +00123456789ABCDE +SELECT HEX(_ucs2 0x0123456789ABCDEF); +HEX(_ucs2 0x0123456789ABCDEF) +0123456789ABCDEF diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 1aef43cd570..7c05b1ea446 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -1,3 +1,4 @@ +drop table if exists t1; set names utf8; select left(_utf8 0xD0B0D0B1D0B2,1); left(_utf8 0xD0B0D0B1D0B2,1) @@ -62,3 +63,19 @@ select 'A' like 'a' collate utf8_bin; select _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD0B1,_utf8 '%'); _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD0B1,_utf8 '%') 1 +select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es'); +insert('txs',2,1,'hi') insert('is ',4,0,'a') insert('txxxxt',2,4,'es') +this is a test +select insert("aa",100,1,"b"),insert("aa",1,3,"b"); +insert("aa",100,1,"b") insert("aa",1,3,"b") +aa b +create table t1 select date_format("2004-01-19 10:10:10", "%Y-%m-%d"); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `date_format("2004-01-19 10:10:10", "%Y-%m-%d")` char(4) character set utf8 default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from t1; +date_format("2004-01-19 10:10:10", "%Y-%m-%d") +2004-01-19 +drop table t1; diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index f311b56f519..d38601c331f 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -85,6 +85,10 @@ a b 2 b 3 c 3 c +select * from (select * from t1 union all select * from t1 limit 2) a; +a b +1 a +2 b explain select * from (select * from t1 union select * from t1) a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 @@ -270,3 +274,27 @@ N M 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 HY000: The target table P2 of the DELETE is not updatable. drop table t1; +CREATE TABLE t1 ( +OBJECTID int(11) NOT NULL default '0', +SORTORDER int(11) NOT NULL auto_increment, +KEY t1_SortIndex (SORTORDER), +KEY t1_IdIndex (OBJECTID) +) TYPE=MyISAM DEFAULT CHARSET=latin1; +Warnings: +Warning 1286 'TYPE=storage_engine' is deprecated. Use 'ENGINE=storage_engine' instead. +CREATE TABLE t2 ( +ID int(11) default NULL, +PARID int(11) default NULL, +UNIQUE KEY t2_ID_IDX (ID), +KEY t2_PARID_IDX (PARID) +) engine=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2); +CREATE TABLE t3 ( +ID int(11) default NULL, +DATA decimal(10,2) default NULL, +UNIQUE KEY t3_ID_IDX (ID) +) engine=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75); +select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP; +497 ID NULL +drop table t1, t2, t3; diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index d51bea020ed..ec5f76409e7 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -6,10 +6,19 @@ inet_ntoa(inet_aton("255.255.255.255.255.255.255.255")) NULL select inet_aton("255.255.255.255.255"),inet_aton("255.255.1.255"),inet_aton("0.1.255"); inet_aton("255.255.255.255.255") inet_aton("255.255.1.255") inet_aton("0.1.255") -1099511627775 4294902271 511 +1099511627775 4294902271 65791 select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); inet_ntoa(1099511627775) inet_ntoa(4294902271) inet_ntoa(511) NULL 255.255.1.255 0.0.1.255 +select hex(inet_aton('127')); +hex(inet_aton('127')) +7F +select hex(inet_aton('127.1')); +hex(inet_aton('127.1')) +7F000001 +select hex(inet_aton('127.1.1')); +hex(inet_aton('127.1.1')) +7F010001 select length(format('nan', 2)) > 0; length(format('nan', 2)) > 0 1 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index c74feccfb7f..f08ae1b1efd 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -605,3 +605,6 @@ 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 Warnings: Note 1003 select high_priority md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,(_latin1'HE' collate _latin1'BINARY') AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate _latin1'latin1_bin'),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substr_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000))) AS `decode(encode(repeat("a",100000),"monty"),"monty")` +SELECT lpad(12345, 5, "#"); +lpad(12345, 5, "#") +12345 diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 04effdfef7c..f51caee39d6 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -21,17 +21,17 @@ DROP TABLE t1; CREATE TABLE t1 (a decimal(240, 20)); INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"), ("0987654321098765432109876543210987654321"); -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( - a decimal(240,20) default NULL +DROP TABLE IF EXISTS `t1`; +CREATE TABLE `t1` ( + `a` decimal(240,20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -/*!40000 ALTER TABLE t1 DISABLE KEYS */; -LOCK TABLES t1 WRITE; -INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890.00000000000000000000"),("0987654321098765432109876543210987654321.00000000000000000000"); +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; +LOCK TABLES `t1` WRITE; +INSERT INTO `t1` VALUES ("1234567890123456789012345678901234567890.00000000000000000000"),("0987654321098765432109876543210987654321.00000000000000000000"); UNLOCK TABLES; -/*!40000 ALTER TABLE t1 ENABLE KEYS */; +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; @@ -41,17 +41,17 @@ UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1 (a double); INSERT INTO t1 VALUES (-9e999999); -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( - a double default NULL +DROP TABLE IF EXISTS `t1`; +CREATE TABLE `t1` ( + `a` double default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -/*!40000 ALTER TABLE t1 DISABLE KEYS */; -LOCK TABLES t1 WRITE; -INSERT INTO t1 VALUES (RES); +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; +LOCK TABLES `t1` WRITE; +INSERT INTO `t1` VALUES (RES); UNLOCK TABLES; -/*!40000 ALTER TABLE t1 ENABLE KEYS */; +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; @@ -105,17 +105,17 @@ INSERT INTO t1 VALUES ("1\""), ("\"2"); DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(255)) DEFAULT CHARSET koi8r; INSERT INTO t1 VALUES (_koi8r x'C1C2C3C4C5'); -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 ( - a varchar(255) default NULL +DROP TABLE IF EXISTS `t1`; +CREATE TABLE `t1` ( + `a` varchar(255) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=koi8r; -/*!40000 ALTER TABLE t1 DISABLE KEYS */; -LOCK TABLES t1 WRITE; -INSERT INTO t1 VALUES ('абцде'); +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; +LOCK TABLES `t1` WRITE; +INSERT INTO `t1` VALUES ('абцде'); UNLOCK TABLES; -/*!40000 ALTER TABLE t1 ENABLE KEYS */; +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index c4af221e117..aa56bce6453 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -153,3 +153,6 @@ explain select * from t1 where a between 2 and 3 or b is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx idx 4 NULL 2 Using where drop table t1; +select cast(NULL as signed); +cast(NULL as signed) +NULL diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index fe2ded691d4..49ae082dead 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -816,4 +816,12 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 4 DROP TABLE t1; +CREATE TABLE t1 (a int(1)); +CREATE DATABASE mysqltest; +USE mysqltest; +DROP DATABASE mysqltest; +SELECT * FROM test.t1; +a +USE test; +DROP TABLE t1; SET GLOBAL query_cache_size=0; diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index f39fa3e7576..290f916ae72 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -88,20 +88,37 @@ drop table t2; create table t1 ( test_set set( 'val1', 'val2', 'val3' ) not null default '', name char(20) default 'O''Brien' comment 'O''Brien as default', -c int not null comment 'int column' - ) comment = 'it\'s a table' ; -show create table t1 ; +c int not null comment 'int column', +`c-b` int comment 'name with a space', +`space ` int comment 'name with a space', +) comment = 'it\'s a table' ; +show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `test_set` set('val1','val2','val3') NOT NULL default '', `name` char(20) default 'O''Brien' COMMENT 'O''Brien as default', - `c` int(11) NOT NULL default '0' COMMENT 'int column' + `c` int(11) NOT NULL default '0' COMMENT 'int column', + `c-b` int(11) default NULL COMMENT 'name with a space', + `space ` int(11) default NULL COMMENT 'name with a space' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='it''s a table' +set sql_quote_show_create=0; +show create table t1; +Table Create Table +t1 CREATE TABLE t1 ( + test_set set('val1','val2','val3') NOT NULL default '', + name char(20) default 'O''Brien' COMMENT 'O''Brien as default', + c int(11) NOT NULL default '0' COMMENT 'int column', + `c-b` int(11) default NULL COMMENT 'name with a space', + `space ` int(11) default NULL COMMENT 'name with a space' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='it''s a table' +set sql_quote_show_create=1; show full columns from t1; Field Type Collation Null Key Default Extra Privileges Comment test_set set('val1','val2','val3') latin1_swedish_ci select,insert,update,references name char(20) latin1_swedish_ci YES O'Brien select,insert,update,references O'Brien as default c int(11) NULL 0 select,insert,update,references int column +c-b int(11) NULL YES NULL select,insert,update,references name with a space +space int(11) NULL YES NULL select,insert,update,references name with a space drop table t1; create table t1 (a int not null, unique aa (a)); show create table t1; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 672a39299dd..ded98265a1c 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1569,3 +1569,12 @@ INSERT INTO t2 VALUES (100, 200, 'C'); SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1); COLC DROP TABLE t1, t2; +create table t1 (a int, b decimal(13, 3)); +insert into t1 values (1, 0.123); +select a, (select max(b) from t1) into outfile "subselect.out.file.1" from t1; +delete from t1; +load data infile "subselect.out.file.1" into table t1; +select * from t1; +a b +1 0.123 +drop table t1; diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index 6729916f1c8..b2a055fa72c 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -60,6 +60,39 @@ 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; 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) 1 1 -2 1 -3 1 +2 2 +3 3 drop table t1,t2,t3; +CREATE TABLE t1 ( +id int(11) NOT NULL default '0', +b int(11) default NULL, +c char(3) default NULL, +PRIMARY KEY (id), +KEY t2i1 (b) +) ENGINE=innodb DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (0,0,'GPL'),(1,0,'GPL'),(2,1,'GPL'),(3,2,'GPL'); +CREATE TABLE t2 ( +id int(11) NOT NULL default '0', +b int(11) default NULL, +c char(3) default NULL, +PRIMARY KEY (id), +KEY t2i (b) +) ENGINE=innodb DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (0,0,'GPL'),(1,0,'GPL'),(2,1,'GPL'),(3,2,'GPL'); +select (select max(id) from t2 where b=1 group by b) as x,b from t1 where b=1; +x b +2 1 +drop table t1,t2; +create table t1 (id int not null, value char(255), primary key(id)) engine=innodb; +create table t2 (id int not null, value char(255)) engine=innodb; +insert into t1 values (1,'a'),(2,'b'); +insert into t2 values (1,'z'),(2,'x'); +select t2.id,t2.value,(select t1.value from t1 where t1.id=t2.id) from t2; +id value (select t1.value from t1 where t1.id=t2.id) +1 z a +2 x b +select t2.id,t2.value,(select t1.value from t1 where t1.id=t2.id) from t2; +id value (select t1.value from t1 where t1.id=t2.id) +1 z a +2 x b +drop table t1,t2; diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 4ba0ca0eac4..6dc48a0a77e 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -66,7 +66,7 @@ t9 CREATE TABLE `t9` ( drop database mysqltest; create table t1 (a int not null) type=myisam; Warnings: -Warning 1286 'TYPE=database_engine' is deprecated. Use 'ENGINE=database_engine' instead. +Warning 1286 'TYPE=storage_engine' is deprecated. Use 'ENGINE=storage_engine' instead. show create table t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index 5c7c75bac00..c3d9f165fed 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -128,10 +128,10 @@ Warning 1265 Using storage engine MyISAM for table 't1' drop table t1; create table t1 (id int) type=heap; Warnings: -Warning 1286 'TYPE=database_engine' is deprecated. Use 'ENGINE=database_engine' instead. +Warning 1286 'TYPE=storage_engine' is deprecated. Use 'ENGINE=storage_engine' instead. alter table t1 type=myisam; Warnings: -Warning 1286 'TYPE=database_engine' is deprecated. Use 'ENGINE=database_engine' instead. +Warning 1286 'TYPE=storage_engine' is deprecated. Use 'ENGINE=storage_engine' instead. drop table t1; set table_type=MYISAM; Warnings: diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index eab4fd7f5f0..71991973105 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -244,3 +244,12 @@ LOCK TABLES t1 WRITE; ALTER TABLE t1 DISABLE KEYS; SHOW INDEX FROM t1; DROP TABLE t1; + +# +# Bug 2361 +# + +CREATE TABLE t1 (a int UNIQUE); +ALTER TABLE t1 DROP PRIMARY KEY; +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/t/ctype_recoding.test b/mysql-test/t/ctype_recoding.test index 0b901009041..40349da8aa9 100644 --- a/mysql-test/t/ctype_recoding.test +++ b/mysql-test/t/ctype_recoding.test @@ -14,6 +14,15 @@ INSERT t2 SELECT * FROM t1; SELECT HEX(a) FROM t2; DROP TABLE t1, t2; + +# +# Check that long strings conversion does not fail (bug#2218) +# +CREATE TABLE t1 (description text character set cp1250 NOT NULL); +INSERT INTO t1 (description) VALUES (_latin2'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaasssssssssssaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddde'); +SELECT description FROM t1; +DROP TABLE t1; + # same with TEXT CREATE TABLE t1 (a TEXT CHARACTER SET cp1251) SELECT _koi8r'ÐÒÏÂÁ' AS a; CREATE TABLE t2 (a TEXT CHARACTER SET utf8); @@ -62,3 +71,4 @@ SET NAMES koi8r; SELECT hex('ÔÅÓÔ'); SET character_set_connection=cp1251; SELECT hex('ÔÅÓÔ'); + diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index 90b423cd1e0..fd2a1b1cd7d 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -93,6 +93,12 @@ SELECT * FROM t1 WHERE word LIKE _ucs2 x'00630025'; SELECT * FROM t1 WHERE word LIKE _ucs2 x'00630061005F'; DROP TABLE t1; +# +# Check that INSERT works fine. +# This invokes charpos() function. +select insert(_ucs2 0x006100620063,10,2,_ucs2 0x006400650066); +select insert(_ucs2 0x006100620063,1,2,_ucs2 0x006400650066); + ###################################################### # @@ -192,3 +198,23 @@ DROP TABLE t1; # ######################################################## + +# Bug #2390 +# Check alignment +# +SELECT HEX(_ucs2 0x0); +SELECT HEX(_ucs2 0x01); +SELECT HEX(_ucs2 0x012); +SELECT HEX(_ucs2 0x0123); +SELECT HEX(_ucs2 0x01234); +SELECT HEX(_ucs2 0x012345); +SELECT HEX(_ucs2 0x0123456); +SELECT HEX(_ucs2 0x01234567); +SELECT HEX(_ucs2 0x012345678); +SELECT HEX(_ucs2 0x0123456789); +SELECT HEX(_ucs2 0x0123456789A); +SELECT HEX(_ucs2 0x0123456789AB); +SELECT HEX(_ucs2 0x0123456789ABC); +SELECT HEX(_ucs2 0x0123456789ABCD); +SELECT HEX(_ucs2 0x0123456789ABCDE); +SELECT HEX(_ucs2 0x0123456789ABCDEF); diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 54d934b66db..5e9324dd68f 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -2,6 +2,9 @@ # Tests with the utf8 character set # +--disable_warnings +drop table if exists t1; +--enable_warnings set names utf8; select left(_utf8 0xD0B0D0B1D0B2,1); @@ -35,3 +38,18 @@ select _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD0B1,_utf8 '%'); # #select _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD091,_utf8 '%'); # + +# +# Bug 2367: INSERT() behaviour is different for different charsets. +# +select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es'); +select insert("aa",100,1,"b"),insert("aa",1,3,"b"); + +# +# CREATE ... SELECT +# +create table t1 select date_format("2004-01-19 10:10:10", "%Y-%m-%d"); +show create table t1; +select * from t1; +drop table t1; + diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 77e76d16772..154fc4b3834 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -35,6 +35,7 @@ select a from (select 1 as a) as b; select 1 from (select 1) as a; select * from (select * from t1 union select * from t1) a; select * from (select * from t1 union all select * from t1) a; +select * from (select * from t1 union all select * from t1 limit 2) a; explain select * from (select * from t1 union select * from t1) a; explain select * from (select * from t1 union all select * from t1) a; CREATE TABLE t2 (a int not null); @@ -157,3 +158,28 @@ select * from t1; -- error 1287 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; drop table t1; + +# +# correct lex->current_select +# +CREATE TABLE t1 ( + OBJECTID int(11) NOT NULL default '0', + SORTORDER int(11) NOT NULL auto_increment, + KEY t1_SortIndex (SORTORDER), + KEY t1_IdIndex (OBJECTID) +) TYPE=MyISAM DEFAULT CHARSET=latin1; +CREATE TABLE t2 ( + ID int(11) default NULL, + PARID int(11) default NULL, + UNIQUE KEY t2_ID_IDX (ID), + KEY t2_PARID_IDX (PARID) +) engine=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2); +CREATE TABLE t3 ( + ID int(11) default NULL, + DATA decimal(10,2) default NULL, + UNIQUE KEY t3_ID_IDX (ID) +) engine=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75); +select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP; +drop table t1, t2, t3; diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index d15c26279ec..9759127b222 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -8,6 +8,10 @@ select inet_ntoa(inet_aton("255.255.255.255.255.255.255.255")); select inet_aton("255.255.255.255.255"),inet_aton("255.255.1.255"),inet_aton("0.1.255"); select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); +select hex(inet_aton('127')); +select hex(inet_aton('127.1')); +select hex(inet_aton('127.1.1')); + # # Test for core dump with nan # diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index ad7b9b21b51..155ed459d1f 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -346,3 +346,9 @@ 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"); + +# +# Bug #2182 +# + +SELECT lpad(12345, 5, "#"); diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index c11ed78253b..9f3b6646e7f 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -97,3 +97,4 @@ insert into t1 values explain select * from t1 where a between 2 and 3; explain select * from t1 where a between 2 and 3 or b is null; drop table t1; +select cast(NULL as signed); diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index d7681e9c2ec..dba5619b777 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -589,4 +589,17 @@ show status like "Qcache_queries_in_cache"; # Keep things tidy # DROP TABLE t1; + +# +# DROP current database test +# +CREATE TABLE t1 (a int(1)); +CREATE DATABASE mysqltest; +USE mysqltest; +DROP DATABASE mysqltest; +SELECT * FROM test.t1; +USE test; +DROP TABLE t1; + + SET GLOBAL query_cache_size=0; diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 4ab39e3ccbc..d262f02c978 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -53,9 +53,14 @@ drop table t2; create table t1 ( test_set set( 'val1', 'val2', 'val3' ) not null default '', name char(20) default 'O''Brien' comment 'O''Brien as default', - c int not null comment 'int column' + c int not null comment 'int column', + `c-b` int comment 'name with a space', + `space ` int comment 'name with a space', ) comment = 'it\'s a table' ; -show create table t1 ; +show create table t1; +set sql_quote_show_create=0; +show create table t1; +set sql_quote_show_create=1; show full columns from t1; drop table t1; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 95e4f022f2d..37dbc8f24d9 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1009,3 +1009,15 @@ INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365'); INSERT INTO t2 VALUES (100, 200, 'C'); SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1); DROP TABLE t1, t2; + +# +# Bug 2198 +# + +create table t1 (a int, b decimal(13, 3)); +insert into t1 values (1, 0.123); +select a, (select max(b) from t1) into outfile "subselect.out.file.1" from t1; +delete from t1; +load data infile "subselect.out.file.1" into table t1; +select * from t1; +drop table t1; diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index 9eb35ffc0fd..8e8d41f7653 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -67,4 +67,37 @@ 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;
\ No newline at end of file +drop table t1,t2,t3; + +# +# innodb locking +# +CREATE TABLE t1 ( + id int(11) NOT NULL default '0', + b int(11) default NULL, + c char(3) default NULL, + PRIMARY KEY (id), + KEY t2i1 (b) +) ENGINE=innodb DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (0,0,'GPL'),(1,0,'GPL'),(2,1,'GPL'),(3,2,'GPL'); +CREATE TABLE t2 ( + id int(11) NOT NULL default '0', + b int(11) default NULL, + c char(3) default NULL, + PRIMARY KEY (id), + KEY t2i (b) +) ENGINE=innodb DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (0,0,'GPL'),(1,0,'GPL'),(2,1,'GPL'),(3,2,'GPL'); +select (select max(id) from t2 where b=1 group by b) as x,b from t1 where b=1; +drop table t1,t2; + +# +# reiniting innodb tables +# +create table t1 (id int not null, value char(255), primary key(id)) engine=innodb; +create table t2 (id int not null, value char(255)) engine=innodb; +insert into t1 values (1,'a'),(2,'b'); +insert into t2 values (1,'z'),(2,'x'); +select t2.id,t2.value,(select t1.value from t1 where t1.id=t2.id) from t2; +select t2.id,t2.value,(select t1.value from t1 where t1.id=t2.id) from t2; +drop table t1,t2; |