ndb_read_multi_range.result 10.4 KB
Newer Older
unknown's avatar
unknown committed
1
DROP TABLE IF EXISTS t1, t2, r1;
unknown's avatar
unknown committed
2 3 4 5 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
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);
create table r1 as select * from t1 where a in (2,8,12);
select * from r1 order by a;
a	b	c
2	3	2
8	5	9
12	5	5
drop table r1;
create table r1 as select * from t1 where b in (1,2,5);
select * from r1 order by a;
a	b	c
1	2	1
4	5	4
5	2	12
8	5	9
9	2	8
12	5	5
drop table r1;
create table r1 as select * from t1 where c in (2,8,12);
select * from r1 order by a;
a	b	c
2	3	2
5	2	12
9	2	8
drop table r1;
36
create table r1 as select * from t1 where a in (2,8) or (a > 11) or (a <= 1);
unknown's avatar
unknown committed
37 38
select * from r1 order by a;
a	b	c
39 40
1	2	1
2	3	2
unknown's avatar
unknown committed
41 42 43
8	5	9
12	5	5
drop table r1;
44 45
create table r1 as select * from t1 where a in (33,8,12);
select * from r1 order by a;
unknown's avatar
unknown committed
46 47 48
a	b	c
8	5	9
12	5	5
49
drop table r1;
unknown's avatar
unknown committed
50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
create table r1 as select * from t1 where a in (2,33,8,12,34);
select * from r1 order by a;
a	b	c
2	3	2
8	5	9
12	5	5
drop table r1;
create table r1 as select * from t1 where b in (1,33,5);
select * from r1 order by a;
a	b	c
4	5	4
8	5	9
12	5	5
drop table r1;
select * from t1 where b in (1,33,5) order by a;
a	b	c
4	5	4
8	5	9
12	5	5
create table r1 as select * from t1 where b in (45,1,33,5,44);
select * from r1 order by a;
a	b	c
4	5	4
8	5	9
12	5	5
drop table r1;
select * from t1 where b in (45,22) order by a;
a	b	c
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
create table r1 as select * from t1 where c in (2,8,33);
select * from r1 order by a;
a	b	c
2	3	2
9	2	8
drop table r1;
create table r1 as select * from t1 where c in (13,2,8,33,12);
select * from r1 order by a;
a	b	c
2	3	2
5	2	12
9	2	8
drop table r1;
select * from t1 where a in (33,8,12) order by a;
a	b	c
8	5	9
12	5	5
select * from t1 where a in (33,34,35) order by a;
a	b	c
select * from t1 where a in (2,8) or (a > 11) or (a <= 1) order by a;
a	b	c
1	2	1
2	3	2
8	5	9
12	5	5
select * from t1 where b in (6,7) or (b <= 5) or (b >= 10) order by b,a;
a	b	c
1	2	1
5	2	12
9	2	8
2	3	2
6	3	11
10	3	7
3	4	3
7	4	10
11	4	6
4	5	4
8	5	9
12	5	5
select * from t1 where c in (13,2,8,33,12) order by c,a;
a	b	c
2	3	2
9	2	8
5	2	12
unknown's avatar
unknown committed
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 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 203 204 205 206 207 208 209 210 211 212 213 214
drop table t1;
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);
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;
a	b	c	d	e
1	2	1	1	1
1	2	1	2	1
1	2	1	3	1
1	2	1	4	1
drop table r1;
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;
a	b	c	d	e
4	5	4	7	100
5	2	12	12	100
9	2	8	6	100
select * from t1 where d not in (12,6,7) and e = 100;
a	b	c	d	e
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;
a	b	c	d	e
1	2	1	1	101
1	2	1	2	101
1	2	1	3	101
1	2	1	4	101
select * 
from t1 
where not (a=1 and b=2 and c=1 and d in (1,4,3,2))
and e=101;
a	b	c	d	e
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;
a	b	c	d	e
4	5	4	7	107
5	2	12	12	112
9	2	8	6	106
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;
a	b	c	d	e
1	2	1	1	111
1	2	1	2	222
1	2	1	3	333
1	2	1	4	444
delete from t1 where d in (12,6,7);
select * from t1 where d in (12,6,7);
a	b	c	d	e
drop table t1;
215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265
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;
a	b	c	d
5	NULL	5	NULL
6	6	6	NULL
7	NULL	NULL	NULL
8	8	NULL	NULL
9	NULL	9	9
10	10	10	10
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;
a	b	c	d
6	6	6	NULL
8	8	NULL	NULL
10	10	10	10
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;
a	b	c	d
5	NULL	5	NULL
6	6	6	NULL
9	NULL	9	9
10	10	10	10
drop table t2;
drop table t1;
unknown's avatar
unknown committed
266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288
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');
289 290
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;
unknown's avatar
unknown committed
291
create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc;
292
create table t22 engine = ndbcluster select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc;
unknown's avatar
unknown committed
293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368
select * from t11 order by 1,2,3;
a	b	c
254	67	NULL
255	67	NULL
256	67	NULL
1120	67	NULL
1133	67	NULL
4101	67	NULL
9199	67	NULL
223456	67	NULL
select * from t12 order by 1,2,3;
a	b	c
254	67	NULL
255	67	NULL
256	67	NULL
1120	67	NULL
1133	67	NULL
4101	67	NULL
9199	67	NULL
223456	67	NULL
select * from t21 order by 1,2,3;
a	b	c
1	67	2006-02-23 15:01:35
254	67	NULL
255	67	NULL
256	67	NULL
1120	67	NULL
1133	67	NULL
4101	67	NULL
9199	67	NULL
223456	67	NULL
245651	67	2005-12-08 15:58:27
245652	67	2005-12-08 15:58:27
245653	67	2005-12-08 15:59:07
245654	67	2005-12-08 15:59:08
245655	67	2005-12-08 15:59:08
398340	67	2006-02-20 04:38:53
398341	67	2006-02-20 04:48:44
398545	67	2006-02-20 04:53:13
406631	67	2006-02-23 10:49:42
406988	67	2006-02-23 17:07:22
406989	67	2006-02-23 17:08:46
406990	67	2006-02-23 18:01:45
406991	67	2006-02-24 16:42:32
406992	67	2006-02-24 16:47:18
406993	67	2006-02-27 11:20:57
406994	67	2006-02-27 11:26:46
406995	67	2006-02-28 11:55:00
select * from t22 order by 1,2,3;
a	b	c
1	67	2006-02-23 15:01:35
254	67	NULL
255	67	NULL
256	67	NULL
1120	67	NULL
1133	67	NULL
4101	67	NULL
9199	67	NULL
223456	67	NULL
245651	67	2005-12-08 15:58:27
245652	67	2005-12-08 15:58:27
245653	67	2005-12-08 15:59:07
245654	67	2005-12-08 15:59:08
245655	67	2005-12-08 15:59:08
398340	67	2006-02-20 04:38:53
398341	67	2006-02-20 04:48:44
398545	67	2006-02-20 04:53:13
406631	67	2006-02-23 10:49:42
406988	67	2006-02-23 17:07:22
406989	67	2006-02-23 17:08:46
406990	67	2006-02-23 18:01:45
406991	67	2006-02-24 16:42:32
406992	67	2006-02-24 16:47:18
406993	67	2006-02-27 11:20:57
406994	67	2006-02-27 11:26:46
406995	67	2006-02-28 11:55:00
369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429
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;
a
255
256
update t22 set c = '2005-12-08 15:58:27' where a = 255;
select * from t22 order by 1,2,3;
a	b	c
1	67	2006-02-23 15:01:35
254	67	NULL
255	67	2005-12-08 15:58:27
256	67	NULL
1120	67	NULL
1133	67	NULL
4101	67	NULL
9199	67	NULL
223456	67	NULL
245651	67	2005-12-08 15:58:27
245652	67	2005-12-08 15:58:27
245653	67	2005-12-08 15:59:07
245654	67	2005-12-08 15:59:08
245655	67	2005-12-08 15:59:08
398340	67	2006-02-20 04:38:53
398341	67	2006-02-20 04:48:44
398545	67	2006-02-20 04:53:13
406631	67	2006-02-23 10:49:42
406988	67	2006-02-23 17:07:22
406989	67	2006-02-23 17:08:46
406990	67	2006-02-23 18:01:45
406991	67	2006-02-24 16:42:32
406992	67	2006-02-24 16:47:18
406993	67	2006-02-27 11:20:57
406994	67	2006-02-27 11:26:46
406995	67	2006-02-28 11:55:00
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;
a	b	c
256	67	NULL
delete from t22 where a > 245651;
update t22 set b = a + 1;
select * from t22 order by 1,2,3;
a	b	c
1	2	2006-02-23 15:01:35
254	255	NULL
255	256	2005-12-08 15:58:27
256	257	NULL
1120	1121	NULL
1133	1134	NULL
4101	4102	NULL
9199	9200	NULL
223456	223457	NULL
245651	245652	2005-12-08 15:58:27
select c, count(*)
from t21 
inner join t22 using (a)
where t22.b in (2,256,257,1121,1134,4102,9200,223457,245652)
group by c
order by c;
c	count(*)
NULL	7
2005-12-08 15:58:27	1
2006-02-23 15:01:35	1
unknown's avatar
unknown committed
430
DROP TABLE t1, t11, t12, t21, t22;
431 432 433 434 435 436 437 438 439 440 441 442 443 444
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');
id	tag	doc	type
SELECT id, tag, doc, type FROM t1 WHERE id IN ('flipper','sakila');
id	tag	doc	type
sakila	1	Some text goes here	text
DROP TABLE t1;
445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461
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)
) ENGINE=MyISAM DEFAULT CHARSET=ascii CHECKSUM=1;
CREATE TRIGGER testtrigger
AFTER UPDATE ON t1 FOR EACH ROW BEGIN
REPLACE INTO t2 SELECT * FROM t1 WHERE t1.var1 = NEW.var1;END|
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;