~drizzle-trunk/drizzle/development

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

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

eval CREATE TABLE t1 (

id int(11) NOT NULL auto_increment,

parent_id int(11) DEFAULT '0' NOT NULL,

level tinyint(4) DEFAULT '0' NOT NULL,

PRIMARY KEY (id),

KEY parent_id (parent_id),

KEY level (level)

) engine=$engine_type;

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;

100

select * from t1;

101

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

102

select * from t1;

103

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

104

select * from t1 where parent_id=102;

105

--replace_column 9 #

106

explain select level from t1 where level=1;

107

--replace_column 9 #

108

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

109

--replace_column 9 #

110

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

111

select level,id from t1 where level=1;

112

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

113

optimize table t1;

114

--replace_column 7 #

115

show keys from t1;

116

drop table t1;

117

118

119

# Test replace

120

121

122

eval CREATE TABLE t1 (

123

gesuchnr int(11) DEFAULT '0' NOT NULL,

124

benutzer_id int(11) DEFAULT '0' NOT NULL,

125

PRIMARY KEY (gesuchnr,benutzer_id)

126

) engine=$engine_type;

127

128

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

129

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

130

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

131

select * from t1;

132

drop table t1;

133

134

135

# test delete using hidden_primary_key

136

137

138

eval create table t1 (a int) engine=$engine_type;

139

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

140

optimize table t1;

141

delete from t1 where a = 1;

142

select * from t1;

143

check table t1;

144

drop table t1;

145

146

eval create table t1 (a int,b varchar(20)) engine=$engine_type;

147

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

148

delete from t1 where a = 1;

149

select * from t1;

150

create index skr on t1 (a);

151

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

152

analyze table t1;

153

--replace_column 7 #

154

show keys from t1;

155

drop table t1;

156

157

158

# Test of reading on secondary key with may be null

159

160

eval create table t1 (a int,b varchar(20),key(a)) engine=$engine_type;

161

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

162

select * from t1 where a = 1;

163

drop table t1;

164

165

if ($test_transactions)

166

{

167

168

# Test rollback

169

170

171

eval create table t1 (n int not null primary key) engine=$engine_type;

172

set autocommit=0;

173

insert into t1 values (4);

174

rollback;

175

select n, "after rollback" from t1;

176

insert into t1 values (4);

177

commit;

178

select n, "after commit" from t1;

179

commit;

180

insert into t1 values (5);

181

insert into t1 values (4);

182

commit;

183

select n, "after commit" from t1;

184

set autocommit=1;

185

insert into t1 values (6);

186

insert into t1 values (4);

187

select n from t1;

188

set autocommit=0;

189

190

# savepoints

191

192

begin;

193

savepoint `my_savepoint`;

194

insert into t1 values (7);

195

savepoint `savept2`;

196

insert into t1 values (3);

197

select n from t1;

198

savepoint savept3;

199

rollback to savepoint savept2;

200

--error 1305

201

rollback to savepoint savept3;

202

rollback to savepoint savept2;

203

release savepoint `my_savepoint`;

204

select n from t1;

205

rollback to savepoint `my_savepoint`;

206

--error 1305

207

rollback to savepoint savept2;

208

insert into t1 values (8);

209

savepoint sv;

210

commit;

211

savepoint sv;

212

set autocommit=1;

213

# nop

214

rollback;

215

drop table t1;

216

217

218

# Test for commit and FLUSH TABLES WITH READ LOCK

219

220

221

eval create table t1 (n int not null primary key) engine=$engine_type;

222

start transaction;

223

insert into t1 values (4);

224

flush tables with read lock;

225

226

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

227

#--error 1223;

228

commit;

229

unlock tables;

230

commit;

231

select * from t1;

232

drop table t1;

233

234

235

# Testing transactions

236

237

238

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

239

begin;

240

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

241

select id as afterbegin_id,nom as afterbegin_nom from t1;

242

rollback;

243

select id as afterrollback_id,nom as afterrollback_nom from t1;

244

set autocommit=0;

245

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

246

select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;

247

rollback;

248

select id as afterrollback_id,nom as afterrollback_nom from t1;

249

set autocommit=1;

250

drop table t1;

251

252

253

# Simple not autocommit test

254

255

256

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

257

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

258

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

259

delete from t1;

260

delete from t1 where id = 'pippo';

261

select * from t1;

262

263

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

264

set autocommit=0;

265

delete from t1;

266

rollback;

267

select * from t1;

268

delete from t1;

269

commit;

270

select * from t1;

271

drop table t1;

272

273

274

# Test of active transactions

275

276

277

eval create table t1 (a integer) engine=$engine_type;

278

start transaction;

279

rename table t1 to t2;

280

eval create table t1 (b integer) engine=$engine_type;

281

insert into t1 values (1);

282

rollback;

283

drop table t1;

284

rename table t2 to t1;

285

drop table t1;

286

set autocommit=1;

287

288

289

# The following simple tests failed at some point

290

291

292

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

293

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

294

select * from t1;

295

drop table t1;

296

297

eval CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=$engine_type;

298

set autocommit=0;

299

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

300

COMMIT;

301

SELECT * FROM t1;

302

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

303

drop table t1;

304

set autocommit=1;

305

306

}

307

# End of transactional tests

308

309

310

# Test when reading on part of unique key

311

312

eval CREATE TABLE t1 (

313

user_id int(10) DEFAULT '0' NOT NULL,

314

name varchar(100),

315

phone varchar(100),

316

ref_email varchar(100) DEFAULT '' NOT NULL,

317

detail varchar(200),

318

PRIMARY KEY (user_id,ref_email)

319

)engine=$engine_type;

320

321

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

322

select * from t1 where user_id=10292;

323

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

324

select * from t1 where user_id=10292;

325

select * from t1 where user_id>=10292;

326

select * from t1 where user_id>10292;

327

select * from t1 where user_id<10292;

328

drop table t1;

329

330

331

# Test that keys are created in right order

332

333

334

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

335

key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = $engine_type;

336

--replace_column 7 #

337

show index from t1;

338

drop table t1;

339

340

341

# Test of ALTER TABLE and innodb tables

342

343

344

eval create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = $other_engine_type;

345

eval alter table t1 engine=$engine_type;

346

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

347

select * from t1;

348

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

349

select * from t1;

350

alter table t1 add co3 int not null;

351

select * from t1;

352

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

353

select * from t1;

354

drop table t1;

355

356

357

# INSERT INTO innodb tables

358

359

360

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

361

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

362

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

363

select * from t1;

364

insert into t2 select * from t1;

365

select * from t2;

366

delete from t1 where b = 3;

367

select * from t1;

368

insert into t1 select * from t2;

369

select * from t1;

370

select * from t2;

371

drop table t1,t2;

372

373

374

# Search on unique key

375

376

377

eval CREATE TABLE t1 (

378

id int(11) NOT NULL auto_increment,

379

ggid varchar(32) binary DEFAULT '' NOT NULL,

380

email varchar(64) DEFAULT '' NOT NULL,

381

passwd varchar(32) binary DEFAULT '' NOT NULL,

382

PRIMARY KEY (id),

383

UNIQUE ggid (ggid)

384

) ENGINE=$engine_type;

385

386

insert into t1 (ggid,passwd) values ('test1','xxx');

387

insert into t1 (ggid,passwd) values ('test2','yyy');

388

insert into t1 (ggid,passwd) values ('test2','this will fail');

389

insert into t1 (ggid,id) values ('this will fail',1);

390

391

select * from t1 where ggid='test1';

392

select * from t1 where passwd='xxx';

393

select * from t1 where id=2;

394

395

replace into t1 (ggid,id) values ('this will work',1);

396

replace into t1 (ggid,passwd) values ('test2','this will work');

397

update t1 set id=100,ggid='test2' where id=1;

398

select * from t1;

399

select * from t1 where id=1;

400

select * from t1 where id=999;

401

drop table t1;

402

403

404

# ORDER BY on not primary key

