~drizzle-trunk/drizzle/development

create table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');

select id, code, name from t1 order by id;

update ignore t1 set id = 8, name = 'Sinisa' where id < 3;

select id, code, name from t1 order by id;

update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;

select id, code, name from t1 order by id;

drop table t1;

# A bit bigger test

# The 'replace_column' statements are needed because the cardinality calculated

# by innodb is not always the same between runs

CREATE TABLE t1 (

id int NOT NULL auto_increment,

parent_id int DEFAULT '0' NOT NULL,

level int DEFAULT '0' NOT NULL,

PRIMARY KEY (id),

KEY parent_id (parent_id),

KEY level (level)

) engine=innodb;

INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2);

update t1 set parent_id=parent_id+100;

select * from t1 where parent_id=102;

update t1 set id=id+1000;

update t1 set id=1024 where id=1009;

select * from t1;

update ignore t1 set id=id+1; # This will change all rows

select * from t1;

update ignore t1 set id=1023 where id=1010;

select * from t1 where parent_id=102;

--replace_column 9 #

explain select level from t1 where level=1;

--replace_column 9 #

explain select level,id from t1 where level=1;

--replace_column 9 #

explain select level,id,parent_id from t1 where level=1;

select level,id from t1 where level=1;

select level,id,parent_id from t1 where level=1;

alter table t1 ENGINE=innodb;

--replace_column 7 #

show keys from t1;

drop table t1;

# Test replace

CREATE TABLE t1 (

gesuchnr int DEFAULT '0' NOT NULL,

benutzer_id int DEFAULT '0' NOT NULL,

PRIMARY KEY (gesuchnr,benutzer_id)

) engine=innodb;

replace into t1 (gesuchnr,benutzer_id) values (2,1);

replace into t1 (gesuchnr,benutzer_id) values (1,1);

select * from t1;

drop table t1;

# test delete using hidden_primary_key

create table t1 (a int) engine=innodb;

insert into t1 values (1), (2);

alter table t1 engine=innodb;

delete from t1 where a = 1;

select * from t1;

check table t1;

drop table t1;

100

101

create table t1 (a int,b varchar(20)) engine=innodb;

102

insert into t1 values (1,""), (2,"testing");

103

delete from t1 where a = 1;

104

select * from t1;

105

create index skr on t1 (a);

106

insert into t1 values (3,""), (4,"testing");

107

analyze table t1;

108

--replace_column 7 #

109

show keys from t1;

110

drop table t1;

111

112

113

# Test of reading on secondary key with may be null

114

115

create table t1 (a int,b varchar(20),key(a)) engine=innodb;

116

insert into t1 values (1,""), (2,"testing");

117

select * from t1 where a = 1;

118

drop table t1;

119

120

121

# Test rollback

122

123

124

create table t1 (n int not null primary key) engine=innodb;

125

set autocommit=0;

126

insert into t1 values (4);

127

rollback;

128

select n, "after rollback" from t1;

129

insert into t1 values (4);

130

commit;

131

select n, "after commit" from t1;

132

commit;

133

insert into t1 values (5);

134

insert into t1 values (4);

135

commit;

136

select n, "after commit" from t1;

137

set autocommit=1;

138

insert into t1 values (6);

139

insert into t1 values (4);

140

select n from t1;

141

set autocommit=0;

142

143

# savepoints

144

145

begin;

146

savepoint `my_savepoint`;

147

insert into t1 values (7);

148

savepoint `savept2`;

149

insert into t1 values (3);

150

select n from t1;

151

savepoint savept3;

152

rollback to savepoint savept2;

153

--error 1305

154

rollback to savepoint savept3;

155

rollback to savepoint savept2;

156

release savepoint `my_savepoint`;

157

select n from t1;

158

rollback to savepoint `my_savepoint`;

159

--error 1305

160

rollback to savepoint savept2;

161

insert into t1 values (8);

162

savepoint sv;

163

commit;

164

savepoint sv;

165

set autocommit=1;

166

# nop

167

rollback;

168

drop table t1;

169

170

171

# Test for commit and FLUSH TABLES WITH READ LOCK

172

173

174

create table t1 (n int not null primary key) engine=innodb;

175

start transaction;

176

insert into t1 values (4);

177

flush tables with read lock;

178

179

# Current code can't handle a read lock in middle of transaction

180

#--error 1223;

181

commit;

182

unlock tables;

183

commit;

184

select * from t1;

185

drop table t1;

186

187

188

# Testing transactions

189

190

191

create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb;

192

begin;

193

insert into t1 values(1,'hamdouni');

194

select id as afterbegin_id,nom as afterbegin_nom from t1;

195

rollback;

196

select id as afterrollback_id,nom as afterrollback_nom from t1;

197

set autocommit=0;

198

insert into t1 values(2,'mysql');

199

select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;

200

rollback;

201

select id as afterrollback_id,nom as afterrollback_nom from t1;

202

set autocommit=1;

203

drop table t1;

204

205

206

# Simple not autocommit test

207

208

209

CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb;

210

insert into t1 values ('pippo', 12);

211

insert into t1 values ('pippo', 12); # Gives error

212

delete from t1;

213

delete from t1 where id = 'pippo';

214

select * from t1;

215

216

insert into t1 values ('pippo', 12);

217

set autocommit=0;

218

delete from t1;

219

rollback;

220

select * from t1;

221

delete from t1;

222

commit;

223

select * from t1;

224

drop table t1;

225

226

227

# Test of active transactions

228

229

230

create table t1 (a integer) engine=innodb;

231

start transaction;

232

rename table t1 to t2;

233

create table t1 (b integer) engine=innodb;

234

insert into t1 values (1);

235

rollback;

236

drop table t1;

237

rename table t2 to t1;

238

drop table t1;

239

set autocommit=1;

240

241

242

# The following simple tests failed at some point

243

244

245

CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb;

246

INSERT INTO t1 VALUES (1, 'Jochen');

247

select * from t1;

248

drop table t1;

249

250

CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb;

251

set autocommit=0;

252

INSERT INTO t1 SET _userid='marc@anyware.co.uk';

253

COMMIT;

254

SELECT * FROM t1;

255

SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';

256

drop table t1;

257

set autocommit=1;

258

259

260

# Test when reading on part of unique key

261

262

CREATE TABLE t1 (

263

user_id int DEFAULT '0' NOT NULL,

264

name varchar(100),

265

phone varchar(100),

266

ref_email varchar(100) DEFAULT '' NOT NULL,

267

detail varchar(200),

268

PRIMARY KEY (user_id,ref_email)

269

)engine=innodb;

270

271

INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar');

272

select * from t1 where user_id=10292;

273

INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');

274

select * from t1 where user_id=10292;

275

select * from t1 where user_id>=10292;

276

select * from t1 where user_id>10292;

277

select * from t1 where user_id<10292;

278

drop table t1;

279

280

281

# Test that keys are created in right order

282

283

284

CREATE TABLE t1 (a int not null, b int not null,c int not null,

285

key(a),primary key(a,b), unique(c),key(a),unique(b));

286

--replace_column 7 #

287

show index from t1;

288

drop table t1;

289

290

291

# Test of ALTER TABLE and innodb tables

292

293

294

create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));

295

alter table t1 engine=innodb;

296

insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');

297

select * from t1;

298

update t1 set col2='7' where col1='4';

299

select * from t1;

300

