ndb_read_multi_range.test 8.51 KB
Newer Older
1
-- source include/have_ndb.inc
2
-- source include/not_embedded.inc
3 4

--disable_warnings
5
DROP TABLE IF EXISTS t1, t2, r1;
6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
--enable_warnings

#
# Basic test to see that batching is working
#

create table t1 (
  a int primary key,
  b int not null,
  c int not null,
  index(b), unique index using hash(c)
) engine = ndb;
insert into t1 values
  (1,2,1),(2,3,2),(3,4,3),(4,5,4),
  (5,2,12),(6,3,11),(7,4,10),(8,5,9),
  (9,2,8),(10,3,7),(11,4,6),(12,5,5);

# batch on primary key
create table r1 as select * from t1 where a in (2,8,12);
select * from r1 order by a;
drop table r1;

# batch on ordered index
create table r1 as select * from t1 where b in (1,2,5);
select * from r1 order by a;
drop table r1;

# batch on unique hash index
create table r1 as select * from t1 where c in (2,8,12);
select * from r1 order by a;
drop table r1;

38 39 40 41 42
# batch mixed
create table r1 as select * from t1 where a in (2,8) or (a > 11) or (a <= 1);
select * from r1 order by a;
drop table r1;

43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
# batch on primary key, missing values
create table r1 as select * from t1 where a in (33,8,12);
select * from r1 order by a;
drop table r1;
create table r1 as select * from t1 where a in (2,33,8,12,34);
select * from r1 order by a;
drop table r1;

# batch on ordered index, missing values
create table r1 as select * from t1 where b in (1,33,5);
select * from r1 order by a;
drop table r1;
select * from t1 where b in (1,33,5) order by a;
create table r1 as select * from t1 where b in (45,1,33,5,44);
select * from r1 order by a;
drop table r1;
select * from t1 where b in (45,22) order by a;

# batch on unique hash index, missing values
62 63 64 65 66 67
create table r1 as select * from t1 where c in (2,8,33);
select * from r1 order by a;
drop table r1;
create table r1 as select * from t1 where c in (13,2,8,33,12);
select * from r1 order by a;
drop table r1;
68

69 70 71 72 73
select * from t1 where a in (33,8,12) order by a;
select * from t1 where a in (33,34,35) order by a;
select * from t1 where a in (2,8) or (a > 11) or (a <= 1) order by a;
select * from t1 where b in (6,7) or (b <= 5) or (b >= 10) order by b,a;
select * from t1 where c in (13,2,8,33,12) order by c,a;
74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160
drop table t1;

#
# Somewhat more complicated
#

create table t1 (
  a int not null,
  b int not null,
  c int not null,
  d int not null,
  e int not null,
  primary key (a,b,c,d), index (d)
) engine = ndb;

insert into t1 values
  (1,2,1,1,1),(2,3,2,3,1),(3,4,3,1,1),(4,5,4,7,1),
  (5,2,12,12,1),(6,3,11,1,1),(7,4,10,3,1),(8,5,9,5,1),
  (9,2,8,6,1),(10,3,7,5,1),(11,4,6,3,1),(12,5,5,2,1),
  (1,2,1,2,1),
  (1,2,1,3,1),
  (1,2,1,4,1),
  (1,2,1,5,1);

# batch on primary key
create table r1 as select * from t1
  where a=1 and b=2 and c=1 and d in (1,4,3,2);
select * from r1 order by a,b,c,d;
drop table r1;

# batched update ordered index, one value for all
update t1 set e = 100
  where d in (12,6,7);
select * from t1 where d in (12,6,7) order by a,b,c,d;
select * from t1 where d not in (12,6,7) and e = 100;

# batched update primary key, one value for all
update t1 
  set e = 101
  where a=1 and 
        b=2 and 
        c=1 and 
        d in (1,4,3,2);
select * 
  from t1
  where a=1 and b=2 and c=1 and d in (1,4,3,2)
  order by a,b,c,d;
select * 
  from t1 
  where not (a=1 and b=2 and c=1 and d in (1,4,3,2))
        and e=101;


# batched update ordered index, different values
update t1 
  set e = 
    (case d
      when 12 then 112
      when 6  then 106
      when 7  then 107
    end)
  where d in (12,6,7);
select * from t1 where d in (12,6,7) order by a,b,c,d;

# batched update primary key, different values
update t1 
  set e = 
    (case d
      when 1 then 111
      when 4 then 444
      when 3 then 333
      when 2 then 222
    end)
  where a=1 and 
        b=2 and 
        c=1 and 
        d in (1,4,3,2);
select * 
  from t1
  where a=1 and b=2 and c=1 and d in (1,4,3,2)
  order by a,b,c,d;

# batched delete
delete from t1 where d in (12,6,7);
select * from t1 where d in (12,6,7);

drop table t1;
161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202

# null handling
create table t1 (
  a int not null primary key,
  b int,
  c int,
  d int,
  unique index (b),
  index(c)
) engine = ndb;

insert into t1 values
  (1,null,1,1),
  (2,2,2,2),
  (3,null,null,3),
  (4,4,null,4),
  (5,null,5,null),
  (6,6,6,null),
  (7,null,null,null),
  (8,8,null,null),
  (9,null,9,9),
  (10,10,10,10),
  (11,null,null,11),
  (12,12,null,12),
  (13,null,13,null),
  (14,14,14,null),
  (15,null,null,null),
  (16,16,null,null);

create table t2 as select * from t1 where a in (5,6,7,8,9,10);
select * from t2 order by a;
drop table t2;

