Debian Codegraph @

mysql-test/suite/ndb/r/ndb_index_ordered.result

raw
1 drop table if exists t1, test1, test2;
2 CREATE TABLE t1 (
3 a int unsigned NOT NULL PRIMARY KEY,
4 b int unsigned not null,
5 c int unsigned,
6 KEY(b)
7 ) engine=ndbcluster;
8 insert t1 values(1, 2, 3), (2,3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
9 select * from t1 order by b;
10 a b c
11 1 2 3
12 2 3 5
13 3 4 6
14 4 5 8
15 5 6 2
16 6 7 2
17 select * from t1 where b >= 4 order by b;
18 a b c
19 3 4 6
20 4 5 8
21 5 6 2
22 6 7 2
23 select * from t1 where b = 4 order by b;
24 a b c
25 3 4 6
26 select * from t1 where b > 4 order by b;
27 a b c
28 4 5 8
29 5 6 2
30 6 7 2
31 select * from t1 where b < 4 order by b;
32 a b c
33 1 2 3
34 2 3 5
35 select * from t1 where b <= 4 order by b;
36 a b c
37 1 2 3
38 2 3 5
39 3 4 6
40 select tt1.* from t1 as tt1, t1 as tt2 use index(b) where tt1.b = tt2.b order by tt1.b;
41 a b c
42 1 2 3
43 2 3 5
44 3 4 6
45 4 5 8
46 5 6 2
47 6 7 2
48 select a, b, c from t1 where a!=2 and c=6;
49 a b c
50 3 4 6
51 select a, b, c from t1 where a!=2 order by a;
52 a b c
53 1 2 3
54 3 4 6
55 4 5 8
56 5 6 2
57 6 7 2
58 update t1 set c = 3 where b = 3;
59 select * from t1 order by a;
60 a b c
61 1 2 3
62 2 3 3
63 3 4 6
64 4 5 8
65 5 6 2
66 6 7 2
67 update t1 set c = 10 where b >= 6;
68 select * from t1 order by a;
69 a b c
70 1 2 3
71 2 3 3
72 3 4 6
73 4 5 8
74 5 6 10
75 6 7 10
76 update t1 set c = 11 where b < 5;
77 select * from t1 order by a;
78 a b c
79 1 2 11
80 2 3 11
81 3 4 11
82 4 5 8
83 5 6 10
84 6 7 10
85 update t1 set c = 12 where b > 0;
86 select * from t1 order by a;
87 a b c
88 1 2 12
89 2 3 12
90 3 4 12
91 4 5 12
92 5 6 12
93 6 7 12
94 update t1 set c = 13 where b <= 3;
95 select * from t1 order by a;
96 a b c
97 1 2 13
98 2 3 13
99 3 4 12
100 4 5 12
101 5 6 12
102 6 7 12
103 update t1 set b = b + 1 where b > 4 and b < 7;
104 select * from t1 order by a;
105 a b c
106 1 2 13
107 2 3 13
108 3 4 12
109 4 6 12
110 5 7 12
111 6 7 12
112 update t1 set a = a + 10 where b > 1 and b < 7;
113 select * from t1 order by a;
114 a b c
115 5 7 12
116 6 7 12
117 11 2 13
118 12 3 13
119 13 4 12
120 14 6 12
121 drop table t1;
122 CREATE TABLE t1 (
123 a int unsigned NOT NULL PRIMARY KEY,
124 b int unsigned not null,
125 c int unsigned,
126 KEY(b)
127 ) engine=ndbcluster;
128 insert t1 values(1, 2, 13), (2,3, 13), (3, 4, 12), (4, 5, 12), (5,6, 12), (6,7, 12);
129 delete from t1 where b = 3;
130 select * from t1 order by a;
131 a b c
132 1 2 13
133 3 4 12
134 4 5 12
135 5 6 12
136 6 7 12
137 delete from t1 where b >= 6;
138 select * from t1 order by a;
139 a b c
140 1 2 13
141 3 4 12
142 4 5 12
143 delete from t1 where b < 4;
144 select * from t1 order by a;
145 a b c
146 3 4 12
147 4 5 12
148 delete from t1 where b > 5;
149 select * from t1 order by a;
150 a b c
151 3 4 12
152 4 5 12
153 delete from t1 where b <= 4;
154 select * from t1 order by a;
155 a b c
156 4 5 12
157 drop table t1;
158 CREATE TABLE t1 (
159 a int unsigned NOT NULL PRIMARY KEY,
160 b int unsigned not null,
161 c int unsigned not null
162 ) engine = ndb;
163 create index a1 on t1 (b, c);
164 insert into t1 values (1, 2, 13);
165 insert into t1 values (2,3, 13);
166 insert into t1 values (3, 4, 12);
167 insert into t1 values (4, 5, 12);
168 insert into t1 values (5,6, 12);
169 insert into t1 values (6,7, 12);
170 insert into t1 values (7, 2, 1);
171 insert into t1 values (8,3, 6);
172 insert into t1 values (9, 4, 12);
173 insert into t1 values (14, 5, 4);
174 insert into t1 values (15,5,5);
175 insert into t1 values (16,5, 6);
176 insert into t1 values (17,4,4);
177 insert into t1 values (18,1, 7);
178 select * from t1 order by a;
179 a b c
180 1 2 13
181 2 3 13
182 3 4 12
183 4 5 12
184 5 6 12
185 6 7 12
186 7 2 1
187 8 3 6
188 9 4 12
189 14 5 4
190 15 5 5
191 16 5 6
192 17 4 4
193 18 1 7
194 select * from t1 where b<=5 order by a;
195 a b c
196 1 2 13
197 2 3 13
198 3 4 12
199 4 5 12
200 7 2 1
201 8 3 6
202 9 4 12
203 14 5 4
204 15 5 5
205 16 5 6
206 17 4 4
207 18 1 7
208 select * from t1 where b<=5 and c=0;
209 a b c
210 insert into t1 values (19,4, 0);
211 select * from t1 where b<=5 and c=0;
212 a b c
213 19 4 0
214 select * from t1 where b=4 and c<=5 order by a;
215 a b c
216 17 4 4
217 19 4 0
218 select * from t1 where b<=4 and c<=5 order by a;
219 a b c
220 7 2 1
221 17 4 4
222 19 4 0
223 select * from t1 where b<=5 and c=0 or b<=5 and c=2;
224 a b c
225 19 4 0
226 select count(*) from t1 where b = 0;
227 count(*)
228 0
229 select count(*) from t1 where b = 1;
230 count(*)
231 1
232 drop table t1;
233 CREATE TABLE t1 (
234 a int unsigned NOT NULL PRIMARY KEY,
235 b int unsigned,
236 c int unsigned,
237 KEY bc(b,c)
238 ) engine = ndb;
239 insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
240 select * from t1 use index (bc) where b IS NULL order by a;
241 a b c
242 2 NULL 2
243 3 NULL NULL
244 select * from t1 use index (bc)order by a;
245 a b c
246 1 1 1
247 2 NULL 2
248 3 NULL NULL
249 4 4 NULL
250 select * from t1 use index (bc) order by a;
251 a b c
252 1 1 1
253 2 NULL 2
254 3 NULL NULL
255 4 4 NULL
256 select * from t1 use index (PRIMARY) where b IS NULL order by a;
257 a b c
258 2 NULL 2
259 3 NULL NULL
260 select * from t1 use index (bc) where b IS NULL order by a;
261 a b c
262 2 NULL 2
263 3 NULL NULL
264 select * from t1 use index (bc) where b IS NULL and c IS NULL order by a;
265 a b c
266 3 NULL NULL
267 select * from t1 use index (bc) where b IS NULL and c = 2 order by a;
268 a b c
269 2 NULL 2
270 select * from t1 use index (bc) where b < 4 order by a;
271 a b c
272 1 1 1
273 select * from t1 use index (bc) where b IS NOT NULL order by a;
274 a b c
275 1 1 1
276 4 4 NULL
277 drop table t1;
278 create table t1 (
279 a int unsigned primary key,
280 b int unsigned,
281 c char(10),
282 key bc (b, c)
283 ) engine=ndb;
284 insert into t1 values(1,1,'a'),(2,2,'b'),(3,3,'c'),(4,4,'d'),(5,5,'e');
285 insert into t1 select a*7,10*b,'f' from t1;
286 insert into t1 select a*13,10*b,'g' from t1;
287 insert into t1 select a*17,10*b,'h' from t1;
288 insert into t1 select a*19,10*b,'i' from t1;
289 insert into t1 select a*23,10*b,'j' from t1;
290 insert into t1 select a*29,10*b,'k' from t1;
291 select b, c from t1 where b <= 10 and c <'f' order by b, c;
292 b c
293 1 a
294 2 b
295 3 c
296 4 d
297 5 e
298 select b, c from t1 where b <= 10 and c <'f' order by b desc, c desc;
299 b c
300 5 e
301 4 d
302 3 c
303 2 b
304 1 a
305 select b, c from t1 where b=4000 and c<'k' order by b, c;
306 b c
307 4000 h
308 4000 i
309 4000 i
310 4000 i
311 4000 j
312 4000 j
313 4000 j
314 4000 j
315 4000 j
316 4000 j
317 select b, c from t1 where b=4000 and c<'k' order by b desc, c desc;
318 b c
319 4000 j
320 4000 j
321 4000 j
322 4000 j
323 4000 j
324 4000 j
325 4000 i
326 4000 i
327 4000 i
328 4000 h
329 select b, c from t1 where 1000<=b and b<=100000 and c<'j' order by b, c;
330 b c
331 1000 h
332 1000 i
333 1000 i
334 1000 i
335 2000 h
336 2000 i
337 2000 i
338 2000 i
339 3000 h
340 3000 i
341 3000 i
342 3000 i
343 4000 h
344 4000 i
345 4000 i
346 4000 i
347 5000 h
348 5000 i
349 5000 i
350 5000 i
351 10000 i
352 20000 i
353 30000 i
354 40000 i
355 50000 i
356 select b, c from t1 where 1000<=b and b<=100000 and c<'j' order by b desc, c desc;
357 b c
358 50000 i
359 40000 i
360 30000 i
361 20000 i
362 10000 i
363 5000 i
364 5000 i
365 5000 i
366 5000 h
367 4000 i
368 4000 i
369 4000 i
370 4000 h
371 3000 i
372 3000 i
373 3000 i
374 3000 h
375 2000 i
376 2000 i
377 2000 i
378 2000 h
379 1000 i
380 1000 i
381 1000 i
382 1000 h
383 select min(b), max(b) from t1;
384 min(b) max(b)
385 1 5000000
386 drop table t1;
387 CREATE TABLE test1 (
388 SubscrID int(11) NOT NULL auto_increment,
389 UsrID int(11) NOT NULL default '0',
390 PRIMARY KEY (SubscrID),
391 KEY idx_usrid (UsrID)
392 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
393 Warnings:
394 Warning 1681 Integer display width is deprecated and will be removed in a future release.
395 Warning 1681 Integer display width is deprecated and will be removed in a future release.
396 INSERT INTO test1 VALUES (2,224),(3,224),(1,224);
397 CREATE TABLE test2 (
398 SbclID int(11) NOT NULL auto_increment,
399 SbcrID int(11) NOT NULL default '0',
400 PRIMARY KEY (SbclID),
401 KEY idx_sbcrid (SbcrID)
402 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
403 Warnings:
404 Warning 1681 Integer display width is deprecated and will be removed in a future release.
405 Warning 1681 Integer display width is deprecated and will be removed in a future release.
406 INSERT INTO test2 VALUES (3,2),(1,1),(2,1),(4,2);
407 select * from test1 order by 1;
408 SubscrID UsrID
409 1 224
410 2 224
411 3 224
412 select * from test2 order by 1;
413 SbclID SbcrID
414 1 1
415 2 1
416 3 2
417 4 2
418 SELECT s.SubscrID,l.SbclID FROM test1 s left JOIN test2 l ON
419 l.SbcrID=s.SubscrID WHERE s.UsrID=224 order by 1, 2;
420 SubscrID SbclID
421 1 1
422 1 2
423 2 3
424 2 4
425 3 NULL
426 drop table test1;
427 drop table test2;
428 create table t1 (
429 pk int primary key,
430 dt datetime not null,
431 da date not null,
432 ye year not null,
433 ti time not null,
434 ts timestamp not null,
435 index(dt),
436 index(da),
437 index(ye),
438 index(ti),
439 index(ts)
440 ) engine=ndb;
441 insert into t1 (pk,dt,da,ye,ti,ts) values
442 (1, '1901-05-05 23:00:59', '1901-05-05', '1901', '23:00:59', '2001-01-01 23:00:59'),
443 (2, '1912-09-05 13:00:59', '1912-09-05', '1912', '13:00:59', '2001-01-01 13:00:59'),
444 (3, '1945-12-31 00:00:00', '1945-12-31', '1945', '00:00:00', '2001-01-01 00:00:00'),
445 (4, '1955-12-31 00:00:00', '1955-12-31', '1955', '00:00:00', '2001-01-01 00:00:00'),
446 (5, '1963-06-06 06:06:06', '1963-06-06', '1963', '06:06:06', '2001-01-01 06:06:06'),
447 (6, '1993-06-06 06:06:06', '1993-06-06', '1993', '06:06:06', '2001-01-01 06:06:06'),
448 (7, '2001-01-01 10:11:10', '2001-01-01', '2001', '10:11:10', '2001-01-01 10:11:10'),
449 (8, '2001-01-01 10:11:11', '2001-01-01', '2001', '10:11:11', '2001-01-01 10:11:11'),
450 (9, '2005-01-31 23:59:59', '2005-01-31', '2005', '23:59:59', '2001-01-01 23:59:59');
451 select count(*)-9 from t1 use index (dt) where dt > '1900-01-01 00:00:00';
452 count(*)-9
453 0
454 select count(*)-6 from t1 use index (dt) where dt >= '1955-12-31 00:00:00';
455 count(*)-6
456 0
457 select count(*)-5 from t1 use index (dt) where dt > '1955-12-31 00:00:00';
458 count(*)-5
459 0
460 select count(*)-5 from t1 use index (dt) where dt < '1970-03-03 22:22:22';
461 count(*)-5
462 0
463 select count(*)-7 from t1 use index (dt) where dt < '2001-01-01 10:11:11';
464 count(*)-7
465 0
466 select count(*)-8 from t1 use index (dt) where dt <= '2001-01-01 10:11:11';
467 count(*)-8
468 0
469 select count(*)-9 from t1 use index (dt) where dt <= '2055-01-01 00:00:00';
470 count(*)-9
471 0
472 select count(*)-9 from t1 use index (da) where da > '1900-01-01';
473 count(*)-9
474 0
475 select count(*)-6 from t1 use index (da) where da >= '1955-12-31';
476 count(*)-6
477 0
478 select count(*)-5 from t1 use index (da) where da > '1955-12-31';
479 count(*)-5
480 0
481 select count(*)-5 from t1 use index (da) where da < '1970-03-03';
482 count(*)-5
483 0
484 select count(*)-6 from t1 use index (da) where da < '2001-01-01';
485 count(*)-6
486 0
487 select count(*)-8 from t1 use index (da) where da <= '2001-01-02';
488 count(*)-8
489 0
490 select count(*)-9 from t1 use index (da) where da <= '2055-01-01';
491 count(*)-9
492 0
493 select count(*)-9 from t1 use index (ye) where ye > '1900';
494 count(*)-9
495 0
496 select count(*)-6 from t1 use index (ye) where ye >= '1955';
497 count(*)-6
498 0
499 select count(*)-5 from t1 use index (ye) where ye > '1955';
500 count(*)-5
501 0
502 select count(*)-5 from t1 use index (ye) where ye < '1970';
503 count(*)-5
504 0
505 select count(*)-6 from t1 use index (ye) where ye < '2001';
506 count(*)-6
507 0
508 select count(*)-8 from t1 use index (ye) where ye <= '2001';
509 count(*)-8
510 0
511 select count(*)-9 from t1 use index (ye) where ye <= '2055';
512 count(*)-9
513 0
514 select count(*)-9 from t1 use index (ti) where ti >= '00:00:00';
515 count(*)-9
516 0
517 select count(*)-7 from t1 use index (ti) where ti > '00:00:00';
518 count(*)-7
519 0
520 select count(*)-7 from t1 use index (ti) where ti > '05:05:05';
521 count(*)-7
522 0
523 select count(*)-5 from t1 use index (ti) where ti > '06:06:06';
524 count(*)-5
525 0
526 select count(*)-5 from t1 use index (ti) where ti < '10:11:11';
527 count(*)-5
528 0
529 select count(*)-6 from t1 use index (ti) where ti <= '10:11:11';
530 count(*)-6
531 0
532 select count(*)-8 from t1 use index (ti) where ti < '23:59:59';
533 count(*)-8
534 0
535 select count(*)-9 from t1 use index (ti) where ti <= '23:59:59';
536 count(*)-9
537 0
538 select count(*)-9 from t1 use index (ts) where ts >= '2001-01-01 00:00:00';
539 count(*)-9
540 0
541 select count(*)-7 from t1 use index (ts) where ts > '2001-01-01 00:00:00';
542 count(*)-7
543 0
544 select count(*)-7 from t1 use index (ts) where ts > '2001-01-01 05:05:05';
545 count(*)-7
546 0
547 select count(*)-5 from t1 use index (ts) where ts > '2001-01-01 06:06:06';
548 count(*)-5
549 0
550 select count(*)-5 from t1 use index (ts) where ts < '2001-01-01 10:11:11';
551 count(*)-5
552 0
553 select count(*)-6 from t1 use index (ts) where ts <= '2001-01-01 10:11:11';
554 count(*)-6
555 0
556 select count(*)-8 from t1 use index (ts) where ts < '2001-01-01 23:59:59';
557 count(*)-8
558 0
559 select count(*)-9 from t1 use index (ts) where ts <= '2001-01-01 23:59:59';
560 count(*)-9
561 0
562 drop table t1;
563 create table t1 (
564 a int primary key,
565 s decimal(12),
566 t decimal(12, 5),
567 u decimal(12) unsigned,
568 v decimal(12, 5) unsigned,
569 key (s),
570 key (t),
571 key (u),
572 key (v)
573 ) engine=ndb;
574 Warnings:
575 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
576 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
577 insert into t1 values
578 ( 0, -000000000007, -0000061.00003, 000000000061, 0000965.00042),
579 ( 1, -000000000007, -0000061.00042, 000000000061, 0000965.00003),
580 ( 2, -071006035767, 4210253.00024, 000000000001, 0000001.84488),
581 ( 3, 000000007115, 0000000.77607, 000077350625, 0000018.00013),
582 ( 4, -000000068391, -0346486.00000, 000000005071, 0005334.00002),
583 ( 5, -521579890459, -1936874.00001, 000000000154, 0000003.00018),
584 ( 6, -521579890459, -1936874.00018, 000000000154, 0000003.00001),
585 ( 7, 000000000333, 0000051.39140, 000000907958, 0788643.08374),
586 ( 8, 000042731229, 0000009.00000, 000000000009, 6428667.00000),
587 ( 9, -000008159769, 0000918.00004, 000096951421, 7607730.00008);
588 select count(*)- 5 from t1 use index (s) where s < -000000000007;
589 count(*)- 5
590 0
591 select count(*)- 7 from t1 use index (s) where s <= -000000000007;
592 count(*)- 7
593 0
594 select count(*)- 2 from t1 use index (s) where s = -000000000007;
595 count(*)- 2
596 0
597 select count(*)- 5 from t1 use index (s) where s >= -000000000007;
598 count(*)- 5
599 0
600 select count(*)- 3 from t1 use index (s) where s > -000000000007;
601 count(*)- 3
602 0
603 select count(*)- 4 from t1 use index (t) where t < -0000061.00003;
604 count(*)- 4
605 0
606 select count(*)- 5 from t1 use index (t) where t <= -0000061.00003;
607 count(*)- 5
608 0
609 select count(*)- 1 from t1 use index (t) where t = -0000061.00003;
610 count(*)- 1
611 0
612 select count(*)- 6 from t1 use index (t) where t >= -0000061.00003;
613 count(*)- 6
614 0
615 select count(*)- 5 from t1 use index (t) where t > -0000061.00003;
616 count(*)- 5
617 0
618 select count(*)- 2 from t1 use index (u) where u < 000000000061;
619 count(*)- 2
620 0
621 select count(*)- 4 from t1 use index (u) where u <= 000000000061;
622 count(*)- 4
623 0
624 select count(*)- 2 from t1 use index (u) where u = 000000000061;
625 count(*)- 2
626 0
627 select count(*)- 8 from t1 use index (u) where u >= 000000000061;
628 count(*)- 8
629 0
630 select count(*)- 6 from t1 use index (u) where u > 000000000061;
631 count(*)- 6
632 0
633 select count(*)- 5 from t1 use index (v) where v < 0000965.00042;
634 count(*)- 5
635 0
636 select count(*)- 6 from t1 use index (v) where v <= 0000965.00042;
637 count(*)- 6
638 0
639 select count(*)- 1 from t1 use index (v) where v = 0000965.00042;
640 count(*)- 1
641 0
642 select count(*)- 5 from t1 use index (v) where v >= 0000965.00042;
643 count(*)- 5
644 0
645 select count(*)- 4 from t1 use index (v) where v > 0000965.00042;
646 count(*)- 4
647 0
648 drop table t1;
649 create table t1 (a int, c varchar(10),
650 primary key using hash (a), index(c)) engine=ndb;
651 insert into t1 (a, c) values (1,'aaa'),(3,'bbb');
652 select count(*) from t1 where c<'bbb';
653 count(*)
654 1
655 drop table t1;
656 create table t1 (a int primary key) engine = ndb;
657 insert into t1 values (1), (2), (3);
658 begin;
659 delete from t1 where a > 1;
660 rollback;
661 select * from t1 order by a;
662 a
663 1
664 2
665 3
666 begin;
667 delete from t1 where a > 1;
668 rollback;
669 begin;
670 select * from t1 order by a;
671 a
672 1
673 2
674 3
675 delete from t1 where a > 2;
676 select * from t1 order by a;
677 a
678 1
679 2
680 delete from t1 where a > 1;
681 select * from t1 order by a;
682 a
683 1
684 delete from t1 where a > 0;
685 select * from t1 order by a;
686 a
687 rollback;
688 select * from t1 order by a;
689 a
690 1
691 2
692 3
693 delete from t1;
694 drop table t1;
695 create table nationaldish (DishID int unsigned NOT NULL AUTO_INCREMENT,
696 CountryCode char(3) NOT NULL,
697 DishTitle varchar(64) NOT NULL,
698 calories smallint unsigned DEFAULT NULL,
699 PRIMARY KEY (DishID),
700 INDEX i USING HASH (countrycode,calories)
701 ) ENGINE=ndbcluster;
702 ERROR HY000: Can't create table 'nationaldish' (use SHOW WARNINGS for more info).
703 SHOW WARNINGS;
704 Level Code Message
705 Warning 1478 Table storage engine 'ndbcluster' does not support the create option 'Ndb does not support non-unique hash based indexes'
706 Error 1005 Can't create table 'nationaldish' (use SHOW WARNINGS for more info).
707 create table nationaldish (DishID int unsigned NOT NULL AUTO_INCREMENT,
708 CountryCode char(3) NOT NULL,
709 DishTitle varchar(64) NOT NULL,
710 calories smallint unsigned DEFAULT NULL,
711 PRIMARY KEY (DishID)
712 ) ENGINE=ndbcluster;
713 create index i on nationaldish(countrycode,calories) using hash;
714 ERROR 42000: Table 'nationaldish' uses an extension that doesn't exist in this MySQL version
715 drop table nationaldish;
716 drop table if exists t1;
717 Warnings:
718 Note 1051 Unknown table 'test.t1'
719 create table t1(c1 varchar(20) primary key, c2 char(20)) engine=ndbcluster;
720 insert into t1(c1,c2) values ('ddd','jg');
721 select * from t1 where (c2 < 'b' AND c1 <> 'g') OR (c2 <> 'a' AND c1 <> 'd');
722 c1 c2
723 ddd jg
724 drop table t1;
725 drop table if exists t1;
726 create table t1 (
727 a int unsigned,
728 b varchar(1024) not null,
729 primary key using hash (a),
730 index x1 (b)
731 ) engine = ndbcluster
732 default charset = utf8
733 collate = utf8_unicode_ci;
734 Warnings:
735 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
736 Warning 3778 'utf8mb3_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
737 Warning 1296 Specified key 'x1' was too long (max = 3056 bytes); statistics will not be generated
738 insert into t1 values (1,'a'),(2,'y'),(3,'z');
739 select * from t1 where b = 'b';
740 a b
741 select * from t1 where b = 'a';
742 a b
743 1 a
744 drop table t1;
745 # bug#57396
746 create table t1 (a int not null, b int not null, primary key (b,a)) engine=ndb;
747 insert into t1(a,b) values(0,0);
748 select * from t1 where b < 8 or b >= 8;
749 a b
750 0 0
751 drop table t1;
752 # bug#56853
753 create table t1 (
754 x varchar(16) not null,
755 primary key (x)
756 )
757 character set latin1
758 collate latin1_swedish_ci
759 engine ndb
760 partition by key (x) partitions 8;
761 insert into t1 (x) values ('aaa');
762 insert into t1 (x) values ('bbb');
763 insert into t1 (x) values ('ccc');
764 insert into t1 (x) values ('ddd');
765 select x from t1 where x like 'aa%';
766 x
767 aaa
768 select x from t1 where x like 'aa_';
769 x
770 aaa
771 select x from t1 where x like 'bb%';
772 x
773 bbb
774 select x from t1 where x like 'bb_';
775 x
776 bbb
777 select x from t1 where x like 'cc%';
778 x
779 ccc
780 select x from t1 where x like 'cc_';
781 x
782 ccc
783 select x from t1 where x like 'dd%';
784 x
785 ddd
786 select x from t1 where x like 'dd_';
787 x
788 ddd
789 drop table t1;
790 create table t1 (
791 x varchar(4) not null,
792 primary key (x)
793 )
794 character set latin1
795 collate latin1_swedish_ci
796 engine ndb
797 partition by key (x) partitions 8;
798 insert into t1 (x) values ('aaaa');
799 insert into t1 (x) values ('bbbb');
800 insert into t1 (x) values ('cccc');
801 insert into t1 (x) values ('dddd');
802 select x from t1 where x like 'aaa%';
803 x
804 aaaa
805 select x from t1 where x like 'aaa_';
806 x
807 aaaa
808 select x from t1 where x like 'bbb%';
809 x
810 bbbb
811 select x from t1 where x like 'bbb_';
812 x
813 bbbb
814 select x from t1 where x like 'ccc%';
815 x
816 cccc
817 select x from t1 where x like 'ccc_';
818 x
819 cccc
820 select x from t1 where x like 'ddd%';
821 x
822 dddd
823 select x from t1 where x like 'ddd_';
824 x
825 dddd
826 drop table t1;
827 #
828 # Bug#29474188 MISSING RESULT ROWS FROM A SORTED RESULT
829 # SPECIFYING A 'LIMIT'
830 #
831 CREATE TABLE t1 (
832 col_int_key int(11) DEFAULT NULL,
833 col_varchar_256_unique varchar(256) COLLATE latin1_bin DEFAULT NULL,
834 col_int int(11) NOT NULL,
835 pk int(11) NOT NULL,
836 PRIMARY KEY (col_int,pk),
837 KEY col_int_key (col_int_key)
838 ) ENGINE=ndbcluster;
839 Warnings:
840 Warning 1681 Integer display width is deprecated and will be removed in a future release.
841 Warning 1681 Integer display width is deprecated and will be removed in a future release.
842 Warning 1681 Integer display width is deprecated and will be removed in a future release.
843 INSERT INTO t1 VALUES
844 (8, 'Stockholm',12,19),
845 (8, 'Trondheim', 5,13);
846 SELECT col_varchar_256_unique AS field1, col_int_key AS field2
847 FROM t1
848 WHERE col_int_key = 8
849 ORDER BY field1,field2 limit 1000;
850 field1 field2
851 Stockholm 8
852 Trondheim 8
853 SELECT col_varchar_256_unique AS field1, col_int_key AS field2
854 FROM t1
855 WHERE col_int_key = 8
856 ORDER BY field1,field2 limit 100;
857 field1 field2
858 Stockholm 8
859 Trondheim 8
860 SELECT col_varchar_256_unique AS field1, col_int_key AS field2
861 FROM t1
862 WHERE col_int_key = 8
863 ORDER BY field1,field2 limit 10000;
864 field1 field2
865 Stockholm 8
866 Trondheim 8
867 DROP TABLE t1;