405

406

407

eval CREATE TABLE t1 (

408

user_name varchar(12),

409

password text,

410

subscribed char(1),

411

user_id int(11) DEFAULT '0' NOT NULL,

412

quota bigint(20),

413

weight double,

414

access_date date,

415

access_time time,

416

approved datetime,

417

dummy_primary_key int(11) NOT NULL auto_increment,

418

PRIMARY KEY (dummy_primary_key)

419

) ENGINE=$engine_type;

420

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

421

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

422

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

423

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

424

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

425

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

426

drop table t1;

427

428

429

# Testing of tables without primary keys

430

431

432

eval CREATE TABLE t1 (

433

id int(11) NOT NULL auto_increment,

434

parent_id int(11) DEFAULT '0' NOT NULL,

435

level tinyint(4) DEFAULT '0' NOT NULL,

436

KEY (id),

437

KEY parent_id (parent_id),

438

KEY level (level)

439

) engine=$engine_type;

440

441

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

442

update t1 set parent_id=parent_id+100;

443

select * from t1 where parent_id=102;

444

update t1 set id=id+1000;

445

update t1 set id=1024 where id=1009;

446

select * from t1;

447

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

448

select * from t1;

449

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

450

select * from t1 where parent_id=102;

451

--replace_column 9 #

452

explain select level from t1 where level=1;

453

select level,id from t1 where level=1;

454

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

455

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

456

delete from t1 where level=1;

457

select * from t1;

458

drop table t1;

459

460

461

# Test of index only reads

462

463

eval CREATE TABLE t1 (

464

sca_code char(6) NOT NULL,

465

cat_code char(6) NOT NULL,

466

sca_desc varchar(50),

467

lan_code char(2) NOT NULL,

468

sca_pic varchar(100),

469

sca_sdesc varchar(50),

470

sca_sch_desc varchar(16),

471

PRIMARY KEY (sca_code, cat_code, lan_code),

472

INDEX sca_pic (sca_pic)

473

) engine = $engine_type ;

474

475

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

476

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

477

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

478

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

479

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

480

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

481

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

482

483

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

484

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

485

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

486

select sca_pic from t1 where sca_pic is null;

487

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

488

delete from t1 where sca_code='pd';

489

drop table t1;

490

491

492

# Test of opening table twice and timestamps

493

494

set @a:=now();

495

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

496

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

497

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

498

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

499

update t1 set a=5 where a=1;

500

select a from t1;

501

drop table t1;

502

503

504

# Test with variable length primary key

505

506

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

507

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

508

select * from t1 order by b desc;

509

optimize table t1;

510

--replace_column 7 #

511

show keys from t1;

512

drop table t1;

513

514

515

# Test of create index with NULL columns

516

517

eval create table t1 (i int, j int ) ENGINE=$engine_type;

518

insert into t1 values (1,2);

519

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

520

create index ax1 on t1 (i,j);

521

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

522

drop table t1;

523

524

525

# Test min-max optimization

526

527

528

eval CREATE TABLE t1 (

529

a int3 NOT NULL,

530

b int1 NOT NULL,

531

UNIQUE (a, b)

532

) ENGINE = $engine_type;

533

534

INSERT INTO t1 VALUES (1, 1);

535

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

536

drop table t1;

537

538

539

# Test INSERT DELAYED

540

541

542

eval CREATE TABLE t1 (a int NOT NULL) engine=$engine_type;

543

# Can't test this in 3.23

544

# INSERT DELAYED INTO t1 VALUES (1);

545

INSERT INTO t1 VALUES (1);

546

SELECT * FROM t1;

547

DROP TABLE t1;

548

549

550

551

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

552

553

554

eval 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 = $engine_type;

555

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

556

--replace_column 9 #

557

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

558

drop table t1;

559

560

561

# Test lock tables

562

563

564

eval create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=$engine_type;

565

insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');

566

LOCK TABLES t1 WRITE;

567

--error ER_DUP_ENTRY

568

insert into t1 values (99,1,2,'D'),(1,1,2,'D');

569

select id from t1;

570

select id from t1;

571

UNLOCK TABLES;

572

DROP TABLE t1;

573

574

eval create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=$engine_type;

575

insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');

576

LOCK TABLES t1 WRITE;

577

begin;

578

--error ER_DUP_ENTRY

579

insert into t1 values (99,1,2,'D'),(1,1,2,'D');

580

select id from t1;

581

insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');

582

commit;

583

select id,id3 from t1;

584

UNLOCK TABLES;

585

DROP TABLE t1;

586

587

588

# Test prefix key

589

590

eval create table t1 (a char(20), unique (a(5))) engine=$engine_type;

591

drop table t1;

592

eval create table t1 (a char(20), index (a(5))) engine=$engine_type;

593

show create table t1;

594

drop table t1;

595

596

597

# Test using temporary table and auto_increment

598

599

600

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

601

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

602

delete from t1 where a=3;

603

insert into t1 values (NULL);

604

select * from t1;

605

alter table t1 add b int;

606

select * from t1;

607

drop table t1;

608

609

#Slashdot bug

610

eval create table t1

611

(

612

id int auto_increment primary key,

613

name varchar(32) not null,

614

value text not null,

615

uid int not null,

616

unique key(name,uid)

617

) engine=$engine_type;

618

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

619

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

620

set insert_id=5;

621

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

622

delete from t1 where uid=102;

623

set insert_id=5;

624

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

625

set insert_id=6;

626

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

627

select * from t1;

628

drop table t1;

629

630

631

# Test DROP DATABASE

632

633

# ML: Test logics

634

# Check that the creation of a table with engine = $engine_type does

635

# in a certain database (already containing some tables using other

636

# storage engines) not prevent the dropping of this database.

637

638

create database mysqltest;

639

eval create table mysqltest.t1 (a int not null) engine= $engine_type;

640

insert into mysqltest.t1 values(1);

641

eval create table mysqltest.t2 (a int not null) engine= $other_engine_type;

642

insert into mysqltest.t2 values(1);

643

eval create table mysqltest.t3 (a int not null) engine= $other_engine_type1;

644

insert into mysqltest.t3 values(1);

645

commit;

646

drop database mysqltest;

647

# Don't check error message

648

--error 1049

649

show tables from mysqltest;

650

651

652

# Test truncate table with and without auto_commit

653

654

655

set autocommit=0;

656

eval create table t1 (a int not null) engine= $engine_type;

657

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

658

truncate table t1;

659

commit;

660

truncate table t1;

661

truncate table t1;

662

select * from t1;

663

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

664

delete from t1;

665

select * from t1;

666

commit;

667

drop table t1;

668

set autocommit=1;

669

670

eval create table t1 (a int not null) engine= $engine_type;

671

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

672

truncate table t1;

673

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

674

select * from t1;

675

truncate table t1;

676

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

677

delete from t1;

678

select * from t1;

679

drop table t1;

680

681

682

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

683

684

685

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

686

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

687

--replace_column 9 #

688

explain select * from t1 order by a;

689

--replace_column 9 #

690

explain select * from t1 order by b;

691

--replace_column 9 #

692

explain select * from t1 order by c;

693

--replace_column 9 #

694

explain select a from t1 order by a;

695

--replace_column 9 #

696

explain select b from t1 order by b;

697

--replace_column 9 #

698

explain select a,b from t1 order by b;

699

--replace_column 9 #

700

explain select a,b from t1;

701

--replace_column 9 #

702

explain select a,b,c from t1;

703

drop table t1;

704

705

706

# Check describe

707

708

709

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

710

desc t1;

711

drop table t1;

712

713

714

# Test of multi-table-delete

715

716

717

