~drizzle-trunk/drizzle/development : revision 751

12

Warnings:

13

Note 1050 Table 't1' already exists

14

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

15

Warnings:

16

Warning 1048 Column 'b' cannot be null

15

ERROR 23000: Column 'b' cannot be null

17

16

select * from t1;

18

17

b

19

20

21

18

drop table t1;

22

19

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

23

20

drop table t1;

36

33

drop table if exists t1;

37

34

Warnings:

38

35

Note 1051 Unknown table 't1'

39

create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) engine=heap;

36

create table t1 (ordid int not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) engine=heap;

40

37

ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key

41

38

create table not_existing_database.test (a int);

42

39

ERROR 42000: Unknown database 'not_existing_database'

44

41

show create table `a/a`;

45

42

Table Create Table

46

43

a/a CREATE TABLE `a/a` (

47

`a` int(11) DEFAULT NULL

48

) ENGINE=MyISAM DEFAULT CHARSET=latin1

44

`a` int

45

) ENGINE=InnoDB

49

46

create table t1 like `a/a`;

50

47

drop table `a/a`;

51

48

drop table `t1`;

59

56

ERROR HY000: Invalid ON UPDATE clause for 'a' column

60

57

create table t1 (a int default 100 auto_increment);

61

58

ERROR 42000: Invalid default value for 'a'

62

create table t1 (a tinyint default 1000);

63

ERROR 42000: Invalid default value for 'a'

64

59

create table t1 (a varchar(5) default 'abcdef');

65

60

ERROR 42000: Invalid default value for 'a'

66

61

create table t1 (a varchar(5) default 'abcde');

104

99

create table t2 (key (b)) select * from t1;

105

100

explain select * from t2 where b="world";

106

101

id select_type table type possible_keys key key_len ref rows Extra

107

1 SIMPLE t2 ref B B 21 const 1 Using index condition

102

1 SIMPLE t2 ref B B 83 const 1 Using where

108

103

select * from t2 where b="world";

109

104

a B

110

105

3 world

121

116

create table t2 select now() as a , curtime() as b, curdate() as c , 1+1 as d , 1.0 + 1 as e , 33333333333333333 + 3 as f;

122

117

describe t2;

123

118

Field Type Null Key Default Extra

124

a datetime NO 0000-00-00 00:00:00

125

b time NO 00:00:00

126

c date NO 0000-00-00

127

d int(3) NO 0

128

e decimal(3,1) NO 0.0

129

f bigint(19) NO 0

119

a datetime YES NULL

120

b time YES NULL

121

c date NO NULL

122

d int NO NULL

123

e decimal(3,1) NO NULL

124

f bigint NO NULL

130

125

drop table t2;

131

126

create table t2 select CAST("2001-12-29" AS DATE) as d, CAST("20:45:11" AS TIME) as t, CAST("2001-12-29 20:45:11" AS DATETIME) as dt;

132

127

describe t2;

135

130

t time YES NULL

136

131

dt datetime YES NULL

137

132

drop table t1,t2;

138

create table t1 (a tinyint);

133

create table t1 (a int);

139

134

create table t2 (a int) select * from t1;

140

135

describe t1;

141

136

Field Type Null Key Default Extra

142

a tinyint(4) YES NULL

137

a int YES NULL

143

138

describe t2;

144

139

Field Type Null Key Default Extra

145

a int(11) YES NULL

140

a int YES NULL

146

141

drop table if exists t2;

147

142

create table t2 (a int, a float) select * from t1;

148

143

ERROR 42S21: Duplicate column name 'a'

173

168

show create table t1;

174

169

Table Create Table

175

170

t1 CREATE TABLE `t1` (

176

`a` int(11) NOT NULL,

177

`b` int(11) DEFAULT NULL,

171

`a` int NOT NULL,

172

`b` int,

178

173

PRIMARY KEY (`a`),

179

174

KEY `b` (`b`),

180

175

KEY `b_2` (`b`),

207

202

KEY `b_29` (`b`),

208

203

KEY `b_30` (`b`),

209

204

KEY `b_31` (`b`)

210

) ENGINE=MyISAM DEFAULT CHARSET=latin1

205

) ENGINE=InnoDB

211

206

drop table t1;

212

207

create table t1 select if(1,'1','0'), month("2002-08-02");

213

208

drop table t1;

224

219

show create table t1;

225

220

Table Create Table

226

221

t1 CREATE TABLE `t1` (

227

`a` int(11) NOT NULL

228

) ENGINE=MEMORY DEFAULT CHARSET=latin1

222

`a` int NOT NULL

223

) ENGINE=MEMORY

229

224

drop table t1;

230

225

SET SESSION storage_engine="gemini";

231

226

ERROR 42000: Unknown table engine 'gemini'

236

231

show create table t1;

237

232

Table Create Table

238

233

t1 CREATE TABLE `t1` (

239

`a` int(11) NOT NULL

240

) ENGINE=MEMORY DEFAULT CHARSET=latin1

234

`a` int NOT NULL

235

) ENGINE=MEMORY

241

236

SET SESSION storage_engine=default;

242

237

drop table t1;

243

238

create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2));

253

248

drop table t1;

254

249

create table t1 select 1,2,3;

255

250

create table if not exists t1 select 1,2;

256

Warnings:

257

Note 1050 Table 't1' already exists

258

251

create table if not exists t1 select 1,2,3,4;

259

ERROR 21S01: Column count doesn't match value count at row 1

260

252

create table if not exists t1 select 1;

261

Warnings:

262

Note 1050 Table 't1' already exists

263

253

select * from t1;

264

254

1 2 3

265

255

1 2 3

266

0 1 2

267

0 0 1

268

256

drop table t1;

269

257

flush status;

270

258

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

271

259

insert into t1 values (1,1);

272

create table if not exists t1 select 2;

273

Warnings:

274

Note 1050 Table 't1' already exists

275

Warning 1364 Field 'a' doesn't have a default value

276

260

select * from t1;

277

261

a b

278

262

1 1

279

0 2

280

263

create table if not exists t1 select 3 as 'a',4 as 'b';

281

264

Warnings:

282

265

Note 1050 Table 't1' already exists

292

275

select * from t1;

293

276

a b

294

277

1 1

295

0 2

296

278

3 4

297

279

drop table t1;

298

280

