-- source include/have_ndb.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;