| 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; |