create table `t1 `(a int);

302

284

create table t1(`a ` int);

303

285

ERROR 42000: Incorrect column name 'a '

304

286

create table t1 (a int,);

305

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

287

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')' at line 1

306

288

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

307

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b int)' at line 1

289

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'b int)' at line 1

308

290

create table t1 (,b int);

309

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b int)' at line 1

291

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'b int)' at line 1

310

292

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

311

293

create table t2 (b int, foreign key(b) references t1(a), key(b));

312

294

drop table if exists t1,t2;

295

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails

296

drop table if exists t2,t1;

297

Warnings:

298

Note 1051 Unknown table 't2'

313

299

create table t1(id int not null, name char(20));

314

300

insert into t1 values(10,'mysql'),(20,'monty- the creator');

315

301

create table t2(id int not null);

318

304

show create table t3;

319

305

Table Create Table

320

306

t3 CREATE TABLE `t3` (

321

`id` int(11) NOT NULL,

322

`name` char(20) DEFAULT NULL

323

) ENGINE=MyISAM DEFAULT CHARSET=latin1

307

`id` int NOT NULL,

308

`name` varchar(20)

309

) ENGINE=InnoDB

324

310

select * from t3;

325

311

id name

326

312

create table if not exists t3 like t1;

333

319

show create table t3;

334

320

Table Create Table

335

321

t3 CREATE TEMPORARY TABLE `t3` (

336

`id` int(11) NOT NULL

337

) ENGINE=MyISAM DEFAULT CHARSET=latin1

322

`id` int NOT NULL

323

) ENGINE=InnoDB

338

324

select * from t3;

339

325

id

340

326

drop table t3;

341

327

show create table t3;

342

328

Table Create Table

343

329

t3 CREATE TABLE `t3` (

344

`id` int(11) NOT NULL,

345

`name` char(20) DEFAULT NULL

346

) ENGINE=MyISAM DEFAULT CHARSET=latin1

330

`id` int NOT NULL,

331

`name` varchar(20)

332

) ENGINE=InnoDB

347

333

select * from t3;

348

334

id name

349

335

drop table t2, t3;

353

339

show create table t3;

354

340

Table Create Table

355

341

t3 CREATE TEMPORARY TABLE `t3` (

356

`id` int(11) NOT NULL,

357

`name` char(20) DEFAULT NULL

358

) ENGINE=MyISAM DEFAULT CHARSET=latin1

342

`id` int NOT NULL,

343

`name` varchar(20)

344

) ENGINE=InnoDB

359

345

create table t2 like t3;

360

346

show create table t2;

361

347

Table Create Table

362

348

t2 CREATE TABLE `t2` (

363

`id` int(11) NOT NULL,

364

`name` char(20) DEFAULT NULL

365

) ENGINE=MyISAM DEFAULT CHARSET=latin1

349

`id` int NOT NULL,

350

`name` varchar(20)

351

) ENGINE=InnoDB

366

352

select * from t2;

367

353

id name

368

354

create table t3 like t1;

385

371

show create table t1;

386

372

Table Create Table

387

373

t1 CREATE TABLE `t1` (

388

`a` int(11) NOT NULL

389

) ENGINE=MEMORY DEFAULT CHARSET=latin1

374

`a` int NOT NULL

375

) ENGINE=MEMORY

390

376

drop table t1;

391

377

SET SESSION storage_engine="gemini";

392

378

ERROR 42000: Unknown table engine 'gemini'

397

383

show create table t1;

398

384

Table Create Table

399

385

t1 CREATE TABLE `t1` (

400

`a` int(11) NOT NULL

401

) ENGINE=MEMORY DEFAULT CHARSET=latin1

386

`a` int NOT NULL

387

) ENGINE=MEMORY

402

388

SET SESSION storage_engine=default;

403

389

drop table t1;

404

create table t1(a int,b int,c int unsigned,d date,e char,f datetime,g time,h blob);

390

create table t1(a int,b int,c int,d date,e char,f datetime,g time,h blob);

405

391

insert into t1(a)values(1);

406

392

insert into t1(a,b,c,d,e,f,g,h)

407

393

values(2,-2,2,'1825-12-14','a','2003-1-1 3:2:1','4:3:2','binary data');

410

396

1 NULL NULL NULL NULL NULL NULL NULL

411

397

2 -2 2 1825-12-14 a 2003-01-01 03:02:01 04:03:02 binary data

412

398

select a,

413

ifnull(b,cast(-7 as signed)) as b,

414

ifnull(c,cast(7 as unsigned)) as c,

399

ifnull(b,-7) as b,

400

ifnull(c,7) as c,

415

401

ifnull(d,cast('2000-01-01' as date)) as d,

416

402

ifnull(e,cast('b' as char)) as e,

417

403

ifnull(f,cast('2000-01-01' as datetime)) as f,

425

411

create table t2

426

412

select

427

413

a,

428

ifnull(b,cast(-7 as signed)) as b,

429

ifnull(c,cast(7 as unsigned)) as c,

414

ifnull(b,-7) as b,

415

ifnull(c,7) as c,

430

416

ifnull(d,cast('2000-01-01' as date)) as d,

431

417

ifnull(e,cast('b' as char)) as e,

432

418

ifnull(f,cast('2000-01-01' as datetime)) as f,

436

422

from t1;

437

423

explain t2;

438

424

Field Type Null Key Default Extra

439

a int(11) YES NULL

440

b bigint(11) NO 0

441

c bigint(11) unsigned NO 0

425

a int YES NULL

426

b bigint NO NULL

427

c bigint NO NULL

442

428

d date YES NULL

443

e varchar(1) NO

429

e varchar(1) YES NULL

444

430

f datetime YES NULL

445

431

g time YES NULL

446

h longblob NO NULL

432

h blob YES NULL

447

433

dd time YES NULL

448

434

select * from t2;

449

435

a b c d e f g h dd

450

436

1 -7 7 2000-01-01 b 2000-01-01 00:00:00 05:04:03 yet another binary data 02:00:00

451

437

2 -2 2 1825-12-14 a 2003-01-01 03:02:01 04:03:02 binary data 02:00:00

452

438

drop table t1, t2;

453

create table t1 (a tinyint, b smallint, 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));

454

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;

439

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

440

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;

455

441

show create table t2;

456

