~drizzle-trunk/drizzle/development

create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int);

--error ER_TOO_LONG_IDENT

--error 1059

create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int);

# Some wrong defaults, so these creates should fail too (Bug #5902)

--error ER_INVALID_DEFAULT

--error 1067

create table t1 (a datetime default now());

--error ER_INVALID_ON_UPDATE

--error 1294

create table t1 (a datetime on update now());

--error ER_INVALID_DEFAULT

--error 1067

create table t1 (a int default 100 auto_increment);

# TODO: Should this really fail? What's wrong with default 1000 ???

#--error ER_INVALID_DEFAULT

#create table t1 (a int default 1000);

--error ER_INVALID_DEFAULT

--error 1067

create table t1 (a int default 1000);

--error 1067

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

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

insert into t1 values();

select * from t1;

--error ER_INVALID_DEFAULT

--error 1067

alter table t1 alter column a set default 'abcdef';

drop table t1;

drop table mysqltest.test2$;

100

drop database mysqltest;

101

102

--error ER_WRONG_TABLE_NAME

--error 1103

103

create table `` (a int);

104

--error ER_WRONG_TABLE_NAME

--error 1103

105

drop table if exists ``;

106

--error ER_WRONG_COLUMN_NAME

--error 1166

107

100

create table t1 (`` int);

108

--error ER_WRONG_NAME_FOR_INDEX

101

--error 1280

109

102

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

110

103

111

104

128

121

describe t1;

129

122

describe t2;

130

123

drop table t2;

131

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

124

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;

132

125

describe t2;

133

126

drop table t2;

134

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

127

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;

135

128

describe t2;

136

129

drop table t1,t2;

137

130

144

137

describe t1;

145

138

describe t2;

146

139

drop table if exists t2;

147

--error ER_DUP_FIELDNAME

140

--error 1060

148

141

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

149

142

drop table if exists t2;

150

--error ER_DUP_FIELDNAME

143

--error 1060

151

144

create table t2 (a int) select a as b, a+1 as b from t1;

152

145

drop table if exists t2;

153

--error ER_DUP_FIELDNAME

146

--error 1060

154

147

create table t2 (b int) select a as b, a+1 as b from t1;

155

148

drop table if exists t1,t2;

156

149

162

155

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

163

156

--error ER_DUP_ENTRY

164

157

CREATE TABLE t2 (primary key(a)) SELECT * FROM t1;

165

--error ER_NO_SUCH_TABLE

158

--error 1146

166

159

SELECT * from t2;

167

160

DROP TABLE t1;

168

161

DROP TABLE IF EXISTS t2;

172

165

173

166

174

167

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

175

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

176

168

show create table t1;

177

169

drop table t1;

178

170

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

184

176

185

177

# Test default table type

186

178

187

SET SESSION storage_engine="MEMORY";

179

SET SESSION storage_engine="heap";

188

180

SELECT @@storage_engine;

189

CREATE TEMPORARY TABLE t1 (a int not null);

181

CREATE TABLE t1 (a int not null);

190

182

show create table t1;

191

183

drop table t1;

192

--error ER_UNKNOWN_STORAGE_ENGINE

184

--error 1286

193

185

SET SESSION storage_engine="gemini";

194

186

SELECT @@storage_engine;

195

CREATE TEMPORARY TABLE t1 (a int not null);

187

CREATE TABLE t1 (a int not null);

196

188

show create table t1;

197

189

SET SESSION storage_engine=default;

198

190

drop table t1;

203

195

204

196

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

205

197

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

206

--error ER_BAD_NULL_ERROR

198

--error 1048

207

199

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

208

--error ER_BAD_NULL_ERROR

200

--error 1048

209

201

insert into t1 values (NULL, 3);

210

--error ER_BAD_NULL_ERROR

202

--error 1048

211

203

insert into t1 values (NULL, NULL);

212

204

drop table t1;

213

205

223

215

224

