diff options
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/alter_table.result | 10 | ||||
| -rw-r--r-- | mysql-test/r/create.result | 22 | ||||
| -rw-r--r-- | mysql-test/r/ctype_many.result | 1393 | ||||
| -rw-r--r-- | mysql-test/r/derived.result | 20 | ||||
| -rw-r--r-- | mysql-test/r/fulltext.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/heap.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/heap_btree.result | 218 | ||||
| -rw-r--r-- | mysql-test/r/heap_hash.result | 205 | ||||
| -rw-r--r-- | mysql-test/r/innodb.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/isam.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/merge.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/myisam.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/select.result | 30 | ||||
| -rw-r--r-- | mysql-test/r/show_check.result | 25 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 94 | ||||
| -rw-r--r-- | mysql-test/r/symlink.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/type_blob.result | 20 | ||||
| -rw-r--r-- | mysql-test/r/type_float.result | 32 | ||||
| -rw-r--r-- | mysql-test/r/type_ranges.result | 170 | ||||
| -rw-r--r-- | mysql-test/r/union.result | 1 |
20 files changed, 2112 insertions, 150 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 1d6d69465da..159421e8f88 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -50,10 +50,10 @@ PRIMARY KEY (GROUP_ID,LANG_ID), KEY NAME (NAME)); ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null; SHOW FULL COLUMNS FROM t1; -Field Type Null Key Default Extra Privileges -GROUP_ID int(10) unsigned PRI 0 select,insert,update,references -LANG_ID smallint(5) unsigned PRI 0 select,insert,update,references -NAME char(80) MUL select,insert,update,references +Field Type Null Key Default Extra Privileges Comment +GROUP_ID int(10) unsigned PRI 0 select,insert,update,references +LANG_ID smallint(5) unsigned PRI 0 select,insert,update,references +NAME char(80) character set latin1 MUL select,insert,update,references DROP TABLE t1; create table t1 (n int); insert into t1 values(9),(3),(12),(10); @@ -120,5 +120,5 @@ alter table t2 rename t1, add c char(10) comment "no comment"; show columns from t1; Field Type Null Key Default Extra i int(10) unsigned PRI NULL auto_increment -c char(10) YES NULL +c char(10) character set latin1 YES NULL drop table t1; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 71044065dad..39bd1caee0c 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -72,10 +72,10 @@ create table t1(x varchar(50) ); create table t2 select x from t1 where 1=2; describe t1; Field Type Null Key Default Extra -x varchar(50) YES NULL +x varchar(50) character set latin1 YES NULL describe t2; Field Type Null Key Default Extra -x char(50) YES NULL +x char(50) character set latin1 YES NULL drop table t2; create table t2 select now() as a , curtime() as b, curdate() as c , 1+1 as d , 1.0 + 1 as e , 33333333333333333 + 3 as f; describe t2; @@ -94,6 +94,24 @@ d date 0000-00-00 t time 00:00:00 dt datetime 0000-00-00 00:00:00 drop table t1,t2; +create table t1 (a tinyint); +create table t2 (a int) select * from t1; +describe t1; +Field Type Null Key Default Extra +a tinyint(4) YES NULL +describe t2; +Field Type Null Key Default Extra +a int(11) YES NULL +drop table if exists t2; +create table t2 (a int, a float) select * from t1; +Duplicate column name 'a' +drop table if exists t2; +create table t2 (a int) select a as b, a+1 as b from t1; +Duplicate column name 'b' +drop table if exists t2; +create table t2 (b int) select a as b, a+1 as b from t1; +Duplicate column name 'b' +drop table if exists t1,t2; create table t1 (a int not null, b int, primary key(a), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b)); show create table t1; Table Create Table diff --git a/mysql-test/r/ctype_many.result b/mysql-test/r/ctype_many.result new file mode 100644 index 00000000000..ddc3989282b --- /dev/null +++ b/mysql-test/r/ctype_many.result @@ -0,0 +1,1393 @@ +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( +comment CHAR(32) CHARACTER SET latin1 NOT NULL, +koi8_ru_f CHAR(32) CHARACTER SET koi8_ru NOT NULL +) CHARSET=latin5; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `comment` char(32) character set latin1 NOT NULL default '', + `koi8_ru_f` char(32) character set koi8_ru NOT NULL default '' +) TYPE=MyISAM CHARSET=latin5 +ALTER TABLE t1 CHANGE comment comment CHAR(32) CHARACTER SET latin2 NOT NULL; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `comment` char(32) character set latin2 NOT NULL default '', + `koi8_ru_f` char(32) character set koi8_ru NOT NULL default '' +) TYPE=MyISAM CHARSET=latin5 +ALTER TABLE t1 ADD latin5_f CHAR(32) NOT NULL; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `comment` char(32) character set latin2 NOT NULL default '', + `koi8_ru_f` char(32) character set koi8_ru NOT NULL default '', + `latin5_f` char(32) character set latin5 NOT NULL default '' +) TYPE=MyISAM CHARSET=latin5 +ALTER TABLE t1 CHARSET=latin2; +ALTER TABLE t1 ADD latin2_f CHAR(32) NOT NULL; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `comment` char(32) character set latin2 NOT NULL default '', + `koi8_ru_f` char(32) character set koi8_ru NOT NULL default '', + `latin5_f` char(32) character set latin5 NOT NULL default '', + `latin2_f` char(32) character set latin2 NOT NULL default '' +) TYPE=MyISAM CHARSET=latin2 +ALTER TABLE t1 DROP latin2_f, DROP latin5_f; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `comment` char(32) character set latin2 NOT NULL default '', + `koi8_ru_f` char(32) character set koi8_ru NOT NULL default '' +) TYPE=MyISAM CHARSET=latin2 +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('a','LAT SMALL A'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('b','LAT SMALL B'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('c','LAT SMALL C'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('d','LAT SMALL D'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('e','LAT SMALL E'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('f','LAT SMALL F'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('g','LAT SMALL G'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('h','LAT SMALL H'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('i','LAT SMALL I'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('j','LAT SMALL J'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('k','LAT SMALL K'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('l','LAT SMALL L'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('m','LAT SMALL M'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('n','LAT SMALL N'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('o','LAT SMALL O'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('p','LAT SMALL P'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('q','LAT SMALL Q'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('r','LAT SMALL R'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('s','LAT SMALL S'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('t','LAT SMALL T'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('u','LAT SMALL U'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('v','LAT SMALL V'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('w','LAT SMALL W'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('x','LAT SMALL X'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('y','LAT SMALL Y'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('z','LAT SMALL Z'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('A','LAT CAPIT A'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('B','LAT CAPIT B'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('C','LAT CAPIT C'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('D','LAT CAPIT D'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('E','LAT CAPIT E'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('F','LAT CAPIT F'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('G','LAT CAPIT G'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('H','LAT CAPIT H'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('I','LAT CAPIT I'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('J','LAT CAPIT J'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('K','LAT CAPIT K'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('L','LAT CAPIT L'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('M','LAT CAPIT M'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('N','LAT CAPIT N'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('O','LAT CAPIT O'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('P','LAT CAPIT P'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Q','LAT CAPIT Q'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('R','LAT CAPIT R'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('S','LAT CAPIT S'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('T','LAT CAPIT T'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('U','LAT CAPIT U'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('V','LAT CAPIT V'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('W','LAT CAPIT W'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('X','LAT CAPIT X'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Y','LAT CAPIT Y'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Z','LAT CAPIT Z'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Á','CYR SMALL A'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Â','CYR SMALL BE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('×','CYR SMALL VE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ç','CYR SMALL GE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ä','CYR SMALL DE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Å','CYR SMALL IE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('£','CYR SMALL IO'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ö','CYR SMALL ZHE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ú','CYR SMALL ZE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('É','CYR SMALL I'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ë','CYR SMALL KA'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ì','CYR SMALL EL'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Í','CYR SMALL EM'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Î','CYR SMALL EN'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ï','CYR SMALL O'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ð','CYR SMALL PE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ò','CYR SMALL ER'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ó','CYR SMALL ES'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ô','CYR SMALL TE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Õ','CYR SMALL U'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Æ','CYR SMALL EF'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('È','CYR SMALL HA'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ã','CYR SMALL TSE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Þ','CYR SMALL CHE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Û','CYR SMALL SHA'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ý','CYR SMALL SCHA'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ß','CYR SMALL HARD SIGN'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ù','CYR SMALL YERU'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ø','CYR SMALL SOFT SIGN'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ü','CYR SMALL E'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('À','CYR SMALL YU'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('Ñ','CYR SMALL YA'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('á','CYR CAPIT A'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('â','CYR CAPIT BE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('÷','CYR CAPIT VE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ç','CYR CAPIT GE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ä','CYR CAPIT DE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('å','CYR CAPIT IE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('³','CYR CAPIT IO'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ö','CYR CAPIT ZHE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ú','CYR CAPIT ZE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('é','CYR CAPIT I'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ë','CYR CAPIT KA'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ì','CYR CAPIT EL'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('í','CYR CAPIT EM'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('î','CYR CAPIT EN'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ï','CYR CAPIT O'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ð','CYR CAPIT PE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ò','CYR CAPIT ER'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ó','CYR CAPIT ES'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ô','CYR CAPIT TE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('õ','CYR CAPIT U'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('æ','CYR CAPIT EF'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('è','CYR CAPIT HA'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ã','CYR CAPIT TSE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('þ','CYR CAPIT CHE'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('û','CYR CAPIT SHA'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ý','CYR CAPIT SCHA'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ÿ','CYR CAPIT HARD SIGN'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ù','CYR CAPIT YERU'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ø','CYR CAPIT SOFT SIGN'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ü','CYR CAPIT E'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('à','CYR CAPIT YU'); +INSERT INTO t1 (koi8_ru_f,comment) VALUES ('ñ','CYR CAPIT YA'); +SELECT CONVERT(koi8_ru_f USING utf8),MIN(comment),COUNT(*) FROM t1 GROUP BY 1; +CONVERT(koi8_ru_f USING utf8) MIN(comment) COUNT(*) +a LAT CAPIT A 2 +b LAT CAPIT B 2 +c LAT CAPIT C 2 +d LAT CAPIT D 2 +e LAT CAPIT E 2 +f LAT CAPIT F 2 +g LAT CAPIT G 2 +h LAT CAPIT H 2 +i LAT CAPIT I 2 +j LAT CAPIT J 2 +k LAT CAPIT K 2 +l LAT CAPIT L 2 +m LAT CAPIT M 2 +n LAT CAPIT N 2 +o LAT CAPIT O 2 +p LAT CAPIT P 2 +q LAT CAPIT Q 2 +r LAT CAPIT R 2 +s LAT CAPIT S 2 +t LAT CAPIT T 2 +u LAT CAPIT U 2 +v LAT CAPIT V 2 +w LAT CAPIT W 2 +x LAT CAPIT X 2 +y LAT CAPIT Y 2 +z LAT CAPIT Z 2 +а CYR CAPIT A 2 +б CYR CAPIT BE 2 +в CYR CAPIT VE 2 +г CYR CAPIT GE 2 +д CYR CAPIT DE 2 +е CYR CAPIT IE 4 +ж CYR CAPIT ZHE 2 +з CYR CAPIT ZE 2 +и CYR CAPIT I 2 +к CYR CAPIT KA 2 +л CYR CAPIT EL 2 +м CYR CAPIT EM 2 +н CYR CAPIT EN 2 +о CYR CAPIT O 2 +п CYR CAPIT PE 2 +Ñ€ CYR CAPIT ER 2 +Ñ CYR CAPIT ES 2 +Ñ‚ CYR CAPIT TE 2 +у CYR CAPIT U 2 +Ñ„ CYR CAPIT EF 2 +Ñ… CYR CAPIT HA 2 +ц CYR CAPIT TSE 2 +ч CYR CAPIT CHE 2 +ш CYR CAPIT SHA 2 +щ CYR CAPIT SCHA 2 +ÑŠ CYR CAPIT HARD SIGN 2 +Ñ‹ CYR CAPIT YERU 2 +ÑŒ CYR CAPIT SOFT SIGN 2 +Ñ CYR CAPIT E 2 +ÑŽ CYR CAPIT YU 2 +Ñ CYR CAPIT YA 2 +ALTER TABLE t1 ADD utf8_f CHAR(32) CHARACTER SET utf8 NOT NULL; +UPDATE t1 SET utf8_f=CONVERT(koi8_ru_f USING utf8); +SELECT * FROM t1; +comment koi8_ru_f utf8_f +LAT SMALL A a a +LAT SMALL B b b +LAT SMALL C c c +LAT SMALL D d d +LAT SMALL E e e +LAT SMALL F f f +LAT SMALL G g g +LAT SMALL H h h +LAT SMALL I i i +LAT SMALL J j j +LAT SMALL K k k +LAT SMALL L l l +LAT SMALL M m m +LAT SMALL N n n +LAT SMALL O o o +LAT SMALL P p p +LAT SMALL Q q q +LAT SMALL R r r +LAT SMALL S s s +LAT SMALL T t t +LAT SMALL U u u +LAT SMALL V v v +LAT SMALL W w w +LAT SMALL X x x +LAT SMALL Y y y +LAT SMALL Z z z +LAT CAPIT A A A +LAT CAPIT B B B +LAT CAPIT C C C +LAT CAPIT D D D +LAT CAPIT E E E +LAT CAPIT F F F +LAT CAPIT G G G +LAT CAPIT H H H +LAT CAPIT I I I +LAT CAPIT J J J +LAT CAPIT K K K +LAT CAPIT L L L +LAT CAPIT M M M +LAT CAPIT N N N +LAT CAPIT O O O +LAT CAPIT P P P +LAT CAPIT Q Q Q +LAT CAPIT R R R +LAT CAPIT S S S +LAT CAPIT T T T +LAT CAPIT U U U +LAT CAPIT V V V +LAT CAPIT W W W +LAT CAPIT X X X +LAT CAPIT Y Y Y +LAT CAPIT Z Z Z +CYR SMALL A Á а +CYR SMALL BE  б +CYR SMALL VE × Ð² +CYR SMALL GE Ç Ð³ +CYR SMALL DE Ä Ð´ +CYR SMALL IE Šе +CYR SMALL IO £ Ñ‘ +CYR SMALL ZHE Ö Ð¶ +CYR SMALL ZE Ú Ð· +CYR SMALL I É Ð¸ +CYR SMALL KA Ë Ðº +CYR SMALL EL Ì Ð» +CYR SMALL EM Í Ð¼ +CYR SMALL EN Πн +CYR SMALL O Ï Ð¾ +CYR SMALL PE Рп +CYR SMALL ER Ò Ñ€ +CYR SMALL ES Ó Ñ +CYR SMALL TE Ô Ñ‚ +CYR SMALL U Õ Ñƒ +CYR SMALL EF Æ Ñ„ +CYR SMALL HA È Ñ… +CYR SMALL TSE à ц +CYR SMALL CHE Þ Ñ‡ +CYR SMALL SHA Û Ñˆ +CYR SMALL SCHA Ý Ñ‰ +CYR SMALL HARD SIGN ß ÑŠ +CYR SMALL YERU Ù Ñ‹ +CYR SMALL SOFT SIGN Ø ÑŒ +CYR SMALL E Ü Ñ +CYR SMALL YU À ÑŽ +CYR SMALL YA Ñ Ñ +CYR CAPIT A á Ð +CYR CAPIT BE â Б +CYR CAPIT VE ÷ Ð’ +CYR CAPIT GE ç Г +CYR CAPIT DE ä Д +CYR CAPIT IE å Е +CYR CAPIT IO ³ Ð +CYR CAPIT ZHE ö Ж +CYR CAPIT ZE ú З +CYR CAPIT I é И +CYR CAPIT KA ë К +CYR CAPIT EL ì Л +CYR CAPIT EM í Ðœ +CYR CAPIT EN î Ð +CYR CAPIT O ï О +CYR CAPIT PE ð П +CYR CAPIT ER ò Ð +CYR CAPIT ES ó С +CYR CAPIT TE ô Т +CYR CAPIT U õ У +CYR CAPIT EF æ Ф +CYR CAPIT HA è Ð¥ +CYR CAPIT TSE ã Ц +CYR CAPIT CHE þ Ч +CYR CAPIT SHA û Ш +CYR CAPIT SCHA ý Щ +CYR CAPIT HARD SIGN ÿ Ъ +CYR CAPIT YERU ù Ы +CYR CAPIT SOFT SIGN ø Ь +CYR CAPIT E ü Ð +CYR CAPIT YU à Ю +CYR CAPIT YA ñ Я +SELECT koi8_ru_f,MIN(comment) FROM t1 GROUP BY 1; +koi8_ru_f MIN(comment) +a LAT CAPIT A +b LAT CAPIT B +c LAT CAPIT C +d LAT CAPIT D +e LAT CAPIT E +f LAT CAPIT F +g LAT CAPIT G +h LAT CAPIT H +i LAT CAPIT I +j LAT CAPIT J +k LAT CAPIT K +l LAT CAPIT L +m LAT CAPIT M +n LAT CAPIT N +o LAT CAPIT O +p LAT CAPIT P +q LAT CAPIT Q +r LAT CAPIT R +s LAT CAPIT S +t LAT CAPIT T +u LAT CAPIT U +v LAT CAPIT V +w LAT CAPIT W +x LAT CAPIT X +y LAT CAPIT Y +z LAT CAPIT Z +Á CYR CAPIT A + CYR CAPIT BE +× CYR CAPIT VE +Ç CYR CAPIT GE +Ä CYR CAPIT DE +Å CYR CAPIT IE +£ CYR CAPIT IO +Ö CYR CAPIT ZHE +Ú CYR CAPIT ZE +É CYR CAPIT I +Ë CYR CAPIT KA +Ì CYR CAPIT EL +Í CYR CAPIT EM +Î CYR CAPIT EN +Ï CYR CAPIT O +Ð CYR CAPIT PE +Ò CYR CAPIT ER +Ó CYR CAPIT ES +Ô CYR CAPIT TE +Õ CYR CAPIT U +Æ CYR CAPIT EF +È CYR CAPIT HA +à CYR CAPIT TSE +Þ CYR CAPIT CHE +Û CYR CAPIT SHA +Ý CYR CAPIT SCHA +ß CYR CAPIT HARD SIGN +Ù CYR CAPIT YERU +Ø CYR CAPIT SOFT SIGN +Ü CYR CAPIT E +À CYR CAPIT YU +Ñ CYR CAPIT YA +SELECT utf8_f,MIN(comment) FROM t1 GROUP BY 1; +utf8_f MIN(comment) +a LAT CAPIT A +b LAT CAPIT B +c LAT CAPIT C +d LAT CAPIT D +e LAT CAPIT E +f LAT CAPIT F +g LAT CAPIT G +h LAT CAPIT H +i LAT CAPIT I +j LAT CAPIT J +k LAT CAPIT K +l LAT CAPIT L +m LAT CAPIT M +n LAT CAPIT N +o LAT CAPIT O +p LAT CAPIT P +q LAT CAPIT Q +r LAT CAPIT R +s LAT CAPIT S +t LAT CAPIT T +u LAT CAPIT U +v LAT CAPIT V +w LAT CAPIT W +x LAT CAPIT X +y LAT CAPIT Y +z LAT CAPIT Z +а CYR CAPIT A +б CYR CAPIT BE +в CYR CAPIT VE +г CYR CAPIT GE +д CYR CAPIT DE +е CYR CAPIT IE +ж CYR CAPIT ZHE +з CYR CAPIT ZE +и CYR CAPIT I +к CYR CAPIT KA +л CYR CAPIT EL +м CYR CAPIT EM +н CYR CAPIT EN +о CYR CAPIT O +п CYR CAPIT PE +Ñ€ CYR CAPIT ER +Ñ CYR CAPIT ES +Ñ‚ CYR CAPIT TE +у CYR CAPIT U +Ñ„ CYR CAPIT EF +Ñ… CYR CAPIT HA +ц CYR CAPIT TSE +ч CYR CAPIT CHE +ш CYR CAPIT SHA +щ CYR CAPIT SCHA +ÑŠ CYR CAPIT HARD SIGN +Ñ‹ CYR CAPIT YERU +ÑŒ CYR CAPIT SOFT SIGN +Ñ CYR CAPIT E +ÑŽ CYR CAPIT YU +Ñ CYR CAPIT YA +SELECT DISTINCT koi8_ru_f FROM t1; +koi8_ru_f +a +b +c +d +e +f +g +h +i +j +k +l +m +n +o +p +q +r +s +t +u +v +w +x +y +z +Á + +× +Ç +Ä +Å +£ +Ö +Ú +É +Ë +Ì +Í +Î +Ï +Ð +Ò +Ó +Ô +Õ +Æ +È +à +Þ +Û +Ý +ß +Ù +Ø +Ü +À +Ñ +SELECT DISTINCT utf8_f FROM t1; +utf8_f +a +b +c +d +e +f +g +h +i +j +k +l +m +n +o +p +q +r +s +t +u +v +w +x +y +z +а +б +в +г +д +е +ж +з +и +к +л +м +н +о +п +Ñ€ +Ñ +Ñ‚ +у +Ñ„ +Ñ… +ц +ч +ш +щ +ÑŠ +Ñ‹ +ÑŒ +Ñ +ÑŽ +Ñ +SELECT lower(koi8_ru_f) FROM t1 ORDER BY 1 DESC; +lower(koi8_ru_f) +Ñ +Ñ +À +À +Ü +Ü +Ø +Ø +Ù +Ù +ß +ß +Ý +Ý +Û +Û +Þ +Þ +à +à +È +È +Æ +Æ +Õ +Õ +Ô +Ô +Ó +Ó +Ò +Ò +Ð +Ð +Ï +Ï +Î +Î +Í +Í +Ì +Ì +Ë +Ë +É +É +Ú +Ú +Ö +Ö +£ +£ +Å +Å +Ä +Ä +Ç +Ç +× +× + + +Á +Á +z +z +y +y +x +x +w +w +v +v +u +u +t +t +s +s +r +r +q +q +p +p +o +o +n +n +m +m +l +l +k +k +j +j +i +i +h +h +g +g +f +f +e +e +d +d +c +c +b +b +a +a +SELECT lower(utf8_f) FROM t1 ORDER BY 1 DESC; +lower(utf8_f) +Ñ +Ñ +ÑŽ +ÑŽ +Ñ +Ñ +ÑŒ +ÑŒ +Ñ‹ +Ñ‹ +ÑŠ +ÑŠ +щ +щ +ш +ш +ч +ч +ц +ц +Ñ… +Ñ… +Ñ„ +Ñ„ +у +у +Ñ‚ +Ñ‚ +Ñ +Ñ +Ñ€ +Ñ€ +п +п +о +о +н +н +м +м +л +л +к +к +и +и +з +з +ж +ж +е +Ñ‘ +е +Ñ‘ +д +д +г +г +в +в +б +б +а +а +z +z +y +y +x +x +w +w +v +v +u +u +t +t +s +s +r +r +q +q +p +p +o +o +n +n +m +m +l +l +k +k +j +j +i +i +h +h +g +g +f +f +e +e +d +d +c +c +b +b +a +a +SELECT t11.comment,t12.comment +FROM t1 t11,t1 t12 WHERE CONVERT(t11.koi8_ru_f USING utf8)=t12.utf8_f +ORDER BY t11.koi8_ru_f,t11.comment,t12.comment; +comment comment +LAT CAPIT A LAT CAPIT A +LAT CAPIT A LAT SMALL A +LAT SMALL A LAT CAPIT A +LAT SMALL A LAT SMALL A +LAT CAPIT B LAT CAPIT B +LAT CAPIT B LAT SMALL B +LAT SMALL B LAT CAPIT B +LAT SMALL B LAT SMALL B +LAT CAPIT C LAT CAPIT C +LAT CAPIT C LAT SMALL C +LAT SMALL C LAT CAPIT C +LAT SMALL C LAT SMALL C +LAT CAPIT D LAT CAPIT D +LAT CAPIT D LAT SMALL D +LAT SMALL D LAT CAPIT D +LAT SMALL D LAT SMALL D +LAT CAPIT E LAT CAPIT E +LAT CAPIT E LAT SMALL E +LAT SMALL E LAT CAPIT E +LAT SMALL E LAT SMALL E +LAT CAPIT F LAT CAPIT F +LAT CAPIT F LAT SMALL F +LAT SMALL F LAT CAPIT F +LAT SMALL F LAT SMALL F +LAT CAPIT G LAT CAPIT G +LAT CAPIT G LAT SMALL G +LAT SMALL G LAT CAPIT G +LAT SMALL G LAT SMALL G +LAT CAPIT H LAT CAPIT H +LAT CAPIT H LAT SMALL H +LAT SMALL H LAT CAPIT H +LAT SMALL H LAT SMALL H +LAT CAPIT I LAT CAPIT I +LAT CAPIT I LAT SMALL I +LAT SMALL I LAT CAPIT I +LAT SMALL I LAT SMALL I +LAT CAPIT J LAT CAPIT J +LAT CAPIT J LAT SMALL J +LAT SMALL J LAT CAPIT J +LAT SMALL J LAT SMALL J +LAT CAPIT K LAT CAPIT K +LAT CAPIT K LAT SMALL K +LAT SMALL K LAT CAPIT K +LAT SMALL K LAT SMALL K +LAT CAPIT L LAT CAPIT L +LAT CAPIT L LAT SMALL L +LAT SMALL L LAT CAPIT L +LAT SMALL L LAT SMALL L +LAT CAPIT M LAT CAPIT M +LAT CAPIT M LAT SMALL M +LAT SMALL M LAT CAPIT M +LAT SMALL M LAT SMALL M +LAT CAPIT N LAT CAPIT N +LAT CAPIT N LAT SMALL N +LAT SMALL N LAT CAPIT N +LAT SMALL N LAT SMALL N +LAT CAPIT O LAT CAPIT O +LAT CAPIT O LAT SMALL O +LAT SMALL O LAT CAPIT O +LAT SMALL O LAT SMALL O +LAT CAPIT P LAT CAPIT P +LAT CAPIT P LAT SMALL P +LAT SMALL P LAT CAPIT P +LAT SMALL P LAT SMALL P +LAT CAPIT Q LAT CAPIT Q +LAT CAPIT Q LAT SMALL Q +LAT SMALL Q LAT CAPIT Q +LAT SMALL Q LAT SMALL Q +LAT CAPIT R LAT CAPIT R +LAT CAPIT R LAT SMALL R +LAT SMALL R LAT CAPIT R +LAT SMALL R LAT SMALL R +LAT CAPIT S LAT CAPIT S +LAT CAPIT S LAT SMALL S +LAT SMALL S LAT CAPIT S +LAT SMALL S LAT SMALL S +LAT CAPIT T LAT CAPIT T +LAT CAPIT T LAT SMALL T +LAT SMALL T LAT CAPIT T +LAT SMALL T LAT SMALL T +LAT CAPIT U LAT CAPIT U +LAT CAPIT U LAT SMALL U +LAT SMALL U LAT CAPIT U +LAT SMALL U LAT SMALL U +LAT CAPIT V LAT CAPIT V +LAT CAPIT V LAT SMALL V +LAT SMALL V LAT CAPIT V +LAT SMALL V LAT SMALL V +LAT CAPIT W LAT CAPIT W +LAT CAPIT W LAT SMALL W +LAT SMALL W LAT CAPIT W +LAT SMALL W LAT SMALL W +LAT CAPIT X LAT CAPIT X +LAT CAPIT X LAT SMALL X +LAT SMALL X LAT CAPIT X +LAT SMALL X LAT SMALL X +LAT CAPIT Y LAT CAPIT Y +LAT CAPIT Y LAT SMALL Y +LAT SMALL Y LAT CAPIT Y +LAT SMALL Y LAT SMALL Y +LAT CAPIT Z LAT CAPIT Z +LAT CAPIT Z LAT SMALL Z +LAT SMALL Z LAT CAPIT Z +LAT SMALL Z LAT SMALL Z +CYR CAPIT A CYR CAPIT A +CYR CAPIT A CYR SMALL A +CYR SMALL A CYR CAPIT A +CYR SMALL A CYR SMALL A +CYR CAPIT BE CYR CAPIT BE +CYR CAPIT BE CYR SMALL BE +CYR SMALL BE CYR CAPIT BE +CYR SMALL BE CYR SMALL BE +CYR CAPIT VE CYR CAPIT VE +CYR CAPIT VE CYR SMALL VE +CYR SMALL VE CYR CAPIT VE +CYR SMALL VE CYR SMALL VE +CYR CAPIT GE CYR CAPIT GE +CYR CAPIT GE CYR SMALL GE +CYR SMALL GE CYR CAPIT GE +CYR SMALL GE CYR SMALL GE +CYR CAPIT DE CYR CAPIT DE +CYR CAPIT DE CYR SMALL DE +CYR SMALL DE CYR CAPIT DE +CYR SMALL DE CYR SMALL DE +CYR CAPIT IE CYR CAPIT IE +CYR CAPIT IE CYR CAPIT IO +CYR CAPIT IE CYR SMALL IE +CYR CAPIT IE CYR SMALL IO +CYR SMALL IE CYR CAPIT IE +CYR SMALL IE CYR CAPIT IO +CYR SMALL IE CYR SMALL IE +CYR SMALL IE CYR SMALL IO +CYR CAPIT IO CYR CAPIT IE +CYR CAPIT IO CYR CAPIT IO +CYR CAPIT IO CYR SMALL IE +CYR CAPIT IO CYR SMALL IO +CYR SMALL IO CYR CAPIT IE +CYR SMALL IO CYR CAPIT IO +CYR SMALL IO CYR SMALL IE +CYR SMALL IO CYR SMALL IO +CYR CAPIT ZHE CYR CAPIT ZHE +CYR CAPIT ZHE CYR SMALL ZHE +CYR SMALL ZHE CYR CAPIT ZHE +CYR SMALL ZHE CYR SMALL ZHE +CYR CAPIT ZE CYR CAPIT ZE +CYR CAPIT ZE CYR SMALL ZE +CYR SMALL ZE CYR CAPIT ZE +CYR SMALL ZE CYR SMALL ZE +CYR CAPIT I CYR CAPIT I +CYR CAPIT I CYR SMALL I +CYR SMALL I CYR CAPIT I +CYR SMALL I CYR SMALL I +CYR CAPIT KA CYR CAPIT KA +CYR CAPIT KA CYR SMALL KA +CYR SMALL KA CYR CAPIT KA +CYR SMALL KA CYR SMALL KA +CYR CAPIT EL CYR CAPIT EL +CYR CAPIT EL CYR SMALL EL +CYR SMALL EL CYR CAPIT EL +CYR SMALL EL CYR SMALL EL +CYR CAPIT EM CYR CAPIT EM +CYR CAPIT EM CYR SMALL EM +CYR SMALL EM CYR CAPIT EM +CYR SMALL EM CYR SMALL EM +CYR CAPIT EN CYR CAPIT EN +CYR CAPIT EN CYR SMALL EN +CYR SMALL EN CYR CAPIT EN +CYR SMALL EN CYR SMALL EN +CYR CAPIT O CYR CAPIT O +CYR CAPIT O CYR SMALL O +CYR SMALL O CYR CAPIT O +CYR SMALL O CYR SMALL O +CYR CAPIT PE CYR CAPIT PE +CYR CAPIT PE CYR SMALL PE +CYR SMALL PE CYR CAPIT PE +CYR SMALL PE CYR SMALL PE +CYR CAPIT ER CYR CAPIT ER +CYR CAPIT ER CYR SMALL ER +CYR SMALL ER CYR CAPIT ER +CYR SMALL ER CYR SMALL ER +CYR CAPIT ES CYR CAPIT ES +CYR CAPIT ES CYR SMALL ES +CYR SMALL ES CYR CAPIT ES +CYR SMALL ES CYR SMALL ES +CYR CAPIT TE CYR CAPIT TE +CYR CAPIT TE CYR SMALL TE +CYR SMALL TE CYR CAPIT TE +CYR SMALL TE CYR SMALL TE +CYR CAPIT U CYR CAPIT U +CYR CAPIT U CYR SMALL U +CYR SMALL U CYR CAPIT U +CYR SMALL U CYR SMALL U +CYR CAPIT EF CYR CAPIT EF +CYR CAPIT EF CYR SMALL EF +CYR SMALL EF CYR CAPIT EF +CYR SMALL EF CYR SMALL EF +CYR CAPIT HA CYR CAPIT HA +CYR CAPIT HA CYR SMALL HA +CYR SMALL HA CYR CAPIT HA +CYR SMALL HA CYR SMALL HA +CYR CAPIT TSE CYR CAPIT TSE +CYR CAPIT TSE CYR SMALL TSE +CYR SMALL TSE CYR CAPIT TSE +CYR SMALL TSE CYR SMALL TSE +CYR CAPIT CHE CYR CAPIT CHE +CYR CAPIT CHE CYR SMALL CHE +CYR SMALL CHE CYR CAPIT CHE +CYR SMALL CHE CYR SMALL CHE +CYR CAPIT SHA CYR CAPIT SHA +CYR CAPIT SHA CYR SMALL SHA +CYR SMALL SHA CYR CAPIT SHA +CYR SMALL SHA CYR SMALL SHA +CYR CAPIT SCHA CYR CAPIT SCHA +CYR CAPIT SCHA CYR SMALL SCHA +CYR SMALL SCHA CYR CAPIT SCHA +CYR SMALL SCHA CYR SMALL SCHA +CYR CAPIT HARD SIGN CYR CAPIT HARD SIGN +CYR CAPIT HARD SIGN CYR SMALL HARD SIGN +CYR SMALL HARD SIGN CYR CAPIT HARD SIGN +CYR SMALL HARD SIGN CYR SMALL HARD SIGN +CYR CAPIT YERU CYR CAPIT YERU +CYR CAPIT YERU CYR SMALL YERU +CYR SMALL YERU CYR CAPIT YERU +CYR SMALL YERU CYR SMALL YERU +CYR CAPIT SOFT SIGN CYR CAPIT SOFT SIGN +CYR CAPIT SOFT SIGN CYR SMALL SOFT SIGN +CYR SMALL SOFT SIGN CYR CAPIT SOFT SIGN +CYR SMALL SOFT SIGN CYR SMALL SOFT SIGN +CYR CAPIT E CYR CAPIT E +CYR CAPIT E CYR SMALL E +CYR SMALL E CYR CAPIT E +CYR SMALL E CYR SMALL E +CYR CAPIT YU CYR CAPIT YU +CYR CAPIT YU CYR SMALL YU +CYR SMALL YU CYR CAPIT YU +CYR SMALL YU CYR SMALL YU +CYR CAPIT YA CYR CAPIT YA +CYR CAPIT YA CYR SMALL YA +CYR SMALL YA CYR CAPIT YA +CYR SMALL YA CYR SMALL YA +SELECT t11.comment,t12.comment +FROM t1 t11,t1 t12 +WHERE t11.koi8_ru_f=CONVERT(t12.utf8_f USING koi8_ru) +ORDER BY t12.utf8_f,t11.comment,t12.comment; +comment comment +LAT CAPIT A LAT CAPIT A +LAT CAPIT A LAT SMALL A +LAT SMALL A LAT CAPIT A +LAT SMALL A LAT SMALL A +LAT CAPIT B LAT CAPIT B +LAT CAPIT B LAT SMALL B +LAT SMALL B LAT CAPIT B +LAT SMALL B LAT SMALL B +LAT CAPIT C LAT CAPIT C +LAT CAPIT C LAT SMALL C +LAT SMALL C LAT CAPIT C +LAT SMALL C LAT SMALL C +LAT CAPIT D LAT CAPIT D +LAT CAPIT D LAT SMALL D +LAT SMALL D LAT CAPIT D +LAT SMALL D LAT SMALL D +LAT CAPIT E LAT CAPIT E +LAT CAPIT E LAT SMALL E +LAT SMALL E LAT CAPIT E +LAT SMALL E LAT SMALL E +LAT CAPIT F LAT CAPIT F +LAT CAPIT F LAT SMALL F +LAT SMALL F LAT CAPIT F +LAT SMALL F LAT SMALL F +LAT CAPIT G LAT CAPIT G +LAT CAPIT G LAT SMALL G +LAT SMALL G LAT CAPIT G +LAT SMALL G LAT SMALL G +LAT CAPIT H LAT CAPIT H +LAT CAPIT H LAT SMALL H +LAT SMALL H LAT CAPIT H +LAT SMALL H LAT SMALL H +LAT CAPIT I LAT CAPIT I +LAT CAPIT I LAT SMALL I +LAT SMALL I LAT CAPIT I +LAT SMALL I LAT SMALL I +LAT CAPIT J LAT CAPIT J +LAT CAPIT J LAT SMALL J +LAT SMALL J LAT CAPIT J +LAT SMALL J LAT SMALL J +LAT CAPIT K LAT CAPIT K +LAT CAPIT K LAT SMALL K +LAT SMALL K LAT CAPIT K +LAT SMALL K LAT SMALL K +LAT CAPIT L LAT CAPIT L +LAT CAPIT L LAT SMALL L +LAT SMALL L LAT CAPIT L +LAT SMALL L LAT SMALL L +LAT CAPIT M LAT CAPIT M +LAT CAPIT M LAT SMALL M +LAT SMALL M LAT CAPIT M +LAT SMALL M LAT SMALL M +LAT CAPIT N LAT CAPIT N +LAT CAPIT N LAT SMALL N +LAT SMALL N LAT CAPIT N +LAT SMALL N LAT SMALL N +LAT CAPIT O LAT CAPIT O +LAT CAPIT O LAT SMALL O +LAT SMALL O LAT CAPIT O +LAT SMALL O LAT SMALL O +LAT CAPIT P LAT CAPIT P +LAT CAPIT P LAT SMALL P +LAT SMALL P LAT CAPIT P +LAT SMALL P LAT SMALL P +LAT CAPIT Q LAT CAPIT Q +LAT CAPIT Q LAT SMALL Q +LAT SMALL Q LAT CAPIT Q +LAT SMALL Q LAT SMALL Q +LAT CAPIT R LAT CAPIT R +LAT CAPIT R LAT SMALL R +LAT SMALL R LAT CAPIT R +LAT SMALL R LAT SMALL R +LAT CAPIT S LAT CAPIT S +LAT CAPIT S LAT SMALL S +LAT SMALL S LAT CAPIT S +LAT SMALL S LAT SMALL S +LAT CAPIT T LAT CAPIT T +LAT CAPIT T LAT SMALL T +LAT SMALL T LAT CAPIT T +LAT SMALL T LAT SMALL T +LAT CAPIT U LAT CAPIT U +LAT CAPIT U LAT SMALL U +LAT SMALL U LAT CAPIT U +LAT SMALL U LAT SMALL U +LAT CAPIT V LAT CAPIT V +LAT CAPIT V LAT SMALL V +LAT SMALL V LAT CAPIT V +LAT SMALL V LAT SMALL V +LAT CAPIT W LAT CAPIT W +LAT CAPIT W LAT SMALL W +LAT SMALL W LAT CAPIT W +LAT SMALL W LAT SMALL W +LAT CAPIT X LAT CAPIT X +LAT CAPIT X LAT SMALL X +LAT SMALL X LAT CAPIT X +LAT SMALL X LAT SMALL X +LAT CAPIT Y LAT CAPIT Y +LAT CAPIT Y LAT SMALL Y +LAT SMALL Y LAT CAPIT Y +LAT SMALL Y LAT SMALL Y +LAT CAPIT Z LAT CAPIT Z +LAT CAPIT Z LAT SMALL Z +LAT SMALL Z LAT CAPIT Z +LAT SMALL Z LAT SMALL Z +CYR CAPIT A CYR CAPIT A +CYR CAPIT A CYR SMALL A +CYR SMALL A CYR CAPIT A +CYR SMALL A CYR SMALL A +CYR CAPIT BE CYR CAPIT BE +CYR CAPIT BE CYR SMALL BE +CYR SMALL BE CYR CAPIT BE +CYR SMALL BE CYR SMALL BE +CYR CAPIT VE CYR CAPIT VE +CYR CAPIT VE CYR SMALL VE +CYR SMALL VE CYR CAPIT VE +CYR SMALL VE CYR SMALL VE +CYR CAPIT GE CYR CAPIT GE +CYR CAPIT GE CYR SMALL GE +CYR SMALL GE CYR CAPIT GE +CYR SMALL GE CYR SMALL GE +CYR CAPIT DE CYR CAPIT DE +CYR CAPIT DE CYR SMALL DE +CYR SMALL DE CYR CAPIT DE +CYR SMALL DE CYR SMALL DE +CYR CAPIT IE CYR CAPIT IE +CYR CAPIT IE CYR SMALL IE +CYR CAPIT IO CYR CAPIT IO +CYR CAPIT IO CYR SMALL IO +CYR SMALL IE CYR CAPIT IE +CYR SMALL IE CYR SMALL IE +CYR SMALL IO CYR CAPIT IO +CYR SMALL IO CYR SMALL IO +CYR CAPIT ZHE CYR CAPIT ZHE +CYR CAPIT ZHE CYR SMALL ZHE +CYR SMALL ZHE CYR CAPIT ZHE +CYR SMALL ZHE CYR SMALL ZHE +CYR CAPIT ZE CYR CAPIT ZE +CYR CAPIT ZE CYR SMALL ZE +CYR SMALL ZE CYR CAPIT ZE +CYR SMALL ZE CYR SMALL ZE +CYR CAPIT I CYR CAPIT I +CYR CAPIT I CYR SMALL I +CYR SMALL I CYR CAPIT I +CYR SMALL I CYR SMALL I +CYR CAPIT KA CYR CAPIT KA +CYR CAPIT KA CYR SMALL KA +CYR SMALL KA CYR CAPIT KA +CYR SMALL KA CYR SMALL KA +CYR CAPIT EL CYR CAPIT EL +CYR CAPIT EL CYR SMALL EL +CYR SMALL EL CYR CAPIT EL +CYR SMALL EL CYR SMALL EL +CYR CAPIT EM CYR CAPIT EM +CYR CAPIT EM CYR SMALL EM +CYR SMALL EM CYR CAPIT EM +CYR SMALL EM CYR SMALL EM +CYR CAPIT EN CYR CAPIT EN +CYR CAPIT EN CYR SMALL EN +CYR SMALL EN CYR CAPIT EN +CYR SMALL EN CYR SMALL EN +CYR CAPIT O CYR CAPIT O +CYR CAPIT O CYR SMALL O +CYR SMALL O CYR CAPIT O +CYR SMALL O CYR SMALL O +CYR CAPIT PE CYR CAPIT PE +CYR CAPIT PE CYR SMALL PE +CYR SMALL PE CYR CAPIT PE +CYR SMALL PE CYR SMALL PE +CYR CAPIT ER CYR CAPIT ER +CYR CAPIT ER CYR SMALL ER +CYR SMALL ER CYR CAPIT ER +CYR SMALL ER CYR SMALL ER +CYR CAPIT ES CYR CAPIT ES +CYR CAPIT ES CYR SMALL ES +CYR SMALL ES CYR CAPIT ES +CYR SMALL ES CYR SMALL ES +CYR CAPIT TE CYR CAPIT TE +CYR CAPIT TE CYR SMALL TE +CYR SMALL TE CYR CAPIT TE +CYR SMALL TE CYR SMALL TE +CYR CAPIT U CYR CAPIT U +CYR CAPIT U CYR SMALL U +CYR SMALL U CYR CAPIT U +CYR SMALL U CYR SMALL U +CYR CAPIT EF CYR CAPIT EF +CYR CAPIT EF CYR SMALL EF +CYR SMALL EF CYR CAPIT EF +CYR SMALL EF CYR SMALL EF +CYR CAPIT HA CYR CAPIT HA +CYR CAPIT HA CYR SMALL HA +CYR SMALL HA CYR CAPIT HA +CYR SMALL HA CYR SMALL HA +CYR CAPIT TSE CYR CAPIT TSE +CYR CAPIT TSE CYR SMALL TSE +CYR SMALL TSE CYR CAPIT TSE +CYR SMALL TSE CYR SMALL TSE +CYR CAPIT CHE CYR CAPIT CHE +CYR CAPIT CHE CYR SMALL CHE +CYR SMALL CHE CYR CAPIT CHE +CYR SMALL CHE CYR SMALL CHE +CYR CAPIT SHA CYR CAPIT SHA +CYR CAPIT SHA CYR SMALL SHA +CYR SMALL SHA CYR CAPIT SHA +CYR SMALL SHA CYR SMALL SHA +CYR CAPIT SCHA CYR CAPIT SCHA +CYR CAPIT SCHA CYR SMALL SCHA +CYR SMALL SCHA CYR CAPIT SCHA +CYR SMALL SCHA CYR SMALL SCHA +CYR CAPIT HARD SIGN CYR CAPIT HARD SIGN +CYR CAPIT HARD SIGN CYR SMALL HARD SIGN +CYR SMALL HARD SIGN CYR CAPIT HARD SIGN +CYR SMALL HARD SIGN CYR SMALL HARD SIGN +CYR CAPIT YERU CYR CAPIT YERU +CYR CAPIT YERU CYR SMALL YERU +CYR SMALL YERU CYR CAPIT YERU +CYR SMALL YERU CYR SMALL YERU +CYR CAPIT SOFT SIGN CYR CAPIT SOFT SIGN +CYR CAPIT SOFT SIGN CYR SMALL SOFT SIGN +CYR SMALL SOFT SIGN CYR CAPIT SOFT SIGN +CYR SMALL SOFT SIGN CYR SMALL SOFT SIGN +CYR CAPIT E CYR CAPIT E +CYR CAPIT E CYR SMALL E +CYR SMALL E CYR CAPIT E +CYR SMALL E CYR SMALL E +CYR CAPIT YU CYR CAPIT YU +CYR CAPIT YU CYR SMALL YU +CYR SMALL YU CYR CAPIT YU +CYR SMALL YU CYR SMALL YU +CYR CAPIT YA CYR CAPIT YA +CYR CAPIT YA CYR SMALL YA +CYR SMALL YA CYR CAPIT YA +CYR SMALL YA CYR SMALL YA +ALTER TABLE t1 ADD ucs2_f CHAR(32) CHARACTER SET ucs2 NOT NULL; +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0391,'GREEK CAPIT ALPHA'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0392,'GREEK CAPIT BETA'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0393,'GREEK CAPIT GAMMA'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0394,'GREEK CAPIT DELTA'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0395,'GREEK CAPIT EPSILON'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B1,'GREEK SMALL ALPHA'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B2,'GREEK SMALL BETA'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B3,'GREEK SMALL GAMMA'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B4,'GREEK SMALL DELTA'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x03B5,'GREEK SMALL EPSILON'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0531,'ARMENIAN CAPIT AYB'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0532,'ARMENIAN CAPIT BEN'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0533,'ARMENIAN CAPIT GIM'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0534,'ARMENIAN CAPIT DA'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0535,'ARMENIAN CAPIT ECH'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0536,'ARMENIAN CAPIT ZA'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0561,'ARMENIAN SMALL YAB'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0562,'ARMENIAN SMALL BEN'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0563,'ARMENIAN SMALL GIM'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0564,'ARMENIAN SMALL DA'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0565,'ARMENIAN SMALL ECH'); +INSERT INTO t1 (ucs2_f,comment) VALUES (0x0566,'ARMENIAN SMALL ZA'); +ALTER TABLE t1 ADD armscii8_f CHAR(32) CHARACTER SET armscii8 NOT NULL; +ALTER TABLE t1 ADD greek_f CHAR(32) CHARACTER SET greek NOT NULL; +UPDATE t1 SET greek_f=CONVERT(ucs2_f USING greek) WHERE comment LIKE 'GRE%'; +UPDATE t1 SET armscii8_f=CONVERT(ucs2_f USING armscii8) WHERE comment LIKE 'ARM%'; +UPDATE t1 SET utf8_f=CONVERT(ucs2_f USING utf8) WHERE utf8_f=''; +UPDATE t1 SET ucs2_f=CONVERT(utf8_f USING ucs2) WHERE ucs2_f=''; +SELECT min(comment),count(*) FROM t1 GROUP BY ucs2_f; +min(comment) count(*) +LAT CAPIT A 2 +LAT CAPIT B 2 +LAT CAPIT C 2 +LAT CAPIT D 2 +LAT CAPIT E 2 +LAT CAPIT F 2 +LAT CAPIT G 2 +LAT CAPIT H 2 +LAT CAPIT I 2 +LAT CAPIT J 2 +LAT CAPIT K 2 +LAT CAPIT L 2 +LAT CAPIT M 2 +LAT CAPIT N 2 +LAT CAPIT O 2 +LAT CAPIT P 2 +LAT CAPIT Q 2 +LAT CAPIT R 2 +LAT CAPIT S 2 +LAT CAPIT T 2 +LAT CAPIT U 2 +LAT CAPIT V 2 +LAT CAPIT W 2 +LAT CAPIT X 2 +LAT CAPIT Y 2 +LAT CAPIT Z 2 +GREEK CAPIT ALPHA 2 +GREEK CAPIT BETA 2 +GREEK CAPIT GAMMA 2 +GREEK CAPIT DELTA 2 +GREEK CAPIT EPSILON 2 +CYR CAPIT A 2 +CYR CAPIT BE 2 +CYR CAPIT VE 2 +CYR CAPIT GE 2 +CYR CAPIT DE 2 +CYR CAPIT IE 4 +CYR CAPIT ZHE 2 +CYR CAPIT ZE 2 +CYR CAPIT I 2 +CYR CAPIT KA 2 +CYR CAPIT EL 2 +CYR CAPIT EM 2 +CYR CAPIT EN 2 +CYR CAPIT O 2 +CYR CAPIT PE 2 +CYR CAPIT ER 2 +CYR CAPIT ES 2 +CYR CAPIT TE 2 +CYR CAPIT U 2 +CYR CAPIT EF 2 +CYR CAPIT HA 2 +CYR CAPIT TSE 2 +CYR CAPIT CHE 2 +CYR CAPIT SHA 2 +CYR CAPIT SCHA 2 +CYR CAPIT HARD SIGN 2 +CYR CAPIT YERU 2 +CYR CAPIT SOFT SIGN 2 +CYR CAPIT E 2 +CYR CAPIT YU 2 +CYR CAPIT YA 2 +ARMENIAN CAPIT AYB 2 +ARMENIAN CAPIT BEN 2 +ARMENIAN CAPIT GIM 2 +ARMENIAN CAPIT DA 2 +ARMENIAN CAPIT ECH 2 +ARMENIAN CAPIT ZA 2 +DROP TABLE t1; diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result new file mode 100644 index 00000000000..e05be96c6b7 --- /dev/null +++ b/mysql-test/r/derived.result @@ -0,0 +1,20 @@ +drop table if exists t1,t2,t3; +CREATE TABLE t1 (a int not null, b char (10) not null); +insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); +CREATE TABLE t2 (a int not null, b char (10) not null); +insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); +select t1.a,t3.y from t1,(select a as y from t2 where b='c') as t3 where t1.a = t3.y; +a y +3 3 +3 3 +select t1.a,t3.a from t1,(select * from t2 where b='c') as t3 where t1.a = t3.a; +a a +3 3 +3 3 +CREATE TABLE t3 (a int not null, b char (10) not null); +insert into t3 values (3,'f'),(4,'y'),(5,'z'),(6,'c'); +select t1.a,t4.y from t1,(select t2.a as y from t2,(select t3.b from t3 where t3.a>3) as t5 where t2.b=t5.b) as t4 where t1.a = t4.y; +a y +3 3 +3 3 +drop table if exists t1.t2,t3; diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index b312f5fbc3d..e71190130ed 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -138,7 +138,7 @@ show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `ticket` int(11) default NULL, - `inhalt` text, + `inhalt` text character set latin1, KEY `tig` (`ticket`), FULLTEXT KEY `tix` (`inhalt`) ) TYPE=MyISAM diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result index 13f452e26d8..eb3bae0fea2 100644 --- a/mysql-test/r/heap.result +++ b/mysql-test/r/heap.result @@ -68,7 +68,7 @@ explain select * from t1 where a in (869751,736494,226312,802616); table type possible_keys key key_len ref rows Extra t1 range uniq_id uniq_id 4 NULL 4 where used; Using index drop table t1; -create table t1 (x int not null, y int not null, key x(x), unique y(y)) +create table t1 (x int not null, y int not null, key x (x), unique y (y)) type=heap; insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6); select * from t1 where x=1; diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result new file mode 100644 index 00000000000..c3080389999 --- /dev/null +++ b/mysql-test/r/heap_btree.result @@ -0,0 +1,218 @@ +drop table if exists t1; +create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100; +insert into t1 values(1,1),(2,2),(3,3),(4,4); +delete from t1 where a=1 or a=0; +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 a A NULL NULL NULL BTREE +select * from t1; +a b +2 2 +3 3 +4 4 +select * from t1 where a=4; +a b +4 4 +update t1 set b=5 where a=4; +update t1 set b=b+1 where a>=3; +replace t1 values (3,3); +select * from t1; +a b +2 2 +3 3 +4 6 +alter table t1 add c int not null, add key using BTREE (c,a); +drop table t1; +create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps"; +insert into t1 values(1,1),(2,2),(3,3),(4,4); +delete from t1 where a > 0; +select * from t1; +a b +drop table t1; +create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps"; +insert into t1 values(1,1),(2,2),(3,3),(4,4); +alter table t1 modify a int not null auto_increment, type=myisam, comment="new myisam table"; +select * from t1; +a b +1 1 +2 2 +3 3 +4 4 +drop table t1; +create table t1 (a int not null) type=heap; +insert into t1 values (869751),(736494),(226312),(802616); +select * from t1 where a > 736494; +a +869751 +802616 +alter table t1 add unique uniq_id using BTREE (a); +select * from t1 where a > 736494; +a +802616 +869751 +select * from t1 where a = 736494; +a +736494 +select * from t1 where a=869751 or a=736494; +a +736494 +869751 +select * from t1 where a in (869751,736494,226312,802616); +a +226312 +736494 +802616 +869751 +alter table t1 type=myisam; +explain select * from t1 where a in (869751,736494,226312,802616); +table type possible_keys key key_len ref rows Extra +t1 range uniq_id uniq_id 4 NULL 4 where used; Using index +drop table t1; +create table t1 (x int not null, y int not null, key x using BTREE (x), unique y using BTREE (y)) +type=heap; +insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6); +select * from t1 where x=1; +x y +1 1 +1 3 +select * from t1,t1 as t2 where t1.x=t2.y; +x y x y +1 1 1 1 +2 2 2 2 +1 3 1 1 +2 4 2 2 +2 5 2 2 +2 6 2 2 +explain select * from t1,t1 as t2 where t1.x=t2.y; +table type possible_keys key key_len ref rows Extra +t1 ALL x NULL NULL NULL 6 +t2 eq_ref y y 4 t1.x 1 +drop table t1; +create table t1 (a int) type=heap; +insert into t1 values(1); +select max(a) from t1; +max(a) +1 +drop table t1; +CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key using BTREE (a,b), key using BTREE (b) ) TYPE=HEAP; +insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); +select * from t1 where a=1; +a b +1 1 +1 2 +1 3 +1 4 +1 5 +1 6 +insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); +select * from t1 where a=1; +a b +1 1 +1 1 +1 2 +1 2 +1 3 +1 3 +1 4 +1 4 +1 5 +1 5 +1 6 +1 6 +explain select * from tx where a=x order by a,b; +table type possible_keys key key_len ref rows Extra +tx ref a a x const x where used +explain select * from tx where a=x order by b; +table type possible_keys key key_len ref rows Extra +tx ref a a x const x where used +select * from t1 where b=1; +a b +1 1 +1 1 +explain select * from tx where b=x; +table type possible_keys key key_len ref rows Extra +tx ref b b x const x where used +drop table t1; +create table t1 (id int unsigned not null, primary key using BTREE (id)) type=HEAP; +insert into t1 values(1); +select max(id) from t1; +max(id) +1 +insert into t1 values(2); +select max(id) from t1; +max(id) +2 +replace into t1 values(1); +drop table t1; +create table t1 (n int) type=heap; +drop table t1; +create table t1 (n int) type=heap; +drop table if exists t1; +CREATE table t1(f1 int not null,f2 char(20) not +null,index(f2)) type=heap; +INSERT into t1 set f1=12,f2="bill"; +INSERT into t1 set f1=13,f2="bill"; +INSERT into t1 set f1=14,f2="bill"; +INSERT into t1 set f1=15,f2="bill"; +INSERT into t1 set f1=16,f2="ted"; +INSERT into t1 set f1=12,f2="ted"; +INSERT into t1 set f1=12,f2="ted"; +INSERT into t1 set f1=12,f2="ted"; +INSERT into t1 set f1=12,f2="ted"; +delete from t1 where f2="bill"; +select * from t1; +f1 f2 +16 ted +12 ted +12 ted +12 ted +12 ted +drop table t1; +create table t1 (btn char(10) not null, key using BTREE (btn)) type=heap; +insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i"); +explain select * from t1 where btn like "q%"; +table type possible_keys key key_len ref rows Extra +t1 ALL btn NULL NULL NULL 14 where used +select * from t1 where btn like "q%"; +btn +alter table t1 add column new_col char(1) not null, add key using BTREE (btn,new_col), drop key btn; +update t1 set new_col=btn; +explain select * from t1 where btn="a"; +table type possible_keys key key_len ref rows Extra +t1 ref btn btn 10 const 1 where used +explain select * from t1 where btn="a" and new_col="a"; +table type possible_keys key key_len ref rows Extra +t1 ref btn btn 11 const,const 1 where used +drop table t1; +CREATE TABLE t1 ( +a int default NULL, +b int default NULL, +KEY a using BTREE (a), +UNIQUE b using BTREE (b) +) type=heap; +INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3); +SELECT * FROM t1 WHERE a=NULL; +a b +explain SELECT * FROM t1 WHERE a IS NULL; +table type possible_keys key key_len ref rows Extra +t1 ref a a 5 const 1 where used +SELECT * FROM t1 WHERE a<=>NULL; +a b +NULL 99 +SELECT * FROM t1 WHERE b=NULL; +a b +explain SELECT * FROM t1 WHERE b IS NULL; +table type possible_keys key key_len ref rows Extra +t1 ref b b 5 const 1 where used +SELECT * FROM t1 WHERE b<=>NULL; +a b +99 NULL +INSERT INTO t1 VALUES (1,3); +Duplicate entry '3' for key 1 +DROP TABLE t1; +CREATE TABLE t1 (a int not null, primary key using BTREE (a)) type=heap; +INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); +DELETE from t1 where a < 100; +SELECT * from t1; +a +DROP TABLE t1; diff --git a/mysql-test/r/heap_hash.result b/mysql-test/r/heap_hash.result new file mode 100644 index 00000000000..9b7f2cca6bc --- /dev/null +++ b/mysql-test/r/heap_hash.result @@ -0,0 +1,205 @@ +drop table if exists t1; +create table t1 (a int not null,b int not null, primary key using HASH (a)) type=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100; +insert into t1 values(1,1),(2,2),(3,3),(4,4); +delete from t1 where a=1 or a=0; +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 a NULL NULL NULL NULL HASH +select * from t1; +a b +2 2 +3 3 +4 4 +select * from t1 where a=4; +a b +4 4 +update t1 set b=5 where a=4; +update t1 set b=b+1 where a>=3; +replace t1 values (3,3); +select * from t1; +a b +2 2 +3 3 +4 6 +alter table t1 add c int not null, add key using HASH (c,a); +drop table t1; +create table t1 (a int not null,b int not null, primary key using HASH (a)) type=heap comment="testing heaps"; +insert into t1 values(1,1),(2,2),(3,3),(4,4); +delete from t1 where a > 0; +select * from t1; +a b +drop table t1; +create table t1 (a int not null,b int not null, primary key using HASH (a)) type=heap comment="testing heaps"; +insert into t1 values(1,1),(2,2),(3,3),(4,4); +alter table t1 modify a int not null auto_increment, type=myisam, comment="new myisam table"; +select * from t1; +a b +1 1 +2 2 +3 3 +4 4 +drop table t1; +create table t1 (a int not null) type=heap; +insert into t1 values (869751),(736494),(226312),(802616); +select * from t1 where a > 736494; +a +869751 +802616 +alter table t1 add unique uniq_id using HASH (a); +select * from t1 where a > 736494; +a +869751 +802616 +select * from t1 where a = 736494; +a +736494 +select * from t1 where a=869751 or a=736494; +a +736494 +869751 +select * from t1 where a in (869751,736494,226312,802616); +a +226312 +736494 +802616 +869751 +alter table t1 type=myisam; +explain select * from t1 where a in (869751,736494,226312,802616); +table type possible_keys key key_len ref rows Extra +t1 range uniq_id uniq_id 4 NULL 4 where used; Using index +drop table t1; +create table t1 (x int not null, y int not null, key x using HASH (x), unique y using HASH (y)) +type=heap; +insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6); +select * from t1 where x=1; +x y +1 3 +1 1 +select * from t1,t1 as t2 where t1.x=t2.y; +x y x y +1 1 1 1 +2 2 2 2 +1 3 1 1 +2 4 2 2 +2 5 2 2 +2 6 2 2 +explain select * from t1,t1 as t2 where t1.x=t2.y; +table type possible_keys key key_len ref rows Extra +t1 ALL x NULL NULL NULL 6 +t2 eq_ref y y 4 t1.x 1 +drop table t1; +create table t1 (a int) type=heap; +insert into t1 values(1); +select max(a) from t1; +max(a) +1 +drop table t1; +CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key using HASH (a), key using HASH (b) ) TYPE=HEAP; +insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); +select * from t1 where a=1; +a b +1 6 +1 5 +1 4 +1 3 +1 2 +1 1 +insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); +select * from t1 where a=1; +a b +1 6 +1 5 +1 4 +1 3 +1 2 +1 1 +1 6 +1 5 +1 4 +1 3 +1 2 +1 1 +drop table t1; +create table t1 (id int unsigned not null, primary key using HASH (id)) type=HEAP; +insert into t1 values(1); +select max(id) from t1; +max(id) +1 +insert into t1 values(2); +select max(id) from t1; +max(id) +2 +replace into t1 values(1); +drop table t1; +create table t1 (n int) type=heap; +drop table t1; +create table t1 (n int) type=heap; +drop table if exists t1; +CREATE table t1(f1 int not null,f2 char(20) not +null,index(f2)) type=heap; +INSERT into t1 set f1=12,f2="bill"; +INSERT into t1 set f1=13,f2="bill"; +INSERT into t1 set f1=14,f2="bill"; +INSERT into t1 set f1=15,f2="bill"; +INSERT into t1 set f1=16,f2="ted"; +INSERT into t1 set f1=12,f2="ted"; +INSERT into t1 set f1=12,f2="ted"; +INSERT into t1 set f1=12,f2="ted"; +INSERT into t1 set f1=12,f2="ted"; +delete from t1 where f2="bill"; +select * from t1; +f1 f2 +16 ted +12 ted +12 ted +12 ted +12 ted +drop table t1; +create table t1 (btn char(10) not null, key using HASH (btn)) type=heap; +insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i"); +explain select * from t1 where btn like "q%"; +table type possible_keys key key_len ref rows Extra +t1 ALL btn NULL NULL NULL 14 where used +select * from t1 where btn like "q%"; +btn +alter table t1 add column new_col char(1) not null, add key using HASH (btn,new_col), drop key btn; +update t1 set new_col=btn; +explain select * from t1 where btn="a"; +table type possible_keys key key_len ref rows Extra +t1 ALL btn NULL NULL NULL 14 where used +explain select * from t1 where btn="a" and new_col="a"; +table type possible_keys key key_len ref rows Extra +t1 ref btn btn 11 const,const 10 where used +drop table t1; +CREATE TABLE t1 ( +a int default NULL, +b int default NULL, +KEY a using HASH (a), +UNIQUE b using HASH (b) +) type=heap; +INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3); +SELECT * FROM t1 WHERE a=NULL; +a b +explain SELECT * FROM t1 WHERE a IS NULL; +table type possible_keys key key_len ref rows Extra +t1 ref a a 5 const 10 where used +SELECT * FROM t1 WHERE a<=>NULL; +a b +NULL 99 +SELECT * FROM t1 WHERE b=NULL; +a b +explain SELECT * FROM t1 WHERE b IS NULL; +table type possible_keys key key_len ref rows Extra +t1 ref b b 5 const 1 where used +SELECT * FROM t1 WHERE b<=>NULL; +a b +99 NULL +INSERT INTO t1 VALUES (1,3); +Duplicate entry '3' for key 1 +DROP TABLE t1; +CREATE TABLE t1 (a int not null, primary key using HASH (a)) type=heap; +INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); +DELETE from t1 where a < 100; +SELECT * from t1; +a +DROP TABLE t1; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index c9ae4170238..a233645fef5 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -805,7 +805,7 @@ create table t1 (a char(20), index (a(5))) type=innodb; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` char(20) default NULL, + `a` char(20) character set latin1 default NULL, KEY `a` (`a`) ) TYPE=InnoDB drop table t1; diff --git a/mysql-test/r/isam.result b/mysql-test/r/isam.result index d19352aad42..5cb218dc4ce 100644 --- a/mysql-test/r/isam.result +++ b/mysql-test/r/isam.result @@ -67,10 +67,10 @@ a int(11) PRI 0 b int(11) MUL 0 c int(11) 0 show full columns from t1; -Field Type Null Key Default Extra Privileges -a int(11) PRI 0 select,insert,update,references -b int(11) MUL 0 select,insert,update,references -c int(11) 0 select,insert,update,references +Field Type Null Key Default Extra Privileges Comment +a int(11) PRI 0 select,insert,update,references +b int(11) MUL 0 select,insert,update,references +c int(11) 0 select,insert,update,references show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 a A 4 NULL NULL BTREE diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index b95352a9eaa..8b617a328a8 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -172,7 +172,7 @@ show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `a` int(11) NOT NULL default '0', - `b` char(20) default NULL, + `b` char(20) character set latin1 default NULL, KEY `a` (`a`) ) TYPE=MRG_MyISAM UNION=(t1,t2) create table t4 (a int not null, b char(10), key(a)) type=MERGE UNION=(t1,t2); diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 8750ea94125..d1de03712ba 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1,7 +1,7 @@ drop table if exists t1; CREATE TABLE t1 ( STRING_DATA char(255) default NULL, -KEY STRING_DATA (STRING_DATA) +KEY string_data (STRING_DATA) ) TYPE=MyISAM; INSERT INTO t1 VALUES ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'); INSERT INTO t1 VALUES ('DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD'); diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 26c84af1618..bdef42aa5a8 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3207,23 +3207,23 @@ Tables_in_test (s%) show tables from test like "t?"; Tables_in_test (t?) show full columns from t2; -Field Type Null Key Default Extra Privileges -auto int(11) PRI NULL auto_increment select,insert,update,references -fld1 int(6) unsigned zerofill UNI 000000 select,insert,update,references -companynr tinyint(2) unsigned zerofill 00 select,insert,update,references -fld3 char(30) MUL select,insert,update,references -fld4 char(35) select,insert,update,references -fld5 char(35) select,insert,update,references -fld6 char(4) select,insert,update,references +Field Type Null Key Default Extra Privileges Comment +auto int(11) PRI NULL auto_increment select,insert,update,references +fld1 int(6) unsigned zerofill UNI 000000 select,insert,update,references +companynr tinyint(2) unsigned zerofill 00 select,insert,update,references +fld3 char(30) character set latin1 MUL select,insert,update,references +fld4 char(35) character set latin1 select,insert,update,references +fld5 char(35) character set latin1 select,insert,update,references +fld6 char(4) character set latin1 select,insert,update,references show full columns from t2 from test like 'f%'; -Field Type Null Key Default Extra Privileges -fld1 int(6) unsigned zerofill UNI 000000 select,insert,update,references -fld3 char(30) MUL select,insert,update,references -fld4 char(35) select,insert,update,references -fld5 char(35) select,insert,update,references -fld6 char(4) select,insert,update,references +Field Type Null Key Default Extra Privileges Comment +fld1 int(6) unsigned zerofill UNI 000000 select,insert,update,references +fld3 char(30) character set latin1 MUL select,insert,update,references +fld4 char(35) character set latin1 select,insert,update,references +fld5 char(35) character set latin1 select,insert,update,references +fld6 char(4) character set latin1 select,insert,update,references show full columns from t2 from test like 's%'; -Field Type Null Key Default Extra Privileges +Field Type Null Key Default Extra Privileges Comment show keys from t2; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 2c32d766a38..1f83bc61cdc 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -87,14 +87,21 @@ t2 CREATE TEMPORARY TABLE `t2` ( drop table t2; create table t1 ( test_set set( 'val1', 'val2', 'val3' ) not null default '', -name char(20) default 'O''Brien' +name char(20) default 'O''Brien' comment 'O''Brien as default', +c int not null comment 'int column' ) comment = 'it\'s a table' ; show create table t1 ; Table Create Table t1 CREATE TABLE `t1` ( `test_set` set('val1','val2','val3') NOT NULL default '', - `name` char(20) default 'O''Brien' + `name` char(20) character set latin1 default 'O''Brien' COMMENT 'O''Brien as default', + `c` int(11) NOT NULL default '0' COMMENT 'int column' ) TYPE=MyISAM COMMENT='it''s a table' +show full columns from t1; +Field Type Null Key Default Extra Privileges Comment +test_set set('val1','val2','val3') select,insert,update,references +name char(20) character set latin1 YES O'Brien select,insert,update,references O'Brien as default +c int(11) 0 select,insert,update,references int column drop table t1; create table t1 (a int not null, unique aa (a)); show create table t1; @@ -126,7 +133,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', - `b` char(10) default NULL, + `b` char(10) character set latin1 default NULL, KEY `b` (`b`) ) TYPE=MyISAM MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test' alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0; @@ -134,7 +141,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', - `b` varchar(10) default NULL, + `b` varchar(10) character set latin1 default NULL, KEY `b` (`b`) ) TYPE=MyISAM MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test' ALTER TABLE t1 AVG_ROW_LENGTH=0 CHECKSUM=0 COMMENT="" MIN_ROWS=0 MAX_ROWS=0 PACK_KEYS=DEFAULT DELAY_KEY_WRITE=0 ROW_FORMAT=default; @@ -142,7 +149,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', - `b` varchar(10) default NULL, + `b` varchar(10) character set latin1 default NULL, KEY `b` (`b`) ) TYPE=MyISAM drop table t1; @@ -155,6 +162,14 @@ e double(9,2) YES NULL f double(5,0) YES NULL h float(3,2) YES NULL i float(3,0) YES NULL +show full columns from t1; +Field Type Null Key Default Extra Privileges Comment +a decimal(9,2) YES NULL select,insert,update,references +b decimal(9,0) YES NULL select,insert,update,references +e double(9,2) YES NULL select,insert,update,references +f double(5,0) YES NULL select,insert,update,references +h float(3,2) YES NULL select,insert,update,references +i float(3,0) YES NULL select,insert,update,references drop table t1; create table t1 (c decimal, d double, f float, r real); show columns from t1; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result new file mode 100644 index 00000000000..fb91bebd727 --- /dev/null +++ b/mysql-test/r/subselect.result @@ -0,0 +1,94 @@ +select (select 2); +(select 2) +2 +drop table if exists t1,t2,t3,t4,attend,clinic; +create table t1 (a int); +create table t2 (a int, b int); +create table t3 (a int); +create table t4 (a int, b int); +insert into t1 values (2); +insert into t2 values (1,7),(2,7); +insert into t4 values (4,8),(3,8),(5,9); +select (select a from t1 where t1.a=t2.a), a from t2; +(select a from t1 where t1.a=t2.a) a +NULL 1 +2 2 +select (select a from t1 where t1.a=t2.b), a from t2; +(select a from t1 where t1.a=t2.b) a +NULL 1 +NULL 2 +select (select a from t1), a from t2; +(select a from t1) a +2 1 +2 2 +select (select a from t3), a from t2; +(select a from t3) a +NULL 1 +NULL 2 +select * from t2 where t2.a=(select a from t1); +a b +2 7 +insert into t3 values (6),(7),(3); +select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1); +a b +1 7 +2 7 +select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) +union (select * from t4 order by a limit 2) limit 3; +a b +1 7 +2 7 +3 8 +select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1) +union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a); +a b +1 7 +2 7 +3 8 +4 8 +select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; +(select a from t3 where a<t2.a*4 order by 1 desc limit 1) a +3 1 +7 2 +select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from +(select * from t2 where a>1) as tt; +(select t3.a from t3 where a<8 order by 1 desc limit 1) a +7 2 +select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); +a +2 +select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1); +a +2 +select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1); +a +select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; +b (select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) +8 7.5000 +8 4.5000 +9 7.5000 +select * from t3 where exists (select * from t2 where t2.b=t3.a); +a +7 +select * from t3 where not exists (select * from t2 where t2.b=t3.a); +a +6 +3 +insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9); +select b,max(a) as ma from t4 group by b having b < (select max(t2.a) +from t2 where t2.b=t4.b); +b ma +select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) +from t2 where t2.b=t4.b); +b ma +7 12 +create table attend (patient_uq int, clinic_uq int, index i1 (clinic_uq)); +create table clinic( uq int primary key, name char(25)); +insert into clinic values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta"); +insert into attend values (1,1),(1,2),(2,2),(1,3); +select * from attend where exists (select * from clinic where uq = clinic_uq); +patient_uq clinic_uq +1 1 +1 2 +2 2 +drop table t1,t2,t3,t4,attend,clinic; diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 5626bc0fe11..b5b5b774ee5 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -36,7 +36,7 @@ show create table t9; Table Create Table t9 CREATE TABLE `t9` ( `a` int(11) NOT NULL auto_increment, - `b` char(16) NOT NULL default '', + `b` char(16) character set latin1 NOT NULL default '', `c` int(11) NOT NULL default '0', PRIMARY KEY (`a`) ) TYPE=MyISAM @@ -57,7 +57,7 @@ show create table test_mysqltest.t9; Table Create Table t9 CREATE TABLE `t9` ( `a` int(11) NOT NULL auto_increment, - `b` char(16) NOT NULL default '', + `b` char(16) character set latin1 NOT NULL default '', `c` int(11) NOT NULL default '0', `d` int(11) NOT NULL default '0', PRIMARY KEY (`a`) diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index 2047eed6227..c99d22c2889 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -37,18 +37,18 @@ insert into t1 values (NULL,NULL,NULL,NULL); update t1 set c="",b=null where c="1"; lock tables t1 READ; show full fields from t1; -Field Type Null Key Default Extra Privileges -t text YES NULL select,insert,update,references -c varchar(10) YES NULL select,insert,update,references -b blob YES NULL select,insert,update,references -d varchar(10) binary YES NULL select,insert,update,references +Field Type Null Key Default Extra Privileges Comment +t text character set latin1 YES NULL select,insert,update,references +c varchar(10) character set latin1 YES NULL select,insert,update,references +b blob YES NULL select,insert,update,references +d varchar(10) binary YES NULL select,insert,update,references lock tables t1 WRITE; show full fields from t1; -Field Type Null Key Default Extra Privileges -t text YES NULL select,insert,update,references -c varchar(10) YES NULL select,insert,update,references -b blob YES NULL select,insert,update,references -d varchar(10) binary YES NULL select,insert,update,references +Field Type Null Key Default Extra Privileges Comment +t text character set latin1 YES NULL select,insert,update,references +c varchar(10) character set latin1 YES NULL select,insert,update,references +b blob YES NULL select,insert,update,references +d varchar(10) binary YES NULL select,insert,update,references unlock tables; select t from t1 where t like "hello"; t diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index e85bced353a..c6eca3abe8b 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -10,9 +10,9 @@ SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1; drop table if exists t1; create table t1 (f1 float(24),f2 float(52)); show full columns from t1; -Field Type Null Key Default Extra Privileges -f1 float YES NULL select,insert,update,references -f2 double YES NULL select,insert,update,references +Field Type Null Key Default Extra Privileges Comment +f1 float YES NULL select,insert,update,references +f2 double YES NULL select,insert,update,references insert into t1 values(10,10),(1e+5,1e+5),(1234567890,1234567890),(1e+10,1e+10),(1e+15,1e+15),(1e+20,1e+20),(1e+50,1e+50),(1e+150,1e+150); insert into t1 values(-10,-10),(1e-5,1e-5),(1e-10,1e-10),(1e-15,1e-15),(1e-20,1e-20),(1e-50,1e-50),(1e-150,1e-150); select * from t1; @@ -69,19 +69,19 @@ min(a) drop table t1; create table t1 (f float, f2 float(24), f3 float(6,2), d double, d2 float(53), d3 double(10,3), de decimal, de2 decimal(6), de3 decimal(5,2), n numeric, n2 numeric(8), n3 numeric(5,6)); show full columns from t1; -Field Type Null Key Default Extra Privileges -f float YES NULL select,insert,update,references -f2 float YES NULL select,insert,update,references -f3 float(6,2) YES NULL select,insert,update,references -d double YES NULL select,insert,update,references -d2 double YES NULL select,insert,update,references -d3 double(10,3) YES NULL select,insert,update,references -de decimal(10,0) YES NULL select,insert,update,references -de2 decimal(6,0) YES NULL select,insert,update,references -de3 decimal(5,2) YES NULL select,insert,update,references -n decimal(10,0) YES NULL select,insert,update,references -n2 decimal(8,0) YES NULL select,insert,update,references -n3 decimal(7,6) YES NULL select,insert,update,references +Field Type Null Key Default Extra Privileges Comment +f float YES NULL select,insert,update,references +f2 float YES NULL select,insert,update,references +f3 float(6,2) YES NULL select,insert,update,references +d double YES NULL select,insert,update,references +d2 double YES NULL select,insert,update,references +d3 double(10,3) YES NULL select,insert,update,references +de decimal(10,0) YES NULL select,insert,update,references +de2 decimal(6,0) YES NULL select,insert,update,references +de3 decimal(5,2) YES NULL select,insert,update,references +n decimal(10,0) YES NULL select,insert,update,references +n2 decimal(8,0) YES NULL select,insert,update,references +n3 decimal(7,6) YES NULL select,insert,update,references drop table t1; create table t1 (a decimal(7,3) not null, key (a)); insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1"); diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index fc7cc5255cf..01149b68935 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -38,31 +38,31 @@ KEY (ulonglong,ulong), KEY (options,flags) ); show full fields from t1; -Field Type Null Key Default Extra Privileges -auto int(5) unsigned PRI NULL auto_increment select,insert,update,references -string varchar(10) YES hello select,insert,update,references -tiny tinyint(4) MUL 0 select,insert,update,references -short smallint(6) MUL 1 select,insert,update,references -medium mediumint(8) MUL 0 select,insert,update,references -long_int int(11) 0 select,insert,update,references -longlong bigint(13) MUL 0 select,insert,update,references -real_float float(13,1) MUL 0.0 select,insert,update,references -real_double double(16,4) YES NULL select,insert,update,references -utiny tinyint(3) unsigned MUL 0 select,insert,update,references -ushort smallint(5) unsigned zerofill MUL 00000 select,insert,update,references -umedium mediumint(8) unsigned MUL 0 select,insert,update,references -ulong int(11) unsigned MUL 0 select,insert,update,references -ulonglong bigint(13) unsigned MUL 0 select,insert,update,references -time_stamp timestamp(14) YES NULL select,insert,update,references -date_field date YES NULL select,insert,update,references -time_field time YES NULL select,insert,update,references -date_time datetime YES NULL select,insert,update,references -blob_col blob YES NULL select,insert,update,references -tinyblob_col tinyblob YES NULL select,insert,update,references -mediumblob_col mediumblob select,insert,update,references -longblob_col longblob select,insert,update,references -options enum('one','two','tree') MUL one select,insert,update,references -flags set('one','two','tree') select,insert,update,references +Field Type Null Key Default Extra Privileges Comment +auto int(5) unsigned PRI NULL auto_increment select,insert,update,references +string varchar(10) character set latin1 YES hello select,insert,update,references +tiny tinyint(4) MUL 0 select,insert,update,references +short smallint(6) MUL 1 select,insert,update,references +medium mediumint(8) MUL 0 select,insert,update,references +long_int int(11) 0 select,insert,update,references +longlong bigint(13) MUL 0 select,insert,update,references +real_float float(13,1) MUL 0.0 select,insert,update,references +real_double double(16,4) YES NULL select,insert,update,references +utiny tinyint(3) unsigned MUL 0 select,insert,update,references +ushort smallint(5) unsigned zerofill MUL 00000 select,insert,update,references +umedium mediumint(8) unsigned MUL 0 select,insert,update,references +ulong int(11) unsigned MUL 0 select,insert,update,references +ulonglong bigint(13) unsigned MUL 0 select,insert,update,references +time_stamp timestamp(14) YES NULL select,insert,update,references +date_field date YES NULL select,insert,update,references +time_field time YES NULL select,insert,update,references +date_time datetime YES NULL select,insert,update,references +blob_col blob YES NULL select,insert,update,references +tinyblob_col tinyblob YES NULL select,insert,update,references +mediumblob_col mediumblob select,insert,update,references +longblob_col longblob select,insert,update,references +options enum('one','two','tree') MUL one select,insert,update,references +flags set('one','two','tree') select,insert,update,references show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 auto A 0 NULL NULL BTREE @@ -168,57 +168,57 @@ drop table t2; create table t2 select * from t1; update t2 set string="changed" where auto=16; show full columns from t1; -Field Type Null Key Default Extra Privileges -auto int(5) unsigned MUL NULL auto_increment select,insert,update,references -string varchar(10) YES new defaul select,insert,update,references -tiny tinyint(4) MUL 0 select,insert,update,references -short smallint(6) MUL 0 select,insert,update,references -medium mediumint(8) MUL 0 select,insert,update,references -long_int int(11) 0 select,insert,update,references -longlong bigint(13) MUL 0 select,insert,update,references -real_float float(13,1) MUL 0.0 select,insert,update,references -real_double double(16,4) YES NULL select,insert,update,references -utiny tinyint(3) unsigned 0 select,insert,update,references -ushort smallint(5) unsigned zerofill 00000 select,insert,update,references -umedium mediumint(8) unsigned MUL 0 select,insert,update,references -ulong int(11) unsigned MUL 0 select,insert,update,references -ulonglong bigint(13) unsigned MUL 0 select,insert,update,references -time_stamp timestamp(14) YES NULL select,insert,update,references -date_field varchar(10) YES NULL select,insert,update,references -time_field time YES NULL select,insert,update,references -date_time datetime YES NULL select,insert,update,references -new_blob_col varchar(20) YES NULL select,insert,update,references -tinyblob_col tinyblob YES NULL select,insert,update,references -mediumblob_col mediumblob select,insert,update,references -options enum('one','two','tree') MUL one select,insert,update,references -flags set('one','two','tree') select,insert,update,references -new_field varchar(10) new select,insert,update,references +Field Type Null Key Default Extra Privileges Comment +auto int(5) unsigned MUL NULL auto_increment select,insert,update,references +string varchar(10) character set latin1 YES new defaul select,insert,update,references +tiny tinyint(4) MUL 0 select,insert,update,references +short smallint(6) MUL 0 select,insert,update,references +medium mediumint(8) MUL 0 select,insert,update,references +long_int int(11) 0 select,insert,update,references +longlong bigint(13) MUL 0 select,insert,update,references +real_float float(13,1) MUL 0.0 select,insert,update,references +real_double double(16,4) YES NULL select,insert,update,references +utiny tinyint(3) unsigned 0 select,insert,update,references +ushort smallint(5) unsigned zerofill 00000 select,insert,update,references +umedium mediumint(8) unsigned MUL 0 select,insert,update,references +ulong int(11) unsigned MUL 0 select,insert,update,references +ulonglong bigint(13) unsigned MUL 0 select,insert,update,references +time_stamp timestamp(14) YES NULL select,insert,update,references +date_field varchar(10) character set latin1 YES NULL select,insert,update,references +time_field time YES NULL select,insert,update,references +date_time datetime YES NULL select,insert,update,references +new_blob_col varchar(20) character set latin1 YES NULL select,insert,update,references +tinyblob_col tinyblob YES NULL select,insert,update,references +mediumblob_col mediumblob select,insert,update,references +options enum('one','two','tree') MUL one select,insert,update,references +flags set('one','two','tree') select,insert,update,references +new_field varchar(10) character set latin1 new select,insert,update,references show full columns from t2; -Field Type Null Key Default Extra Privileges -auto int(5) unsigned 0 select,insert,update,references -string varchar(10) YES new defaul select,insert,update,references -tiny tinyint(4) 0 select,insert,update,references -short smallint(6) 0 select,insert,update,references -medium mediumint(8) 0 select,insert,update,references -long_int int(11) 0 select,insert,update,references -longlong bigint(13) 0 select,insert,update,references -real_float float(13,1) 0.0 select,insert,update,references -real_double double(16,4) YES NULL select,insert,update,references -utiny tinyint(3) unsigned 0 select,insert,update,references -ushort smallint(5) unsigned zerofill 00000 select,insert,update,references -umedium mediumint(8) unsigned 0 select,insert,update,references -ulong int(11) unsigned 0 select,insert,update,references -ulonglong bigint(13) unsigned 0 select,insert,update,references -time_stamp timestamp(14) YES NULL select,insert,update,references -date_field varchar(10) YES NULL select,insert,update,references -time_field time YES NULL select,insert,update,references -date_time datetime YES NULL select,insert,update,references -new_blob_col varchar(20) YES NULL select,insert,update,references -tinyblob_col tinyblob YES NULL select,insert,update,references -mediumblob_col mediumblob select,insert,update,references -options enum('one','two','tree') one select,insert,update,references -flags set('one','two','tree') select,insert,update,references -new_field varchar(10) new select,insert,update,references +Field Type Null Key Default Extra Privileges Comment +auto int(5) unsigned 0 select,insert,update,references +string varchar(10) character set latin1 YES new defaul select,insert,update,references +tiny tinyint(4) 0 select,insert,update,references +short smallint(6) 0 select,insert,update,references +medium mediumint(8) 0 select,insert,update,references +long_int int(11) 0 select,insert,update,references +longlong bigint(13) 0 select,insert,update,references +real_float float(13,1) 0.0 select,insert,update,references +real_double double(16,4) YES NULL select,insert,update,references +utiny tinyint(3) unsigned 0 select,insert,update,references +ushort smallint(5) unsigned zerofill 00000 select,insert,update,references +umedium mediumint(8) unsigned 0 select,insert,update,references +ulong int(11) unsigned 0 select,insert,update,references +ulonglong bigint(13) unsigned 0 select,insert,update,references +time_stamp timestamp(14) YES NULL select,insert,update,references +date_field varchar(10) character set latin1 YES NULL select,insert,update,references +time_field time YES NULL select,insert,update,references +date_time datetime YES NULL select,insert,update,references +new_blob_col varchar(20) character set latin1 YES NULL select,insert,update,references +tinyblob_col tinyblob YES NULL select,insert,update,references +mediumblob_col mediumblob select,insert,update,references +options enum('one','two','tree') one select,insert,update,references +flags set('one','two','tree') select,insert,update,references +new_field varchar(10) character set latin1 new select,insert,update,references select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.time_field<>t2.time_field and (t1.time_field is not null or t2.time_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.new_blob_col<>t2.new_blob_col and (t1.new_blob_col is not null or t2.new_blob_col is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field is not null))); auto auto 16 16 @@ -228,12 +228,12 @@ auto auto drop table t2; create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, "a" as t2, repeat("a",256) as t3, binary repeat("b",256) as t4 from t1; show full columns from t2; -Field Type Null Key Default Extra Privileges -auto bigint(17) unsigned PRI 0 select,insert,update,references -t1 bigint(1) 0 select,insert,update,references -t2 char(1) select,insert,update,references -t3 mediumtext select,insert,update,references -t4 mediumblob select,insert,update,references +Field Type Null Key Default Extra Privileges Comment +auto bigint(17) unsigned PRI 0 select,insert,update,references +t1 bigint(1) 0 select,insert,update,references +t2 char(1) character set latin1 select,insert,update,references +t3 mediumtext character set latin1 select,insert,update,references +t4 mediumblob select,insert,update,references select * from t2; auto t1 t2 t3 t4 11 1 a aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb @@ -251,10 +251,10 @@ create table t3 select * from t1, t2; Duplicate column name 'c' create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2; show full columns from t3; -Field Type Null Key Default Extra Privileges -c1 int(11) YES NULL select,insert,update,references -c2 int(11) YES NULL select,insert,update,references -const bigint(1) 0 select,insert,update,references +Field Type Null Key Default Extra Privileges Comment +c1 int(11) YES NULL select,insert,update,references +c2 int(11) YES NULL select,insert,update,references +const bigint(1) 0 select,insert,update,references drop table t1,t2,t3; create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield)); drop table t1; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 896901dd8af..ec30db36cd4 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -88,7 +88,6 @@ explain (select a,b from t1 limit 2) union all (select a,b from t2 order by a l table type possible_keys key key_len ref rows Extra t1 ALL NULL NULL NULL NULL 4 t2 ALL NULL NULL NULL NULL 4 Using filesort -t1 ALL NULL NULL NULL NULL 4 (select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; a b 1 a |