diff options
Diffstat (limited to 'mysql-test/t')
30 files changed, 607 insertions, 27 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index eab4fd7f5f0..c013b2251a9 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -244,3 +244,14 @@ LOCK TABLES t1 WRITE; ALTER TABLE t1 DISABLE KEYS; SHOW INDEX FROM t1; DROP TABLE t1; + +# +# Bug 2361 +# + +CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE); +ALTER TABLE t1 DROP PRIMARY KEY; +SHOW CREATE TABLE t1; +--error 1091 +ALTER TABLE t1 DROP PRIMARY KEY; +DROP TABLE t1; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index dd17904effb..ab0242990fc 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -10,9 +10,17 @@ select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1; select ~5, cast(~5 as signed); explain extended select ~5, cast(~5 as signed); select cast(5 as unsigned) -6.0; +select cast(NULL as signed), cast(1/0 as signed); +select cast(NULL as unsigned), cast(1/0 as unsigned); select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A"; select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME); select cast("1:2:3" as TIME); +select CONVERT("2004-01-22 21:45:33",DATE); +select CONVERT(DATE "2004-01-22 21:45:33" USING latin1); +select CONVERT(DATE "2004-01-22 21:45:33",CHAR); +select CONVERT(DATE "2004-01-22 21:45:33",CHAR(4)); +select CONVERT(DATE "2004-01-22 21:45:33",CHAR(4) BINARY); +select CAST(DATE "2004-01-22 21:45:33" AS CHAR(4) BINARY); # # Character set convertion @@ -20,6 +28,8 @@ select cast("1:2:3" as TIME); set names binary; select cast(_latin1'test' as char character set latin2); select cast(_koi8r'ÔÅÓÔ' as char character set cp1251); +select convert(_latin1'test', "latin1_german1_ci", "latin1_swedish_ci"); +select convert(_koi8r'ÔÅÓÔ', "koi8r_general_ci", "cp1251_general_ci"); create table t1 select cast(_koi8r'ÔÅÓÔ' as char character set cp1251) as t; show create table t1; drop table t1; @@ -64,6 +74,19 @@ select * from t1; show create table t1; drop table t1; +# +# Bug 2202 +# CAST from BINARY to non-BINARY and from non-BINARY to BINARY +# +create table t1 (a binary(10), b char(10) character set koi8r); +insert into t1 values (_binary'ÔÅÓÔ',_binary'ÔÅÓÔ'); +select a,b,cast(a as char character set cp1251),cast(b as binary) from t1; +set names koi8r; +select a,b,cast(a as char character set cp1251),cast(b as binary) from t1; +set names cp1251; +select a,b,cast(a as char character set cp1251),cast(b as binary) from t1; +drop table t1; +set names binary; # # The following should be fixed in 4.1 diff --git a/mysql-test/t/ctype_big5.test b/mysql-test/t/ctype_big5.test new file mode 100644 index 00000000000..9bf1808636e --- /dev/null +++ b/mysql-test/t/ctype_big5.test @@ -0,0 +1,18 @@ +-- source include/have_big5.inc + +# +# Tests with the big5 character set +# +--disable_warnings +drop table if exists t1; +--enable_warnings + +SET NAMES big5; + +# +# Bug 1883: LIKE did not work in some cases with a key. +# +CREATE TABLE t1 (c CHAR(10) CHARACTER SET big5, KEY(c)); +INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa'); +SELECT * FROM t1 WHERE c LIKE 'aaa%'; +DROP TABLE t1; diff --git a/mysql-test/t/ctype_collate.test b/mysql-test/t/ctype_collate.test index 2d9a4be5b36..5916e3da241 100644 --- a/mysql-test/t/ctype_collate.test +++ b/mysql-test/t/ctype_collate.test @@ -156,3 +156,41 @@ CREATE TABLE t1 --error 1266 SELECT * FROM t1 WHERE s1 = s2; DROP TABLE t1; + + +# +# Test that optimizer doesn't use indexes with wrong collation +# +SET NAMES latin1; +CREATE TABLE t1 +(s1 char(10) COLLATE latin1_german1_ci, + s2 char(10) COLLATE latin1_swedish_ci, + KEY(s1), + KEY(s2)); + +INSERT INTO t1 VALUES ('a','a'); +INSERT INTO t1 VALUES ('b','b'); +INSERT INTO t1 VALUES ('c','c'); +INSERT INTO t1 VALUES ('d','d'); +INSERT INTO t1 VALUES ('e','e'); +INSERT INTO t1 VALUES ('f','f'); +INSERT INTO t1 VALUES ('g','g'); +INSERT INTO t1 VALUES ('h','h'); +INSERT INTO t1 VALUES ('i','i'); +INSERT INTO t1 VALUES ('j','j'); + +EXPLAIN SELECT * FROM t1 WHERE s1='a'; +EXPLAIN SELECT * FROM t1 WHERE s2='a'; +EXPLAIN SELECT * FROM t1 WHERE s1='a' COLLATE latin1_german1_ci; +EXPLAIN SELECT * FROM t1 WHERE s2='a' COLLATE latin1_german1_ci; + +EXPLAIN SELECT * FROM t1 WHERE s1 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci; +EXPLAIN SELECT * FROM t1 WHERE s2 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci; + +EXPLAIN SELECT * FROM t1 WHERE s1 IN ('a','b' COLLATE latin1_german1_ci); +EXPLAIN SELECT * FROM t1 WHERE s2 IN ('a','b' COLLATE latin1_german1_ci); + +EXPLAIN SELECT * FROM t1 WHERE s1 LIKE 'a' COLLATE latin1_german1_ci; +EXPLAIN SELECT * FROM t1 WHERE s2 LIKE 'a' COLLATE latin1_german1_ci; + +DROP TABLE t1; diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test index a5d0c29baf6..e29e43496af 100644 --- a/mysql-test/t/ctype_latin1_de.test +++ b/mysql-test/t/ctype_latin1_de.test @@ -2,6 +2,9 @@ # Test latin_de character set # +set names latin1; +set @@collation_connection=latin1_german2_ci; + select @@collation_connection; --disable_warnings 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_tis620.test b/mysql-test/t/ctype_tis620.test new file mode 100644 index 00000000000..82c660dfea0 --- /dev/null +++ b/mysql-test/t/ctype_tis620.test @@ -0,0 +1,64 @@ +-- source include/have_tis620.inc + +# +# Tests with the big5 character set +# +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# Bug 1552: tis620 <-> unicode conversion crashed +# Check tis620 -> utf8 -> tis620 round trip conversion +# + +SET @pl0= _tis620 0x000102030405060708090A0B0C0D0E0F; +SET @pl1= _tis620 0x101112131415161718191A1B1C1D1E1F; +SET @pl2= _tis620 0x202122232425262728292A2B2C2D2E2F; +SET @pl3= _tis620 0x303132333435363738393A3B3C3D3E3F; +SET @pl4= _tis620 0x404142434445464748494A4B4C4D4E4F; +SET @pl5= _tis620 0x505152535455565758595A5B5C5D5E5F; +SET @pl6= _tis620 0x606162636465666768696A6B6C6D6E6F; +SET @pl7= _tis620 0x707172737475767778797A7B7C7D7E7F; +SET @pl8= _tis620 0x808182838485868788898A8B8C8D8E8F; +SET @pl9= _tis620 0x909192939495969798999A9B9C9D9E9F; +SET @plA= _tis620 0xA0A1A2A3A4A5A6A7A8A9AAABACADAEAF; +SET @plB= _tis620 0xB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF; +SET @plC= _tis620 0xC0C1C2C3C4C5C6C7C8C9CACBCCCDCECF; +SET @plD= _tis620 0xD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF; +SET @plE= _tis620 0xE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF; +SET @plF= _tis620 0xF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF; + +SELECT hex(@u0:=convert(@pl0 using utf8)); +SELECT hex(@u1:=convert(@pl1 using utf8)); +SELECT hex(@u2:=convert(@pl2 using utf8)); +SELECT hex(@u3:=convert(@pl3 using utf8)); +SELECT hex(@u4:=convert(@pl4 using utf8)); +SELECT hex(@u5:=convert(@pl5 using utf8)); +SELECT hex(@u6:=convert(@pl6 using utf8)); +SELECT hex(@u7:=convert(@pl7 using utf8)); +SELECT hex(@u8:=convert(@pl8 using utf8)); +SELECT hex(@u9:=convert(@pl9 using utf8)); +SELECT hex(@uA:=convert(@plA using utf8)); +SELECT hex(@uB:=convert(@plB using utf8)); +SELECT hex(@uC:=convert(@plC using utf8)); +SELECT hex(@uD:=convert(@plD using utf8)); +SELECT hex(@uE:=convert(@plE using utf8)); +SELECT hex(@uF:=convert(@plF using utf8)); + +SELECT hex(convert(@u0 USING tis620)); +SELECT hex(convert(@u1 USING tis620)); +SELECT hex(convert(@u2 USING tis620)); +SELECT hex(convert(@u3 USING tis620)); +SELECT hex(convert(@u4 USING tis620)); +SELECT hex(convert(@u5 USING tis620)); +SELECT hex(convert(@u6 USING tis620)); +SELECT hex(convert(@u7 USING tis620)); +SELECT hex(convert(@u8 USING tis620)); +SELECT hex(convert(@u9 USING tis620)); +SELECT hex(convert(@uA USING tis620)); +SELECT hex(convert(@uB USING tis620)); +SELECT hex(convert(@uC USING tis620)); +SELECT hex(convert(@uD USING tis620)); +SELECT hex(convert(@uE USING tis620)); +SELECT hex(convert(@uF USING tis620)); diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index 90b423cd1e0..5b1a5923ad4 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -69,6 +69,35 @@ select * from t1 where a like "%abcd%"; select * from t1 where a like "%abc\d%"; drop table t1; +# +# More LIKE test: bug#2619 +# +select 'AA' like 'AA'; +select 'AA' like 'A%A'; +select 'AA' like 'A%%A'; +select 'AA' like 'AA%'; +select 'AA' like '%AA%'; +select 'AA' like '%A'; +select 'AA' like '%AA'; +select 'AA' like 'A%A%'; +select 'AA' like '_%_%'; +select 'AA' like '%A%A'; +select 'AAA'like 'A%A%A'; + +select 'AZ' like 'AZ'; +select 'AZ' like 'A%Z'; +select 'AZ' like 'A%%Z'; +select 'AZ' like 'AZ%'; +select 'AZ' like '%AZ%'; +select 'AZ' like '%Z'; +select 'AZ' like '%AZ'; +select 'AZ' like 'A%Z%'; +select 'AZ' like '_%_%'; +select 'AZ' like '%A%Z'; +select 'AZ' like 'A_'; +select 'AZ' like '_Z'; +select 'AMZ'like 'A%M%Z'; + CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ucs2); INSERT INTO t1 VALUES ('ÆÙ×Á'),('æÙ×Á'),('Æù×Á'),('ÆÙ÷Á'),('ÆÙ×á'),('æù÷á'); INSERT INTO t1 VALUES ('ÆÙ×ÁÐÒÏÌÄÖ'),('æÙ×ÁÐÒÏÌÄÖ'),('Æù×ÁÐÒÏÌÄÖ'),('ÆÙ÷ÁÐÒÏÌÄÖ'); @@ -93,6 +122,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 +227,59 @@ DROP TABLE t1; # ######################################################## + +# Bug #2390 +# Check alignment for constants +# +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); + +# +# Check alignment for from-binary-conversion with CAST and CONVERT +# +SELECT hex(cast(0xAA as char character set ucs2)); +SELECT hex(convert(0xAA using ucs2)); + +# +# Check alignment for string types +# +CREATE TABLE t1 (a char(10) character set ucs2); +INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a varchar(10) character set ucs2); +INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a text character set ucs2); +INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a mediumtext character set ucs2); +INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a longtext character set ucs2); +INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +-- the same should be also done with enum and set diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 54d934b66db..0615de99b7a 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,63 @@ 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; + +# +# Bug #2366 Wrong utf8 behaviour when data is trancated +# +set names koi8r; +create table t1 (s1 char(1) character set utf8); +insert into t1 values (_koi8r'ÁÂ'); +select s1,hex(s1),char_length(s1),octet_length(s1) from t1; +drop table t1; + +create table t1 (s1 tinytext character set utf8); +insert into t1 select repeat('a',300); +insert into t1 select repeat('Ñ',300); +insert into t1 select repeat('aÑ',300); +insert into t1 select repeat('Ña',300); +insert into t1 select repeat('ÑÑ',300); +select hex(s1) from t1; +select length(s1),char_length(s1) from t1; +drop table t1; + +create table t1 (s1 text character set utf8); +insert into t1 select repeat('a',66000); +insert into t1 select repeat('Ñ',66000); +insert into t1 select repeat('aÑ',66000); +insert into t1 select repeat('Ña',66000); +insert into t1 select repeat('ÑÑ',66000); +select length(s1),char_length(s1) from t1; +drop table t1; + +# +# Bug #2368 Multibyte charsets do not check that incoming data is well-formed +# +create table t1 (s1 char(10) character set utf8); +insert into t1 values (0x41FF); +select hex(s1) from t1; +drop table t1; + +create table t1 (s1 varchar(10) character set utf8); +insert into t1 values (0x41FF); +select hex(s1) from t1; +drop table t1; + +create table t1 (s1 text character set utf8); +insert into t1 values (0x41FF); +select hex(s1) from t1; +drop table t1; diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test index 7b88c0ecf72..18af3dfb3db 100644 --- a/mysql-test/t/date_formats.test +++ b/mysql-test/t/date_formats.test @@ -2,9 +2,9 @@ # Test of date format functions # ---disable-warnings +--disable_warnings drop table if exists t1; ---enable-warnings +--enable_warnings SHOW GLOBAL VARIABLES LIKE "%_format%"; SHOW SESSION VARIABLES LIKE "%_format%"; diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index caf673d95c1..a9341ada416 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); @@ -115,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 1149 +-- error 1287 update (select * from t1) as t1 set a = 5; -- error 1064 delete from (select * from t1); @@ -138,3 +139,70 @@ insert into t1 values (1),(2); select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; drop table t1; + + +# +# multi-update & multi-delete with derived tables +# +CREATE TABLE `t1` ( + `N` int(11) unsigned NOT NULL default '0', + `M` tinyint(1) default '0', +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +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 +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 +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; +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) +) ENGINE=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; + + +# +# explain derived +# +CREATE TABLE t1 (name char(1) default NULL, val int(5) default NULL); +INSERT INTO t1 VALUES ('a',1), ('a',2), ('a',2), ('a',2), ('a',3), ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5); +SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name; +explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name; +drop table t1; + +# +# "Using index" in explain +# +create table t2 (a int, b int, primary key (a)); +insert into t2 values (1,7),(2,7); +explain select a from t2 where a>1; +explain select a from (select a from t2 where a>1) tt; +drop table t2; diff --git a/mysql-test/t/func_like.test b/mysql-test/t/func_like.test index 0cd85385df4..a05a2a3bdaa 100644 --- a/mysql-test/t/func_like.test +++ b/mysql-test/t/func_like.test @@ -44,3 +44,22 @@ SELECT * FROM t1 WHERE a LIKE '%Æù×%'; SELECT * FROM t1 WHERE a LIKE 'Æù×Á%'; DROP TABLE t1; + +# Bug #2547 Strange "like" behaviour in tables with default charset=cp1250 +# Test like with non-default character set using TurboBM +# +SET NAMES cp1250; +CREATE TABLE t1 (a varchar(250) NOT NULL) DEFAULT CHARACTER SET=cp1250; +INSERT INTO t1 VALUES +('Techni Tapes Sp. z o.o.'), +('Pojazdy Szynowe PESA Bydgoszcz SA Holding'), +('AKAPESTER 1 P.P.H.U.'), +('Pojazdy Szynowe PESA Bydgoszcz S A Holding'), +('PPUH PESKA-I Maria Struniarska'); + +select * from t1 where a like '%PESA%'; +select * from t1 where a like '%PESA %'; +select * from t1 where a like '%PES%'; +select * from t1 where a like '%PESKA%'; +select * from t1 where a like '%ESKA%'; +DROP TABLE t1; 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/func_test.test b/mysql-test/t/func_test.test index 1559fef7e6e..0d055549866 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -85,3 +85,11 @@ CREATE TABLE t2 ( access_id smallint(6) NOT NULL default '0', name varchar(20 INSERT INTO t2 VALUES (1,'Everyone',2),(2,'Help',3),(3,'Customer Support',1); SELECT f_acc.rank, a1.rank, a2.rank FROM t1 LEFT JOIN t1 f1 ON (f1.access_id=1 AND f1.faq_group_id = t1.faq_group_id) LEFT JOIN t2 a1 ON (a1.access_id = f1.access_id) LEFT JOIN t1 f2 ON (f2.access_id=3 AND f2.faq_group_id = t1.faq_group_id) LEFT JOIN t2 a2 ON (a2.access_id = f2.access_id), t2 f_acc WHERE LEAST(a1.rank,a2.rank) = f_acc.rank; DROP TABLE t1,t2; + +# +# Test for GREATEST() and LEAST() bug, which segfaulted 4.1.1 server +# +CREATE TABLE t1 (d varchar(6), k int); +INSERT INTO t1 VALUES (NULL, 2); +SELECT GREATEST(d,d) FROM t1 WHERE k=2; +DROP TABLE t1; diff --git a/mysql-test/t/key_cache.test b/mysql-test/t/key_cache.test index b2bc57f3804..d9a2200a636 100644 --- a/mysql-test/t/key_cache.test +++ b/mysql-test/t/key_cache.test @@ -75,7 +75,7 @@ select * from t2; update t1 set p=2 where p=1; update t2 set i=2 where i=1; -cache index t1 keys (`primary`) in keycache1; +cache index t1 key (`primary`) in keycache1; explain select p from t1; select p from t1; @@ -101,7 +101,7 @@ select a from t2; # Test some error conditions --error 1283 cache index t1 in unknown_key_cache; -cache index t1 keys (unknown_key) in keycache1; +cache index t1 key (unknown_key) in keycache1; select @@keycache2.key_buffer_size; select @@keycache2.key_cache_block_size; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 50bd2d114ed..84b9c816ee5 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -218,7 +218,7 @@ select * from t2; UPDATE t1 a ,t2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n; select * from t1; select * from t2; -DELETE t1, t2 FROM t1 a,t2 b where a.n=b.n; +DELETE a, b FROM t1 a,t2 b where a.n=b.n; select * from t1; select * from t2; drop table t1,t2; diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 1299d73eb99..dc0d45187b4 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -28,12 +28,12 @@ INSERT INTO t1 VALUES (-9e999999); # The following replaces is here because some systems replaces the above # double with '-inf' and others with MAX_DOUBLE --replace_result (-1.79769313486232e+308) (RES) (NULL) (RES) ---exec $MYSQL_DUMP --skip-comments test t1 +--exec $MYSQL_DUMP --skip-comments test t1 DROP TABLE t1; CREATE TABLE t1(a int, b text, c varchar(3)); INSERT INTO t1 VALUES (1, "test", "tes"), (2, "TEST", "TES"); ---exec $MYSQL_DUMP --skip-all --skip-comments -X test t1 +--exec $MYSQL_DUMP --skip-all --skip-comments -X test t1 DROP TABLE t1; # @@ -42,7 +42,7 @@ DROP TABLE t1; CREATE TABLE t1 (`a"b"` char(2)); INSERT INTO t1 VALUES ("1\""), ("\"2"); ---exec $MYSQL_DUMP --skip-all --skip-comments -X test t1 +--exec $MYSQL_DUMP --skip-all --skip-comments -X test t1 DROP TABLE t1; # @@ -51,5 +51,23 @@ DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(255)) DEFAULT CHARSET koi8r; INSERT INTO t1 VALUES (_koi8r x'C1C2C3C4C5'); ---exec $MYSQL_DUMP --skip-comments test t1 +--exec $MYSQL_DUMP --skip-comments test t1 +DROP TABLE t1; + +# +# Bug #2634 +# + +CREATE TABLE t1 (a int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1), (2); +--exec $MYSQL_DUMP --skip-comments --compatible=mysql40 test t1 +--exec $MYSQL_DUMP --skip-comments --compatible=mysql323 test t1 DROP TABLE t1; + +# +# Bug #2592 'mysqldum doesn't quote "tricky" names correctly' +# + +create table ```a` (i int); +--exec $MYSQL_DUMP --skip-comments test +drop table ```a`; 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/order_by.test b/mysql-test/t/order_by.test index 1db783c212b..bbb0046b47f 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -381,3 +381,15 @@ CREATE TABLE t1 ( id smallint(6) unsigned NOT NULL default '0', menu tinyint(4 INSERT INTO t1 VALUES (11384, 2),(11392, 2); SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ; drop table t1; + +# +# REF_OR_NULL optimization + filesort (bug #2419) +# + +create table t1(a int, b int, index(b)); +insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); +explain select * from t1 where b=1 or b is null order by a; +select * from t1 where b=1 or b is null order by a; +explain select * from t1 where b=2 or b is null order by a; +select * from t1 where b=2 or b is null order by a; +drop table t1; diff --git a/mysql-test/t/preload.test b/mysql-test/t/preload.test index 7eff5cee08f..7a049d06a86 100644 --- a/mysql-test/t/preload.test +++ b/mysql-test/t/preload.test @@ -81,7 +81,7 @@ flush tables; flush status; show status like "key_read%"; set session preload_buffer_size=1*1024; select @@preload_buffer_size; -load index into cache t1, t2 keys (primary,b) ignore leaves; +load index into cache t1, t2 key (primary,b) ignore leaves; show status like "key_read%"; select count(*) from t1 where b = 'test1'; select count(*) from t2 where b = 'test1'; @@ -89,12 +89,12 @@ show status like "key_read%"; flush tables; flush status; show status like "key_read%"; -load index into cache t3, t2 keys (primary,b) ; +load index into cache t3, t2 key (primary,b) ; show status like "key_read%"; flush tables; flush status; show status like "key_read%"; -load index into cache t3 keys (b), t2 keys (c) ; +load index into cache t3 key (b), t2 key (c) ; show status like "key_read%"; drop table t1, t2; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 17ba5418c8a..b46685505ef 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -466,8 +466,10 @@ select * from t1 where id=2; create table t1 (word char(20) not null); select * from t1; show status like "Qcache_queries_in_cache"; -load data infile '../../std_data/words.dat' into table t1; +--replace_result $MYSQL_TEST_DIR TEST_DIR +eval load data infile '$MYSQL_TEST_DIR/std_data/words.dat' into table t1; show status like "Qcache_queries_in_cache"; +select count(*) from t1; drop table t1; # @@ -476,7 +478,9 @@ drop table t1; create table t1 (a int); insert into t1 values (1),(2),(3); show status like "Qcache_queries_in_cache"; -select * from t1 into outfile "query_caceh.out.file"; +select * from t1 into outfile "query_cache.out.file"; +--error 1086 +select * from t1 into outfile "query_cache.out.file"; select * from t1 limit 1 into dumpfile "query_cache.dump.file"; show status like "Qcache_queries_in_cache"; drop table t1; @@ -585,9 +589,21 @@ set character_set_results=cp1251; SELECT a,'Â','â'='Â' FROM t1; show status like "Qcache_hits"; show status like "Qcache_queries_in_cache"; -drop table t1; +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; + + # comments before command # create table t1 (a int); diff --git a/mysql-test/t/rpl_until.test b/mysql-test/t/rpl_until.test index 40b810dfd62..545b495938a 100644 --- a/mysql-test/t/rpl_until.test +++ b/mysql-test/t/rpl_until.test @@ -29,7 +29,7 @@ sleep 2; # here table should be still not deleted select * from t1; --replace_result $MASTER_MYPORT MASTER_MYPORT ---replace_column 1 # 33 # +--replace_column 1 # 9 # 23 # 33 # show slave status; # this should fail right after start @@ -59,9 +59,10 @@ stop slave; # this should stop immediately as we are already there start slave until master_log_file='master-bin.000001', master_log_pos=710; -sleep 2; +# 2 is not enough when running with valgrind +real_sleep 4 # here the sql slave thread should be stopped ---replace_result $MASTER_MYPORT MASTER_MYPORT +--replace_result $MASTER_MYPORT MASTER_MYPORT bin.000005 bin.000004 bin.000006 bin.000004 bin.000007 bin.000004 --replace_column 1 # 9 # 23 # 33 # show slave status; 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 828443dfa10..2f05141ee31 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1014,3 +1014,43 @@ 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; + +# +# Bug 2479 +# + +CREATE TABLE `t1` ( + `id` int(11) NOT NULL auto_increment, + `id_cns` tinyint(3) unsigned NOT NULL default '0', + `tipo` enum('','UNO','DUE') NOT NULL default '', + `anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000', + `particolare` mediumint(8) unsigned NOT NULL default '0', + `generale` mediumint(8) unsigned NOT NULL default '0', + `bis` tinyint(3) unsigned NOT NULL default '0', + PRIMARY KEY (`id`), + UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`), + UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`) +); +INSERT INTO `t1` VALUES (1,16,'UNO',1987,2048,9681,0),(2,50,'UNO',1987,1536,13987,0),(3,16,'UNO',1987,2432,14594,0),(4,16,'UNO',1987,1792,13422,0),(5,16,'UNO',1987,1025,10240,0),(6,16,'UNO',1987,1026,7089,0); +CREATE TABLE `t2` ( + `id` tinyint(3) unsigned NOT NULL auto_increment, + `max_anno_dep` smallint(6) unsigned NOT NULL default '0', + PRIMARY KEY (`id`) +); +INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990); + +SELECT cns.id, cns.max_anno_dep, cns.max_anno_dep = (SELECT s.anno_dep FROM t1 AS s WHERE s.id_cns = cns.id ORDER BY s.anno_dep DESC LIMIT 1) AS PIPPO FROM t2 AS cns; + +DROP TABLE t1, t2; 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; diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index cddb0347b7e..3257002ae2c 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -248,7 +248,7 @@ CREATE TABLE t1 (a_dec DECIMAL(-1,1)); # # Zero prepend overflow bug # ---disable-warnings +--disable_warnings create table t1(a decimal(7,3)); insert into t1 values ('1'),('+1'),('-1'),('0000000001'),('+0000000001'),('-0000000001'),('10'),('+10'),('-10'),('0000000010'),('+0000000010'),('-0000000010'),('100'),('+100'),('-100'),('0000000100'),('+0000000100'),('-0000000100'),('1000'),('+1000'),('-1000'),('0000001000'),('+0000001000'),('-0000001000'),('10000'),('+10000'),('-10000'),('0000010000'),('+0000010000'),('-0000010000'),('100000'),('+100000'),('-100000'),('0000100000'),('+0000100000'),('-0000100000'),('1000000'),('+1000000'),('-1000000'),('0001000000'),('+0001000000'),('-0001000000'),('10000000'),('+10000000'),('-10000000'),('0010000000'),('+0010000000'),('-0010000000'),('100000000'),('+100000000'),('-100000000'),('0100000000'),('+0100000000'),('-0100000000'),('1000000000'),('+1000000000'),('-1000000000'),('1000000000'),('+1000000000'),('-1000000000'); select * from t1; @@ -259,6 +259,6 @@ select * from t1; drop table t1; create table t1(a decimal(7,3) zerofill); insert into t1 values ('1'),('+1'),('-1'),('0000000001'),('+0000000001'),('-0000000001'),('10'),('+10'),('-10'),('0000000010'),('+0000000010'),('-0000000010'),('100'),('+100'),('-100'),('0000000100'),('+0000000100'),('-0000000100'),('1000'),('+1000'),('-1000'),('0000001000'),('+0000001000'),('-0000001000'),('10000'),('+10000'),('-10000'),('0000010000'),('+0000010000'),('-0000010000'),('100000'),('+100000'),('-100000'),('0000100000'),('+0000100000'),('-0000100000'),('1000000'),('+1000000'),('-1000000'),('0001000000'),('+0001000000'),('-0001000000'),('10000000'),('+10000000'),('-10000000'),('0010000000'),('+0010000000'),('-0010000000'),('100000000'),('+100000000'),('-100000000'),('0100000000'),('+0100000000'),('-0100000000'),('1000000000'),('+1000000000'),('-1000000000'),('1000000000'),('+1000000000'),('-1000000000'); ---enable-warnings +--enable_warnings select * from t1; drop table t1; diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 8e50fee56fd..3406dfd6158 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -126,6 +126,6 @@ insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6), ('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4), ('2','2','0',1,7); -delete from t1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3); +delete from m1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3); select * from t1; drop table t1; diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 39742d20c7c..32ed6fe33db 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -71,3 +71,31 @@ select @a:=0; select @a, @a:=@a+count(*), count(*), @a from t1 group by i; select @a:=0; select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i; drop table t1; +# +# Bug #2244: User variables didn't copy collation and derivation +# attributes from values they were initialized to. +# + +set @a=_latin2'test'; +select charset(@a),collation(@a),coercibility(@a); +select @a=_latin2'TEST'; +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 +select @a=_latin2'TEST' collate latin2_bin; + +# +# Check the same invoking Item_set_user_var +# +select charset(@a:=_latin2'test'); +select collation(@a:=_latin2'test'); +select coercibility(@a:=_latin2'test'); +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 +select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci; diff --git a/mysql-test/t/warnings.test b/mysql-test/t/warnings.test index 0456ca8c1fe..b6042df51f1 100644 --- a/mysql-test/t/warnings.test +++ b/mysql-test/t/warnings.test @@ -1,9 +1,9 @@ # # Test some warnings # ---disable-warnings +--disable_warnings drop table if exists t1, t2; ---enable-warnings +--enable_warnings SET SQL_WARNINGS=1; create table t1 (a int); |