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

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 AND TABLE_SCHEMA = SCHEMA() > 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

318

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

319

300

show create table t3;

320

321

301

create table t2 like t3;

322

323

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

324

302

show create table t2;

325

303

select * from t2;

326

327

--error ER_TABLE_EXISTS_ERROR

328

304

create table t3 like t1;

329

330

--error ER_TABLE_EXISTS_ERROR

305

--error 1050

331

306

create table t3 like mysqltest.t3;

332

333

--error ER_BAD_DB_ERROR

307

--error 1049

334

308

create table non_existing_database.t1 like t1;

335

336

--error ER_TABLE_UNKNOWN

337

create table t4 like non_existing_table;

338

339

--error ER_TABLE_EXISTS_ERROR

309

--error ER_NO_SUCH_TABLE

310

create table t3 like non_existing_table;

311

--error 1050

340

312

create temporary table t3 like t1;

341

313

drop table t1, t2, t3;

314

drop table t3;

342

315

drop database mysqltest;

343

316

344

317

345

318

# Test default table type

346

319

347

SET SESSION storage_engine="MEMORY";

320

SET SESSION storage_engine="heap";

348

321

SELECT @@storage_engine;

349

CREATE TEMPORARY TABLE t1 (a int not null);

322

CREATE TABLE t1 (a int not null);

350

323

show create table t1;

351

324

drop table t1;

352

--error ER_UNKNOWN_STORAGE_ENGINE

325

--error 1286

353

326

SET SESSION storage_engine="gemini";

354

327

SELECT @@storage_engine;

355

CREATE TEMPORARY TABLE t1 (a int not null);

328

CREATE TABLE t1 (a int not null);

356

329

show create table t1;

357

330

SET SESSION storage_engine=default;

358

331

drop table t1;

361

334

# Test types of data for create select with functions

362

335

363

336

364

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

365

338

insert into t1(a)values(1);

366

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

367

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

368

341

select * from t1;

369

342

select a,

370

ifnull(b,-7) as b,

371

ifnull(c,7) as c,

343

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

344

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

372

345

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

373

346

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

374

347

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

375

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

376

351

from t1;

377

352

378

353

create table t2

379

354

select

380

355

381

ifnull(b,-7) as b,

382

ifnull(c,7) as c,

356

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

357

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

383

358

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

384

359

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

385

360

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

386

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

387

364

from t1;

388

365

explain t2;

389

366

select * from t2;

390

367

drop table t1, t2;

391

368

392

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

393

SHOW CREATE TABLE t1;

394

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;

395

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

396

371

show create table t2;

397

372

drop table t1,t2;

398

373

420

395

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

421

396

422

397

423

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

424

--error ER_DUPLICATED_VALUE_IN_TYPE

425

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

426

--error ER_DUPLICATED_VALUE_IN_TYPE

427

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

428

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;

429

402

430

403

431

404

# Bug #1209

436

409

select database();

437

410

drop database mysqltest;

438

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;

439

421

use test;

440

422

441

423

442

424

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

443

425

444

426

445

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

446

#--error ER_WRONG_NAME_FOR_INDEX

447

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

448

#--error ER_WRONG_NAME_FOR_INDEX

449

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

450

451

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

452

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

453

#show create table t1;

454

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

455

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

456

#show create table t2;

457

458

#create table t3 (a int);

459

#--error ER_WRONG_NAME_FOR_INDEX

460

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

461

#--error ER_WRONG_NAME_FOR_INDEX

462

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

463

464

#create table t4 (`primary` int);

465

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

466

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

467

#show create table t4;

468

#create table t5 (`PRIMARY` int);

469

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

470

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

471

#show create table t5;

472

473

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

474

451

475

452

476

453

# bug #3266 TEXT in CREATE TABLE SELECT

486

463

SELECT * FROM t3;

487

464

drop table t1, t2, t3;

488

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;

489

476

490

477

491

478

# Bug#10224 - ANALYZE TABLE crashing with simultaneous

494

481

# an improper fix is present.

495

482

496

483

create table t1 (a int);

497

--error ER_UPDATE_TABLE_USED

484

--error 1093

498

485

create table t1 select * from t1;

499

## TODO: Huh? --error ER_WRONG_OBJECT

500

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

486

--error ER_WRONG_OBJECT

487

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

501

488

