diff options
Diffstat (limited to 'mysql-test/t')
| -rw-r--r-- | mysql-test/t/create.test | 16 | ||||
| -rw-r--r-- | mysql-test/t/ctype_many.test | 191 | ||||
| -rw-r--r-- | mysql-test/t/derived.test | 11 | ||||
| -rw-r--r-- | mysql-test/t/heap.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/heap_btree.test | 147 | ||||
| -rw-r--r-- | mysql-test/t/heap_hash.test | 140 | ||||
| -rw-r--r-- | mysql-test/t/myisam.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/show_check.test | 5 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 43 |
9 files changed, 554 insertions, 3 deletions
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 7e924e9f262..0beb71aac48 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -83,6 +83,22 @@ describe t2; drop table t1,t2; # +# Test of CREATE ... SELECT with duplicate fields +# + +create table t1 (a tinyint); +create table t2 (a int) select * from t1; +describe t1; +describe t2; +drop table if exists t2; +!$1060 create table t2 (a int, a float) select * from t1; +drop table if exists t2; +!$1060 create table t2 (a int) select a as b, a+1 as b from t1; +drop table if exists t2; +!$1060 create table t2 (b int) select a as b, a+1 as b from t1; +drop table if exists t1,t2; + +# # Test of primary key with 32 index # diff --git a/mysql-test/t/ctype_many.test b/mysql-test/t/ctype_many.test new file mode 100644 index 00000000000..1e89b1419d2 --- /dev/null +++ b/mysql-test/t/ctype_many.test @@ -0,0 +1,191 @@ +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; +ALTER TABLE t1 CHANGE comment comment CHAR(32) CHARACTER SET latin2 NOT NULL; +SHOW CREATE TABLE t1; +ALTER TABLE t1 ADD latin5_f CHAR(32) NOT NULL; +SHOW CREATE TABLE t1; +ALTER TABLE t1 CHARSET=latin2; +ALTER TABLE t1 ADD latin2_f CHAR(32) NOT NULL; +SHOW CREATE TABLE t1; +ALTER TABLE t1 DROP latin2_f, DROP latin5_f; +SHOW CREATE TABLE t1; + +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; + +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; + +SELECT koi8_ru_f,MIN(comment) FROM t1 GROUP BY 1; +SELECT utf8_f,MIN(comment) FROM t1 GROUP BY 1; +SELECT DISTINCT koi8_ru_f FROM t1; +SELECT DISTINCT utf8_f FROM t1; +SELECT lower(koi8_ru_f) FROM t1 ORDER BY 1 DESC; +SELECT lower(utf8_f) FROM t1 ORDER BY 1 DESC; + +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; + +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; + +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; +DROP TABLE t1; diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test new file mode 100644 index 00000000000..76ef5fba351 --- /dev/null +++ b/mysql-test/t/derived.test @@ -0,0 +1,11 @@ +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; +select t1.a,t3.a from t1,(select * from t2 where b='c') as t3 where t1.a = t3.a; +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; +drop table if exists t1.t2,t3; diff --git a/mysql-test/t/heap.test b/mysql-test/t/heap.test index ef7e3239a96..f8b27d09a19 100644 --- a/mysql-test/t/heap.test +++ b/mysql-test/t/heap.test @@ -42,7 +42,7 @@ alter table t1 type=myisam; explain select * from t1 where a in (869751,736494,226312,802616); 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/t/heap_btree.test b/mysql-test/t/heap_btree.test new file mode 100644 index 00000000000..e4b7d8674b9 --- /dev/null +++ b/mysql-test/t/heap_btree.test @@ -0,0 +1,147 @@ +# +# Test of heap tables. +# + +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 table status like "t1"; +show keys from t1; +select * from t1; +select * from t1 where a=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; +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; +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"; +#show table status like "t1"; +select * from t1; +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; +alter table t1 add unique uniq_id using BTREE (a); +select * from t1 where a > 736494; +select * from t1 where a = 736494; +select * from t1 where a=869751 or a=736494; +select * from t1 where a in (869751,736494,226312,802616); +alter table t1 type=myisam; +explain select * from t1 where a in (869751,736494,226312,802616); +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; +select * from t1,t1 as t2 where t1.x=t2.y; +explain select * from t1,t1 as t2 where t1.x=t2.y; +drop table t1; + +create table t1 (a int) type=heap; +insert into t1 values(1); +select max(a) from t1; +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; +insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); +select * from t1 where a=1; +--replace_result 0 x 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x 11 x 12 x 13 x 14 x +explain select * from t1 where a=1 order by a,b; +--replace_result 0 x 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x 11 x 12 x 13 x 14 x +explain select * from t1 where a=1 order by b; +select * from t1 where b=1; +--replace_result 0 x 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x 11 x 12 x 13 x 14 x +explain select * from t1 where b=1; +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; +insert into t1 values(2); +select max(id) from t1; +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; + +# Test of non unique index + +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; +drop table t1; + +# +# Test when using part key searches +# + +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%"; +select * from t1 where btn like "q%"; +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"; +explain select * from t1 where btn="a" and new_col="a"; +drop table t1; + +# +# Test of NULL keys +# + +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; +explain SELECT * FROM t1 WHERE a IS NULL; +SELECT * FROM t1 WHERE a<=>NULL; +SELECT * FROM t1 WHERE b=NULL; +explain SELECT * FROM t1 WHERE b IS NULL; +SELECT * FROM t1 WHERE b<=>NULL; + +--error 1062 +INSERT INTO t1 VALUES (1,3); +DROP TABLE t1; + +# +# Test when deleting all rows +# + +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; +DROP TABLE t1; diff --git a/mysql-test/t/heap_hash.test b/mysql-test/t/heap_hash.test new file mode 100644 index 00000000000..5dbd2b4a928 --- /dev/null +++ b/mysql-test/t/heap_hash.test @@ -0,0 +1,140 @@ +# +# Test of heap tables. +# + +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 table status like "t1"; +show keys from t1; +select * from t1; +select * from t1 where a=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; +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; +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"; +#show table status like "t1"; +select * from t1; +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; +alter table t1 add unique uniq_id using HASH (a); +select * from t1 where a > 736494; +select * from t1 where a = 736494; +select * from t1 where a=869751 or a=736494; +select * from t1 where a in (869751,736494,226312,802616); +alter table t1 type=myisam; +explain select * from t1 where a in (869751,736494,226312,802616); +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; +select * from t1,t1 as t2 where t1.x=t2.y; +explain select * from t1,t1 as t2 where t1.x=t2.y; +drop table t1; + +create table t1 (a int) type=heap; +insert into t1 values(1); +select max(a) from t1; +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; +insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); +select * from t1 where a=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; +insert into t1 values(2); +select max(id) from t1; +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; + +# Test of non unique index + +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; +drop table t1; + +# +# Test when using part key searches +# + +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%"; +select * from t1 where btn like "q%"; +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"; +explain select * from t1 where btn="a" and new_col="a"; +drop table t1; + +# +# Test of NULL keys +# + +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; +explain SELECT * FROM t1 WHERE a IS NULL; +SELECT * FROM t1 WHERE a<=>NULL; +SELECT * FROM t1 WHERE b=NULL; +explain SELECT * FROM t1 WHERE b IS NULL; +SELECT * FROM t1 WHERE b<=>NULL; + +--error 1062 +INSERT INTO t1 VALUES (1,3); +DROP TABLE t1; + +# +# Test when deleting all rows +# + +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; +DROP TABLE t1; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index c607cb4d63e..2479a68fc09 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -5,7 +5,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'); diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 5e10ebf23a3..eb15eac8bad 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -49,9 +49,11 @@ 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 ; +show full columns from t1; drop table t1; create table t1 (a int not null, unique aa (a)); @@ -78,6 +80,7 @@ drop table t1; create table t1 (a decimal(9,2), b decimal (9,0), e double(9,2), f double(5,0), h float(3,2), i float(3,0)); show columns from t1; +show full columns from t1; drop table t1; # Check auto conversions of types diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test new file mode 100644 index 00000000000..16ba6c9e602 --- /dev/null +++ b/mysql-test/t/subselect.test @@ -0,0 +1,43 @@ + +select (select 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 (select a from t1 where t1.a=t2.b), a from t2; +select (select a from t1), a from t2; +select (select a from t3), a from t2; +select * from t2 where t2.a=(select a from t1); +insert into t3 values (6),(7),(3); +select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1); +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; +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); +select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2; +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 * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); +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); +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); +select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4; +select * from t3 where exists (select * from t2 where t2.b=t3.a); +select * from t3 where not exists (select * from t2 where t2.b=t3.a); +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); +select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) +from t2 where t2.b=t4.b); + +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); + +drop table t1,t2,t3,t4,attend,clinic; |