216

225

217

create table t1 select 1,2,3;

226

--error ER_NO_DEFAULT_FOR_FIELD

227

218

create table if not exists t1 select 1,2;

228

--error ER_WRONG_VALUE_COUNT_ON_ROW

219

--error 1136

229

220

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

230

--error ER_NO_DEFAULT_FOR_FIELD

231

221

create table if not exists t1 select 1;

232

222

select * from t1;

233

223

drop table t1;

239

229

flush status;

240

230

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

241

231

insert into t1 values (1,1);

242

# TODO: BUG here, this is filling in right to left for some reason

243

#create table if not exists t1 select 2;

232

create table if not exists t1 select 2;

244

233

select * from t1;

245

234

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

246

235

--error ER_DUP_ENTRY

247

236

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

248

237

show warnings;

249

--replace_column 3 # 4 # 5 #

250

select * from DATA_DICTIONARY.TABLE_DEFINITION_CACHE WHERE TABLE_COUNT > 1 ORDER BY TABLE_SCHEMA, TABLE_NAME;

238

show status like "Opened_tables";

251

239

select * from t1;

252

240

drop table t1;

253

241

256

244

# "Table truncated when creating another table name with Spaces"

257

245

258

246

259

--error ER_WRONG_TABLE_NAME

247

--error 1103

260

248

create table `t1 `(a int);

261

--error ER_WRONG_DB_NAME

249

--error 1102

262

250

create database `db1 `;

263

--error ER_WRONG_COLUMN_NAME

251

--error 1166

264

252

create table t1(`a ` int);

265

253

266

254

268

256

# "Parser permits multiple commas without syntax error"

269

257

270

258

271

--error ER_PARSE_ERROR

259

--error 1064

272

260

create table t1 (a int,);

273

--error ER_PARSE_ERROR

261

--error 1064

274

262

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

275

--error ER_PARSE_ERROR

263

--error 1064

276

264

create table t1 (,b int);

277

265

278

266

281

269

282

270

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

283

271

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

284

--error ER_ROW_IS_REFERENCED

285

272

drop table if exists t1,t2;

286

drop table if exists t2,t1;

287

273

288

274

289

275

# Test for CREATE TABLE .. LIKE ..

294

280

create table t2(id int not null);

295

281

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

296

282

create table t3 like t1;

297

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

298

283

show create table t3;

299

284

select * from t3;

300

285

# Disable PS becasue of @@warning_count

303

288

select @@warning_count;

304

289

--enable_ps_protocol

305

290

create temporary table t3 like t2;

306

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

307

291

show create table t3;

308

292

select * from t3;

309

293

drop table t3;

310

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

311

294

show create table t3;

312

295

select * from t3;

313

296

drop table t2, t3;

314

297

create database mysqltest;

315

298

create table mysqltest.t3 like t1;

316

299

create temporary table t3 like mysqltest.t3;

317

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

318

300

show create table t3;

319

301

create table t2 like t3;

320

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

321

302

show create table t2;

322

303

select * from t2;

323

--error ER_TABLE_EXISTS_ERROR

324

304

create table t3 like t1;

325

--error ER_TABLE_EXISTS_ERROR

305

--error 1050

326

306

create table t3 like mysqltest.t3;

327

--error ER_BAD_DB_ERROR

307

--error 1049

328

308

create table non_existing_database.t1 like t1;

329

309

--error ER_NO_SUCH_TABLE

330

310

create table t3 like non_existing_table;

331

--error ER_TABLE_EXISTS_ERROR

311

--error 1050

332

312

create temporary table t3 like t1;

333

313

drop table t1, t2, t3;

314

drop table t3;

334

315

drop database mysqltest;

335

316

336

317

337

318

# Test default table type

338

319

339

SET SESSION storage_engine="MEMORY";

320

SET SESSION storage_engine="heap";

340

321

SELECT @@storage_engine;

341