442

Table Create Table

457

443

t2 CREATE TABLE `t2` (

458

`ifnull(a,a)` tinyint(4) DEFAULT NULL,

459

`ifnull(b,b)` smallint(6) DEFAULT NULL,

460

`ifnull(c,c)` mediumint(8) DEFAULT NULL,

461

`ifnull(d,d)` int(11) DEFAULT NULL,

462

`ifnull(e,e)` bigint(20) DEFAULT NULL,

463

`ifnull(f,f)` float(3,2) DEFAULT NULL,

464

`ifnull(g,g)` double(4,3) DEFAULT NULL,

465

`ifnull(h,h)` decimal(5,4) DEFAULT NULL,

466

`ifnull(i,i)` year(4) DEFAULT NULL,

467

`ifnull(j,j)` date DEFAULT NULL,

468

`ifnull(k,k)` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

469

`ifnull(l,l)` datetime DEFAULT NULL,

470

`ifnull(m,m)` varchar(1) DEFAULT NULL,

471

`ifnull(n,n)` varchar(3) DEFAULT NULL,

472

`ifnull(o,o)` varchar(10) DEFAULT NULL

473

) ENGINE=MyISAM DEFAULT CHARSET=latin1

444

`ifnull(a,a)` int,

445

`ifnull(b,b)` int,

446

`ifnull(d,d)` int,

447

`ifnull(e,e)` bigint,

448

`ifnull(f,f)` double(3,2),

449

`ifnull(g,g)` double(4,3),

450

`ifnull(h,h)` decimal(5,4),

451

`ifnull(j,j)` date,

452

`ifnull(k,k)` timestamp NOT NULL,

453

`ifnull(l,l)` datetime,

454

`ifnull(m,m)` varchar(1),

455

`ifnull(o,o)` varchar(10)

456

) ENGINE=InnoDB

474

457

drop table t1,t2;

475

458

create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14');

476

459

insert into t1 values ('','',0,0.0);

477

460

describe t1;

478

461

Field Type Null Key Default Extra

479

str varchar(10) YES def

462

str varchar(10) YES NULL

480

463

strnull varchar(10) YES NULL

481

intg int(11) YES 10

482

rel double YES 3.14

464

intg int YES NULL

465

rel double YES NULL

483

466

create table t2 select default(str) as str, default(strnull) as strnull, default(intg) as intg, default(rel) as rel from t1;

484

467

describe t2;

485

468

Field Type Null Key Default Extra

486

469

str varchar(10) YES NULL

487

470

strnull varchar(10) YES NULL

488

intg int(11) YES NULL

471

intg int YES NULL

489

472

rel double YES NULL

490

473

drop table t1, t2;

491

create table t1(name varchar(10), age smallint default -1);

474

create table t1(name varchar(10), age int default -1);

492

475

describe t1;

493

476

Field Type Null Key Default Extra

494

477

name varchar(10) YES NULL

495

age smallint(6) YES -1

496

create table t2(name varchar(10), age smallint default - 1);

478

age int YES NULL

479

create table t2(name varchar(10), age int default - 1);

497

480

describe t2;

498

481

Field Type Null Key Default Extra

499

482

name varchar(10) YES NULL

500

age smallint(6) YES -1

483

age int YES NULL

501

484

drop table t1, t2;

502

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

503

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

504

Warnings:

505

Note 1291 Column 'cenum' has duplicated value 'a' in ENUM

506

Note 1291 Column 'cset' has duplicated value 'b' in SET

507

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

508

Warnings:

509

Note 1291 Column 'cenum' has duplicated value 'a' in ENUM

510

Note 1291 Column 'cenum' has duplicated value 'A' in ENUM

511

Note 1291 Column 'cenum' has duplicated value 'c' in ENUM

512

Note 1291 Column 'cset' has duplicated value 'b' in SET

513

Note 1291 Column 'cset' has duplicated value 'B' in SET

514

Note 1291 Column 'cset' has duplicated value 'd' in SET

515

drop table t1, t2, t3;

485

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

486

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

487

ERROR HY000: Column 'cenum' has duplicated value 'a' in ENUM

488

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

489

ERROR HY000: Column 'cenum' has duplicated value 'a' in ENUM

490

drop table t1;

516

491

create database mysqltest;

517

492

use mysqltest;

518

493

select database();

522

497

select database();

523

498

database()

524

499

NULL

525

create user mysqltest_1;

526

select database(), user();

527

database() user()

528

NULL mysqltest_1@localhost

529

drop user mysqltest_1;

530

500

use test;

531

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

532

ERROR 42000: Incorrect index name 'primary'

533

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

534

ERROR 42000: Incorrect index name 'PRIMARY'

535

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

536

show create table t1;

537

Table Create Table

538

t1 CREATE TABLE `t1` (

539

`primary` int(11) DEFAULT NULL,

540

KEY `primary_2` (`primary`)

541

) ENGINE=MyISAM DEFAULT CHARSET=latin1

542

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

543

show create table t2;

544

Table Create Table

545

t2 CREATE TABLE `t2` (

546

`PRIMARY` int(11) DEFAULT NULL,

547

KEY `PRIMARY_2` (`PRIMARY`)

548

) ENGINE=MyISAM DEFAULT CHARSET=latin1

549

create table t3 (a int);

550

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

551

ERROR 42000: Incorrect index name 'primary'

552

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

553

ERROR 42000: Incorrect index name 'PRIMARY'

554

create table t4 (`primary` int);

555

alter table t4 add index(`primary`);

556

show create table t4;

557

Table Create Table

558

t4 CREATE TABLE `t4` (

559

`primary` int(11) DEFAULT NULL,

560

KEY `primary_2` (`primary`)

561

) ENGINE=MyISAM DEFAULT CHARSET=latin1

562

create table t5 (`PRIMARY` int);

563

alter table t5 add index(`PRIMARY`);

564

show create table t5;

565

Table Create Table

566

t5 CREATE TABLE `t5` (

567

`PRIMARY` int(11) DEFAULT NULL,

568

KEY `PRIMARY_2` (`PRIMARY`)

569

) ENGINE=MyISAM DEFAULT CHARSET=latin1

570

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

571

501

CREATE TABLE t1(id varchar(10) NOT NULL PRIMARY KEY, dsc longtext);

572

502

