~drizzle-trunk/drizzle/development

create TEMPORARY table t1 (a int not null default 0, b int not null default 0, c int not null default 0, d int not null default 0, e int not null default 0, f int not null default 0, g int not null default 0, h int not null default 0,i int not null default 0, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;

223

insert into t1 (a) values(1);

224

--replace_column 1 # 6 # 7 # 8 # 9 # 10 #

225

show table status like 't1';

226

drop table t1;

227

228

229

# Bug 2361 (Don't drop UNIQUE with DROP PRIMARY KEY)

230

231

232

CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE);

233

ALTER TABLE t1 DROP PRIMARY KEY;

234

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

235

SHOW CREATE TABLE t1;

236

--error ER_CANT_DROP_FIELD_OR_KEY

237

ALTER TABLE t1 DROP PRIMARY KEY;

238

DROP TABLE t1;

239

240

# BUG#3899

241

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

242

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

243

--error ER_CANT_DROP_FIELD_OR_KEY

244

alter table t1 drop key no_such_key;

245

alter table t1 drop key a;

246

drop table t1;

247

248

249

# BUG 12207 alter table ... discard table space on MyISAM table causes ERROR 2013 (HY000)

250

251

# Some platforms (Mac OS X, Windows) will send the error message using small letters.

252

CREATE TEMPORARY TABLE T12207(a int) ENGINE=MYISAM;

253

--replace_result t12207 T12207

254

--error ER_ILLEGAL_HA

255

ALTER TABLE T12207 DISCARD TABLESPACE;

256

DROP TABLE T12207;

257

258

259

# Test for bug #7884 "Able to add invalid unique index on TIMESTAMP prefix"

260

# MySQL should not think that packed field with non-zero decimals is

261

# geometry field and allow to create prefix index which is

262

# shorter than packed field length.

263

264

create table t1 ( a timestamp );

265

--error ER_WRONG_SUB_KEY

266

alter table t1 add unique ( a(1) );

267

drop table t1;

268

269

270

# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table

271

272

# This problem happens if the data change is compatible.

273

# Changing to the same type is compatible for example.

274

275

--disable_warnings

276

drop table if exists t1;

277

--enable_warnings

278

create table t1 (a int, key(a));

279

show indexes from t1;

280

--echo "this used not to disable the index"

281

alter table t1 modify a int;

282

show indexes from t1;

283

284

alter table t1 enable keys;

285

show indexes from t1;

286

287

alter table t1 modify a bigint;

288

show indexes from t1;

289

290

alter table t1 enable keys;

291

show indexes from t1;

292

293

alter table t1 add b char(10);

294

show indexes from t1;

295

296

alter table t1 add c decimal(10,2);

297

show indexes from t1;

298

299

--echo "this however did"

300

alter table t1;

301

show indexes from t1;

302

303

desc t1;

304

305

alter table t1 add d decimal(15,5);

306

--echo "The key should still be disabled"

307

show indexes from t1;

308

309

drop table t1;

310

311

--echo "Now will test with one unique index"

312

create table t1(a int, b char(10), unique(a));

313

show indexes from t1;

314

alter table t1;

315

show indexes from t1;

316

alter table t1 enable keys;

317

318

--echo "If no copy on noop change, this won't touch the data file"

319

--echo "Unique index, no change"

320

alter table t1 modify a int;

321

show indexes from t1;

322

323

--echo "Change the type implying data copy"

324

--echo "Unique index, no change"

325

alter table t1 modify a bigint;

326

show indexes from t1;

327

328

alter table t1 modify a bigint;

329

show indexes from t1;

330

331

alter table t1 modify a int;

332

show indexes from t1;

333

334

drop table t1;

335

336

--echo "Now will test with one unique and one non-unique index"

337

create table t1(a int, b char(10), unique(a), key(b));

338

show indexes from t1;

339

alter table t1;

340

show indexes from t1;

341

alter table t1 enable keys;

342

343

344

--echo "If no copy on noop change, this won't touch the data file"

345

--echo "The non-unique index will be disabled"

346

alter table t1 modify a int;

347

show indexes from t1;

348

alter table t1 enable keys;

349

show indexes from t1;

350

351

--echo "Change the type implying data copy"

352

--echo "The non-unique index will be disabled"

353