CREATE TEMPORARY TABLE t1 (a int not null);

322

CREATE TABLE t1 (a int not null);

342

323

show create table t1;

343

324

drop table t1;

344

--error ER_UNKNOWN_STORAGE_ENGINE

325

--error 1286

345

326

SET SESSION storage_engine="gemini";

346

327

SELECT @@storage_engine;

347

CREATE TEMPORARY TABLE t1 (a int not null);

328

CREATE TABLE t1 (a int not null);

348

329

show create table t1;

349

330

SET SESSION storage_engine=default;

350

331

drop table t1;

353

334

# Test types of data for create select with functions

354

335

355

336

356

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

337

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

357

338

insert into t1(a)values(1);

358

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

359

values(2,-2,2,'1825-12-14','a','2003-01-01 03:02:01','binary data');

339

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

340

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

360

341

select * from t1;

361

342

select a,

362

ifnull(b,-7) as b,

363

ifnull(c,7) as c,

343

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

344

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

364

345

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

365

346

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

366

347

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

367

ifnull(h,cast('yet another binary data' as binary)) as h

348

ifnull(g,cast('5:4:3' as time)) as g,

349

ifnull(h,cast('yet another binary data' as binary)) as h,

350

addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd

368

351

from t1;

369

352

370

353

create table t2

371

354

select

372

355

373

ifnull(b,-7) as b,

374

ifnull(c,7) as c,

356

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

357

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

375

358

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

376

359

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

377

360

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

378

ifnull(h,cast('yet another binary data' as binary)) as h

361

ifnull(g,cast('5:4:3' as time)) as g,

362

ifnull(h,cast('yet another binary data' as binary)) as h,

363

addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd

379

364

from t1;

380

365

explain t2;

381

366

select * from t2;

382

367

drop table t1, t2;

383

368

384

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

385

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;

386

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

369

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;

387

371

show create table t2;

388

372

drop table t1,t2;

389

373

411

395

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

412

396

413

397

414

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

415

--error ER_DUPLICATED_VALUE_IN_TYPE

416

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

417

--error ER_DUPLICATED_VALUE_IN_TYPE

418

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

419

drop table t1;

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;

420

402

421

403

422

404

# Bug #1209

427

409

select database();

428

410

drop database mysqltest;

429

411

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;

430

421

use test;

431

422

432

423

433

424

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

434

425

435

426

436

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

437

#--error ER_WRONG_NAME_FOR_INDEX

438

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

439

#--error ER_WRONG_NAME_FOR_INDEX

440

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

441

442

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

443

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

444

#show create table t1;

445

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

446

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

447

#show create table t2;

448

449

#create table t3 (a int);

450

#--error ER_WRONG_NAME_FOR_INDEX

451

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

452

#--error ER_WRONG_NAME_FOR_INDEX

453

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

454

455

#create table t4 (`primary` int);

456

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

457

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

458

#show create table t4;

459

#create table t5 (`PRIMARY` int);

460

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

461

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

462

#show create table t5;

463

464

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

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;

465

451

466

452

467

453

# bug #3266 TEXT in CREATE TABLE SELECT

477

463

SELECT * FROM t3;

478

464

drop table t1, t2, t3;

479

465

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;

480

476

481

477

482

478

# Bug#10224 - ANALYZE TABLE crashing with simultaneous

485

481

# an improper fix is present.

486

482

487

483

create table t1 (a int);

488

--error ER_UPDATE_TABLE_USED

484

--error 1093

489

485

create table t1 select * from t1;

490

## TODO: Huh? --error ER_WRONG_OBJECT

491

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

486

--error ER_WRONG_OBJECT

487

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

492

488

flush tables with read lock;

493

489

unlock tables;

494

490

drop table t1;

496

492

497

493

# Bug#10413: Invalid column name is not rejected

498

494

499

--error ER_WRONG_TABLE_NAME

495

--error 1103

500

496

create table t1(column.name int);

501