flush tables with read lock;

502

489

unlock tables;

503

490

drop table t1;

505

492

506

493

# Bug#10413: Invalid column name is not rejected

507

494

508

--error ER_WRONG_TABLE_NAME

495

--error 1103

509

496

create table t1(column.name int);

510

--error ER_WRONG_TABLE_NAME

497

--error 1103

511

498

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

512

--error ER_WRONG_DB_NAME

499

--error 1102

513

500

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

514

501

create table t1(t1.name int);

515

502

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

518

505

519

506

# Bug #12537: UNION produces longtext instead of varchar

520

507

521

CREATE TABLE t1 (f1 VARCHAR(255));

508

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

522

509

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

523

510

DESC t2;

524

511

DROP TABLE t1,t2;

542

529

drop table if exists test.t1;

543

530

--enable_warnings

544

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;

545

542

# Bug #6008 MySQL does not create warnings when

546

543

# creating database and using IF NOT EXISTS

547

544

548

545

create database mysqltest;

549

create database if not exists mysqltest;

546

create database if not exists mysqltest character set latin2;

550

547

show create database mysqltest;

551

548

drop database mysqltest;

552

549

use test;

556

553

557

554

# BUG#14139

558

555

create table t1 (

559

a varchar(112) collate utf8_bin not null,

556

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

560

557

primary key (a)

561

558

) select 'test' as a ;

562

559

#--warning 1364

563

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

564

560

show create table t1;

565

561

drop table t1;

566

562

569

565

# calculation of number of NULLs.

570

566

571

567

CREATE TABLE t2 (

572

a int default NULL

568

a int(11) default NULL

573

569

);

574

570

insert into t2 values(111);

575

571

576

572

#--warning 1364

577

573

create table t1 (

578

a varchar(12) collate utf8_bin not null,

574

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

579

575

b int not null, primary key (a)

580

576

) select a, 1 as b from t2 ;

581

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

582

577

show create table t1;

583

578

drop table t1;

584

579

585

--error ER_NO_DEFAULT_FOR_FIELD

580

#--warning 1364

586

581

create table t1 (

587

a varchar(12) collate utf8_bin not null,

582

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

588

583

b int not null, primary key (a)

589

584

) select a, 1 as c from t2 ;

585

show create table t1;

586

drop table t1;

590

587

588

#--warning 1364

591

589

create table t1 (

592

a varchar(12) collate utf8_bin not null,

590

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

593

591

b int null, primary key (a)

594

592

) select a, 1 as c from t2 ;

595

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

596

show create table t1;

597

drop table t1;

598

599