alter table t1 modify a bigint;

354

show indexes from t1;

355

356

--echo "Change again the type, but leave the indexes as_is"

357

alter table t1 modify a int;

358

show indexes from t1;

359

--echo "Try the same. When data is no copied on similar tables, this is noop"

360

alter table t1 modify a int;

361

show indexes from t1;

362

363

drop table t1;

364

365

366

367

# Bug#11493 - Alter table rename to default database does not work without

368

# db name qualifying

369

370

create database mysqltest;

371

create table t1 (c1 int);

372

# Move table to other database.

373

alter table t1 rename mysqltest.t1;

374

# Assure that it has moved.

375

--error ER_BAD_TABLE_ERROR

376

drop table t1;

377

# Move table back.

378

alter table mysqltest.t1 rename t1;

379

# Assure that it is back.

380

drop table t1;

381

# Now test for correct message if no database is selected.

382

# Create t1 in 'test'.

383

create table t1 (c1 int);

384

# Change to other db.

385

use mysqltest;

386

# Drop the current db. This de-selects any db.

387

drop database mysqltest;

388

# Now test for correct message.

389

--error ER_NO_DB_ERROR

390

alter table test.t1 rename t1;

391

# Check that explicit qualifying works even with no selected db.

392

alter table test.t1 rename test.t1;

393

# Go back to standard 'test' db.

394

use test;

395

drop table t1;

396

397

398

# BUG#23404 - ROW_FORMAT=COMPACT option is lost is an index is added to the

399

# table

400

401

CREATE TABLE t1(a INT) ROW_FORMAT=COMPACT;

402

CREATE INDEX i1 ON t1(a);

403

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

404

SHOW CREATE TABLE t1;

405

DROP INDEX i1 ON t1;

406

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

407

SHOW CREATE TABLE t1;

408

DROP TABLE t1;

409

410

411

# Bug#24219 - ALTER TABLE ... RENAME TO ... , DISABLE KEYS leads to crash

412

413

--disable_warnings

414

DROP TABLE IF EXISTS bug24219;

415

DROP TABLE IF EXISTS bug24219_2;

416

--enable_warnings

417

418

CREATE TABLE bug24219 (a INT, INDEX(a));

419

420

SHOW INDEX FROM bug24219;

421

422

ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS;

423

424

SHOW INDEX FROM bug24219_2;

425

426

DROP TABLE bug24219_2;

427

428

429

# Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts)

430

431

432

--disable_warnings

433

drop table if exists table_24562;

434

--enable_warnings

435

436

create table table_24562(

437

section int,

438

subsection int,

439

title varchar(50));

440

441

insert into table_24562 values

442

(1, 0, "Introduction"),

443

(1, 1, "Authors"),

444

(1, 2, "Acknowledgements"),

445

(2, 0, "Basics"),

446

(2, 1, "Syntax"),

447

(2, 2, "Client"),

448

(2, 3, "Server"),

449

(3, 0, "Intermediate"),

450

(3, 1, "Complex queries"),

451

(3, 2, "Stored Procedures"),

452

(3, 3, "Stored Functions"),

453

(4, 0, "Advanced"),

454

(4, 1, "Replication"),

455

(4, 2, "Load balancing"),

456

(4, 3, "High availability"),

457

(5, 0, "Conclusion");

458

459

select * from table_24562;

460

461

alter table table_24562 add column reviewer varchar(20),

462

order by title;

463

464

select * from table_24562;

465

466

update table_24562 set reviewer="Me" where section=2;

467

update table_24562 set reviewer="You" where section=3;

468

469

alter table table_24562

470

order by section ASC, subsection DESC;

471

472

select * from table_24562;

473

474

alter table table_24562

475

order by table_24562.subsection ASC, table_24562.section DESC;

476

477

select * from table_24562;

478

479

--error ER_PARSE_ERROR

480

alter table table_24562 order by 12;

481

--error ER_PARSE_ERROR

482

alter table table_24562 order by (section + 12);

483

--error ER_PARSE_ERROR

484

alter table table_24562 order by length(title);

485

486

--error ER_BAD_FIELD_ERROR

487

alter table table_24562 order by no_such_col;

488

489

drop table table_24562;

490

491

# End of 4.1 tests

492

493

494

# Bug #14693 (ALTER SET DEFAULT doesn't work)