INSERT INTO t1 VALUES ('5000000001', NULL),('5000000003', 'Test'),('5000000004', NULL);

573

503

CREATE TABLE t2(id varchar(15) NOT NULL, proc varchar(100) NOT NULL, runID varchar(16) NOT NULL, start datetime NOT NULL, PRIMARY KEY (id,proc,runID,start));

579

509

Test 0

580

510

NULL 1

581

511

drop table t1, t2, t3;

582

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

583

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

584

insert into t1 values ();

585

insert into t2 values ();

586

select * from t1;

587

b

588

0

589

select * from t2;

590

b

591

1

592

drop table t1,t2;

593

512

create table t1 (a int);

594

513

create table t1 select * from t1;

595

ERROR HY000: You can't specify target table 't1' for update in FROM clause

596

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

597

ERROR HY000: 'test.t2' is not BASE TABLE

598

514

flush tables with read lock;

599

515

unlock tables;

600

516

drop table t1;

607

523

create table t1(t1.name int);

608

524

create table t2(test.t2.name int);

609

525

drop table t1,t2;

610

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

526

CREATE TABLE t1 (f1 VARCHAR(255));

611

527

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

612

528

DESC t2;

613

529

Field Type Null Key Default Extra

625

541

ERROR 3D000: No database selected

626

542

drop table if exists test.t1;

627

543

create database mysqltest;

628

use mysqltest;

629

create view v1 as select 'foo' from dual;

630

create table t1 like v1;

631

ERROR HY000: 'mysqltest.v1' is not BASE TABLE

632

drop view v1;

633

drop database mysqltest;

634

create database mysqltest;

635

create database if not exists mysqltest character set latin2;

544

create database if not exists mysqltest;

636

545

Warnings:

637

546

Note 1007 Can't create database 'mysqltest'; database exists

638

547

show create database mysqltest;

639

548

Database Create Database

640

mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */

549

mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET utf8 */

641

550

drop database mysqltest;

642

551

use test;

643

552

create table t1 (a int);

646

555

Note 1050 Table 't1' already exists

647

556

drop table t1;

648

557

create table t1 (

649

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

558

a varchar(112) collate utf8_bin not null,

650

559

primary key (a)

651

560

) select 'test' as a ;

652

561

show create table t1;

654

563

t1 CREATE TABLE `t1` (

655

564

`a` varchar(112) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

656

565

PRIMARY KEY (`a`)

657

) ENGINE=MyISAM DEFAULT CHARSET=latin1

566

) ENGINE=InnoDB

658

567

drop table t1;

659

568

CREATE TABLE t2 (

660

a int(11) default NULL

569

a int default NULL

661

570

);

662

571

insert into t2 values(111);

663

572

create table t1 (

664

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

573

a varchar(12) collate utf8_bin not null,

665

574

b int not null, primary key (a)

666

575

) select a, 1 as b from t2 ;

667

576

show create table t1;

668

577

Table Create Table

669

578

t1 CREATE TABLE `t1` (

670

579

`a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

671

`b` int(11) NOT NULL,

580

`b` int NOT NULL,

672

581

PRIMARY KEY (`a`)

673

) ENGINE=MyISAM DEFAULT CHARSET=latin1

582

) ENGINE=InnoDB

674

583

drop table t1;

675

584

create table t1 (

676

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

585

a varchar(12) collate utf8_bin not null,

677

586

b int not null, primary key (a)

678

587

) select a, 1 as c from t2 ;

679

Warnings:

680

Warning 1364 Field 'b' doesn't have a default value

681

show create table t1;

682

Table Create Table

683

t1 CREATE TABLE `t1` (

684

`b` int(11) NOT NULL,

685

`a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

686

`c` int(1) NOT NULL DEFAULT '0',

687

PRIMARY KEY (`a`)

688

) ENGINE=MyISAM DEFAULT CHARSET=latin1

689

drop table t1;

588

ERROR HY000: Field 'b' doesn't have a default value

690

589

create table t1 (

691

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

590

a varchar(12) collate utf8_bin not null,

692

591

b int null, primary key (a)

693

592

) select a, 1 as c from t2 ;

694

593

show create table t1;

695

594

Table Create Table

696

595

t1 CREATE TABLE `t1` (

697

`b` int(11) DEFAULT NULL,

698

`a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

699

`c` int(1) NOT NULL DEFAULT '0',

700

PRIMARY KEY (`a`)

701

) ENGINE=MyISAM DEFAULT CHARSET=latin1

702

drop table t1;

703

create table t1 (

704

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

705

b int not null, primary key (a)

706

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

707

show create table t1;

708

Table Create Table

709

t1 CREATE TABLE `t1` (

710

`a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

711

`b` int(11) NOT NULL,

712

PRIMARY KEY (`a`)

713

) ENGINE=MyISAM DEFAULT CHARSET=latin1

714

drop table t1;

715

