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

211

insert into t1 (a) values(1);

212

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

213

show table status like 't1';

214

drop table t1;

215

216

217

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

218

219

220

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

221

ALTER TABLE t1 DROP PRIMARY KEY;

222

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

223

SHOW CREATE TABLE t1;

224

--error ER_CANT_DROP_FIELD_OR_KEY

225

ALTER TABLE t1 DROP PRIMARY KEY;

226

DROP TABLE t1;

227

228

# BUG#3899

229

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

230

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

231

--error ER_CANT_DROP_FIELD_OR_KEY

232

alter table t1 drop key no_such_key;

233

alter table t1 drop key a;

234

drop table t1;

235

236

237

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

238

239

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

240

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

241

--replace_result t12207 T12207

242

--error ER_ILLEGAL_HA

243

ALTER TABLE T12207 DISCARD TABLESPACE;

244

DROP TABLE T12207;

245

246

247

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

248

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

249

# geometry field and allow to create prefix index which is

250

# shorter than packed field length.

251

252

create table t1 ( a timestamp );

253

--error ER_WRONG_SUB_KEY

254

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

255

drop table t1;

256

257

258

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

259

260

# This problem happens if the data change is compatible.

261

# Changing to the same type is compatible for example.

262

263

--disable_warnings

264

drop table if exists t1;

265

--enable_warnings

266

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

267

show indexes from t1;

268

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

269

alter table t1 modify a int;

270

show indexes from t1;

271

272

alter table t1 enable keys;

273

show indexes from t1;

274

275

alter table t1 modify a bigint;

276

show indexes from t1;

277

278

alter table t1 enable keys;

279

show indexes from t1;

280

281

alter table t1 add b char(10);

282

show indexes from t1;

283

284

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

285

show indexes from t1;

286

287

--echo "this however did"

288

alter table t1;

289

show indexes from t1;

290

291

desc t1;

292

293

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

294

--echo "The key should still be disabled"

295

show indexes from t1;

296

297

drop table t1;

298

299

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

300

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

301

show indexes from t1;

302

alter table t1;

303

show indexes from t1;

304

alter table t1 enable keys;

305

306

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

307

--echo "Unique index, no change"

308

alter table t1 modify a int;

309

show indexes from t1;

310

311

--echo "Change the type implying data copy"

312

--echo "Unique index, no change"

313

alter table t1 modify a bigint;

314

show indexes from t1;

315

316

alter table t1 modify a bigint;

317

show indexes from t1;

318

319

alter table t1 modify a int;

320

show indexes from t1;

321

322

drop table t1;

323

324

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

325

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

326

show indexes from t1;

327

alter table t1;

328

show indexes from t1;

329

alter table t1 enable keys;

330

331

332

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

333

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

334

alter table t1 modify a int;

335

show indexes from t1;

336

alter table t1 enable keys;

337

show indexes from t1;

338

339

--echo "Change the type implying data copy"

340

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

341

alter table t1 modify a bigint;

342

show indexes from t1;

343

344

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

345

alter table t1 modify a int;

346

show indexes from t1;

347

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

348

alter table t1 modify a int;

349

show indexes from t1;

350

351

drop table t1;

352

353

354

355

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

356

# db name qualifying

357

358

create database mysqltest;

359

create table t1 (c1 int);

360

# Move table to other database.

361

alter table t1 rename mysqltest.t1;

362

# Assure that it has moved.

363

--error ER_BAD_TABLE_ERROR

364

drop table t1;

365

# Move table back.

366

alter table mysqltest.t1 rename t1;

367

# Assure that it is back.

368

drop table t1;

369

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

370

# Create t1 in 'test'.

371

create table t1 (c1 int);

372

# Change to other db.

373

use mysqltest;

374

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

375

drop database mysqltest;

376

# Now test for correct message.

377

--error ER_NO_DB_ERROR

378

alter table test.t1 rename t1;

379

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

380

alter table test.t1 rename test.t1;

381