--error ER_WRONG_TABLE_NAME

497

--error 1103

502

498

create table t1(test.column.name int);

503

--error ER_WRONG_DB_NAME

499

--error 1102

504

500

create table t1(xyz.t1.name int);

505

501

create table t1(t1.name int);

506

502

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

509

505

510

506

# Bug #12537: UNION produces longtext instead of varchar

511

507

512

CREATE TABLE t1 (f1 VARCHAR(255));

508

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

513

509

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

514

510

DESC t2;

515

511

DROP TABLE t1,t2;

533

529

drop table if exists test.t1;

534

530

--enable_warnings

535

531

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;

536

542

# Bug #6008 MySQL does not create warnings when

537

543

# creating database and using IF NOT EXISTS

538

544

539

545

create database mysqltest;

540

create database if not exists mysqltest;

546

create database if not exists mysqltest character set latin2;

541

547

show create database mysqltest;

542

548

drop database mysqltest;

543

549

use test;

547

553

548

554

# BUG#14139

549

555

create table t1 (

550

a varchar(112) collate utf8_bin not null,

556

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

551

557

primary key (a)

552

558

) select 'test' as a ;

553

559

#--warning 1364

554

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

555

560

show create table t1;

556

561

drop table t1;

557

562

560

565

# calculation of number of NULLs.

561

566

562

567

CREATE TABLE t2 (

563

a int default NULL

568

a int(11) default NULL

564

569

);

565

570

insert into t2 values(111);

566

571

567

572

#--warning 1364

568

573

create table t1 (

569

a varchar(12) collate utf8_bin not null,

574

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

570

575

b int not null, primary key (a)

571

576

) select a, 1 as b from t2 ;

572

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

573

577

show create table t1;

574

578

drop table t1;

575

579

576

--error ER_NO_DEFAULT_FOR_FIELD

580

#--warning 1364

577

581

create table t1 (

578

a varchar(12) collate utf8_bin not null,

582

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

579

583

b int not null, primary key (a)

580

584

) select a, 1 as c from t2 ;

585

show create table t1;

586

drop table t1;

581

587

588

#--warning 1364

582

589

create table t1 (

583

a varchar(12) collate utf8_bin not null,

590

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

584

591

b int null, primary key (a)

585

592

) select a, 1 as c from t2 ;

586

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

587

show create table t1;

588

drop table t1;

589

590

