~drizzle-trunk/drizzle/development

create table t1 (a int, b int, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp, l datetime, m enum('a','b'), n set('a','b'), o char(10));

370

create table t2 select ifnull(a,a), ifnull(b,b), ifnull(c,c), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(i,i), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(n,n), ifnull(o,o) from t1;

372

create table t1 (a int, b int, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), j date, k timestamp, l datetime, m enum('a','b'), o char(10));

373

create table t2 select ifnull(a,a), ifnull(b,b), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(o,o) from t1;

371

374

show create table t2;

372

375

drop table t1,t2;

373

376

395

398

# test for bug #1427 "enum allows duplicate values in the list"

396

399

397

400

398

create table t1(cenum enum('a'), cset set('b'));

399

create table t2(cenum enum('a','a'), cset set('b','b'));

400

create table t3(cenum enum('a','A','a','c','c'), cset set('b','B','b','d','d'));

401

drop table t1, t2, t3;

401

create table t1(cenum enum('a'));

402

--error 1291

403

create table t2(cenum enum('a','a'));

404

--error 1291

405

create table t3(cenum enum('a','A','a','c','c'));

406

drop table t1;

402

407

403

408

404

409

# Bug #1209

409

414

select database();

410

415

drop database mysqltest;

411

416

select database();

412

413

# Connect without a database as user mysqltest_1

414

create user mysqltest_1;

415

connect (user1,localhost,mysqltest_1,,*NO-ONE*);

416

connection user1;

417

select database(), user();

418

connection default;

419

disconnect user1;

420

drop user mysqltest_1;

421

417

use test;

422

418

423

419

424

420

# Test for Bug 856 'Naming a key "Primary" causes trouble'

425

421

426

422

427

--error 1280

428

create table t1 (a int, index `primary` (a));

429

--error 1280

430

create table t1 (a int, index `PRIMARY` (a));

431

432

create table t1 (`primary` int, index(`primary`));

433

show create table t1;

434

create table t2 (`PRIMARY` int, index(`PRIMARY`));

435

show create table t2;

436

437

create table t3 (a int);

438

--error 1280

439

alter table t3 add index `primary` (a);

440

--error 1280

441

alter table t3 add index `PRIMARY` (a);

442

443

create table t4 (`primary` int);

444

alter table t4 add index(`primary`);

445

show create table t4;

446

create table t5 (`PRIMARY` int);

447

alter table t5 add index(`PRIMARY`);

448

show create table t5;

449

450

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

423

## TODO: Is this really a bug? It works in Drizzle. Should it?

424

#--error 1280

425

#create table t1 (a int, index `primary` (a));

426

#--error 1280

427

#create table t1 (a int, index `PRIMARY` (a));

428

429

#create table t1 (`primary` int, index(`primary`));

430

#show create table t1;

431

#create table t2 (`PRIMARY` int, index(`PRIMARY`));

432

#show create table t2;

433

434

#create table t3 (a int);

435

#--error 1280

436

#alter table t3 add index `primary` (a);

437

#--error 1280

438

#alter table t3 add index `PRIMARY` (a);

439

440

#create table t4 (`primary` int);

441

#alter table t4 add index(`primary`);

442

#show create table t4;

443

#create table t5 (`PRIMARY` int);

444

#alter table t5 add index(`PRIMARY`);

445

#show create table t5;

446

447

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

451

448

452

449

453

450

# bug #3266 TEXT in CREATE TABLE SELECT

463

460

SELECT * FROM t3;

464

461

drop table t1, t2, t3;

465

462

466

467

# Bug#9666: Can't use 'DEFAULT FALSE' for column of type bool

468

469

create table t1 (b bool not null default false);

470

create table t2 (b bool not null default true);

471

insert into t1 values ();

472

insert into t2 values ();

473

select * from t1;

474

select * from t2;

475

drop table t1,t2;

476

463

477

464

478

465

# Bug#10224 - ANALYZE TABLE crashing with simultaneous