eval CREATE TABLE t1 (

718

number bigint(20) NOT NULL default '0',

719

cname char(15) NOT NULL default '',

720

carrier_id smallint(6) NOT NULL default '0',

721

privacy tinyint(4) NOT NULL default '0',

722

last_mod_date timestamp NOT NULL,

723

last_mod_id smallint(6) NOT NULL default '0',

724

last_app_date timestamp NOT NULL,

725

last_app_id smallint(6) default '-1',

726

version smallint(6) NOT NULL default '0',

727

assigned_scps int(11) default '0',

728

status tinyint(4) default '0'

729

) ENGINE=$engine_type;

730

INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);

731

INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);

732

INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);

733

INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);

734

INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);

735

INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);

736

eval CREATE TABLE t2 (

737

number bigint(20) NOT NULL default '0',

738

cname char(15) NOT NULL default '',

739

carrier_id smallint(6) NOT NULL default '0',

740

privacy tinyint(4) NOT NULL default '0',

741

last_mod_date timestamp NOT NULL,

742

last_mod_id smallint(6) NOT NULL default '0',

743

last_app_date timestamp NOT NULL,

744

last_app_id smallint(6) default '-1',

745

version smallint(6) NOT NULL default '0',

746

assigned_scps int(11) default '0',

747

status tinyint(4) default '0'

748

) ENGINE=$engine_type;

749

INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);

750

INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);

751

INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);

752

INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);

753

select * from t1;

754

select * from t2;

755

delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null);

756

select * from t1;

757

select * from t2;

758

select * from t2;

759

drop table t1,t2;

760

761

762

# A simple test with some isolation levels

763

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

764

# this works.

765

766

767

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

768

769

BEGIN;

770

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

771

SELECT @@tx_isolation,@@global.tx_isolation;

772

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

773

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

774

COMMIT;

775

776

BEGIN;

777

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

778

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

779

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

780

COMMIT;

781

782

BEGIN;

783

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

784

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

785

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

786

COMMIT;

787

DROP TABLE t1;

788

789

790

# Test of multi-table-update

791

792

eval create table t1 (n int(10), d int(10)) engine=$engine_type;

793

eval create table t2 (n int(10), d int(10)) engine=$engine_type;

794

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

795

insert into t2 values(1,10),(2,20);

796

UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;

797

select * from t1;

798

select * from t2;

799

drop table t1,t2;

800

801

802

# Testing of IFNULL

803

804

eval create table t1 (a int, b int) engine=$engine_type;

805

insert into t1 values(20,null);

806

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

807

t2.b=t3.a;

808

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

809

t2.b=t3.a order by 1;

810

insert into t1 values(10,null);

811

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

812

t2.b=t3.a order by 1;

813

drop table t1;

814

815

816

# Test of read_through not existing const_table

817

818

819

eval create table t1 (a varchar(10) not null) engine = $other_engine_type;

820

eval create table t2 (b varchar(10) not null unique) engine=$engine_type;

821

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

822

drop table t1,t2;

823

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

824

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

825

insert into t1 values (10, 20);

826

insert into t2 values (10, 20);

827

update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;

828

drop table t1,t2;

829

830

if ($test_foreign_keys)

831

{

832

833

# Test of multi-table-delete with foreign key constraints

834

835

836

eval CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=$engine_type;

837

eval CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE ) ENGINE=$engine_type;

838

insert into t1 set id=1;

839

insert into t2 set id=1, t1_id=1;

840

delete t1,t2 from t1,t2 where t1.id=t2.t1_id;

841

select * from t1;

842

select * from t2;

843

drop table t2,t1;

844

eval CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=$engine_type;

845

eval CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=$engine_type;

846

INSERT INTO t1 VALUES(1);

847

INSERT INTO t2 VALUES(1, 1);

848

SELECT * from t1;

849

UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;

850

SELECT * from t1;

851

UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;

852

SELECT * from t1;

853

DROP TABLE t1,t2;

854

}

855

856

if ($test_transactions)

857

{

858

859

# Test of range_optimizer

860

861

862

set autocommit=0;

863

864

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

865

866

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

867

868

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

869

870

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

871

COMMIT;

872

873

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

874

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

875

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

876

877

SELECT * FROM t1;

878

SELECT * FROM t2;

879

SELECT * FROM t3;

880

ROLLBACK;

881

882

SELECT * FROM t1;

883

SELECT * FROM t2;

884

SELECT * FROM t3;

885

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

886

COMMIT;

887

set autocommit=1;

888

DROP TABLE t1,t2,t3;

889

}

890

891

892

# Check update with conflicting key

893

894

895

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

896

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

897

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

898

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

899

SELECT * from t1;

900

drop table t1;

901

902

903

# Test multi update with different join methods

904

905

906

eval CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=$engine_type;

907

eval CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=$engine_type;

908

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

909

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

910

911

# Full join, without key

912

update t1,t2 set t1.a=t1.a+100;

913

select * from t1;

914

915

# unique key

916

update t1,t2 set t1.a=t1.a+100 where t1.a=101;

917

select * from t1;

918

919

# ref key

920

update t1,t2 set t1.b=t1.b+10 where t1.b=2;

921

select * from t1;

922

923

# Range key (in t1)

924

update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;

925

select * from t1;

926

select * from t2;

927

928

drop table t1,t2;

929

eval CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=$other_non_trans_engine_type;

930

eval CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=$engine_type;

931

SET AUTOCOMMIT=0;

932

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

933

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

934

ROLLBACK;

935

SELECT * FROM t1;

936

drop table t1,t2;

937

eval create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = $engine_type;

938

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

939

select distinct parent,child from t1 order by parent;

940

drop table t1;

941

942

943

# Test that MySQL priorities clustered indexes

944

945

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

946

eval create table t2 (a int not null auto_increment primary key, b int) ENGINE = $other_engine_type;

947

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

948

insert into t2 (a) select b from t1;

949

insert into t1 (b) select b from t2;

950

insert into t2 (a) select b from t1;

951

insert into t1 (a) select b from t2;

952

insert into t2 (a) select b from t1;

953

insert into t1 (a) select b from t2;

954

insert into t2 (a) select b from t1;

955

insert into t1 (a) select b from t2;

956

insert into t2 (a) select b from t1;

957

insert into t1 (a) select b from t2;

958

insert into t2 (a) select b from t1;

959

insert into t1 (a) select b from t2;

960

insert into t2 (a) select b from t1;

961

insert into t1 (a) select b from t2;

962

insert into t2 (a) select b from t1;

963

insert into t1 (a) select b from t2;

964

insert into t2 (a) select b from t1;

965

insert into t1 (a) select b from t2;

966

select count(*) from t1;

967

--replace_column 9 #

968

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

969

update t1 set c=a;

970

--replace_column 9 #

971

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

972

drop table t1,t2;

973

974

975

# Test of UPDATE ... ORDER BY

976

977

978

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

979

980

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

981

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

982

SELECT * from t1;

983

drop table t1;

984

985

eval create table t1 (a int not null, b int not null, key (a)) engine=$engine_type;

986

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

987

SET @tmp=0;

988

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

989

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

990

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

991

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

992

select * from t1 order by a,b;

993

drop table t1;

994

995

996

# Test of multi-table-updates (bug #1980).

997

998

999

eval create table t1 ( c char(8) not null ) engine=$engine_type;

1000

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');

1001

insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

1002

1003

alter table t1 add b char(8) not null;

1004

alter table t1 add a char(8) not null;

1005

alter table t1 add primary key (a,b,c);

1006

update t1 set a=c, b=c;

1007

1008

eval create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=$engine_type;

1009

insert into t2 select * from t1;

1010

1011

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

1012

drop table t1,t2;

1013

1014

1015

# test autoincrement with TRUNCATE

1016

1017

1018

SET AUTOCOMMIT=1;

1019

eval create table t1 (a integer auto_increment primary key) engine=$engine_type;

1020

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

1021

truncate table t1;

1022

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

1023

SELECT * from t1;

1024

drop table t1;

1025

1026

1027

if ($test_foreign_keys)

1028