create table t1 (

591

a varchar(12) collate utf8_bin not null,

592

b int not null, primary key (a)

593

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

594

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

595

show create table t1;

596

drop table t1;

597

598

create table t1 (

599

a varchar(12) collate utf8_bin,

600

b int not null, primary key (a)

601

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

602

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

593

show create table t1;

594

drop table t1;

595

596

#--warning 1364

597

create table t1 (

598

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

599

b int not null, primary key (a)

600

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

601

show create table t1;

602

drop table t1;

603

604

#--warning 1364

605

create table t1 (

606

a varchar(12) charset utf8 collate utf8_bin,

607

b int not null, primary key (a)

608

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

603

609

show create table t1;

604

610

drop table t1, t2;

605

611

617

612

618

#--warning 1364

613

619

create table t2 (

614

a1 varchar(12) collate utf8_bin not null,

620

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

615

621

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

616

622

primary key (a1)

617

623

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

619

625

620

626

#--warning 1364

621

627

create table t2 (

622

a1 varchar(12) collate utf8_bin,

628

a1 varchar(12) charset utf8 collate utf8_bin,

623

629

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

624

630

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

625

631

632

638

633

639

#--warning 1364

634

640

create table t2 (

635

a1 varchar(12) collate utf8_bin not null,

641

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

636

642

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

637

643

primary key (a1)

638

644

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

642

648

643

649

create table t2 ( a int default 3, b int default 3)

644

650

select a1,a2 from t1;

645

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

646

651

show create table t2;

647

652

648

653

drop table t1, t2;

649

654

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

664

665

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

666

# platforms

667

668

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

669

show create table t1;

670

alter table t1 max_rows=100;

671

show create table t1;

672

alter table t1 max_rows=100000000000;

673

show create table t1;

674

drop table t1;

675

650

676

651

677

652

678

# Tests for errors happening at various stages of CREATE TABLES ... SELECT

662

688

create table t1 select * from t1;

663

689

# Error which happens before select_create::prepare()

664

690

--error ER_CANT_AGGREGATE_2COLLATIONS

665

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

691

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

666

692

# Error during table creation

667

693

--error ER_KEY_COLUMN_DOES_NOT_EXITS

668

694

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

669

695

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

670

# TODO: This really should be failing...

671

# create table t1 (a int);

672

# --error ER_WRONG_VALUE_COUNT_ON_ROW

673

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

674

# drop table t1;

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;

675

700

# Finally error which happens during insert

676

701

--error ER_DUP_ENTRY

677

702

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

678

703

# What happens if table already exists ?

679

704

create table t1 (i int);

680

# TODO: BUG lp:311045

681

#--error ER_TABLE_EXISTS_ERROR

682

#create table t1 select 1 as i;

705

--error ER_TABLE_EXISTS_ERROR

706

create table t1 select 1 as i;

683

707

create table if not exists t1 select 1 as i;

684

708

select * from t1;

685

drop table t1;

686

709

# Error before select_create::prepare()

687

710

--error ER_CANT_AGGREGATE_2COLLATIONS

688

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

711

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

712

select * from t1;

689

713

# Error which happens during insertion of rows

690

# TODO: Bug lp:311072

691

# create table t1 (i int);

692

# alter table t1 add primary key (i);

693

# --error ER_DUP_ENTRY

694

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

695

# select * from t1;

696

# drop table t1;

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;

697

719

698

720

699

721

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

713

735

714

736

715

737

738

# CREATE TABLE ... SELECT and LOCK TABLES

739

740

# There is little sense in using CREATE TABLE ... SELECT under

741

# LOCK TABLES as it mostly does not work. At least we check that

742

# the server doesn't crash, hang and produces sensible errors.

743

# Includes test for bug #20662 "Infinite loop in CREATE TABLE

744

# IF NOT EXISTS ... SELECT with locked tables".

745

create table t1 (i int);

746

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

747

lock tables t1 read;

748

--error ER_TABLE_NOT_LOCKED

749

create table t2 select * from t1;

750

--error ER_TABLE_NOT_LOCKED

751

create table if not exists t2 select * from t1;

752

unlock tables;

753

create table t2 (j int);

754

lock tables t1 read;

755

--error ER_TABLE_NOT_LOCKED

756

create table t2 select * from t1;

757

# This should not be ever allowed as it will undermine

758

# lock-all-at-once approach

759

--error ER_TABLE_NOT_LOCKED

760

create table if not exists t2 select * from t1;

761

unlock tables;

762

lock table t1 read, t2 read;

763

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

764

create table t2 select * from t1;

765

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

766

create table if not exists t2 select * from t1;

767

unlock tables;

768

lock table t1 read, t2 write;

769

--error ER_TABLE_EXISTS_ERROR

770

create table t2 select * from t1;

771

# This is the only case which really works.

772

create table if not exists t2 select * from t1;

773

select * from t1;

774

unlock tables;

775

drop table t2;

776

777

# OTOH CREATE TEMPORARY TABLE ... SELECT should work

778

# well under LOCK TABLES.

779

lock tables t1 read;

780

create temporary table t2 select * from t1;

781

create temporary table if not exists t2 select * from t1;

782

select * from t2;

783

unlock tables;

784

drop table t1, t2;

785

786

787

716

788

# Bug#21772: can not name a column 'upgrade' when create a table

717

789

718

790

create table t1 (upgrade int);

868

940

);

869

941

870

942

# Check that the table is not corrupted

871

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

872

943

show create table t1;

873

944

flush tables;

874

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

875

945

show create table t1;

876

946

877

947

# Repeat test using ALTER to add indexes

1017

1087

add key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (

1018

1088

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

1019

1089

1020

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

1021

1090

show create table t1;

1022

1091

flush tables;

1023

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

1024

1092

show create table t1;

1025

1093

1026

1094

# Test the server limits; if any of these pass, all above tests need

1027

1095

# to be rewritten to hit the limit

1028

1096

1029

1097

# Ensure limit is really 64 keys

1030

--error ER_TOO_MANY_KEYS

1098

--error 1069

1031

1099

alter table t1 add key

1032

1100

a065_long_123456789_123456789_123456789_123456789_123456789_1234 (

1033

1101

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

1041

1109

c16 int, c17 int);

1042

1110

1043

1111

# Get error for max key parts

1044

--error ER_TOO_MANY_KEY_PARTS

1112

--error 1070

1045

1113

alter table t1 add key i1 (

1046

1114

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

1047

1115

1048

1116

# Get error for max key-name length

1049

--error ER_TOO_LONG_IDENT

1117

--error 1059

1050

1118

alter table t1 add key

1051

1119

a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);

1052

1120

1053

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

1054

1121

show create table t1;

1055

1122

1056

1123

drop table t1;

1100

1167

# Show that in case of multiple index type definitions, the last one takes

1101

1168

# precedence

1102

1169

1103

CREATE TEMPORARY TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY;

1104

#SHOW INDEX FROM t1;

1170

CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY;

1171

SHOW INDEX FROM t1;

1105

1172

DROP TABLE t1;

1106

1173

1107

CREATE TEMPORARY TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY;

1108

#SHOW INDEX FROM t1;

1174

CREATE TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY;

1175

SHOW INDEX FROM t1;

1109

1176

DROP TABLE t1;

1110

1177

1111

1178

1125

1192

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

1126

1193

# This should give warning

1127

1194

drop table if exists t2;

1128

# TODO: Bug lp:311072

1129

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

1130

#--error ER_DUP_ENTRY

1131

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

1132

#SELECT * from t2;

1133

#TRUNCATE table t2;

1134

#--error ER_DUP_ENTRY

1135

#INSERT INTO t2 select * from t1;

1136

#SELECT * from t2;

1137

#drop table 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;

1138

1204

1139

1205

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

1140

1206

--error ER_DUP_ENTRY

1151

1217

# Test incorrect database names

1152

1218

1153

1219

1154

--error ER_WRONG_DB_NAME

1220

--error 1102

1155

1221

CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1156

--error ER_WRONG_DB_NAME

1222

--error 1102

1157

1223

DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1158

1224

1159

1225

# TODO: enable these tests when RENAME DATABASE is implemented.

1160

# --error ER_BAD_DB_ERROR

1226

# --error 1049

1161

1227

# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;

1162

# --error ER_WRONG_DB_NAME

1228

# --error 1102

1163

1229

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1164

1230

# create database mysqltest;

1165

# --error ER_WRONG_DB_NAME

1231

# --error 1102

1166

1232

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1167

1233

# drop database mysqltest;

1168

1234

1169

--error ER_WRONG_DB_NAME

1235

--error 1102

1170

1236

USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1171

--error ER_WRONG_DB_NAME

1237

--error 1102

1172

1238

SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1173

1239

1174

1175

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

1176

1240

1241

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

1242

1243

set names utf8;

1177

1244

1178

1245

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

1179

1246

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

1180

1247

select database();

1181

1248

use test;

1182

1249

1183

select SCHEMA_NAME from data_dictionary.schemas

1250

select SCHEMA_NAME from information_schema.schemata

1184

1251

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

1185

1252

1186

1253

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

1190

1257

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

1191

1258

);

1192

1259

1260

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

1261

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

1262

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

1193

1263

1194

# database, table, field, key

1264

# database, table, field, key, view

1195

1265

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

1196

1266

1197

select TABLE_NAME from data_dictionary.tables where

1198

table_schema='test';

1199

1200

select COLUMN_NAME from data_dictionary.columns where

1201

table_schema='test';

1202

1203

select INDEX_NAME from data_dictionary.indexes where

1204

table_schema='test';

1205

1206

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

1267

select TABLE_NAME from information_schema.tables where

1268

table_schema='test';

1269

1270

select COLUMN_NAME from information_schema.columns where

1271

table_schema='test';

1272

1273

select INDEX_NAME from information_schema.statistics where

1274

table_schema='test';

1275

1276

select TABLE_NAME from information_schema.views where

1277

table_schema='test';

1278

1207

1279

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

1208

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;

1209

1319

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

1210

1211

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;

1345

1212

1346

1213

1347

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

1214

1348

1215

--error ER_CANT_CREATE_TABLE

1216

create table t1 like data_dictionary.processlist;

1217

create table t1 like data_dictionary.processlist engine=innodb;

1218

show create table t1;

1219

drop table t1;

1220

--error ER_CANT_CREATE_TABLE

1221

create temporary table t1 like data_dictionary.processlist;

1222

create temporary table t1 like data_dictionary.processlist engine=myisam;

1349

create table t1 like information_schema.processlist;

1350

show create table t1;

1351

drop table t1;

1352

create temporary table t1 like information_schema.processlist;

1353

show create table t1;

1354

drop table t1;

1355

create table t1 like information_schema.character_sets;

1223

1356

show create table t1;

1224

1357

drop table t1;

1225

1358

1244

1377

c1 INT DEFAULT 12 COMMENT 'column1',

1245

1378

c2 INT NULL COMMENT 'column2',

1246

1379

c3 INT NOT NULL COMMENT 'column3',

1247

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

1380

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

1248

1381

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

1249

1382

c6 VARCHAR(255))

1250

COLLATE=utf8_bin;

1383

COLLATE latin1_bin;

1251

1384

1252

1385

--echo

1253

1386

1254

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

1255

1387

SHOW CREATE TABLE t1;

1256

1388

1257

1389

--echo

1260

1392

1261

1393

--echo

1262

1394

1263

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

1264

1395

SHOW CREATE TABLE t2;

1265

1396

1266

1397

--echo

1290

1421

1291

1422

--echo

1292

1423

1293

--echo

1294

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

1295

drop table t2;

1296

1297

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

1298

drop table t2;

1299

1300

--echo

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

1301

1432

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);

1302

drop table t2;

1303

1433

1304

1434

--echo

1305

1435

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

1307

1437

1308

1438

--echo

1309

1439

--echo # -- Check ALTER TABLE.

1440

--error ER_INVALID_DEFAULT

1310

1441

ALTER TABLE t1 ADD INDEX(c1);

1311

1442

1312

1443

--echo

1313

1444

--echo # -- Check DATETIME.

1445

SET sql_mode = '';

1446

1314

1447

--echo

1315

1448

1316

1449

CREATE TABLE t3(c1 DATETIME NOT NULL);

1317

--error ER_INVALID_DATETIME_VALUE # Bad datetime

1318

1450

INSERT INTO t3 VALUES (0);

1319

1451

1320

1452

--echo

1453

SET sql_mode = TRADITIONAL;

1454

1455

--echo

1456

--error ER_TRUNCATED_WRONG_VALUE

1321

1457

ALTER TABLE t3 ADD INDEX(c1);

1322

1458

1323

1459

--echo

1324

1460

--echo # -- Cleanup.

1325

1461

1462

SET sql_mode = '';

1326

1463

DROP TABLE t1;

1327

1464

DROP TABLE t2;

1328

1465

DROP TABLE t3;

1330

1467

--echo

1331

1468

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

1332

1469

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 »