create table t1 (

716

a varchar(12) charset utf8 collate utf8_bin,

717

b int not null, primary key (a)

718

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

719

show create table t1;

720

Table Create Table

721

t1 CREATE TABLE `t1` (

722

`a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

723

`b` int(11) NOT NULL,

724

PRIMARY KEY (`a`)

725

) ENGINE=MyISAM DEFAULT CHARSET=latin1

596

`b` int,

597

`a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

598

`c` int NOT NULL,

599

PRIMARY KEY (`a`)

600

) ENGINE=InnoDB

601

drop table t1;

602

create table t1 (

603

a varchar(12) collate utf8_bin not null,

604

b int not null, primary key (a)

605

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

606

show create table t1;

607

Table Create Table

608

t1 CREATE TABLE `t1` (

609

`a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

610

`b` int NOT NULL,

611

PRIMARY KEY (`a`)

612

) ENGINE=InnoDB

613

drop table t1;

614

create table t1 (

615

a varchar(12) collate utf8_bin,

616

b int not null, primary key (a)

617

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

618

show create table t1;

619

Table Create Table

620

t1 CREATE TABLE `t1` (

621

`a` varchar(12) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

622

`b` int NOT NULL,

623

PRIMARY KEY (`a`)

624

) ENGINE=InnoDB

726

625

drop table t1, t2;

727

626

create table t1 (

728

627

a1 int not null,

730

629

);

731

630

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

732

631

create table t2 (

733

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

632

a1 varchar(12) collate utf8_bin not null,

734

633

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

735

634

primary key (a1)

736

635

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

737

636

drop table t2;

738

637

create table t2 (

739

a1 varchar(12) charset utf8 collate utf8_bin,

638

a1 varchar(12) collate utf8_bin,

740

639

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

741

640

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

742

641

drop table t1, t2;

745

644

);

746

645

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

747

646

create table t2 (

748

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

647

a1 varchar(12) collate utf8_bin not null,

749

648

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

750

649

primary key (a1)

751

650

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

755

654

show create table t2;

756

655

Table Create Table

757

656

t2 CREATE TABLE `t2` (

758

`a` int(11) DEFAULT '3',

759

`b` int(11) DEFAULT '3',

760

`a1` int(11) DEFAULT NULL,

761

`a2` int(11) DEFAULT NULL

762

) ENGINE=MyISAM DEFAULT CHARSET=latin1

657

`a` int,

658

`b` int,

659

`a1` int,

660

`a2` int

661

) ENGINE=InnoDB

763

662

drop table t1, t2;

764

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

765

ERROR 22007: Illegal set 'a,b' value found during parsing

766

663

create table t1 (i int) engine=myisam max_rows=100000000000;

767

664

show create table t1;

768

665

Table Create Table

769

666

t1 CREATE TABLE `t1` (

770

`i` int(11) DEFAULT NULL

771

) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=4294967295

667

`i` int

668

) ENGINE=MyISAM MAX_ROWS=4294967295

772

669

alter table t1 max_rows=100;

773

670

show create table t1;

774

671

Table Create Table

775

672

t1 CREATE TABLE `t1` (

776

`i` int(11) DEFAULT NULL

777

) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=100

673

`i` int

674

) ENGINE=MyISAM MAX_ROWS=100

778

675

alter table t1 max_rows=100000000000;

779

676

show create table t1;

780

677

Table Create Table

781

678

t1 CREATE TABLE `t1` (

782

`i` int(11) DEFAULT NULL

783

) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=4294967295

679

`i` int

680

) ENGINE=MyISAM MAX_ROWS=4294967295

784

681

drop table t1;

785

682

create table t1 select * from t2;

786

683

ERROR 42S02: Table 'test.t2' doesn't exist

787

684

create table t1 select * from t1;

788

685

ERROR HY000: You can't specify target table 't1' for update in FROM clause

789

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

790

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce'

686

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

687

ERROR HY000: Illegal mix of collations (utf8_swedish_ci,EXPLICIT) and (utf8_bin,EXPLICIT) for operation 'coalesce'

791

688

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

792

689

ERROR 42000: Key column 'a' doesn't exist in table

793

create table t1 (a int);

794

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

795

ERROR 21S01: Column count doesn't match value count at row 1

796

drop table t1;

797

690

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

798

691

ERROR 23000: Duplicate entry '1' for key 'PRIMARY'

799

692

create table t1 (i int);

800

create table t1 select 1 as i;

801

ERROR 42S01: Table 't1' already exists

802

693

create table if not exists t1 select 1 as i;

803

Warnings:

804

Note 1050 Table 't1' already exists

805

select * from t1;

806

i

807

1

808

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

809

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce'

810

select * from t1;

811

i

812

1

813

alter table t1 add primary key (i);

814

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

815

ERROR 23000: Duplicate entry '2' for key 'PRIMARY'

816

select * from t1;

817

i

818

1

819

2

694

select * from t1;

695

i

820

696

drop table t1;

697

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

698

ERROR HY000: Illegal mix of collations (utf8_swedish_ci,EXPLICIT) and (utf8_bin,EXPLICIT) for operation 'coalesce'

821

699

create temporary table t1 (j int);

822

700

create table if not exists t1 select 1;

823

701

Warnings:

1013

891

show create table t1;

1014

892

Table Create Table

1015

893

t1 CREATE TABLE `t1` (

1016

`c1` int(11) DEFAULT NULL,

1017

`c2` int(11) DEFAULT NULL,

1018

`c3` int(11) DEFAULT NULL,

1019

`c4` int(11) DEFAULT NULL,

1020

`c5` int(11) DEFAULT NULL,

1021

`c6` int(11) DEFAULT NULL,

1022

`c7` int(11) DEFAULT NULL,

1023

`c8` int(11) DEFAULT NULL,

1024

`c9` int(11) DEFAULT NULL,

1025

`c10` int(11) DEFAULT NULL,

1026

`c11` int(11) DEFAULT NULL,

1027

`c12` int(11) DEFAULT NULL,

1028

`c13` int(11) DEFAULT NULL,

1029

`c14` int(11) DEFAULT NULL,

1030

`c15` int(11) DEFAULT NULL,

1031

`c16` int(11) DEFAULT NULL,

894

`c1` int,

895

`c2` int,

896

`c3` int,

897

`c4` int,

898

`c5` int,

899

`c6` int,

900

`c7` int,

901

`c8` int,

902

`c9` int,

903

`c10` int,

904

`c11` int,

905

`c12` int,

906

`c13` int,

907

`c14` int,

908

`c15` int,

909

`c16` int,

1032

910

KEY `a001_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1033

911

KEY `a002_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1034

912

KEY `a003_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1093

971

KEY `a062_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1094

972

KEY `a063_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1095

973

KEY `a064_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`)

1096

) ENGINE=MyISAM DEFAULT CHARSET=latin1

974

) ENGINE=InnoDB

1097

975

flush tables;

1098

976

show create table t1;

1099

977

Table Create Table

1100

978