alter table t1 add co3 int not null;

301

select * from t1;

302

update t1 set col2='9' where col1='2';

303

select * from t1;

304

drop table t1;

305

306

307

# INSERT INTO innodb tables

308

309

310

create table t1 (a int not null , b int, primary key (a)) engine = innodb;

311

create TEMPORARY table t2 (a int not null , b int, primary key (a)) engine = myisam;

312

insert into t1 VALUES (1,3) , (2,3), (3,3);

313

select * from t1;

314

insert into t2 select * from t1;

315

select * from t2;

316

delete from t1 where b = 3;

317

select * from t1;

318

insert into t1 select * from t2;

319

select * from t1;

320

select * from t2;

321

drop table t1,t2;

322

323

324

# ORDER BY on not primary key

325

326

327

CREATE TABLE t1 (

328

user_name varchar(12),

329

password text,

330

subscribed char(1),

331

user_id int DEFAULT '0' NOT NULL,

332

quota bigint,

333

weight double,

334

access_date date,

335

approved datetime,

336

dummy_primary_key int NOT NULL auto_increment,

337

PRIMARY KEY (dummy_primary_key)

338

) ENGINE=innodb;

339

INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','2000-09-07 23:06:59',1);

340

INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','2000-09-07 23:06:59',2);

341

INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','2000-09-07 23:06:59',3);

342

INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','2000-09-07 23:06:59',4);

343

INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','2000-09-07 23:06:59',5);

344

select user_name, password , subscribed, user_id, quota, weight, access_date, approved, dummy_primary_key from t1 order by user_name;

345

drop table t1;

346

347

348

# Testing of tables without primary keys

349

350

351

CREATE TABLE t1 (

352

id int NOT NULL auto_increment,

353

parent_id int DEFAULT '0' NOT NULL,

354

level int DEFAULT '0' NOT NULL,

355

KEY (id),

356

KEY parent_id (parent_id),

357

KEY level (level)

358

) engine=innodb;

359

360

INSERT INTO t1 values (179,5,2);

361

update t1 set parent_id=parent_id+100;

362

select * from t1 where parent_id=102;

363

update t1 set id=id+1000;

364

update t1 set id=1024 where id=1009;

365

select * from t1;

366

update ignore t1 set id=id+1; # This will change all rows

367

select * from t1;

368

update ignore t1 set id=1023 where id=1010;

369

select * from t1 where parent_id=102;

370

--replace_column 9 #

371

explain select level from t1 where level=1;

372

select level,id from t1 where level=1;

373

select level,id,parent_id from t1 where level=1;

374

select level,id from t1 where level=1 order by id;

375

delete from t1 where level=1;

376

select * from t1;

377

drop table t1;

378

379

380

# Test of index only reads

381

382

CREATE TABLE t1 (

383

sca_code char(6) NOT NULL,

384

cat_code char(6) NOT NULL,

385

sca_desc varchar(50),

386

lan_code char(2) NOT NULL,

387

sca_pic varchar(100),

388

sca_sdesc varchar(50),

389

sca_sch_desc varchar(16),

390

PRIMARY KEY (sca_code, cat_code, lan_code),

391

INDEX sca_pic (sca_pic)

392

) engine = innodb ;

393

394

INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING');

395

select count(*) from t1 where sca_code = 'PD';

396

select count(*) from t1 where sca_code <= 'PD';

397

select count(*) from t1 where sca_pic is null;

398

alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);

399

select count(*) from t1 where sca_code='PD' and sca_pic is null;

400

select count(*) from t1 where cat_code='E';

401

alter table t1 drop index sca_pic, add index (sca_pic, cat_code);

402

select count(*) from t1 where sca_code='PD' and sca_pic is null;

403

select count(*) from t1 where sca_pic >= 'n';

404

select sca_pic from t1 where sca_pic is null;

405

update t1 set sca_pic="test" where sca_pic is null;

406

delete from t1 where sca_code='pd';

407

drop table t1;

408

409

410

# Test of opening table twice and timestamps

411

412

set @a:=now();

413

CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;

414

insert into t1 (a) values(1),(2),(3);

415

select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;

416

select a from t1 natural join t1 as t2 where b >= @a order by a;

417

update t1 set a=5 where a=1;

418

select a from t1;

419

drop table t1;

420

421

422

# Test with variable length primary key

423

424

create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb;

425

insert into t1 values("hello",1),("world",2);

426

select * from t1 order by b desc;

427

alter table t1 engine=innodb;

428

--replace_column 7 #

429

show keys from t1;

430

drop table t1;

431

432

433

# Test of create index with NULL columns

434

435

create table t1 (i int, j int ) ENGINE=innodb;

436

insert into t1 values (1,2);

437

select * from t1 where i=1 and j=2;

438

create index ax1 on t1 (i,j);

439

select * from t1 where i=1 and j=2;

440

drop table t1;

441

442

443

# Test min-max optimization

444

445

446

CREATE TABLE t1 (

447

a int NOT NULL,

448

b int NOT NULL,

449

UNIQUE (a, b)

450

) ENGINE = innodb;

451

452

INSERT INTO t1 VALUES (1, 1);

453

SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;

454

drop table t1;

455

456

457

# Test INSERT DELAYED

458

459

460

CREATE TABLE t1 (a int NOT NULL) engine=innodb;

461

# Can't test this in 3.23

462

# INSERT DELAYED INTO t1 VALUES (1);

463

INSERT INTO t1 VALUES (1);

464

SELECT * FROM t1;

465

DROP TABLE t1;

466

467

468

469

# Crash when using many tables (Test case by Jeremy D Zawodny)

470

471

472

create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = innodb;

473

insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);

474

--replace_column 9 #

475

explain select * from t1 where a > 0 and a < 50;

476

drop table t1;

477

478

479

# Test prefix key

480

481

create table t1 (a char(20), unique (a(5))) engine=innodb;

482

drop table t1;

483

create table t1 (a char(20), index (a(5))) engine=innodb;

484

show create table t1;

485

drop table t1;

486

487

488

# Test using temporary table and auto_increment

489

490

491

create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;

492

insert into t1 values (NULL),(NULL),(NULL);

493

delete from t1 where a=3;

494

insert into t1 values (NULL);

495

select * from t1;

496

alter table t1 add b int;

497

select * from t1;

498

drop table t1;

499

500

#Slashdot bug

501

## Heikki had nevered considered the possibility of a second key that could

502

## be used as a primary key for replace. This is from the table that

503

## generates topics and "vars" -Brian

504

create table t1

505

(

506

id int auto_increment primary key,

507

name varchar(32) not null,

508

value text not null,

509

uid int not null,

510

unique key(name,uid)

511

) engine=innodb;

512

insert into t1 values (1,'one','one value',101),

513

(2,'two','two value',102),(3,'three','three value',103);

514

replace into t1 (value,name,uid) values ('other value','two',102);

515

delete from t1 where uid=102;

516

replace into t1 (value,name,uid) values ('other value','two',102);

517

replace into t1 (value,name,uid) values ('other value','two',102);

518

select * from t1;

519

drop table t1;

520

521

522

# Test DROP DATABASE

523

524

525

create database mysqltest;

526

create table mysqltest.t1 (a int not null) engine= innodb;

527

insert into mysqltest.t1 values(1);

528

create TEMPORARY table mysqltest.t2 (a int not null) engine= myisam;

529

