ndb_index_unique.test
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:8k
源码类别:
MySQL数据库
开发平台:
Visual C++
- -- source include/have_ndb.inc
- -- source include/not_embedded.inc
- --disable_warnings
- drop table if exists t1, t2, t3, t4, t5, t6, t7;
- --enable_warnings
- #
- # Simple test to show use of UNIQUE indexes
- #
- CREATE TABLE t1 (
- a int unsigned NOT NULL PRIMARY KEY,
- b int unsigned not null,
- c int unsigned,
- UNIQUE(b)
- ) engine=ndbcluster;
- insert t1 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
- select * from t1 order by b;
- select * from t1 where b = 4 order by b;
- insert into t1 values(7,8,3);
- select * from t1 where b = 4 order by a;
- -- error 1062
- insert into t1 values(8, 2, 3);
- select * from t1 order by a;
- delete from t1 where a = 1;
- insert into t1 values(8, 2, 3);
- select * from t1 order by a;
- drop table t1;
- #
- # Indexing NULL values
- #
- CREATE TABLE t1 (
- a int unsigned NOT NULL PRIMARY KEY,
- b int unsigned,
- c int unsigned,
- UNIQUE bc(b,c)
- ) engine = ndb;
- insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
- select * from t1 use index (bc) where b IS NULL order by a;
- select * from t1 use index (bc)order by a;
- select * from t1 use index (bc) order by a;
- select * from t1 use index (PRIMARY) where b IS NULL order by a;
- select * from t1 use index (bc) where b IS NULL order by a;
- select * from t1 use index (bc) where b IS NULL and c IS NULL order by a;
- select * from t1 use index (bc) where b IS NULL and c = 2 order by a;
- select * from t1 use index (bc) where b < 4 order by a;
- select * from t1 use index (bc) where b IS NOT NULL order by a;
- -- error 1062
- insert into t1 values(5,1,1);
- drop table t1;
- #
- # Show use of UNIQUE USING HASH indexes
- #
- CREATE TABLE t2 (
- a int unsigned NOT NULL PRIMARY KEY,
- b int unsigned not null,
- c int unsigned not null,
- UNIQUE USING HASH (b, c)
- ) engine=ndbcluster;
- insert t2 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
- select * from t2 where a = 3;
- select * from t2 where b = 4;
- select * from t2 where c = 6;
- insert into t2 values(7,8,3);
- select * from t2 where b = 4 order by a;
- -- error 1062
- insert into t2 values(8, 2, 3);
- select * from t2 order by a;
- delete from t2 where a = 1;
- insert into t2 values(8, 2, 3);
- select * from t2 order by a;
- drop table t2;
- -- error 1121
- CREATE TABLE t2 (
- a int unsigned NOT NULL PRIMARY KEY,
- b int unsigned not null,
- c int unsigned,
- UNIQUE USING HASH (b, c)
- ) engine=ndbcluster;
- #
- # Show use of PRIMARY KEY USING HASH indexes
- #
- CREATE TABLE t3 (
- a int unsigned NOT NULL,
- b int unsigned not null,
- c int unsigned,
- PRIMARY KEY USING HASH (a, b)
- ) engine=ndbcluster;
- insert t3 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
- select * from t3 where a = 3;
- select * from t3 where b = 4;
- select * from t3 where c = 6;
- insert into t3 values(7,8,3);
- select * from t3 where b = 4 order by a;
- drop table t3;
- #
- # Indexes on NULL-able columns
- #
- CREATE TABLE t1 (
- pk int NOT NULL PRIMARY KEY,
- a int unsigned,
- UNIQUE KEY (a)
- ) engine=ndbcluster;
- insert into t1 values (-1,NULL), (0,0), (1,NULL),(2,2),(3,NULL),(4,4);
- select * from t1 order by pk;
- --error 1062
- insert into t1 values (5,0);
- select * from t1 order by pk;
- delete from t1 where a = 0;
- insert into t1 values (5,0);
- select * from t1 order by pk;
- CREATE TABLE t2 (
- pk int NOT NULL PRIMARY KEY,
- a int unsigned,
- b tinyint NOT NULL,
- c VARCHAR(10),
- UNIQUE KEY si(a, c)
- ) engine=ndbcluster;
- insert into t2 values (-1,1,17,NULL),(0,NULL,18,NULL),(1,3,19,'abc');
- select * from t2 order by pk;
- --error 1062
- insert into t2 values(2,3,19,'abc');
- select * from t2 order by pk;
- delete from t2 where c IS NOT NULL;
- insert into t2 values(2,3,19,'abc');
- select * from t2 order by pk;
- drop table t1, t2;
- #
- # More complex tables
- #
- CREATE TABLE t1 (
- cid smallint(5) unsigned NOT NULL default '0',
- cv varchar(250) NOT NULL default '',
- PRIMARY KEY (cid),
- UNIQUE KEY cv (cv)
- ) engine=ndbcluster;
- INSERT INTO t1 VALUES (8,'dummy');
- CREATE TABLE t2 (
- cid bigint(20) unsigned NOT NULL auto_increment,
- cap varchar(255) NOT NULL default '',
- PRIMARY KEY (cid),
- UNIQUE KEY (cid, cap)
- ) engine=ndbcluster;
- INSERT INTO t2 VALUES (NULL,'another dummy');
- CREATE TABLE t3 (
- gid bigint(20) unsigned NOT NULL auto_increment,
- gn varchar(255) NOT NULL default '',
- must tinyint(4) default NULL,
- PRIMARY KEY (gid)
- ) engine=ndbcluster;
- INSERT INTO t3 VALUES (1,'V1',NULL);
- CREATE TABLE t4 (
- uid bigint(20) unsigned NOT NULL default '0',
- gid bigint(20) unsigned NOT NULL,
- rid bigint(20) unsigned NOT NULL default '-1',
- cid bigint(20) unsigned NOT NULL default '-1',
- UNIQUE KEY m (uid,gid,rid,cid)
- ) engine=ndbcluster;
- INSERT INTO t4 VALUES (1,1,2,4);
- INSERT INTO t4 VALUES (1,1,2,3);
- INSERT INTO t4 VALUES (1,1,5,7);
- INSERT INTO t4 VALUES (1,1,10,8);
- CREATE TABLE t5 (
- rid bigint(20) unsigned NOT NULL auto_increment,
- rl varchar(255) NOT NULL default '',
- PRIMARY KEY (rid)
- ) engine=ndbcluster;
- CREATE TABLE t6 (
- uid bigint(20) unsigned NOT NULL auto_increment,
- un varchar(250) NOT NULL default '',
- uc smallint(5) unsigned NOT NULL default '0',
- PRIMARY KEY (uid),
- UNIQUE KEY nc (un,uc)
- ) engine=ndbcluster;
- INSERT INTO t6 VALUES (1,'test',8);
- INSERT INTO t6 VALUES (2,'test2',9);
- INSERT INTO t6 VALUES (3,'tre',3);
- CREATE TABLE t7 (
- mid bigint(20) unsigned NOT NULL PRIMARY KEY,
- uid bigint(20) unsigned NOT NULL default '0',
- gid bigint(20) unsigned NOT NULL,
- rid bigint(20) unsigned NOT NULL default '-1',
- cid bigint(20) unsigned NOT NULL default '-1',
- UNIQUE KEY m (uid,gid,rid,cid)
- ) engine=ndbcluster;
- INSERT INTO t7 VALUES(1, 1, 1, 1, 1);
- INSERT INTO t7 VALUES(2, 2, 1, 1, 1);
- INSERT INTO t7 VALUES(3, 3, 1, 1, 1);
- INSERT INTO t7 VALUES(4, 4, 1, 1, 1);
- INSERT INTO t7 VALUES(5, 5, 1, 1, 1);
- INSERT INTO t7 VALUES(6, 1, 1, 1, 6);
- INSERT INTO t7 VALUES(7, 2, 1, 1, 7);
- INSERT INTO t7 VALUES(8, 3, 1, 1, 8);
- INSERT INTO t7 VALUES(9, 4, 1, 1, 9);
- INSERT INTO t7 VALUES(10, 5, 1, 1, 10);
- select * from t1 where cv = 'dummy';
- select * from t1 where cv = 'test';
- select * from t2 where cap = 'another dummy';
- select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4;
- select * from t4 where uid = 1 and gid=1 and rid=1 and cid=4;
- select * from t4 where uid = 1 order by cid;
- select * from t4 where rid = 2 order by cid;
- select * from t6 where un='test' and uc=8;
- select * from t6 where un='test' and uc=7;
- select * from t6 where un='test';
- select * from t7 where mid = 8;
- select * from t7 where uid = 8;
- select * from t7 where uid = 1 order by mid;
- select * from t7 where uid = 4 order by mid;
- select * from t7 where gid = 4;
- select * from t7 where gid = 1 order by mid;
- select * from t7 where cid = 4;
- select * from t7 where cid = 8;
- #
- # insert more records into t4
- #
- let $1=100;
- disable_query_log;
- while ($1)
- {
- eval insert into t4 values(1, $1, 5, 12);
- eval insert into t4 values($1, 3, 9, 11);
- dec $1;
- }
- enable_query_log;
- select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4;
- select * from t4 where uid = 1 and gid=1 and rid=1 and cid=4;
- select * from t4 where uid = 1 order by gid,cid;
- select * from t4 where uid = 1 order by gid,cid;
- select * from t4 where rid = 2 order by cid;
- drop table t1,t2,t3,t4,t5,t6,t7;
- # test null in indexes
- CREATE TABLE t1 (
- a int unsigned NOT NULL PRIMARY KEY,
- b int unsigned,
- c int unsigned,
- UNIQUE bc(b,c) ) engine = ndb;
- insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
- select * from t1 where b=1 and c=1;
- select * from t1 where b is null and c is null;
- select * from t1 where b is null and c = 2;
- select * from t1 where b = 4 and c is null;
- create table t8 as
- select * from t1 where (b = 1 and c = 1)
- or (b is null and c is null)
- or (b is null and c = 2)
- or (b = 4 and c is null);
- select * from t8 order by a;
- select * from t1 order by a;
- drop table t1, t8;
- ###############################
- # Bug 8101
- #
- # Unique index not specified in the same order as in table
- #
- create table t1(
- id integer not null auto_increment,
- month integer not null,
- year integer not null,
- code varchar( 2) not null,
- primary key ( id),
- unique idx_t1( month, code, year)
- ) engine=ndb;
- INSERT INTO t1 (month, year, code) VALUES (4,2004,'12');
- INSERT INTO t1 (month, year, code) VALUES (5,2004,'12');
- select * from t1 where code = '12' and month = 4 and year = 2004 ;
- drop table t1;
- # End of 4.1 tests