t1 CREATE TABLE `t1` (

1101

`c1` int(11) DEFAULT NULL,

1102

`c2` int(11) DEFAULT NULL,

1103

`c3` int(11) DEFAULT NULL,

1104

`c4` int(11) DEFAULT NULL,

1105

`c5` int(11) DEFAULT NULL,

1106

`c6` int(11) DEFAULT NULL,

1107

`c7` int(11) DEFAULT NULL,

1108

`c8` int(11) DEFAULT NULL,

1109

`c9` int(11) DEFAULT NULL,

1110

`c10` int(11) DEFAULT NULL,

1111

`c11` int(11) DEFAULT NULL,

1112

`c12` int(11) DEFAULT NULL,

1113

`c13` int(11) DEFAULT NULL,

1114

`c14` int(11) DEFAULT NULL,

1115

`c15` int(11) DEFAULT NULL,

1116

`c16` int(11) DEFAULT NULL,

979

`c1` int,

980

`c2` int,

981

`c3` int,

982

`c4` int,

983

`c5` int,

984

`c6` int,

985

`c7` int,

986

`c8` int,

987

`c9` int,

988

`c10` int,

989

`c11` int,

990

`c12` int,

991

`c13` int,

992

`c14` int,

993

`c15` int,

994

`c16` int,

1117

995

KEY `a001_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1118

996

KEY `a002_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1119

997

KEY `a003_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1178

1056

KEY `a062_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1179

1057

KEY `a063_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1180

1058

KEY `a064_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`)

1181

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1059

) ENGINE=InnoDB

1182

1060

drop table t1;

1183

1061

create table t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int,

1184

1062

c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int);

1314

1192

show create table t1;

1315

1193

Table Create Table

1316

1194

t1 CREATE TABLE `t1` (

1317

`c1` int(11) DEFAULT NULL,

1318

`c2` int(11) DEFAULT NULL,

1319

`c3` int(11) DEFAULT NULL,

1320

`c4` int(11) DEFAULT NULL,

1321

`c5` int(11) DEFAULT NULL,

1322

`c6` int(11) DEFAULT NULL,

1323

`c7` int(11) DEFAULT NULL,

1324

`c8` int(11) DEFAULT NULL,

1325

`c9` int(11) DEFAULT NULL,

1326

`c10` int(11) DEFAULT NULL,

1327

`c11` int(11) DEFAULT NULL,

1328

`c12` int(11) DEFAULT NULL,

1329

`c13` int(11) DEFAULT NULL,

1330

`c14` int(11) DEFAULT NULL,

1331

`c15` int(11) DEFAULT NULL,

1332

`c16` int(11) DEFAULT NULL,

1195

`c1` int,

1196

`c2` int,

1197

`c3` int,

1198

`c4` int,

1199

`c5` int,

1200

`c6` int,

1201

`c7` int,

1202

`c8` int,

1203

`c9` int,

1204

`c10` int,

1205

`c11` int,

1206

`c12` int,

1207

`c13` int,

1208

`c14` int,

1209

`c15` int,

1210

`c16` int,

1333

1211

KEY `a001_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1334

1212

KEY `a002_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1335

1213

KEY `a003_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1394

1272

KEY `a062_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1395

1273

KEY `a063_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1396

1274

KEY `a064_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`)

1397

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1275

) ENGINE=InnoDB

1398

1276

flush tables;

1399

1277

show create table t1;

1400

1278

Table Create Table

1401

1279

t1 CREATE TABLE `t1` (

1402

`c1` int(11) DEFAULT NULL,

1403

`c2` int(11) DEFAULT NULL,

1404

`c3` int(11) DEFAULT NULL,

1405

`c4` int(11) DEFAULT NULL,

1406

`c5` int(11) DEFAULT NULL,

1407

`c6` int(11) DEFAULT NULL,

1408

`c7` int(11) DEFAULT NULL,

1409

`c8` int(11) DEFAULT NULL,

1410

`c9` int(11) DEFAULT NULL,

1411

`c10` int(11) DEFAULT NULL,

1412

`c11` int(11) DEFAULT NULL,

1413

`c12` int(11) DEFAULT NULL,

1414

`c13` int(11) DEFAULT NULL,

1415

`c14` int(11) DEFAULT NULL,

1416

`c15` int(11) DEFAULT NULL,

1417

`c16` int(11) DEFAULT NULL,

1280

`c1` int,

1281

`c2` int,

1282

`c3` int,

1283

`c4` int,

1284

`c5` int,

1285

`c6` int,

1286

`c7` int,

1287

`c8` int,

1288

`c9` int,

1289

`c10` int,

1290

`c11` int,

1291

`c12` int,

1292

`c13` int,

1293

`c14` int,

1294

`c15` int,

1295

`c16` int,

1418

1296

KEY `a001_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1419

1297

KEY `a002_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1420

1298

KEY `a003_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1479

1357

KEY `a062_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1480

1358

KEY `a063_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`),

1481

1359

