Debian Codegraph @

mysql-test/suite/ndb/t/ndb_index_ordered.test

raw
1 -- source include/have_ndb.inc
2
3 --disable_warnings
4 drop table if exists t1, test1, test2;
5 --enable_warnings
6
7 #
8 # Simple test to show use of ordered indexes
9 #
10
11 CREATE TABLE t1 (
12 a int unsigned NOT NULL PRIMARY KEY,
13 b int unsigned not null,
14 c int unsigned,
15 KEY(b)
16 ) engine=ndbcluster;
17
18 insert t1 values(1, 2, 3), (2,3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
19 select * from t1 order by b;
20 select * from t1 where b >= 4 order by b;
21 select * from t1 where b = 4 order by b;
22 select * from t1 where b > 4 order by b;
23 select * from t1 where b < 4 order by b;
24 select * from t1 where b <= 4 order by b;
25
26 # Test of reset_bounds
27 select tt1.* from t1 as tt1, t1 as tt2 use index(b) where tt1.b = tt2.b order by tt1.b;
28 select a, b, c from t1 where a!=2 and c=6;
29 select a, b, c from t1 where a!=2 order by a;
30
31 #
32 # Here we should add some "explain select" to verify that the ordered index is
33 # used for these queries.
34 #
35
36 #
37 # Update using ordered index scan
38 #
39
40 update t1 set c = 3 where b = 3;
41 select * from t1 order by a;
42 update t1 set c = 10 where b >= 6;
43 select * from t1 order by a;
44 update t1 set c = 11 where b < 5;
45 select * from t1 order by a;
46 update t1 set c = 12 where b > 0;
47 select * from t1 order by a;
48 update t1 set c = 13 where b <= 3;
49 select * from t1 order by a;
50 update t1 set b = b + 1 where b > 4 and b < 7;
51 select * from t1 order by a;
52 # Update primary key
53 update t1 set a = a + 10 where b > 1 and b < 7;
54 select * from t1 order by a;
55
56 #
57 # Delete using ordered index scan
58 #
59
60 drop table t1;
61
62 CREATE TABLE t1 (
63 a int unsigned NOT NULL PRIMARY KEY,
64 b int unsigned not null,
65 c int unsigned,
66 KEY(b)
67 ) engine=ndbcluster;
68
69 insert t1 values(1, 2, 13), (2,3, 13), (3, 4, 12), (4, 5, 12), (5,6, 12), (6,7, 12);
70
71 delete from t1 where b = 3;
72 select * from t1 order by a;
73 delete from t1 where b >= 6;
74 select * from t1 order by a;
75 delete from t1 where b < 4;
76 select * from t1 order by a;
77 delete from t1 where b > 5;
78 select * from t1 order by a;
79 delete from t1 where b <= 4;
80 select * from t1 order by a;
81
82 drop table t1;
83
84
85 #
86 #multi part key
87 #
88 CREATE TABLE t1 (
89 a int unsigned NOT NULL PRIMARY KEY,
90 b int unsigned not null,
91 c int unsigned not null
92 ) engine = ndb;
93
94 create index a1 on t1 (b, c);
95
96 insert into t1 values (1, 2, 13);
97 insert into t1 values (2,3, 13);
98 insert into t1 values (3, 4, 12);
99 insert into t1 values (4, 5, 12);
100 insert into t1 values (5,6, 12);
101 insert into t1 values (6,7, 12);
102 insert into t1 values (7, 2, 1);
103 insert into t1 values (8,3, 6);
104 insert into t1 values (9, 4, 12);
105 insert into t1 values (14, 5, 4);
106 insert into t1 values (15,5,5);
107 insert into t1 values (16,5, 6);
108 insert into t1 values (17,4,4);
109 insert into t1 values (18,1, 7);
110
111
112
113 select * from t1 order by a;
114 select * from t1 where b<=5 order by a;
115 select * from t1 where b<=5 and c=0;
116 insert into t1 values (19,4, 0);
117 select * from t1 where b<=5 and c=0;
118 select * from t1 where b=4 and c<=5 order by a;
119 select * from t1 where b<=4 and c<=5 order by a;
120 select * from t1 where b<=5 and c=0 or b<=5 and c=2;
121
122 select count(*) from t1 where b = 0;
123 select count(*) from t1 where b = 1;
124 drop table t1;
125
126 #
127 # Indexing NULL values
128 #
129
130 CREATE TABLE t1 (
131 a int unsigned NOT NULL PRIMARY KEY,
132 b int unsigned,
133 c int unsigned,
134 KEY bc(b,c)
135 ) engine = ndb;
136
137 insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
138 select * from t1 use index (bc) where b IS NULL order by a;
139
140 select * from t1 use index (bc)order by a;
141 select * from t1 use index (bc) order by a;
142 select * from t1 use index (PRIMARY) where b IS NULL order by a;
143 select * from t1 use index (bc) where b IS NULL order by a;
144 select * from t1 use index (bc) where b IS NULL and c IS NULL order by a;
145 select * from t1 use index (bc) where b IS NULL and c = 2 order by a;
146 select * from t1 use index (bc) where b < 4 order by a;
147 select * from t1 use index (bc) where b IS NOT NULL order by a;
148 drop table t1;
149
150 #
151 # Order by again, including descending.
152 #
153
154 create table t1 (
155 a int unsigned primary key,
156 b int unsigned,
157 c char(10),
158 key bc (b, c)
159 ) engine=ndb;
160
161 insert into t1 values(1,1,'a'),(2,2,'b'),(3,3,'c'),(4,4,'d'),(5,5,'e');
162 insert into t1 select a*7,10*b,'f' from t1;
163 insert into t1 select a*13,10*b,'g' from t1;
164 insert into t1 select a*17,10*b,'h' from t1;
165 insert into t1 select a*19,10*b,'i' from t1;
166 insert into t1 select a*23,10*b,'j' from t1;
167 insert into t1 select a*29,10*b,'k' from t1;
168 #
169 select b, c from t1 where b <= 10 and c <'f' order by b, c;
170 select b, c from t1 where b <= 10 and c <'f' order by b desc, c desc;
171 #
172 select b, c from t1 where b=4000 and c<'k' order by b, c;
173 select b, c from t1 where b=4000 and c<'k' order by b desc, c desc;
174 select b, c from t1 where 1000<=b and b<=100000 and c<'j' order by b, c;
175 select b, c from t1 where 1000<=b and b<=100000 and c<'j' order by b desc, c desc;
176 #
177 select min(b), max(b) from t1;
178 #
179 drop table t1;
180
181 #
182 # Bug #6435
183 CREATE TABLE test1 (
184 SubscrID int(11) NOT NULL auto_increment,
185 UsrID int(11) NOT NULL default '0',
186 PRIMARY KEY (SubscrID),
187 KEY idx_usrid (UsrID)
188 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
189
190 INSERT INTO test1 VALUES (2,224),(3,224),(1,224);
191
192 CREATE TABLE test2 (
193 SbclID int(11) NOT NULL auto_increment,
194 SbcrID int(11) NOT NULL default '0',
195 PRIMARY KEY (SbclID),
196 KEY idx_sbcrid (SbcrID)
197 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
198
199 INSERT INTO test2 VALUES (3,2),(1,1),(2,1),(4,2);
200 select * from test1 order by 1;
201 select * from test2 order by 1;
202 SELECT s.SubscrID,l.SbclID FROM test1 s left JOIN test2 l ON
203 l.SbcrID=s.SubscrID WHERE s.UsrID=224 order by 1, 2;
204 drop table test1;
205 drop table test2;
206
207 # bug#7424 + bug#7725
208
209 create table t1 (
210 pk int primary key,
211 dt datetime not null,
212 da date not null,
213 ye year not null,
214 ti time not null,
215 ts timestamp not null,
216 index(dt),
217 index(da),
218 index(ye),
219 index(ti),
220 index(ts)
221 ) engine=ndb;
222
223 insert into t1 (pk,dt,da,ye,ti,ts) values
224 (1, '1901-05-05 23:00:59', '1901-05-05', '1901', '23:00:59', '2001-01-01 23:00:59'),
225 (2, '1912-09-05 13:00:59', '1912-09-05', '1912', '13:00:59', '2001-01-01 13:00:59'),
226 (3, '1945-12-31 00:00:00', '1945-12-31', '1945', '00:00:00', '2001-01-01 00:00:00'),
227 (4, '1955-12-31 00:00:00', '1955-12-31', '1955', '00:00:00', '2001-01-01 00:00:00'),
228 (5, '1963-06-06 06:06:06', '1963-06-06', '1963', '06:06:06', '2001-01-01 06:06:06'),
229 (6, '1993-06-06 06:06:06', '1993-06-06', '1993', '06:06:06', '2001-01-01 06:06:06'),
230 (7, '2001-01-01 10:11:10', '2001-01-01', '2001', '10:11:10', '2001-01-01 10:11:10'),
231 (8, '2001-01-01 10:11:11', '2001-01-01', '2001', '10:11:11', '2001-01-01 10:11:11'),
232 (9, '2005-01-31 23:59:59', '2005-01-31', '2005', '23:59:59', '2001-01-01 23:59:59');
233
234 # datetime
235 select count(*)-9 from t1 use index (dt) where dt > '1900-01-01 00:00:00';
236 select count(*)-6 from t1 use index (dt) where dt >= '1955-12-31 00:00:00';
237 select count(*)-5 from t1 use index (dt) where dt > '1955-12-31 00:00:00';
238 select count(*)-5 from t1 use index (dt) where dt < '1970-03-03 22:22:22';
239 select count(*)-7 from t1 use index (dt) where dt < '2001-01-01 10:11:11';
240 select count(*)-8 from t1 use index (dt) where dt <= '2001-01-01 10:11:11';
241 select count(*)-9 from t1 use index (dt) where dt <= '2055-01-01 00:00:00';
242
243 # date
244 select count(*)-9 from t1 use index (da) where da > '1900-01-01';
245 select count(*)-6 from t1 use index (da) where da >= '1955-12-31';
246 select count(*)-5 from t1 use index (da) where da > '1955-12-31';
247 select count(*)-5 from t1 use index (da) where da < '1970-03-03';
248 select count(*)-6 from t1 use index (da) where da < '2001-01-01';
249 select count(*)-8 from t1 use index (da) where da <= '2001-01-02';
250 select count(*)-9 from t1 use index (da) where da <= '2055-01-01';
251
252 # year
253 select count(*)-9 from t1 use index (ye) where ye > '1900';
254 select count(*)-6 from t1 use index (ye) where ye >= '1955';
255 select count(*)-5 from t1 use index (ye) where ye > '1955';
256 select count(*)-5 from t1 use index (ye) where ye < '1970';
257 select count(*)-6 from t1 use index (ye) where ye < '2001';
258 select count(*)-8 from t1 use index (ye) where ye <= '2001';
259 select count(*)-9 from t1 use index (ye) where ye <= '2055';
260
261 # time
262 select count(*)-9 from t1 use index (ti) where ti >= '00:00:00';
263 select count(*)-7 from t1 use index (ti) where ti > '00:00:00';
264 select count(*)-7 from t1 use index (ti) where ti > '05:05:05';
265 select count(*)-5 from t1 use index (ti) where ti > '06:06:06';
266 select count(*)-5 from t1 use index (ti) where ti < '10:11:11';
267 select count(*)-6 from t1 use index (ti) where ti <= '10:11:11';
268 select count(*)-8 from t1 use index (ti) where ti < '23:59:59';
269 select count(*)-9 from t1 use index (ti) where ti <= '23:59:59';
270
271 # timestamp
272 select count(*)-9 from t1 use index (ts) where ts >= '2001-01-01 00:00:00';
273 select count(*)-7 from t1 use index (ts) where ts > '2001-01-01 00:00:00';
274 select count(*)-7 from t1 use index (ts) where ts > '2001-01-01 05:05:05';
275 select count(*)-5 from t1 use index (ts) where ts > '2001-01-01 06:06:06';
276 select count(*)-5 from t1 use index (ts) where ts < '2001-01-01 10:11:11';
277 select count(*)-6 from t1 use index (ts) where ts <= '2001-01-01 10:11:11';
278 select count(*)-8 from t1 use index (ts) where ts < '2001-01-01 23:59:59';
279 select count(*)-9 from t1 use index (ts) where ts <= '2001-01-01 23:59:59';
280
281 drop table t1;
282
283 # decimal (not the new 5.0 thing)
284
285 create table t1 (
286 a int primary key,
287 s decimal(12),
288 t decimal(12, 5),
289 u decimal(12) unsigned,
290 v decimal(12, 5) unsigned,
291 key (s),
292 key (t),
293 key (u),
294 key (v)
295 ) engine=ndb;
296 #
297 insert into t1 values
298 ( 0, -000000000007, -0000061.00003, 000000000061, 0000965.00042),
299 ( 1, -000000000007, -0000061.00042, 000000000061, 0000965.00003),
300 ( 2, -071006035767, 4210253.00024, 000000000001, 0000001.84488),
301 ( 3, 000000007115, 0000000.77607, 000077350625, 0000018.00013),
302 ( 4, -000000068391, -0346486.00000, 000000005071, 0005334.00002),
303 ( 5, -521579890459, -1936874.00001, 000000000154, 0000003.00018),
304 ( 6, -521579890459, -1936874.00018, 000000000154, 0000003.00001),
305 ( 7, 000000000333, 0000051.39140, 000000907958, 0788643.08374),
306 ( 8, 000042731229, 0000009.00000, 000000000009, 6428667.00000),
307 ( 9, -000008159769, 0000918.00004, 000096951421, 7607730.00008);
308 #
309 select count(*)- 5 from t1 use index (s) where s < -000000000007;
310 select count(*)- 7 from t1 use index (s) where s <= -000000000007;
311 select count(*)- 2 from t1 use index (s) where s = -000000000007;
312 select count(*)- 5 from t1 use index (s) where s >= -000000000007;
313 select count(*)- 3 from t1 use index (s) where s > -000000000007;
314 #
315 select count(*)- 4 from t1 use index (t) where t < -0000061.00003;
316 select count(*)- 5 from t1 use index (t) where t <= -0000061.00003;
317 select count(*)- 1 from t1 use index (t) where t = -0000061.00003;
318 select count(*)- 6 from t1 use index (t) where t >= -0000061.00003;
319 select count(*)- 5 from t1 use index (t) where t > -0000061.00003;
320 #
321 select count(*)- 2 from t1 use index (u) where u < 000000000061;
322 select count(*)- 4 from t1 use index (u) where u <= 000000000061;
323 select count(*)- 2 from t1 use index (u) where u = 000000000061;
324 select count(*)- 8 from t1 use index (u) where u >= 000000000061;
325 select count(*)- 6 from t1 use index (u) where u > 000000000061;
326 #
327 select count(*)- 5 from t1 use index (v) where v < 0000965.00042;
328 select count(*)- 6 from t1 use index (v) where v <= 0000965.00042;
329 select count(*)- 1 from t1 use index (v) where v = 0000965.00042;
330 select count(*)- 5 from t1 use index (v) where v >= 0000965.00042;
331 select count(*)- 4 from t1 use index (v) where v > 0000965.00042;
332
333 drop table t1;
334
335 # mysqld 5.0.13 crash, no bug#
336 create table t1 (a int, c varchar(10),
337 primary key using hash (a), index(c)) engine=ndb;
338 insert into t1 (a, c) values (1,'aaa'),(3,'bbb');
339 select count(*) from t1 where c<'bbb';
340 drop table t1;
341
342 # index stats v4: old v2 tests are not meaningful and are removed
343
344 # End of 4.1 tests
345
346 # bug#24039
347
348 create table t1 (a int primary key) engine = ndb;
349 insert into t1 values (1), (2), (3);
350 begin;
351 delete from t1 where a > 1;
352 rollback;
353 select * from t1 order by a;
354 begin;
355 delete from t1 where a > 1;
356 rollback;
357
358 begin;
359 select * from t1 order by a;
360 delete from t1 where a > 2;
361 select * from t1 order by a;
362 delete from t1 where a > 1;
363 select * from t1 order by a;
364 delete from t1 where a > 0;
365 select * from t1 order by a;
366 rollback;
367 select * from t1 order by a;
368 delete from t1;
369 drop table t1;
370
371 # bug#24820 CREATE INDEX ....USING HASH on NDB table creates ordered index, not HASH index
372
373 --error ER_CANT_CREATE_TABLE
374 create table nationaldish (DishID int unsigned NOT NULL AUTO_INCREMENT,
375 CountryCode char(3) NOT NULL,
376 DishTitle varchar(64) NOT NULL,
377 calories smallint unsigned DEFAULT NULL,
378 PRIMARY KEY (DishID),
379 INDEX i USING HASH (countrycode,calories)
380 ) ENGINE=ndbcluster;
381 SHOW WARNINGS;
382
383 create table nationaldish (DishID int unsigned NOT NULL AUTO_INCREMENT,
384 CountryCode char(3) NOT NULL,
385 DishTitle varchar(64) NOT NULL,
386 calories smallint unsigned DEFAULT NULL,
387 PRIMARY KEY (DishID)
388 ) ENGINE=ndbcluster;
389
390 --error ER_UNSUPPORTED_EXTENSION
391 create index i on nationaldish(countrycode,calories) using hash;
392
393 drop table nationaldish;
394
395 # bug#42857 Got error 4541 -IndexBound has no bound information- from NDBCLUSTER
396 # Test that query returns results expected
397
398 drop table if exists t1;
399 create table t1(c1 varchar(20) primary key, c2 char(20)) engine=ndbcluster;
400 insert into t1(c1,c2) values ('ddd','jg');
401 select * from t1 where (c2 < 'b' AND c1 <> 'g') OR (c2 <> 'a' AND c1 <> 'd');
402 drop table t1;
403
404 # bug#45053 xfrm buffer overflow in TUX
405 # 3*1024 = 3072 = max bytes in MySQL key
406 --disable_warnings
407 drop table if exists t1;
408 --enable_warnings
409 create table t1 (
410 a int unsigned,
411 b varchar(1024) not null,
412 primary key using hash (a),
413 index x1 (b)
414 ) engine = ndbcluster
415 default charset = utf8
416 collate = utf8_unicode_ci;
417 insert into t1 values (1,'a'),(2,'y'),(3,'z');
418 select * from t1 where b = 'b';
419 select * from t1 where b = 'a';
420 drop table t1;
421
422 # bug#57396 MRR empty range sets wrong index attr id
423
424 --echo # bug#57396
425 create table t1 (a int not null, b int not null, primary key (b,a)) engine=ndb;
426 insert into t1(a,b) values(0,0);
427 select * from t1 where b < 8 or b >= 8;
428 drop table t1;
429
430 # bug#56853 missing shrink varchar in index bound dist key check
431
432 --echo # bug#56853
433
434 create table t1 (
435 x varchar(16) not null,
436 primary key (x)
437 )
438 character set latin1
439 collate latin1_swedish_ci
440 engine ndb
441 partition by key (x) partitions 8;
442
443 # like % works here
444 insert into t1 (x) values ('aaa');
445 insert into t1 (x) values ('bbb');
446 insert into t1 (x) values ('ccc');
447 insert into t1 (x) values ('ddd');
448 select x from t1 where x like 'aa%';
449 select x from t1 where x like 'aa_';
450 select x from t1 where x like 'bb%';
451 select x from t1 where x like 'bb_';
452 select x from t1 where x like 'cc%';
453 select x from t1 where x like 'cc_';
454 select x from t1 where x like 'dd%';
455 select x from t1 where x like 'dd_';
456 drop table t1;
457
458 # show same bug with like %
459 create table t1 (
460 x varchar(4) not null,
461 primary key (x)
462 )
463 character set latin1
464 collate latin1_swedish_ci
465 engine ndb
466 partition by key (x) partitions 8;
467
468 insert into t1 (x) values ('aaaa');
469 insert into t1 (x) values ('bbbb');
470 insert into t1 (x) values ('cccc');
471 insert into t1 (x) values ('dddd');
472 select x from t1 where x like 'aaa%';
473 select x from t1 where x like 'aaa_';
474 select x from t1 where x like 'bbb%';
475 select x from t1 where x like 'bbb_';
476 select x from t1 where x like 'ccc%';
477 select x from t1 where x like 'ccc_';
478 select x from t1 where x like 'ddd%';
479 select x from t1 where x like 'ddd_';
480 drop table t1;
481
482
483 --echo #
484 --echo # Bug#29474188 MISSING RESULT ROWS FROM A SORTED RESULT
485 --echo # SPECIFYING A 'LIMIT'
486 --echo #
487
488 CREATE TABLE t1 (
489 col_int_key int(11) DEFAULT NULL,
490 col_varchar_256_unique varchar(256) COLLATE latin1_bin DEFAULT NULL,
491 col_int int(11) NOT NULL,
492 pk int(11) NOT NULL,
493 PRIMARY KEY (col_int,pk),
494 KEY col_int_key (col_int_key)
495 ) ENGINE=ndbcluster;
496
497 INSERT INTO t1 VALUES
498 (8, 'Stockholm',12,19),
499 (8, 'Trondheim', 5,13);
500
501 # 'Limit 1000' is a 'magic' number required to trigger the usage
502 # of 'priority queue' sort algorithm. Unfortunately the selected
503 # sort algorithm seems to be invisible from any explain and so.
504 SELECT col_varchar_256_unique AS field1, col_int_key AS field2
505 FROM t1
506 WHERE col_int_key = 8
507 ORDER BY field1,field2 limit 1000;
508
509 # Both a higher and lower limit seems to pass without failure:
510 SELECT col_varchar_256_unique AS field1, col_int_key AS field2
511 FROM t1
512 WHERE col_int_key = 8
513 ORDER BY field1,field2 limit 100;
514
515 SELECT col_varchar_256_unique AS field1, col_int_key AS field2
516 FROM t1
517 WHERE col_int_key = 8
518 ORDER BY field1,field2 limit 10000;
519
520 DROP TABLE t1;