483

470

create table t1 (a int);

484

471

--error 1093

485

472

create table t1 select * from t1;

486

--error ER_WRONG_OBJECT

487

create table t2 union = (t1) select * from t1;

473

## TODO: Huh? --error ER_WRONG_OBJECT

474

#create table t2 union = (t1) select * from t1;

488

475

flush tables with read lock;

489

476

unlock tables;

490

477

drop table t1;

505

492

506

493

# Bug #12537: UNION produces longtext instead of varchar

507

494

508

CREATE TABLE t1 (f1 VARCHAR(255) CHARACTER SET utf8);

495

CREATE TABLE t1 (f1 VARCHAR(255));

509

496

CREATE TABLE t2 AS SELECT LEFT(f1,171) AS f2 FROM t1 UNION SELECT LEFT(f1,171) AS f2 FROM t1;

510

497

DESC t2;

511

498

DROP TABLE t1,t2;

529

516

drop table if exists test.t1;

530

517

--enable_warnings

531

518

532

533

# Bug #6859: Bogus error message on attempt to CREATE TABLE t LIKE view

534

535

create database mysqltest;

536

use mysqltest;

537

create view v1 as select 'foo' from dual;

538

--error 1347

539

create table t1 like v1;

540

drop view v1;

541

drop database mysqltest;

542

519

# Bug #6008 MySQL does not create warnings when

543

520

# creating database and using IF NOT EXISTS

544

521

545

522

create database mysqltest;

546

create database if not exists mysqltest character set latin2;

523

create database if not exists mysqltest;

547

524

show create database mysqltest;

548

525

drop database mysqltest;

549

526

use test;

553

530

554

531

# BUG#14139

555

532

create table t1 (

556

a varchar(112) charset utf8 collate utf8_bin not null,

533

a varchar(112) collate utf8_bin not null,

557

534

primary key (a)

558

535

) select 'test' as a ;

559

536

#--warning 1364

565

542

# calculation of number of NULLs.

566

543

567

544

CREATE TABLE t2 (

568

a int(11) default NULL

545

a int default NULL

569

546

);

570

547

insert into t2 values(111);

571

548

572

549

#--warning 1364

573

550

create table t1 (

574

a varchar(12) charset utf8 collate utf8_bin not null,

551

a varchar(12) collate utf8_bin not null,

575

552

b int not null, primary key (a)

576

553

) select a, 1 as b from t2 ;

577

554

show create table t1;

578

555

drop table t1;

579

556

580

#--warning 1364

557

--error 1364

581

558

create table t1 (

582

a varchar(12) charset utf8 collate utf8_bin not null,

559

a varchar(12) collate utf8_bin not null,

583

560

b int not null, primary key (a)

584

561

) select a, 1 as c from t2 ;

585

show create table t1;

586

drop table t1;

587

562

588

#--warning 1364

589

563

create table t1 (

590

a varchar(12) charset utf8 collate utf8_bin not null,

564

a varchar(12) collate utf8_bin not null,

591

565

b int null, primary key (a)

592

566

) select a, 1 as c from t2 ;

593

567

show create table t1;

594

568

drop table t1;

595

569

596

#--warning 1364

597

570

create table t1 (

598

a varchar(12) charset utf8 collate utf8_bin not null,

571

a varchar(12) collate utf8_bin not null,

599

572

b int not null, primary key (a)

600

573

) select 'a' as a , 1 as b from t2 ;

601

574

show create table t1;

602

575

drop table t1;

603

576

604

#--warning 1364

605

577

create table t1 (

606

a varchar(12) charset utf8 collate utf8_bin,

578

a varchar(12) collate utf8_bin,

607

579

b int not null, primary key (a)

608

580

) select 'a' as a , 1 as b from t2 ;

609

581

show create table t1;

617

589

618

590

#--warning 1364

619

591