495

496

497

create table t1 (mycol int not null);

498

alter table t1 alter column mycol set default 0;

499

desc t1;

500

drop table t1;

501

502

503

# Bug#25262 Auto Increment lost when changing Engine type

504

505

506

create TEMPORARY table t1(id int primary key auto_increment) engine=MEMORY;

507

508

insert into t1 values (null);

509

insert into t1 values (null);

510

511

select * from t1;

512

513

# Set auto increment to 50

514

alter table t1 auto_increment = 50;

515

516

# Alter to myisam

517

alter table t1 engine = myisam;

518

519

# This insert should get id 50

520

insert into t1 values (null);

521

select * from t1;

522

523

# Alter to MEMORY again

524

alter table t1 engine = MEMORY;

525

insert into t1 values (null);

526

select * from t1;

527

528

drop table t1;

529

530

531

## Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the

532

## NO_ZERO_DATE mode.

533

534

#create table t1(f1 int);

535

#alter table t1 add column f2 datetime not null, add column f21 date not null;

536

#insert into t1 values(1,'2000-01-01','2000-01-01');

537

#--error ER_TRUNCATED_WRONG_VALUE

538

#alter table t1 add column f3 datetime not null;

539

#--error ER_TRUNCATED_WRONG_VALUE

540

#alter table t1 add column f3 date not null;

541

#--error ER_TRUNCATED_WRONG_VALUE

542

#alter table t1 add column f4 datetime not null default '2002-02-02',

543

# add column f41 date not null;

544

#alter table t1 add column f4 datetime not null default '2002-02-02',

545

# add column f41 date not null default '2002-02-02';

546

#select * from t1;

547

#drop table t1;

548

549

550

# Some additional tests for new, faster alter table. Note that most of the

551

# whole alter table code is being tested all around the test suite already.

552

553

554

create table t1 (v varchar(32));

555

insert into t1 values ('def'),('abc'),('hij'),('3r4f');

556

select * from t1;

557

# Fast alter, no copy performed

558

alter table t1 change v v2 varchar(32);

559

select * from t1;

560

# Fast alter, no copy performed

561

alter table t1 change v2 v varchar(64);

562

select * from t1;

563

update t1 set v = 'lmn' where v = 'hij';

564

select * from t1;

565

# Regular alter table

566

alter table t1 add i int auto_increment not null primary key first;

567

select * from t1;

568

update t1 set i=5 where i=3;

569

select * from t1;

570

alter table t1 change i i bigint;

571

select * from t1;

572

alter table t1 add unique key (i, v);

573

select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn');

574

drop table t1;

575

576

577

# Bug#6073 "ALTER table minor glich": ALTER TABLE complains that an index

578

# without # prefix is not allowed for TEXT columns, while index

579

# is defined with prefix.

580

581

create TEMPORARY table t1 (t varchar(255) default null, key t (t(80))) engine=myisam;

582

alter table t1 change t t text;

583

drop table t1;

584

585

586

# Bug#18038 MySQL server corrupts binary columns data

587

588

589

CREATE TABLE t1 (s CHAR(8));

590

INSERT INTO t1 VALUES ('test');

591

SELECT LENGTH(s) FROM t1;

592

ALTER TABLE t1 MODIFY s CHAR(10);

593

SELECT LENGTH(s) FROM t1;

594

DROP TABLE t1;

595

596

CREATE TABLE t1 (s varbinary(8));

597

INSERT INTO t1 VALUES ('test');

598

SELECT LENGTH(s) FROM t1;

599

SELECT HEX(s) FROM t1;

600

ALTER TABLE t1 MODIFY s varbinary(10);

601

SELECT HEX(s) FROM t1;

602

SELECT LENGTH(s) FROM t1;

603

DROP TABLE t1;

604

605

606

# Bug#19386: Multiple alter causes crashed table

607

# The trailing column would get corrupted data, or server could not even read

608

# it.

609

610

611

CREATE TABLE t1 (v VARCHAR(3), b INT);

612

INSERT INTO t1 VALUES ('abc', 5);

613

SELECT * FROM t1;

614

ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4);

615

SELECT * FROM t1;

616

DROP TABLE t1;

617

618

--echo End of 5.0 tests

619

620

621

