diff options
Diffstat (limited to 'mysql-test/r')
28 files changed, 606 insertions, 477 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index ce324dc1fa8..1891da73d85 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -424,3 +424,24 @@ t1 CREATE TABLE `t1` ( ALTER TABLE t1 DROP PRIMARY KEY; ERROR 42000: Can't DROP 'PRIMARY'. Check that column/key exists 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; +Table 't1' already exists +select * from t1; +name +current +select * from mysqltest.t1; +name +mysqltest +drop table t1; +drop database mysqltest; diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result index b02574cf8a3..44a7b4e03b2 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; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 2 +select a from t1; +a +a +A +explain select b from t1; +table type possible_keys key key_len ref rows Extra +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; +table type possible_keys key key_len ref rows Extra +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/ctype_tis620.result b/mysql-test/r/ctype_tis620.result index 10164cd07ef..811609d4ba9 100644 --- a/mysql-test/r/ctype_tis620.result +++ b/mysql-test/r/ctype_tis620.result @@ -1,113 +1,18 @@ drop table if exists t1; -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)); -hex(@u0:=convert(@pl0 using utf8)) -000102030405060708090A0B0C0D0E0F -SELECT hex(@u1:=convert(@pl1 using utf8)); -hex(@u1:=convert(@pl1 using utf8)) -101112131415161718191A1B1C1D1E1F -SELECT hex(@u2:=convert(@pl2 using utf8)); -hex(@u2:=convert(@pl2 using utf8)) -202122232425262728292A2B2C2D2E2F -SELECT hex(@u3:=convert(@pl3 using utf8)); -hex(@u3:=convert(@pl3 using utf8)) -303132333435363738393A3B3C3D3E3F -SELECT hex(@u4:=convert(@pl4 using utf8)); -hex(@u4:=convert(@pl4 using utf8)) -404142434445464748494A4B4C4D4E4F -SELECT hex(@u5:=convert(@pl5 using utf8)); -hex(@u5:=convert(@pl5 using utf8)) -505152535455565758595A5B5C5D5E5F -SELECT hex(@u6:=convert(@pl6 using utf8)); -hex(@u6:=convert(@pl6 using utf8)) -606162636465666768696A6B6C6D6E6F -SELECT hex(@u7:=convert(@pl7 using utf8)); -hex(@u7:=convert(@pl7 using utf8)) -707172737475767778797A7B7C7D7E7F -SELECT hex(@u8:=convert(@pl8 using utf8)); -hex(@u8:=convert(@pl8 using utf8)) -C280C281C282C283C284C285C286C287C288C289C28AC28BC28CC28DC28EC28F -SELECT hex(@u9:=convert(@pl9 using utf8)); -hex(@u9:=convert(@pl9 using utf8)) -C290C291C292C293C294C295C296C297C298C299C29AC29BC29CC29DC29EC29F -SELECT hex(@uA:=convert(@plA using utf8)); -hex(@uA:=convert(@plA using utf8)) -EFBFBDE0B881E0B882E0B883E0B884E0B885E0B886E0B887E0B888E0B889E0B88AE0B88BE0B88CE0B88DE0B88EE0B88F -SELECT hex(@uB:=convert(@plB using utf8)); -hex(@uB:=convert(@plB using utf8)) -E0B890E0B891E0B892E0B893E0B894E0B895E0B896E0B897E0B898E0B899E0B89AE0B89BE0B89CE0B89DE0B89EE0B89F -SELECT hex(@uC:=convert(@plC using utf8)); -hex(@uC:=convert(@plC using utf8)) -E0B8A0E0B8A1E0B8A2E0B8A3E0B8A4E0B8A5E0B8A6E0B8A7E0B8A8E0B8A9E0B8AAE0B8ABE0B8ACE0B8ADE0B8AEE0B8AF -SELECT hex(@uD:=convert(@plD using utf8)); -hex(@uD:=convert(@plD using utf8)) -E0B8B0E0B8B1E0B8B2E0B8B3E0B8B4E0B8B5E0B8B6E0B8B7E0B8B8E0B8B9E0B8BAEFBFBDEFBFBDEFBFBDEFBFBDE0B8BF -SELECT hex(@uE:=convert(@plE using utf8)); -hex(@uE:=convert(@plE using utf8)) -E0B980E0B981E0B982E0B983E0B984E0B985E0B986E0B987E0B988E0B989E0B98AE0B98BE0B98CE0B98DE0B98EE0B98F -SELECT hex(@uF:=convert(@plF using utf8)); -hex(@uF:=convert(@plF using utf8)) -E0B990E0B991E0B992E0B993E0B994E0B995E0B996E0B997E0B998E0B999E0B99AE0B99BEFBFBDEFBFBDEFBFBDEFBFBD -SELECT hex(convert(@u0 USING tis620)); -hex(convert(@u0 USING tis620)) -000102030405060708090A0B0C0D0E0F -SELECT hex(convert(@u1 USING tis620)); -hex(convert(@u1 USING tis620)) -101112131415161718191A1B1C1D1E1F -SELECT hex(convert(@u2 USING tis620)); -hex(convert(@u2 USING tis620)) -202122232425262728292A2B2C2D2E2F -SELECT hex(convert(@u3 USING tis620)); -hex(convert(@u3 USING tis620)) -303132333435363738393A3B3C3D3E3F -SELECT hex(convert(@u4 USING tis620)); -hex(convert(@u4 USING tis620)) -404142434445464748494A4B4C4D4E4F -SELECT hex(convert(@u5 USING tis620)); -hex(convert(@u5 USING tis620)) -505152535455565758595A5B5C5D5E5F -SELECT hex(convert(@u6 USING tis620)); -hex(convert(@u6 USING tis620)) -606162636465666768696A6B6C6D6E6F -SELECT hex(convert(@u7 USING tis620)); -hex(convert(@u7 USING tis620)) -707172737475767778797A7B7C7D7E7F -SELECT hex(convert(@u8 USING tis620)); -hex(convert(@u8 USING tis620)) -808182838485868788898A8B8C8D8E8F -SELECT hex(convert(@u9 USING tis620)); -hex(convert(@u9 USING tis620)) -909192939495969798999A9B9C9D9E9F -SELECT hex(convert(@uA USING tis620)); -hex(convert(@uA USING tis620)) -FFA1A2A3A4A5A6A7A8A9AAABACADAEAF -SELECT hex(convert(@uB USING tis620)); -hex(convert(@uB USING tis620)) -B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF -SELECT hex(convert(@uC USING tis620)); -hex(convert(@uC USING tis620)) -C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF -SELECT hex(convert(@uD USING tis620)); -hex(convert(@uD USING tis620)) -D0D1D2D3D4D5D6D7D8D9DAFFFFFFFFDF -SELECT hex(convert(@uE USING tis620)); -hex(convert(@uE USING tis620)) -E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF -SELECT hex(convert(@uF USING tis620)); -hex(convert(@uF USING tis620)) -F0F1F2F3F4F5F6F7F8F9FAFBFFFFFFFF +CREATE TABLE t1 ( +recid int(11) NOT NULL auto_increment, +dyninfo text, +PRIMARY KEY (recid) +) ENGINE=MyISAM; +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_tis620.result-old b/mysql-test/r/ctype_tis620.result-old new file mode 100644 index 00000000000..10164cd07ef --- /dev/null +++ b/mysql-test/r/ctype_tis620.result-old @@ -0,0 +1,113 @@ +drop table if exists t1; +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)); +hex(@u0:=convert(@pl0 using utf8)) +000102030405060708090A0B0C0D0E0F +SELECT hex(@u1:=convert(@pl1 using utf8)); +hex(@u1:=convert(@pl1 using utf8)) +101112131415161718191A1B1C1D1E1F +SELECT hex(@u2:=convert(@pl2 using utf8)); +hex(@u2:=convert(@pl2 using utf8)) +202122232425262728292A2B2C2D2E2F +SELECT hex(@u3:=convert(@pl3 using utf8)); +hex(@u3:=convert(@pl3 using utf8)) +303132333435363738393A3B3C3D3E3F +SELECT hex(@u4:=convert(@pl4 using utf8)); +hex(@u4:=convert(@pl4 using utf8)) +404142434445464748494A4B4C4D4E4F +SELECT hex(@u5:=convert(@pl5 using utf8)); +hex(@u5:=convert(@pl5 using utf8)) +505152535455565758595A5B5C5D5E5F +SELECT hex(@u6:=convert(@pl6 using utf8)); +hex(@u6:=convert(@pl6 using utf8)) +606162636465666768696A6B6C6D6E6F +SELECT hex(@u7:=convert(@pl7 using utf8)); +hex(@u7:=convert(@pl7 using utf8)) +707172737475767778797A7B7C7D7E7F +SELECT hex(@u8:=convert(@pl8 using utf8)); +hex(@u8:=convert(@pl8 using utf8)) +C280C281C282C283C284C285C286C287C288C289C28AC28BC28CC28DC28EC28F +SELECT hex(@u9:=convert(@pl9 using utf8)); +hex(@u9:=convert(@pl9 using utf8)) +C290C291C292C293C294C295C296C297C298C299C29AC29BC29CC29DC29EC29F +SELECT hex(@uA:=convert(@plA using utf8)); +hex(@uA:=convert(@plA using utf8)) +EFBFBDE0B881E0B882E0B883E0B884E0B885E0B886E0B887E0B888E0B889E0B88AE0B88BE0B88CE0B88DE0B88EE0B88F +SELECT hex(@uB:=convert(@plB using utf8)); +hex(@uB:=convert(@plB using utf8)) +E0B890E0B891E0B892E0B893E0B894E0B895E0B896E0B897E0B898E0B899E0B89AE0B89BE0B89CE0B89DE0B89EE0B89F +SELECT hex(@uC:=convert(@plC using utf8)); +hex(@uC:=convert(@plC using utf8)) +E0B8A0E0B8A1E0B8A2E0B8A3E0B8A4E0B8A5E0B8A6E0B8A7E0B8A8E0B8A9E0B8AAE0B8ABE0B8ACE0B8ADE0B8AEE0B8AF +SELECT hex(@uD:=convert(@plD using utf8)); +hex(@uD:=convert(@plD using utf8)) +E0B8B0E0B8B1E0B8B2E0B8B3E0B8B4E0B8B5E0B8B6E0B8B7E0B8B8E0B8B9E0B8BAEFBFBDEFBFBDEFBFBDEFBFBDE0B8BF +SELECT hex(@uE:=convert(@plE using utf8)); +hex(@uE:=convert(@plE using utf8)) +E0B980E0B981E0B982E0B983E0B984E0B985E0B986E0B987E0B988E0B989E0B98AE0B98BE0B98CE0B98DE0B98EE0B98F +SELECT hex(@uF:=convert(@plF using utf8)); +hex(@uF:=convert(@plF using utf8)) +E0B990E0B991E0B992E0B993E0B994E0B995E0B996E0B997E0B998E0B999E0B99AE0B99BEFBFBDEFBFBDEFBFBDEFBFBD +SELECT hex(convert(@u0 USING tis620)); +hex(convert(@u0 USING tis620)) +000102030405060708090A0B0C0D0E0F +SELECT hex(convert(@u1 USING tis620)); +hex(convert(@u1 USING tis620)) +101112131415161718191A1B1C1D1E1F +SELECT hex(convert(@u2 USING tis620)); +hex(convert(@u2 USING tis620)) +202122232425262728292A2B2C2D2E2F +SELECT hex(convert(@u3 USING tis620)); +hex(convert(@u3 USING tis620)) +303132333435363738393A3B3C3D3E3F +SELECT hex(convert(@u4 USING tis620)); +hex(convert(@u4 USING tis620)) +404142434445464748494A4B4C4D4E4F +SELECT hex(convert(@u5 USING tis620)); +hex(convert(@u5 USING tis620)) +505152535455565758595A5B5C5D5E5F +SELECT hex(convert(@u6 USING tis620)); +hex(convert(@u6 USING tis620)) +606162636465666768696A6B6C6D6E6F +SELECT hex(convert(@u7 USING tis620)); +hex(convert(@u7 USING tis620)) +707172737475767778797A7B7C7D7E7F +SELECT hex(convert(@u8 USING tis620)); +hex(convert(@u8 USING tis620)) +808182838485868788898A8B8C8D8E8F +SELECT hex(convert(@u9 USING tis620)); +hex(convert(@u9 USING tis620)) +909192939495969798999A9B9C9D9E9F +SELECT hex(convert(@uA USING tis620)); +hex(convert(@uA USING tis620)) +FFA1A2A3A4A5A6A7A8A9AAABACADAEAF +SELECT hex(convert(@uB USING tis620)); +hex(convert(@uB USING tis620)) +B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF +SELECT hex(convert(@uC USING tis620)); +hex(convert(@uC USING tis620)) +C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF +SELECT hex(convert(@uD USING tis620)); +hex(convert(@uD USING tis620)) +D0D1D2D3D4D5D6D7D8D9DAFFFFFFFFDF +SELECT hex(convert(@uE USING tis620)); +hex(convert(@uE USING tis620)) +E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF +SELECT hex(convert(@uF USING tis620)); +hex(convert(@uF USING tis620)) +F0F1F2F3F4F5F6F7F8F9FAFBFFFFFFFF diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 04c36f059e2..2ca4f55d24c 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 @@ -210,7 +214,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_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..ffa7191f4e5 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -263,348 +263,3 @@ SELECT bugdesc, REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb') bugdesc REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb') aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa drop table t1; -CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) ENGINE=MyISAM; -INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); -SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); -1 -DROP TABLE t1; -select 1=_latin1'1'; -1=_latin1'1' -1 -select _latin1'1'=1; -_latin1'1'=1 -1 -select _latin2'1'=1; -_latin2'1'=1 -1 -select 1=_latin2'1'; -1=_latin2'1' -1 -select _latin1'1'=_latin2'1'; -ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '=' -select row('a','b','c') = row('a','b','c'); -row('a','b','c') = row('a','b','c') -1 -select row('A','b','c') = row('a','b','c'); -row('A','b','c') = row('a','b','c') -1 -select row('A' COLLATE latin1_bin,'b','c') = row('a','b','c'); -row('A' COLLATE latin1_bin,'b','c') = row('a','b','c') -0 -select row('A','b','c') = row('a' COLLATE latin1_bin,'b','c'); -row('A','b','c') = row('a' COLLATE latin1_bin,'b','c') -0 -select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c'); -ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation '=' -select concat(_latin1'a',_latin2'a'); -ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat' -select concat(_latin1'a',_latin2'a',_latin5'a'); -ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin5_turkish_ci,COERCIBLE) for operation 'concat' -select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a'); -ERROR HY000: Illegal mix of collations for operation 'concat' -select FIELD('b','A','B'); -FIELD('b','A','B') -2 -select FIELD('B','A','B'); -FIELD('B','A','B') -2 -select FIELD('b' COLLATE latin1_bin,'A','B'); -FIELD('b' COLLATE latin1_bin,'A','B') -0 -select FIELD('b','A' COLLATE latin1_bin,'B'); -FIELD('b','A' COLLATE latin1_bin,'B') -0 -select FIELD(_latin2'b','A','B'); -ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field' -select FIELD('b',_latin2'A','B'); -ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field' -select FIELD('b',_latin2'A','B',1); -FIELD('b',_latin2'A','B',1) -1 -select POSITION(_latin1'B' IN _latin1'abcd'); -POSITION(_latin1'B' IN _latin1'abcd') -2 -select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin); -POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin) -0 -select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd'); -POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd') -0 -select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin); -ERROR HY000: Illegal mix of collations (latin1_bin,EXPLICIT) and (latin1_general_ci,EXPLICIT) for operation 'locate' -select POSITION(_latin1'B' IN _latin2'abcd'); -ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'locate' -select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'); -FIND_IN_SET(_latin1'B',_latin1'a,b,c,d') -2 -select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin); -ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'find_in_set' -select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d'); -ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'find_in_set' -select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2); -SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2) -abcdabc -select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2); -ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'substr_index' -select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2); -ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'substr_index' -select _latin1'B' between _latin1'a' and _latin1'c'; -_latin1'B' between _latin1'a' and _latin1'c' -1 -select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c'; -_latin1'B' collate latin1_bin between _latin1'a' and _latin1'c' -0 -select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c'; -_latin1'B' between _latin1'a' collate latin1_bin and _latin1'c' -0 -select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin; -_latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin -0 -select _latin2'B' between _latin1'a' and _latin1'b'; -ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between' -select _latin1'B' between _latin2'a' and _latin1'b'; -ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between' -select _latin1'B' between _latin1'a' and _latin2'b'; -ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation 'between' -select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b'; -ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation 'between' -select _latin1'B' in (_latin1'a',_latin1'b'); -_latin1'B' in (_latin1'a',_latin1'b') -1 -select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b'); -_latin1'B' collate latin1_bin in (_latin1'a',_latin1'b') -0 -select _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b'); -_latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b') -0 -select _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin); -_latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin) -0 -select _latin2'B' in (_latin1'a',_latin1'b'); -ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN ' -select _latin1'B' in (_latin2'a',_latin1'b'); -ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN ' -select _latin1'B' in (_latin1'a',_latin2'b'); -ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation ' IN ' -select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b'); -ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation ' IN ' -select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin); -ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_bin,EXPLICIT) for operation ' IN ' -select collation(bin(130)), coercibility(bin(130)); -collation(bin(130)) coercibility(bin(130)) -latin1_swedish_ci 3 -select collation(oct(130)), coercibility(oct(130)); -collation(oct(130)) coercibility(oct(130)) -latin1_swedish_ci 3 -select collation(conv(130,16,10)), coercibility(conv(130,16,10)); -collation(conv(130,16,10)) coercibility(conv(130,16,10)) -latin1_swedish_ci 3 -select collation(hex(130)), coercibility(hex(130)); -collation(hex(130)) coercibility(hex(130)) -latin1_swedish_ci 3 -select collation(char(130)), coercibility(hex(130)); -collation(char(130)) coercibility(hex(130)) -binary 3 -select collation(format(130,10)), coercibility(format(130,10)); -collation(format(130,10)) coercibility(format(130,10)) -latin1_swedish_ci 3 -select collation(lcase(_latin2'a')), coercibility(lcase(_latin2'a')); -collation(lcase(_latin2'a')) coercibility(lcase(_latin2'a')) -latin2_general_ci 3 -select collation(ucase(_latin2'a')), coercibility(ucase(_latin2'a')); -collation(ucase(_latin2'a')) coercibility(ucase(_latin2'a')) -latin2_general_ci 3 -select collation(left(_latin2'a',1)), coercibility(left(_latin2'a',1)); -collation(left(_latin2'a',1)) coercibility(left(_latin2'a',1)) -latin2_general_ci 3 -select collation(right(_latin2'a',1)), coercibility(right(_latin2'a',1)); -collation(right(_latin2'a',1)) coercibility(right(_latin2'a',1)) -latin2_general_ci 3 -select collation(substring(_latin2'a',1,1)), coercibility(substring(_latin2'a',1,1)); -collation(substring(_latin2'a',1,1)) coercibility(substring(_latin2'a',1,1)) -latin2_general_ci 3 -select collation(concat(_latin2'a',_latin2'b')), coercibility(concat(_latin2'a',_latin2'b')); -collation(concat(_latin2'a',_latin2'b')) coercibility(concat(_latin2'a',_latin2'b')) -latin2_general_ci 3 -select collation(lpad(_latin2'a',4,_latin2'b')), coercibility(lpad(_latin2'a',4,_latin2'b')); -collation(lpad(_latin2'a',4,_latin2'b')) coercibility(lpad(_latin2'a',4,_latin2'b')) -latin2_general_ci 3 -select collation(rpad(_latin2'a',4,_latin2'b')), coercibility(rpad(_latin2'a',4,_latin2'b')); -collation(rpad(_latin2'a',4,_latin2'b')) coercibility(rpad(_latin2'a',4,_latin2'b')) -latin2_general_ci 3 -select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b')); -collation(concat_ws(_latin2'a',_latin2'b')) coercibility(concat_ws(_latin2'a',_latin2'b')) -latin2_general_ci 3 -select collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')), coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c')); -collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')) coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c')) -latin2_general_ci 3 -select collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')), coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' ')); -collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')) coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' ')) -binary 3 -select collation(trim(_latin2' a ')), coercibility(trim(_latin2' a ')); -collation(trim(_latin2' a ')) coercibility(trim(_latin2' a ')) -latin2_general_ci 3 -select collation(ltrim(_latin2' a ')), coercibility(ltrim(_latin2' a ')); -collation(ltrim(_latin2' a ')) coercibility(ltrim(_latin2' a ')) -latin2_general_ci 3 -select collation(rtrim(_latin2' a ')), coercibility(rtrim(_latin2' a ')); -collation(rtrim(_latin2' a ')) coercibility(rtrim(_latin2' a ')) -latin2_general_ci 3 -select collation(trim(LEADING _latin2' ' FROM _latin2'a')), coercibility(trim(LEADING _latin2'a' FROM _latin2'a')); -collation(trim(LEADING _latin2' ' FROM _latin2'a')) coercibility(trim(LEADING _latin2'a' FROM _latin2'a')) -latin2_general_ci 3 -select collation(trim(TRAILING _latin2' ' FROM _latin2'a')), coercibility(trim(TRAILING _latin2'a' FROM _latin2'a')); -collation(trim(TRAILING _latin2' ' FROM _latin2'a')) coercibility(trim(TRAILING _latin2'a' FROM _latin2'a')) -latin2_general_ci 3 -select collation(trim(BOTH _latin2' ' FROM _latin2'a')), coercibility(trim(BOTH _latin2'a' FROM _latin2'a')); -collation(trim(BOTH _latin2' ' FROM _latin2'a')) coercibility(trim(BOTH _latin2'a' FROM _latin2'a')) -latin2_general_ci 3 -select collation(repeat(_latin2'a',10)), coercibility(repeat(_latin2'a',10)); -collation(repeat(_latin2'a',10)) coercibility(repeat(_latin2'a',10)) -latin2_general_ci 3 -select collation(reverse(_latin2'ab')), coercibility(reverse(_latin2'ab')); -collation(reverse(_latin2'ab')) coercibility(reverse(_latin2'ab')) -latin2_general_ci 3 -select collation(quote(_latin2'ab')), coercibility(quote(_latin2'ab')); -collation(quote(_latin2'ab')) coercibility(quote(_latin2'ab')) -latin2_general_ci 3 -select collation(soundex(_latin2'ab')), coercibility(soundex(_latin2'ab')); -collation(soundex(_latin2'ab')) coercibility(soundex(_latin2'ab')) -latin2_general_ci 3 -select collation(substring(_latin2'ab',1)), coercibility(substring(_latin2'ab',1)); -collation(substring(_latin2'ab',1)) coercibility(substring(_latin2'ab',1)) -latin2_general_ci 3 -select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef')); -collation(insert(_latin2'abcd',2,3,_latin2'ef')) coercibility(insert(_latin2'abcd',2,3,_latin2'ef')) -latin2_general_ci 3 -select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B')); -collation(replace(_latin2'abcd',_latin2'b',_latin2'B')) coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B')) -latin2_general_ci 3 -create table t1 -select -bin(130), -oct(130), -conv(130,16,10), -hex(130), -char(130), -format(130,10), -left(_latin2'a',1), -right(_latin2'a',1), -lcase(_latin2'a'), -ucase(_latin2'a'), -substring(_latin2'a',1,1), -concat(_latin2'a',_latin2'b'), -lpad(_latin2'a',4,_latin2'b'), -rpad(_latin2'a',4,_latin2'b'), -concat_ws(_latin2'a',_latin2'b'), -make_set(255,_latin2'a',_latin2'b',_latin2'c'), -export_set(255,_latin2'y',_latin2'n',_latin2' '), -trim(_latin2' a '), -ltrim(_latin2' a '), -rtrim(_latin2' a '), -trim(LEADING _latin2' ' FROM _latin2' a '), -trim(TRAILING _latin2' ' FROM _latin2' a '), -trim(BOTH _latin2' ' FROM _latin2' a '), -repeat(_latin2'a',10), -reverse(_latin2'ab'), -quote(_latin2'ab'), -soundex(_latin2'ab'), -substring(_latin2'ab',1), -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 -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `bin(130)` char(64) NOT NULL default '', - `oct(130)` char(64) NOT NULL default '', - `conv(130,16,10)` char(64) NOT NULL default '', - `hex(130)` char(6) NOT NULL default '', - `char(130)` char(1) NOT NULL default '', - `format(130,10)` char(4) NOT NULL default '', - `left(_latin2'a',1)` char(1) character set latin2 NOT NULL default '', - `right(_latin2'a',1)` char(1) character set latin2 NOT NULL default '', - `lcase(_latin2'a')` char(1) character set latin2 NOT NULL default '', - `ucase(_latin2'a')` char(1) character set latin2 NOT NULL default '', - `substring(_latin2'a',1,1)` char(1) character set latin2 NOT NULL default '', - `concat(_latin2'a',_latin2'b')` char(2) character set latin2 NOT NULL default '', - `lpad(_latin2'a',4,_latin2'b')` char(4) character set latin2 NOT NULL default '', - `rpad(_latin2'a',4,_latin2'b')` char(4) character set latin2 NOT NULL default '', - `concat_ws(_latin2'a',_latin2'b')` char(1) character set latin2 NOT NULL default '', - `make_set(255,_latin2'a',_latin2'b',_latin2'c')` char(5) character set latin2 NOT NULL default '', - `export_set(255,_latin2'y',_latin2'n',_latin2' ')` char(127) character set latin2 NOT NULL default '', - `trim(_latin2' a ')` char(3) character set latin2 NOT NULL default '', - `ltrim(_latin2' a ')` char(3) character set latin2 NOT NULL default '', - `rtrim(_latin2' a ')` char(3) character set latin2 NOT NULL default '', - `trim(LEADING _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '', - `trim(TRAILING _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '', - `trim(BOTH _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '', - `repeat(_latin2'a',10)` char(10) character set latin2 NOT NULL default '', - `reverse(_latin2'ab')` char(2) character set latin2 NOT NULL default '', - `quote(_latin2'ab')` char(6) character set latin2 NOT NULL default '', - `soundex(_latin2'ab')` char(4) character set latin2 NOT NULL default '', - `substring(_latin2'ab',1)` char(2) character set latin2 NOT NULL default '', - `insert(_latin2'abcd',2,3,_latin2'ef')` char(6) character set latin2 NOT NULL default '', - `replace(_latin2'abcd',_latin2'b',_latin2'B')` char(4) character set latin2 NOT NULL default '' -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -drop table t1; -select SUBSTR('abcdefg',3,2); -SUBSTR('abcdefg',3,2) -cd -select SUBSTRING('abcdefg',3,2); -SUBSTRING('abcdefg',3,2) -cd -select SUBSTR('abcdefg',-3,2) FROM DUAL; -SUBSTR('abcdefg',-3,2) -ef -select SUBSTR('abcdefg',-1,5) FROM DUAL; -SUBSTR('abcdefg',-1,5) -g -select SUBSTR('abcdefg',0,0) FROM DUAL; -SUBSTR('abcdefg',0,0) - -select SUBSTR('abcdefg',-1,-1) FROM DUAL; -SUBSTR('abcdefg',-1,-1) - -select SUBSTR('abcdefg',1,-1) FROM DUAL; -SUBSTR('abcdefg',1,-1) - -create table t7 (s1 char); -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 -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"); -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/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..1c48f5f9048 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,6 +1329,9 @@ 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; diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index a77d67b5f64..93007a325ba 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -595,3 +595,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; +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; +select * from t2; +No Field Count +0 1 100 +0 2 100 +drop table t1, t2; diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result index 2cb9527b94b..2e95c25fd9c 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; +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..4ab9a3dc8fe 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -398,6 +398,52 @@ 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 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; +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..578e1dcaec8 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -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/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..6d924ff9f2e 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -289,6 +289,19 @@ 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 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..fa254d76393 --- /dev/null +++ b/mysql-test/r/rpl_multi_delete.result @@ -0,0 +1,22 @@ +slave stop; +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; +slave start; +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/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/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..8538263f7d6 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -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_timestamp.result b/mysql-test/r/type_timestamp.result index 172ccc440f4..f704add4ebc 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -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(14) NOT NULL, + `t2` timestamp(14) NOT NULL default '2003-01-01 00:00:00' +) TYPE=MyISAM +show columns from t1; +Field Type Null Key Default Extra +t1 timestamp(14) YES NULL +t2 timestamp(14) YES 2003-01-01 00:00:00 +show columns from t1 like 't2'; +Field Type Null Key Default Extra +t2 timestamp(14) 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(14) NOT NULL, + `t2` timestamp(14) NOT NULL default '2003-01-01 00:00:00' +) TYPE=MyISAM +alter table t1 add column t0 timestamp first; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t0` timestamp(14) NOT NULL, + `t1` timestamp(14) NOT NULL default '2003-01-01 00:00:00', + `t2` timestamp(14) NOT NULL default '2003-01-01 00:00:00' +) TYPE=MyISAM +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_year.result b/mysql-test/r/type_year.result index 25ddda88f48..5ef3c1bba81 100644 --- a/mysql-test/r/type_year.result +++ b/mysql-test/r/type_year.result @@ -26,3 +26,9 @@ y y2 2001 01 2069 69 drop table t1; +create table t1 (y year); +insert into t1 values (now()); +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..801b9ccf80f 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -380,6 +380,8 @@ ERROR HY000: Unknown system variable 'xxxxxxxxxx' select 1; 1 1 +select @@session.key_buffer_size; +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 |