~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

SHOW CREATE TABLE t1;

386

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;

387

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

388

371

show create table t2;

389

372

drop table t1,t2;

390

373

412

395

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

413

396

414

397

415

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

416

--error ER_DUPLICATED_VALUE_IN_TYPE

417

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

418

--error ER_DUPLICATED_VALUE_IN_TYPE

419

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

420

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;

421

402

422

403

423

404

# Bug #1209

428

409

select database();

429

410

drop database mysqltest;

430

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;

431

421

use test;

432

422

433

423

434

424

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

435

425

436

426

437

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

438

#--error ER_WRONG_NAME_FOR_INDEX

439

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

440

#--error ER_WRONG_NAME_FOR_INDEX

441

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

442

443

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

444

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

445

#show create table t1;

446

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

447

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

448

#show create table t2;

449

450

#create table t3 (a int);

451

#--error ER_WRONG_NAME_FOR_INDEX

452

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

453

#--error ER_WRONG_NAME_FOR_INDEX

454

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

455

456

#create table t4 (`primary` int);

457

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

458

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

459

#show create table t4;

460

#create table t5 (`PRIMARY` int);

461

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

462

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

463

#show create table t5;

464

465

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

466

451

467

452

468

453

# bug #3266 TEXT in CREATE TABLE SELECT

478

463

SELECT * FROM t3;

479

464

drop table t1, t2, t3;

480

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;

481

476

482

477

483

478

# Bug#10224 - ANALYZE TABLE crashing with simultaneous

486

481

# an improper fix is present.

487

482

488

483

create table t1 (a int);

489

--error ER_UPDATE_TABLE_USED

484

--error 1093

490

485

create table t1 select * from t1;

491

## TODO: Huh? --error ER_WRONG_OBJECT

492

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

486

--error ER_WRONG_OBJECT

487

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

493

488

flush tables with read lock;

494

489

unlock tables;

495

490

drop table t1;

497

492

498

493

# Bug#10413: Invalid column name is not rejected

499

494

500

--error ER_WRONG_TABLE_NAME

495

--error 1103

501

496

create table t1(column.name int);

502

--error ER_WRONG_TABLE_NAME

497

--error 1103

503

498

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

504

--error ER_WRONG_DB_NAME

499

--error 1102

505

500

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

506

501

create table t1(t1.name int);

507

502

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

510

505

511

506

# Bug #12537: UNION produces longtext instead of varchar

512

507

513

CREATE TABLE t1 (f1 VARCHAR(255));

508

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

514

509

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

515

510

DESC t2;

516

511

DROP TABLE t1,t2;

534

529

drop table if exists test.t1;

535

530

--enable_warnings

536

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;

537

542

# Bug #6008 MySQL does not create warnings when

538

543

# creating database and using IF NOT EXISTS

539

544

540

545

create database mysqltest;

541

create database if not exists mysqltest;

546

create database if not exists mysqltest character set latin2;

542

547

show create database mysqltest;

543

548

drop database mysqltest;

544

549

use test;

548

553

549

554

# BUG#14139

550

555

create table t1 (

551

a varchar(112) collate utf8_bin not null,

556

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

552

557

primary key (a)

553

558

) select 'test' as a ;

554

559

#--warning 1364

555

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

556

560

show create table t1;

557

561

drop table t1;

558

562

561

565

# calculation of number of NULLs.

562

566

563

567

CREATE TABLE t2 (

564

a int default NULL

568

a int(11) default NULL

565

569

);

566

570

insert into t2 values(111);

567

571

568

572

#--warning 1364

569

573

create table t1 (

570

a varchar(12) collate utf8_bin not null,

574

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

571

575

b int not null, primary key (a)

572

576

) select a, 1 as b from t2 ;

573

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

574

577

show create table t1;

575

578

drop table t1;

576

579

577

--error ER_NO_DEFAULT_FOR_FIELD

580

#--warning 1364

578

581

create table t1 (

579

a varchar(12) collate utf8_bin not null,

582

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

580

583

b int not null, primary key (a)

581

584

) select a, 1 as c from t2 ;

585

show create table t1;

586

drop table t1;

582

587

588

#--warning 1364

583

589

create table t1 (

584

a varchar(12) collate utf8_bin not null,

590

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

585

591

b int null, primary key (a)

586

592

) select a, 1 as c from t2 ;

587

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

588

show create table t1;

589

drop table t1;

590

591

create table t1 (

592

a varchar(12) collate utf8_bin not null,

593

b int not null, primary key (a)

594

) select 'a' as a , 1 as b 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,

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/

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 ;

604

609

show create table t1;

605

610