# Go back to standard 'test' db.

382

use test;

383

drop table t1;

384

385

386

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

387

# table

388

389

CREATE TABLE t1(a INT) ROW_FORMAT=COMPACT;

390

CREATE INDEX i1 ON t1(a);

391

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

392

SHOW CREATE TABLE t1;

393

DROP INDEX i1 ON t1;

394

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

395

SHOW CREATE TABLE t1;

396

DROP TABLE t1;

397

398

399

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

400

401

--disable_warnings

402

DROP TABLE IF EXISTS bug24219;

403

DROP TABLE IF EXISTS bug24219_2;

404

--enable_warnings

405

406

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

407

408

SHOW INDEX FROM bug24219;

409

410

ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS;

411

412

SHOW INDEX FROM bug24219_2;

413

414

DROP TABLE bug24219_2;

415

416

417

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

418

419

420

--disable_warnings

421

drop table if exists table_24562;

422

--enable_warnings

423

424

create table table_24562(

425

section int,

426

subsection int,

427

title varchar(50));

428

429

insert into table_24562 values

430

(1, 0, "Introduction"),

431

(1, 1, "Authors"),

432

(1, 2, "Acknowledgements"),

433

(2, 0, "Basics"),

434

(2, 1, "Syntax"),

435

(2, 2, "Client"),

436

(2, 3, "Server"),

437

(3, 0, "Intermediate"),

438

(3, 1, "Complex queries"),

439

(3, 2, "Stored Procedures"),

440

(3, 3, "Stored Functions"),

441

(4, 0, "Advanced"),

442

(4, 1, "Replication"),

443

(4, 2, "Load balancing"),

444

(4, 3, "High availability"),

445

(5, 0, "Conclusion");

446

447

select * from table_24562;

448

449

alter table table_24562 add column reviewer varchar(20),

450

order by title;

451

452

select * from table_24562;

453

454

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

455

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

456

457

alter table table_24562

458

order by section ASC, subsection DESC;

459

460

select * from table_24562;

461

462

alter table table_24562

463

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

464

465

select * from table_24562;

466

467

--error ER_PARSE_ERROR

468

alter table table_24562 order by 12;

469

--error ER_PARSE_ERROR

470

alter table table_24562 order by (section + 12);

471

--error ER_PARSE_ERROR

472

alter table table_24562 order by length(title);

473

474

--error ER_BAD_FIELD_ERROR

475

alter table table_24562 order by no_such_col;

476

477

drop table table_24562;

478

479

# End of 4.1 tests

480

481

482

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

483

484

485

create table t1 (mycol int not null);

486

alter table t1 alter column mycol set default 0;

487

desc t1;

488

drop table t1;

489

490

491

# Bug#25262 Auto Increment lost when changing Engine type

492

493

494

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

495

496

insert into t1 values (null);

497

insert into t1 values (null);

498

499

select * from t1;

500

501

# Set auto increment to 50

502

alter table t1 auto_increment = 50;

503

504

# Alter to myisam

505

alter table t1 engine = myisam;

506

507

# This insert should get id 50

508

insert into t1 values (null);

509

select * from t1;

510

511

# Alter to MEMORY again

512

alter table t1 engine = MEMORY;

513

insert into t1 values (null);

514

select * from t1;

515

516

drop table t1;

517

518

519

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

520

## NO_ZERO_DATE mode.

521

522

#create table t1(f1 int);

523

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

524

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

525

#--error ER_TRUNCATED_WRONG_VALUE

526

#alter table t1 add column f3 datetime not null;

527

#--error ER_TRUNCATED_WRONG_VALUE

528

#alter table t1 add column f3 date not null;

529

#--error ER_TRUNCATED_WRONG_VALUE

530

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

531

# add column f41 date not null;

532

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

533

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

534

#select * from t1;

535

#drop table t1;

536

537

538

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

539

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

540

541

542

create table t1 (v varchar(32));

543

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

544

select * from t1;

545

# Fast alter, no copy performed

546

