1
-- source include/have_ndb.inc
2
-- source include/not_embedded.inc
5
DROP TABLE IF EXISTS t1, t2, r1;
9
# Basic test to see that batching is working
16
index(b), unique index using hash(c)
19
(1,2,1),(2,3,2),(3,4,3),(4,5,4),
20
(5,2,12),(6,3,11),(7,4,10),(8,5,9),
21
(9,2,8),(10,3,7),(11,4,6),(12,5,5);
23
# batch on primary key
24
create table r1 as select * from t1 where a in (2,8,12);
25
select * from r1 order by a;
28
# batch on ordered index
29
create table r1 as select * from t1 where b in (1,2,5);
30
select * from r1 order by a;
33
# batch on unique hash index
34
create table r1 as select * from t1 where c in (2,8,12);
35
select * from r1 order by a;
39
create table r1 as select * from t1 where a in (2,8) or (a > 11) or (a <= 1);
40
select * from r1 order by a;
43
# batch on primary key, missing values
44
create table r1 as select * from t1 where a in (33,8,12);
45
select * from r1 order by a;
47
create table r1 as select * from t1 where a in (2,33,8,12,34);
48
select * from r1 order by a;
51
# batch on ordered index, missing values
52
create table r1 as select * from t1 where b in (1,33,5);
53
select * from r1 order by a;
55
select * from t1 where b in (1,33,5) order by a;
56
create table r1 as select * from t1 where b in (45,1,33,5,44);
57
select * from r1 order by a;
59
select * from t1 where b in (45,22) order by a;
61
# batch on unique hash index, missing values
62
create table r1 as select * from t1 where c in (2,8,33);
63
select * from r1 order by a;
65
create table r1 as select * from t1 where c in (13,2,8,33,12);
66
select * from r1 order by a;
69
select * from t1 where a in (33,8,12) order by a;
70
select * from t1 where a in (33,34,35) order by a;
71
select * from t1 where a in (2,8) or (a > 11) or (a <= 1) order by a;
72
select * from t1 where b in (6,7) or (b <= 5) or (b >= 10) order by b,a;
73
select * from t1 where c in (13,2,8,33,12) order by c,a;
77
# Somewhat more complicated
86
primary key (a,b,c,d), index (d)
90
(1,2,1,1,1),(2,3,2,3,1),(3,4,3,1,1),(4,5,4,7,1),
91
(5,2,12,12,1),(6,3,11,1,1),(7,4,10,3,1),(8,5,9,5,1),
92
(9,2,8,6,1),(10,3,7,5,1),(11,4,6,3,1),(12,5,5,2,1),
98
# batch on primary key
99
create table r1 as select * from t1
100
where a=1 and b=2 and c=1 and d in (1,4,3,2);
101
select * from r1 order by a,b,c,d;
104
# batched update ordered index, one value for all
105
update t1 set e = 100
107
select * from t1 where d in (12,6,7) order by a,b,c,d;
108
select * from t1 where d not in (12,6,7) and e = 100;
110
# batched update primary key, one value for all
119
where a=1 and b=2 and c=1 and d in (1,4,3,2)
123
where not (a=1 and b=2 and c=1 and d in (1,4,3,2))
127
# batched update ordered index, different values
136
select * from t1 where d in (12,6,7) order by a,b,c,d;
138
# batched update primary key, different values
153
where a=1 and b=2 and c=1 and d in (1,4,3,2)
157
delete from t1 where d in (12,6,7);
158
select * from t1 where d in (12,6,7);
164
a int not null primary key,
172
insert into t1 values
190
create table t2 as select * from t1 where a in (5,6,7,8,9,10);
191
select * from t2 order by a;
194
create table t2 as select * from t1 where b in (5,6,7,8,9,10);
195
select * from t2 order by a;
198
create table t2 as select * from t1 where c in (5,6,7,8,9,10);
199
select * from t2 order by a;
209
c datetime default NULL,
214
INSERT INTO t1 VALUES
215
(406989,67,'2006-02-23 17:08:46'), (150078,67,'2005-10-26 11:17:45'),
216
(406993,67,'2006-02-27 11:20:57'), (245655,67,'2005-12-08 15:59:08'),
217
(406994,67,'2006-02-27 11:26:46'), (256,67,NULL),
218
(398341,67,'2006-02-20 04:48:44'), (254,67,NULL),(1120,67,NULL),
219
(406988,67,'2006-02-23 17:07:22'), (255,67,NULL),
220
(398340,67,'2006-02-20 04:38:53'),(406631,67,'2006-02-23 10:49:42'),
221
(245653,67,'2005-12-08 15:59:07'),(406992,67,'2006-02-24 16:47:18'),
222
(245654,67,'2005-12-08 15:59:08'),(406995,67,'2006-02-28 11:55:00'),
223
(127261,67,'2005-10-13 12:17:58'),(406991,67,'2006-02-24 16:42:32'),
224
(245652,67,'2005-12-08 15:58:27'),(398545,67,'2006-02-20 04:53:13'),
225
(154504,67,'2005-10-28 11:53:01'),(9199,67,NULL),(1,67,'2006-02-23 15:01:35'),
226
(223456,67,NULL),(4101,67,NULL),(1133,67,NULL),
227
(406990,67,'2006-02-23 18:01:45'),(148815,67,'2005-10-25 15:34:17'),
228
(148812,67,'2005-10-25 15:30:01'),(245651,67,'2005-12-08 15:58:27'),
229
(154503,67,'2005-10-28 11:52:38');
231
create table t11 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 asc;
232
create table t12 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 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc;
236
select * from t11 order by 1,2,3;
237
select * from t12 order by 1,2,3;
238
select * from t21 order by 1,2,3;
239
select * from t22 order by 1,2,3;
240
DROP TABLE t1, t11, t12, t21, t22;