diff options
Diffstat (limited to 'mysql-test/suite/ndb/t/ndb_index.test')
-rw-r--r-- | mysql-test/suite/ndb/t/ndb_index.test | 132 |
1 files changed, 132 insertions, 0 deletions
diff --git a/mysql-test/suite/ndb/t/ndb_index.test b/mysql-test/suite/ndb/t/ndb_index.test new file mode 100644 index 00000000000..fc284407973 --- /dev/null +++ b/mysql-test/suite/ndb/t/ndb_index.test @@ -0,0 +1,132 @@ +-- source include/have_ndb.inc +-- source include/have_binlog_format_row.inc +-- source include/not_embedded.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +CREATE TABLE t1 ( + PORT varchar(16) NOT NULL, + ACCESSNODE varchar(16) NOT NULL, + POP varchar(48) NOT NULL, + ACCESSTYPE int unsigned NOT NULL, + CUSTOMER_ID varchar(20) collate latin1_bin NOT NULL, + PROVIDER varchar(16), + TEXPIRE int unsigned, + NUM_IP int unsigned, + LEASED_NUM_IP int unsigned, + LOCKED_IP int unsigned, + STATIC_DNS int unsigned, + SUSPENDED_SERVICE int unsigned, + SUSPENDED_REASON int unsigned, + BGP_COMMUNITY int unsigned, + INDEX CUSTOMER_ID_INDEX(CUSTOMER_ID), + INDEX FQPN_INDEX(POP,ACCESSNODE,PORT), + PRIMARY KEY(POP,ACCESSNODE,PORT,ACCESSTYPE) +) engine=ndbcluster; + +INSERT INTO t1 VALUES ('port67', 'node78', 'pop98', 1, 'kllopmn', 'pr_43', 121212, 1, 2, 3, 8, NULL, NULL, NULL); +INSERT INTO t1 VALUES ('port67', 'node78', 'pop99', 2, 'klkighh', 'pr_44', 121213, 3, 3, 6, 7, NULL, NULL, NULL); +INSERT INTO t1 VALUES ('port79', 'node79', 'pop79', 2, 'kpongfaa', 'pr_44', 981213, 2, 4, 10, 11, 2, 99, 1278); + + +# Test select using port +select port, accessnode, pop, accesstype from t1 where port='port67' order by accesstype; +select port, accessnode, pop, accesstype from t1 where port='foo'; + +# Test select using accessnode +select port, accessnode, pop, accesstype from t1 where accessnode='node78' order by accesstype; +select port, accessnode, pop, accesstype from t1 where accessnode='foo'; + +# Test select using pop +select port, accessnode, pop, accesstype from t1 where pop='pop98'; +select port, accessnode, pop, accesstype from t1 where pop='pop98'; +select port, accessnode, pop, accesstype from t1 where pop='pop98'; +select port, accessnode, pop, accesstype from t1 where pop='pop98' order by accesstype; +# The following two querys will not return any rows since +# the index used for access is case sensitive +# They are thus disabled for now +#select port, accessnode, pop, accesstype from t1 where pop='POP98'; +#select port, accessnode, pop, accesstype from t1 where pop='POP98' order by accesstype; +select port, accessnode, pop, accesstype from t1 where pop='foo'; + +# Test select using accesstype +select port, accessnode, pop, accesstype from t1 where accesstype=1; +select port, accessnode, pop, accesstype from t1 where accesstype=2 order by port; +select port, accessnode, pop, accesstype from t1 where accesstype=98 order by port; + +# Test select using customer_id +# NOTE! customer_id has a INDEX (ordered index in NDB), it's case sensitive! +select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn'; +select port, accessnode, pop, accesstype from t1 where customer_id='KLLOPMN'; +select port, accessnode, pop, accesstype from t1 where customer_id='kLLoPMn'; +select port, accessnode, pop, accesstype from t1 where customer_id='foo'; + +# Test select using provider +select port, accessnode, pop, accesstype from t1 where provider='pr_43'; +select port, accessnode, pop, accesstype from t1 where provider='foo'; + +# Test select using texpire +select port, accessnode from t1 where texpire=121212; +select port, accessnode from t1 where texpire=2323; + +# Test select using num_ip +select port, accessnode, pop, accesstype from t1 where num_ip=1; +select port, accessnode, pop, accesstype from t1 where num_ip=89; + +# Test select using leased_num_ip +select port, accessnode, pop, accesstype from t1 where leased_num_ip=2; +select port, accessnode, pop, accesstype from t1 where leased_num_ip=89; + +# Test select using locked_ip +select port, accessnode, pop, accesstype from t1 where locked_ip=3; +select port, accessnode, pop, accesstype from t1 where locked_ip=89; + +# Test select using static_dns +select port, accessnode, pop, accesstype from t1 where static_dns=8; +select port, accessnode, pop, accesstype from t1 where static_dns=89; + +# Test select using suspended_service +select port, accessnode, pop, accesstype from t1 where suspended_service=8; +select port, accessnode, pop, accesstype from t1 where suspended_service=89; + +# Test select using suspended_reason +select port, accessnode, pop, accesstype from t1 where suspended_reason=NULL; +select port, accessnode, pop, accesstype from t1 where suspended_reason=89; +select port, accessnode, pop, accesstype from t1 where suspended_reason=0; + +# Test select using bgp_community +select port, accessnode, pop, accesstype from t1 where bgp_community=NULL; +select port, accessnode, pop, accesstype from t1 where bgp_community=89; +select port, accessnode, pop, accesstype from t1 where bgp_community=0; + +# Test select using full primary key +select port, accessnode, pop, accesstype from t1 where port='port67' and accessnode='node78' and pop='pop98' and accesstype=1; +select port, accessnode, pop, accesstype from t1 where port='port67' and accesstype=1 and accessnode='node78' and pop='pop98'; +select port, accessnode, pop, accesstype from t1 where pop='pop98' and port='port67' and accesstype=1 and accessnode='node78'; +select port, accessnode from t1 where port='foo' and accessnode='foo' and pop='foo' and accesstype=99; + +# Test select using partial primary key +select port, accessnode, pop, accesstype from t1 where port='port67' and pop='pop98' and accesstype=1; +select port, accessnode, pop, accesstype from t1 where accesstype=1 and accessnode='node78' and pop='pop98'; +select port, accessnode, pop, accesstype from t1 where port='port67' and accesstype=1 and accessnode='node78'; +select port, accessnode from t1 where port='foo' and accessnode='foo' and pop='foo'; + +# Test select using CUSTOMER_ID_INDEX +select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn'; +select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn' and accesstype=1; +select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn' and accesstype=2; +select port, accessnode, pop, accesstype from t1 where accesstype=2 and customer_id='kllopmn'; + +# Test select using FQPN_INDEX +select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67'; +#select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67' order by accesstype; +#select port, accessnode, pop, accesstype from t1 where accessnode='node78' and port='port67' and pop='pop98' order by accesstype; +#select port, accessnode, pop, accesstype from t1 where port='port67' and pop='pop98' and accessnode='node78' order by accesstype; +select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67' and customer_id='kllopmn'; +select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67' and customer_id='foo'; + +drop table t1; + +# End of 4.1 tests |