create table t2 (

620

a1 varchar(12) charset utf8 collate utf8_bin not null,

592

a1 varchar(12) collate utf8_bin not null,

621

593

a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int,

622

594

primary key (a1)

623

595

) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ;

625

597

626

598

#--warning 1364

627

599

create table t2 (

628

a1 varchar(12) charset utf8 collate utf8_bin,

600

a1 varchar(12) collate utf8_bin,

629

601

a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int

630

602

) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1;

631

603

638

610

639

611

#--warning 1364

640

612

create table t2 (

641

a1 varchar(12) charset utf8 collate utf8_bin not null,

613

a1 varchar(12) collate utf8_bin not null,

642

614

a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int,

643

615

primary key (a1)

644

616

) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ;

652

624

653

625

drop table t1, t2;

654

626

655

656

# Bug #15316 SET value having comma not correctly handled

657

658

--error 1367

659

create table t1(a set("a,b","c,d") not null);

660

661

# End of 4.1 tests

662

663

627

664

628

665

629

# Bug #14155: Maximum value of MAX_ROWS handled incorrectly on 64-bit

688

652

create table t1 select * from t1;

689

653

# Error which happens before select_create::prepare()

690

654

--error ER_CANT_AGGREGATE_2COLLATIONS

691

create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);

655

create table t1 select coalesce('a' collate utf8_swedish_ci,'b' collate utf8_bin);

692

656

# Error during table creation

693

657

--error ER_KEY_COLUMN_DOES_NOT_EXITS

694

658

create table t1 (primary key(a)) select "b" as b;

695

659

# Error in select_create::prepare() which is not related to table creation

696

create table t1 (a int);

697

--error ER_WRONG_VALUE_COUNT_ON_ROW

698

create table if not exists t1 select 1 as a, 2 as b;

699

drop table t1;

660

# TODO: This really should be failing...

661

# create table t1 (a int);

662

# --error ER_WRONG_VALUE_COUNT_ON_ROW

663

# create table if not exists t1 select 1 as a, 2 as b;

664

# drop table t1;

700

665

# Finally error which happens during insert

701

666

--error ER_DUP_ENTRY

702

667

create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a);

703

668

# What happens if table already exists ?

704

669

create table t1 (i int);

705

--error ER_TABLE_EXISTS_ERROR

706

create table t1 select 1 as i;

670

# TODO: BUG lp:311045

671

#--error ER_TABLE_EXISTS_ERROR

672

#create table t1 select 1 as i;

707

673

create table if not exists t1 select 1 as i;

708

674

select * from t1;

675

drop table t1;

709

676

# Error before select_create::prepare()

710

677

--error ER_CANT_AGGREGATE_2COLLATIONS

711

create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);

712

select * from t1;

678

create table t1 select coalesce('a' collate utf8_swedish_ci,'b' collate utf8_bin);

713

679

# Error which happens during insertion of rows

714

alter table t1 add primary key (i);

715

--error ER_DUP_ENTRY

716

create table if not exists t1 (select 2 as i) union all (select 2 as i);

717

select * from t1;

718

drop table t1;

680

# TODO: Bug lp:311072

681

# create table t1 (i int);

682

# alter table t1 add primary key (i);

683

# --error ER_DUP_ENTRY

684

# create table if not exists t1 (select 2 as i) union all (select 2 as i);

685

# select * from t1;

686

# drop table t1;

719

687

720

688

721

689

# Base vs temporary tables dillema (a.k.a. bug#24508 "Inconsistent

1192

1160

CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;

1193

1161

# This should give warning

1194

1162

drop table if exists t2;

1195

CREATE TABLE t2 (a int, b int, primary key (a));

1196

--error ER_DUP_ENTRY

1197

CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;

1198

SELECT * from t2;

1199

TRUNCATE table t2;

1200

--error ER_DUP_ENTRY

1201

INSERT INTO t2 select * from t1;

1202

SELECT * from t2;

1203

drop table t2;

1163

# TODO: Bug lp:311072

1164