{

1029

1030

# Test dictionary handling with spaceand quoting

1031

1032

1033

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

1034

eval 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=$engine_type;

1035

#show create table t2;

1036

drop table t2,t1;

1037

1038

1039

# Test of multi updated and foreign keys

1040

1041

1042

eval create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = $engine_type;

1043

insert into `t1`values ( 1 ) ;

1044

eval create table `t2` (`id` int( 11 ) not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = $engine_type;

1045

insert into `t2`values ( 1 ) ;

1046

eval create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = $engine_type;

1047

insert into `t3`values ( 1 ) ;

1048

--error 1451

1049

delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

1050

--error 1451

1051

update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

1052

--error 1054

1053

update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

1054

drop table t3,t2,t1;

1055

1056

1057

# test for recursion depth limit

1058

1059

eval create table t1(

1060

id int primary key,

1061

pid int,

1062

index(pid),

1063

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

1064

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

1065

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

1066

delete from t1 where id=0;

1067

delete from t1 where id=15;

1068

delete from t1 where id=0;

1069

1070

drop table t1;

1071

}

1072

# End of FOREIGN KEY tests

1073

1074

1075

# Test timestamps

1076

1077

1078

eval CREATE TABLE t1 (col1 int(1))ENGINE=$engine_type;

1079

eval CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx

1080

(stamp))ENGINE=$engine_type;

1081

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

1082

# Note that timestamp 3 is wrong

1083

insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);

1084

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

1085

'20020204120000' GROUP BY col1;

1086

drop table t1,t2;

1087

1088

1089

# Test by Francois MASUREL

1090

1091

1092

eval CREATE TABLE t1 (

1093

`id` int(10) NOT NULL auto_increment,

1094

`id_object` int(10) default '0',

1095

`id_version` int(10) NOT NULL default '1',

1096

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

1097

`description` text,

1098

PRIMARY KEY (`id`),

1099

KEY `id_object` (`id_object`),

1100

KEY `id_version` (`id_version`)

1101

) ENGINE=$engine_type;

1102

1103

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

1104

1105

eval CREATE TABLE t2 (

1106

`id` int(10) NOT NULL auto_increment,

1107

`id_version` int(10) NOT NULL default '1',

1108

PRIMARY KEY (`id`),

1109

KEY `id_version` (`id_version`)

1110

) ENGINE=$engine_type;

1111

1112

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

1113

1114

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

1115

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

1116

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

1117

drop table t1,t2;

1118

1119

# Live checksum feature available + enabled

1120

eval create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=$other_live_chks_engine_type;

1121

# Live checksum feature available + disabled

1122

eval create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=$other_live_chks_engine_type;

1123

1124

# Live checksum feature not available + enabled

1125

eval create table t3 (a int, b varchar(200), c varchar(200) not null) checksum=1 engine=$other_non_live_chks_engine_type;

1126

# Live checksum feature not available + disabled

1127

eval create table t4 (a int, b varchar(200), c varchar(200) not null) checksum=0 engine=$other_non_live_chks_engine_type;

1128

1129

# Live checksum feature probably available + enabled

1130

eval create table t5 (a int, b varchar(200), c text not null) checksum=1 engine=$engine_type;

1131

# Live checksum feature probably available + disabled

1132

eval create table t6 (a int, b varchar(200), c text not null) checksum=0 engine=$engine_type;

1133

1134

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

1135

insert t2 select * from t1;

1136

insert t3 select * from t1;

1137

insert t4 select * from t1;

1138

insert t5 select * from t1;

1139

insert t6 select * from t1;

1140

checksum table t1, t2, t3, t4, t5, t6, t7 quick;

1141

checksum table t1, t2, t3, t4, t5, t6, t7;

1142

checksum table t1, t2, t3, t4, t5, t6, t7 extended;

1143

# #show table status;

1144

drop table t1,t2,t3, t4, t5, t6;

1145

1146

1147

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

1148

# (Bug#2552: UNION returns NULL instead of expected value (innoDB only tables))

1149

1150

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

1151

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

1152

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

1153

drop table t1;

1154

1155

1156

# Bug#2160: Extra error message for CREATE TABLE LIKE with InnoDB

1157

1158

eval create table t1 (a int) engine=$engine_type;

1159

create table t2 like t1;

1160

show create table t2;

1161

drop table t1,t2;

1162

1163

if ($test_foreign_keys)

1164

{

1165

1166

# Test of automaticly created foreign keys

1167

1168

1169

eval create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=$engine_type;

1170

eval create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = $engine_type;

1171

show create table t1;

1172

show create table t2;

1173

create index id on t2 (id);

1174

show create table t2;

1175

create index id2 on t2 (id);

1176

show create table t2;

1177

drop index id2 on t2;

1178

--error 1025,1025

1179

drop index id on t2;

1180

show create table t2;

1181

drop table t2;

1182

1183

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

1184

show create table t2;

1185

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

1186

show create table t2;

1187

drop table t2;

1188

1189

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

1190

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

1191

show create table t2;

1192

drop table t2;

1193

1194

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

1195

show create table t2;

1196

drop table t2;

1197

1198

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

1199

show create table t2;

1200

drop table t2;

1201

1202

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

1203

show create table t2;

1204

drop table t2;

1205

1206

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

1207

show create table t2;

1208

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

1209

show create table t2;

1210

drop table t2;

1211

1212

# Test error handling

1213

1214

# Clean up filename -- embedded server reports whole path without .frm,

1215

# regular server reports relative path with .frm (argh!)

1216

--replace_result \\ / $DRIZZLE_TEST_DIR . /var/master-data/ / t2.frm t2

1217

--error 1005

1218

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

1219

1220

# bug#3749

1221

1222

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

1223

show create table t2;

1224

drop table t2;

1225

eval 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=$engine_type;

1226

show create table t2;

1227

drop table t2, t1;

1228

}

1229

# End of FOREIGN KEY tests

1230

1231

1232

1233

# Let us test binlog_cache_use and binlog_cache_disk_use status vars.

1234

# Actually this test has nothing to do with innodb per se, it just requires

1235

# transactional table.

1236

1237

flush status;

1238

show status like "binlog_cache_use";

1239

show status like "binlog_cache_disk_use";

1240

1241

eval create table t1 (a int) engine=$engine_type;

1242

1243

# Now we are going to create transaction which is long enough so its

1244

# transaction binlog will be flushed to disk...

1245

let $1=2000;

1246

disable_query_log;

1247

begin;

1248

while ($1)

1249

{

1250

eval insert into t1 values( $1 );

1251

dec $1;

1252

}

1253

commit;

1254

enable_query_log;

1255

show status like "binlog_cache_use";

1256

show status like "binlog_cache_disk_use";

1257

1258

# Transaction which should not be flushed to disk and so should not

1259

# increase binlog_cache_disk_use.

1260

begin;

1261

delete from t1;

1262

commit;

1263

show status like "binlog_cache_use";

1264

show status like "binlog_cache_disk_use";

1265

drop table t1;

1266

1267

1268

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

1269

1270

--error 1060

1271

eval create table t1 (c char(10), index (c,c)) engine=$engine_type;

1272

--error 1060

1273

eval create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=$engine_type;

1274

--error 1060

1275

eval create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=$engine_type;

1276

--error 1060

1277

eval create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=$engine_type;

1278

eval create table t1 (c1 char(10), c2 char(10)) engine=$engine_type;

1279

--error 1060

1280

alter table t1 add key (c1,c1);

1281

--error 1060

1282

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

1283

--error 1060

1284

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

1285

--error 1060

1286

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

1287

drop table t1;

1288

1289

1290

# Bug #4082: integer truncation

1291

1292

1293

eval create table t1(a int(1) , b int(1)) engine=$engine_type;

1294

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

1295

select distinct concat(a, b) from t1;

1296

drop table t1;

1297

1298

if ($fulltext_query_unsupported)

1299

{

1300

1301

# BUG#7709 test case - Boolean fulltext query against unsupported

1302

# engines does not fail

1303

1304

1305

eval CREATE TABLE t1 ( a char(10) ) ENGINE=$engine_type;

1306

--error 1214

1307

SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);