# Bug#18775 - Temporary table from alter table visible to other threads

622

623

# Check if special characters work and duplicates are detected.

624

--disable_warnings

625

DROP TABLE IF EXISTS `t+1`, `t+2`;

626

--enable_warnings

627

CREATE TABLE `t+1` (c1 INT);

628

ALTER TABLE `t+1` RENAME `t+2`;

629

CREATE TABLE `t+1` (c1 INT);

630

--error ER_TABLE_EXISTS_ERROR

631

ALTER TABLE `t+1` RENAME `t+2`;

632

DROP TABLE `t+1`, `t+2`;

633

634

# Same for temporary tables though these names do not become file names.

635

CREATE TEMPORARY TABLE `tt+1` (c1 INT);

636

ALTER TABLE `tt+1` RENAME `tt+2`;

637

CREATE TEMPORARY TABLE `tt+1` (c1 INT);

638

--error ER_TABLE_EXISTS_ERROR

639

ALTER TABLE `tt+1` RENAME `tt+2`;

640

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

641

SHOW CREATE TABLE `tt+1`;

642

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

643

SHOW CREATE TABLE `tt+2`;

644

DROP TABLE `tt+1`, `tt+2`;

645

646

## Check if special characters as in tmp_file_prefix work.

647

#CREATE TABLE `#sql1` (c1 INT);

648

#CREATE TABLE `@0023sql2` (c1 INT);

649

#SHOW TABLES;

650

#RENAME TABLE `#sql1` TO `@0023sql1`;

651

#RENAME TABLE `@0023sql2` TO `#sql2`;

652

#SHOW TABLES;

653

#ALTER TABLE `@0023sql1` RENAME `#sql-1`;

654

#ALTER TABLE `#sql2` RENAME `@0023sql-2`;

655

#SHOW TABLES;

656

#INSERT INTO `#sql-1` VALUES (1);

657

#INSERT INTO `@0023sql-2` VALUES (2);

658

#DROP TABLE `#sql-1`, `@0023sql-2`;

659

660

# Same for temporary tables though these names do not become file names.

661

CREATE TEMPORARY TABLE `#sql1` (c1 INT);

662

CREATE TEMPORARY TABLE `@0023sql2` (c1 INT);

663

SHOW TABLES;

664

ALTER TABLE `#sql1` RENAME `@0023sql1`;

665

ALTER TABLE `@0023sql2` RENAME `#sql2`;

666

SHOW TABLES;

667

INSERT INTO `#sql2` VALUES (1);

668

INSERT INTO `@0023sql1` VALUES (2);

669

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

670

SHOW CREATE TABLE `#sql2`;

671

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

672

SHOW CREATE TABLE `@0023sql1`;

673

DROP TABLE `#sql2`, `@0023sql1`;

674

675

676

677

# Bug #22369: Alter table rename combined with other alterations causes lost tables

678

679

# This problem happens if the data change is compatible.

680

# Changing to the same type is compatible for example.

681

682

--disable_warnings

683

DROP TABLE IF EXISTS t1;

684

DROP TABLE IF EXISTS t2;

685

--enable_warnings

686

CREATE TABLE t1 (

687

int_field INTEGER NOT NULL,

688

char_field CHAR(10),

689

INDEX(`int_field`)

690

);

691

692

DESCRIBE t1;

693

694

SHOW INDEXES FROM t1;

695

696

INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet");

697

--echo "Non-copy data change - new frm, but old data and index files"

698

ALTER TABLE t1 CHANGE int_field unsigned_int_field INTEGER NOT NULL, RENAME t2;

699

700

--error ER_TABLE_UNKNOWN

701

SELECT * FROM t1 ORDER BY int_field;

702

SELECT * FROM t2 ORDER BY unsigned_int_field;

703

DESCRIBE t2;

704

DESCRIBE t2;

705

ALTER TABLE t2 MODIFY unsigned_int_field BIGINT NOT NULL;

706

DESCRIBE t2;

707

708

DROP TABLE t2;

709

710

711

# Bug#28427: Columns were renamed instead of moving by ALTER TABLE.

712

713

CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);

714

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

715

SELECT * FROM t1;

716

ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1;

717

SELECT * FROM t1;

718

ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2;

719

SELECT * FROM t1;

720

DROP TABLE t1;

Older »