insert into mysqltest.t2 values(1);

530

create temporary table mysqltest.t3 (a int not null) engine= MEMORY;

531

insert into mysqltest.t3 values(1);

532

commit;

533

drop database mysqltest;

534

# Don't check error message

535

--error 1049

536

show tables from mysqltest;

537

538

539

# Test truncate table with and without auto_commit

540

541

542

set autocommit=0;

543

create table t1 (a int not null) engine= innodb;

544

insert into t1 values(1),(2);

545

truncate table t1;

546

commit;

547

truncate table t1;

548

truncate table t1;

549

select * from t1;

550

insert into t1 values(1),(2);

551

delete from t1;

552

select * from t1;

553

commit;

554

drop table t1;

555

set autocommit=1;

556

557

create table t1 (a int not null) engine= innodb;

558

insert into t1 values(1),(2);

559

truncate table t1;

560

insert into t1 values(1),(2);

561

select * from t1;

562

truncate table t1;

563

insert into t1 values(1),(2);

564

delete from t1;

565

select * from t1;

566

drop table t1;

567

568

569

# Test of how ORDER BY works when doing it on the whole table

570

571

572

create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb;

573

insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);

574

--replace_column 9 #

575

explain select * from t1 order by a;

576

--replace_column 9 #

577

explain select * from t1 order by b;

578

--replace_column 9 #

579

explain select * from t1 order by c;

580

--replace_column 9 #

581

explain select a from t1 order by a;

582

--replace_column 9 #

583

explain select b from t1 order by b;

584

--replace_column 9 #

585

explain select a,b from t1 order by b;

586

--replace_column 9 #

587

explain select a,b from t1;

588

--replace_column 9 #

589

explain select a,b,c from t1;

590

drop table t1;

591

592

593

# Check describe

594

595

596

create table t1 (t int not null default 1, key (t)) engine=innodb;

597

desc t1;

598

drop table t1;

599

600

601

# A simple test with some isolation levels

602

# TODO: Make this into a test using replication to really test how

603

# this works.

604

605

606

create table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;

607

608

BEGIN;

609

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

610

SELECT @@tx_isolation,@@global.tx_isolation;

611

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');

612

select id, code, name from t1 order by id;

613

COMMIT;

614

615

BEGIN;

616

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

617

insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');

618

select id, code, name from t1 order by id;

619

COMMIT;

620

621

BEGIN;

622

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

623

insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');

624

select id, code, name from t1 order by id;

625

COMMIT;

626

DROP TABLE t1;

627

628

629

# Testing of IFNULL

630

631

create table t1 (a int, b int) engine=innodb;

632

insert into t1 values(20,null);

633

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

634

t2.b=t3.a;

635

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

636

t2.b=t3.a order by 1;

637

insert into t1 values(10,null);

638

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

639

t2.b=t3.a order by 1;

640

drop table t1;

641

642

643

# Test of read_through not existing const_table

644

645

646

create TEMPORARY table t1 (a varchar(10) not null) engine=myisam;

647

create table t2 (b varchar(10) not null unique) engine=innodb;

648

select t1.a from t1,t2 where t1.a=t2.b;

649

drop table t1,t2;

650

create table t1 (a int not null, b int, primary key (a)) engine = innodb;

651

create table t2 (a int not null, b int, primary key (a)) engine = innodb;

652

insert into t1 values (10, 20);

653

insert into t2 values (10, 20);

654

drop table t1,t2;

655

656

657

# Test of range_optimizer

658

659

660

set autocommit=0;

661

662

CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

663

664

CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

665

666

CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;

667

668

INSERT INTO t3 VALUES("my-test-1", "my-test-2");

669

COMMIT;

670

671

INSERT INTO t1 VALUES("this-key", "will disappear");

672

INSERT INTO t2 VALUES("this-key", "will also disappear");

673

DELETE FROM t3 WHERE id1="my-test-1";

674

675

SELECT * FROM t1;

676

SELECT * FROM t2;

677

SELECT * FROM t3;

678

ROLLBACK;

679

680

SELECT * FROM t1;

681

SELECT * FROM t2;

682

SELECT * FROM t3;

683

SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;

684

COMMIT;

685

set autocommit=1;

686

DROP TABLE t1,t2,t3;

687

688

689

# Check update with conflicting key

690

691

692

CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;

693

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

694

# We need the a < 1000 test here to quard against the halloween problems

695

UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;

696

SELECT * from t1;

697

drop table t1;

698

699

700

# Test that MySQL priorities clustered indexes

701

702

create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;

703

create table t2 (a int not null auto_increment primary key, b int);

704

insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);

705

insert into t2 (a) select b from t1;

706

insert into t1 (b) select b from t2;

707

insert into t2 (a) select b from t1;

708

insert into t1 (a) select b from t2;

709

insert into t2 (a) select b from t1;

710

insert into t1 (a) select b from t2;

711

insert into t2 (a) select b from t1;

712

insert into t1 (a) select b from t2;

713

insert into t2 (a) select b from t1;

714

insert into t1 (a) select b from t2;

715

select count(*) from t1;

716

--replace_column 9 #

717

explain select * from t1 where c between 1 and 2500;

718

update t1 set c=a;

719

--replace_column 9 #

720

explain select * from t1 where c between 1 and 2500;

721

drop table t1,t2;

722

723

724

# Test of UPDATE ... ORDER BY

725

726

727

create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;

728

729

insert into t1 (id) values (null),(null),(null),(null),(null);

730

update t1 set fk=69 where fk is null order by id limit 1;

731

SELECT * from t1;

732

drop table t1;

733

734

create table t1 (a int not null, b int not null, key (a));

735

insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);

736

SET @tmp=0;

737

update t1 set b=(@tmp:=@tmp+1) order by a;

738

update t1 set b=99 where a=1 order by b asc limit 1;

739

update t1 set b=100 where a=1 order by b desc limit 2;

740

update t1 set a=a+10+b where a=1 order by b;

741

select * from t1 order by a,b;

742

drop table t1;

743

744

745

# test autoincrement with TRUNCATE

746

747

748

SET AUTOCOMMIT=1;

749

create table t1 (a integer auto_increment primary key) engine=innodb;

750

insert into t1 (a) values (NULL),(NULL);

751

truncate table t1;

752

insert into t1 (a) values (NULL),(NULL);

753

SELECT * from t1;

754

drop table t1;

755

756

757

# Test dictionary handling with spaceand quoting

758

759

760

CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;

761

CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`) ON DELETE CASCADE ) ENGINE=INNODB;

762

#show create table t2;

763

drop table t2,t1;

764

765

766

# test for recursion depth limit

767

768

create table t1(

769

id int primary key,

770

pid int,

771

index(pid),

772

foreign key(pid) references t1(id) on delete cascade) engine=innodb;

773

insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),

774

(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);

775

delete from t1 where id=0;

776

delete from t1 where id=15;

777

delete from t1 where id=0;

778

779

drop table t1;

780

781

782

# Test timestamps

783

784

785

CREATE TABLE t1 (col1 int) ENGINE=InnoDB;

786

CREATE TABLE t2 (col1 int, stamp TIMESTAMP,INDEX stamp_idx (stamp)) ENGINE=InnoDB;

787

insert into t1 values (1),(2),(3);

788

# Note that timestamp 3 is wrong

789

insert into t2 values (1, 20020204110000),(2, 20020204110001),(4,20020204110002 ),(5,20020204110003);

790

SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <

791

'20020204120000' GROUP BY col1;

792

drop table t1,t2;

793

794

795

# Test by Francois MASUREL

796

797

798

CREATE TABLE t1 (

799

`id` int NOT NULL auto_increment,

800

`id_object` int default '0',

801

`id_version` int NOT NULL default '1',

802

`label` varchar(100) NOT NULL default '',

803

`description` text,

804

PRIMARY KEY (`id`),

805

KEY `id_object` (`id_object`),

806

KEY `id_version` (`id_version`)

807

) ENGINE=InnoDB;

808

809

INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL);

810

811

CREATE TABLE t2 (

812

`id` int NOT NULL auto_increment,

813

`id_version` int NOT NULL default '1',

814

PRIMARY KEY (`id`),

815

KEY `id_version` (`id_version`)

816

) ENGINE=InnoDB;

817

818

INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");

819

820

SELECT t2.id, t1.`label` FROM t2 INNER JOIN

821

(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl

822

ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);

823

drop table t1,t2;

824

825

create TEMPORARY table t1 (a int, b varchar(200), c text not null) engine=myisam;

826

create table t2 (a int, b varchar(200), c text not null) engine=innodb;

827

create table t3 (a int, b varchar(200), c text not null) engine=innodb;

828

insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");

829

insert t2 select * from t1;

830

insert t3 select * from t1;

831

checksum table t1, t2, t3, t4;

832

checksum table t1, t2, t3, t4;

833

checksum table t1, t2, t3, t4;

834

#show table status;

835

drop table t1,t2,t3;

836

837

838

# Test problem with refering to different fields in same table in UNION

839

# (Bug #2552)

840

841

create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb;

842

insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');

843

select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;

844

drop table t1;

845

846

847

# Bug2160

848

849

create table t1 (a int) engine=innodb;

850

create table t2 like t1;

851

drop table t1,t2;

852

853

854

# Test of automaticly created foreign keys

855

856

857

create table t1 (id int not null, id2 int not null, unique (id,id2)) engine=innodb;

858

create table t2 (id int not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;

859

show create table t1;

860

show create table t2;

861

create index id on t2 (id);

862

show create table t2;

863

create index id2 on t2 (id);

864

show create table t2;

865

drop index id2 on t2;

866

--error 1025,1025

867

drop index id on t2;

868

show create table t2;

869

drop table t2;

870

871

create table t2 (id int not null, id2 int not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = innodb;

872

show create table t2;

873

create unique index id on t2 (id,id2);

874

show create table t2;

875

drop table t2;

876

877

# Check foreign key columns created in different order than key columns

878

create table t2 (id int not null, id2 int not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;

879

show create table t2;

880

drop table t2;

881

882

create table t2 (id int not null, id2 int not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = innodb;

883

show create table t2;

884

drop table t2;

885

886

create table t2 (id int not null, id2 int not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;

887

show create table t2;

888

drop table t2;

889

890

create table t2 (id int not null auto_increment, id2 int not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = innodb;

891

show create table t2;

892

drop table t2;

893

894

create table t2 (id int not null auto_increment, id2 int not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= innodb;

895

show create table t2;

896

alter table t2 add index id_test (id), add index id_test2 (id,id2);

897

show create table t2;

898

drop table t2;

899

900

# Test error handling

901

902

# Embedded server doesn't chdir to data directory

903

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

904

--error ER_WRONG_FK_DEF

905

create table t2 (id int not null, id2 int not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb;

906

907

# bug#3749

908

909

create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;

910

show create table t2;

911

drop table t2;

912

create table t2 (a int auto_increment primary key, b int, foreign key (b) references t1(id), foreign key (b) references t1(id), unique(b)) engine=innodb;

913

show create table t2;

914

drop table t2, t1;

915

916

917

918

# Bug #6126: Duplicate columns in keys gives misleading error message

919

920

--error 1060

921

create table t1 (c char(10), index (c,c)) engine=innodb;

922

--error 1060

923

create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;

924

--error 1060

925

create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;

926

--error 1060

927

create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;

928

create table t1 (c1 char(10), c2 char(10)) engine=innodb;

929

--error 1060

930

alter table t1 add key (c1,c1);

931

--error 1060

932

alter table t1 add key (c2,c1,c1);

933

--error 1060

934

alter table t1 add key (c1,c2,c1);

935

--error 1060

936

alter table t1 add key (c1,c1,c2);

937

drop table t1;

938

939

940

# Bug #4082: integer truncation

941

942

943

create table t1(a int, b int) engine=innodb;

944

insert into t1 values ('1111', '3333');

945

select distinct concat(a, b) from t1;

946

drop table t1;

947

948

949

# check null values #1

950

951

952

--disable_warnings

953

CREATE TABLE t1 (a_id int NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB;

954

INSERT INTO t1 VALUES (1),(2),(3);

955

CREATE TABLE t2 (b_id int NOT NULL default '0',b_a int NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),

956

CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB;

957

--enable_warnings

958

INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);

959

SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz;

960

DROP TABLE t2;

961

DROP TABLE t1;

962

963

964

# Bug#11816 - Truncate table doesn't work with temporary innodb tables

965

# This is not an innodb bug, but we test it using innodb.

966

967

create temporary table t1 (a int) engine=innodb;

968

insert into t1 values (4711);

969

truncate t1;

970

insert into t1 values (42);

971

select * from t1;

972

drop table t1;

973

# Show that it works with permanent tables too.

974

create table t1 (a int) engine=innodb;

975

insert into t1 values (4711);

976

truncate t1;

977

insert into t1 values (42);

978

select * from t1;

979

drop table t1;

980

981

982

# Bug #13025 Server crash during filesort

983

984

985

create table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=innodb;

986

insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);

987

select * from t1 order by a,b,c,d;

988

explain select * from t1 order by a,b,c,d;

989

drop table t1;

990

991

992

# BUG#11039,#13218 Wrong key length in min()

993

994

995

create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;

996

insert into t1 values ('8', '6'), ('4', '7');

997

select min(a) from t1;

998

select min(b) from t1 where a='8';

999

drop table t1;

1000

1001

# End of 4.1 tests

1002

1003

1004

# range optimizer problem

1005

1006

1007

create table t1 (x bigint not null primary key) engine=innodb;

1008

insert into t1(x) values (0x0ffffffffffffff0),(0x0ffffffffffffff1);

1009

select * from t1;

1010

select count(*) from t1 where x>0;

1011

select count(*) from t1 where x=0;

1012

select count(*) from t1 where x<0;

1013

select count(*) from t1 where x < -16;

1014

select count(*) from t1 where x = -16;

1015

explain select count(*) from t1 where x > -16;

1016

select count(*) from t1 where x > -16;

1017

select * from t1 where x > -16;

1018

select count(*) from t1 where x = 1152921504606846961;

1019

drop table t1;

1020

1021

1022

## Not deterministic.

1023

# Test for testable InnoDB status variables. This test

1024

# uses previous ones(pages_created, rows_deleted, ...).

1025

#show status like "Innodb_buffer_pool_pages_total";

1026

#show status like "Innodb_page_size";

1027

#show status like "Innodb_rows_deleted";

1028

#show status like "Innodb_rows_inserted";

1029

#show status like "Innodb_rows_updated";

1030

1031

## Test for row locks InnoDB status variables.

1032

#show status like "Innodb_row_lock_waits";

1033

#show status like "Innodb_row_lock_current_waits";

1034

#show status like "Innodb_row_lock_time";

1035

#show status like "Innodb_row_lock_time_max";

1036

#show status like "Innodb_row_lock_time_avg";

1037

1038

# Test for innodb_sync_spin_loops variable

1039

show variables like "innodb_sync_spin_loops";

1040

set global innodb_sync_spin_loops=1000;

1041

show variables like "innodb_sync_spin_loops";

1042

set global innodb_sync_spin_loops=0;

1043

show variables like "innodb_sync_spin_loops";

1044

set global innodb_sync_spin_loops=20;

1045

show variables like "innodb_sync_spin_loops";

1046

1047

# Test for innodb_thread_concurrency variable

1048

show variables like "innodb_thread_concurrency";

1049

set global innodb_thread_concurrency=1001;

1050

show variables like "innodb_thread_concurrency";

1051

set global innodb_thread_concurrency=0;

1052

show variables like "innodb_thread_concurrency";

1053

set global innodb_thread_concurrency=16;

1054

show variables like "innodb_thread_concurrency";

1055

1056

# Test for innodb_concurrency_tickets variable

1057

show variables like "innodb_concurrency_tickets";

1058

set global innodb_concurrency_tickets=1000;

1059

show variables like "innodb_concurrency_tickets";

1060

set global innodb_concurrency_tickets=0;

1061

show variables like "innodb_concurrency_tickets";

1062

set global innodb_concurrency_tickets=500;

1063

show variables like "innodb_concurrency_tickets";

1064

1065

# Test for innodb_thread_sleep_delay variable

1066

show variables like "innodb_thread_sleep_delay";

1067

set global innodb_thread_sleep_delay=100000;

1068

show variables like "innodb_thread_sleep_delay";

1069

set global innodb_thread_sleep_delay=0;

1070

show variables like "innodb_thread_sleep_delay";

1071

set global innodb_thread_sleep_delay=10000;

1072

show variables like "innodb_thread_sleep_delay";

1073

1074

1075

# Test varchar

1076

1077

1078

let $default=`select @@storage_engine`;

1079

set storage_engine=INNODB;

1080

source include/varchar.inc;

1081

1082

1083

# Some errors/warnings on create

1084

1085

1086

# Embedded server doesn't chdir to data directory

1087

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1088

create table t1 (v varchar(16383), key(v));

1089

drop table t1;

1090

create table t1 (v varchar(16383));

1091

show create table t1;

1092

drop table t1;

1093

create table t1 (v varchar(16383));

1094

show create table t1;

1095

drop table t1;

1096

1097

eval set storage_engine=$default;

1098

1099

# InnoDB specific varchar tests

1100

create table t1 (v varchar(16383)) engine=innodb;

1101

drop table t1;

1102

1103

1104

# BUG#11039 Wrong key length in min()

1105

1106

1107

create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;

1108

insert into t1 values ('8', '6'), ('4', '7');

1109

select min(a) from t1;

1110

select min(b) from t1 where a='8';

1111

drop table t1;

1112

1113

1114

# Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error

1115

1116

1117

CREATE TABLE t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;

1118

insert into t1 (b) values (1);

1119

replace into t1 (b) values (2), (1), (3);

1120

select * from t1;

1121

truncate table t1;

1122

insert into t1 (b) values (1);

1123

replace into t1 (b) values (2);

1124

replace into t1 (b) values (1);

1125

replace into t1 (b) values (3);

1126

select * from t1;

1127

drop table t1;

1128

1129

create table t1 (rowid int not null auto_increment, val int not null,primary

1130

key (rowid), unique(val)) engine=innodb;

1131

replace into t1 (val) values ('1'),('2');

1132

replace into t1 (val) values ('1'),('2');

1133

--error ER_DUP_ENTRY

1134

insert into t1 (val) values ('1'),('2');

1135

select * from t1;

1136

drop table t1;

1137

1138

1139

# Test that update does not change internal auto-increment value

1140

1141

1142

create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;

1143

insert into t1 (val) values (1);

1144

update t1 set a=2 where a=1;

1145

# We should get the following error because InnoDB does not update the counter

1146

--error ER_DUP_ENTRY

1147

insert into t1 (val) values (1);

1148

select * from t1;

1149

drop table t1;

1150

1151

# Bug #10465

1152

1153

1154

--disable_warnings

1155

CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;

1156

--enable_warnings

1157

INSERT INTO t1 (GRADE) VALUES (151),(252),(343);

1158

SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;

1159

SELECT GRADE FROM t1 WHERE GRADE= 151;

1160

DROP TABLE t1;

1161

1162

1163

# Test that the slow TRUNCATE implementation resets autoincrement columns

1164

# (bug #11946)

1165

1166

1167

CREATE TABLE t1 (

1168

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

1169

) ENGINE=InnoDB;

1170

1171

CREATE TABLE t2 (

1172

id INTEGER NOT NULL,

1173

FOREIGN KEY (id) REFERENCES t1 (id)

1174

) ENGINE=InnoDB;

1175

1176

INSERT INTO t1 (id) VALUES (NULL);

1177

SELECT * FROM t1;

1178

TRUNCATE t1;

1179

INSERT INTO t1 (id) VALUES (NULL);

1180

SELECT * FROM t1;

1181

1182

# continued from above; test that doing a slow TRUNCATE on a table with 0

1183

# rows resets autoincrement columns

1184

DELETE FROM t1;

1185

TRUNCATE t1;

1186

INSERT INTO t1 (id) VALUES (NULL);

1187

SELECT * FROM t1;

1188

DROP TABLE t2, t1;

1189

1190

# Test that foreign keys in temporary tables are not accepted (bug #12084)

1191

CREATE TABLE t1

1192

(

1193

id INT PRIMARY KEY

1194

) ENGINE=InnoDB;

1195

1196

--error 1005,1005

1197

CREATE TEMPORARY TABLE t2

1198

(

1199

id INT NOT NULL PRIMARY KEY,

1200

b INT,

1201

FOREIGN KEY (b) REFERENCES test.t1(id)

1202

) ENGINE=InnoDB;

1203

DROP TABLE t1;

1204

1205

1206

# Test that index column max sizes are honored (bug #13315)

1207

1208

1209

# prefix index

1210

create table t1 (col1 varchar(2000), index (col1(767)))

1211

engine = innodb;

1212

1213

# normal indexes

1214

create table t2 (col1 char(255), index (col1))

1215

engine = innodb;

1216

create table t4 (col1 varchar(767), index (col1))

1217

engine = innodb;

1218

create table t5 (col1 varchar(190) primary key)

1219

engine = innodb;

1220

create table t6 (col1 varbinary(254) primary key)

1221

engine = innodb;

1222

create table t7 (col1 text, index(col1(767)))

1223

engine = innodb;

1224

create table t8 (col1 blob, index(col1(767)))

1225

engine = innodb;

1226

1227

# multi-column indexes are allowed to be longer

1228

create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))

1229

engine = innodb;

1230

1231

show create table t9;

1232

1233

drop table t1, t2, t4, t5, t6, t7, t8, t9;

1234

1235

# these should have their index length trimmed

1236

create table t1 (col1 varchar(768), index(col1))

1237

engine = innodb;

1238

create table t2 (col1 varbinary(768), index(col1))

1239

engine = innodb;

1240

create table t3 (col1 text, index(col1(768)))

1241

engine = innodb;

1242

create table t4 (col1 blob, index(col1(768)))

1243

engine = innodb;

1244

1245

show create table t1;

1246

1247

drop table t1, t2, t3, t4;

1248

1249

# these should be refused

1250

--error 1071

1251

create table t1 (col1 varchar(768) primary key)

1252

engine = innodb;

1253

--error 1071

1254

create table t2 (col1 varbinary(768) primary key)

1255

engine = innodb;

1256

--error 1071

1257

create table t3 (col1 text, primary key(col1(768)))

1258

engine = innodb;

1259

--error 1071

1260

create table t4 (col1 blob, primary key(col1(768)))

1261

engine = innodb;

1262

1263

1264

# Test improved foreign key error messages (bug #3443)

1265

1266

1267

CREATE TABLE t1

1268

(

1269

id INT PRIMARY KEY

1270

) ENGINE=InnoDB;

1271

1272

CREATE TABLE t2

1273

(

1274

v INT,

1275

CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)

1276

) ENGINE=InnoDB;

1277

1278

--error 1452

1279

INSERT INTO t2 VALUES(2);

1280

1281

INSERT INTO t1 VALUES(1);

1282

INSERT INTO t2 VALUES(1);

1283

1284

--error 1451

1285

DELETE FROM t1 WHERE id = 1;

1286

1287

--error 1217

1288

DROP TABLE t1;

1289

1290

SET FOREIGN_KEY_CHECKS=0;

1291

DROP TABLE t1;

1292

SET FOREIGN_KEY_CHECKS=1;

1293

1294

--error 1452

1295

INSERT INTO t2 VALUES(3);

1296

1297

DROP TABLE t2;

1298

1299

# Test that checksum table uses a consistent read Bug #12669

1300

1301

connect (a,localhost,root,,);

1302

connect (b,localhost,root,,);

1303

connection a;

1304

create table t1(a int not null) engine=innodb;

1305

insert into t1 values (1),(2);

1306

set autocommit=0;

1307

checksum table t1;

1308

connection b;

1309

insert into t1 values(3);

1310

connection a;

1311

1312

# Here checksum should not see insert

1313

1314

checksum table t1;

1315

connection a;

1316

commit;

1317

checksum table t1;

1318

commit;

1319

drop table t1;

1320

1321

# autocommit = 1

1322

1323

connection a;

1324

create table t1(a int not null) engine=innodb;

1325

insert into t1 values (1),(2);

1326

set autocommit=1;

1327

checksum table t1;

1328

connection b;

1329

set autocommit=1;

1330

insert into t1 values(3);

1331

connection a;

1332

1333

# Here checksum sees insert

1334

1335

checksum table t1;

1336

drop table t1;

1337

1338

connection default;

1339

disconnect a;

1340

disconnect b;

1341

1342

# tests for bugs #9802 and #13778

1343

1344

# test that FKs between invalid types are not accepted

1345

1346

set foreign_key_checks=0;

1347

create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;

1348

# Embedded server doesn't chdir to data directory

1349

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1350

create table t1(a char(10) primary key, b varchar(20)) engine = innodb;

1351

set foreign_key_checks=1;

1352

drop table t2;

1353

1354

# test that invalid datatype conversions with ALTER are not allowed

1355

1356

set foreign_key_checks=0;

1357

create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;

1358

create table t1(a varchar(10) primary key) engine = innodb;

1359

alter table t1 modify column a int;

1360

set foreign_key_checks=1;

1361

drop table t2,t1;

1362

1363

# test that foreign key errors are reported correctly (Bug #15550)

1364

1365

create table t1(a int primary key) row_format=redundant engine=innodb;

1366

create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;

1367

create table t3(a int primary key) row_format=compact engine=innodb;

1368

create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;

1369

1370

insert into t1 values(1);

1371

insert into t3 values(1);

1372

insert into t2 values(2);

1373

insert into t4 values(2);

1374

insert into t2 values(1);

1375

insert into t4 values(1);

1376

update t1 set a=2;

1377

update t2 set a=2;

1378

update t3 set a=2;

1379

update t4 set a=2;

1380

truncate t1;

1381

truncate t3;

1382

truncate t2;

1383

truncate t4;

1384

truncate t1;

1385

truncate t3;

1386

1387

drop table t4,t3,t2,t1;

1388

1389

1390

1391

# Test that we can create a large (>1K) key

1392

1393

create table t1 (a varchar(255),

1394

b varchar(255),

1395

c varchar(255),

1396

d varchar(255),

1397

key (a,b,c,d)) engine=innodb;

1398

drop table t1;

1399

--error ER_TOO_LONG_KEY

1400

create table t1 (a varchar(255),

1401

b varchar(255),

1402

c varchar(255),

1403

d varchar(255),

1404

e varchar(255),

1405

key (a,b,c,d,e)) engine=innodb;

1406

1407

1408

# test the padding of BINARY types and collations (Bug #14189)

1409

1410

create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;

1411

create table t3 (s1 varchar(2) ,primary key (s1)) engine=innodb;

1412

create table t4 (s1 char(2) ,primary key (s1)) engine=innodb;

1413

1414

insert into t1 values (0x41),(0x4120),(0x4100);

1415

insert into t3 values (0x41),(0x4120),(0x4100);

1416

insert into t3 values (0x41),(0x4100);

1417

insert into t4 values (0x41),(0x4120),(0x4100);

1418

insert into t4 values (0x41),(0x4100);

1419

select hex(s1) from t1;

1420

select hex(s1) from t3;

1421

select hex(s1) from t4;

1422

drop table t1,t3,t4;

1423

1424

create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;

1425

create table t2 (s1 varbinary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;

1426

1427

insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);

1428

insert into t2 values(0x42);

1429

insert into t2 values(0x41);

1430

select hex(s1) from t2;

1431

update t1 set s1=0x123456 where a=2;

1432

select hex(s1) from t2;

1433

update t1 set s1=0x12 where a=1;

1434

update t1 set s1=0x12345678 where a=1;

1435

update t1 set s1=0x123457 where a=1;

1436

update t1 set s1=0x1220 where a=1;

1437

select hex(s1) from t2;

1438

update t1 set s1=0x1200 where a=1;

1439

select hex(s1) from t2;

1440

update t1 set s1=0x4200 where a=1;

1441

select hex(s1) from t2;

1442

delete from t1 where a=1;

1443

update t2 set s1=0x4120;

1444

delete from t1;

1445

delete from t1 where a!=3;

1446

select a,hex(s1) from t1;

1447

select hex(s1) from t2;

1448

1449

drop table t2,t1;

1450

1451

create table t1 (a int primary key,s1 varchar(2) not null unique) engine=innodb;

1452

create table t2 (s1 char(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;

1453

1454

insert into t1 values(1,0x4100),(2,0x41);

1455

insert into t2 values(0x41);

1456

select hex(s1) from t2;

1457

update t1 set s1=0x1234 where a=1;

1458

select hex(s1) from t2;

1459

update t1 set s1=0x12 where a=2;

1460

select hex(s1) from t2;

1461

delete from t1 where a=1;

1462

delete from t1 where a=2;

1463

select a,hex(s1) from t1;

1464

select hex(s1) from t2;

1465

1466

drop table t2,t1;

1467

# Ensure that <tablename>_ibfk_0 is not mistreated as a

1468

# generated foreign key identifier. (Bug #16387)

1469

1470

CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;

1471

CREATE TABLE t2(a INT) ENGINE=InnoDB;

1472

ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);

1473

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;

1474

ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);

1475

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;

1476

SHOW CREATE TABLE t2;

1477

DROP TABLE t2,t1;

1478

1479

1480

# Test that cascading updates leading to duplicate keys give the correct

1481

# error message (bug #9680)

1482

1483

1484

CREATE TABLE t1 (

1485

field1 varchar(8) NOT NULL DEFAULT '',

1486

field2 varchar(8) NOT NULL DEFAULT '',

1487

PRIMARY KEY (field1, field2)

1488

) ENGINE=InnoDB;

1489

1490

CREATE TABLE t2 (

1491

field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,

1492

FOREIGN KEY (field1) REFERENCES t1 (field1)

1493

ON DELETE CASCADE ON UPDATE CASCADE

1494

) ENGINE=InnoDB;

1495

1496

INSERT INTO t1 VALUES ('old', 'somevalu');

1497

INSERT INTO t1 VALUES ('other', 'anyvalue');

1498

1499

INSERT INTO t2 VALUES ('old');

1500

INSERT INTO t2 VALUES ('other');

1501

1502

--error ER_FOREIGN_DUPLICATE_KEY

1503

UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';

1504

1505

DROP TABLE t2;

1506

DROP TABLE t1;

1507

1508

1509

# Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE

1510

1511

create table t1 (

1512

c1 bigint not null,

1513

c2 bigint not null,

1514

primary key (c1),

1515

unique key (c2)

1516

) engine=innodb;

1517

1518

create table t2 (

1519

c1 bigint not null,

1520

primary key (c1)

1521

) engine=innodb;

1522

1523

alter table t1 add constraint c2_fk foreign key (c2)

1524

references t2(c1) on delete cascade;

1525

show create table t1;

1526

1527

alter table t1 drop foreign key c2_fk;

1528

show create table t1;

1529

1530

drop table t1, t2;

1531

1532

1533

# Bug #14360: problem with intervals

1534

1535

1536

create table t1(a date) engine=innodb;

1537

create table t2(a date, key(a)) engine=innodb;

1538

insert into t1 values('2005-10-01');

1539

insert into t2 values('2005-10-01');

1540

select * from t1, t2

1541

where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;

1542

drop table t1, t2;

1543

1544

create table t1 (id int not null, f_id int not null, f int not null,

1545

primary key(f_id, id)) engine=innodb;

1546

create table t2 (id int not null,s_id int not null,s varchar(200),

1547

primary key(id)) engine=innodb;

1548

INSERT INTO t1 VALUES (8, 1, 3);

1549

INSERT INTO t1 VALUES (1, 2, 1);

1550

INSERT INTO t2 VALUES (1, 0, '');

1551

INSERT INTO t2 VALUES (8, 1, '');

1552

commit;

1553

select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)

1554

where mm.id is null lock in share mode;

1555

drop table t1,t2;

1556

1557

1558

# Test case where X-locks on unused rows should be released in a

1559

# update (because READ COMMITTED isolation level)

1560

1561

1562

connect (a,localhost,root,,);

1563

connect (b,localhost,root,,);

1564

connection a;

1565

create table t1(a int not null, b int, primary key(a)) engine=innodb;

1566

insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);

1567

commit;

1568

set autocommit = 0;

1569

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1570

update t1 set b = 5 where b = 1;

1571

connection b;

1572

set autocommit = 0;

1573

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1574

1575

# X-lock to record (7,3) should be released in a update

1576

1577

select * from t1 where a = 7 and b = 3 for update;

1578

connection a;

1579

commit;

1580

connection b;

1581

commit;

1582

drop table t1;

1583

connection default;

1584

disconnect a;

1585

disconnect b;

1586

1587

1588

# Test case where no locks should be released (because we are not

1589

# using READ COMMITTED isolation level)

1590

1591

1592

connect (a,localhost,root,,);

1593

connect (b,localhost,root,,);

1594

connection a;

1595

create table t1(a int not null, b int, primary key(a)) engine=innodb;

1596

insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);

1597

commit;

1598

set autocommit = 0;

1599

select * from t1 lock in share mode;

1600

update t1 set b = 5 where b = 1;

1601

connection b;

1602

set autocommit = 0;

1603

1604

# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update

1605

1606

--error 1205

1607

select * from t1 where a = 2 and b = 2 for update;

1608

1609

# X-lock to record (1,1),(3,1),(5,1) should not be released in a update

1610

1611

--error 1205

1612

connection a;

1613

commit;

1614

connection b;

1615

commit;

1616

connection default;

1617

disconnect a;

1618

disconnect b;

1619

drop table t1;

1620

1621

1622

# Consistent read should be used in following selects

1623

1624

# 1) INSERT INTO ... SELECT

1625

# 2) UPDATE ... = ( SELECT ...)

1626

# 3) CREATE ... SELECT

1627

1628

connect (a,localhost,root,,);

1629

connect (b,localhost,root,,);

1630

connection a;

1631

create table t1(a int not null, b int, primary key(a)) engine=innodb;

1632

insert into t1 values (1,2),(5,3),(4,2);

1633

create table t2(d int not null, e int, primary key(d)) engine=innodb;

1634

insert into t2 values (8,6),(12,1),(3,1);

1635

commit;

1636

set autocommit = 0;

1637

select * from t2 for update;

1638

connection b;

1639

set autocommit = 0;

1640

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1641

insert into t1 select * from t2;

1642

update t1 set b = (select e from t2 where a = d);

1643

create table t3(d int not null, e int, primary key(d)) engine=innodb

1644

select * from t2;

1645

commit;

1646

connection a;

1647

commit;

1648

connection default;

1649

disconnect a;

1650

disconnect b;

1651

drop table t1, t2, t3;

1652

1653

1654

# Consistent read should not be used if

1655

1656

# (a) isolation level is serializable OR

1657

# (b) select ... lock in share mode OR

1658

# (c) select ... for update

1659

1660

# in following queries:

1661

1662

# 1) INSERT INTO ... SELECT

1663

# 2) UPDATE ... = ( SELECT ...)

1664

# 3) CREATE ... SELECT

1665

1666

connect (a,localhost,root,,);

1667

connect (b,localhost,root,,);

1668

connect (c,localhost,root,,);

1669

connect (d,localhost,root,,);

1670

connect (e,localhost,root,,);

1671

connect (f,localhost,root,,);

1672

connect (g,localhost,root,,);

1673

connect (h,localhost,root,,);

1674

connect (i,localhost,root,,);

1675

connect (j,localhost,root,,);

1676

connection a;

1677

create table t1(a int not null, b int, primary key(a)) engine=innodb;

1678

insert into t1 values (1,2),(5,3),(4,2);

1679

create table t2(a int not null, b int, primary key(a)) engine=innodb;

1680

insert into t2 values (8,6),(12,1),(3,1);

1681

create table t3(d int not null, b int, primary key(d)) engine=innodb;

1682

insert into t3 values (8,6),(12,1),(3,1);

1683

create table t5(a int not null, b int, primary key(a)) engine=innodb;

1684

insert into t5 values (1,2),(5,3),(4,2);

1685

create table t6(d int not null, e int, primary key(d)) engine=innodb;

1686

insert into t6 values (8,6),(12,1),(3,1);

1687

create table t8(a int not null, b int, primary key(a)) engine=innodb;

1688

insert into t8 values (1,2),(5,3),(4,2);

1689

create table t9(d int not null, e int, primary key(d)) engine=innodb;

1690

insert into t9 values (8,6),(12,1),(3,1);

1691

commit;

1692

set autocommit = 0;

1693

select * from t2 for update;

1694

connection b;

1695

set autocommit = 0;

1696

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

1697

--send

1698

insert into t1 select * from t2;

1699

connection c;

1700

set autocommit = 0;

1701

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

1702

--send

1703

update t3 set b = (select b from t2 where a = d);

1704

connection d;

1705

set autocommit = 0;

1706

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

1707

--send

1708

create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;

1709

connection e;

1710

set autocommit = 0;

1711

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1712

--send

1713

insert into t5 (select * from t2 lock in share mode);

1714

connection f;

1715

set autocommit = 0;

1716

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1717

--send

1718

update t6 set e = (select b from t2 where a = d lock in share mode);

1719

connection g;

1720

set autocommit = 0;

1721

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1722

--send

1723

create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;

1724

connection h;

1725

set autocommit = 0;

1726

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1727

--send

1728

insert into t8 (select * from t2 for update);

1729

connection i;

1730

set autocommit = 0;

1731

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1732

--send

1733

update t9 set e = (select b from t2 where a = d for update);

1734

connection j;

1735

set autocommit = 0;

1736

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1737

--send

1738

create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;

1739

1740

connection b;

1741

--error 1205

1742

reap;

1743

1744

connection c;

1745

--error 1205

1746

reap;

1747

1748

connection d;

1749

--error 1205

1750

reap;

1751

1752

connection e;

1753

--error 1205

1754

reap;

1755

1756

connection f;

1757

--error 1205

1758

reap;

1759

1760

connection g;

1761

--error 1205

1762

reap;

1763

1764

connection h;

1765

--error 1205

1766

reap;

1767

1768

connection i;

1769

--error 1205

1770

reap;

1771

1772

connection j;

1773

--error 1205

1774

reap;

1775

1776

connection a;

1777

commit;

1778

1779

connection default;

1780

disconnect a;

1781

disconnect b;

1782

disconnect c;

1783

disconnect d;

1784

disconnect e;

1785

disconnect f;

1786

disconnect g;

1787

disconnect h;

1788

disconnect i;

1789

disconnect j;

1790

drop table t1, t2, t3, t5, t6, t8, t9;

1791

1792

# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"

1793

--error 1005

1794

CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;

1795

1796

1797

# Bug #17152: Wrong result with BINARY comparison on aliased column

1798

1799

1800

CREATE TABLE t1 (

1801

a BIGINT NOT NULL,

1802

PRIMARY KEY (a)

1803

) ENGINE=INNODB;

1804

1805

CREATE TABLE t2 (

1806

a BIGINT NOT NULL,

1807

b VARCHAR(128) NOT NULL,

1808

c TEXT NOT NULL,

1809

PRIMARY KEY (a,b),

1810

KEY idx_t2_b_c (b,c(200)),

1811

CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)

1812

ON DELETE CASCADE

1813

) ENGINE=INNODB;

1814

1815

INSERT INTO t1 VALUES (1);

1816

INSERT INTO t2 VALUES (1, 'bar', 'vbar');

1817

INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');

1818

INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');

1819

INSERT INTO t2 VALUES (1, 'customer_over', '1');

1820

1821

SELECT * FROM t2 WHERE b = 'customer_over';

1822

SELECT * FROM t2 WHERE BINARY b = 'customer_over';

1823

SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';

1824

/* Bang: Empty result set, above was expected: */

1825

SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';

1826

SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';

1827

1828

drop table t2, t1;

1829

1830

1831

# Test optimize on table with open transaction

1832

1833

1834

CREATE TABLE t1 ( a int ) ENGINE=innodb;

1835

BEGIN;

1836

INSERT INTO t1 VALUES (1);

1837

ALTER TABLE t1 ENGINE=innodb;

1838

DROP TABLE t1;

1839

1840

1841

# Bug #24741 (existing cascade clauses disappear when adding foreign keys)

1842

1843

1844

CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;

1845

1846

CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,

1847

CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)

1848

ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

1849

1850

ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON

1851

DELETE CASCADE ON UPDATE CASCADE;

1852

1853

SHOW CREATE TABLE t2;

1854

DROP TABLE t2, t1;

1855

1856

1857

# Bug #25927: Prevent ALTER TABLE ... MODIFY ... NOT NULL on columns

1858

# for which there is a foreign key constraint ON ... SET NULL.

1859

1860

1861

CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;

1862

CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;

1863

INSERT INTO t1 VALUES (1);

1864

INSERT INTO t2 VALUES (1);

1865

ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;

1866

# mysqltest first does replace_regex, then replace_result

1867

--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/

1868

# Embedded server doesn't chdir to data directory

1869

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1870

--error 1025

1871

ALTER TABLE t2 MODIFY a INT NOT NULL;

1872

DELETE FROM t1;

1873

DROP TABLE t2,t1;

1874

1875

1876

# Bug #26835: table corruption after delete+insert

1877

1878

1879

CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)

1880

ENGINE=InnoDB;

1881

INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);

1882

DELETE FROM t1;

1883

INSERT INTO t1 VALUES ('DDD');

1884

SELECT * FROM t1;

1885

DROP TABLE t1;

1886

1887

1888

# Bug #23313 (AUTO_INCREMENT=# not reported back for InnoDB tables)

1889

# Bug #21404 (AUTO_INCREMENT value reset when Adding FKEY (or ALTER?))

1890

1891

1892

CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB

1893

AUTO_INCREMENT=42;

1894

1895

INSERT INTO t1 VALUES (NULL),(347),(NULL);

1896

SELECT * FROM t1;

1897

1898

SHOW CREATE TABLE t1;

1899

1900

CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;

1901

INSERT INTO t2 VALUES(42),(347),(348);

1902

ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);

1903

SHOW CREATE TABLE t1;

1904

1905

DROP TABLE t1,t2;

1906

1907

1908

# Bug #31860 InnoDB assumes AUTOINC values can only be positive.

1909

1910

DROP TABLE IF EXISTS t1;

1911

CREATE TABLE t1(

1912

id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY

1913

) ENGINE=InnoDB;

1914

INSERT INTO t1 VALUES(-10);

1915

SELECT * FROM t1;

1916

1917

# NOTE: The server really needs to be restarted at this point

1918

# for the test to be useful.

1919

1920

# Without the fix InnoDB would trip over an assertion here.

1921

INSERT INTO t1 VALUES(NULL);

1922

# The next value should be 1 and not -9 or a -ve number

1923

SELECT * FROM t1;

1924

DROP TABLE t1;

1925

1926

#######################################################################

1927

# #

1928

# Please, DO NOT TOUCH this file as well as the innodb.result file. #

1929

# These files are to be modified ONLY BY INNOBASE guys. #

1930

# #

1931

# Use innodb_mysql.[test|result] files instead. #

1932

# #

1933

# If nevertheless you need to make some changes here, please, forward #

1934

# your commit message To: dev@innodb.com Cc: dev-innodb@mysql.com #

1935

# (otherwise your changes may be erased). #

1936

# #

1937

#######################################################################

Older »