alter table t1 change v v2 varchar(32);

547

select * from t1;

548

# Fast alter, no copy performed

549

alter table t1 change v2 v varchar(64);

550

select * from t1;

551

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

552

select * from t1;

553

# Regular alter table

554

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

555

select * from t1;

556

update t1 set i=5 where i=3;

557

select * from t1;

558

alter table t1 change i i bigint;

559

select * from t1;

560

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

561

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

562

drop table t1;

563

564

565

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

566

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

567

# is defined with prefix.

568

569

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

570

alter table t1 change t t text;

571

drop table t1;

572

573

574

# Bug#18038 MySQL server corrupts binary columns data

575

576

577

CREATE TABLE t1 (s CHAR(8));

578

INSERT INTO t1 VALUES ('test');

579

SELECT LENGTH(s) FROM t1;

580

ALTER TABLE t1 MODIFY s CHAR(10);

581

SELECT LENGTH(s) FROM t1;

582

DROP TABLE t1;

583

584

CREATE TABLE t1 (s varbinary(8));

585

INSERT INTO t1 VALUES ('test');

586

SELECT LENGTH(s) FROM t1;

587

SELECT HEX(s) FROM t1;

588

ALTER TABLE t1 MODIFY s varbinary(10);

589

SELECT HEX(s) FROM t1;

590

SELECT LENGTH(s) FROM t1;

591

DROP TABLE t1;

592

593

594

# Bug#19386: Multiple alter causes crashed table

595

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

596

# it.

597

598

599

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

600

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

601

SELECT * FROM t1;

602

ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4);

603

SELECT * FROM t1;

604

DROP TABLE t1;

605

606

--echo End of 5.0 tests

607

608

609

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

610

611

# Check if special characters work and duplicates are detected.

612

--disable_warnings

613

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

614

--enable_warnings

615

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

616

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

617

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

618

--error ER_TABLE_EXISTS_ERROR

619

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

620

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

621

622

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

623

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

624

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

625

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

626

--error ER_TABLE_EXISTS_ERROR

627

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

628

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

629

SHOW CREATE TABLE `tt+1`;

630

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

631

SHOW CREATE TABLE `tt+2`;

632

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

633

634

## Check if special characters as in tmp_file_prefix work.

635

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

636

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

637

#SHOW TABLES;

638

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

639

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

640

#SHOW TABLES;

641

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

642

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

643

#SHOW TABLES;

644

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

645

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

646

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

647

648

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

649

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

650

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

651

SHOW TABLES;

652

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

653

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

654

SHOW TABLES;

655

INSERT INTO `#sql2` VALUES (1);

656

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

657

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

658

SHOW CREATE TABLE `#sql2`;

659

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

660

SHOW CREATE TABLE `@0023sql1`;

661

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

662

663

664

665

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

666

667

# This problem happens if the data change is compatible.

668

# Changing to the same type is compatible for example.

669

670

--disable_warnings

671

DROP TABLE IF EXISTS t1;

672

DROP TABLE IF EXISTS t2;

673

--enable_warnings

674

CREATE TABLE t1 (

675

int_field INTEGER NOT NULL,

676

char_field CHAR(10),

677

INDEX(`int_field`)

678

);

679

680

DESCRIBE t1;

681

682

SHOW INDEXES FROM t1;

683

684

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

685

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

686

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

687

688

--error ER_TABLE_UNKNOWN

689

SELECT * FROM t1 ORDER BY int_field;

690

SELECT * FROM t2 ORDER BY unsigned_int_field;

691

DESCRIBE t2;

692

DESCRIBE t2;

693

ALTER TABLE t2 MODIFY unsigned_int_field BIGINT NOT NULL;

694

DESCRIBE t2;

695

696

DROP TABLE t2;

697

698

699

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

700

701

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

702

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

703

SELECT * FROM t1;

704

ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1;

705

SELECT * FROM t1;

706

ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2;

707

SELECT * FROM t1;

708

DROP TABLE t1;

Older »