drop table t1, t2;

606

611

612

617

613

618

#--warning 1364

614

619

create table t2 (

615

a1 varchar(12) collate utf8_bin not null,

620

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

616

621

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

617

622

primary key (a1)

618

623

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

620

625

621

626

#--warning 1364

622

627

create table t2 (

623

a1 varchar(12) collate utf8_bin,

628

a1 varchar(12) charset utf8 collate utf8_bin,

624

629

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

625

630

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

626

631

633

638

634

639

#--warning 1364

635

640

create table t2 (

636

a1 varchar(12) collate utf8_bin not null,

641

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

637

642

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

638

643

primary key (a1)

639

644

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

643

648

644

649

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

645

650

select a1,a2 from t1;

646

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

647

651

show create table t2;

648

652

649

653

drop table t1, t2;

650

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

651

676

652

677

653

678

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

663

688

create table t1 select * from t1;

664

689

# Error which happens before select_create::prepare()

665

690

--error ER_CANT_AGGREGATE_2COLLATIONS

666

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

667

692

# Error during table creation

668

693

--error ER_KEY_COLUMN_DOES_NOT_EXITS

669

694

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

670

695

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

671

# TODO: This really should be failing...

672

# create table t1 (a int);

673

# --error ER_WRONG_VALUE_COUNT_ON_ROW

674

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

675

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

676

700

# Finally error which happens during insert

677

701

--error ER_DUP_ENTRY

678

702

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

679

703

# What happens if table already exists ?

680

704

create table t1 (i int);

681

# TODO: BUG lp:311045

682

#--error ER_TABLE_EXISTS_ERROR

683

#create table t1 select 1 as i;

705

--error ER_TABLE_EXISTS_ERROR

706

create table t1 select 1 as i;

684

707

create table if not exists t1 select 1 as i;

685

708

select * from t1;

686

drop table t1;

687

709

# Error before select_create::prepare()

688

710

--error ER_CANT_AGGREGATE_2COLLATIONS

689

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;

690

713

# Error which happens during insertion of rows

691

# TODO: Bug lp:311072

692

# create table t1 (i int);

693

# alter table t1 add primary key (i);

694

# --error ER_DUP_ENTRY

695

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

696

# select * from t1;

697

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

698

719

699

720

700

721

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

714

735

715

736

716

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

717

788

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

718

789

719

790

create table t1 (upgrade int);

869

940

);

870

941

871

942

# Check that the table is not corrupted

872

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

873

943

show create table t1;

874

944

flush tables;

875

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

876

945

show create table t1;

877

946

878

947

# Repeat test using ALTER to add indexes

1018

1087

add key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (

1019

1088

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

1020

1089

1021

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

1022

1090

show create table t1;

1023

1091

flush tables;

1024

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

1025

1092

show create table t1;

1026

1093

1027

1094

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

1028

1095

# to be rewritten to hit the limit

1029

1096

1030

1097

# Ensure limit is really 64 keys

1031

--error ER_TOO_MANY_KEYS

1098

--error 1069

1032

1099

alter table t1 add key

1033

1100

a065_long_123456789_123456789_123456789_123456789_123456789_1234 (

1034

1101

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

1042

1109

c16 int, c17 int);

1043

1110

1044

1111

# Get error for max key parts

1045

--error ER_TOO_MANY_KEY_PARTS

1112

--error 1070

1046

1113

alter table t1 add key i1 (

1047

1114

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

1048

1115

1049

1116

# Get error for max key-name length

1050

--error ER_TOO_LONG_IDENT

1117

--error 1059

1051

1118

alter table t1 add key

1052

1119

a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);

1053

1120

1054

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

1055

1121

show create table t1;

1056

1122

1057

1123

drop table t1;

1101

1167

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

1102

1168

# precedence

1103

1169

1104

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

1105

#SHOW INDEX FROM t1;

1170

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

1171

SHOW INDEX FROM t1;

1106

1172

DROP TABLE t1;

1107

1173

1108

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

1109

#SHOW INDEX FROM t1;

1174

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

1175

SHOW INDEX FROM t1;

1110

1176

DROP TABLE t1;

1111

1177

1112

1178

1126

1192

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

1127

1193

# This should give warning

1128

1194

drop table if exists t2;

1129

# TODO: Bug lp:311072

1130

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

1131

#--error ER_DUP_ENTRY

1132

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

1133

#SELECT * from t2;

1134

#TRUNCATE table t2;

1135

#--error ER_DUP_ENTRY

1136

#INSERT INTO t2 select * from t1;

1137

#SELECT * from t2;

1138

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

1139

1204

1140

1205

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

1141

1206

--error ER_DUP_ENTRY

1152

1217

# Test incorrect database names

1153

1218

1154

1219

1155

--error ER_WRONG_DB_NAME

1220

--error 1102

1156

1221

CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1157

--error ER_WRONG_DB_NAME

1222

--error 1102

1158

1223

DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1159

1224

1160

1225

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

1161

# --error ER_BAD_DB_ERROR

1226

# --error 1049

1162

1227

# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;

1163

# --error ER_WRONG_DB_NAME

1228

# --error 1102

1164

1229

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1165

1230

# create database mysqltest;

1166

# --error ER_WRONG_DB_NAME

1231

# --error 1102

1167

1232

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1168

1233

# drop database mysqltest;

1169

1234

1170

--error ER_WRONG_DB_NAME

1235

--error 1102

1171

1236

USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1172

#--error ER_WRONG_DB_NAME

1237

--error 1102

1173

1238

SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1174

1239

1175

1176

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

1177

1240

1241

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

1242

1243

set names utf8;

1178

1244

1179

1245

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

1180

1246

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

1181

1247

select database();

1182

1248

use test;

1183

1249

1184

select SCHEMA_NAME from data_dictionary.schemas

1250

select SCHEMA_NAME from information_schema.schemata

1185

1251

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

1186

1252

1187

1253

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

1191

1257

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

1192

1258

);

1193

1259

1260

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

1261

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

1262

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

1194

1263

1195

# database, table, field, key

1264

# database, table, field, key, view

1196

1265

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

1197

1266

1198

select TABLE_NAME from data_dictionary.tables where

1199

table_schema='test';

1200

1201

select COLUMN_NAME from data_dictionary.columns where

1202

table_schema='test';

1203

1204

select INDEX_NAME from data_dictionary.indexes where

1205

table_schema='test';

1206

1207

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

1208

1279

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

1209

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;

1210

1319

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

1211

1212

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

1213

1346

1214

1347

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

1215

1348

1216

--error ER_CANT_CREATE_TABLE

1217

create table t1 like data_dictionary.processlist;

1218

create table t1 like data_dictionary.processlist engine=innodb;

1219

show create table t1;

1220

drop table t1;

1221

--error ER_CANT_CREATE_TABLE

1222

create temporary table t1 like data_dictionary.processlist;

1223

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;

1224

1356

show create table t1;

1225

1357

drop table t1;

1226

1358

1245

1377

c1 INT DEFAULT 12 COMMENT 'column1',

1246

1378

c2 INT NULL COMMENT 'column2',

1247

1379

c3 INT NOT NULL COMMENT 'column3',

1248

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

1380

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

1249

1381

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

1250

1382

c6 VARCHAR(255))

1251

COLLATE=utf8_bin;

1383

COLLATE latin1_bin;

1252

1384

1253

1385

--echo

1254

1386

1255

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

1256

1387

SHOW CREATE TABLE t1;

1257

1388

1258

1389

--echo

1261

1392

1262

1393

--echo

1263

1394

1264

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

1265

1395

SHOW CREATE TABLE t2;

1266

1396

1267

1397

--echo

1291

1421

1292

1422

--echo

1293

1423

1294

--echo

1295

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

1296

drop table t2;

1297

1298

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

1299

drop table t2;

1300

1301

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

1302

1432

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);