1308

DROP TABLE t1;

1309

}

1310

1311

if ($test_foreign_keys)

1312

{

1313

1314

# check null values #1

1315

1316

1317

--disable_warnings

1318

eval CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=$engine_type DEFAULT CHARSET=latin1;

1319

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

1320

eval CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),

1321

CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=$engine_type DEFAULT CHARSET=latin1;

1322

--enable_warnings

1323

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

1324

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;

1325

DROP TABLE t2;

1326

DROP TABLE t1;

1327

}

1328

1329

1330

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

1331

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

1332

1333

eval create temporary table t1 (a int) engine=$engine_type;

1334

insert into t1 values (4711);

1335

truncate t1;

1336

insert into t1 values (42);

1337

select * from t1;

1338

drop table t1;

1339

# Show that it works with permanent tables too.

1340

eval create table t1 (a int) engine=$engine_type;

1341

insert into t1 values (4711);

1342

truncate t1;

1343

insert into t1 values (42);

1344

select * from t1;

1345

drop table t1;

1346

1347

1348

# Bug #13025 Server crash during filesort

1349

1350

1351

eval 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=$engine_type;

1352

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

1353

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

1354

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

1355

drop table t1;

1356

1357

1358

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

1359

1360

1361

eval create table t1 (a char(1), b char(1), key(a, b)) engine=$engine_type;

1362

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

1363

select min(a) from t1;

1364

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

1365

drop table t1;

1366

1367

# End of 4.1 tests

1368

1369

1370

# range optimizer problem

1371

1372

1373

eval create table t1 (x bigint not null primary key) engine=$engine_type;

1374

insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);

1375

select * from t1;

1376

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

1377

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

1378

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

1379

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

1380

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

1381

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

1382

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

1383

select * from t1 where x > -16;

1384

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

1385

drop table t1;

1386

1387

# Please do not remove the following skipped InnoDB specific tests.

1388

# They make the synchronization with innodb.test easier and give

1389

# an idea what to test on other storage engines.

1390

if (0)

1391

{

1392

1393

# Test for testable InnoDB status variables. This test

1394

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

1395

show status like "Innodb_buffer_pool_pages_total";

1396

show status like "Innodb_page_size";

1397

show status like "Innodb_rows_deleted";

1398

show status like "Innodb_rows_inserted";

1399

show status like "Innodb_rows_updated";

1400

1401

# Test for row locks InnoDB status variables.

1402

show status like "Innodb_row_lock_waits";

1403

show status like "Innodb_row_lock_current_waits";

1404

show status like "Innodb_row_lock_time";

1405

show status like "Innodb_row_lock_time_max";

1406

show status like "Innodb_row_lock_time_avg";

1407

1408

# Test for innodb_sync_spin_loops variable

1409

show variables like "innodb_sync_spin_loops";

1410

set global innodb_sync_spin_loops=1000;

1411

show variables like "innodb_sync_spin_loops";

1412

set global innodb_sync_spin_loops=0;

1413

show variables like "innodb_sync_spin_loops";

1414

set global innodb_sync_spin_loops=20;

1415

show variables like "innodb_sync_spin_loops";

1416

1417

# Test for innodb_thread_concurrency variable

1418

show variables like "innodb_thread_concurrency";

1419

set global innodb_thread_concurrency=1001;

1420

show variables like "innodb_thread_concurrency";

1421

set global innodb_thread_concurrency=0;

1422

show variables like "innodb_thread_concurrency";

1423

set global innodb_thread_concurrency=16;

1424

show variables like "innodb_thread_concurrency";

1425

1426

# Test for innodb_concurrency_tickets variable

1427

show variables like "innodb_concurrency_tickets";

1428

set global innodb_concurrency_tickets=1000;

1429

show variables like "innodb_concurrency_tickets";

1430

set global innodb_concurrency_tickets=0;

1431

show variables like "innodb_concurrency_tickets";

1432

set global innodb_concurrency_tickets=500;

1433

show variables like "innodb_concurrency_tickets";

1434

1435

# Test for innodb_thread_sleep_delay variable

1436

show variables like "innodb_thread_sleep_delay";

1437

set global innodb_thread_sleep_delay=100000;

1438

show variables like "innodb_thread_sleep_delay";

1439

set global innodb_thread_sleep_delay=0;

1440

show variables like "innodb_thread_sleep_delay";

1441

set global innodb_thread_sleep_delay=10000;

1442

show variables like "innodb_thread_sleep_delay";

1443

1444

}

1445

1446

1447

1448

# Test varchar

1449

1450

1451

let $default=`select @@storage_engine`;

1452

eval set storage_engine=$engine_type;

1453

source include/varchar.inc;

1454

1455

1456

# Some errors/warnings on create

1457

1458

1459

# Clean up filename -- embedded server reports whole path without .frm,

1460

# regular server reports relative path with .frm (argh!)

1461

--replace_result \\ / $DRIZZLE_TEST_DIR . /var/master-data/ / t1.frm t1

1462

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

1463

drop table t1;

1464

create table t1 (v varchar(65536));

1465

show create table t1;

1466

drop table t1;

1467

create table t1 (v varchar(65530) character set utf8);

1468

show create table t1;

1469

drop table t1;

1470

1471

eval set storage_engine=$default;

1472

1473

# InnoDB specific varchar tests

1474

eval create table t1 (v varchar(16384)) engine=$engine_type;

1475

drop table t1;

1476

1477

1478

# BUG#11039 Wrong key length in min()

1479

1480

1481

eval create table t1 (a char(1), b char(1), key(a, b)) engine=$engine_type;

1482

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

1483

select min(a) from t1;

1484

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

1485

drop table t1;

1486

1487

1488

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

1489

1490

1491

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

1492

insert into t1 (b) values (1);

1493

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

1494

select * from t1;

1495

truncate table t1;

1496

insert into t1 (b) values (1);

1497

replace into t1 (b) values (2);

1498

replace into t1 (b) values (1);

1499

replace into t1 (b) values (3);

1500

select * from t1;

1501

drop table t1;

1502

1503

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

1504

key (rowid), unique(val)) engine=$engine_type;

1505

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

1506

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

1507

--error ER_DUP_ENTRY

1508

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

1509

select * from t1;

1510

drop table t1;

1511

1512

if ($no_autoinc_update)

1513

{

1514

1515

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

1516

1517

1518

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

1519

insert into t1 (val) values (1);

1520

update t1 set a=2 where a=1;

1521

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

1522

--error ER_DUP_ENTRY

1523

insert into t1 (val) values (1);

1524

select * from t1;

1525

drop table t1;

1526

}

1527

1528

1529

1530

# Bug#10465: DECIMAL, crash on DELETE (InnoDB only)

1531

1532

1533

--disable_warnings

1534

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

1535

--enable_warnings

1536

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

1537

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

1538

SELECT GRADE FROM t1 WHERE GRADE= 151;

1539

DROP TABLE t1;

1540

1541

1542

# Bug #12340 multitable delete deletes only one record

1543

1544

eval create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=$engine_type;

1545

eval create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=$engine_type;

1546

insert into t2 values ('aa','cc');

1547

insert into t1 values ('aa','bb'),('aa','cc');

1548

delete t1 from t1,t2 where f1=f3 and f4='cc';

1549

select * from t1;

1550

drop table t1,t2;

1551

1552

if ($test_foreign_keys)

1553