KEY `a064_long_123456789_123456789_123456789_123456789_123456789_1234` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`,`c7`,`c8`,`c9`,`c10`,`c11`,`c12`,`c13`,`c14`,`c15`,`c16`)

1482

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1360

) ENGINE=InnoDB

1483

1361

alter table t1 add key

1484

1362

a065_long_123456789_123456789_123456789_123456789_123456789_1234 (

1485

1363

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16);

1497

1375

show create table t1;

1498

1376

Table Create Table

1499

1377

t1 CREATE TABLE `t1` (

1500

`c1` int(11) DEFAULT NULL,

1501

`c2` int(11) DEFAULT NULL,

1502

`c3` int(11) DEFAULT NULL,

1503

`c4` int(11) DEFAULT NULL,

1504

`c5` int(11) DEFAULT NULL,

1505

`c6` int(11) DEFAULT NULL,

1506

`c7` int(11) DEFAULT NULL,

1507

`c8` int(11) DEFAULT NULL,

1508

`c9` int(11) DEFAULT NULL,

1509

`c10` int(11) DEFAULT NULL,

1510

`c11` int(11) DEFAULT NULL,

1511

`c12` int(11) DEFAULT NULL,

1512

`c13` int(11) DEFAULT NULL,

1513

`c14` int(11) DEFAULT NULL,

1514

`c15` int(11) DEFAULT NULL,

1515

`c16` int(11) DEFAULT NULL,

1516

`c17` int(11) DEFAULT NULL

1517

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1378

`c1` int,

1379

`c2` int,

1380

`c3` int,

1381

`c4` int,

1382

`c5` int,

1383

`c6` int,

1384

`c7` int,

1385

`c8` int,

1386

`c9` int,

1387

`c10` int,

1388

`c11` int,

1389

`c12` int,

1390

`c13` int,

1391

`c14` int,

1392

`c15` int,

1393

`c16` int,

1394

`c17` int

1395

) ENGINE=InnoDB

1518

1396

drop table t1;

1519

1397

1520

1398

Bug #26104 Bug on foreign key class constructor

1538

1416

create table t2 select sql_big_result f1,count(f2) from t1 group by f1;

1539

1417

show status like 'handler_read%';

1540

1418

Variable_name Value

1541

Handler_read_first 0

1542

Handler_read_key 0

1419

Handler_read_first 1

1420

Handler_read_key 3

1543

1421

Handler_read_next 0

1544

1422

Handler_read_prev 0

1545

1423

Handler_read_rnd 0

1572

1450

drop table if exists t2;

1573

1451

Warnings:

1574

1452

Note 1051 Unknown table 't2'

1575

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

1576

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

1577

ERROR 23000: Duplicate entry '1' for key 'PRIMARY'

1578

SELECT * from t2;

1579

a b

1580

1 1

1581

TRUNCATE table t2;

1582

INSERT INTO t2 select * from t1;

1583

ERROR 23000: Duplicate entry '1' for key 'PRIMARY'

1584

SELECT * from t2;

1585

a b

1586

1 1

1587

drop table t2;

1588

1453

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

1589

1454

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

1590

1455

ERROR 23000: Duplicate entry '1' for key 'PRIMARY'

1591

1456

SELECT * from t2;

1592

1457

a b

1593

1 1

1594

1458

TRUNCATE table t2;

1595

1459

INSERT INTO t2 select * from t1;

1596

1460

ERROR 23000: Duplicate entry '1' for key 'PRIMARY'

1597

1461

SELECT * from t2;

1598

1462

a b

1599

1 1

1600

1463

drop table t1,t2;

1601

1464

CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1602

1465

ERROR 42000: Incorrect database name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'

1606

1469

ERROR 42000: Incorrect database name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'

1607

1470

SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1608

1471

ERROR 42000: Incorrect database name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'

1609

set names utf8;

1610

1472

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

1611

1473

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

1612

1474

select database();

1623

1485

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

1624

1486

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

1625

1487

);

1626

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

1627

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

1628

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

1629

1488

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

1630

1489

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

1631

1490

select TABLE_NAME from information_schema.tables where

1632

1491

table_schema='test';

1633

1492

TABLE_NAME

1634

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

1635

1493

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

1636

1494

select COLUMN_NAME from information_schema.columns where

1637

1495

table_schema='test';

1638

1496

COLUMN_NAME

1639

1497

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

1640

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

1641

1498

select INDEX_NAME from information_schema.statistics where

1642

1499

table_schema='test';

1643

1500

INDEX_NAME

1644

1501

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

1645

select TABLE_NAME from information_schema.views where

1646

table_schema='test';

1647

TABLE_NAME

1648

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

1649

1502

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

1650

1503

Table Create Table

1651

1504

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

1652

`имя_поля_в_кодировке_утф8_длиной_больше_чем_45` int(11) DEFAULT NULL,

1505

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

1653

1506

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

1654

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1655

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

1656

View Create View character_set_client collation_connection

1657

имя_вью_кодировке_утф8_длиной_больше_чем_42 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `имя_вью_кодировке_утф8_длиной_больше_чем_42` AS select `имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48`.`имя_поля_в_кодировке_утф8_длиной_больше_чем_45` AS `имя_поля_в_кодировке_утф8_длиной_больше_чем_45` from `имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48` utf8 utf8_general_ci

1658

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

1659

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

1660

select TRIGGER_NAME from information_schema.triggers where

1661

trigger_schema='test';

1662

TRIGGER_NAME

1663

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

1664

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

1665

create trigger

1666

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

1667

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

1668

ERROR 42000: Identifier name 'очень_очень_очень_очень_очень_очень_очень_очень_длинна' is too long

1669

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

1670

ERROR 42000: Identifier name 'очень_очень_очень_очень_очень_очень_очень_очень_длинна' is too long

1671

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

1672

begin

1673

end;

1674

select ROUTINE_NAME from information_schema.routines where

1675

routine_schema='test';

1676

ROUTINE_NAME

1677

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

1678

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

1679

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

1680

begin

1681

end;

1682

ERROR 42000: Identifier name 'очень_очень_очень_очень_очень_очень_очень_очень_длинна' is too long

1683

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

1684

returns int

1685

return 0;

1686

select ROUTINE_NAME from information_schema.routines where

1687

routine_schema='test';

1688

ROUTINE_NAME

1689

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

1690

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

1691

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

1692

returns int

1693

return 0;

1694

ERROR 42000: Identifier name 'очень_очень_очень_очень_очень_очень_очень_очень_длинна' is too long

1695

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

1507

) ENGINE=InnoDB

1696

1508

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

1697

set names default;

1698

drop table if exists t1,t2,t3;

1699

drop function if exists f1;

1700

create function f1() returns int

1701

begin

1702

declare res int;

1703

create temporary table t3 select 1 i;

1704

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

1705

drop temporary table t3;

1706

return res;

1707

end|

1708

create table t1 as select 1;

1709

create table t2 as select f1() from t1;

1710

drop table t1,t2;

1711

drop function f1;

1712

1509

create table t1 like information_schema.processlist;

1713

1510

show create table t1;

1714

1511

Table Create Table

1715

1512

t1 CREATE TABLE `t1` (

1716

`ID` bigint(4) NOT NULL DEFAULT '0',

1717

`USER` varchar(16) NOT NULL DEFAULT '',

1718

`HOST` varchar(64) NOT NULL DEFAULT '',

1719

`DB` varchar(64) DEFAULT NULL,

1720

`COMMAND` varchar(16) NOT NULL DEFAULT '',

1721

`TIME` bigint(7) NOT NULL DEFAULT '0',

1722

`STATE` varchar(64) DEFAULT NULL,

1723

`INFO` longtext

1724

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1513

`ID` bigint NOT NULL,

1514

`USER` varchar(16) NOT NULL,

1515

`HOST` varchar(64) NOT NULL,

1516

`DB` varchar(64),

1517

`COMMAND` varchar(16) NOT NULL,

1518

`TIME` bigint NOT NULL,

1519

`STATE` varchar(64),

1520

`INFO` text

1521

) ENGINE=MyISAM

1725

1522

drop table t1;

1726

1523

create temporary table t1 like information_schema.processlist;

1727

1524

show create table t1;

1728

1525

Table Create Table

1729

1526

t1 CREATE TEMPORARY TABLE `t1` (

1730

`ID` bigint(4) NOT NULL DEFAULT '0',

1731

`USER` varchar(16) NOT NULL DEFAULT '',

1732

`HOST` varchar(64) NOT NULL DEFAULT '',

1733

`DB` varchar(64) DEFAULT NULL,

1734

`COMMAND` varchar(16) NOT NULL DEFAULT '',

1735

`TIME` bigint(7) NOT NULL DEFAULT '0',

1736

`STATE` varchar(64) DEFAULT NULL,

1737

`INFO` longtext

1738

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1739

drop table t1;

1740

create table t1 like information_schema.character_sets;

1741

show create table t1;

1742

Table Create Table

1743

t1 CREATE TABLE `t1` (

1744

`CHARACTER_SET_NAME` varchar(64) NOT NULL DEFAULT '',

1745

`DEFAULT_COLLATE_NAME` varchar(64) NOT NULL DEFAULT '',

1746

`DESCRIPTION` varchar(60) NOT NULL DEFAULT '',

1747

`MAXLEN` bigint(3) NOT NULL DEFAULT '0'

1748

) ENGINE=MEMORY DEFAULT CHARSET=utf8

1527

`ID` bigint NOT NULL,

1528

`USER` varchar(16) NOT NULL,

1529

`HOST` varchar(64) NOT NULL,

1530

`DB` varchar(64),

1531

`COMMAND` varchar(16) NOT NULL,

1532

`TIME` bigint NOT NULL,

1533

`STATE` varchar(64),

1534

`INFO` text

1535

) ENGINE=MyISAM

1749

1536

drop table t1;

1750

1537

1751

1538

# --

1760

1547

c1 INT DEFAULT 12 COMMENT 'column1',

1761

1548

c2 INT NULL COMMENT 'column2',

1762

1549

c3 INT NOT NULL COMMENT 'column3',

1763

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

1550

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

1764

1551

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

1765

1552

c6 VARCHAR(255))

1766

COLLATE latin1_bin;

1553

COLLATE utf8_bin;

1767

1554

1768

1555

SHOW CREATE TABLE t1;

1769

1556

Table Create Table

1770

1557

t1 CREATE TABLE `t1` (

1771

`c1` int(11) DEFAULT '12' COMMENT 'column1',

1772

`c2` int(11) DEFAULT NULL COMMENT 'column2',

1773

`c3` int(11) NOT NULL COMMENT 'column3',

1774

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

1775

`c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'b',