#CREATE TABLE t2 (a int, b int, primary key (a));

1165

#--error ER_DUP_ENTRY

1166

#CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;

1167

#SELECT * from t2;

1168

#TRUNCATE table t2;

1169

#--error ER_DUP_ENTRY

1170

#INSERT INTO t2 select * from t1;

1171

#SELECT * from t2;

1172

#drop table t2;

1204

1173

1205

1174

CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));

1206

1175

--error ER_DUP_ENTRY

1240

1209

1241

1210

# Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte

1242

1211

1243

set names utf8;

1244

1212

1245

1213

create database имя_базы_в_кодировке_утф8_длиной_больше_чем_45;

1246

1214

use имя_базы_в_кодировке_утф8_длиной_больше_чем_45;

1257

1225

index имя_индекса_в_кодировке_утф8_длиной_больше_чем_48 (имя_поля_в_кодировке_утф8_длиной_больше_чем_45)

1258

1226

);

1259

1227

1260

create view имя_вью_кодировке_утф8_длиной_больше_чем_42 as

1261

select имя_поля_в_кодировке_утф8_длиной_больше_чем_45

1262

from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;

1263

1228

1264

# database, table, field, key, view

1229

# database, table, field, key

1265

1230

select * from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;

1266

1231

1267

1232

select TABLE_NAME from information_schema.tables where

1273

1238

select INDEX_NAME from information_schema.statistics where

1274

1239

table_schema='test';

1275

1240

1276

select TABLE_NAME from information_schema.views where

1277

table_schema='test';

1278

1279

1241

show create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;

1280

show create view имя_вью_кодировке_утф8_длиной_больше_чем_42;

1281

1282

# procedure, function, trigger

1283

1284

create trigger имя_триггера_в_кодировке_утф8_длиной_больше_чем_49

1285

before insert on имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 for each row set @a:=1;

1286

select TRIGGER_NAME from information_schema.triggers where

1287

trigger_schema='test';

1288

drop trigger имя_триггера_в_кодировке_утф8_длиной_больше_чем_49;

1289

--error 1059

1290

create trigger

1291

очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66

1292

before insert on имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 for each row set @a:=1;

1293

--error 1059

1294

drop trigger очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66;

1295

1296

create procedure имя_процедуры_в_кодировке_утф8_длиной_больше_чем_50()

1297

begin

1298

end;

1299

select ROUTINE_NAME from information_schema.routines where

1300

routine_schema='test';

1301

drop procedure имя_процедуры_в_кодировке_утф8_длиной_больше_чем_50;

1302

--error 1059

1303

create procedure очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66()

1304

begin

1305

end;

1306

1307

create function имя_функции_в_кодировке_утф8_длиной_больше_чем_49()

1308

returns int

1309

return 0;

1310

select ROUTINE_NAME from information_schema.routines where

1311

routine_schema='test';

1312

drop function имя_функции_в_кодировке_утф8_длиной_больше_чем_49;

1313

--error 1059

1314

create function очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66()

1315

returns int

1316

return 0;

1317

1318

drop view имя_вью_кодировке_утф8_длиной_больше_чем_42;

1242

1319

1243

drop table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;

1320

set names default;

1321

1322

1323

# Bug#21136 CREATE TABLE SELECT within CREATE TABLE SELECT causes server crash

1324

1325

1326

--disable_warnings

1327

drop table if exists t1,t2,t3;

1328

drop function if exists f1;

1329

--enable_warnings

1330

1331

--delimiter |

1332

create function f1() returns int

1333

begin

1334

declare res int;

1335

create temporary table t3 select 1 i;

1336

set res:= (select count(*) from t1);

1337

drop temporary table t3;

1338

return res;

1339

end|

1340

--delimiter ;

1341

create table t1 as select 1;

1342

create table t2 as select f1() from t1;

1343

drop table t1,t2;

1344

drop function f1;

1244

1345

1245

1346

1246

1347

1247

