| 1 |
-- source include/have_ndb.inc |
| 2 |
|
| 3 |
# |
| 4 |
# Simple test to show use of UNIQUE indexes |
| 5 |
# |
| 6 |
|
| 7 |
CREATE TABLE t1 ( |
| 8 |
a int NOT NULL PRIMARY KEY, |
| 9 |
b int not null, |
| 10 |
c int, |
| 11 |
UNIQUE ib(b) |
| 12 |
) engine=ndbcluster; |
| 13 |
|
| 14 |
insert t1 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2); |
| 15 |
select * from t1 order by b; |
| 16 |
select * from t1 where b = 4 order by b; |
| 17 |
insert into t1 values(7,8,3); |
| 18 |
select * from t1 where b = 4 order by a; |
| 19 |
|
| 20 |
-- error ER_DUP_ENTRY |
| 21 |
insert into t1 values(8, 2, 3); |
| 22 |
select * from t1 order by a; |
| 23 |
delete from t1 where a = 1; |
| 24 |
insert into t1 values(8, 2, 3); |
| 25 |
select * from t1 order by a; |
| 26 |
|
| 27 |
alter table t1 drop index ib; |
| 28 |
insert into t1 values(1, 2, 3); |
| 29 |
# Bug# #18129 |
| 30 |
--error 1169 |
| 31 |
create unique index ib on t1(b); |
| 32 |
|
| 33 |
drop table t1; |
| 34 |
|
| 35 |
# |
| 36 |
# Indexing NULL values |
| 37 |
# |
| 38 |
|
| 39 |
CREATE TABLE t1 ( |
| 40 |
a int unsigned NOT NULL PRIMARY KEY, |
| 41 |
b int unsigned, |
| 42 |
c int unsigned, |
| 43 |
UNIQUE bc(b,c) |
| 44 |
) engine = ndb; |
| 45 |
|
| 46 |
insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL); |
| 47 |
select * from t1 use index (bc) where b IS NULL order by a; |
| 48 |
|
| 49 |
select * from t1 use index (bc)order by a; |
| 50 |
select * from t1 use index (bc) order by a; |
| 51 |
select * from t1 use index (PRIMARY) where b IS NULL order by a; |
| 52 |
select * from t1 use index (bc) where b IS NULL order by a; |
| 53 |
select * from t1 use index (bc) where b IS NULL and c IS NULL order by a; |
| 54 |
select * from t1 use index (bc) where b IS NULL and c = 2 order by a; |
| 55 |
select * from t1 use index (bc) where b < 4 order by a; |
| 56 |
select * from t1 use index (bc) where b IS NOT NULL order by a; |
| 57 |
-- error ER_DUP_ENTRY |
| 58 |
insert into t1 values(5,1,1); |
| 59 |
drop table t1; |
| 60 |
|
| 61 |
|
| 62 |
# |
| 63 |
# Show use of UNIQUE USING HASH indexes |
| 64 |
# |
| 65 |
|
| 66 |
CREATE TABLE t2 ( |
| 67 |
a int unsigned NOT NULL PRIMARY KEY, |
| 68 |
b int unsigned not null, |
| 69 |
c int unsigned not null, |
| 70 |
UNIQUE (b, c) USING HASH |
| 71 |
) engine=ndbcluster; |
| 72 |
|
| 73 |
insert t2 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2); |
| 74 |
select * from t2 where a = 3; |
| 75 |
select * from t2 where b = 4; |
| 76 |
select * from t2 where c = 6; |
| 77 |
insert into t2 values(7,8,3); |
| 78 |
select * from t2 where b = 4 order by a; |
| 79 |
|
| 80 |
-- error ER_DUP_ENTRY |
| 81 |
insert into t2 values(8, 2, 3); |
| 82 |
select * from t2 order by a; |
| 83 |
delete from t2 where a = 1; |
| 84 |
insert into t2 values(8, 2, 3); |
| 85 |
select * from t2 order by a; |
| 86 |
|
| 87 |
# Bug #24818 CREATE UNIQUE INDEX (...) USING HASH on a NDB table crashes mysqld |
| 88 |
create unique index bi using hash on t2(b); |
| 89 |
-- error ER_DUP_ENTRY |
| 90 |
insert into t2 values(9, 3, 1); |
| 91 |
alter table t2 drop index bi; |
| 92 |
insert into t2 values(9, 3, 1); |
| 93 |
select * from t2 order by a; |
| 94 |
|
| 95 |
drop table t2; |
| 96 |
|
| 97 |
CREATE TABLE t2 ( |
| 98 |
a int unsigned NOT NULL PRIMARY KEY, |
| 99 |
b int unsigned not null, |
| 100 |
c int unsigned, |
| 101 |
UNIQUE (b, c) USING HASH |
| 102 |
) engine=ndbcluster |
| 103 |
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"; |
| 104 |
|
| 105 |
|
| 106 |
insert t2 values(1,1,NULL),(2,2,2),(3,3,NULL),(4,4,4),(5,5,NULL),(6,6,6),(7,7,NULL),(8,3,NULL),(9,3,NULL); |
| 107 |
|
| 108 |
select * from t2 where c IS NULL order by a; |
| 109 |
select * from t2 where b = 3 AND c IS NULL order by a; |
| 110 |
select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a; |
| 111 |
set @old_os = @@session.optimizer_switch; |
| 112 |
set optimizer_switch = 'engine_condition_pushdown=on'; |
| 113 |
--replace_column 10 # 11 # |
| 114 |
explain select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a; |
| 115 |
select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a; |
| 116 |
set optimizer_switch = @old_os; |
| 117 |
|
| 118 |
################## |
| 119 |
# Bug#33466554 Prevent optimizer from generating range scans on |
| 120 |
# unique hash indexes. |
| 121 |
# |
| 122 |
# The query plan for some of the above queries set up a range scan |
| 123 |
# using the HASH-index on (b,c) - That is not an available access type |
| 124 |
# on an 'UNIQUE <key> USING HASH'. Even if set up by the optimizer as such, |
| 125 |
# ha_ndbcluster::read_range_first_to_buf detected that we tried to do |
| 126 |
# use an access type not supported by a UNIQUE_INDEX and did multiple |
| 127 |
# 'full_table_scans' instead. |
| 128 |
|
| 129 |
# Explain some of the relevant queries. Should *not* do |
| 130 |
# range scans on the HASH index. Instead do either full table scan, |
| 131 |
# or a range scan on PK. |
| 132 |
|
| 133 |
--replace_regex / \(cost=.*// |
| 134 |
explain format=tree |
| 135 |
select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a; |
| 136 |
--replace_regex / \(cost=.*// |
| 137 |
explain format=tree |
| 138 |
select * from t2 where (b = 3 OR b = 5) AND c IS NULL; |
| 139 |
--replace_regex / \(cost=.*// |
| 140 |
explain format=tree |
| 141 |
select * from t2 where b in (1,2,3) AND c IS NULL; |
| 142 |
--replace_regex / \(cost=.*// |
| 143 |
explain format=tree |
| 144 |
select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a; |
| 145 |
--replace_regex / \(cost=.*// |
| 146 |
explain format=tree |
| 147 |
select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9; |
| 148 |
|
| 149 |
############################### |
| 150 |
# Bug#33474345 Avoid NULL-constants as part of REF keys. |
| 151 |
# |
| 152 |
# Explains of some tests above where the optimizer set up REF / Index lookup |
| 153 |
# access with a NULL constant on the ix-HASH key, which handler degraded |
| 154 |
# to full_table_scan. |
| 155 |
# Will now reject the usage of ix-index and do an ordered scan on PK instead. |
| 156 |
# More test cases for this bug further below. |
| 157 |
|
| 158 |
--replace_regex / \(cost=.*// |
| 159 |
explain format=tree |
| 160 |
select * from t2 where c IS NULL order by a; |
| 161 |
--replace_regex / \(cost=.*// |
| 162 |
explain format=tree |
| 163 |
select * from t2 where b = 3 AND c IS NULL order by a; |
| 164 |
|
| 165 |
|
| 166 |
drop table t2; |
| 167 |
|
| 168 |
# |
| 169 |
# Show use of PRIMARY KEY USING HASH indexes |
| 170 |
# |
| 171 |
|
| 172 |
CREATE TABLE t3 ( |
| 173 |
a int unsigned NOT NULL, |
| 174 |
b int unsigned not null, |
| 175 |
c int unsigned, |
| 176 |
PRIMARY KEY (a, b) USING HASH |
| 177 |
) engine=ndbcluster; |
| 178 |
|
| 179 |
insert t3 values(1, 2, 3), (2, 3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2); |
| 180 |
select * from t3 where a = 3; |
| 181 |
select * from t3 where b = 4; |
| 182 |
select * from t3 where c = 6; |
| 183 |
insert into t3 values(7,8,3); |
| 184 |
select * from t3 where b = 4 order by a; |
| 185 |
|
| 186 |
drop table t3; |
| 187 |
|
| 188 |
# |
| 189 |
# Indexes on NULL-able columns |
| 190 |
# |
| 191 |
|
| 192 |
CREATE TABLE t1 ( |
| 193 |
pk int NOT NULL PRIMARY KEY, |
| 194 |
a int unsigned, |
| 195 |
UNIQUE KEY (a) |
| 196 |
) engine=ndbcluster; |
| 197 |
|
| 198 |
insert into t1 values (-1,NULL), (0,0), (1,NULL),(2,2),(3,NULL),(4,4); |
| 199 |
|
| 200 |
select * from t1 order by pk; |
| 201 |
|
| 202 |
--error ER_DUP_ENTRY |
| 203 |
insert into t1 values (5,0); |
| 204 |
select * from t1 order by pk; |
| 205 |
delete from t1 where a = 0; |
| 206 |
insert into t1 values (5,0); |
| 207 |
select * from t1 order by pk; |
| 208 |
|
| 209 |
CREATE TABLE t2 ( |
| 210 |
pk int NOT NULL PRIMARY KEY, |
| 211 |
a int unsigned, |
| 212 |
b tinyint NOT NULL, |
| 213 |
c VARCHAR(10), |
| 214 |
UNIQUE KEY si(a, c) |
| 215 |
) engine=ndbcluster; |
| 216 |
|
| 217 |
insert into t2 values (-1,1,17,NULL),(0,NULL,18,NULL),(1,3,19,'abc'); |
| 218 |
|
| 219 |
select * from t2 order by pk; |
| 220 |
|
| 221 |
--error ER_DUP_ENTRY |
| 222 |
insert into t2 values(2,3,19,'abc'); |
| 223 |
select * from t2 order by pk; |
| 224 |
delete from t2 where c IS NOT NULL; |
| 225 |
insert into t2 values(2,3,19,'abc'); |
| 226 |
select * from t2 order by pk; |
| 227 |
|
| 228 |
drop table t1, t2; |
| 229 |
|
| 230 |
# |
| 231 |
# More complex tables |
| 232 |
# |
| 233 |
|
| 234 |
CREATE TABLE t1 ( |
| 235 |
cid smallint(5) unsigned NOT NULL default '0', |
| 236 |
cv varchar(250) NOT NULL default '', |
| 237 |
PRIMARY KEY (cid), |
| 238 |
UNIQUE KEY cv (cv) |
| 239 |
) engine=ndbcluster; |
| 240 |
INSERT INTO t1 VALUES (8,'dummy'); |
| 241 |
CREATE TABLE t2 ( |
| 242 |
cid bigint(20) unsigned NOT NULL auto_increment, |
| 243 |
cap varchar(255) NOT NULL default '', |
| 244 |
PRIMARY KEY (cid), |
| 245 |
UNIQUE KEY (cid, cap) |
| 246 |
) engine=ndbcluster; |
| 247 |
INSERT INTO t2 VALUES (NULL,'another dummy'); |
| 248 |
CREATE TABLE t3 ( |
| 249 |
gid bigint(20) unsigned NOT NULL auto_increment, |
| 250 |
gn varchar(255) NOT NULL default '', |
| 251 |
must tinyint(4) default NULL, |
| 252 |
PRIMARY KEY (gid) |
| 253 |
) engine=ndbcluster; |
| 254 |
INSERT INTO t3 VALUES (1,'V1',NULL); |
| 255 |
CREATE TABLE t4 ( |
| 256 |
uid bigint(20) unsigned NOT NULL default '0', |
| 257 |
gid bigint(20) unsigned NOT NULL, |
| 258 |
rid bigint(20) unsigned NOT NULL, |
| 259 |
cid bigint(20) unsigned NOT NULL, |
| 260 |
UNIQUE KEY m (uid,gid,rid,cid) |
| 261 |
) engine=ndbcluster; |
| 262 |
INSERT INTO t4 VALUES (1,1,2,4); |
| 263 |
INSERT INTO t4 VALUES (1,1,2,3); |
| 264 |
INSERT INTO t4 VALUES (1,1,5,7); |
| 265 |
INSERT INTO t4 VALUES (1,1,10,8); |
| 266 |
CREATE TABLE t5 ( |
| 267 |
rid bigint(20) unsigned NOT NULL auto_increment, |
| 268 |
rl varchar(255) NOT NULL default '', |
| 269 |
PRIMARY KEY (rid) |
| 270 |
) engine=ndbcluster; |
| 271 |
CREATE TABLE t6 ( |
| 272 |
uid bigint(20) unsigned NOT NULL auto_increment, |
| 273 |
un varchar(250) NOT NULL default '', |
| 274 |
uc smallint(5) unsigned NOT NULL default '0', |
| 275 |
PRIMARY KEY (uid), |
| 276 |
UNIQUE KEY nc (un,uc) |
| 277 |
) engine=ndbcluster; |
| 278 |
INSERT INTO t6 VALUES (1,'test',8); |
| 279 |
INSERT INTO t6 VALUES (2,'test2',9); |
| 280 |
INSERT INTO t6 VALUES (3,'tre',3); |
| 281 |
CREATE TABLE t7 ( |
| 282 |
mid bigint(20) unsigned NOT NULL PRIMARY KEY, |
| 283 |
uid bigint(20) unsigned NOT NULL default '0', |
| 284 |
gid bigint(20) unsigned NOT NULL, |
| 285 |
rid bigint(20) unsigned NOT NULL, |
| 286 |
cid bigint(20) unsigned NOT NULL, |
| 287 |
UNIQUE KEY m (uid,gid,rid,cid) |
| 288 |
) engine=ndbcluster; |
| 289 |
INSERT INTO t7 VALUES(1, 1, 1, 1, 1); |
| 290 |
INSERT INTO t7 VALUES(2, 2, 1, 1, 1); |
| 291 |
INSERT INTO t7 VALUES(3, 3, 1, 1, 1); |
| 292 |
INSERT INTO t7 VALUES(4, 4, 1, 1, 1); |
| 293 |
INSERT INTO t7 VALUES(5, 5, 1, 1, 1); |
| 294 |
INSERT INTO t7 VALUES(6, 1, 1, 1, 6); |
| 295 |
INSERT INTO t7 VALUES(7, 2, 1, 1, 7); |
| 296 |
INSERT INTO t7 VALUES(8, 3, 1, 1, 8); |
| 297 |
INSERT INTO t7 VALUES(9, 4, 1, 1, 9); |
| 298 |
INSERT INTO t7 VALUES(10, 5, 1, 1, 10); |
| 299 |
|
| 300 |
select * from t1 where cv = 'dummy'; |
| 301 |
select * from t1 where cv = 'test'; |
| 302 |
select * from t2 where cap = 'another dummy'; |
| 303 |
select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4; |
| 304 |
select * from t4 where uid = 1 and gid=1 and rid=1 and cid=4; |
| 305 |
select * from t4 where uid = 1 order by cid; |
| 306 |
select * from t4 where rid = 2 order by cid; |
| 307 |
select * from t6 where un='test' and uc=8; |
| 308 |
select * from t6 where un='test' and uc=7; |
| 309 |
select * from t6 where un='test'; |
| 310 |
select * from t7 where mid = 8; |
| 311 |
select * from t7 where uid = 8; |
| 312 |
select * from t7 where uid = 1 order by mid; |
| 313 |
select * from t7 where uid = 4 order by mid; |
| 314 |
select * from t7 where gid = 4; |
| 315 |
select * from t7 where gid = 1 order by mid; |
| 316 |
select * from t7 where cid = 4; |
| 317 |
select * from t7 where cid = 8; |
| 318 |
|
| 319 |
# |
| 320 |
# insert more records into t4 |
| 321 |
# |
| 322 |
let $1=100; |
| 323 |
disable_query_log; |
| 324 |
while ($1) |
| 325 |
{ |
| 326 |
eval insert into t4 values(1, $1, 5, 12); |
| 327 |
eval insert into t4 values($1, 3, 9, 11); |
| 328 |
dec $1; |
| 329 |
} |
| 330 |
enable_query_log; |
| 331 |
|
| 332 |
select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4; |
| 333 |
select * from t4 where uid = 1 and gid=1 and rid=1 and cid=4; |
| 334 |
select * from t4 where uid = 1 order by gid,cid; |
| 335 |
select * from t4 where uid = 1 order by gid,cid; |
| 336 |
select * from t4 where rid = 2 order by cid; |
| 337 |
|
| 338 |
|
| 339 |
drop table t1,t2,t3,t4,t5,t6,t7; |
| 340 |
|
| 341 |
# test null in indexes |
| 342 |
CREATE TABLE t1 ( |
| 343 |
a int unsigned NOT NULL PRIMARY KEY, |
| 344 |
b int unsigned, |
| 345 |
c int unsigned, |
| 346 |
UNIQUE bc(b,c) ) engine = ndb; |
| 347 |
|
| 348 |
insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL); |
| 349 |
select * from t1 where b=1 and c=1; |
| 350 |
select * from t1 where b is null and c is null; |
| 351 |
select * from t1 where b is null and c = 2; |
| 352 |
select * from t1 where b = 4 and c is null; |
| 353 |
create table t8 as |
| 354 |
select * from t1 where (b = 1 and c = 1) |
| 355 |
or (b is null and c is null) |
| 356 |
or (b is null and c = 2) |
| 357 |
or (b = 4 and c is null); |
| 358 |
select * from t8 order by a; |
| 359 |
select * from t1 order by a; |
| 360 |
drop table t1, t8; |
| 361 |
|
| 362 |
############################### |
| 363 |
# Bug 8101 |
| 364 |
# |
| 365 |
# Unique index not specified in the same order as in table |
| 366 |
# |
| 367 |
|
| 368 |
create table t1( |
| 369 |
id integer not null auto_increment, |
| 370 |
month integer not null, |
| 371 |
year integer not null, |
| 372 |
code varchar( 2) not null, |
| 373 |
primary key ( id), |
| 374 |
unique idx_t1( month, code, year) |
| 375 |
) engine=ndb; |
| 376 |
|
| 377 |
INSERT INTO t1 (month, year, code) VALUES (4,2004,'12'); |
| 378 |
INSERT INTO t1 (month, year, code) VALUES (5,2004,'12'); |
| 379 |
|
| 380 |
select * from t1 where code = '12' and month = 4 and year = 2004 ; |
| 381 |
|
| 382 |
drop table t1; |
| 383 |
|
| 384 |
# bug#15918 Unique Key Limit in NDB Engine |
| 385 |
|
| 386 |
create table t1 (a int primary key, b varchar(1000) not null, unique key (b)) |
| 387 |
engine=ndb charset=utf8; |
| 388 |
|
| 389 |
insert into t1 values (1, repeat(_utf8 0xe288ab6474, 200)); |
| 390 |
--error ER_DUP_ENTRY |
| 391 |
insert into t1 values (2, repeat(_utf8 0xe288ab6474, 200)); |
| 392 |
select a, sha1(b) from t1; |
| 393 |
|
| 394 |
# perl -e 'print pack("H2000","e288ab6474"x200)' | sha1sum |
| 395 |
|
| 396 |
drop table t1; |
| 397 |
|
| 398 |
# bug#21873 MySQLD Crash on ALTER...ADD..UNIQUE..USING HASH statement for NDB backed table |
| 399 |
|
| 400 |
create table t1(id int not null) engine = NDB; |
| 401 |
|
| 402 |
alter table t1 add constraint uk_test unique (id) using hash; |
| 403 |
|
| 404 |
drop table t1; |
| 405 |
|
| 406 |
# End of 4.1 tests |
| 407 |
|
| 408 |
# bug#44132 |
| 409 |
|
| 410 |
CREATE TABLE t1 ( |
| 411 |
pk0 INT, |
| 412 |
pk1 VARCHAR(100), |
| 413 |
col0 INT NOT NULL, |
| 414 |
PRIMARY KEY(pk0, pk1), |
| 415 |
UNIQUE (col0) USING HASH |
| 416 |
) engine = ndb; |
| 417 |
|
| 418 |
CREATE TABLE t2 ( |
| 419 |
pk0 VARCHAR(100), |
| 420 |
pk1 INT, |
| 421 |
col0 INT NOT NULL, |
| 422 |
PRIMARY KEY(pk0, pk1), |
| 423 |
UNIQUE (col0) USING HASH |
| 424 |
) engine = ndb; |
| 425 |
|
| 426 |
let $1=100 ; |
| 427 |
disable_query_log; |
| 428 |
while ($1) |
| 429 |
{ |
| 430 |
eval insert into t1 values($1, 'a', $1); |
| 431 |
eval insert into t2 values('a', $1, $1); |
| 432 |
dec $1; |
| 433 |
} |
| 434 |
enable_query_log; |
| 435 |
|
| 436 |
drop table t1, t2; |
| 437 |
|
| 438 |
# bug#57827 : Don't load dd columns when building unique index |
| 439 |
# Build a unique index in a table with DD columns to give |
| 440 |
# some coverage of the scenario |
| 441 |
# |
| 442 |
|
| 443 |
CREATE LOGFILE GROUP lg1 |
| 444 |
ADD UNDOFILE 'undofile.dat' |
| 445 |
INITIAL_SIZE 8M |
| 446 |
UNDO_BUFFER_SIZE = 1M engine=ndb; |
| 447 |
|
| 448 |
CREATE TABLESPACE ts1 |
| 449 |
ADD DATAFILE 'datafile.dat' |
| 450 |
USE LOGFILE GROUP lg1 |
| 451 |
INITIAL_SIZE 32M |
| 452 |
ENGINE NDB; |
| 453 |
|
| 454 |
use test; |
| 455 |
|
| 456 |
create table t1 (pk int primary key, un int, data varchar(500) storage disk) tablespace ts1 engine=ndb; |
| 457 |
|
| 458 |
insert into t1 values |
| 459 |
(1,1,repeat('B', 500)), |
| 460 |
(2,2,repeat('B', 500)), |
| 461 |
(3,3,repeat('B', 500)), |
| 462 |
(4,4,repeat('B', 500)), |
| 463 |
(5,5,repeat('B', 500)), |
| 464 |
(6,6,repeat('B', 500)), |
| 465 |
(7,7,repeat('B', 500)), |
| 466 |
(8,8,repeat('B', 500)), |
| 467 |
(9,9,repeat('B', 500)), |
| 468 |
(10,10,repeat('B', 500)), |
| 469 |
(11,11,repeat('B', 500)), |
| 470 |
(12,12,repeat('B', 500)), |
| 471 |
(13,13,repeat('B', 500)), |
| 472 |
(14,14,repeat('B', 500)), |
| 473 |
(15,15,repeat('B', 500)); |
| 474 |
|
| 475 |
alter table t1 add unique(un); |
| 476 |
|
| 477 |
drop table t1; |
| 478 |
alter tablespace ts1 drop datafile 'datafile.dat'; |
| 479 |
drop tablespace ts1; |
| 480 |
drop logfile group lg1 engine=ndb; |
| 481 |
|
| 482 |
# bug#57032 'NOT NULL' evaluation is incorrect when using an 'unique index |
| 483 |
|
| 484 |
--echo # bug#57032 |
| 485 |
|
| 486 |
create table t1 ( |
| 487 |
a int not null, |
| 488 |
b int, |
| 489 |
primary key using hash (a), |
| 490 |
unique key using hash (b) |
| 491 |
) |
| 492 |
engine ndb; |
| 493 |
|
| 494 |
insert into t1 values |
| 495 |
(0,0),(1,1),(2,2),(3,3),(4,4), |
| 496 |
(5,null),(6,null),(7,null),(8,null),(9,null); |
| 497 |
|
| 498 |
|
| 499 |
set optimizer_switch = 'engine_condition_pushdown=off'; |
| 500 |
# failed, empty result |
| 501 |
select a from t1 where b is not null order by a; |
| 502 |
# worked |
| 503 |
select a from t1 where b is null order by a; |
| 504 |
|
| 505 |
set optimizer_switch = 'engine_condition_pushdown=on'; |
| 506 |
# failed, empty result |
| 507 |
select a from t1 where b is not null order by a; |
| 508 |
# worked |
| 509 |
select a from t1 where b is null order by a; |
| 510 |
|
| 511 |
set optimizer_switch = @old_os; |
| 512 |
|
| 513 |
drop table t1; |
| 514 |
|
| 515 |
create table t1 ( |
| 516 |
a int not null, |
| 517 |
b int, |
| 518 |
c int, |
| 519 |
primary key using hash (a), |
| 520 |
unique key using hash (b,c) |
| 521 |
) |
| 522 |
engine ndb; |
| 523 |
|
| 524 |
insert into t1 values |
| 525 |
(0,0,0),(1,1,1),(2,2,1),(3,3,1),(4,4,2), |
| 526 |
(5,null,0),(6,null,1),(7,null,1),(8,null,1),(9,null,2), |
| 527 |
(10,0,null),(11,1,null),(12,1,null),(13,1,null),(14,2,null), |
| 528 |
(15,null,null),(16,null,null),(17,null,null),(18,null,null),(19,null,null); |
| 529 |
|
| 530 |
|
| 531 |
set optimizer_switch = 'engine_condition_pushdown=off'; |
| 532 |
# worked |
| 533 |
select a from t1 where b is not null and c = 1 order by a; |
| 534 |
# failed, empty result |
| 535 |
select a from t1 where b is null and c = 1 order by a; |
| 536 |
# failed, empty result |
| 537 |
select a from t1 where b = 1 and c is null order by a; |
| 538 |
# worked |
| 539 |
select a from t1 where b is null and c is null order by a; |
| 540 |
select a from t1 where b is not null and c is null order by a; |
| 541 |
select a from t1 where b is null and c is not null order by a; |
| 542 |
select a from t1 where b is not null and c is not null order by a; |
| 543 |
|
| 544 |
set optimizer_switch = 'engine_condition_pushdown=on'; |
| 545 |
# worked |
| 546 |
select a from t1 where b is not null and c = 1 order by a; |
| 547 |
# failed, empty result |
| 548 |
select a from t1 where b is null and c = 1 order by a; |
| 549 |
# failed, empty result |
| 550 |
select a from t1 where b = 1 and c is null order by a; |
| 551 |
# worked |
| 552 |
select a from t1 where b is null and c is null order by a; |
| 553 |
select a from t1 where b is not null and c is null order by a; |
| 554 |
select a from t1 where b is null and c is not null order by a; |
| 555 |
select a from t1 where b is not null and c is not null order by a; |
| 556 |
|
| 557 |
set optimizer_switch = @old_os; |
| 558 |
|
| 559 |
drop table t1; |
| 560 |
|
| 561 |
# |
| 562 |
# Bug#58750: 'out of connection objects' due to missing close() |
| 563 |
# of prev. full_table_scan() |
| 564 |
# |
| 565 |
create table t1 (pk int primary key, a int) engine=ndb |
| 566 |
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"; |
| 567 |
create table t2 (pk int primary key, uq int, a int, |
| 568 |
unique key ix(uq,a) USING HASH) engine=ndb |
| 569 |
comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"; |
| 570 |
|
| 571 |
insert into t2 values |
| 572 |
(0,0,0), (1,1,0), (2,2,0), (3,3,0), (4,4,0), |
| 573 |
(5,5,1), (6,6,1), (7,7,1), (8,8,1), (9,9,1); |
| 574 |
|
| 575 |
## |
| 576 |
# 10^4 cross product on t2 creates 10.000 rows: |
| 577 |
## |
| 578 |
insert into t1 |
| 579 |
select |
| 580 |
t1.pk + t2.pk*10 + t3.pk*100 + t4.pk*1000, t1.a |
| 581 |
from |
| 582 |
t2 as t1, t2 as t2, t2 as t3, t2 as t4; |
| 583 |
|
| 584 |
# Execute a 'scan(t1) join REF(t2) using ix' |
| 585 |
# - Where condition 't2.uq IS NULL' in combination with |
| 586 |
# join cond. 't2.a=t1.a' will create the REF keys (NULL,t1.a). |
| 587 |
# This will initiate a ::full_table_scan() which fails to |
| 588 |
# close any open table scans. |
| 589 |
# - Table scan(t1) will 'drive' the case above and eventually |
| 590 |
# cause all ScanOperations / hupp'ed transactions to have been |
| 591 |
# consumed |
| 592 |
# |
| 593 |
# Note that after fixing Bug#33474345, see below, the original test case |
| 594 |
# didn't behave as intended. Thus it was rewritten from using |
| 595 |
# 't2.uq IS NULL' to 't2.uq <=> t1.a+NULL'. |
| 596 |
# I.e. from a known NULL-valued key, to an expression evaluating |
| 597 |
# to NULL at execution time. Note the usage of '<=>' operator which |
| 598 |
# compare NULLs as equal. |
| 599 |
# |
| 600 |
# The original test case became a test case for bug#33474345, where |
| 601 |
# we try to avoid doing such repeated scans whenever possible. |
| 602 |
|
| 603 |
explain |
| 604 |
SELECT STRAIGHT_JOIN count(*) FROM |
| 605 |
t1 JOIN t2 ON t2.a=t1.a where t2.uq <=> t1.a+NULL; |
| 606 |
SELECT STRAIGHT_JOIN count(*) FROM |
| 607 |
t1 JOIN t2 ON t2.a=t1.a where t2.uq <=> t1.a+NULL; |
| 608 |
|
| 609 |
# |
| 610 |
# Bug#33474345: |
| 611 |
# Avoid table REF-access in query plan, |
| 612 |
# when handler does full table scan. |
| 613 |
# |
| 614 |
# There is a known NULL-constant in the key for a potential |
| 615 |
# REF / Index lookup access on t2, using the 'ix' HASH index. Thus the |
| 616 |
# handler would have fallen back to do repeated full table scan. |
| 617 |
# Avoid that when it can be easily detected by the optimizer. |
| 618 |
# |
| 619 |
# Test case below will now do a single table scan on t2 and hash-join(t1,t2), |
| 620 |
# which is about 10x faster for this query |
| 621 |
|
| 622 |
--replace_regex / \(cost=.*// |
| 623 |
explain format=tree |
| 624 |
SELECT STRAIGHT_JOIN count(*) FROM |
| 625 |
t1 JOIN t2 ON t2.a=t1.a where t2.uq IS NULL; |
| 626 |
SELECT STRAIGHT_JOIN count(*) FROM |
| 627 |
t1 JOIN t2 ON t2.a=t1.a where t2.uq IS NULL; |
| 628 |
|
| 629 |
drop table t1,t2; |
| 630 |
|
| 631 |
# |
| 632 |
# Bug #16678033 IN-SUBQUERY WITH TRUE/FALSE/UNKNOWN TEST |
| 633 |
# MAY RETURN INCORRECT RESULT OR ASSERT |
| 634 |
# |
| 635 |
CREATE TABLE t1 ( |
| 636 |
i int |
| 637 |
) ENGINE=ndbcluster |
| 638 |
COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"; |
| 639 |
|
| 640 |
INSERT INTO t1 VALUES (1); |
| 641 |
|
| 642 |
CREATE TABLE t2 ( |
| 643 |
col_int_unique int, |
| 644 |
UNIQUE KEY ix1 (col_int_unique) USING HASH |
| 645 |
) ENGINE=ndbcluster |
| 646 |
COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"; |
| 647 |
|
| 648 |
INSERT INTO t2 VALUES (42); |
| 649 |
|
| 650 |
EXPLAIN SELECT * FROM t1 WHERE i IN |
| 651 |
(SELECT t2.col_int_unique FROM t2) IS TRUE; |
| 652 |
|
| 653 |
# Remember 'row' conter when test started. |
| 654 |
--disable_warnings |
| 655 |
create temporary table row_counts_at_startup |
| 656 |
select * |
| 657 |
from performance_schema.session_status |
| 658 |
WHERE variable_name LIKE 'NDB_API_READ_ROW_COUNT'; |
| 659 |
--enable_warnings |
| 660 |
|
| 661 |
SELECT * FROM t1 WHERE i IN |
| 662 |
(SELECT t2.col_int_unique FROM t2) IS TRUE; |
| 663 |
|
| 664 |
SELECT * FROM t1 WHERE i IN |
| 665 |
(SELECT t2.col_int_unique FROM t2) IS FALSE; |
| 666 |
|
| 667 |
SELECT * FROM t1 WHERE i IN |
| 668 |
(SELECT t2.col_int_unique FROM t2) IS UNKNOWN; |
| 669 |
|
| 670 |
SELECT * FROM t1 WHERE i IN |
| 671 |
(SELECT t2.col_int_unique FROM t2) IS NOT TRUE; |
| 672 |
|
| 673 |
SELECT * FROM t1 WHERE i IN |
| 674 |
(SELECT t2.col_int_unique FROM t2) IS NOT FALSE; |
| 675 |
|
| 676 |
SELECT * FROM t1 WHERE i IN |
| 677 |
(SELECT t2.col_int_unique FROM t2) IS NOT UNKNOWN; |
| 678 |
|
| 679 |
# Verify row counter at end: |
| 680 |
# Used to verify that correct hi/low limits was set for |
| 681 |
# the EQ_RANGE checking for col_int_unique NULL values |
| 682 |
--disable_warnings |
| 683 |
create temporary table row_counts_at_end |
| 684 |
select * |
| 685 |
from performance_schema.session_status |
| 686 |
WHERE variable_name LIKE 'NDB_API_READ_ROW_COUNT'; |
| 687 |
--enable_warnings |
| 688 |
|
| 689 |
select row_counts_at_end.VARIABLE_NAME, |
| 690 |
row_counts_at_end.VARIABLE_VALUE - row_counts_at_startup.VARIABLE_VALUE |
| 691 |
from row_counts_at_end, row_counts_at_startup |
| 692 |
where row_counts_at_end.VARIABLE_NAME = row_counts_at_startup.VARIABLE_NAME; |
| 693 |
|
| 694 |
drop table row_counts_at_end; |
| 695 |
drop table row_counts_at_startup; |
| 696 |
|
| 697 |
drop table t1,t2; |
| 698 |
|
| 699 |
--echo # |
| 700 |
--echo # Bug#21777589 : UNIQUE KEY IS PARTIALLY DROPPED FROM MYSQLD EVEN WHEN A 'DROP INDEX' FAILS |
| 701 |
--echo # Bug#22104597 : DROP INDEX FAILURE CORRUPTS THE INDEX METADATA IN MYSQLD |
| 702 |
--echo # |
| 703 |
|
| 704 |
--echo # Create table t1 with multiple keys |
| 705 |
create table t1( |
| 706 |
a int, |
| 707 |
b int, |
| 708 |
unique key uk1(a), |
| 709 |
unique key uk2(b) using hash |
| 710 |
)engine=ndb; |
| 711 |
|
| 712 |
--echo # Create table t2 with a fk based on t1's uk |
| 713 |
create table t2( |
| 714 |
a int, |
| 715 |
b int, |
| 716 |
c int, |
| 717 |
foreign key fk1(a) references t1(a), |
| 718 |
unique key uk3(b), |
| 719 |
unique key uk4(c) |
| 720 |
)engine=ndb; |
| 721 |
|
| 722 |
--echo # Try dropping uk1. should fail with correct uk constraint |
| 723 |
--error ER_DROP_INDEX_FK |
| 724 |
drop index uk1 on t1; |
| 725 |
|
| 726 |
--echo # Perform a select from t1 to verify the index has not been partially dropped |
| 727 |
--echo # also to ensure the ordering in m_index is intact |
| 728 |
select * from t1; |
| 729 |
|
| 730 |
--echo # Try dropping uk2 now. should be dropped without any problem |
| 731 |
drop index uk2 on t1; |
| 732 |
|
| 733 |
--echo # Verify through show create table |
| 734 |
--disable_warnings |
| 735 |
show create table t1; |
| 736 |
--enable_warnings |
| 737 |
|
| 738 |
--echo # Make sure dropping multiple indexes together works |
| 739 |
alter table t2 drop index uk3, drop index uk4; |
| 740 |
|
| 741 |
--echo # Verify through show create table |
| 742 |
--disable_warnings |
| 743 |
show create table t1; |
| 744 |
--enable_warnings |
| 745 |
|
| 746 |
--echo # cleanup |
| 747 |
drop table t2,t1; |
| 748 |
|
| 749 |
--echo # |
| 750 |
--echo # Bug#22110051 : ASSERTION IN PROTOCOL::END_STATEMENT DURING DROP INDEX |
| 751 |
--echo # |
| 752 |
|
| 753 |
--echo # Create table t1 with unique key |
| 754 |
create table t1( |
| 755 |
a int not null, |
| 756 |
b int not null, |
| 757 |
unique key uk1(a), |
| 758 |
unique key uk2(b) |
| 759 |
)engine=ndb; |
| 760 |
|
| 761 |
--echo # Create table t2 with a fk mapped to t1(a) |
| 762 |
create table t2( |
| 763 |
a int, |
| 764 |
constraint fk1 foreign key (a) references t1(a) |
| 765 |
)engine=ndb; |
| 766 |
|
| 767 |
--echo # desc t1 to show the implicit pk |
| 768 |
desc t1; |
| 769 |
|
| 770 |
--echo # Drop index uk1 - it should fail |
| 771 |
--error ER_DROP_INDEX_FK |
| 772 |
drop index uk1 on t1; |
| 773 |
|
| 774 |
--echo # Verify table is intact |
| 775 |
--disable_warnings |
| 776 |
show create table t1; |
| 777 |
--enable_warnings |
| 778 |
|
| 779 |
--echo # Modify column on t1 - remove not null |
| 780 |
alter table t1 modify column a int; |
| 781 |
|
| 782 |
--echo # verify that pk is now on b. |
| 783 |
desc t1; |
| 784 |
--disable_warnings |
| 785 |
show create table t1; |
| 786 |
--enable_warnings |
| 787 |
--echo # Verify that the fk constraint is intact through inserts |
| 788 |
insert into t1 values (1,10), (2,20), (3,30); |
| 789 |
insert into t2 values (1), (2), (3); |
| 790 |
--error ER_NO_REFERENCED_ROW_2 |
| 791 |
insert into t2 values (4); |
| 792 |
|
| 793 |
--echo # Try removing uk2. should pass. |
| 794 |
alter table t1 drop index uk2; |
| 795 |
|
| 796 |
--echo # Verify that everything is fine. |
| 797 |
desc t1; |
| 798 |
--disable_warnings |
| 799 |
show create table t1; |
| 800 |
--enable_warnings |
| 801 |
--echo # Verify that the fk constraint is intact through inserts |
| 802 |
insert into t2 values (1), (2), (3); |
| 803 |
--error ER_NO_REFERENCED_ROW_2 |
| 804 |
insert into t2 values (4); |
| 805 |
|
| 806 |
--echo #cleanup |
| 807 |
drop table t2, t1; |
| 808 |
|
| 809 |
--echo # |
| 810 |
--echo # Bug#24444899 MYSQLD CRASHED WHILE DROPPING UNIQUE KEY ... |
| 811 |
--echo # - test read using unique key where keys are specified in |
| 812 |
--echo # different order than in the NDB table, thus testing |
| 813 |
--echo # the NDB_INDEX_DATA::Attrid_map mapping |
| 814 |
CREATE TABLE t1 ( |
| 815 |
a int PRIMARY KEY, |
| 816 |
b int, c int, d int, e int, f int, g int, h int, i int, |
| 817 |
UNIQUE INDEX unique_hgfedcb (h,f,d,e,g,c,b) |
| 818 |
) engine = ndb; |
| 819 |
CREATE TABLE t2 ( |
| 820 |
a int PRIMARY KEY, |
| 821 |
b int |
| 822 |
) engine = ndb; |
| 823 |
INSERT INTO t1 VALUES |
| 824 |
(1,2,3,4,5,6,7,8,40), |
| 825 |
(2,2,4,4,5,6,7,9,41), |
| 826 |
(3,2,5,4,5,6,7,10,42); |
| 827 |
INSERT INTO t2 VALUES |
| 828 |
(1,2); |
| 829 |
|
| 830 |
--echo # Read with pushed join through unique index and use mapping |
| 831 |
SELECT t1.i FROM t1, t2 |
| 832 |
WHERE t2.a = t1.a /* join with t2 to trigger pushed join */ AND |
| 833 |
t1.b = 2 AND t1.c = 3 AND t1.d = 4 AND |
| 834 |
t1.e = 5 AND t1.f = 6 AND t1.g = 7 AND |
| 835 |
t1.h = 8 /* use unique index on t1 */; |
| 836 |
|
| 837 |
DROP TABLE t1, t2; |
| 838 |
|
| 839 |
|
| 840 |
--echo # |
| 841 |
--echo # Bug#33317872 Incorrect Index selected leading to slower execution of queries |
| 842 |
--echo # |
| 843 |
--echo # When choosing between a Multi-range-read on an unique index, |
| 844 |
--echo # and a range scan on an ordered index, where few rows are expected |
| 845 |
--echo # to be returned, prefer the unique index. |
| 846 |
--echo # (An ordered index scan has higher cost, as all fragments are scanned) |
| 847 |
--echo # |
| 848 |
|
| 849 |
CREATE TABLE t ( |
| 850 |
delivery_id bigint unsigned NOT NULL AUTO_INCREMENT, |
| 851 |
msg_id int unsigned NOT NULL, |
| 852 |
auth_login_type char(1) DEFAULT NULL, |
| 853 |
auth_login_id varchar(64), |
| 854 |
PRIMARY KEY (delivery_id), |
| 855 |
UNIQUE KEY msg_id(msg_id,auth_login_type,auth_login_id), |
| 856 |
KEY auth_login_id(auth_login_id) |
| 857 |
) ENGINE=ndbcluster; |
| 858 |
|
| 859 |
disable_query_log; |
| 860 |
|
| 861 |
insert into t (msg_id, auth_login_type, auth_login_id) values |
| 862 |
(4866, '5', '91774132'), (4869, '5', '91884132'); |
| 863 |
insert into t (msg_id, auth_login_type, auth_login_id) values |
| 864 |
(4866, '5', '91774134'), (4869, '5', '91884134'); |
| 865 |
|
| 866 |
insert into t (msg_id, auth_login_type, auth_login_id) values |
| 867 |
(4866, '4', '91774133'), (4869, '4', '91884133'); |
| 868 |
insert into t (msg_id, auth_login_type, auth_login_id) values |
| 869 |
(4866, '6', '91774133'), (4869, '6', '91884133'); |
| 870 |
|
| 871 |
insert into t (msg_id, auth_login_type, auth_login_id) values |
| 872 |
(4865, '5', '91774133'), (4868, '5', '91774133'); |
| 873 |
insert into t (msg_id, auth_login_type, auth_login_id) values |
| 874 |
(4867, '5', '91774133'), (4870, '5', '91774133'); |
| 875 |
# ^^^ Add some more rows, none will match the query |
| 876 |
|
| 877 |
# Use these as base for filling in more rows |
| 878 |
insert into t (msg_id, auth_login_type, auth_login_id) select |
| 879 |
msg_id - 4800, auth_login_type, auth_login_id+10 from t; |
| 880 |
|
| 881 |
insert into t (msg_id, auth_login_type, auth_login_id) select |
| 882 |
msg_id + 1000, auth_login_type, auth_login_id+20 from t where msg_id < 100; |
| 883 |
insert into t (msg_id, auth_login_type, auth_login_id) select |
| 884 |
msg_id + 2000, auth_login_type, auth_login_id+30 from t where msg_id < 100; |
| 885 |
insert into t (msg_id, auth_login_type, auth_login_id) select |
| 886 |
msg_id + 3000, auth_login_type, auth_login_id+40 from t where msg_id < 100; |
| 887 |
|
| 888 |
insert into t (msg_id, auth_login_type, auth_login_id) select |
| 889 |
msg_id + 5000, auth_login_type, auth_login_id+50 from t where msg_id < 100; |
| 890 |
insert into t (msg_id, auth_login_type, auth_login_id) select |
| 891 |
msg_id + 6000, auth_login_type, auth_login_id+60 from t where msg_id < 100; |
| 892 |
insert into t (msg_id, auth_login_type, auth_login_id) select |
| 893 |
msg_id + 7000, auth_login_type, auth_login_id+70 from t where msg_id < 100; |
| 894 |
|
| 895 |
|
| 896 |
########################################## |
| 897 |
# Statistics is asynchronous: We can not predict when it |
| 898 |
# is available in the statistics cache, and thus which |
| 899 |
# statistics is being used for the query. Thus we |
| 900 |
# need to cheat: |
| 901 |
# |
| 902 |
# We 'analyze' without the rows to be select yet inserted. |
| 903 |
# -> statistics will predictable contain 0 rows to be returned. |
| 904 |
# Which is 'rounded' up to 2 rows, which are the lowest number of |
| 905 |
# rows allowed to be estimated from non unique indexes. |
| 906 |
# |
| 907 |
# Note that the estimated 2 rows is actually what _is_ in the table |
| 908 |
# as well, when we later insert these rows below! |
| 909 |
|
| 910 |
analyze table t; |
| 911 |
enable_query_log; |
| 912 |
|
| 913 |
########################################## |
| 914 |
# Then insert the rows we want returned, without being part of the statistics |
| 915 |
insert into t (msg_id, auth_login_type, auth_login_id) values |
| 916 |
(4866, '5', '81774133'), |
| 917 |
(4869, '5', '81774133'); |
| 918 |
# ^^^^ Rows we want from the query |
| 919 |
|
| 920 |
# optimizer trace, For debugging, or understanding of test case + bug: |
| 921 |
# |
| 922 |
# If enabled, we will find that optimizer first calculate the range-access |
| 923 |
# cost, using handler ::cost methods, and correctly find the msg_id to |
| 924 |
# have the lowest cost. |
| 925 |
# Possible REF-accesses are then investigated and estimated: However, these |
| 926 |
# cost estimates was 'pre-patch' based in an entirely different page/cache |
| 927 |
# cost metric, not being compare compatible with the former. A lower cost |
| 928 |
# was found for using REF access on auth_login_id index, incorrectly causing |
| 929 |
# this index to to be used for the table access. |
| 930 |
# |
| 931 |
#SET optimizer_trace_max_mem_size=1048576; # 1MB |
| 932 |
#SET end_markers_in_json=on; |
| 933 |
#SET optimizer_trace="enabled=on,one_line=off"; |
| 934 |
|
| 935 |
explain format=tree |
| 936 |
select * from t |
| 937 |
where auth_login_type='5' AND auth_login_id = '81774133' AND msg_id IN (4866,4869); |
| 938 |
|
| 939 |
#SELECT * FROM information_schema.optimizer_trace; |
| 940 |
|
| 941 |
drop table t; |