{

1554

1555

# Test that the slow TRUNCATE implementation resets autoincrement columns

1556

# (bug #11946)

1557

1558

1559

eval CREATE TABLE t1 (

1560

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

1561

) ENGINE=$engine_type;

1562

1563

eval CREATE TABLE t2 (

1564

id INTEGER NOT NULL,

1565

FOREIGN KEY (id) REFERENCES t1 (id)

1566

) ENGINE=$engine_type;

1567

1568

INSERT INTO t1 (id) VALUES (NULL);

1569

SELECT * FROM t1;

1570

TRUNCATE t1;

1571

INSERT INTO t1 (id) VALUES (NULL);

1572

SELECT * FROM t1;

1573

1574

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

1575

# rows resets autoincrement columns

1576

DELETE FROM t1;

1577

TRUNCATE t1;

1578

INSERT INTO t1 (id) VALUES (NULL);

1579

SELECT * FROM t1;

1580

DROP TABLE t2, t1;

1581

1582

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

1583

eval CREATE TABLE t1

1584

(

1585

id INT PRIMARY KEY

1586

) ENGINE=$engine_type;

1587

1588

--error 1005,1005

1589

eval CREATE TEMPORARY TABLE t2

1590

(

1591

id INT NOT NULL PRIMARY KEY,

1592

b INT,

1593

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

1594

) ENGINE=$engine_type;

1595

DROP TABLE t1;

1596

}

1597

# End of FOREIGN KEY test

1598

1599

# Please do not remove the following skipped InnoDB specific tests.

1600

# They make the synchronization with innodb.test easier and give

1601

# an idea what to test on other storage engines.

1602

if (0)

1603

{

1604

1605

1606

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

1607

1608

1609

# prefix index

1610

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

1611

character set = latin1 engine = $engine_type;

1612

1613

# normal indexes

1614

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

1615

character set = latin1 engine = $engine_type;

1616

eval create table t3 (col1 binary(255), index (col1))

1617

character set = latin1 engine = $engine_type;

1618

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

1619

character set = latin1 engine = $engine_type;

1620

eval create table t5 (col1 varchar(767) primary key)

1621

character set = latin1 engine = $engine_type;

1622

eval create table t6 (col1 varbinary(767) primary key)

1623

character set = latin1 engine = $engine_type;

1624

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

1625

character set = latin1 engine = $engine_type;

1626

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

1627

character set = latin1 engine = $engine_type;

1628

1629

1630

# multi-column indexes are allowed to be longer

1631

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

1632

character set = latin1 engine = $engine_type;

1633

1634

show create table t9;

1635

1636

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

1637

1638

# these should have their index length trimmed

1639

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

1640

character set = latin1 engine = $engine_type;

1641

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

1642

character set = latin1 engine = $engine_type;

1643

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

1644

character set = latin1 engine = $engine_type;

1645

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

1646

character set = latin1 engine = $engine_type;

1647

1648

show create table t1;

1649

1650

drop table t1, t2, t3, t4;

1651

1652

}

1653

# End of skipped test

1654

1655

# Please do not remove the following skipped InnoDB specific tests.

1656

# They make the synchronization with innodb.test easier and give

1657

# an idea what to test on other storage engines.

1658

if (0)

1659

{

1660

1661

# these should be refused

1662

--error 1071

1663

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

1664

character set = latin1 engine = $engine_type;

1665

--error 1071

1666

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

1667

character set = latin1 engine = $engine_type;

1668

--error 1071

1669

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

1670

character set = latin1 engine = $engine_type;

1671

--error 1071

1672

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

1673

character set = latin1 engine = $engine_type;

1674

1675

}

1676

1677

if ($test_foreign_keys)

1678

{

1679

1680

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

1681

1682

1683

eval CREATE TABLE t1

1684

(

1685

id INT PRIMARY KEY

1686

) ENGINE=$engine_type;

1687

1688

eval CREATE TABLE t2

1689

(

1690

v INT,

1691

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

1692

) ENGINE=$engine_type;

1693

1694

--error 1452

1695

INSERT INTO t2 VALUES(2);

1696

1697

INSERT INTO t1 VALUES(1);

1698

INSERT INTO t2 VALUES(1);

1699

1700

--error 1451

1701

DELETE FROM t1 WHERE id = 1;

1702

1703

--error 1217

1704

DROP TABLE t1;

1705

1706

SET FOREIGN_KEY_CHECKS=0;

1707

DROP TABLE t1;

1708

SET FOREIGN_KEY_CHECKS=1;

1709

1710

--error 1452

1711

INSERT INTO t2 VALUES(3);

1712

1713

DROP TABLE t2;

1714

}

1715

# End of FOREIGN tests

1716

1717

if ($test_transactions)

1718

{

1719

1720

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

1721

1722

connect (a,localhost,root,,);

1723

connect (b,localhost,root,,);

1724

connection a;

1725

eval create table t1(a int not null) engine=$engine_type DEFAULT CHARSET=latin1;

1726

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

1727

set autocommit=0;

1728

checksum table t1;

1729

connection b;

1730

insert into t1 values(3);

1731

connection a;

1732

1733

# Here checksum should not see insert

1734

1735

checksum table t1;

1736

connection a;

1737

commit;

1738

checksum table t1;

1739

commit;

1740

drop table t1;

1741

1742

# autocommit = 1

1743

1744

connection a;

1745

eval create table t1(a int not null) engine=$engine_type DEFAULT CHARSET=latin1;

1746

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

1747

set autocommit=1;

1748

checksum table t1;

1749

connection b;

1750

set autocommit=1;

1751

insert into t1 values(3);

1752

connection a;

1753

1754

# Here checksum sees insert

1755

1756

checksum table t1;

1757

drop table t1;

1758

1759

connection default;

1760

disconnect a;

1761

disconnect b;

1762

}

1763

1764

# tests for bugs #9802 and #13778

1765

1766

if ($test_foreign_keys)

1767

