drop table if exists t1, test1, test2; CREATE TABLE t1 ( a int unsigned NOT NULL PRIMARY KEY, b int unsigned not null, c int unsigned, KEY(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; a b c 1 2 3 2 3 5 3 4 6 4 5 8 5 6 2 6 7 2 select * from t1 where b >= 4 order by b; a b c 3 4 6 4 5 8 5 6 2 6 7 2 select * from t1 where b = 4 order by b; a b c 3 4 6 select * from t1 where b > 4 order by b; a b c 4 5 8 5 6 2 6 7 2 select * from t1 where b < 4 order by b; a b c 1 2 3 2 3 5 select * from t1 where b <= 4 order by b; a b c 1 2 3 2 3 5 3 4 6 select tt1.* from t1 as tt1, t1 as tt2 use index(b) where tt1.b = tt2.b order by tt1.b; a b c 1 2 3 2 3 5 3 4 6 4 5 8 5 6 2 6 7 2 select a, b, c from t1 where a!=2 and c=6; a b c 3 4 6 select a, b, c from t1 where a!=2 order by a; a b c 1 2 3 3 4 6 4 5 8 5 6 2 6 7 2 update t1 set c = 3 where b = 3; select * from t1 order by a; a b c 1 2 3 2 3 3 3 4 6 4 5 8 5 6 2 6 7 2 update t1 set c = 10 where b >= 6; select * from t1 order by a; a b c 1 2 3 2 3 3 3 4 6 4 5 8 5 6 10 6 7 10 update t1 set c = 11 where b < 5; select * from t1 order by a; a b c 1 2 11 2 3 11 3 4 11 4 5 8 5 6 10 6 7 10 update t1 set c = 12 where b > 0; select * from t1 order by a; a b c 1 2 12 2 3 12 3 4 12 4 5 12 5 6 12 6 7 12 update t1 set c = 13 where b <= 3; select * from t1 order by a; a b c 1 2 13 2 3 13 3 4 12 4 5 12 5 6 12 6 7 12 update t1 set b = b + 1 where b > 4 and b < 7; select * from t1 order by a; a b c 1 2 13 2 3 13 3 4 12 4 6 12 5 7 12 6 7 12 update t1 set a = a + 10 where b > 1 and b < 7; select * from t1 order by a; a b c 5 7 12 6 7 12 11 2 13 12 3 13 13 4 12 14 6 12 drop table t1; CREATE TABLE t1 ( a int unsigned NOT NULL PRIMARY KEY, b int unsigned not null, c int unsigned, KEY(b) ) engine=ndbcluster; insert t1 values(1, 2, 13), (2,3, 13), (3, 4, 12), (4, 5, 12), (5,6, 12), (6,7, 12); delete from t1 where b = 3; select * from t1 order by a; a b c 1 2 13 3 4 12 4 5 12 5 6 12 6 7 12 delete from t1 where b >= 6; select * from t1 order by a; a b c 1 2 13 3 4 12 4 5 12 delete from t1 where b < 4; select * from t1 order by a; a b c 3 4 12 4 5 12 delete from t1 where b > 5; select * from t1 order by a; a b c 3 4 12 4 5 12 delete from t1 where b <= 4; select * from t1 order by a; a b c 4 5 12 drop table t1; CREATE TABLE t1 ( a int unsigned NOT NULL PRIMARY KEY, b int unsigned not null, c int unsigned not null ) engine = ndb; create index a1 on t1 (b, c); insert into t1 values (1, 2, 13); insert into t1 values (2,3, 13); insert into t1 values (3, 4, 12); insert into t1 values (4, 5, 12); insert into t1 values (5,6, 12); insert into t1 values (6,7, 12); insert into t1 values (7, 2, 1); insert into t1 values (8,3, 6); insert into t1 values (9, 4, 12); insert into t1 values (14, 5, 4); insert into t1 values (15,5,5); insert into t1 values (16,5, 6); insert into t1 values (17,4,4); insert into t1 values (18,1, 7); select * from t1 order by a; a b c 1 2 13 2 3 13 3 4 12 4 5 12 5 6 12 6 7 12 7 2 1 8 3 6 9 4 12 14 5 4 15 5 5 16 5 6 17 4 4 18 1 7 select * from t1 where b<=5 order by a; a b c 1 2 13 2 3 13 3 4 12 4 5 12 7 2 1 8 3 6 9 4 12 14 5 4 15 5 5 16 5 6 17 4 4 18 1 7 select * from t1 where b<=5 and c=0; a b c insert into t1 values (19,4, 0); select * from t1 where b<=5 and c=0; a b c 19 4 0 select * from t1 where b=4 and c<=5 order by a; a b c 17 4 4 19 4 0 select * from t1 where b<=4 and c<=5 order by a; a b c 7 2 1 17 4 4 19 4 0 select * from t1 where b<=5 and c=0 or b<=5 and c=2; a b c 19 4 0 select count(*) from t1 where b = 0; count(*) 0 select count(*) from t1 where b = 1; count(*) 1 drop table t1; CREATE TABLE t1 ( a int unsigned NOT NULL PRIMARY KEY, b int unsigned, c int unsigned, KEY 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; a b c 2 NULL 2 3 NULL NULL select * from t1 use index (bc)order by a; a b c 1 1 1 2 NULL 2 3 NULL NULL 4 4 NULL select * from t1 use index (bc) order by a; a b c 1 1 1 2 NULL 2 3 NULL NULL 4 4 NULL select * from t1 use index (PRIMARY) where b IS NULL order by a; a b c 2 NULL 2 3 NULL NULL select * from t1 use index (bc) where b IS NULL order by a; a b c 2 NULL 2 3 NULL NULL select * from t1 use index (bc) where b IS NULL and c IS NULL order by a; a b c 3 NULL NULL select * from t1 use index (bc) where b IS NULL and c = 2 order by a; a b c 2 NULL 2 select * from t1 use index (bc) where b < 4 order by a; a b c 1 1 1 select * from t1 use index (bc) where b IS NOT NULL order by a; a b c 1 1 1 4 4 NULL drop table t1; CREATE TABLE test1 ( SubscrID int(11) NOT NULL auto_increment, UsrID int(11) NOT NULL default '0', PRIMARY KEY (SubscrID), KEY idx_usrid (UsrID) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1; INSERT INTO test1 VALUES (2,224),(3,224),(1,224); CREATE TABLE test2 ( SbclID int(11) NOT NULL auto_increment, SbcrID int(11) NOT NULL default '0', PRIMARY KEY (SbclID), KEY idx_sbcrid (SbcrID) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1; INSERT INTO test2 VALUES (3,2),(1,1),(2,1),(4,2); select * from test1 order by 1; SubscrID UsrID 1 224 2 224 3 224 select * from test2 order by 1; SbclID SbcrID 1 1 2 1 3 2 4 2 SELECT s.SubscrID,l.SbclID FROM test1 s left JOIN test2 l ON l.SbcrID=s.SubscrID WHERE s.UsrID=224 order by 1, 2; SubscrID SbclID 1 1 1 2 2 3 2 4 3 NULL drop table test1; drop table test2; create table t1 ( pk int primary key, dt datetime not null, da date not null, ye year not null, ti time not null, ts timestamp not null, index(dt), index(da), index(ye), index(ti), index(ts) ) engine=ndb; insert into t1 (pk,dt,da,ye,ti,ts) values (1, '1901-05-05 23:00:59', '1901-05-05', '1901', '23:00:59', '2001-01-01 23:00:59'), (2, '1912-09-05 13:00:59', '1912-09-05', '1912', '13:00:59', '2001-01-01 13:00:59'), (3, '1945-12-31 00:00:00', '1945-12-31', '1945', '00:00:00', '2001-01-01 00:00:00'), (4, '1955-12-31 00:00:00', '1955-12-31', '1955', '00:00:00', '2001-01-01 00:00:00'), (5, '1963-06-06 06:06:06', '1963-06-06', '1963', '06:06:06', '2001-01-01 06:06:06'), (6, '1993-06-06 06:06:06', '1993-06-06', '1993', '06:06:06', '2001-01-01 06:06:06'), (7, '2001-01-01 10:11:10', '2001-01-01', '2001', '10:11:10', '2001-01-01 10:11:10'), (8, '2001-01-01 10:11:11', '2001-01-01', '2001', '10:11:11', '2001-01-01 10:11:11'), (9, '2005-01-31 23:59:59', '2005-01-31', '2005', '23:59:59', '2001-01-01 23:59:59'); select count(*)-9 from t1 use index (dt) where dt > '1900-01-01 00:00:00'; count(*)-9 0 select count(*)-6 from t1 use index (dt) where dt >= '1955-12-31 00:00:00'; count(*)-6 0 select count(*)-5 from t1 use index (dt) where dt > '1955-12-31 00:00:00'; count(*)-5 0 select count(*)-5 from t1 use index (dt) where dt < '1970-03-03 22:22:22'; count(*)-5 0 select count(*)-7 from t1 use index (dt) where dt < '2001-01-01 10:11:11'; count(*)-7 0 select count(*)-8 from t1 use index (dt) where dt <= '2001-01-01 10:11:11'; count(*)-8 0 select count(*)-9 from t1 use index (dt) where dt <= '2055-01-01 00:00:00'; count(*)-9 0 select count(*)-9 from t1 use index (da) where da > '1900-01-01'; count(*)-9 0 select count(*)-6 from t1 use index (da) where da >= '1955-12-31'; count(*)-6 0 select count(*)-5 from t1 use index (da) where da > '1955-12-31'; count(*)-5 0 select count(*)-5 from t1 use index (da) where da < '1970-03-03'; count(*)-5 0 select count(*)-6 from t1 use index (da) where da < '2001-01-01'; count(*)-6 0 select count(*)-8 from t1 use index (da) where da <= '2001-01-02'; count(*)-8 0 select count(*)-9 from t1 use index (da) where da <= '2055-01-01'; count(*)-9 0 select count(*)-9 from t1 use index (ye) where ye > '1900'; count(*)-9 0 select count(*)-6 from t1 use index (ye) where ye >= '1955'; count(*)-6 0 select count(*)-5 from t1 use index (ye) where ye > '1955'; count(*)-5 0 select count(*)-5 from t1 use index (ye) where ye < '1970'; count(*)-5 0 select count(*)-6 from t1 use index (ye) where ye < '2001'; count(*)-6 0 select count(*)-8 from t1 use index (ye) where ye <= '2001'; count(*)-8 0 select count(*)-9 from t1 use index (ye) where ye <= '2055'; count(*)-9 0 select count(*)-9 from t1 use index (ti) where ti >= '00:00:00'; count(*)-9 0 select count(*)-7 from t1 use index (ti) where ti > '00:00:00'; count(*)-7 0 select count(*)-7 from t1 use index (ti) where ti > '05:05:05'; count(*)-7 0 select count(*)-5 from t1 use index (ti) where ti > '06:06:06'; count(*)-5 0 select count(*)-5 from t1 use index (ti) where ti < '10:11:11'; count(*)-5 0 select count(*)-6 from t1 use index (ti) where ti <= '10:11:11'; count(*)-6 0 select count(*)-8 from t1 use index (ti) where ti < '23:59:59'; count(*)-8 0 select count(*)-9 from t1 use index (ti) where ti <= '23:59:59'; count(*)-9 0 select count(*)-9 from t1 use index (ts) where ts >= '2001-01-01 00:00:00'; count(*)-9 0 select count(*)-7 from t1 use index (ts) where ts > '2001-01-01 00:00:00'; count(*)-7 0 select count(*)-7 from t1 use index (ts) where ts > '2001-01-01 05:05:05'; count(*)-7 0 select count(*)-5 from t1 use index (ts) where ts > '2001-01-01 06:06:06'; count(*)-5 0 select count(*)-5 from t1 use index (ts) where ts < '2001-01-01 10:11:11'; count(*)-5 0 select count(*)-6 from t1 use index (ts) where ts <= '2001-01-01 10:11:11'; count(*)-6 0 select count(*)-8 from t1 use index (ts) where ts < '2001-01-01 23:59:59'; count(*)-8 0 select count(*)-9 from t1 use index (ts) where ts <= '2001-01-01 23:59:59'; count(*)-9 0 drop table t1; create table t1 ( a int primary key, s decimal(12), t decimal(12, 5), u decimal(12) unsigned, v decimal(12, 5) unsigned, key (s), key (t), key (u), key (v) ) engine=ndb; insert into t1 values ( 0, -000000000007, -0000061.00003, 000000000061, 0000965.00042), ( 1, -000000000007, -0000061.00042, 000000000061, 0000965.00003), ( 2, -071006035767, 4210253.00024, 000000000001, 0000001.84488), ( 3, 000000007115, 0000000.77607, 000077350625, 0000018.00013), ( 4, -000000068391, -0346486.00000, 000000005071, 0005334.00002), ( 5, -521579890459, -1936874.00001, 000000000154, 0000003.00018), ( 6, -521579890459, -1936874.00018, 000000000154, 0000003.00001), ( 7, 000000000333, 0000051.39140, 000000907958, 0788643.08374), ( 8, 000042731229, 0000009.00000, 000000000009, 6428667.00000), ( 9, -000008159769, 0000918.00004, 000096951421, 7607730.00008); select count(*)- 5 from t1 use index (s) where s < -000000000007; count(*)- 5 0 select count(*)- 7 from t1 use index (s) where s <= -000000000007; count(*)- 7 0 select count(*)- 2 from t1 use index (s) where s = -000000000007; count(*)- 2 0 select count(*)- 5 from t1 use index (s) where s >= -000000000007; count(*)- 5 0 select count(*)- 3 from t1 use index (s) where s > -000000000007; count(*)- 3 0 select count(*)- 4 from t1 use index (t) where t < -0000061.00003; count(*)- 4 0 select count(*)- 5 from t1 use index (t) where t <= -0000061.00003; count(*)- 5 0 select count(*)- 1 from t1 use index (t) where t = -0000061.00003; count(*)- 1 0 select count(*)- 6 from t1 use index (t) where t >= -0000061.00003; count(*)- 6 0 select count(*)- 5 from t1 use index (t) where t > -0000061.00003; count(*)- 5 0 select count(*)- 2 from t1 use index (u) where u < 000000000061; count(*)- 2 0 select count(*)- 4 from t1 use index (u) where u <= 000000000061; count(*)- 4 0 select count(*)- 2 from t1 use index (u) where u = 000000000061; count(*)- 2 0 select count(*)- 8 from t1 use index (u) where u >= 000000000061; count(*)- 8 0 select count(*)- 6 from t1 use index (u) where u > 000000000061; count(*)- 6 0 select count(*)- 5 from t1 use index (v) where v < 0000965.00042; count(*)- 5 0 select count(*)- 6 from t1 use index (v) where v <= 0000965.00042; count(*)- 6 0 select count(*)- 1 from t1 use index (v) where v = 0000965.00042; count(*)- 1 0 select count(*)- 5 from t1 use index (v) where v >= 0000965.00042; count(*)- 5 0 select count(*)- 4 from t1 use index (v) where v > 0000965.00042; count(*)- 4 0 drop table t1; create table t1(a int primary key, b int not null, index(b)); insert into t1 values (1,1), (2,2); set autocommit=0; begin; select count(*) from t1; count(*) 2 ALTER TABLE t1 ADD COLUMN c int; select a from t1 where b = 2; a 2 show tables; Tables_in_test t1 drop table t1;