create table t1 (

600

a varchar(12) collate utf8_bin not null,

601

b int not null, primary key (a)

602

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

603

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

604

show create table t1;

605

drop table t1;

606

607

create table t1 (

608

a varchar(12) collate utf8_bin,

609

b int not null, primary key (a)

610

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

611

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

612

609

show create table t1;

613

610

drop table t1, t2;

614

611

620

617

621

618

#--warning 1364

622

619

create table t2 (

623

a1 varchar(12) collate utf8_bin not null,

620

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

624

621

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

625

622

primary key (a1)

626

623

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

628

625

629

626

#--warning 1364

630

627

create table t2 (

631

a1 varchar(12) collate utf8_bin,

628

a1 varchar(12) charset utf8 collate utf8_bin,

632

629

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

633

630

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

634

631

641

638

642

639

#--warning 1364

643

640

create table t2 (

644

a1 varchar(12) collate utf8_bin not null,

641

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

645

642

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

646

643

primary key (a1)

647

644

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

651

648

652

649

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

653

650

select a1,a2 from t1;

654

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

655

651

show create table t2;

656

652

657

653

drop table t1, t2;

658

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

659

676

660

677

661

678

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

664

681

# of error it is automatically dropped if it has not existed before.)

665

682

666

683

# Error during open_and_lock_tables() of tables

667

--error ER_TABLE_UNKNOWN

684

--error ER_NO_SUCH_TABLE

668

685

create table t1 select * from t2;

669

686

# Rather special error which also caught during open tables pahse

670

687

--error ER_UPDATE_TABLE_USED

671

688

create table t1 select * from t1;

672

689

# Error which happens before select_create::prepare()

673

690

--error ER_CANT_AGGREGATE_2COLLATIONS

674

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

675

692

# Error during table creation

676

693

--error ER_KEY_COLUMN_DOES_NOT_EXITS

677

694

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

678

695

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

679

# TODO: This really should be failing...

680

# create table t1 (a int);

681

# --error ER_WRONG_VALUE_COUNT_ON_ROW

682

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

683

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

684

700

# Finally error which happens during insert

685

701

--error ER_DUP_ENTRY

686

702

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

687

703

# What happens if table already exists ?

688

704

create table t1 (i int);

689

# TODO: BUG lp:311045

690

#--error ER_TABLE_EXISTS_ERROR

691

#create table t1 select 1 as i;

705

--error ER_TABLE_EXISTS_ERROR

706

create table t1 select 1 as i;

692

707

create table if not exists t1 select 1 as i;

693

708

select * from t1;

694

drop table t1;

695

709

# Error before select_create::prepare()

696

710

--error ER_CANT_AGGREGATE_2COLLATIONS

697

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;

698

713

# Error which happens during insertion of rows

699

# TODO: Bug lp:311072

700

# create table t1 (i int);

701

# alter table t1 add primary key (i);

702

# --error ER_DUP_ENTRY

703

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

704

# select * from t1;

705

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

706

719

707

720

708

721

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

715

728

create table if not exists t1 select 1;

716

729

select * from t1;

717

730

drop temporary table t1;

718

--error ER_TABLE_UNKNOWN

731

--error ER_NO_SUCH_TABLE

719

732

select * from t1;

720

733

--error ER_BAD_TABLE_ERROR

721

734

drop table t1;

722

735

723

736

724

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

725

788

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

726

789

727

790

create table t1 (upgrade int);

877

940

);

878

941

879

942

# Check that the table is not corrupted

880

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

881

943

show create table t1;

882

944

flush tables;

883

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

884

945

show create table t1;

885

946

886

947

# Repeat test using ALTER to add indexes

1026

1087

add key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (

1027

1088

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

1028

1089

1029

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

1030

1090

show create table t1;

1031

1091

flush tables;

1032

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

1033

1092

show create table t1;

1034

1093

1035

1094

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

1036

1095

# to be rewritten to hit the limit

1037

1096

1038

1097

# Ensure limit is really 64 keys

1039

--error ER_TOO_MANY_KEYS

1098

--error 1069

1040

1099

alter table t1 add key

1041

1100

a065_long_123456789_123456789_123456789_123456789_123456789_1234 (

1042

1101

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

1050

1109

c16 int, c17 int);

1051

1110

1052

1111

# Get error for max key parts

1053

--error ER_TOO_MANY_KEY_PARTS

1112

--error 1070

1054

1113

alter table t1 add key i1 (

1055

1114

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

1056

1115

1057

1116

# Get error for max key-name length

1058

--error ER_TOO_LONG_IDENT

1117

--error 1059

1059

1118

alter table t1 add key

1060

1119

a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);

1061

1120

1062

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

1063

1121

show create table t1;

1064

1122

1065

1123

drop table t1;

1109

1167

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

1110

1168

# precedence

1111

1169

1112

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

1113

#SHOW INDEX FROM t1;

1170

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

1171

SHOW INDEX FROM t1;

1114

1172

DROP TABLE t1;

1115

1173

1116

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

1117

#SHOW INDEX FROM t1;

1174

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

1175

SHOW INDEX FROM t1;

1118

1176

DROP TABLE t1;

1119

1177

1120

1178

1134

1192

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

1135

1193

# This should give warning

1136

1194

drop table if exists t2;

1137

# TODO: Bug lp:311072

1138

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

1139

#--error ER_DUP_ENTRY

1140

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

1141

#SELECT * from t2;

1142

#TRUNCATE table t2;

1143

#--error ER_DUP_ENTRY

1144

#INSERT INTO t2 select * from t1;

1145

#SELECT * from t2;

1146

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

1147

1204

1148

1205

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

1149

1206

--error ER_DUP_ENTRY

1160

1217

# Test incorrect database names

1161

1218

1162

1219

1163

--error ER_WRONG_DB_NAME

1220

--error 1102

1164

1221

CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1165

--error ER_WRONG_DB_NAME

1222

--error 1102

1166

1223

DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1167

1224

1168

1225

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

1169

# --error ER_BAD_DB_ERROR

1226

# --error 1049

1170

1227

# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;

1171

# --error ER_WRONG_DB_NAME

1228

# --error 1102

1172

1229

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1173

1230

# create database mysqltest;

1174

# --error ER_WRONG_DB_NAME

1231

# --error 1102

1175

1232

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1176

1233

# drop database mysqltest;

1177

1234

1178

--error ER_WRONG_DB_NAME

1235

--error 1102

1179

1236

USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1180

#--error ER_WRONG_DB_NAME

1237

--error 1102

1181

1238

SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1182

1239

1183

1184

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

1185

1240

1241

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

1242

1243

set names utf8;

1186

1244

1187

1245

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

1188

1246

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

1189

1247

select database();

1190

1248

use test;

1191

1249

1192

select SCHEMA_NAME from data_dictionary.schemas

1250

select SCHEMA_NAME from information_schema.schemata

1193

1251

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

1194

1252

1195

1253

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

1199

1257

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

1200

1258

);

1201

1259

1260

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

1261

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

1262

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

1202

1263

1203

# database, table, field, key

1264

# database, table, field, key, view

1204

1265

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

1205

1266

1206

select TABLE_NAME from data_dictionary.tables where

1207

table_schema='test';

1208

1209

select COLUMN_NAME from data_dictionary.columns where

1210

table_schema='test';

1211

1212

select INDEX_NAME from data_dictionary.indexes where

1213

table_schema='test';

1214

1215

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

1216

1279

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

1217

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;

1218

1319

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

1219

1220

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

1221

1346

1222

1347

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

1223

1348

1224

--error ER_CANT_CREATE_TABLE,ER_TABLE_PERMISSION_DENIED

1225

create table t1 like data_dictionary.processlist;

1226

create table t1 like data_dictionary.processlist engine=innodb;

1227

show create table t1;

1228

drop table t1;

1229

--error ER_CANT_CREATE_TABLE,ER_TABLE_PERMISSION_DENIED

1230

create temporary table t1 like data_dictionary.processlist;

1231

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;

1232

1356

show create table t1;

1233

1357

drop table t1;

1234

1358

1253

1377

c1 INT DEFAULT 12 COMMENT 'column1',

1254

1378

c2 INT NULL COMMENT 'column2',

1255

1379

c3 INT NOT NULL COMMENT 'column3',

1256

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

1380

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

1257

1381

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

1258

1382

c6 VARCHAR(255))

1259

COLLATE=utf8_bin;

1383

COLLATE latin1_bin;

1260

1384

1261

1385

--echo

1262

1386

1263

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

1264

1387

SHOW CREATE TABLE t1;

1265

1388

1266

1389

--echo

1269

1392

1270

1393

--echo

1271

1394

1272

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

1273

1395

SHOW CREATE TABLE t2;

1274

1396

1275

1397

--echo

1299

1421

1300

1422

--echo

1301

1423

1302

--echo

1303

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

1304

drop table t2;

1305

1306

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

1307

drop table t2;

1308

1309

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

1310

1432

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);

1311

drop table t2;

1312

1433

1313

1434

--echo

1314

1435

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

1316

1437

1317

1438

--echo

1318

1439

--echo # -- Check ALTER TABLE.

1440

--error ER_INVALID_DEFAULT

1319

1441

ALTER TABLE t1 ADD INDEX(c1);

1320

1442

1321

1443

--echo

1322

1444

--echo # -- Check DATETIME.

1445

SET sql_mode = '';

1446

1323

1447

--echo

1324

1448

1325

1449

CREATE TABLE t3(c1 DATETIME NOT NULL);

1326

--error ER_INVALID_DATETIME_VALUE # Bad datetime

1327

1450

INSERT INTO t3 VALUES (0);

1328

1451

1329

1452

--echo

1453

SET sql_mode = TRADITIONAL;

1454

1455

--echo

1456

--error ER_TRUNCATED_WRONG_VALUE

1330

1457

ALTER TABLE t3 ADD INDEX(c1);

1331

1458

1332

1459

--echo

1333

1460

--echo # -- Cleanup.

1334

1461

1462

SET sql_mode = '';

1335

1463

DROP TABLE t1;

1336

1464

DROP TABLE t2;

1337

1465

DROP TABLE t3;

1339

1467

--echo

1340

1468

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

1341

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 »