create table t2 as select * from t1 where b in (5,6,7,8,9,10);
select * from t2 order by a;
drop table t2;

create table t2 as select * from t1 where c in (5,6,7,8,9,10);
select * from t2 order by a;
drop table t2;

drop table t1;
203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230

# bug17729

CREATE TABLE t1 (
  a int(11) NOT NULL,
  b int(11) NOT NULL,
  c datetime default NULL,
  PRIMARY KEY  (a),
  KEY idx_bc (b,c)
) ENGINE=ndbcluster;

INSERT INTO t1 VALUES 
(406989,67,'2006-02-23 17:08:46'), (150078,67,'2005-10-26 11:17:45'),
(406993,67,'2006-02-27 11:20:57'), (245655,67,'2005-12-08 15:59:08'),
(406994,67,'2006-02-27 11:26:46'), (256,67,NULL),
(398341,67,'2006-02-20 04:48:44'), (254,67,NULL),(1120,67,NULL),
(406988,67,'2006-02-23 17:07:22'), (255,67,NULL),
(398340,67,'2006-02-20 04:38:53'),(406631,67,'2006-02-23 10:49:42'),
(245653,67,'2005-12-08 15:59:07'),(406992,67,'2006-02-24 16:47:18'),
(245654,67,'2005-12-08 15:59:08'),(406995,67,'2006-02-28 11:55:00'),
(127261,67,'2005-10-13 12:17:58'),(406991,67,'2006-02-24 16:42:32'),
(245652,67,'2005-12-08 15:58:27'),(398545,67,'2006-02-20 04:53:13'),
(154504,67,'2005-10-28 11:53:01'),(9199,67,NULL),(1,67,'2006-02-23 15:01:35'),
(223456,67,NULL),(4101,67,NULL),(1133,67,NULL),
(406990,67,'2006-02-23 18:01:45'),(148815,67,'2005-10-25 15:34:17'),
(148812,67,'2005-10-25 15:30:01'),(245651,67,'2005-12-08 15:58:27'),
(154503,67,'2005-10-28 11:52:38');

231 232
create table t11 engine = ndbcluster select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 asc;
create table t12 engine = ndbcluster select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 desc;
233
create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc;
234
create table t22 engine = ndbcluster select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc;
235 236 237 238 239

select * from t11 order by 1,2,3;
select * from t12 order by 1,2,3;
select * from t21 order by 1,2,3;
select * from t22 order by 1,2,3;
240 241 242 243 244 245 246 247 248 249 250 251

# join tests
select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null order by t12.a;

update t22 set c = '2005-12-08 15:58:27' where a = 255;
select * from t22 order by 1,2,3;
select t21.* from t21,t22 where t21.a = t22.a and 
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;

delete from t22 where a > 245651;
update t22 set b = a + 1;
select * from t22 order by 1,2,3;
252
select t21.c, count(*)
253 254 255
from t21 
inner join t22 using (a)
where t22.b in (2,256,257,1121,1134,4102,9200,223457,245652)
256 257
group by t21.c
order by t21.c;
258

259
DROP TABLE t1, t11, t12, t21, t22;
260 261 262 263 264 265 266 267 268 269 270 271 272 273 274

# bug#19956
CREATE TABLE t1 (id varchar(255) NOT NULL,
		 tag int(11) NOT NULL,
                 doc text NOT NULL,
                 type varchar(150) NOT NULL,
                 modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                 PRIMARY KEY (id)
                ) ENGINE=ndbcluster;

INSERT INTO t1 VALUES ('sakila',1,'Some text goes here','text',CURRENT_TIMESTAMP);
SELECT id, tag, doc, type FROM t1 WHERE id IN ('flipper','orka');
SELECT id, tag, doc, type FROM t1 WHERE id IN ('flipper','sakila');

DROP TABLE t1;
275 276 277 278 279 280 281 282 283 284 285 286 287

#bug#25522
CREATE TABLE t1 (
       var1 int(2) NOT NULL,
       var2 int(2) NOT NULL,
       PRIMARY KEY  (var1)
     ) ENGINE=ndbcluster DEFAULT CHARSET=ascii CHECKSUM=1;


CREATE TABLE t2 (
       var1 int(2) NOT NULL,
       var2 int(2) NOT NULL,
       PRIMARY KEY  (var1)
288
     ) ENGINE=ndbcluster DEFAULT CHARSET=ascii CHECKSUM=1;
289 290 291 292 293 294 295 296 297 298 299 300 301 302 303


DELIMITER |;
CREATE TRIGGER testtrigger
     AFTER UPDATE ON t1 FOR EACH ROW BEGIN
     REPLACE INTO t2 SELECT * FROM t1 WHERE t1.var1 = NEW.var1;END|
DELIMITER ;|

INSERT INTO t1 VALUES (1,1),(2,2),(3,3);

UPDATE t1 SET var2 = 9 WHERE var1 IN(1,2,3);

DROP TRIGGER testtrigger;

DROP TABLE t1, t2;
304 305

#bug#25821
306 307 308 309 310 311 312 313
create table t1 (a int, b int, primary key (a), key ab (a,b)) engine=ndbcluster;

insert into t1 values (1,1), (10,10);

select * from t1 use index (ab) where a in(1,10) order by a;

create table t2 (a int, b int, primary key (a,b)) engine=ndbcluster
partition by key(a);
314 315 316

insert into t2 values (1,1), (10,10);

317
select * from t2 where a in (1,10) order by a;
318

319
drop table t1, t2;