# Bug#25629 CREATE TABLE LIKE does not work with INFORMATION_SCHEMA

1352

1252

create temporary table t1 like information_schema.processlist;

1353

1253

show create table t1;

1354

1254

drop table t1;

1355

create table t1 like information_schema.character_sets;

1356

show create table t1;

1357

drop table t1;

1358

1255

1359

1256

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

1360

1257

1377

1274

c1 INT DEFAULT 12 COMMENT 'column1',

1378

1275

c2 INT NULL COMMENT 'column2',

1379

1276

c3 INT NOT NULL COMMENT 'column3',

1380

c4 VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a',

1277

c4 VARCHAR(255) NOT NULL DEFAULT 'a',

1381

1278

c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b',

1382

1279

c6 VARCHAR(255))

1383

COLLATE latin1_bin;

1280

COLLATE utf8_bin;

1384

1281

1385

1282

--echo

1386

1283

1421

1318

1422

1319

--echo

1423

1320

1424

SET sql_mode = NO_ZERO_DATE;

1425

1426

--echo

1427

--error ER_INVALID_DEFAULT

1428

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0);

1429

1430

--echo

1431

--error ER_INVALID_DEFAULT

1321

--echo

1322

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP NULL);

1323

drop table t2;

1324

1325

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT '1982-01-29');

1326

drop table t2;

1327

1328

--echo

1432

1329

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);

1330

drop table t2;

1433

1331

1434

1332

--echo

1435

1333

--echo # -- Check that NULL column still can be created.

1437

1335

1438

1336

--echo

1439

1337

--echo # -- Check ALTER TABLE.

1440

--error ER_INVALID_DEFAULT

1441

1338

ALTER TABLE t1 ADD INDEX(c1);

1442

1339

1443

1340

--echo

1444

1341

--echo # -- Check DATETIME.

1445

SET sql_mode = '';

1446

1447

1342

--echo

1448

1343

1449

1344

CREATE TABLE t3(c1 DATETIME NOT NULL);

1345

--error 1686 # Bad datetime

1450

1346

INSERT INTO t3 VALUES (0);

1451

1347

1452

1348

--echo

1453

SET sql_mode = TRADITIONAL;

1454

1455

--echo

1456

--error ER_TRUNCATED_WRONG_VALUE

1457

1349

ALTER TABLE t3 ADD INDEX(c1);

1458

1350

1459

1351

--echo

1460

1352

--echo # -- Cleanup.

1461

1353

1462

SET sql_mode = '';

1463

1354

DROP TABLE t1;

1464

1355

DROP TABLE t2;

1465

1356

DROP TABLE t3;

1467

1358

--echo

1468

1359

--echo # -- End of Bug#18834.

1469

1360

1470

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

1471

1472

--echo

1473

--echo # --

1474

--echo # -- Bug#34274: Invalid handling of 'DEFAULT 0' for YEAR data type.

1475

--echo # --

1476

--echo

1477

1478

--disable_warnings

1479

DROP TABLE IF EXISTS t1;

1480

--enable_warnings

1481

1482

--echo

1483

CREATE TABLE t1(c1 YEAR DEFAULT 2008, c2 YEAR DEFAULT 0);

1484

1485

--echo

1486

SHOW CREATE TABLE t1;

1487

1488

--echo

1489

INSERT INTO t1 VALUES();

1490

1491

--echo

1492

SELECT * FROM t1;

1493

1494

--echo

1495

ALTER TABLE t1 MODIFY c1 YEAR DEFAULT 0;

1496

1497

--echo

1498

SHOW CREATE TABLE t1;

1499

1500

--echo

1501

INSERT INTO t1 VALUES();

1502

1503

--echo

1504

SELECT * FROM t1;

1505

1506

--echo

1507

DROP TABLE t1;

1508

1509

--echo

1510

--echo # -- End of Bug#34274

1511

1512

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

1513

1514

--echo

1515

--echo End of 5.1 tests

Older »