1303

drop table t2;

1304

1433

1305

1434

--echo

1306

1435

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

1308

1437

1309

1438

--echo

1310

1439

--echo # -- Check ALTER TABLE.

1440

--error ER_INVALID_DEFAULT

1311

1441

ALTER TABLE t1 ADD INDEX(c1);

1312

1442

1313

1443

--echo

1314

1444

--echo # -- Check DATETIME.

1445

SET sql_mode = '';

1446

1315

1447

--echo

1316

1448

1317

1449

CREATE TABLE t3(c1 DATETIME NOT NULL);

1318

--error ER_INVALID_DATETIME_VALUE # Bad datetime

1319

1450

INSERT INTO t3 VALUES (0);

1320

1451

1321

1452

--echo

1453

SET sql_mode = TRADITIONAL;

1454

1455

--echo

1456

--error ER_TRUNCATED_WRONG_VALUE

1322

1457

ALTER TABLE t3 ADD INDEX(c1);

1323

1458

1324

1459

--echo

1325

1460

--echo # -- Cleanup.

1326

1461

1462

SET sql_mode = '';

1327

1463

DROP TABLE t1;

1328

1464

DROP TABLE t2;

1329

1465

DROP TABLE t3;

1331

1467

--echo

1332

1468

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

1333

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 »