1776

`c6` varchar(255) COLLATE latin1_bin DEFAULT NULL

1777

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin

1558

`c1` int COMMENT 'column1',

1559

`c2` int COMMENT 'column2',

1560

`c3` int NOT NULL COMMENT 'column3',

1561

`c4` varchar(255) COLLATE utf8_bin NOT NULL,

1562

`c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci,

1563

`c6` varchar(255) COLLATE utf8_bin

1564

) ENGINE=InnoDB

1778

1565

1779

1566

CREATE TABLE t2 AS SELECT * FROM t1;

1780

1567

1781

1568

SHOW CREATE TABLE t2;

1782

1569

Table Create Table

1783

1570

t2 CREATE TABLE `t2` (

1784

`c1` int(11) DEFAULT '12' COMMENT 'column1',

1785

`c2` int(11) DEFAULT NULL COMMENT 'column2',

1786

`c3` int(11) NOT NULL COMMENT 'column3',

1787

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

1788

`c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'b',

1789

`c6` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL

1790

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1571

`c1` int COMMENT 'column1',

1572

`c2` int COMMENT 'column2',

1573

`c3` int NOT NULL COMMENT 'column3',

1574

`c4` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

1575

`c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci,

1576

`c6` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin

1577

) ENGINE=InnoDB

1791

1578

1792

1579

DROP TABLE t2;

1793

1580

1803

1590

1804

1591

CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP);

1805

1592

1806

SET sql_mode = NO_ZERO_DATE;

1807

1593

1808

1594

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

1809

ERROR 42000: Invalid default value for 'c2'

1595

drop table t2;

1810

1596

1811

1597

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);

1812

ERROR 42000: Invalid default value for 'c2'

1598

drop table t2;

1813

1599

1814

1600

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

1815

1601

CREATE TABLE t2(c1 TIMESTAMP NULL);

1816

1602

1817

1603

# -- Check ALTER TABLE.

1818

1604

ALTER TABLE t1 ADD INDEX(c1);

1819

ERROR 42000: Invalid default value for 'c2'

1820

1605

1821

1606

# -- Check DATETIME.

1822

SET sql_mode = '';

1823

1607

1824

1608

CREATE TABLE t3(c1 DATETIME NOT NULL);

1825

1609

INSERT INTO t3 VALUES (0);

1826

1610

1827

SET sql_mode = TRADITIONAL;

1828

1829

1611

ALTER TABLE t3 ADD INDEX(c1);

1830

ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c1' at row 1

1831

1612

1832

1613

# -- Cleanup.

1833

SET sql_mode = '';

1834

1614

DROP TABLE t1;

1835

1615

DROP TABLE t2;

1836

1616

DROP TABLE t3;

1837

1617

1838

1618

# -- End of Bug#18834.

1839

1840

# --

1841

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

1842

# --

1843

1844

DROP TABLE IF EXISTS t1;

1845

1846

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

1847

1848

SHOW CREATE TABLE t1;

1849

Table Create Table

1850

t1 CREATE TABLE `t1` (

1851

`c1` year(4) DEFAULT '2008',

1852

`c2` year(4) DEFAULT '0000'

1853

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1854

1855

INSERT INTO t1 VALUES();

1856

1857

SELECT * FROM t1;

1858

c1 c2

1859

2008 0000

1860

1861

ALTER TABLE t1 MODIFY c1 YEAR DEFAULT 0;

1862

1863

SHOW CREATE TABLE t1;

1864

Table Create Table

1865

t1 CREATE TABLE `t1` (

1866

`c1` year(4) DEFAULT '0000',

1867

`c2` year(4) DEFAULT '0000'

1868

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1869

1870

INSERT INTO t1 VALUES();

1871

1872

SELECT * FROM t1;

1873

c1 c2

1874

2008 0000

1875

0000 0000

1876

1877

DROP TABLE t1;

1878

1879

# -- End of Bug#34274

1880

1881

End of 5.1 tests