{

1768

# test that FKs between invalid types are not accepted

1769

1770

set foreign_key_checks=0;

1771

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

1772

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

1773

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

1774

set foreign_key_checks=1;

1775

drop table t2;

1776

1777

# test that FKs between different charsets are not accepted in CREATE even

1778

# when f_k_c is 0

1779

1780

set foreign_key_checks=0;

1781

eval create table t1(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=latin1;

1782

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

1783

eval create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=utf8;

1784

set foreign_key_checks=1;

1785

drop table t1;

1786

1787

# test that invalid datatype conversions with ALTER are not allowed

1788

1789

set foreign_key_checks=0;

1790

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

1791

eval create table t1(a varchar(10) primary key) engine = $engine_type;

1792

alter table t1 modify column a int;

1793

set foreign_key_checks=1;

1794

drop table t2,t1;

1795

1796

# test that charset conversions with ALTER are allowed when f_k_c is 0

1797

1798

set foreign_key_checks=0;

1799

eval create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=latin1;

1800

eval create table t1(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=latin1;

1801

alter table t1 convert to character set utf8;

1802

set foreign_key_checks=1;

1803

drop table t2,t1;

1804

1805

# test that RENAME does not allow invalid charsets when f_k_c is 0

1806

1807

set foreign_key_checks=0;

1808

eval create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=latin1;

1809

eval create table t3(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=utf8;

1810

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

1811

rename table t3 to t1;

1812

set foreign_key_checks=1;

1813

drop table t2,t3;

1814

1815

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

1816

1817

eval create table t1(a int primary key) row_format=redundant engine=$engine_type;

1818

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

1819

eval create table t3(a int primary key) row_format=compact engine=$engine_type;

1820

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

1821

1822

insert into t1 values(1);

1823

insert into t3 values(1);

1824

insert into t2 values(2);

1825

insert into t4 values(2);

1826

insert into t2 values(1);

1827

insert into t4 values(1);

1828

update t1 set a=2;

1829

update t2 set a=2;

1830

update t3 set a=2;

1831

update t4 set a=2;

1832

truncate t1;

1833

truncate t3;

1834

truncate t2;

1835

truncate t4;

1836

truncate t1;

1837

truncate t3;

1838

1839

drop table t4,t3,t2,t1;

1840

}

1841

# End of FOREIGN KEY tests

1842

1843

1844

# Please do not remove the following skipped InnoDB specific tests.

1845

# They make the synchronization with innodb.test easier and give

1846

# an idea what to test on other storage engines.

1847

if (0)

1848

{

1849

1850

1851

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

1852

1853

eval create table t1 (a varchar(255) character set utf8,

1854

b varchar(255) character set utf8,

1855

c varchar(255) character set utf8,

1856

d varchar(255) character set utf8,

1857

key (a,b,c,d)) engine=$engine_type;

1858

drop table t1;

1859

--error ER_TOO_LONG_KEY

1860

eval create table t1 (a varchar(255) character set utf8,

1861

b varchar(255) character set utf8,

1862

c varchar(255) character set utf8,

1863

d varchar(255) character set utf8,

1864

e varchar(255) character set utf8,

1865

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

1866

1867

1868

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

1869

1870

eval create table t1 (s1 varbinary(2),primary key (s1)) engine=$engine_type;

1871

eval create table t2 (s1 binary(2),primary key (s1)) engine=$engine_type;

1872

eval create table t3 (s1 varchar(2) binary,primary key (s1)) engine=$engine_type;

1873

eval create table t4 (s1 char(2) binary,primary key (s1)) engine=$engine_type;

1874

1875

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

1876

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

1877

insert into t2 values (0x41),(0x4120);

1878

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

1879

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

1880

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

1881

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

1882

select hex(s1) from t1;

1883

select hex(s1) from t2;

1884

select hex(s1) from t3;

1885

select hex(s1) from t4;

1886

drop table t1,t2,t3,t4;

1887

}

1888

1889

if (test_foreign_keys)

1890

{

1891

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

1892

eval create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=$engine_type;

1893

1894

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

1895

insert into t2 values(0x42);

1896

insert into t2 values(0x41);

1897

select hex(s1) from t2;

1898

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

1899

select hex(s1) from t2;

1900

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

1901

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

1902

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

1903

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

1904

select hex(s1) from t2;

1905

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

1906

select hex(s1) from t2;

1907

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

1908

select hex(s1) from t2;

1909

delete from t1 where a=1;

1910

delete from t1 where a=2;

1911

update t2 set s1=0x4120;

1912

delete from t1;

1913

delete from t1 where a!=3;

1914

select a,hex(s1) from t1;

1915

select hex(s1) from t2;

1916

1917

drop table t2,t1;

1918

1919

eval create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=$engine_type;

1920

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

1921

1922

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

1923

insert into t2 values(0x41);

1924

select hex(s1) from t2;

1925

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

1926

select hex(s1) from t2;

1927

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

1928

select hex(s1) from t2;

1929

delete from t1 where a=1;

1930

delete from t1 where a=2;

1931

select a,hex(s1) from t1;

1932

select hex(s1) from t2;

1933

1934

drop table t2,t1;

1935

}

1936

# End FOREIGN KEY tests

1937

1938

if ($test_foreign_keys)

1939

{

1940

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

1941

# generated foreign key identifier. (Bug #16387)

1942

1943

eval CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=$engine_type;

1944

eval CREATE TABLE t2(a INT) ENGINE=$engine_type;

1945

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

1946

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;

1947

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

1948

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;

1949

SHOW CREATE TABLE t2;

1950

DROP TABLE t2,t1;

1951

}

1952

1953

1954

# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing

1955

1956

1957

connect (a,localhost,root,,);

1958

connect (b,localhost,root,,);

1959

connection a;

1960

eval create table t1(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

1961

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

1962

commit;

1963

connection b;

1964

set autocommit = 0;

1965

update t1 set b = 5 where a = 2;

1966

connection a;

1967

delimiter |;

1968

create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |

1969

delimiter ;|

1970

set autocommit = 0;

1971

connection a;

1972

insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),

1973

(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),

1974

(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),

1975

(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),

1976

(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);

1977

connection b;

1978

commit;

1979

connection a;

1980

commit;

1981

drop trigger t1t;

1982

drop table t1;

1983

disconnect a;

1984

disconnect b;

1985

1986

# Another trigger test

1987

1988

connect (a,localhost,root,,);

1989

connect (b,localhost,root,,);

1990

connection a;

1991

eval create table t1(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

1992

eval create table t2(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

1993

eval create table t3(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

1994

eval create table t4(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

1995

eval create table t5(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

1996

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

1997

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

1998

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

1999

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

2000

insert into t3(a) values (5),(7),(8);

2001

insert into t4(a) values (5),(7),(8);

2002

insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);

2003

2004

delimiter |;

2005

create trigger t1t before insert on t1 for each row begin

2006

INSERT INTO t2 SET a = NEW.a;

2007

end |

2008

2009

create trigger t2t before insert on t2 for each row begin

2010

DELETE FROM t3 WHERE a = NEW.a;

2011

end |

2012

2013

create trigger t3t before delete on t3 for each row begin

2014

UPDATE t4 SET b = b + 1 WHERE a = OLD.a;

2015

end |

2016

2017

create trigger t4t before update on t4 for each row begin

2018

UPDATE t5 SET b = b + 1 where a = NEW.a;

2019

end |

2020

delimiter ;|

2021

commit;

2022

set autocommit = 0;

2023

update t1 set b = b + 5 where a = 1;

2024

update t2 set b = b + 5 where a = 1;

2025

update t3 set b = b + 5 where a = 1;

2026

update t4 set b = b + 5 where a = 1;

2027

insert into t5(a) values(20);

2028

connection b;

2029

set autocommit = 0;

2030

insert into t1(a) values(7);

2031

insert into t2(a) values(8);

2032

delete from t2 where a = 3;

2033

update t4 set b = b + 1 where a = 3;

2034

commit;

2035

drop trigger t1t;

2036

drop trigger t2t;

2037

drop trigger t3t;

2038

drop trigger t4t;

2039

drop table t1, t2, t3, t4, t5;

2040

connection default;

2041

disconnect a;

2042

disconnect b;

2043

2044

if ($test_foreign_keys)

2045

{

2046

2047

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

2048

# error message (bug #9680)

2049

2050

2051

eval CREATE TABLE t1 (

2052

field1 varchar(8) NOT NULL DEFAULT '',

2053

field2 varchar(8) NOT NULL DEFAULT '',

2054

PRIMARY KEY (field1, field2)

2055

) ENGINE=$engine_type;

2056

2057

eval CREATE TABLE t2 (

2058

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

2059

FOREIGN KEY (field1) REFERENCES t1 (field1)

2060

ON DELETE CASCADE ON UPDATE CASCADE

2061

) ENGINE=$engine_type;

2062

2063

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

2064

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

2065

2066

INSERT INTO t2 VALUES ('old');

2067

INSERT INTO t2 VALUES ('other');

2068

2069

--error ER_FOREIGN_DUPLICATE_KEY

2070

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

2071

2072

DROP TABLE t2;

2073

DROP TABLE t1;

2074

2075

2076

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

2077

2078

eval create table t1 (

2079

c1 bigint not null,

2080

c2 bigint not null,

2081

primary key (c1),

2082

unique key (c2)

2083

) engine=$engine_type;

2084

2085

eval create table t2 (

2086

c1 bigint not null,

2087

primary key (c1)

2088

) engine=$engine_type;

2089

2090

alter table t1 add constraint c2_fk foreign key (c2)

2091

references t2(c1) on delete cascade;

2092

show create table t1;

2093

2094

alter table t1 drop foreign key c2_fk;

2095

show create table t1;

2096

2097

drop table t1, t2;

2098

}

2099

# End FOREIGN KEY test

2100

2101

2102

# Bug #14360: problem with intervals

2103

2104

2105

eval create table t1(a date) engine=$engine_type;

2106

eval create table t2(a date, key(a)) engine=$engine_type;

2107

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

2108

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

2109

select * from t1, t2

2110

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

2111

drop table t1, t2;

2112

2113

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

2114

primary key(f_id, id)) engine=$engine_type;

2115

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

2116

primary key(id)) engine=$engine_type;

2117

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

2118

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

2119

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

2120

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

2121

commit;

2122

DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)

2123

WHERE mm.id IS NULL;

2124

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

2125

where mm.id is null lock in share mode;

2126

drop table t1,t2;

2127

2128

2129

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

2130

# update (because READ COMMITTED isolation level)

2131

2132

2133

connect (a,localhost,root,,);

2134

connect (b,localhost,root,,);

2135

connection a;

2136

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

2137

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

2138

commit;

2139

set autocommit = 0;

2140

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2141

update t1 set b = 5 where b = 1;

2142

connection b;

2143

set autocommit = 0;

2144

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2145

2146

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

2147

2148

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

2149

connection a;

2150

commit;

2151

connection b;

2152

commit;

2153

drop table t1;

2154

connection default;

2155

disconnect a;

2156

disconnect b;

2157

2158

if ($test_transactions)

2159

{

2160

2161

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

2162

# using READ COMMITTED isolation level)

2163

2164

2165

connect (a,localhost,root,,);

2166

connect (b,localhost,root,,);

2167

connection a;

2168

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

2169

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

2170

commit;

2171

set autocommit = 0;

2172

select * from t1 lock in share mode;

2173

update t1 set b = 5 where b = 1;

2174

connection b;

2175

set autocommit = 0;

2176

2177

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

2178

2179

--error 1205

2180

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

2181

2182

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

2183

2184

--error 1205

2185

connection a;

2186

commit;

2187

connection b;

2188

commit;

2189

connection default;

2190

disconnect a;

2191

disconnect b;

2192

drop table t1;

2193

2194

2195

# Consistent read should be used in following selects

2196

2197

# 1) INSERT INTO ... SELECT

2198

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

2199

# 3) CREATE ... SELECT

2200

2201

connect (a,localhost,root,,);

2202

connect (b,localhost,root,,);

2203

connection a;

2204

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

2205

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

2206

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

2207

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

2208

commit;

2209

set autocommit = 0;

2210

select * from t2 for update;

2211

connection b;

2212

set autocommit = 0;

2213

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2214

insert into t1 select * from t2;

2215

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

2216

eval create table t3(d int not null, e int, primary key(d)) engine=$engine_type

2217

select * from t2;

2218

commit;

2219

connection a;

2220

commit;

2221

connection default;

2222

disconnect a;

2223

disconnect b;

2224

drop table t1, t2, t3;

2225

2226

2227

# Consistent read should not be used if

2228

2229

# (a) isolation level is serializable OR

2230

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

2231

# (c) select ... for update

2232

2233

# in following queries:

2234

2235

# 1) INSERT INTO ... SELECT

2236

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

2237

# 3) CREATE ... SELECT

2238

2239

connect (a,localhost,root,,);

2240

eval SET SESSION STORAGE_ENGINE = $engine_type;

2241

connect (b,localhost,root,,);

2242

eval SET SESSION STORAGE_ENGINE = $engine_type;

2243

connect (c,localhost,root,,);

2244

eval SET SESSION STORAGE_ENGINE = $engine_type;

2245

connect (d,localhost,root,,);

2246

eval SET SESSION STORAGE_ENGINE = $engine_type;

2247

connect (e,localhost,root,,);

2248

eval SET SESSION STORAGE_ENGINE = $engine_type;

2249

connect (f,localhost,root,,);

2250

eval SET SESSION STORAGE_ENGINE = $engine_type;

2251

connect (g,localhost,root,,);

2252

eval SET SESSION STORAGE_ENGINE = $engine_type;

2253

connect (h,localhost,root,,);

2254

eval SET SESSION STORAGE_ENGINE = $engine_type;

2255

connect (i,localhost,root,,);

2256

eval SET SESSION STORAGE_ENGINE = $engine_type;

2257

connect (j,localhost,root,,);

2258

eval SET SESSION STORAGE_ENGINE = $engine_type;

2259

connection a;

2260

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

2261

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

2262

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

2263

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

2264

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

2265

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

2266

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

2267

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

2268

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

2269

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

2270

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

2271

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

2272

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

2273

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

2274

commit;

2275

set autocommit = 0;

2276

select * from t2 for update;

2277

connection b;

2278

set autocommit = 0;

2279

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2280

--send

2281

insert into t1 select * from t2;

2282

connection c;

2283

set autocommit = 0;

2284

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2285

--send

2286

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

2287

connection d;

2288

set autocommit = 0;

2289

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2290

--send

2291

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

2292

connection e;

2293

set autocommit = 0;

2294

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2295

--send

2296

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

2297

connection f;

2298

set autocommit = 0;

2299

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2300

--send

2301

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

2302

connection g;

2303

set autocommit = 0;

2304

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2305

--send

2306

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

2307

connection h;

2308

set autocommit = 0;

2309

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2310

--send

2311

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

2312

connection i;

2313

set autocommit = 0;

2314

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2315

--send

2316

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

2317

connection j;

2318

set autocommit = 0;

2319

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2320

--send

2321

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

2322

2323

connection b;

2324

--error 1205

2325

reap;

2326

2327

connection c;

2328

--error 1205

2329

reap;

2330

2331

connection d;

2332

--error 1205

2333

reap;

2334

2335

connection e;

2336

--error 1205

2337

reap;

2338

2339

connection f;

2340

--error 1205

2341

reap;

2342

2343

connection g;

2344

--error 1205

2345

reap;

2346

2347

connection h;

2348

--error 1205

2349

reap;

2350

2351

connection i;

2352

--error 1205

2353

reap;

2354

2355

connection j;

2356

--error 1205

2357

reap;

2358

2359

connection a;

2360

commit;

2361

2362

connection default;

2363

disconnect a;

2364

disconnect b;

2365

disconnect c;

2366

disconnect d;

2367

disconnect e;

2368

disconnect f;

2369

disconnect g;

2370

disconnect h;

2371

disconnect i;

2372

disconnect j;

2373

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

2374

}

2375

# End transactional tests

2376

2377

if (test_foreign_keys)

2378

{

2379

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

2380

--error 1005

2381

eval CREATE TABLE t1 (DB_ROW_ID int) engine=$engine_type;

2382

2383

2384

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

2385

2386

2387

eval CREATE TABLE t1 (

2388

a BIGINT(20) NOT NULL,

2389

PRIMARY KEY (a)

2390

) ENGINE=$engine_type DEFAULT CHARSET=UTF8;

2391

2392

eval CREATE TABLE t2 (

2393

a BIGINT(20) NOT NULL,

2394

b VARCHAR(128) NOT NULL,

2395

c TEXT NOT NULL,

2396

PRIMARY KEY (a,b),

2397

KEY idx_t2_b_c (b,c(200)),

2398

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

2399

ON DELETE CASCADE

2400

) ENGINE=$engine_type DEFAULT CHARSET=UTF8;

2401

2402

INSERT INTO t1 VALUES (1);

2403

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

2404

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

2405

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

2406

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

2407

2408

SELECT * FROM t2 WHERE b = 'customer_over';

2409

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

2410

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

2411

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

2412

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

2413

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

2414

2415

drop table t2, t1;

2416

}

2417

2418

if ($no_spatial_key)

2419

{

2420

2421

# Bug #15680 (SPATIAL key in innodb)

2422

2423

--error ER_TABLE_CANT_HANDLE_SPKEYS

2424

eval create table t1 (g geometry not null, spatial gk(g)) engine=$engine_type;

2425

}

2426

2427

2428

# Test optimize on table with open transaction

2429

2430

2431

eval CREATE TABLE t1 ( a int ) ENGINE=$engine_type;

2432

BEGIN;

2433

INSERT INTO t1 VALUES (1);

2434

OPTIMIZE TABLE t1;

2435

DROP TABLE t1;

2436

2437

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

2438

# #

2439

# This is derivate of t/innodb.test and has to be maintained by #

2440

# MySQL guys only. #

2441

# #

2442

# Please synchronize this file from time to time with t/innodb.test. #

2443

# Please, DO NOT create a toplevel testcase innodb-mix2.test, because #

2444

# innodb.test does already these tests. #

2445

# #

2446

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

Older »