release/src/router/mysql/mysql-test/suite/ndb/t/ndb_index_ordered.test

   1 -- source include/have_ndb.inc
   2 -- source include/not_embedded.inc
   3
   4 --disable_warnings
   5 drop table if exists t1, test1, test2;
   6 --enable_warnings
   7
   8 #
   9 # Simple test to show use of ordered indexes
  10 #
  11
  12 CREATE TABLE t1 (
  13   a int unsigned NOT NULL PRIMARY KEY,
  14   b int unsigned not null,
  15   c int unsigned,
  16   KEY(b)
  17 ) engine=ndbcluster;
  18
  19 insert t1 values(1, 2, 3), (2,3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
  20 select * from t1 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 select * from t1 where b <= 4 order by b;
  26
  27 # Test of reset_bounds
  28 select tt1.* from t1 as tt1, t1 as tt2 use index(b) where tt1.b = tt2.b order by tt1.b;
  29 select a, b, c from t1 where a!=2 and c=6;
  30 select a, b, c from t1 where a!=2 order by a;
  31
  32 #
  33 # Here we should add some "explain select" to verify that the ordered index is
  34 # used for these queries.
  35 #
  36
  37 #
  38 # Update using ordered index scan
  39 #
  40
  41 update t1 set c = 3 where b = 3;
  42 select * from t1 order by a;
  43 update t1 set c = 10 where b >= 6;
  44 select * from t1 order by a;
  45 update t1 set c = 11 where b < 5;
  46 select * from t1 order by a;
  47 update t1 set c = 12 where b > 0;
  48 select * from t1 order by a;
  49 update t1 set c = 13 where b <= 3;
  50 select * from t1 order by a;
  51 update t1 set b = b + 1 where b > 4 and b < 7;
  52 select * from t1 order by a;
  53 # Update primary key
  54 update t1 set a = a + 10 where b > 1 and b < 7;
  55 select * from t1 order by a;
  56
  57 #
  58 # Delete using ordered index scan
  59 #
  60
  61 drop table t1;
  62
  63 CREATE TABLE t1 (
  64   a int unsigned NOT NULL PRIMARY KEY,
  65   b int unsigned not null,
  66   c int unsigned,
  67   KEY(b)
  68 ) engine=ndbcluster;
  69
  70 insert t1 values(1, 2, 13), (2,3, 13), (3, 4, 12), (4, 5, 12), (5,6, 12), (6,7, 12);
  71
  72 delete from t1 where b = 3;
  73 select * from t1 order by a;
  74 delete from t1 where b >= 6;
  75 select * from t1 order by a;
  76 delete from t1 where b < 4;
  77 select * from t1 order by a;
  78 delete from t1 where b > 5;
  79 select * from t1 order by a;
  80 delete from t1 where b <= 4;
  81 select * from t1 order by a;
  82
  83 drop table t1;
  84
  85
  86 #
  87 #multi part key
  88 #
  89 CREATE TABLE t1 (
  90   a int unsigned NOT NULL PRIMARY KEY,
  91   b int unsigned not null,
  92   c int unsigned not null
  93 ) engine = ndb;
  94
  95 create index a1 on t1 (b, c);
  96
  97 insert into t1 values (1, 2, 13);
  98 insert into t1 values (2,3, 13);
  99 insert into t1 values (3, 4, 12);
 100 insert into t1 values (4, 5, 12);
 101 insert into t1 values (5,6, 12);
 102 insert into t1 values (6,7, 12);
 103 insert into t1 values (7, 2, 1);
 104 insert into t1 values (8,3, 6);
 105 insert into t1 values (9, 4, 12);
 106 insert into t1 values (14, 5, 4);
 107 insert into t1 values (15,5,5);
 108 insert into t1 values (16,5, 6);
 109 insert into t1 values (17,4,4);
 110 insert into t1 values (18,1, 7);
 111
 112
 113
 114 select * from t1 order by a;
 115 select * from t1 where b<=5 order by a;
 116 select * from t1 where b<=5 and c=0;
 117 insert into t1 values (19,4, 0);
 118 select * from t1 where b<=5 and c=0;
 119 select * from t1 where b=4 and c<=5 order by a;
 120 select * from t1 where b<=4 and c<=5 order by a;
 121 select * from t1 where b<=5 and c=0 or b<=5 and c=2;
 122
 123 select count(*) from t1 where b = 0;
 124 select count(*) from t1 where b = 1;
 125 drop table t1;
 126
 127 #
 128 # Indexing NULL values
 129 #
 130
 131 CREATE TABLE t1 (
 132   a int unsigned NOT NULL PRIMARY KEY,
 133   b int unsigned,
 134   c int unsigned,
 135   KEY bc(b,c)
 136 ) engine = ndb;
 137
 138 insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
 139 select * from t1 use index (bc) where b IS NULL order by a;
 140
 141 select * from t1 use index (bc)order by a;
 142 select * from t1 use index (bc) order by a;
 143 select * from t1 use index (PRIMARY) where b IS NULL order by a;
 144 select * from t1 use index (bc) where b IS NULL order by a;
 145 select * from t1 use index (bc) where b IS NULL and c IS NULL order by a;
 146 select * from t1 use index (bc) where b IS NULL and c = 2 order by a;
 147 select * from t1 use index (bc) where b < 4 order by a;
 148 select * from t1 use index (bc) where b IS NOT NULL order by a;
 149 drop table t1;
 150
 151 #
 152 # Order by again, including descending.
 153 #
 154
 155 create table t1 (
 156   a int unsigned primary key,
 157   b int unsigned,
 158   c char(10),
 159   key bc (b, c)
 160 ) engine=ndb;
 161
 162 insert into t1 values(1,1,'a'),(2,2,'b'),(3,3,'c'),(4,4,'d'),(5,5,'e');
 163 insert into t1 select a*7,10*b,'f' from t1;
 164 insert into t1 select a*13,10*b,'g' from t1;
 165 insert into t1 select a*17,10*b,'h' from t1;
 166 insert into t1 select a*19,10*b,'i' from t1;
 167 insert into t1 select a*23,10*b,'j' from t1;
 168 insert into t1 select a*29,10*b,'k' from t1;
 169 #
 170 select b, c from t1 where b <= 10 and c <'f' order by b, c;
 171 select b, c from t1 where b <= 10 and c <'f' order by b desc, c desc;
 172 #
 173 select b, c from t1 where b=4000 and c<'k' order by b, c;
 174 select b, c from t1 where b=4000 and c<'k' order by b desc, c desc;
 175 select b, c from t1 where 1000<=b and b<=100000 and c<'j' order by b, c;
 176 select b, c from t1 where 1000<=b and b<=100000 and c<'j' order by b desc, c desc;
 177 #
 178 select min(b), max(b) from t1;
 179 #
 180 drop table t1;
 181
 182 #
 183 # Bug #6435
 184 CREATE TABLE test1 (
 185 SubscrID int(11) NOT NULL auto_increment,
 186 UsrID int(11) NOT NULL default '0',
 187 PRIMARY KEY  (SubscrID),
 188 KEY idx_usrid (UsrID)
 189 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
 190
 191 INSERT INTO test1 VALUES (2,224),(3,224),(1,224);
 192
 193 CREATE TABLE test2 (
 194 SbclID int(11) NOT NULL auto_increment,
 195 SbcrID int(11) NOT NULL default '0',
 196 PRIMARY KEY  (SbclID),
 197 KEY idx_sbcrid (SbcrID)
 198 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
 199
 200 INSERT INTO test2 VALUES (3,2),(1,1),(2,1),(4,2);
 201 select * from test1 order by 1;
 202 select * from test2 order by 1;
 203 SELECT s.SubscrID,l.SbclID FROM test1 s left JOIN test2 l ON
 204 l.SbcrID=s.SubscrID WHERE s.UsrID=224 order by 1, 2;
 205 drop table test1;
 206 drop table test2;
 207
 208 # bug#7424 + bug#7725
 209
 210 create table t1 (
 211   pk int primary key,
 212   dt datetime not null,
 213   da date not null,
 214   ye year not null,
 215   ti time not null,
 216   ts timestamp not null,
 217   index(dt),
 218   index(da),
 219   index(ye),
 220   index(ti),
 221   index(ts)
 222 ) engine=ndb;
 223
 224 insert into t1 (pk,dt,da,ye,ti,ts) values
 225   (1, '1901-05-05 23:00:59', '1901-05-05', '1901', '23:00:59', '2001-01-01 23:00:59'),
 226   (2, '1912-09-05 13:00:59', '1912-09-05', '1912', '13:00:59', '2001-01-01 13:00:59'),
 227   (3, '1945-12-31 00:00:00', '1945-12-31', '1945', '00:00:00', '2001-01-01 00:00:00'),
 228   (4, '1955-12-31 00:00:00', '1955-12-31', '1955', '00:00:00', '2001-01-01 00:00:00'),
 229   (5, '1963-06-06 06:06:06', '1963-06-06', '1963', '06:06:06', '2001-01-01 06:06:06'),
 230   (6, '1993-06-06 06:06:06', '1993-06-06', '1993', '06:06:06', '2001-01-01 06:06:06'),
 231   (7, '2001-01-01 10:11:10', '2001-01-01', '2001', '10:11:10', '2001-01-01 10:11:10'),
 232   (8, '2001-01-01 10:11:11', '2001-01-01', '2001', '10:11:11', '2001-01-01 10:11:11'),
 233   (9, '2005-01-31 23:59:59', '2005-01-31', '2005', '23:59:59', '2001-01-01 23:59:59');
 234
 235 # datetime
 236 select count(*)-9 from t1 use index (dt) where dt >  '1900-01-01 00:00:00';
 237 select count(*)-6 from t1 use index (dt) where dt >= '1955-12-31 00:00:00';
 238 select count(*)-5 from t1 use index (dt) where dt >  '1955-12-31 00:00:00';
 239 select count(*)-5 from t1 use index (dt) where dt <  '1970-03-03 22:22:22';
 240 select count(*)-7 from t1 use index (dt) where dt <  '2001-01-01 10:11:11';
 241 select count(*)-8 from t1 use index (dt) where dt <= '2001-01-01 10:11:11';
 242 select count(*)-9 from t1 use index (dt) where dt <= '2055-01-01 00:00:00';
 243
 244 # date
 245 select count(*)-9 from t1 use index (da) where da >  '1900-01-01';
 246 select count(*)-6 from t1 use index (da) where da >= '1955-12-31';
 247 select count(*)-5 from t1 use index (da) where da >  '1955-12-31';
 248 select count(*)-5 from t1 use index (da) where da <  '1970-03-03';
 249 select count(*)-6 from t1 use index (da) where da <  '2001-01-01';
 250 select count(*)-8 from t1 use index (da) where da <= '2001-01-02';
 251 select count(*)-9 from t1 use index (da) where da <= '2055-01-01';
 252
 253 # year
 254 select count(*)-9 from t1 use index (ye) where ye >  '1900';
 255 select count(*)-6 from t1 use index (ye) where ye >= '1955';
 256 select count(*)-5 from t1 use index (ye) where ye >  '1955';
 257 select count(*)-5 from t1 use index (ye) where ye <  '1970';
 258 select count(*)-6 from t1 use index (ye) where ye <  '2001';
 259 select count(*)-8 from t1 use index (ye) where ye <= '2001';
 260 select count(*)-9 from t1 use index (ye) where ye <= '2055';
 261
 262 # time
 263 select count(*)-9 from t1 use index (ti) where ti >= '00:00:00';
 264 select count(*)-7 from t1 use index (ti) where ti >  '00:00:00';
 265 select count(*)-7 from t1 use index (ti) where ti >  '05:05:05';
 266 select count(*)-5 from t1 use index (ti) where ti >  '06:06:06';
 267 select count(*)-5 from t1 use index (ti) where ti <  '10:11:11';
 268 select count(*)-6 from t1 use index (ti) where ti <= '10:11:11';
 269 select count(*)-8 from t1 use index (ti) where ti <  '23:59:59';
 270 select count(*)-9 from t1 use index (ti) where ti <= '23:59:59';
 271
 272 # timestamp
 273 select count(*)-9 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 00:00:00';
 275 select count(*)-7 from t1 use index (ts) where ts >  '2001-01-01 05:05:05';
 276 select count(*)-5 from t1 use index (ts) where ts >  '2001-01-01 06:06:06';
 277 select count(*)-5 from t1 use index (ts) where ts <  '2001-01-01 10:11:11';
 278 select count(*)-6 from t1 use index (ts) where ts <= '2001-01-01 10:11:11';
 279 select count(*)-8 from t1 use index (ts) where ts <  '2001-01-01 23:59:59';
 280 select count(*)-9 from t1 use index (ts) where ts <= '2001-01-01 23:59:59';
 281
 282 drop table t1;
 283
 284 # decimal (not the new 5.0 thing)
 285
 286 create table t1 (
 287   a int primary key,
 288   s decimal(12),
 289   t decimal(12, 5),
 290   u decimal(12) unsigned,
 291   v decimal(12, 5) unsigned,
 292   key (s),
 293   key (t),
 294   key (u),
 295   key (v)
 296 ) engine=ndb;
 297 #
 298 insert into t1 values
 299   ( 0, -000000000007, -0000061.00003,  000000000061,  0000965.00042),
 300   ( 1, -000000000007, -0000061.00042,  000000000061,  0000965.00003),
 301   ( 2, -071006035767,  4210253.00024,  000000000001,  0000001.84488),
 302   ( 3,  000000007115,  0000000.77607,  000077350625,  0000018.00013),
 303   ( 4, -000000068391, -0346486.00000,  000000005071,  0005334.00002),
 304   ( 5, -521579890459, -1936874.00001,  000000000154,  0000003.00018),
 305   ( 6, -521579890459, -1936874.00018,  000000000154,  0000003.00001),
 306   ( 7,  000000000333,  0000051.39140,  000000907958,  0788643.08374),
 307   ( 8,  000042731229,  0000009.00000,  000000000009,  6428667.00000),
 308   ( 9, -000008159769,  0000918.00004,  000096951421,  7607730.00008);
 309 #
 310 select count(*)- 5 from t1 use index (s) where s  < -000000000007;
 311 select count(*)- 7 from t1 use index (s) where s <= -000000000007;
 312 select count(*)- 2 from t1 use index (s) where s  = -000000000007;
 313 select count(*)- 5 from t1 use index (s) where s >= -000000000007;
 314 select count(*)- 3 from t1 use index (s) where s  > -000000000007;
 315 #
 316 select count(*)- 4 from t1 use index (t) where t  < -0000061.00003;
 317 select count(*)- 5 from t1 use index (t) where t <= -0000061.00003;
 318 select count(*)- 1 from t1 use index (t) where t  = -0000061.00003;
 319 select count(*)- 6 from t1 use index (t) where t >= -0000061.00003;
 320 select count(*)- 5 from t1 use index (t) where t  > -0000061.00003;
 321 #
 322 select count(*)- 2 from t1 use index (u) where u  <  000000000061;
 323 select count(*)- 4 from t1 use index (u) where u <=  000000000061;
 324 select count(*)- 2 from t1 use index (u) where u  =  000000000061;
 325 select count(*)- 8 from t1 use index (u) where u >=  000000000061;
 326 select count(*)- 6 from t1 use index (u) where u  >  000000000061;
 327 #
 328 select count(*)- 5 from t1 use index (v) where v  <  0000965.00042;
 329 select count(*)- 6 from t1 use index (v) where v <=  0000965.00042;
 330 select count(*)- 1 from t1 use index (v) where v  =  0000965.00042;
 331 select count(*)- 5 from t1 use index (v) where v >=  0000965.00042;
 332 select count(*)- 4 from t1 use index (v) where v  >  0000965.00042;
 333
 334 drop table t1;
 335
 336 # bug#7798
 337 create table t1(a int primary key, b int not null, index(b));
 338 insert into t1 values (1,1), (2,2);
 339 connect (con1,localhost,root,,test);
 340 connect (con2,localhost,root,,test);
 341 connection con1;
 342 set autocommit=0;
 343 begin;
 344 select count(*) from t1;
 345 connection con2;
 346 ALTER TABLE t1 ADD COLUMN c int;
 347 connection con1;
 348 select a from t1 where b = 2;
 349 show tables;
 350 drop table t1;
 351
 352 # mysqld 5.0.13 crash, no bug#
 353 create table t1 (a int, c varchar(10),
 354   primary key using hash (a), index(c)) engine=ndb;
 355 insert into t1 (a, c) values (1,'aaa'),(3,'bbb');
 356 select count(*) from t1 where c<'bbb';
 357 drop table t1;
 358
 359 # -- index statistics --
 360
 361 set autocommit=1;
 362 show session variables like 'ndb_index_stat_%';
 363
 364 set ndb_index_stat_enable = off;
 365 show session variables like 'ndb_index_stat_%';
 366
 367 create table t1 (a int, b int, c varchar(10) not null,
 368   primary key using hash (a), index(b,c)) engine=ndb;
 369 insert into t1 values
 370   (1,10,'aaa'),(2,10,'bbb'),(3,10,'ccc'),
 371   (4,20,'aaa'),(5,20,'bbb'),(6,20,'ccc'),
 372   (7,30,'aaa'),(8,30,'bbb'),(9,30,'ccc');
 373 select count(*) from t1 where b < 10;
 374 select count(*) from t1 where b >= 10 and c >= 'bbb';
 375 select count(*) from t1 where b > 10;
 376 select count(*) from t1 where b <= 20 and c < 'ccc';
 377 select count(*) from t1 where b = 20 and c = 'ccc';
 378 select count(*) from t1 where b > 20;
 379 select count(*) from t1 where b = 30 and c > 'aaa';
 380 select count(*) from t1 where b <= 20;
 381 select count(*) from t1 where b >= 20 and c > 'aaa';
 382 drop table t1;
 383
 384 set ndb_index_stat_enable = on;
 385 set ndb_index_stat_cache_entries = 0;
 386 show session variables like 'ndb_index_stat_%';
 387
 388 create table t1 (a int, b int, c varchar(10) not null,
 389   primary key using hash (a), index(b,c)) engine=ndb;
 390 insert into t1 values
 391   (1,10,'aaa'),(2,10,'bbb'),(3,10,'ccc'),
 392   (4,20,'aaa'),(5,20,'bbb'),(6,20,'ccc'),
 393   (7,30,'aaa'),(8,30,'bbb'),(9,30,'ccc');
 394 select count(*) from t1 where b < 10;
 395 select count(*) from t1 where b >= 10 and c >= 'bbb';
 396 select count(*) from t1 where b > 10;
 397 select count(*) from t1 where b <= 20 and c < 'ccc';
 398 select count(*) from t1 where b = 20 and c = 'ccc';
 399 select count(*) from t1 where b > 20;
 400 select count(*) from t1 where b = 30 and c > 'aaa';
 401 select count(*) from t1 where b <= 20;
 402 select count(*) from t1 where b >= 20 and c > 'aaa';
 403 drop table t1;
 404
 405 set ndb_index_stat_enable = on;
 406 set ndb_index_stat_cache_entries = 4;
 407 set ndb_index_stat_update_freq = 2;
 408 show session variables like 'ndb_index_stat_%';
 409
 410 create table t1 (a int, b int, c varchar(10) not null,
 411   primary key using hash (a), index(b,c)) engine=ndb;
 412 insert into t1 values
 413   (1,10,'aaa'),(2,10,'bbb'),(3,10,'ccc'),
 414   (4,20,'aaa'),(5,20,'bbb'),(6,20,'ccc'),
 415   (7,30,'aaa'),(8,30,'bbb'),(9,30,'ccc');
 416 select count(*) from t1 where b < 10;
 417 select count(*) from t1 where b >= 10 and c >= 'bbb';
 418 select count(*) from t1 where b > 10;
 419 select count(*) from t1 where b <= 20 and c < 'ccc';
 420 select count(*) from t1 where b = 20 and c = 'ccc';
 421 select count(*) from t1 where b > 20;
 422 select count(*) from t1 where b = 30 and c > 'aaa';
 423 select count(*) from t1 where b <= 20;
 424 select count(*) from t1 where b >= 20 and c > 'aaa';
 425 drop table t1;
 426
 427 set ndb_index_stat_enable = @@global.ndb_index_stat_enable;
 428 set ndb_index_stat_cache_entries = @@global.ndb_index_stat_cache_entries;
 429 set ndb_index_stat_update_freq = @@global.ndb_index_stat_update_freq;
 430 show session variables like 'ndb_index_stat_%';
 431
 432 # End of 4.1 tests
 433
 434 # bug#24039
 435
 436 create table t1 (a int primary key) engine = ndb;
 437 insert into t1 values (1), (2), (3);
 438 begin;
 439 delete from t1 where a > 1;
 440 rollback;
 441 select * from t1 order by a;
 442 begin;
 443 delete from t1 where a > 1;
 444 rollback;
 445
 446 begin;
 447 select * from t1 order by a;
 448 delete from t1 where a > 2;
 449 select * from t1 order by a;
 450 delete from t1 where a > 1;
 451 select * from t1 order by a;
 452 delete from t1 where a > 0;
 453 select * from t1 order by a;
 454 rollback;
 455 select * from t1 order by a;
 456 delete from t1;
 457 drop table t1;
 458
 459 # bug#24820 CREATE INDEX ....USING HASH on NDB table creates ordered index, not HASH index
 460
 461 --error ER_CANT_CREATE_TABLE
 462 create table nationaldish (DishID int(10) unsigned NOT NULL AUTO_INCREMENT,
 463                            CountryCode char(3) NOT NULL,
 464                            DishTitle varchar(64) NOT NULL,
 465                            calories smallint(5) unsigned DEFAULT NULL,
 466                            PRIMARY KEY (DishID),
 467                            INDEX i USING HASH (countrycode,calories)
 468                            ) ENGINE=ndbcluster;
 469
 470 create table nationaldish (DishID int(10) unsigned NOT NULL AUTO_INCREMENT,
 471                            CountryCode char(3) NOT NULL,
 472                            DishTitle varchar(64) NOT NULL,
 473                            calories smallint(5) unsigned DEFAULT NULL,
 474                            PRIMARY KEY (DishID)
 475                            ) ENGINE=ndbcluster;
 476
 477 --error ER_UNSUPPORTED_EXTENSION
 478 create index i on nationaldish(countrycode,calories) using hash;
 479
 480 drop table nationaldish;